MySQL中文件存储形式

MySQL中文件存储形式（InnoDB/MyIsam）

与innodb文件格式相关的参数为

innodb_data_file_path、innodb_data_file_path、innodb_log_group_home_dir、innodb_open_files

1、独占表空间

下面进入MySQL数据存放目录，查看使用innodb时MySQL存储文件格式

[root@www ~]# cd /var/lib/mysql/

[root@www mysql]# ls –lrt

总用量 21352

drwx------. 2 mysql mysql     4096 11月 26 2017 test

drwx------. 2 mysql mysql     4096 11月 26 2017 mysql

-rw-rw----. 1 mysql mysql   765307 11月 26 2017master-bin.000002

-rw-rw----. 1 mysql mysql    19742 11月 26 2017master-bin.000001

-rw-rw----. 1 mysqlmysql  5242880 11月 26 2017 ib_logfile1

drwx------. 2 mysql mysql     4096 11月 26 2017 testslave

-rw-rw----. 1 mysql mysql      764 11月 27 2017master-bin.000003

-rw-rw----. 1 mysql mysql      59911月 28 2017 master-bin.000004

drwx------. 2 mysql mysql     4096 11月 28 2017 hellodb

-rw-rw----. 1 mysql mysql    10270 11月 28 2017master-bin.000005

-rw-rw----. 1 mysql mysql      125 4月  20 21:34 master-bin.000006

-rw-rw----. 1 mysql mysql      596 4月  25 00:23 master-bin.000007

-rw-rw----. 1 mysql mysql      367 4月  28 20:08 master-bin.000008

srwxrwxrwx. 1 mysql mysql        0 6月   8 17:34 mysql.sock

-rw-rw----. 1 mysql mysql      180 6月   8 17:34 master-bin.index

drwx------. 2 mysql mysql     4096 6月   8 18:36 mydb

-rw-rw----. 1 mysql mysql     1031 6月   8 19:20 master-bin.000009

-rw-rw----. 1 mysqlmysql  5242880 6月   8 19:20 ib_logfile0

-rw-rw----. 1 mysql mysql 10485760 6月   8 19:20 ibdata1

1）、日志组文件：

可以发现，./目录下数据存在日志组文件： ib_logfile0和ib_logfile1，默认均为5M。

2）、表结构文件：*.frm

在MYSQL中建立任何一张数据表，在其数据存放目录对应的数据库目录下都有对应表的.frm文件,

.frm文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，

在MySQL中，“.frm”文件跟数据库存储引擎无关，任何存储引擎的数据表结构文件都存储为.frm文件，

命名方式为“tablename.frm”，如scores.frm，.frm文件可以用来在数据库崩溃时恢复表结构。

[root@www mysql]# cd hellodb/

[root@www hellodb]# ls -lrt

总用量 824

-rw-rw----. 1 mysql mysql   8658 11月 28 2017 scores.frm

-rw-rw----. 1 mysql mysql     61 11月 28 2017 db.opt

-rw-rw----. 1 mysql mysql   8602 11月 28 2017 courses.frm

-rw-rw----. 1 mysql mysql   8630 11月 28 2017 coc.frm

-rw-rw----. 1 mysql mysql   8636 11月 28 2017 classes.frm

-rw-rw----. 1 mysql mysql   8622 11月 28 2017 toc.frm

-rw-rw----. 1 mysql mysql   8656 11月 28 2017teachers.frm

-rw-rw----. 1 mysql mysql  98304 11月 28 2017 toc.ibd

-rw-rw----. 1 mysql mysql  98304 11月 28 2017teachers.ibd

-rw-rw----. 1 mysql mysql  98304 11月 28 2017 scores.ibd

-rw-rw----. 1 mysql mysql  98304 11月 28 2017 courses.ibd

-rw-rw----. 1 mysql mysql  98304 11月 28 2017 classes.ibd

-rw-rw----. 1 mysql mysql   8736 11月 28 2017students.frm

-rw-rw----. 1 mysql mysql    603 11月 28 2017 test.frm

-rw-rw----. 1 mysql mysql 131072 11月 28 2017students.ibd

-rw-rw----. 1 mysql mysql  98304 6月   8 19:20 coc.ibd

3）、独占表空间文件：*.ibd

在innodb_file_per_table配置为ON时设置为独占表空间，此时，系统将为每一个表单独存储为“.ibd”文件，

（如scores.ibd）在此文件中，存储与该表相关的数据、索引、表的内部数据字典信息。

4）、字符集和排序规则文件：db.opt

[root@www hellodb]# cat db.opt

default-character-set=utf8

default-collation=utf8_general_ci

文件中记录了该库的默认字符集编码和字符集排序规则。如果你创建数据库指定默认字符集和排序规则，

后续创建的表如果没有指定字符集和排序规则，那么该新建的表将采用db.opt文件中指定的属性。

如果删除这个db.opt，MySQL在建表时将参照全局的字符设置，即default-character-set ，

这个影响是在建表的时候才会有体现，你不在那个库新建表，是没什么差异的。

字符集配置:

vim /etc/my.cnf

[client]

default-character-set =charset (charset是mysql支持的字符集）

5）、binlog二进制日志文件：记录主数据库服务器的DDL和DML操作。

在主从复制时，二进制日志文件将通过Binlog Dump线程传递给从服务器的I/O线程，

I/O线程获取事件数据后传递给从服务器的relaylog,然后在从服务器重做一遍实行复制。

[root@www hellodb]# cd ..

[root@www mysql]#

cat master-bin.000009                                                                                                                                                  

可以发现，该日志中记录的是一些DDL和DML操作。

6）、二进制日志索引文件：master-bin.index

在数据库目录下有一个索引文件用来记录已经使用的二进制日志文件的名字，该索引文件的名字为"xxx-bin.index"。

索引文件最后一行表示当前正在使用的二进制日志名字

[root@www mysql]# cat master-bin.index

./master-bin.000001

./master-bin.000002

./master-bin.000003

./master-bin.000004

./master-bin.000005

./master-bin.000006

./master-bin.000007

./master-bin.000008

./master-bin.000009

./master-bin.000010

 

2、共享表空间

系统表空间文件，存储InnoDB系统信息和用户数据库表数据和索引，所有表共用。

配置为innodb共享表空间查看文件格式，必须修改配置文件后重启服务器。

[root@test ~]# vim /etc/my.cnf

innodb_file_per_table=0

#innodb_file_per_table=1

[root@www etc]# vim my.cnf

innodb_file_per_table=0  

#innodb_file_per_table=1

[root@test ~]# service mysqld restart ;sshwww "service mysqld restart"

停止 mysqld：                                              [确定]

正在启动 mysqld：                                          [确定]

停止 mysqld： [确定]

正在启动 mysqld： [确定]

[root@test ~]# mysql -u root

mysql> show variableslike 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF   |

+-----------------------+-------+

1 row in set (0.00 sec)

查看参数innodb_file_per_table可以发现，已关闭独占表空间，配置为共享表空间

前提：共享表空间模式下在数据库中新增数据，查看文件存储形式

新建一个数据库ibdatax，并建表，查看表存储文件方式

mysql> create database ibdatax;

Query OK, 1 row affected (0.04 sec)

mysql> use ibdatax

Database changed

mysql> create table tb_dept( Id intprimary key auto_increment,Name varchar(18),description varchar(100));

Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb_deptvalue('1','hr','recruit employee');

Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_deptvalue('','SN','Service Network');

Query OK, 1 row affected, 1 warning (0.00sec)

mysql> create table tb_emp(id intprimary key auto_increment,Name varchar(18),sex varchar(2),age int,addressvarchar(200),email varchar(100));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb_empvalue('1','Jones','Man','31','GZ','jone@163.com');

Query OK, 1 row affected, 1 warning (0.00sec)

mysql> insert into tb_empvalue('','Will','Man','28','GZ','will@126.com');

Query OK, 1 row affected, 2 warnings (0.00sec)

mysql> insert into tb_empvalue('3','Kin','Man','35','FS','kin@126.com');

Query OK, 1 row affected, 1 warning (0.00sec)

mysql> SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB |Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000010 |     1740 |              |                  |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

进入目录，查看表数据存储形式：

[root@www mysql]# pwd

/var/lib/mysql

[root@www mysql]# ls -lrt

总用量 21360

drwx------. 2 mysql mysql     4096 11月 26 2017 test

drwx------. 2 mysql mysql     4096 11月 26 2017 mysql

-rw-rw----. 1 mysql mysql   765307 11月 26 2017master-bin.000002

-rw-rw----. 1 mysql mysql    19742 11月 26 2017master-bin.000001

-rw-rw----. 1 mysql mysql  5242880 11月 26 2017 ib_logfile1

drwx------. 2 mysql mysql     4096 11月 26 2017 testslave

-rw-rw----. 1 mysql mysql      764 11月 27 2017master-bin.000003

-rw-rw----. 1 mysql mysql      599 11月 28 2017master-bin.000004

drwx------. 2 mysql mysql     4096 11月 28 2017 hellodb

-rw-rw----. 1 mysql mysql    10270 11月 28 2017master-bin.000005

-rw-rw----. 1 mysql mysql      125 4月  20 21:34 master-bin.000006

-rw-rw----. 1 mysql mysql      596 4月  25 00:23 master-bin.000007

-rw-rw----. 1 mysql mysql      367 4月  28 20:08 master-bin.000008

drwx------. 2 mysql mysql     4096 6月   8 18:36 mydb

-rw-rw----. 1 mysql mysql     2893 6月   8 22:21 master-bin.000009

srwxrwxrwx. 1 mysql mysql        0 6月   8 22:21 mysql.sock

-rw-rw----. 1 mysql mysql      200 6月   8 22:21 master-bin.index

drwx------. 2 mysql mysql     4096 6月   8 22:36 ibdatax

-rw-rw----. 1 mysql mysql     1740 6月   8 22:39 master-bin.000010

-rw-rw----. 1 mysql mysql  5242880 6月   8 22:39 ib_logfile0

-rw-rw----. 1 mysql mysql 10485760 6月   8 22:39 ibdata1

1）、共享表空间表结构文件：*.frm

可以发现，在以数据库名命名的目录中，只存在表tb_dept和表tb_emp的结构文件，不存在数据文件。

此外就是文件记录该库的默认字符集编码和字符集排序规则的db.opt文件

[root@www mysql]# cd ibdatax

[root@www ibdatax]# ls -lrt

总用量 28

-rw-rw----. 1 mysql mysql   65 6月   8 22:23 db.opt

-rw-rw----. 1 mysql mysql 8630 6月   8 22:26 tb_dept.frm

-rw-rw----. 1 mysql mysql 8710 6月   8 22:36 tb_emp.frm

[root@www ibdatax]# cat db.opt

default-character-set=latin1

default-collation=latin1_swedish_ci

2）、共享表空间文件：ibdata1

因为配置共享表空间的参数innodb_data_home_dir未配置，所以默认存放在数据存放根目录下，

可以发现，所有表数据文件都被记录在一个名为ibdata1的大小为10M的文件中。

[root@www mysql]# cd ..

[root@www mysql]# ls -lh|grep ibdata

-rw-rw----. 1 mysql mysql  10M 6月   8 22:39 ibdata1

drwx------. 2 mysql mysql 4.0K 6月   8 22:36 ibdatax

 

3）、从服务器上记录主从最后一次同步信息的日志文件：

root@test ~]# cd /var/lib/mysql/

[root@test mysql]# ls -lrt

总用量 20748

drwx------ 2 mysql mysql     4096 11月 26 2017 test

drwx------ 2 mysql mysql     4096 11月 26 2017 mysql

-rw-rw---- 1 mysql mysql  5242880 11月 26 2017 ib_logfile1

drwx------ 2 mysql mysql     4096 11月 26 2017 testslave

drwx------ 2 mysql mysql     4096 11月 28 2017 hellodb

drwx------ 2 mysql mysql     4096 6月   8 18:36 mydb

-rw-rw---- 1 mysql mysql     1220 6月   8 21:10 relay-log.000041

-rw-rw---- 1 mysql mysql     2114 6月   8 22:20 relay-log.000042

-rw-rw---- 1 mysql mysql      244 6月   8 22:20 master-bin.000005

-rw-rw---- 1 mysql mysql      149 6月   8 22:21 relay-log.000043

srwxrwxrwx 1 mysql mysql        0 6月   8 22:21 mysql.sock

-rw-rw---- 1 mysql mysql      120 6月   8 22:21 master-bin.index

-rw-rw---- 1 mysql mysql      1066月   8 22:21 master-bin.000006

-rw-rw---- 1 mysql mysql      779 6月   8 22:22 relay_log.index

-rw-rw---- 1 mysql mysql      295 6月   8 22:22 relay-log.000045

-rw-rw---- 1 mysql mysql      295 6月   8 22:22 relay-log.000044

drwx------ 2 mysql mysql     40966月   8 22:36 ibdatax

-rw-rw---- 1 mysqlmysql       47 6月   8 22:39relay-log.info

-rw-rw---- 1 mysql mysql     1886 6月   8 22:39 relay-log.000046

-rw-rw---- 1 mysqlmysql       77 6月   8 22:39 master.info

-rw-rw---- 1 mysql mysql  5242880 6月   8 22:39 ib_logfile0

-rw-rw---- 1 mysql mysql 10485760 6月   8 22:39 ibdata1

[root@test mysql]# cat master.info

15

master-bin.000010   <-- 主服务器binlog dump线程传递的给从服务器binlog文件

1740              <-- 主服务器Position

192.168.88.131     <-- 主服务地址

repluser           <-- 提供复制的账户

replpass           <-- 提供复制的账户密码

3306              <-- 主服务地址端口

60

0

0

[root@test mysql]# cat relay-log.info

./relay-log.000046     <--从服务器I/O线程更新event的从服务器relay-log文件

1886                <--从服务器Relay_Log_Pos

master-bin.000010     <--从服务器I/O线程传递的主服务器binlog文件

1740                <-- 主服务器Position

[root@test mysql]# cd ibdatax

[root@test ibdatax]# ls -lrt

总用量 28

-rw-rw---- 1 mysql mysql   65 6月   8 22:23 db.opt

-rw-rw---- 1 mysql mysql 8630 6月   8 22:26 tb_dept.frm

-rw-rw---- 1 mysql mysql 8710 6月   8 22:36 tb_emp.frm

4）、二进制日志索引文件：relay_log.index

在数据库目录下有一个索引文件用来记录已经使用的二进制日志文件的名字，该索引文件的名字为"xxx-bin.index"。

索引文件最后一行表示当前正在使用的二进制日志名字

[root@test mysql]# cat relay_log.index

./relay-log.000006

./relay-log.000007

./relay-log.000008

./relay-log.000009

./relay-log.000010

./relay-log.000011

….

./relay-log.000049

./relay-log.000050

./relay-log.000051

./relay-log.000052

[root@test mysql]#

附：MySQL存储引擎MyISAM存储的文件格式：

.frm 文件与操作系统和数据库引擎无关，所有数据结构文件均存储为.frm文件。

[root@test mysql]# cd mysql

[root@test mysql]# ll|head

总用量 992

-rw-rw---- 1 mysql mysql   8820 11月 26 2017columns_priv.frm

-rw-rw---- 1 mysql mysql      0 11月 26 2017columns_priv.MYD

-rw-rw---- 1 mysql mysql   4096 11月 26 2017columns_priv.MYI

-rw-rw---- 1 mysql mysql   9582 11月 26 2017 db.frm

-rw-rw---- 1 mysql mysql    880 11月 26 2017 db.MYD

-rw-rw---- 1 mysql mysql   5120 11月 26 2017 db.MYI

-rw-rw---- 1 mysql mysql  10223 11月 26 2017 event.frm

-rw-rw---- 1 mysql mysql      0 11月 26 2017 event.MYD

-rw-rw---- 1 mysql mysql   2048 11月 26 2017 event.MYI

db.frm：表结构文件

db.MYD：my data表数据文件

db.MYI：my index表索引文件，没有索引时为空