mysql物理文件

• 1. 数据库的数据存储文件
• 2. 慢查询日志
• 3. 错误日志与二进制文件
• 4. 二进制文件的基本操作
• 5. 使用二进制文件恢复数据
• 6. 中继日志（了解）

1.数据库中的数据存储文件

MySQL数据会在data目录下面简历一个以数据库为名的文件夹，用来存储数据库的表文件数据。按照表后缀做介绍：

.frm文件: 无论哪种存储引擎，创建表后一定会生成一个以表名命名的'.frm'文件，frm文件主要存放与表相关的数据信息。主要包括表结构的定义信息。当数据奔溃时，用户可以通过frm文件来恢复数据表结构

.MYD:是MYISAM存储引擎专用，存放MYISAM表的数据。每一个MYISAM表都会有一个'.MYD'文件与之对应，同样存放在所属数据库的文件夹下。和'.frm'文件一起。
.MYI: 是MYISAM存储引擎专用，主要存放MYISAM表的索引相关信息，对于MYISAM存储来说，可以被cache的内容主要来源于'.myi'文件中。
.ibd"文件与".ibdata"文件:这两种文件都是存放INNODB数据文件，INNODB的数据存储方式可以通过配置来决定使用共享表空间存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用'.ibd'文件来存放数据。
ibdata用共享存储表空间来存储数据，则会使用ibdata文件来存放，所有表共同使用一个（或者多个，可以自行配置）ibdata文件。
ibdata文件可以通过innodb_data_home_dir（数据存放目录）和innodb_data_file_path(配置每个文件的名称) 两个参数配置组成
innodb_data_file_path中可以一次配置多个ibdata文件 #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend 配置方式

共享表空间以及独占表空间都是针对数据的存储方式而言的。
共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中。
独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有 一个.ibd文件。其中这个文件包括了 单独一个表的数据 内容以及索引内容。

共享表空间和独享表空间的对比：
（1.）共享表空间：
优点： 可以放表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。
缺点： 所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日志系统这类应用最不适合用共享表空间。
（2.）独立表空间：
优点:
1. 每个表都有自已独立的表空间。
2. 每个表的数据和索引都会存在自已的表空间中。
3. 可以实现单表在不同的数据库中移动。
4. 空间可以回收
a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:altertable TableName engine=innodb;回缩不用的空间。
b) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点：单表增加过大，如超过100个G。 相比较之下，使用独占表空间的效率以及性能会更高一点

共享表空间和独立表空间之间的转换：

mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

ON代表独立表空间管理，OFF代表共享表空间管理；
修改数据库的表空间管理方式 修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间；
innodb_file_per_table=1 为使用独占表空间
innodb_file_per_table=0 为使用共享表空间

2.慢查询日志

什么是慢查询，用来做什么？
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一 定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

慢查询相关参数

查看慢查询开启状态和慢查询日志文件存储位置

mysql> show variables like "%slow_query_%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。
log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

mysql> show variables like "%slow_query_log%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

mysql> set global slow_query_log=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%slow_query_log%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | OFF                             |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%slow_query_log%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如 果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器，
如下所示

slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log 
long_query_time=3

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数long_query_time控 制，默认情况下long_query_time的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。关于运 行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行时间， 之前仅用秒为单位记录。如果记录到表里面，只会记录整数部分，不会记录微秒部分。

设置long_query_time和设置slow_query_log相同。

查询慢查询存储类型

mysql>  show variables like "%log_output%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow

mysqldumpslow不会用，以后补充学习

3. 错误日志

错误日志（Error Log）是 MySQL 中最常用的一种日志，主要记录 MySQL 服务器启动和停止过程中的信 息、服务器在运行过程中发生的故障和异常情况等。

启动错误日志
在 MySQL 数据库中，默认开启错误日志功能。一般情况下，错误日志存储在 MySQL 数据库的数据文件夹 下，通常名称为 hostname.err。其中，hostname 表示 MySQL 服务器的主机名。
在 MySQL 配置文件中，错误日志所记录的信息可以通过 log-error 和 log-warnings 来定义，其中，log-err 定义是否启用错误日志功能和错误日志的存储位置，log-warnings 定义是否将警告信息也记录到错误日志 中。

错误日志中记录着开启和关闭 MySQL 服务的时间，以及服务运行过程中出现哪些异常等信息。如果 MySQL 服务出现异常，可以到错误日志中查找原因。
在 MySQL 中，通过 SHOW 命令可以查看错误日志文件所在的目录及文件名信息。

mysql> show variables like "log_error";
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | ./localhost.132.err |
+---------------+---------------------+
1 row in set (0.00 sec)

在 MySQL 中，可以使用 mysqladmin 命令来开启新的错误日志，以保证 MySQL 服务器上的硬盘空间。 mysqladmin 命令的语法如下：

mysqladmin -uroot -p flush-logs

执行该命令后，MySQL 服务器首先会自动创建一个新的错误日志，然后将旧的错误日志更名为filename.err-old。
MySQL 服务器发生异常时，管理员可以在错误日志中找到发生异常的时间、原因，然后根据这些信息来解 决异常。对于很久之前的错误日志，查看的可能性不大，可以直接将这些错误日志删除。

二进制日志

二进制日志（Binary Log）也可叫作变更日志（Update Log），是 MySQL 中非常重要的日志。主要用于记 录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作。

如果 MySQL 数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。

默认情况下，二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启，命令如下：

mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

在 MySQL 中，可以通过在配置文件中添加 log-bin 选项来开启二进制日志，格式如下:

[mysqld] log-bin=dir/[filename]

其中，dir 参数指定二进制文件的存储路径；filename 参数指定二进制文件的文件名，其形式为 filename.number，number 的形式为 000001、000002 等。

每次重启 MySQL 服务后，都会生成一个新的二进制日志文件，这些日志文件的文件名中filename部分不会改变,number会不断递增
如果没有 dir 和 filename 参数，二进制日志将默认存储在数据库的数据目录下，默认的文件名为 hostname-bin.number，其中 hostname 表示主机名。

方案配置如下：

log-bin=mysql-bin 
binlog_format=mixed 
server-id = 1

在mysql的data目录下查看生成的binlog日志文件，如下：

对二进制文件的操作:
1.查看二进制日志文件列表：

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |        177 |
| mysql-bin.000002 |        437 |
| mysql-bin.000003 |        685 |
| mysql-bin.000004 |       1610 |
| mysql-bin.000005 | 1073742176 |
| mysql-bin.000006 | 1073741882 |
| mysql-bin.000007 | 1073741951 |
| mysql-bin.000008 | 1073741901 |
| mysql-bin.000009 | 1073742138 |
| mysql-bin.000010 | 1073742172 |
| mysql-bin.000011 | 1073742126 |
| mysql-bin.000012 | 1073741922 |
| mysql-bin.000013 |  809725695 |
| mysql-bin.000014 |        154 |
| mysql-bin.000015 |        177 |
| mysql-bin.000016 |        177 |
| mysql-bin.000017 |        325 |
| mysql-bin.000018 |        154 |
+------------------+------------+
18 rows in set (0.00 sec)

2.查看当前正在写入的二进制日志文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000018 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.查看二进制日志文件内容

mysql> show binlog events in 'mysql-bin.000001'G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.31-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 154
 Event_type: Stop
  Server_id: 1
End_log_pos: 177
       Info: 
3 rows in set (0.00 sec)

ERROR: 
No query specified

4.删除二进制文件

二进制日志中记录着大量的信息，如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。删除二进 制日志的方法很多，下面介绍几种删除二进制日志的方法。

1. 使用 RESET MASTER 语句可以删除的所有二进制日志，该语句的形式如下：

RESET MASTER;

登录 MySQL 数据库后，可以执行该语句来删除所有二进制日志。删除所有二进制日志后，MySQL 将会重 新创建新的二进制日志，新二进制日志的编号从 000001 开始。

2. 根据编号删除二进制日志
   每个二进制日志文件后面有一个 6 位数的编号，如 000001。使用 PURGE MASTER LOGS TO 语句，可以删除指定二进制日志的编号之前的日志。该语句的基本语法形式如下：

PURGE MASTER LOGS TO 'filename.number';

该语句将删除编号小于 filename.number 的所有二进制日志

下面删除 mylog.000004 之前的二进制日志，代码如下： PURGE MASTER LOGS TO 'mylog.000004';
代码执行完后，编号为 000001、000002 和 000003 的二进制日志将被删除。

3. 根据创建时间删除二进制日志
   使用 PURGE MASTER LOGS TO 语句，可以删除指定时间之前创建的二进制日志，该语句的基本语法格式如下

PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

其中，“hh”为 24 制的小时。该语句将删除在指定时间之前创建的所有二进制日志。
下面删除 2019-12-20 15:00:00 之前创建的二进制日志，代码如下：

PURGE MASTER LOGS TO '2019-12-20 15:00:00"; 

代码执行完后，2019-12-20 15:00:00 之前创建的所有二进制日志将被删除。

使用二进制文件恢复数据

1. 创建一个用于测试的库create database mytest;

mysql> create database mytest;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1046 (3D000): No database selected
mysql> use mytest;
Database changed
mysql> CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| user             |
+------------------+
1 row in set (0.00 sec)

mysql> insert into `user`(username,age)values('starsky',10); insert into `user`(username,age)values('ShineYork',10); insert into `user`(username,age)values('Will',10);
Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

查看二进制文件：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000018 |     1673 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |        177 |
| mysql-bin.000002 |        437 |
| mysql-bin.000003 |        685 |
| mysql-bin.000004 |       1610 |
| mysql-bin.000005 | 1073742176 |
| mysql-bin.000006 | 1073741882 |
| mysql-bin.000007 | 1073741951 |
| mysql-bin.000008 | 1073741901 |
| mysql-bin.000009 | 1073742138 |
| mysql-bin.000010 | 1073742172 |
| mysql-bin.000011 | 1073742126 |
| mysql-bin.000012 | 1073741922 |
| mysql-bin.000013 |  809725695 |
| mysql-bin.000014 |        154 |
| mysql-bin.000015 |        177 |
| mysql-bin.000016 |        177 |
| mysql-bin.000017 |        325 |
| mysql-bin.000018 |       1673 |
+------------------+------------+
18 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000018'G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000018
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.31-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000018
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 

*************************** 20. row ***************************
   Log_name: mysql-bin.000018
        Pos: 1515
 Event_type: Query
  Server_id: 1
End_log_pos: 1642
       Info: use `mytest`; insert into `user`(username,age)values('Will',10)
*************************** 21. row ***************************
   Log_name: mysql-bin.000018
        Pos: 1642
 Event_type: Xid
  Server_id: 1
End_log_pos: 1673
       Info: COMMIT /* xid=40 */
21 rows in set (0.00 sec)

ERROR: 
No query specified

删除表数据

mysql> delete from `user` where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> delete from `user` where id=2;
Query OK, 1 row affected (0.01 sec)

mysql> delete from `user` where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> drop table `user`;
Query OK, 0 rows affected (0.02 sec)

mysql> drop database `mytest`;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| big                |
| laohedb            |
| laohedb_utf8       |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

通过日志文件节点恢复数据

root@localhost ~]# find / -name mysqlbinlog
/www/server/mysql/bin/mysqlbinlog
[root@localhost ~]# cd /www/server/mysql
[root@localhost mysql]# ./bin/mysqlbinlog --start-position=154 --stop-position=1738 /www/server/data/mysql-bin.000018 | mysql -uroot -p
Enter password: 

//根据时间恢复数据

mysqlbinlog --start-datetime='2020-09-27 22:22:22' --stop-datetime='2020-09-27 22:30:00' /www/server/data/mysql-bin.000036 | mysql -uroot -p 

//直接执行binlog日志

mysqlbinlog /www/server/data/mysql-bin.000036 | mysql -uroot -p