介绍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管理的数据库对应。如sys,user等。其中mysql，performance_shema，sys是默认就有的数据库。
information_schema也是一个默认的数据库，但是不在这个目录下
-
一些日志文件 binlog.*
-
InnoDB的表空间和日志文件 ib_logfile
-
一些（自动生成的）用于SSL和RSA的证书和密钥文件 pem结尾的文件
-
服务运行时的进程ID
-
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 ：所有视图信息
...
官方文档
网友评论