-
- 数据库的数据存储文件
-
- 慢查询日志
-
- 错误日志与二进制文件
-
- 二进制文件的基本操作
-
- 使用二进制文件恢复数据
-
- 中继日志（了解）
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.删除二进制文件
二进制日志中记录着大量的信息，如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。删除二进 制日志的方法很多，下面介绍几种删除二进制日志的方法。
- 使用 RESET MASTER 语句可以删除的所有二进制日志，该语句的形式如下：
RESET MASTER;
登录 MySQL 数据库后，可以执行该语句来删除所有二进制日志。删除所有二进制日志后，MySQL 将会重 新创建新的二进制日志，新二进制日志的编号从 000001 开始。
- 根据编号删除二进制日志
每个二进制日志文件后面有一个 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 的二进制日志将被删除。
- 根据创建时间删除二进制日志
使用 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 之前创建的所有二进制日志将被删除。
使用二进制文件恢复数据
- 创建一个用于测试的库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