MYSQL02高级_目录结构、默认数据库、表文件、系统独立表空间

①. MySQL目录结构

• ①. 如何查看关联mysql目录

[root@mysql8 ~]# find / -name mysql
/var/lib/mysql
/var/lib/mysql/mysql
/etc/selinux/targeted/tmp/modules/100/mysql
/etc/selinux/targeted/active/modules/100/mysql
/etc/logrotate.d/mysql
/usr/lib64/mysql
/usr/bin/mysql
[root@mysql8 ~]# 
1
2
3
4
5
6
7
8
9

• ②. 数据库文件存放路径为:/var/lib/mysql/

1. MYSQL服务器程序在启动时会到文件系统的某个目录下加载一些文件,之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中,这个目录就称为数据目录
2. 数据目录对应着一个系统变量datadir,我们在使用客户端与服务器建立连接之后查看这个系统变量的值就可以了

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.05 sec)
1
2
3
4
5
6
7

[root@mysql8 mysql]# pwd
/var/lib/mysql
[root@mysql8 mysql]# ll
总用量 188888
-rw-r-----. 1 mysql mysql       56 11月 22 21:42 auto.cnf
-rw-r-----. 1 mysql mysql      350 5月   5 21:09 binlog.000002
-rw-r-----. 1 mysql mysql     1907 5月   7 19:19 binlog.000003
-rw-r-----. 1 mysql mysql      179 5月   7 22:27 binlog.000004
-rw-r-----. 1 mysql mysql      156 5月  12 20:45 binlog.000005
-rw-r-----. 1 mysql mysql       64 5月  12 20:45 binlog.index
-rw-------. 1 mysql mysql     1676 11月 22 21:42 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 11月 22 21:42 ca.pem
-rw-r--r--. 1 mysql mysql     1112 11月 22 21:42 client-cert.pem
-rw-------. 1 mysql mysql     1676 11月 22 21:42 client-key.pem
drwxr-x---. 2 mysql mysql     4096 5月   5 22:08 dbtest1
-rw-r-----. 1 mysql mysql   196608 5月  12 20:47 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql  8585216 11月 22 21:42 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql     3557 5月   7 22:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月  12 20:45 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月  12 20:47 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 11月 22 21:42 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月  12 20:45 ibtmp1
drwxr-x---. 2 mysql mysql     4096 5月  12 20:45 #innodb_temp
drwxr-x---. 2 mysql mysql     4096 11月 22 21:42 mysql
-rw-r-----. 1 mysql mysql 25165824 5月  12 20:45 mysql.ibd
srwxrwxrwx. 1 mysql mysql        0 5月  12 20:45 mysql.sock
-rw-------. 1 mysql mysql        5 5月  12 20:45 mysql.sock.lock
drwxr-x---. 2 mysql mysql     4096 11月 22 21:42 performance_schema
-rw-------. 1 mysql mysql     1680 11月 22 21:42 private_key.pem
-rw-r--r--. 1 mysql mysql      452 11月 22 21:42 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 11月 22 21:42 server-cert.pem
-rw-------. 1 mysql mysql     1680 11月 22 21:42 server-key.pem
drwxr-x---. 2 mysql mysql     4096 11月 22 21:42 sys
-rw-r-----. 1 mysql mysql 16777216 5月  12 20:47 undo_001
-rw-r-----. 1 mysql mysql 16777216 5月  12 20:47 undo_002
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

• ③. 相关命令目录：/usr/bin(mysqladmin、mysqlbinlog、mysqldump等命令)和/usr/sbin
  
• ④. 配置文件目录：/usr/share/mysql-8.0(命令及配置文件）/etc - my.cnf

②. 查看默认数据库

• ①. 查看默认的数据库

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10

• ②. mysql:MySQL 系统自带的核心数据库,它存储了MySQL的用户账户和权限信息,一些存储过程、事件的定义信息,一些运行过程中产生的日志信息,一些帮助信息以及时区信息等

• ③. information_schema:MySQL系统自带的数据库,这个数据库保存着MySQL服务器 维护的所有其他数据库的信息 ,比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据,而是一些描述性信息,有时候也称之为 元数据 。在系统数据库 information_schema中提供了一些以innodb_sys开头的表,用于表示内部系统表

• ④. performance_schema:MySQL系统自带的数据库,这个数据库里主要保存MySQL服务器运行过程中的一些状态信息,可以用来监控MySQL服务的各类性能指标 。包括统计最近执行了哪些语句,在执行过程的每个阶段都花费了多长时间,内存的使用情况等信息

• ⑤. sys:MySQL系统自带的数据库,这个数据库主要是通过视图的形式把information_schema和performance_schema结合起来,帮助系统管理员和开发人员监控 MySQL 的技术性能

③. MYSQL5.7和8表文件

• ①. 对于innodb,对应的表有两个文件:.frm + .ibd,存储了表结构信息和表数据信息,db.opt的文件:这个文件描述了这个数据库的各种属性(比如字符集和比较规则是个啥）
  

• ②. 对于myisam存储引擎,会有三个文件,分别是上图的表结构信息 + 表数据信息 + 表索引信息
  

• ③. MYSQL8 - 对于innodb,对应的表只有了.ibd文件,存储了表结构信息和表数据信息

[root@mysql8 etc]# cd /var/lib/mysql
[root@mysql8 mysql]# ll
总用量 188888
-rw-r-----. 1 mysql mysql       56 11月 22 21:42 auto.cnf
-rw-r-----. 1 mysql mysql      350 5月   5 21:09 binlog.000002
-rw-r-----. 1 mysql mysql     1907 5月   7 19:19 binlog.000003
-rw-r-----. 1 mysql mysql      179 5月   7 22:27 binlog.000004
-rw-r-----. 1 mysql mysql      156 5月  12 20:45 binlog.000005
-rw-r-----. 1 mysql mysql       64 5月  12 20:45 binlog.index
-rw-------. 1 mysql mysql     1676 11月 22 21:42 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 11月 22 21:42 ca.pem
-rw-r--r--. 1 mysql mysql     1112 11月 22 21:42 client-cert.pem
-rw-------. 1 mysql mysql     1676 11月 22 21:42 client-key.pem
drwxr-x---. 2 mysql mysql     4096 5月   5 22:08 dbtest1
-rw-r-----. 1 mysql mysql   196608 5月  12 20:47 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql  8585216 11月 22 21:42 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql     3557 5月   7 22:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月  12 20:45 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月  12 20:47 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 11月 22 21:42 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月  12 20:45 ibtmp1
drwxr-x---. 2 mysql mysql     4096 5月  12 20:45 #innodb_temp
drwxr-x---. 2 mysql mysql     4096 11月 22 21:42 mysql
-rw-r-----. 1 mysql mysql 25165824 5月  12 20:45 mysql.ibd
srwxrwxrwx. 1 mysql mysql        0 5月  12 20:45 mysql.sock
-rw-------. 1 mysql mysql        5 5月  12 20:45 mysql.sock.lock
drwxr-x---. 2 mysql mysql     4096 11月 22 21:42 performance_schema
-rw-------. 1 mysql mysql     1680 11月 22 21:42 private_key.pem
-rw-r--r--. 1 mysql mysql      452 11月 22 21:42 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 11月 22 21:42 server-cert.pem
-rw-------. 1 mysql mysql     1680 11月 22 21:42 server-key.pem
drwxr-x---. 2 mysql mysql     4096 11月 22 21:42 sys
-rw-r-----. 1 mysql mysql 16777216 5月  12 20:47 undo_001
-rw-r-----. 1 mysql mysql 16777216 5月  12 20:47 undo_002
[root@mysql8 mysql]# cd dbtest1/
[root@mysql8 dbtest1]# ll
总用量 224
-rw-r-----. 1 mysql mysql 147456 5月   5 21:46 ssm_shopping_cart.ibd
-rw-r-----. 1 mysql mysql 114688 5月   5 22:08 test1.ibd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

• ④. MYSQL8 - 对于Myisam有三个文件生产
  

③. 系统、独立表空间

• ①. 系统表空间(system tablespace)

1. 默认情况下,InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件,这个文件就是对应的系统表空间 在文件系统上的表示。怎么才12M？注意这个文件是 自扩展文件 ,当不够用的时候它会自己增加文件大小
2. 如果你想让系统表空间对应文件系统上多个实际文件,或者仅仅觉得原来的ibdata1这个文件名
   难听,那可以在MySQL启动时配置对应的文件路径以及它们的大小,比如我们这样修改一下my.cnf 配置文件

[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend
1
2

• ②. 独立表空间(file-per-table tablespace)

1. 在MySQL5.6.6以及之后的版本中,InnoDB并不会默认的把各个表的数据存储到系统表空间中,而是为每一个表建立一个独立表空间 ,也就是说我们创建了多少个表,就有多少个独立表空间。使用独立表空间 来存储表数据的话,会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件,文件名和表名相同,只不过添加了一个.ibd 的扩展名而已,所以完整的文件名称长这样
2. MYSQL5.7:比如:我们使用了独立表空间去存储数据库dbtest1下的test1表的话,那么在该表所在数据库对应的目录下会为test1表创建这两个文件:frm + idb文件
   

• ③. 系统表空间与独立表空间的设置
  我们可以自己指定使用系统表空间 还是 独立表空间来存储数据,这个功能由启动参数
  innodb_file_per_table 控制,比如说我们想刻意将表数据都存储到 系统表空间 时,可以在启动
  MySQL服务器的时候这样配置

[server]
innodb_file_per_table=0 # 0:代表使用系统表空间； 1:代表使用独立表空间
1
2

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7