database와 USER

Oracle은 스키마와 유저가 동일한 의미로 사용됩니다. USER가 스키마인 것이지요.

하지만 Mysql은 오라클의 스키마 역할을 하는 database가 존재하고(우리가 흔히 알고 쓰는 database와 다르게 Mysql에서 사용하는 스키마와 비슷한 개념), 유저를 따로 생성한 후 해당 database에 대한 권한을 부여하여 사용하게 됩니다.

즉, Mysql은 USER와 Database가 분리되있는 개념입니다.

 

현재 DB의 database 확인

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

information_schema

mysql

performance_schema

sys

위의 4개의 database는 mysql설치 시 기본적으로 구성되어 있는 database입니다.

각각의 database들에는 어떤 테이블들이 존재하는지 알아보겠습니다.

 

information_schema

information_schema 안에는 mysql이 구성된 system 정보들이 저장된 테이블들이 보입니다.

오라클의 dba_ 나 v$ 뷰같은 딕셔너리 뷰의 역할을 하는 테이블들이 존재하고 있습니다.

 

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS     |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
| CHECK_CONSTRAINTS                     |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMNS_EXTENSIONS                    |
| COLUMN_PRIVILEGES                     |
| COLUMN_STATISTICS                     |
| ENABLED_ROLES                         |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| INNODB_BUFFER_PAGE                    |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_CACHED_INDEXES                 |
| INNODB_CMP                            |
| INNODB_CMPMEM                         |
| INNODB_CMPMEM_RESET                   |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_CMP_RESET                      |
| INNODB_COLUMNS                        |
| INNODB_DATAFILES                      |
| INNODB_FIELDS                         |
| INNODB_FOREIGN                        |
| INNODB_FOREIGN_COLS                   |
| INNODB_FT_BEING_DELETED               |
| INNODB_FT_CONFIG                      |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_DELETED                     |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_INDEXES                        |
| INNODB_METRICS                        |
| INNODB_SESSION_TEMP_TABLESPACES       |
| INNODB_TABLES                         |
| INNODB_TABLESPACES                    |
| INNODB_TABLESPACES_BRIEF              |
| INNODB_TABLESTATS                     |
| INNODB_TEMP_TABLE_INFO                |
| INNODB_TRX                            |
| INNODB_VIRTUAL                        |
| KEYWORDS                              |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| RESOURCE_GROUPS                       |
| ROLE_COLUMN_GRANTS                    |
| ROLE_ROUTINE_GRANTS                   |
| ROLE_TABLE_GRANTS                     |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMATA_EXTENSIONS                   |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| ST_GEOMETRY_COLUMNS                   |
| ST_SPATIAL_REFERENCE_SYSTEMS          |
| ST_UNITS_OF_MEASURE                   |
| TABLES                                |
| TABLESPACES                           |
| TABLESPACES_EXTENSIONS                |
| TABLES_EXTENSIONS                     |
| TABLE_CONSTRAINTS                     |
| TABLE_CONSTRAINTS_EXTENSIONS          |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_ATTRIBUTES                       |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| VIEW_ROUTINE_USAGE                    |
| VIEW_TABLE_USAGE                      |
+---------------------------------------+
79 rows in set (0.00 sec)

 

 

 

mysql

mysql소유의 table들도 mysql을 구성하고 사용하는데 필요한 system 정보들이 저장되있는 테이블들이 보입니다.

대표적으로 user나 db가 있습니다. 현재 mysql에 있는 user와 db를 조회할 때 사용합니다.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------------------------------+
| Tables_in_mysql                              |
+----------------------------------------------+
| columns_priv                                 |
| component                                    |
| db                                           |
| default_roles                                |
| engine_cost                                  |
| func                                         |
| general_log                                  |
| global_grants                                |
| gtid_executed                                |
| help_category                                |
| help_keyword                                 |
| help_relation                                |
| help_topic                                   |
| innodb_index_stats                           |
| innodb_table_stats                           |
| password_history                             |
| plugin                                       |
| procs_priv                                   |
| proxies_priv                                 |
| replication_asynchronous_connection_failover |
| role_edges                                   |
| server_cost                                  |
| servers                                      |
| slave_master_info                            |
| slave_relay_log_info                         |
| slave_worker_info                            |
| slow_log                                     |
| tables_priv                                  |
| time_zone                                    |
| time_zone_leap_second                        |
| time_zone_name                               |
| time_zone_transition                         |
| time_zone_transition_type                    |
| user                                         |
+----------------------------------------------+
34 rows in set (0.00 sec)

 

PERFORMANCE_SCHEMA

DB 성능 정보에 관한 테이블을이 이곳에 저장되어 있습니다. mysql은 여러 수집도구용 코드들을 통해 성능데이터를 수집하고 있으며 그 정보들을 확인할 수 있는 database입니다.

 

mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> show tables;
+------------------------------------------------------+
| Tables_in_performance_schema                         |
+------------------------------------------------------+
| accounts                                             |
| binary_log_transaction_compression_stats             |
| cond_instances                                       |
| data_lock_waits                                      |
| data_locks                                           |
| error_log                                            |
| events_errors_summary_by_account_by_error            |
| events_errors_summary_by_host_by_error               |
| events_errors_summary_by_thread_by_error             |
| events_errors_summary_by_user_by_error               |
| events_errors_summary_global_by_error                |
| events_stages_current                                |
| events_stages_history                                |
| events_stages_history_long                           |
| events_stages_summary_by_account_by_event_name       |
| events_stages_summary_by_host_by_event_name          |
| events_stages_summary_by_thread_by_event_name        |
| events_stages_summary_by_user_by_event_name          |
| events_stages_summary_global_by_event_name           |
| events_statements_current                            |
| events_statements_histogram_by_digest                |
| events_statements_histogram_global                   |
| events_statements_history                            |
| events_statements_history_long                       |
| events_statements_summary_by_account_by_event_name   |
| events_statements_summary_by_digest                  |
| events_statements_summary_by_host_by_event_name      |
| events_statements_summary_by_program                 |
| events_statements_summary_by_thread_by_event_name    |
| events_statements_summary_by_user_by_event_name      |
| events_statements_summary_global_by_event_name       |
| events_transactions_current                          |
| events_transactions_history                          |
| events_transactions_history_long                     |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
| events_waits_current                                 |
| events_waits_history                                 |
| events_waits_history_long                            |
| events_waits_summary_by_account_by_event_name        |
| events_waits_summary_by_host_by_event_name           |
| events_waits_summary_by_instance                     |
| events_waits_summary_by_thread_by_event_name         |
| events_waits_summary_by_user_by_event_name           |
| events_waits_summary_global_by_event_name            |
| file_instances                                       |
| file_summary_by_event_name                           |
| file_summary_by_instance                             |
| global_status                                        |
| global_variables                                     |
| host_cache                                           |
| hosts                                                |
| keyring_keys                                         |
| log_status                                           |
| memory_summary_by_account_by_event_name              |
| memory_summary_by_host_by_event_name                 |
| memory_summary_by_thread_by_event_name               |
| memory_summary_by_user_by_event_name                 |
| memory_summary_global_by_event_name                  |
| metadata_locks                                       |
| mutex_instances                                      |
| objects_summary_global_by_type                       |
| performance_timers                                   |
| persisted_variables                                  |
| prepared_statements_instances                        |
| processlist                                          |
| replication_applier_configuration                    |
| replication_applier_filters                          |
| replication_applier_global_filters                   |
| replication_applier_status                           |
| replication_applier_status_by_coordinator            |
| replication_applier_status_by_worker                 |
| replication_asynchronous_connection_failover         |
| replication_connection_configuration                 |
| replication_connection_status                        |
| replication_group_member_stats                       |
| replication_group_members                            |
| rwlock_instances                                     |
| session_account_connect_attrs                        |
| session_connect_attrs                                |
| session_status                                       |
| session_variables                                    |
| setup_actors                                         |
| setup_consumers                                      |
| setup_instruments                                    |
| setup_objects                                        |
| setup_threads                                        |
| socket_instances                                     |
| socket_summary_by_event_name                         |
| socket_summary_by_instance                           |
| status_by_account                                    |
| status_by_host                                       |
| status_by_thread                                     |
| status_by_user                                       |
| table_handles                                        |
| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |
| threads                                              |
| tls_channel_status                                   |
| user_defined_functions                               |
| user_variables_by_thread                             |
| users                                                |
| variables_by_thread                                  |
| variables_info                                       |
+------------------------------------------------------+
108 rows in set (0.01 sec)

 

sys

Mysql 5.7.7버전 부터 New Feature로 소개된 database입니다.

performance_schema를 통해 원하는 데이터를 추출하기가 까다롭다는 단점을 극복하기 위해 나왔습니다.

performance_schema의 대량 데이터를 목적에 따라 쉽게 사용할수 있도록 views,procedures,functions을 제공합니다.

 

mysql> use sys
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| innodb_lock_waits                             |
| io_by_thread_by_latency                       |
| io_global_by_file_by_bytes                    |
| io_global_by_file_by_latency                  |
| io_global_by_wait_by_bytes                    |
| io_global_by_wait_by_latency                  |
| latest_file_io                                |
| memory_by_host_by_current_bytes               |
| memory_by_thread_by_current_bytes             |
| memory_by_user_by_current_bytes               |
| memory_global_by_current_bytes                |
| memory_global_total                           |
| metrics                                       |
| processlist                                   |
| ps_check_lost_instrumentation                 |
| schema_auto_increment_columns                 |
| schema_index_statistics                       |
| schema_object_overview                        |
| schema_redundant_indexes                      |
| schema_table_lock_waits                       |
| schema_table_statistics                       |
| schema_table_statistics_with_buffer           |
| schema_tables_with_full_table_scans           |
| schema_unused_indexes                         |
| session                                       |
| session_ssl_status                            |
| statement_analysis                            |
| statements_with_errors_or_warnings            |
| statements_with_full_table_scans              |
| statements_with_runtimes_in_95th_percentile   |
| statements_with_sorting                       |
| statements_with_temp_tables                   |
| sys_config                                    |
| user_summary                                  |
| user_summary_by_file_io                       |
| user_summary_by_file_io_type                  |
| user_summary_by_stages                        |
| user_summary_by_statement_latency             |
| user_summary_by_statement_type                |
| version                                       |
| wait_classes_global_by_avg_latency            |
| wait_classes_global_by_latency                |
| waits_by_host_by_latency                      |
| waits_by_user_by_latency                      |
| waits_global_by_latency                       |
| x$host_summary                                |
| x$host_summary_by_file_io                     |
| x$host_summary_by_file_io_type                |
| x$host_summary_by_stages                      |
| x$host_summary_by_statement_latency           |
| x$host_summary_by_statement_type              |
| x$innodb_buffer_stats_by_schema               |
| x$innodb_buffer_stats_by_table                |
| x$innodb_lock_waits                           |
| x$io_by_thread_by_latency                     |
| x$io_global_by_file_by_bytes                  |
| x$io_global_by_file_by_latency                |
| x$io_global_by_wait_by_bytes                  |
| x$io_global_by_wait_by_latency                |
| x$latest_file_io                              |
| x$memory_by_host_by_current_bytes             |
| x$memory_by_thread_by_current_bytes           |
| x$memory_by_user_by_current_bytes             |
| x$memory_global_by_current_bytes              |
| x$memory_global_total                         |
| x$processlist                                 |
| x$ps_digest_95th_percentile_by_avg_us         |
| x$ps_digest_avg_latency_distribution          |
| x$ps_schema_table_statistics_io               |
| x$schema_flattened_keys                       |
| x$schema_index_statistics                     |
| x$schema_table_lock_waits                     |
| x$schema_table_statistics                     |
| x$schema_table_statistics_with_buffer         |
| x$schema_tables_with_full_table_scans         |
| x$session                                     |
| x$statement_analysis                          |
| x$statements_with_errors_or_warnings          |
| x$statements_with_full_table_scans            |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting                     |
| x$statements_with_temp_tables                 |
| x$user_summary                                |
| x$user_summary_by_file_io                     |
| x$user_summary_by_file_io_type                |
| x$user_summary_by_stages                      |
| x$user_summary_by_statement_latency           |
| x$user_summary_by_statement_type              |
| x$wait_classes_global_by_avg_latency          |
| x$wait_classes_global_by_latency              |
| x$waits_by_host_by_latency                    |
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
101 rows in set (0.00 sec)

 

DATABASE의 생성

 

오라클의 user를 생성하듯이 간단한 명령어로 database를 생성할 수 있습니다.

db_test라는 이름의 database를 생성해보도록 하겠습니다. 또한 UTF8을 캐릭터셋으로 합니다.

create database [DB명];

mysql> create database db_test default character set UTF8;
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| db_test            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

 

사용자 생성

사용자도 마찬가지로 오라클의 user를 생성하듯이 명령어로 생성가능합니다.

db_test라는 유저를 생성한 후 권한을 부여하여 db_test database를 사용할 수 있게 하겠습니다.

오라클과의 차이점은 'USER'@'HOST' 의 형식으로 생성된다는 것입니다. 

 

유저 생성시 host의 종류는  @ 뒤에 '%' 나 'localhost'나 'IP주소'를 적어주게 되고, 이는 접속가능한 IP를 적어주는 것입니다.

%의 경우에는 어느 IP나 상관없이(ALL) 접속 가능하다는 의미이고, 'localhost'나 'IP주소'는 특정 IP를 가진 컴퓨터만 접속 가능하게 합니다.

 

즉 같은 이름의 user라도 뒤의 host 가 다르면 mysql에서 다르게 인식을 한다는 것입니다.

유저 생성
mysql> create user 'db_test'@'localhost' identified by 'db_test';
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> create user 'db_test'@'192.168.56.58' identified by 'db_test';
Query OK, 0 rows affected (0.00 sec)



확인
mysql> select host,user from mysql.user;
+---------------+------------------+
| host          | user             |
+---------------+------------------+
| %             | test             |
| 192.168.56.58 | db_test          |
| localhost     | db_test          |
| localhost     | mysql.infoschema |
| localhost     | mysql.session    |
| localhost     | mysql.sys        |
| localhost     | root             |
+---------------+------------------+
7 rows in set (0.00 sec)

 

권한 조회

show grants for 'USER'@'HOST'의 형식으로 권한을 조회할 수 있습니다.

현재는 생성한 직후라 별다른 권한이 없습니다.

mysql> show grants for 'db_test'@'localhost';
+---------------------------------------------+
| Grants for db_test@localhost                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `db_test`@`localhost` |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'db_test'@'192.168.56.58';
+-------------------------------------------------+
| Grants for db_test@192.168.56.58                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `db_test`@`192.168.56.58` |
+-------------------------------------------------+
1 row in set (0.00 sec)

 

권한 부여

권한은 grant [권한 명] on [DB 명].[테이블명] to 'USER'@'HOST';

의 형식으로 부여합니다.

해당 DB의 모든 권한을 부여하고 싶으면 권한 명에 all privilegess 를 넣어 모든 권한을 줄 수 있습니다.

그리고 특정 테이블명이 아니라 *를 사용시, 해당 DB에 있는 모든 테이블에게 동일하게 권한을 부여할 수 있습니다.

 

 

mysql> grant create,alter,drop,select,insert,update,delete on db_test.* to 'db_test'@'localhost';
Query OK, 0 rows affected (0.01 sec)


mysql> show grants for 'db_test'@'localhost';
+---------------------------------------------------------------------------------------------------+
| Grants for db_test@localhost                                                                      |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `db_test`@`localhost`                                                       |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `db_test`.* TO `db_test`@`localhost` |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'db_test'@'192.168.56.58';
+-------------------------------------------------+
| Grants for db_test@192.168.56.58                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `db_test`@`192.168.56.58` |
+-------------------------------------------------+
1 row in set (0.00 sec)

 

확인

[root@ysbae ~]# mysql -u db_test -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show datbases;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'datbases' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_test            |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> create table db_test.test(a int(5));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> select * from db_test.test;
Empty set (0.01 sec)

 

아마 IP를 다르게한 서버를 하나 더 구축 후 db_test로 권한을 localhost와 서로 다르게 부여하고, 접속시도해서 테이블이 생성되는지, 조회되는지 확인하면 같은 USER여도 권한을 다르게 관리할 수 있는것을 확인할 수 있을 것 같습니다. 현재 저의 테스트 서버로는 힘들고 하나더 구축해야 가능할것 같네요.