Introduce the storage directory of MYSQL
I. Location of Mysql Data Storage by Command
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.12 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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 global variables like "%datadir%"; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | datadir | /usr/local/mysql/data/ | +---------------+------------------------+ 1 row in set (0.02 sec) mysql>
You can see that the path is / usr/local/mysql/data. By default, the data folder is not allowed to be accessed by ordinary users. It requires root users to enter the directory.
The catalogue is as follows:
# sparrow @ localhost in /usr/local/mysql/data [15:10:05]
$ ll
total 350640
-rw-r----- 1 _mysql _mysql 56B 9 17 14:44 auto.cnf
-rw-r----- 1 _mysql _mysql 1.4M 9 29 12:30 binlog.000002
-rw-r----- 1 _mysql _mysql 12K 10 9 12:26 binlog.000003
-rw-r----- 1 _mysql _mysql 4.1K 10 9 17:19 binlog.000004
-rw-r----- 1 _mysql _mysql 22K 10 11 20:00 binlog.000005
-rw-r----- 1 _mysql _mysql 31K 10 15 18:20 binlog.000006
-rw-r----- 1 _mysql _mysql 52K 10 19 10:39 binlog.000007
-rw-r----- 1 _mysql _mysql 2.0K 10 22 14:37 binlog.000008
-rw-r----- 1 _mysql _mysql 155B 10 22 14:37 binlog.000009
-rw-r----- 1 _mysql _mysql 128B 10 22 14:37 binlog.index
-rw------- 1 _mysql _mysql 1.6K 9 17 14:44 ca-key.pem
-rw-r--r-- 1 _mysql _mysql 1.1K 9 17 14:44 ca.pem
-rw-r--r-- 1 _mysql _mysql 1.1K 9 17 14:44 client-cert.pem
-rw------- 1 _mysql _mysql 1.6K 9 17 14:44 client-key.pem
drwxr-x--- 14 _mysql _mysql 448B 9 25 12:21 config
-rw-r----- 1 _mysql _mysql 4.6K 10 22 14:37 ib_buffer_pool
-rw-r----- 1 _mysql _mysql 48M 10 22 14:37 ib_logfile0
-rw-r----- 1 _mysql _mysql 48M 9 17 14:44 ib_logfile1
-rw-r----- 1 _mysql _mysql 12M 10 22 14:37 ibdata1
-rw-r----- 1 _mysql _mysql 12M 10 22 14:37 ibtmp1
-rw-r----- 1 _mysql _mysql 12K 9 17 14:51 localhost.err
drwxr-x--- 8 _mysql _mysql 256B 9 17 14:44 mysql
-rw-r----- 1 _mysql _mysql 25M 10 22 14:37 mysql.ibd
-rw-r----- 1 _mysql _mysql 11K 10 22 14:37 mysqld.local.err
-rw-r----- 1 _mysql _mysql 3B 10 22 14:37 mysqld.local.pid
drwxr-x--- 104 _mysql _mysql 3.3K 9 17 14:44 performance_schema
-rw------- 1 _mysql _mysql 1.6K 9 17 14:44 private_key.pem
-rw-r--r-- 1 _mysql _mysql 452B 9 17 14:44 public_key.pem
-rw-r--r-- 1 _mysql _mysql 1.1K 9 17 14:44 server-cert.pem
-rw------- 1 _mysql _mysql 1.6K 9 17 14:44 server-key.pem
drwxr-x--- 3 _mysql _mysql 96B 9 17 14:44 sys
-rw-r----- 1 _mysql _mysql 12M 10 22 14:37 undo_001
-rw-r----- 1 _mysql _mysql 12M 10 22 14:37 undo_002
drwxr-x--- 15 _mysql _mysql 480B 9 25 19:32 user
Introduction to the official Mysql data directory
-
Each subdirectory is a database directory, corresponding to the database managed by mysql. For example, sys,user, etc. Mysql, performance_shema and sys are the default databases.
information_schema is also a default database, but not in this directory
-
Some log files binlog.*
-
InnoDB tablespace and log files ib_logfile
-
Certificate and key files for SSL and RSA Files at the end of pem
-
Process ID of service runtime
-
mysqld-auto.cnf saves persistent global variables (once persistent variables are created files)
########### Setting persistent variables ########## mysql> set presist max_connections = 2600; 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 'max_connections = 2600' at line 1 mysql> set presist max_connections=2000; 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 'max_connections=2000' at line 1 mysql> set persist max_connections=2000; Query OK, 0 rows affected (0.01 sec) mysql> ########### see file ########## # sparrow @ localhost in /usr/local/mysql/data [16:20:08] C:1 $ sudo cat mysqld-auto.cnf { "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "2000" , "Metadata" : { "Timestamp" : 1540196262026574 , "User" : "root" , "Host" : "localhost" } } } }% # sparrow @ localhost in /usr/local/mysql/data [16:20:18] $
II. Introduction to Four Default Schema s
After successful installation, there are four default databases: mysql, performance_shema, sys and information_schema. Mysql 8.0 is used, different versions will be different, but after 5.7 there will be no significant difference.
### 1. System database-mysql
+---------------------------+ | 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 | | 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 | +---------------------------+ 33 rows in set (0.00 sec)
Mainly contains information: user information, role information, time zone information, external key information of tables, and information needed by Mysql services.
Official Documents
### 2. Performance Collection Table - performance_schema,sys
+------------------------------------------------------+ | Tables_in_performance_schema | +------------------------------------------------------+ | accounts | | cond_instances | | data_lock_waits | | data_locks | | events_errors_summary_by_account_by_error | ... | events_stages_current | | events_stages_history | | events_stages_history_long | | events_statements_current | ... | events_waits_current | | events_waits_history | | events_waits_history_long | ... | file_instances | | file_summary_by_event_name | | file_summary_by_instance | | global_status | | global_variables | | host_cache | | hosts | | log_status | | memory_summary_by_account_by_event_name | | metadata_locks | | mutex_instances | | objects_summary_global_by_type | | performance_timers | | persisted_variables | | prepared_statements_instances | | replication_applier_configuration | ... | 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 | | user_defined_functions | | user_variables_by_thread | | users | | variables_by_thread | | variables_info | +------------------------------------------------------+ 102 rows in set (0.00 sec)
It is mainly used to collect database server performance parameters.
Official Documents
sys schema mainly provides some views (data from performance_schema) for developers and users to view performance problems easily.
Official Documents
3. Information Schema-information_schema
INFORMATION_SCHEMA provides access to database metadata, information about MySQL servers, such as database or table names, column data types or access rights.
mysql> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLLATIONS | | COLUMN_PRIVILEGES | | COLUMN_STATISTICS | | COLUMNS | | ENGINES | | EVENTS | | FILES | | INNODB_BUFFER_PAGE | | INNODB_BUFFER_PAGE_LRU | | INNODB_BUFFER_POOL_STATS | | INNODB_CACHED_INDEXES | | INNODB_CMP | | INNODB_CMP_PER_INDEX | | INNODB_CMP_PER_INDEX_RESET | | INNODB_CMP_RESET | | INNODB_CMPMEM | | INNODB_CMPMEM_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_TABLES | | INNODB_TABLESPACES | | INNODB_TABLESPACES_BRIEF | | INNODB_TABLESTATS | | INNODB_TEMP_TABLE_INFO | | INNODB_TRX | | INNODB_VIRTUAL | | KEY_COLUMN_USAGE | | KEYWORDS | | OPTIMIZER_TRACE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | RESOURCE_GROUPS | | ROUTINES | | SCHEMA_PRIVILEGES | | SCHEMATA | | ST_GEOMETRY_COLUMNS | | ST_SPATIAL_REFERENCE_SYSTEMS | | STATISTICS | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TABLES | | TABLESPACES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | +---------------------------------------+ 62 rows in set (0.01 sec)
It is not difficult to see from the name of the table that many of them are things that we have come into contact with when designing the table:
- CHARACTER_SETS: Character Dictionary Table
- COLUMN_PRIVILEGES: Column permission table
- KEYWORDS: Keyword List
- TABLES: All table information
- VIEWS: All View Information
...
Official Documents