MYSQL的存储目录以及标准的数据库

介绍MYSQL的存储目录

Ⅰ、通过命令查找Mysql数据存放的位置

$ 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>

​ 可以看到 路径是 /usr/local/mysql/data，默认情况下data文件夹不允许普通用户访问，需要使用root用户才能进入该目录。
目录如下：

# 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

官方Mysql data目录介绍

1. 每一个子目录都是一个数据库目录，和mysql管理的数据库对应。如sys,user等。其中mysql，performance_shema，sys是默认就有的数据库。

   information_schema也是一个默认的数据库，但是不在这个目录下

2. 一些日志文件 binlog.*

3. InnoDB的表空间和日志文件 ib_logfile

4. 一些（自动生成的）用于SSL和RSA的证书和密钥文件 pem结尾的文件

5. 服务运行时的进程ID

6. mysqld-auto.cnf保存持久化的全局变量（一旦持久化变量，就会创建文件）

###########  设置持久变量  ##########
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>

###########  查看文件  ##########
# 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]
$

Ⅱ、四个默认的Schema介绍

数据库安装成功后有四个默认的数据库，分别是mysql，performance_shema，sys，information_schema。使用的是Mysql8.0，不同版本会有差异，但是5.7之后差异不会很大。

###1、系统数据库—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)

主要包含的信息：用户信息，角色信息，时区信息，表的外键信息，以及Mysql服务需要的信息
官方文档

###2、性能收集表—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)

主要用于收集数据库服务器性能参数。
官方文档

sys schema主要是提供了一些视图（数据来自performation_schema）,让开发者和使用者方便的查看性能问题
官方文档

3、信息Schema–information_schema

INFORMATION_SCHEMA提供对数据库元数据的访问，有关MySQL服务器的信息，例如数据库或表的名称，列的数据类型或访问权限。

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)

从表的名称中我们不难看到，很多都是我们设计表时接触过的东西：

• CHARACTER_SETS ：字符字典表
• COLUMN_PRIVILEGES ：列权限表
• KEYWORDS ：关键词表
• TABLES ：所有表信息
• VIEWS ：所有视图信息
  …
  官方文档

我的个人博客,有空来坐坐