本文最后更新于 269 天前，其中的信息可能已经过时，如有错误请发送邮件到wuxianglongblog@163.com
一.MySQL错误日志管理
1.MySQL错误日志概述
MySQL错误日志记录内容:
记录mysql数据库实例启动以来，所有的状态，警告，错误等信息。
MySQL错误日志作用:
帮助数据库管理员来定位数据库实例的在运行过程中出现的一些列问题，比如数据库启动失败时，我们首先看的就应该是错误日志。
推荐阅读:
https://dev.mysql.com/doc/refman/8.0/en/server-logs.html2.错误日志的配置方法
MySQL错误日志功能默认是开启状态，默认的存放位置在数据目录("datadir")下，默认的文件名称为:"hostname.err"。
mysql> SELECT @@datadir;
+-------------------------------+
| @@datadir |
+-------------------------------+
| /oldboyedu/data/mysql23307/ |
+-------------------------------+
1 row in set (0.00 sec)
mysql>
如下所示，我们可以指定MySQL错误日志的文件名称及存放位置(需要重启MySQL数据库实例哟~):
[root@docker201.oldboyedu.com ~]# vim /oldboyedu/softwares/mysql23307/my.cnf
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# tail -2 /oldboyedu/softwares/mysql23307/my.cnf
# 指定错误日志存放位置(指定文件名称时，请确定其绝对路径必须存在哟~)，别忘记指定的路径一定要有写入权限哟~
log_error=/oldboyedu/logs/mysql23307/errlog/oldboyedu-mysqld-err.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# install -d /oldboyedu/logs/mysql23307/errlog -o mysql -g mysql
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -d /oldboyedu/logs/mysql23307/errlog/
drwxr-xr-x 2 mysql mysql 6 2月 3 22:23 /oldboyedu/logs/mysql23307/errlog/
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/errlog/
总用量 0
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# systemctl restart mysqld23307
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/errlog/
总用量 4
-rw-r----- 1 mysql mysql 3563 2月 3 22:26 oldboyedu-mysqld-err.log
[root@docker201.oldboyedu.com ~]#
3.怎么看错误日志
查看日志的工具有很多种，比如vim,tail,more,less等等，选择你一个你习惯使用的即可。
如下所示，在查看日志时，我们没有必要逐行去看，我们生产环境中通常会去关注"ERROR"的行:
[root@docker201.oldboyedu.com ~]# tail -100f /oldboyedu/data/mysql23307/oldboyedu-mysqld-err.log
......
2021-02-03T13:16:48.457648Z 0 [ERROR] unknown variable 'INNODB_BUFFER_POOL_SIZE=1073741824'
2021-02-03T13:16:48.457670Z 0 [ERROR] Aborting
......
分析问题:
上面的案例通过"unknown variable 'INNODB_BUFFER_POOL_SIZE=1073741824'"可得知MySQL实例启动时未能识别"INNODB_BUFFER_POOL_SIZE"变量。
解决方案:
根据提示我们将配置文件的变量改为小写(即"innodb_buffer_pool_size=1073741824")后，问题得到解决。
二.MySQL通用日志管理
通用日志的作用:
用于临时调试,可以做简单的日志审计功能.
常用参数:
[mysqld]
...
general_log=ON :
如果general_log_file的父目录不存在或无权限访问时,尽管将改参数设置为ON也无效!
general_log_file:
指定通用日志的存储路径
温馨提示:
开启通用日志功能，生产环境建议关闭，可能会影响性能，如果调试可以临时打开,不建议将它审计三.MySQL二进制日志管理
1.MySQL二进制日志概述
MySQL二进制日志记录内容:
记录mysql数据变化性质的日志(比如会记录"DDL","DCL","DML"等操作，并不会记录DQL，因为"SELECT"操作并不会改变数据)，属于逻辑层性质日志，并不会记录数据页的变化。
MySQL二进制日志作用:
主要用于数据恢复和主从复制中的应用.
2.二进制日志的配置方法
在MySQL 5.7版本之前， 默认是没有开启二进制日志功能的，生产环境中，我们建议开启二进制日志功能，尤其是对于数据非常重要的场景中，开启二进制日志可以帮助我们用于数据恢复及备份等功能。
启动二进制日志可能会用到以下几个常用几个参数:
server_id:
主机编号，主从复制中必须开启此参数，MySQL 5.7以后开启binlog也必须配置该参数。
log_bin:
指定二进制日志的前缀名称，通常需要写一个绝对路径。
sync_binlog:
binlog日志刷写策略，我们通常设置为"1"，表示每次事物提交理都刷写"binlog buffer"到磁盘，其目的是为了保证数据的持久性与安全性。
如果我们将该值设置为"0"，则每次提交事务并不会立即刷写已提交的事务到磁盘，而是交由操作系统来完成事务的刷写，尽管性能上是有所提升的，但这同样也带来了高风险，即当操作意外断电崩溃，将意味着数据MySQL部分已提交的事务数据将丢失。
binlog_format:
binlog的记录格式，我们通常设置为"row"模式，后面会有详细的章节介绍该参数。
expire_logs_days:
若不设置，默认值为0，表示永不删除二进制日志(binlog)。生产环境建议设置最少2个全量备份周期(如果你是每周做一次全量备份，那么你的周期就是7天)+1，目的是最近一个全量备份若损坏则可以考虑使用上一个的备份。
如下所示，启用MySQL二进制日志功能:
[root@docker201.oldboyedu.com ~]# vim /oldboyedu/softwares/mysql23307/my.cnf
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# tail -4 /oldboyedu/softwares/mysql23307/my.cnf
server_id=7
log_bin=/oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary
sync_binlog=1
binlog_format=row
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# install -d /oldboyedu/logs/mysql23307/binlog -o mysql -g mysql
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -d /oldboyedu/logs/mysql23307/binlog/
drwxr-xr-x 2 mysql mysql 85 2月 3 22:26 /oldboyedu/logs/mysql23307/binlog/
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/
总用量 0
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# systemctl restart mysqld23307
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/
总用量 8
-rw-r----- 1 mysql mysql 154 2月 3 22:26 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 69 2月 3 22:26 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
温馨提示:
(1)生产环境中建议将数据和日志分开存储在不同的存储设备上，这样就算数据盘损坏，我们也可以基于二进制日志进行数据恢复哟~
(2)所谓的"双一标准"指的就是"innodb_flush_log_at_trx_commit=1"和"sync_binlog=1":
"innodb_flush_log_at_trx_commit=1":
每次事务提交，必然将内存中"redo log buffer"落地到磁盘。
"sync_binlog=1":
每次事务提交，必然保证"binlog buffer"落地到磁盘。
3.二进制日志(binlog)记录内容详解
binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。记录SQL语句种类
DDL:
原封不动的记录当前DDL的statement语句方式。
DCL:
原封不动的记录当前DCL的statement语句方式。
DML:
只记录已提交的DML事务。
DML三种记录方式
statement:
这是MySQL 5.6默认的记录方式，即Statement Based Replication(简称"SBR")，这种记录方式会原封不动的记录当前的DML。
优点：
可读性较高，日志量较少，因为它是原封不动的记录用户执行的SQL语句。
缺点:
这种记录方式并不严谨('SELECT NOW()')，这也是为什么MySQL 5.7开始就不在使用该模式了。
ROW:
这是MySQL 5.7默认的记录方式，即Row Based Replication(简称"RBR")，这种记录方式会记录数据行的变化，这种方式可读性极差，需要借助工具进行分析，但记录的方式也是最严谨的，生产环境中我推荐大家使用这种记录方式。
优点：
弥补了statement模式不够严谨的缺点。
缺点:
可读性极差，日志量较大，因为他会记录每一行的数据变化。
mixed:
这是混合模式，即Mixed Based Replication(简称"MBR")，这种记录方式会将上面的statement和ROW这两种模式混合使用，生产环境中很少使用。
温馨提示:
请思考以下两种语句，应该选择哪种记录方式呢?
(1)UPDATA oldboyedu.t1 SET age=120 WHERE id > 10000; # 假设该表有10亿行数据。
答: 只根据这一条UPDATA语句而言，我推荐大家使用"statement"，因为这样只会在日志中原封不动将该SQL记录，占用很少的空间，但如果选用ROW模式的话，则意味着要记录9亿多行数据的变化！
(2)INSERT INTO t1 (id,name,age,time_of_enrollment) VALUES(100010,'Jason Yin',20,NOW());
答: 只根据这一条INSERT语句而言，我推荐大家使用"ROW"，因为"NOW()"函数记录的在插入时的时间，这样记录的时间更准确，但如果选用statement的话，则意味着记录的数据并不准确！
综上所述，生产环境中我们使用的"ROW"更为准确，其性能可能相比statement较差，但为了数据准确性，我们通常愿意牺牲一些性能。
二进制日志最小记录单元-事件(event)
事件(event)简介：
众所周知，事件(event)是二进制最小记录单元。对于DDL和DCL而言，一个语句就是一个事件(event)，而对于DML语句而言，只记录已提交的事务。
事件(event)的组成:
(1)事件的开始标识(at position，例如: at 219);
(2)事件内容(记录的DDL,DCL和已提交的DML语句);
(3)事件的结束标识(end_log_pos position，例如: End_log_pos 343);
温馨提示:
位置编号(position)是记录某个事件在binlog中的相对位置编号，为了方便我们截取事件。
4.在mysql交互式命令行中查看二进制日志(binlog)
4.1查看二进制日志(binlog)的启用状态
如下所示，通过"log_bin"参数可以查看二进制日志(binlog)功能是否启用，如果为"1"表示启用状态，如果为0表示禁用状态。
mysql> SELECT @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql>
如下所示，通过"log_bin_basename"参数可以查看二进制日志(binlog)的前缀名称。
mysql> SELECT @@log_bin_basename;
+---------------------------------------------------------------+
| @@log_bin_basename |
+---------------------------------------------------------------+
| /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
4.2二进制日志(binlog)文件查看
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary*
-rw-r----- 1 mysql mysql 154 2月 3 22:26 /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 69 2月 3 22:26 /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# file /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000001
/oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000001: MySQL replication log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# file /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.index
/oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.index: ASCII text
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.index
/oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000001
[root@docker201.oldboyedu.com ~]#
4.3二进制日志(binlog)内置查看命令
如下所示，通过"SHOW BINARY LOGS"命令可以查看目前有几个日志文件:
mysql> SHOW BINARY LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000001 | 154 |
+----------------------------------+-----------+
1 row in set (0.00 sec)
mysql>
如下所示，通过"SHOW MASTER STATUS"命令可以查看当前在用的二进制日志(binlog)文件:
mysql> SHOW MASTER STATUS;
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000001 | 154 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
如下所示，通过"SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000001';"命令可以查看二进制日志(binlog)事件:
mysql> SHOW MASTER STATUS;
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000001 | 154 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000001'; # 注意哈，这里的'oldboyedu-mysqld-binary.000001'文件名称我们可以通过"SHOW MASTER STATUS"或者"SHOW MASTER STATUS"命令来获取哟~
+----------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| oldboyedu-mysqld-binary.000001 | 4 | Format_desc | 7 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| oldboyedu-mysqld-binary.000001 | 123 | Previous_gtids | 7 | 154 | |
+----------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql>
4.4执行DDL，DCL语句来观察二进制日志(binlog)的记录事件(Event)
执行DDL语句观察二进制日志(binlog)的记录事件(Event):
mysql> SHOW MASTER STATUS;
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000001 | 154 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000001'; # 注意哈，下面两行内容是每一个二进制日志文件都会有的头文件信息哟~
+----------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| oldboyedu-mysqld-binary.000001 | 4 | Format_desc | 7 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| oldboyedu-mysqld-binary.000001 | 123 | Previous_gtids | 7 | 154 | |
+----------------------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> CREATE DATABASE zabbix DEFAULT CHARSET utf8mb4; # 为了看到实验效果，我此处执行一个DDL语句来创建一个数据库。
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000001'; # 很明显DDL语句被记录下来了，如下所示，不难发现多了2行记录，其中Pos=154的行我们可以忽略，这是MySQL内部维护的信息，我们更关注的是Pos=219的记录。
+----------------------------------+-----+----------------+-----------+-------------+------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------------+-----+----------------+-----------+-------------+------------------------------------------------+
| oldboyedu-mysqld-binary.000001 | 4 | Format_desc | 7 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| oldboyedu-mysqld-binary.000001 | 123 | Previous_gtids | 7 | 154 | |
| oldboyedu-mysqld-binary.000001 | 154 | Anonymous_Gtid | 7 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000001 | 219 | Query | 7 | 343 | CREATE DATABASE zabbix DEFAULT CHARSET utf8mb4 |
+----------------------------------+-----+----------------+-----------+-------------+------------------------------------------------+
4 rows in set (0.00 sec)
mysql>
执行DCL语句观察二进制日志(binlog)的记录事件(Event):
mysql> CREATE USER jason@'172.200.1.%' IDENTIFIED BY 'oldboyedu'; # 创建一个MySQL用户！
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> GRANT ALL ON zabbix.* TO jason@'172.200.1.%'; # 给创建的用户授权！
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000001'; # 很明显DCL语句也被记录下来了，但同时也记录了Event_type=Anonymous_Gtid，关于Gtid我们后面会有专门的章节来讲解，这里请先忽略！
+----------------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
|+----------------------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+
| oldboyedu-mysqld-binary.000001 | 4 | Format_desc | 7 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| oldboyedu-mysqld-binary.000001 | 123 | Previous_gtids | 7 | 154 | |
| oldboyedu-mysqld-binary.000001 | 154 | Anonymous_Gtid | 7 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000001 | 219 | Query | 7 | 343 | CREATE DATABASE zabbix DEFAULT CHARSET utf8mb4 |
| oldboyedu-mysqld-binary.000001 | 343 | Anonymous_Gtid | 7 | 408 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000001 | 408 | Query | 7 | 599 | CREATE USER 'jason'@'172.200.1.%' IDENTIFIED WITH 'mysql_native_password' AS '*BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7' |
| oldboyedu-mysqld-binary.000001 | 599 | Anonymous_Gtid | 7 | 664 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000001 | 664 | Query | 7 | 817 | GRANT ALL PRIVILEGES ON `zabbix`.* TO 'jason'@'172.200.1.%' |
+----------------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql>
执行DML语句观察二进制日志(binlog)的记录事件(Event):
mysql> SELECT COUNT(*) FROM oldboyedu.student;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELETE FROM oldboyedu.student WHERE id<5;
Query OK, 4 rows affected (0.00 sec)
mysql>
mysql> COMMIT; # 注意哈，一定要提交事件哟~如果不提交，我们是无法在二进制日志(binlog)中查看相应的事件！
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000001'; # 很明显DML语句也被记录下来了，注意观察"Event_type"和"Info"字段。尽管看不到执行的SQL，但可以判断这是一个删除事件(Event)。具体操作我们后续可查看日志!
+----------------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
|+----------------------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+
| oldboyedu-mysqld-binary.000001 | 4 | Format_desc | 7 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| oldboyedu-mysqld-binary.000001 | 123 | Previous_gtids | 7 | 154 | |
| oldboyedu-mysqld-binary.000001 | 154 | Anonymous_Gtid | 7 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000001 | 219 | Query | 7 | 343 | CREATE DATABASE zabbix DEFAULT CHARSET utf8mb4 |
| oldboyedu-mysqld-binary.000001 | 343 | Anonymous_Gtid | 7 | 408 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000001 | 408 | Query | 7 | 599 | CREATE USER 'jason'@'172.200.1.%' IDENTIFIED WITH 'mysql_native_password' AS '*BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7' |
| oldboyedu-mysqld-binary.000001 | 599 | Anonymous_Gtid | 7 | 664 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000001 | 664 | Query | 7 | 817 | GRANT ALL PRIVILEGES ON `zabbix`.* TO 'jason'@'172.200.1.%' |
...(此处省略几百行，因为我执行了一条"DELETE FROM oldboyedu.student WHERE id>10000;"语句，这张表最开始是10万行)
| oldboyedu-mysqld-binary.000001 | 5786216 | Anonymous_Gtid | 7 | 5786281 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000001 | 5786281 | Query | 7 | 5786349 | BEGIN |
| oldboyedu-mysqld-binary.000001 | 5786349 | Table_map | 7 | 5786422 | table_id: 109 (oldboyedu.student) |
| oldboyedu-mysqld-binary.000001 | 5786422 | Delete_rows | 7 | 5786681 | table_id: 109 flags: STMT_END_F |
| oldboyedu-mysqld-binary.000001 | 5786681 | Query | 7 | 5786750 | COMMIT |
+----------------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
726 rows in set (0.00 sec)
mysql>
4.5非交互式命令行窗口查看二进制日志(binlog)的所有删除记录
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock -e "SHOW MASTER LOGS;"
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000001 | 5786773 |
| oldboyedu-mysqld-binary.000002 | 154 |
+----------------------------------+-----------+
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock -e "SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000001'" | grep -i delete | head
oldboyedu-mysqld-binary.000001 1023 Delete_rows 7 9186 table_id: 109
oldboyedu-mysqld-binary.000001 9186 Delete_rows 7 17349 table_id: 109
oldboyedu-mysqld-binary.000001 17349 Delete_rows 7 25512 table_id: 109
oldboyedu-mysqld-binary.000001 25512 Delete_rows 7 33675 table_id: 109
oldboyedu-mysqld-binary.000001 33675 Delete_rows 7 41838 table_id: 109
oldboyedu-mysqld-binary.000001 41838 Delete_rows 7 50001 table_id: 109
oldboyedu-mysqld-binary.000001 50001 Delete_rows 7 58164 table_id: 109
oldboyedu-mysqld-binary.000001 58164 Delete_rows 7 66327 table_id: 109
oldboyedu-mysqld-binary.000001 66327 Delete_rows 7 74490 table_id: 109
oldboyedu-mysqld-binary.000001 74490 Delete_rows 7 82653 table_id: 109
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock -e "SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000001'" | grep -i delete | wc -l
710
[root@docker201.oldboyedu.com ~]#
4.6滚动二进制日志
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.02 sec)
mysql>5.在Linux的命令行中查看二进制日志(binlog)
通过mysqlbinlog工具将二进制日志(binlog)格式转换为文本格式
如下所示，我们可以通过mysqlbinlog工具将二进制日志(binlog)格式转换为文本格式:
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/
总用量 5660
-rw-r----- 1 mysql mysql 5786773 2月 4 08:16 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 154 2月 4 08:16 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 138 2月 4 08:16 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysqlbinlog /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000001 > /tmp/mysql.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /tmp/mysql.log
-rw-r--r-- 1 root root 7895883 2月 4 08:46 /tmp/mysql.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /tmp/mysql.log
-rw-r--r-- 1 root root 7.6M 2月 4 08:46 /tmp/mysql.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# file /tmp/mysql.log
/tmp/mysql.log: ASCII text
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# file /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000001
/oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000001: MySQL replication log
[root@docker201.oldboyedu.com ~]#
如下所示，我们可以通过"mysqlbinlog --help"命令来查看mysqlbinlog工具的帮助信息:
[root@docker201.oldboyedu.com ~]# mysqlbinlog --help
mysqlbinlog Ver 3.4 for linux-glibc2.12 at x86_64
Copyright (c) 2000, 2020, 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.
Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client.
Usage: mysqlbinlog [options] log-files
-?, --help Display this help and exit.
--base64-output=name
Determine when the output statements should be
base64-encoded BINLOG statements: 'never' disables it and
works only for binlogs without row-based events;
'decode-rows' decodes row events into commented
pseudo-SQL statements if the --verbose option is also
given; 'auto' prints base64 only when necessary (i.e.,
for row-based events and format description events). If
no --base64-output[=name] option is given at all, the
default is 'auto'.
--bind-address=name IP address to bind to.
--character-sets-dir=name
Directory for character set files.
-d, --database=name List entries for just this database (local log only).
--rewrite-db=name Rewrite the row event to point so that it can be applied
to a new database
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check This is a non-debug version. Catch this and exit.
--debug-info This is a non-debug version. Catch this and exit.
--default-auth=name Default authentication client-side plugin to use.
-D, --disable-log-bin
Disable binary log. This is useful, if you enabled
--to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.
-F, --force-if-open Force if binlog was not closed properly.
(Defaults to on; use --skip-force-if-open to disable.)
-f, --force-read Force reading unknown binlog events.
-H, --hexdump Augment output with hexadecimal and ASCII event dump.
-h, --host=name Get the binlog from server.
-i, --idempotent Notify the server to use idempotent mode before applying
Row Events
-l, --local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-o, --offset=# Skip the first N entries.
-p, --password[=name]
Password to connect to remote server.
--plugin-dir=name Directory for client-side plugins.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-R, --read-from-remote-server
Read binary logs from a MySQL server. This is an alias
for read-from-remote-master=BINLOG-DUMP-NON-GTIDS.
--read-from-remote-master=name
Read binary logs from a MySQL server through the
COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by
setting the option to either BINLOG-DUMP-NON-GTIDS or
BINLOG-DUMP-GTIDS, respectively. If
--read-from-remote-master=BINLOG-DUMP-GTIDS is combined
with --exclude-gtids, transactions can be filtered out on
the master avoiding unnecessary network traffic.
--raw Requires -R. Output raw binlog data instead of SQL
statements, output is to log files.
-r, --result-file=name
Direct output to a given file. With --raw this is a
prefix for the file names.
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol. Deprecated. Always TRUE
--server-id=# Extract only binlog entries created by the server having
the given id.
--server-id-bits=# Set number of significant bits in server-id
--set-charset=name Add 'SET NAMES character_set' to the output.
-s, --short-form Just show regular queries: no extra info and no row-based
events. This is for testing only, and should not be used
in production systems. If you want to suppress
base64-output, consider using --base64-output=never
instead.
-S, --socket=name The socket file to use for connection.
--ssl-mode=name SSL connection mode.
--ssl Deprecated. Use --ssl-mode instead.
(Defaults to on; use --skip-ssl to disable.)
--ssl-verify-server-cert
Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
--ssl-ca=name CA file in PEM format.
--ssl-capath=name CA directory.
--ssl-cert=name X509 cert in PEM format.
--ssl-cipher=name SSL cipher to use.
--ssl-key=name X509 key in PEM format.
--ssl-crl=name Certificate revocation list.
--ssl-crlpath=name Certificate revocation list path.
--tls-version=name TLS version to use, permitted values are: TLSv1, TLSv1.1,
TLSv1.2
--server-public-key-path=name
File path to the server public RSA key in PEM format.
--get-server-public-key
Get server public key
--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
-j, --start-position=#
Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-never Wait for more data from the server instead of stopping at
the end of the last log. Implicitly sets --to-last-log
but instead of stopping at the end of the last log it
continues to wait till the server disconnects.
--stop-never-slave-server-id=#
The slave server_id used for --read-from-remote-server
--stop-never. This option cannot be used together with
connection-server-id.
--connection-server-id=#
The slave server_id used for --read-from-remote-server.
This option cannot be used together with
stop-never-slave-server-id.
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.
-t, --to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, --user=name Connect to the remote server as username.
-v, --verbose Reconstruct pseudo-SQL statements out of row events. -v
-v adds comments on column data types.
-V, --version Print version and exit.
--open-files-limit=#
Used to reserve file descriptors for use by this program.
-c, --verify-binlog-checksum
Verify checksum binlog events.
--binlog-row-event-max-size=#
The maximum size of a row-based binary log event in
bytes. Rows will be grouped into events smaller than this
size if possible. This value must be a multiple of 256.
--skip-gtids Do not preserve Global Transaction Identifiers; instead
make the server execute the transactions as if they were
new.
--include-gtids=name
Print events whose Global Transaction Identifiers were
provided.
--exclude-gtids=name
Print all events but those whose Global Transaction
Identifiers were provided.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
base64-output (No default value)
bind-address (No default value)
character-sets-dir (No default value)
database (No default value)
rewrite-db (No default value)
default-auth (No default value)
disable-log-bin FALSE
force-if-open TRUE
force-read FALSE
hexdump FALSE
host (No default value)
idempotent FALSE
local-load (No default value)
offset 0
plugin-dir (No default value)
port 0
read-from-remote-server FALSE
read-from-remote-master (No default value)
raw FALSE
result-file (No default value)
secure-auth TRUE
server-id 0
server-id-bits 32
set-charset (No default value)
short-form FALSE
socket (No default value)
ssl TRUE
ssl-verify-server-cert FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
tls-version (No default value)
server-public-key-path (No default value)
get-server-public-key FALSE
start-datetime (No default value)
start-position 4
stop-datetime (No default value)
stop-never FALSE
stop-never-slave-server-id -1
connection-server-id -1
stop-position 18446744073709551615
to-last-log FALSE
user (No default value)
open-files-limit 64
verify-binlog-checksum FALSE
binlog-row-event-max-size 4294967040
skip-gtids FALSE
include-gtids (No default value)
exclude-gtids (No default value)
[root@docker201.oldboyedu.com ~]#
查看DDL语句对应的二进制日志(binlog)
查看DDL语句如下所示:
[root@docker201.oldboyedu.com ~]# head -35 /tmp/mysql.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210203 22:26:02 server id 7 end_log_pos 123 CRC32 0xe2d0bf51 Start: binlog v 4, server v 5.7.31-log created 210203 22:26:02 at startup
ROLLBACK/*!*/;
BINLOG '
erIaYA8HAAAAdwAAAHsAAAAAAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB6shpgEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AVG/0OI=
'/*!*/;
# at 123
#210203 22:26:02 server id 7 end_log_pos 154 CRC32 0x0350e93a Previous-GTIDs
# [empty]
# at 154
#210204 6:19:39 server id 7 end_log_pos 219 CRC32 0xc17a095e Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210204 6:19:39 server id 7 end_log_pos 343 CRC32 0xe47cc745 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1612390779/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DATABASE zabbix DEFAULT CHARSET utf8mb4
/*!*/;
# at 343
#210204 6:26:49 server id 7 end_log_pos 408 CRC32 0x0f38c8a2 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 408
#210204 6:26:49 server id 7 end_log_pos 599 CRC32 0x5e28c821 Query thread_id=4 exec_time=0 error_code=0
[root@docker201.oldboyedu.com ~]#
温馨提示:
(1)"/*...*/"和"#"开头的行都是注释内容;
(2)SET命令我们通常情况下不会特别去关注他，这是MySQL程序所关心的;
(3)接下来我们将上面的219-343的事务日志关心的内容分析如下:
# at 219
#210204 6:19:39 server id 7 end_log_pos 343
CREATE DATABASE zabbix DEFAULT CHARSET utf8mb4
# at 343查看DML语句对应的二进制日志(binlog)
查看DML语句如下所示:
[root@docker201.oldboyedu.com ~]# tail -35 /tmp/mysql.log
# at 5786216
#210204 6:41:32 server id 7 end_log_pos 5786281 CRC32 0xe06ff9bc Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 5786281
#210204 6:41:32 server id 7 end_log_pos 5786349 CRC32 0xa528f8da Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1612392092/*!*/;
BEGIN
/*!*/;
# at 5786349
#210204 6:41:32 server id 7 end_log_pos 5786422 CRC32 0xc6b022cd Table_map: `oldboyedu`.`student` mapped to number 109
# at 5786422
#210204 6:41:32 server id 7 end_log_pos 5786681 CRC32 0xc14ddee5 Delete_rows: table id 109 flags: STMT_END_F
BINLOG '
nCYbYBMHAAAASQAAADZLWAAAAG0AAAAAAAEAC3lpbnpoZW5namllAAdzdHVkZW50AAgDDwH+Eg8I
Dwl4APcBAPwD/AOczSKwxg==
nCYbYCAHAAAAAwEAADlMWAAAAG0AAAAAAAEAAgAI/4ABAAAADHlpbnpoZW5namllMQEBmais428V
AHlpbnpoZW5namllLWFkZHJlc3MtMQEAAAAAAAAAgAIAAAAMeWluemhlbmdqaWUyAgGZqKzjbxUA
eWluemhlbmdqaWUtYWRkcmVzcy0yAgAAAAAAAACAAwAAAAx5aW56aGVuZ2ppZTMDAZmorONvFQB5
aW56aGVuZ2ppZS1hZGRyZXNzLTMDAAAAAAAAAIAEAAAADHlpbnpoZW5namllNAQBmais428VAHlp
bnpoZW5namllLWFkZHJlc3MtNAQAAAAAAAAA5d5NwQ==
'/*!*/;
# at 5786681
#210204 6:41:32 server id 7 end_log_pos 5786750 CRC32 0xaab66d3c Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1612392092/*!*/;
COMMIT
/*!*/;
# at 5786750
#210204 8:16:20 server id 7 end_log_pos 5786773 CRC32 0x6c6e19e0 Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@docker201.oldboyedu.com ~]#
温馨提示:
(1)"/*...*/"和"#"开头的行都是注释内容;
(2)SET命令我们通常情况下不会特别去关注他，这是MySQL程序所关心的;
(3)接下来我们将上面的5786281-5786750的事务日志关心的内容分析如下:(可能你会发现下面有基于BASE64编码的内容，直接去看的确优点看不懂!但我们可以借助mysqlbinlog工具的"--base64-output=decode-rows -vvv"参数查看更详细的内容)
# at 5786281
#210204 6:41:32 server id 7 end_log_pos 5786349
BEGIN
# at 5786349
#210204 6:41:32 server id 7 end_log_pos 5786422
# at 5786422
#210204 6:41:32 server id 7 end_log_pos 5786681
BINLOG '
nCYbYBMHAAAASQAAADZLWAAAAG0AAAAAAAEAC3lpbnpoZW5namllAAdzdHVkZW50AAgDDwH+Eg8I
Dwl4APcBAPwD/AOczSKwxg==
nCYbYCAHAAAAAwEAADlMWAAAAG0AAAAAAAEAAgAI/4ABAAAADHlpbnpoZW5namllMQEBmais428V
AHlpbnpoZW5namllLWFkZHJlc3MtMQEAAAAAAAAAgAIAAAAMeWluemhlbmdqaWUyAgGZqKzjbxUA
eWluemhlbmdqaWUtYWRkcmVzcy0yAgAAAAAAAACAAwAAAAx5aW56aGVuZ2ppZTMDAZmorONvFQB5
aW56aGVuZ2ppZS1hZGRyZXNzLTMDAAAAAAAAAIAEAAAADHlpbnpoZW5namllNAQBmais428VAHlp
bnpoZW5namllLWFkZHJlc3MtNAQAAAAAAAAA5d5NwQ==
# at 5786681
#210204 6:41:32 server id 7 end_log_pos 5786750
COMMIT
# at 5786750
通过mysqlbinlog工具将二进制日志(binlog)格式转换为详细的文本格式(这意味着文件的会占用更大的空间)
如下所示，我们可以借助mysqlbinlog工具的"--base64-output=decode-rows -vvv"参数查看更详细的内容，但这也意味着会占用更多的磁盘空间:
[root@docker201.oldboyedu.com ~]# mysqlbinlog --base64-output=decode-rows -vvv /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000001 > /tmp/mysql2.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# file /tmp/mysql2.log
/tmp/mysql2.log: ASCII text
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /tmp/mysql.log
-rw-r--r-- 1 root root 7895883 2月 4 08:46 /tmp/mysql.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /tmp/mysql.log # 这是不使用"--base64-output=decode-rows -vvv"参数生成的文件，文件大小较小！
-rw-r--r-- 1 root root 7.6M 2月 4 08:46 /tmp/mysql.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /tmp/mysql2.log
-rw-r--r-- 1 root root 53635879 2月 4 09:06 /tmp/mysql2.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /tmp/mysql2.log # 当我们使用"--base64-output=decode-rows -vvv"参数生成的文件，可以很明显的发现文件内容较大！
-rw-r--r-- 1 root root 52M 2月 4 09:06 /tmp/mysql2.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# tail -65 /tmp/mysql2.log
# at 5786216
#210204 6:41:32 server id 7 end_log_pos 5786281 CRC32 0xe06ff9bc Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 5786281
#210204 6:41:32 server id 7 end_log_pos 5786349 CRC32 0xa528f8da Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1612392092/*!*/;
BEGIN
/*!*/;
# at 5786349
#210204 6:41:32 server id 7 end_log_pos 5786422 CRC32 0xc6b022cd Table_map: `oldboyedu`.`student` mapped to number 109
# at 5786422
#210204 6:41:32 server id 7 end_log_pos 5786681 CRC32 0xc14ddee5 Delete_rows: table id 109 flags: STMT_END_F
### DELETE FROM `oldboyedu`.`student`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='oldboyedu1' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### @3=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
### @5='2021-01-22 14:13:47' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @6='oldboyedu-address-1' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @7=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
### DELETE FROM `oldboyedu`.`student`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='oldboyedu2' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### @3=2 /* TINYINT meta=0 nullable=1 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
### @5='2021-01-22 14:13:47' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @6='oldboyedu-address-2' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @7=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
### DELETE FROM `oldboyedu`.`student`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='oldboyedu3' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### @3=3 /* TINYINT meta=0 nullable=1 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
### @5='2021-01-22 14:13:47' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @6='oldboyedu-address-3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @7=3 /* LONGINT meta=0 nullable=0 is_null=0 */
### @8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
### DELETE FROM `oldboyedu`.`student`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='oldboyedu4' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### @3=4 /* TINYINT meta=0 nullable=1 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
### @5='2021-01-22 14:13:47' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @6='oldboyedu-address-4' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @7=4 /* LONGINT meta=0 nullable=0 is_null=0 */
### @8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
# at 5786681
#210204 6:41:32 server id 7 end_log_pos 5786750 CRC32 0xaab66d3c Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1612392092/*!*/;
COMMIT
/*!*/;
# at 5786750
#210204 8:16:20 server id 7 end_log_pos 5786773 CRC32 0x6c6e19e0 Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@docker201.oldboyedu.com ~]#
温馨提示:
(1)"/*...*/"和"#"开头的行都是注释内容;
(2)SET命令我们通常情况下不会特别去关注他，这是MySQL程序所关心的;
(3)接下来我们将上面的5786281-5786750的事务日志关心的内容分析如下:(可能你会发现下面有基于BASE64编码的内容，直接去看的确优点看不懂!但我们可以借助mysqlbinlog工具的"--base64-output=decode-rows -vvv"参数查看更详细的内容)
# at 5786281
#210204 6:41:32 server id 7 end_log_pos 5786349
BEGIN
# at 5786349
#210204 6:41:32 server id 7 end_log_pos 5786422
# at 5786422
#210204 6:41:32 server id 7 end_log_pos 5786681
### DELETE FROM `oldboyedu`.`student`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='oldboyedu1' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### @3=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
### @5='2021-01-22 14:13:47' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @6='oldboyedu-address-1' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @7=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
### DELETE FROM `oldboyedu`.`student`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='oldboyedu2' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### @3=2 /* TINYINT meta=0 nullable=1 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
### @5='2021-01-22 14:13:47' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @6='oldboyedu-address-2' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @7=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
### DELETE FROM `oldboyedu`.`student`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='oldboyedu3' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### @3=3 /* TINYINT meta=0 nullable=1 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
### @5='2021-01-22 14:13:47' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @6='oldboyedu-address-3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @7=3 /* LONGINT meta=0 nullable=0 is_null=0 */
### @8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
### DELETE FROM `oldboyedu`.`student`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='oldboyedu4' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### @3=4 /* TINYINT meta=0 nullable=1 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
### @5='2021-01-22 14:13:47' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @6='oldboyedu-address-4' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @7=4 /* LONGINT meta=0 nullable=0 is_null=0 */
### @8=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
# at 5786681
#210204 6:41:32 server id 7 end_log_pos 5786750 CRC32 0xaab66d3c Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1612392092/*!*/;
COMMIT
/*!*/;
# at 5786750
6.基于二进制日志(binlog)恢复数据实战案例
滚动一个新的二进制日志(binlog)文件
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SHOW MASTER LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000001 | 5786773 |
| oldboyedu-mysqld-binary.000002 | 217 |
| oldboyedu-mysqld-binary.000003 | 8921 |
+----------------------------------+-----------+
3 rows in set (0.00 sec)
mysql>
mysql> FLUSH LOGS; # 立即滚动一个新的二进制日志(binlog)文件
Query OK, 0 rows affected (0.28 sec)
mysql>
mysql> SHOW MASTER LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000001 | 5786773 |
| oldboyedu-mysqld-binary.000002 | 217 |
| oldboyedu-mysqld-binary.000003 | 8984 |
| oldboyedu-mysqld-binary.000004 | 154 |
+----------------------------------+-----------+
4 rows in set (0.00 sec)
mysql>
mysql> SHOW MASTER STATUS;
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000004 | 154 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
模拟数据环境(注意观察新滚动的二进制日志"Position"字段大小变化)
[root@docker201.oldboyedu.com ~]# vim user.sql
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat user.sql
# 创建数据库:
CREATE DATABASE IF NOT EXISTS cmdb DEFAULT CHARSET utf8mb4;
# 创建表:
CREATE TABLE IF NOT EXISTS cmdb.user (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '用户编号',
username VARCHAR(30) NOT NULL UNIQUE KEY COMMENT '用户名',
password VARCHAR(50) NOT NULL COMMENT '密码',
is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '0表示未删除,1表示已经删除'
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
# 插入数据:
INSERT INTO cmdb.user
(username,password)
VALUES
('jason',MD5('123456')),
('oldboyedu',MD5(123)),
('jay',MD5(123)),
('jenny',MD5(123)),
('danny',MD5(123)),
('liming',MD5(123)),
('alic',MD5(123)),
('tom',MD5(666));
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SHOW MASTER STATUS; # 在我们没有执行咱们自定义的"user.sql"之前，注意观察"Position=154"
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000004 | 154 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> SOURCE user.sql;
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.29 sec)
Query OK, 8 rows affected (0.02 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql>
mysql> SHOW MASTER STATUS; # 在我们执行咱们自定义的"user.sql"之后，注意观察"Position=1448"，说明有新的日志生成！
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000004 | 1448 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM cmdb.user;
+----+-------------+----------------------------------+------------+
| id | username | password | is_deleted |
+----+-------------+----------------------------------+------------+
| 1 | jason | e10adc3949ba59abbe56e057f20f883e | 0 |
| 2 | oldboyedu | 202cb962ac59075b964b07152d234b70 | 0 |
| 3 | jay | 202cb962ac59075b964b07152d234b70 | 0 |
| 4 | jenny | 202cb962ac59075b964b07152d234b70 | 0 |
| 5 | danny | 202cb962ac59075b964b07152d234b70 | 0 |
| 6 | liming | 202cb962ac59075b964b07152d234b70 | 0 |
| 7 | alic | 202cb962ac59075b964b07152d234b70 | 0 |
| 8 | tom | fae0b27c451c728867a567e8c1bb4e53 | 0 |
+----+-------------+----------------------------------+------------+
8 rows in set (0.00 sec)
mysql> 删除数据库，请思考如何根据二进制日志进行数据恢复。
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SHOW MASTER STATUS;
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000004 | 1448 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> DROP DATABASE cmdb; # 我们将cmdb数据库删除，目的是为了模拟通过二进制日志恢复数据。
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SHOW MASTER STATUS;
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000004 | 1605 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
根据二进制日志进行数据恢复实操
(1)如下所示，通过下面的命令，我们可以判断出要重新执行的事件(Event):
mysql> SHOW MASTER STATUS;
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000004 | 1605 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql>
mysql>
mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000004';
+----------------------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
+----------------------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------+
| oldboyedu-mysqld-binary.000004 | 4 | Format_desc | 7 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| oldboyedu-mysqld-binary.000004 | 123 | Previous_gtids | 7 | 154 | |
| oldboyedu-mysqld-binary.000004 | 154 | Anonymous_Gtid | 7 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000004 | 219 | Query | 7 | 351 | CREATE DATABASE IF NOT EXISTS cmdb DEFAULT CHARSET utf8mb4 |
| oldboyedu-mysqld-binary.000004 | 351 | Anonymous_Gtid | 7 | 416 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000004 | 416 | Query | 7 | 833 | CREATE TABLE IF NOT EXISTS cmdb.user (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '用户编号',
username VARCHAR(30) NOT NULL UNIQUE KEY COMMENT '用户名',
password VARCHAR(50) NOT NULL COMMENT '密码',
is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '0表示未删除,1表示已经删除'
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 |
| oldboyedu-mysqld-binary.000004 | 833 | Anonymous_Gtid | 7 | 898 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000004 | 898 | Query | 7 | 966 | BEGIN |
| oldboyedu-mysqld-binary.000004 | 966 | Table_map | 7 | 1020 | table_id: 120 (cmdb.user) |
| oldboyedu-mysqld-binary.000004 | 1020 | Write_rows | 7 | 1417 | table_id: 120 flags: STMT_END_F |
| oldboyedu-mysqld-binary.000004 | 1417 | Xid | 7 | 1448 | COMMIT /* xid=140 */ |
| oldboyedu-mysqld-binary.000004 | 1448 | Anonymous_Gtid | 7 | 1513 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| oldboyedu-mysqld-binary.000004 | 1513 | Query | 7 | 1605 | DROP DATABASE cmdb |
+----------------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
mysql>
(2)经过上一步得到的信息，我们可以判断起点是创建cmdb数据库(即"Pos=219")，终点是删除数据库之前(即"Pos=1513")，于是可以基于binlog日志来截取想要重新执行的日志，具体操作如下所示:
[root@docker201.oldboyedu.com ~]# mysqlbinlog --start-position=219 --stop-position=1513 /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000004 > /tmp/recover_cmdb.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /tmp/recover_cmdb.log
-rw-r--r-- 1 root root 3739 2月 4 16:00 /tmp/recover_cmdb.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# file /tmp/recover_cmdb.log
/tmp/recover_cmdb.log: UTF-8 Unicode text
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /tmp/recover_cmdb.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210204 15:29:06 server id 7 end_log_pos 123 CRC32 0xea1cd3d8 Start: binlog v 4, server v 5.7.31-log created 210204 15:29:06
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
QqIbYA8HAAAAdwAAAHsAAAABAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AdjTHOo=
'/*!*/;
# at 219
#210204 15:32:21 server id 7 end_log_pos 351 CRC32 0x28f420e6 Query thread_id=29 exec_time=0 error_code=0
SET TIMESTAMP=1612423941/*!*/;
SET @@session.pseudo_thread_id=29/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DATABASE IF NOT EXISTS cmdb DEFAULT CHARSET utf8mb4
/*!*/;
# at 351
#210204 15:32:21 server id 7 end_log_pos 416 CRC32 0x9b41ae73 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 416
#210204 15:32:21 server id 7 end_log_pos 833 CRC32 0xca3f12fc Query thread_id=29 exec_time=0 error_code=0
SET TIMESTAMP=1612423941/*!*/;
CREATE TABLE IF NOT EXISTS cmdb.user (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '用户编号',
username VARCHAR(30) NOT NULL UNIQUE KEY COMMENT '用户名',
password VARCHAR(50) NOT NULL COMMENT '密码',
is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '0表示未删除,1表示已经删除'
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4
/*!*/;
# at 833
#210204 15:32:21 server id 7 end_log_pos 898 CRC32 0xad72d307 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 898
#210204 15:32:21 server id 7 end_log_pos 966 CRC32 0x939b5262 Query thread_id=29 exec_time=0 error_code=0
SET TIMESTAMP=1612423941/*!*/;
BEGIN
/*!*/;
# at 966
#210204 15:32:21 server id 7 end_log_pos 1020 CRC32 0xe749177a Table_map: `cmdb`.`user` mapped to number 120
# at 1020
#210204 15:32:21 server id 7 end_log_pos 1417 CRC32 0xd6639db7 Write_rows: table id 120 flags: STMT_END_F
BINLOG '
BaMbYBMHAAAANgAAAPwDAAAAAHgAAAAAAAEABGNtZGIABHVzZXIABAMPDwEEeADIAAB6F0nn
BaMbYB4HAAAAjQEAAIkFAAAAAHgAAAAAAAEAAgAE//ABAAAABWphc29uIGUxMGFkYzM5NDliYTU5
YWJiZTU2ZTA1N2YyMGY4ODNlAPACAAAAC3lpbnpoZW5namllIDIwMmNiOTYyYWM1OTA3NWI5NjRi
MDcxNTJkMjM0YjcwAPADAAAAA2pheSAyMDJjYjk2MmFjNTkwNzViOTY0YjA3MTUyZDIzNGI3MADw
BAAAAAVqZW5ueSAyMDJjYjk2MmFjNTkwNzViOTY0YjA3MTUyZDIzNGI3MADwBQAAAAVkYW5ueSAy
MDJjYjk2MmFjNTkwNzViOTY0YjA3MTUyZDIzNGI3MADwBgAAAAZsaW1pbmcgMjAyY2I5NjJhYzU5
MDc1Yjk2NGIwNzE1MmQyMzRiNzAA8AcAAAAEYWxpYyAyMDJjYjk2MmFjNTkwNzViOTY0YjA3MTUy
ZDIzNGI3MADwCAAAAAN0b20gZmFlMGIyN2M0NTFjNzI4ODY3YTU2N2U4YzFiYjRlNTMAt51j1g==
'/*!*/;
# at 1417
#210204 15:32:21 server id 7 end_log_pos 1448 CRC32 0x7b895b4a Xid = 140
COMMIT/*!*/;
# at 1448
#210204 15:38:59 server id 7 end_log_pos 1513 CRC32 0x3460a348 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
BEGIN /*added by mysqlbinlog */ /*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]#
(3)在MySQL会话终端临时关闭二进制日志功能，因为我们此时需要重放二进制日志，如果不关闭将会继续将该操作记录到二进制日志文件哟!
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> SET sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql>
(4)恢复cmdb数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| world |
| oldboyedu |
| zabbix |
+--------------------+
8 rows in set (0.00 sec)
mysql>
mysql> SOURCE /tmp/recover_cmdb.log;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cmdb |
| mysql |
| performance_schema |
| school |
| sys |
| world |
| oldboyedu |
| zabbix |
+--------------------+
9 rows in set (0.01 sec)
mysql>
mysql> SHOW TABLES FROM cmdb;
+----------------+
| Tables_in_cmdb |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM cmdb.user;
+----+-------------+----------------------------------+------------+
| id | username | password | is_deleted |
+----+-------------+----------------------------------+------------+
| 1 | jason | e10adc3949ba59abbe56e057f20f883e | 0 |
| 2 | oldboyedu | 202cb962ac59075b964b07152d234b70 | 0 |
| 3 | jay | 202cb962ac59075b964b07152d234b70 | 0 |
| 4 | jenny | 202cb962ac59075b964b07152d234b70 | 0 |
| 5 | danny | 202cb962ac59075b964b07152d234b70 | 0 |
| 6 | liming | 202cb962ac59075b964b07152d234b70 | 0 |
| 7 | alic | 202cb962ac59075b964b07152d234b70 | 0 |
| 8 | tom | fae0b27c451c728867a567e8c1bb4e53 | 0 |
+----+-------------+----------------------------------+------------+
8 rows in set (0.00 sec)
mysql>
(5)再次开启二进制日志功能
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> SET sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql>
基于二进制日志(binlog)恢复数据的弊端
(1)使用mysqlbinlog工具截取指定数据库(本案例为"cmdb")的日志信息:
mysqlbinlog -d cmdb --start-position=219 --stop-position=1513 /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000004 > /tmp/recover_cmdb.log
(2)需要的日志分布在多个二进制日志(binlog)文件中，通常可采取以下方案:
1)老办法，我们只需要去确定哪几个日志文件，而后找到每一个二进制文件对应的日志段后按顺序追加到同一个文件即可;
2)使用"--start-datetime"和"--stop-datetime"参数，这样我们就可以同时针对多个文件按照事件来截取事件，但需要注意的是，在同一个时间点可能会发生多个事务，因确认在该时间段内执行的SQL是否是你要恢复的数据;
3)我们可以结合"--start-datetime"，"--stop-datetime"，"start-position=219"，"--stop-position"这几个参数一起使用用;
(3)当要恢复的数据量过大该如何操作呢?比如:创建了3年的数据库，插入的数据操作从"oldboyedu-mysqld-binary.000001"到"oldboyedu-mysqld-binary.089124"之间的日志都有，光存储这些日志的量都有300T左右！
答：在这种场景下再来基于二进制日志手动恢复数据就显得有心无力了，尽管理论上我们是可以实现的。但在实际生产环境中，二进制日志(binlog)是我们数据恢复时配合备份一起恢复数据的手段。
7.二进制日志(binlog)的维护操作
7.1二进制日志(binlog)滚动
二进制日志(binlog)滚动触发机制:
(1)MySQL内部基于"max_binlog_size"指定的1GB文件大小来自动触发日志滚动，也就是说当日志的文件大小达到"1GB"时，会自动触发日志滚动，当然生产环境中，我们也可以修改配置文件来自定义日志滚动的文件大小哟;
(2)可以通过mysql工具提供的字符交互式终端执行"FLUSH LOGS;"命令会手动触发日志滚动，我们也可以借助mysqldump，mysqladmin等工具来触发日志滚动;
(3)当然，重启MySQL数据库实例时，也会自动触发日志滚动;
如下所示，MySQL内部有默认的日志滚动策略，我们可以通过修改"max_binlog_size"参数来定义MySQL内部二进制日志滚动策略，若不设置，默认以1G文件大小来自动触发日志滚动;
mysql> SELECT @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1073741824 |
+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT 1073741824/1024/1024/1024;
+---------------------------+
| 1073741824/1024/1024/1024 |
+---------------------------+
| 1.000000000000 |
+---------------------------+
1 row in set (0.00 sec)
mysql>
如下所示，我们可以基于MySQL内置的交互式命令行来滚动日志:
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SHOW BINARY LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000001 | 5786773 |
| oldboyedu-mysqld-binary.000002 | 217 |
| oldboyedu-mysqld-binary.000003 | 8984 |
| oldboyedu-mysqld-binary.000004 | 1605 |
+----------------------------------+-----------+
4 rows in set (0.01 sec)
mysql>
mysql> SHOW MASTER STATUS;
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000004 | 1605 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.07 sec)
mysql>
mysql> SHOW MASTER STATUS;
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| oldboyedu-mysqld-binary.000005 | 154 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql>
mysql> SHOW BINARY LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000001 | 5786773 |
| oldboyedu-mysqld-binary.000002 | 217 |
| oldboyedu-mysqld-binary.000003 | 8984 |
| oldboyedu-mysqld-binary.000004 | 1668 |
| oldboyedu-mysqld-binary.000005 | 154 |
+----------------------------------+-----------+
5 rows in set (0.00 sec)
mysql>
mysql> QUIT
Bye
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/logs/mysql23307/binlog/
总用量 5.6M
-rw-r----- 1 mysql mysql 5.6M 2月 4 08:16 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 217 2月 4 09:57 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 8.8K 2月 4 15:29 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1.7K 2月 4 16:42 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 154 2月 4 16:42 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 345 2月 4 16:42 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
如下所示，我们也可以借助mysqladmin工具来滚动日志:
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/logs/mysql23307/binlog/
总用量 5.6M
-rw-r----- 1 mysql mysql 5.6M 2月 4 08:16 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 217 2月 4 09:57 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 8.8K 2月 4 15:29 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1.7K 2月 4 16:42 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 154 2月 4 16:42 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 345 2月 4 16:42 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysqladmin -S /tmp/mysql23307.sock flush-logs
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/logs/mysql23307/binlog/
总用量 5.6M
-rw-r----- 1 mysql mysql 5.6M 2月 4 08:16 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 217 2月 4 09:57 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 8.8K 2月 4 15:29 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1.7K 2月 4 16:42 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 217 2月 4 16:44 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 154 2月 4 16:44 oldboyedu-mysqld-binary.000006
-rw-r----- 1 mysql mysql 414 2月 4 16:44 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
如下所示，我们可以使用mysqldump工具的"-F"选项来滚动日志:
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/
总用量 5684
-rw-r----- 1 mysql mysql 5786773 2月 4 08:16 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 217 2月 4 09:57 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 8984 2月 4 15:29 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1668 2月 4 16:42 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 217 2月 4 16:44 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 154 2月 4 16:44 oldboyedu-mysqld-binary.000006
-rw-r----- 1 mysql mysql 414 2月 4 16:44 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysqldump cmdb -S /tmp/mysql23307.sock -F
-- MySQL dump 10.13 Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: cmdb
-- ------------------------------------------------------
-- Server version 5.7.31-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`username` varchar(30) NOT NULL COMMENT '用户名',
`password` varchar(50) NOT NULL COMMENT '密码',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0表示未删除,1表示已经删除',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'jason','e10adc3949ba59abbe56e057f20f883e',0),(2,'oldboyedu','202cb962ac59075b964b07152d234b70',0),(3,'jay','202cb962ac59075b964b07152d234b70',0),(4,'jenny','
202cb962ac59075b964b07152d234b70',0),(5,'danny','202cb962ac59075b964b07152d234b70',0),(6,'liming','202cb962ac59075b964b07152d234b70',0),(7,'alic','202cb962ac59075b964b07152d234b70',0),(8,'tom','fae0b27c451c728867a567e8c1bb4e53',0);/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-02-04 16:50:18
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/
总用量 5688
-rw-r----- 1 mysql mysql 5786773 2月 4 08:16 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 217 2月 4 09:57 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 8984 2月 4 15:29 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1668 2月 4 16:42 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 217 2月 4 16:44 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 217 2月 4 16:50 oldboyedu-mysqld-binary.000006
-rw-r----- 1 mysql mysql 154 2月 4 16:50 oldboyedu-mysqld-binary.000007
-rw-r----- 1 mysql mysql 483 2月 4 16:50 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]#
如下所示，很明显，每当我们重启MySQL数据库实例时就会重启数据库:
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/
总用量 5688
-rw-r----- 1 mysql mysql 5786773 2月 4 08:16 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 217 2月 4 09:57 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 8984 2月 4 15:29 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1668 2月 4 16:42 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 217 2月 4 16:44 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 217 2月 4 16:50 oldboyedu-mysqld-binary.000006
-rw-r----- 1 mysql mysql 154 2月 4 16:50 oldboyedu-mysqld-binary.000007
-rw-r----- 1 mysql mysql 483 2月 4 16:50 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# systemctl restart mysqld23307
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/
总用量 5692
-rw-r----- 1 mysql mysql 5786773 2月 4 08:16 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 217 2月 4 09:57 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 8984 2月 4 15:29 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1668 2月 4 16:42 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 217 2月 4 16:44 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 217 2月 4 16:50 oldboyedu-mysqld-binary.000006
-rw-r----- 1 mysql mysql 177 2月 4 16:52 oldboyedu-mysqld-binary.000007
-rw-r----- 1 mysql mysql 154 2月 4 16:52 oldboyedu-mysqld-binary.000008
-rw-r----- 1 mysql mysql 552 2月 4 16:52 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
7.2二进制日志(binlog)删除
二进制日志(binlog)删除触发机制:
(1)MySQL内部基于"expire_logs_days"参数来删除二进制日志(binlog)文件，若不设置，默认值为0，表示永不删除二进制日志(binlog)。生产环境建议设置最少2个全量备份周期(如果你是每周做一次全量备份，那么你的周期就是7天)+1，目的是最近一个全量备份若损坏则可以考虑使用上一个的备份。
(2)我们可以借助"PURGE BINARY LOGS"命令工具来手动删除二进制日志(binlog)文件，也可以借助"reset master"命令来手动清空全部的二进制日志文件(新的日志文件名称编号将从"000001"开始命名，此命令比较危险，如果在主从架构中的主库执行该命令，那么主从复制会立即失效);
(3)注意，千万不要手动使用"rm"命令来删除二进制日志(binlog)文件哟~
如下所示，MySQL内部有默认的日志删除策略，我们可以通过修改"expire_logs_days"参数来定义MySQL内部二进制日志删除策略，若不设置，默认值为0，表示永不删除二进制日志(binlog)。
mysql> SELECT @@expire_logs_days; # 生产环境建议设置最少2个全量备份周期(如果你是每周做一次全量备份，那么你的周期就是7天)+1，目的是最近一个全量备份若损坏则可以考虑使用上一个的备份。
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
mysql>
如下所示，我删除了"oldboyedu-mysqld-binary.000003"之前产生的所有二进制日志(binlog)文件:
mysql> SHOW BINARY LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000001 | 5786773 |
| oldboyedu-mysqld-binary.000002 | 217 |
| oldboyedu-mysqld-binary.000003 | 8984 |
| oldboyedu-mysqld-binary.000004 | 1668 |
| oldboyedu-mysqld-binary.000005 | 217 |
| oldboyedu-mysqld-binary.000006 | 217 |
| oldboyedu-mysqld-binary.000007 | 177 |
| oldboyedu-mysqld-binary.000008 | 154 |
+----------------------------------+-----------+
8 rows in set (0.00 sec)
mysql>
mysql> PURGE BINARY LOGS TO 'oldboyedu-mysqld-binary.000003'; # 删除在'oldboyedu-mysqld-binary.000003'之前产生的所有二进制日志(binlog)文件。
Query OK, 0 rows affected (0.28 sec)
mysql>
mysql> SHOW BINARY LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000003 | 8984 |
| oldboyedu-mysqld-binary.000004 | 1668 |
| oldboyedu-mysqld-binary.000005 | 217 |
| oldboyedu-mysqld-binary.000006 | 217 |
| oldboyedu-mysqld-binary.000007 | 177 |
| oldboyedu-mysqld-binary.000008 | 154 |
+----------------------------------+-----------+
6 rows in set (0.00 sec)
mysql>
如下图所示，删除指定日期之前的二进制日志:
mysql> SHOW BINARY LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000003 | 8984 |
| oldboyedu-mysqld-binary.000004 | 1668 |
| oldboyedu-mysqld-binary.000005 | 217 |
| oldboyedu-mysqld-binary.000006 | 217 |
| oldboyedu-mysqld-binary.000007 | 177 |
| oldboyedu-mysqld-binary.000008 | 154 |
+----------------------------------+-----------+
6 rows in set (0.00 sec)
mysql>
mysql> PURGE BINARY LOGS BEFORE '2021-02-04 16:44:17';
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> SHOW BINARY LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000005 | 217 |
| oldboyedu-mysqld-binary.000006 | 217 |
| oldboyedu-mysqld-binary.000007 | 177 |
| oldboyedu-mysqld-binary.000008 | 154 |
+----------------------------------+-----------+
4 rows in set (0.00 sec)
mysql>
如下所示，我们可以使用"reset master"命令来手动清空全部的二进制日志文件(新的日志文件名称编号将从"000001"开始命名，此命令比较危险，如果在主从架构中的主库执行该命令，那么主从复制会立即失效):
mysql> SHOW BINARY LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000005 | 217 |
| oldboyedu-mysqld-binary.000006 | 217 |
| oldboyedu-mysqld-binary.000007 | 177 |
| oldboyedu-mysqld-binary.000008 | 154 |
+----------------------------------+-----------+
4 rows in set (0.00 sec)
mysql>
mysql> RESET MASTER; # 该命令会删除所有的二进制日志文件，并重新从"000001"开始命名二进制文件哟~在主从复制的时候要慎用哈！
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> SHOW BINARY LOGS;
+----------------------------------+-----------+
| Log_name | File_size |
+----------------------------------+-----------+
| oldboyedu-mysqld-binary.000001 | 154 |
+----------------------------------+-----------+
1 row in set (0.00 sec)
mysql>
8.生产中,使用binlog日志恢复数据会有什么痛点?
(1)建库时间太久,日志量太多, 日志有可能只剩部分了,怎么办?
备份+binlog可以恢复到.
(2)binlog保存了多个不同库的日志. 只需要其中一个库的日志.怎么办?
mysqlbinlog -d database
(3)我需要的日志跨了多个文件,怎么办?
方案一:
单独截取多个文件日志,然后合并;
方案二:
gtid日志记录模式 ,后续有相关章节讲解;
(4)只需要期中一个表的日志.怎么办?
借助binlog2sql.
(5)一张表10亿行,误删除10行数据. 怎么办 ?
binlog2sql,做数据闪回.四.MySQL慢日志管理
1.MySQL慢日志概述
MySQL慢日志记录内容:
记录mysql在运行过程中执行较慢的语句，通过一个文本文件记录下来。
MySQL慢日志作用:
主要帮助DBA进行语句优化的工具日志。
2.慢日志配置方法
我们可以通过"slow_query_log"和"slow_query_log_file"来个参数来配置慢日志功能，其默认值如下所示:
mysql> SELECT @@slow_query_log; # 指定是否开启慢日志功能。很明显，默认情况下，MySQL是没有开启慢日志查询功能的
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT @@slow_query_log_file; # 指定慢日志文件存储位置。需要注意的是，慢日志的默认存放位置是和数据目录存储在一起的，但生产环境中，建议大家将慢日志和数据文件分开存储。
+-------------------------------------------------+
| @@slow_query_log_file |
+-------------------------------------------------+
| /oldboyedu/data/mysql23307/docker201-slow.log |
+-------------------------------------------------+
1 row in set (0.01 sec)
mysql>
mysql> SELECT @@long_query_time; # 指定慢语句的阈值，即超出该阈值则默认为慢SQL语句。很明显，默认情况下，超过10秒的SQL均被视为慢语句查询。生产环境中我们可以循序渐进设置阈值，比如0.1~3秒均可。
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT @@log_queries_not_using_indexes; # 指定是否记录不走索引的语句。很明显，默认情况下并不记录。
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.00 sec)
mysql>
生产环境中在配置文件中启用慢日志功能:
[root@docker201.oldboyedu.com ~]# vim /oldboyedu/softwares/mysql23307/my.cnf
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# tail -5 /oldboyedu/softwares/mysql23307/my.cnf
# 开启慢日志功能
slow_query_log=1
slow_query_log_file=/oldboyedu/logs/mysql23307/slowlog/slow.log
long_query_time=0.1
log_queries_not_using_indexes=1
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# install -d /oldboyedu/logs/mysql23307/slowlog -o mysql -g mysql
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# systemctl restart mysqld23307
[root@docker201.oldboyedu.com ~]#
3.模拟慢日志语句
创建存储过程
[root@docker201.oldboyedu.com ~]# cat student.sql
# 创建数据库
CREATE DATABASE IF NOT EXISTS oldboyedu DEFAULT CHARACTER SET = utf8mb4;
# 创建表结构
CREATE TABLE IF NOT EXISTS oldboyedu.student (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '学生编号ID',
name varchar(30) NOT NULL COMMENT '学生姓名',
age tinyint UNSIGNED DEFAULT NULL COMMENT '年龄',
gender enum('Male','Female') DEFAULT 'Male' COMMENT '性别',
time_of_enrollment DATETIME(0) COMMENT '报名时间',
address varchar(255) NOT NULL COMMENT '家庭住址',
mobile_number bigint UNIQUE KEY NOT NULL COMMENT '手机号码',
remarks VARCHAR(255) COMMENT '备注信息'
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
# 临时修改MySQL默认的分隔符(";")为"$$",因为我们创建存储过程时可能要执行多条SQL语句,里面会写多个";"哟~
DELIMITER $$
# 创建存储过程
CREATE PROCEDURE IF NOT EXISTS
pro_testlog()
BEGIN
DECLARE i INT;
SET i = 1;
WHILE i <= 100000
DO
INSERT INTO oldboyedu.student
(name,age,time_of_enrollment,address,mobile_number)
VALUES
(CONCAT('oldboyedu',i),CASE WHEN i < 150 THEN i ELSE 255 END,NOW(),CONCAT('oldboyedu-address-',i),i);
SET
i = i +1;
END
WHILE;
END$$
# 切记要将修改后的分隔符("$$")改回之前默认的分隔符哟~
DELIMITER ;
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SOURCE student.sql;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> USE oldboyedu
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_oldboyedu |
+-----------------------+
| student |
+-----------------------+
1 rows in set (0.01 sec)
mysql>
调用存储过程
mysql> SET sql_log_bin=0; # 先临时关闭二进制日志功能，下面调用存储过程会有大量的插入测试语句。
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CALL pro_testlog; # 调用存储过程
Query OK, 1 row affected (1 min 49.26 sec)
mysql>
mysql> SET sql_log_bin=1; # 调用存储过程后，再次开启二进制日志功能
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
| 99999 |
+----------+
1 row in set (0.01 sec)
mysql>
清空慢日志记录，以便于后续测试
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/logs/mysql23307/slowlog/slow.log
/oldboyedu/softwares/mysql/mysql/bin/mysqld, Version: 5.7.31-log (MySQL Community Server (GPL)). started with:
Tcp port: 23307 Unix socket: /tmp/mysql23307.sock
Time Id Command Argument
# Time: 2021-02-10T22:50:39.007735Z
# User@Host: root[root] @ localhost [] Id: 2
# Query_time: 109.262281 Lock_time: 0.000053 Rows_sent: 0 Rows_examined: 0
use oldboyedu;
SET timestamp=1612997439;
CALL pro_testlog;
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# > /oldboyedu/logs/mysql23307/slowlog/slow.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/logs/mysql23307/slowlog/slow.log
[root@docker201.oldboyedu.com ~]#
模拟慢日志查询
查询表的索引信息:
mysql> SHOW INDEX FROM oldboyedu.student;
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 98500 | NULL | NULL | | BTREE | | |
| student | 0 | mobile_number | 1 | mobile_number | A | 99189 | NULL | NULL | | BTREE | | |
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql>
查询所有数据，但只从第50000条数据开始显示，只显示20行
mysql> SELECT * FROM oldboyedu.student LIMIT 50000,20;
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
| id | name | age | gender | time_of_enrollment | address | mobile_number | remarks |
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
| 50001 | oldboyedu50001 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50001 | 50001 | NULL |
| 50002 | oldboyedu50002 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50002 | 50002 | NULL |
| 50003 | oldboyedu50003 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50003 | 50003 | NULL |
| 50004 | oldboyedu50004 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50004 | 50004 | NULL |
| 50005 | oldboyedu50005 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50005 | 50005 | NULL |
| 50006 | oldboyedu50006 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50006 | 50006 | NULL |
| 50007 | oldboyedu50007 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50007 | 50007 | NULL |
| 50008 | oldboyedu50008 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50008 | 50008 | NULL |
| 50009 | oldboyedu50009 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50009 | 50009 | NULL |
| 50010 | oldboyedu50010 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50010 | 50010 | NULL |
| 50011 | oldboyedu50011 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50011 | 50011 | NULL |
| 50012 | oldboyedu50012 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50012 | 50012 | NULL |
| 50013 | oldboyedu50013 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50013 | 50013 | NULL |
| 50014 | oldboyedu50014 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50014 | 50014 | NULL |
| 50015 | oldboyedu50015 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50015 | 50015 | NULL |
| 50016 | oldboyedu50016 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50016 | 50016 | NULL |
| 50017 | oldboyedu50017 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50017 | 50017 | NULL |
| 50018 | oldboyedu50018 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50018 | 50018 | NULL |
| 50019 | oldboyedu50019 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50019 | 50019 | NULL |
| 50020 | oldboyedu50020 | 255 | Male | 2021-02-11 06:49:45 | oldboyedu-address-50020 | 50020 | NULL |
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
20 rows in set (0.02 sec)
mysql>
查询所有数据，但只从第60000条数据开始显示，只显示30行
mysql> SELECT * FROM oldboyedu.student LIMIT 60000,30;
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
| id | name | age | gender | time_of_enrollment | address | mobile_number | remarks |
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
| 60001 | oldboyedu60001 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60001 | 60001 | NULL |
| 60002 | oldboyedu60002 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60002 | 60002 | NULL |
| 60003 | oldboyedu60003 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60003 | 60003 | NULL |
| 60004 | oldboyedu60004 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60004 | 60004 | NULL |
| 60005 | oldboyedu60005 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60005 | 60005 | NULL |
| 60006 | oldboyedu60006 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60006 | 60006 | NULL |
| 60007 | oldboyedu60007 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60007 | 60007 | NULL |
| 60008 | oldboyedu60008 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60008 | 60008 | NULL |
| 60009 | oldboyedu60009 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60009 | 60009 | NULL |
| 60010 | oldboyedu60010 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60010 | 60010 | NULL |
| 60011 | oldboyedu60011 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60011 | 60011 | NULL |
| 60012 | oldboyedu60012 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60012 | 60012 | NULL |
| 60013 | oldboyedu60013 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60013 | 60013 | NULL |
| 60014 | oldboyedu60014 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60014 | 60014 | NULL |
| 60015 | oldboyedu60015 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60015 | 60015 | NULL |
| 60016 | oldboyedu60016 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60016 | 60016 | NULL |
| 60017 | oldboyedu60017 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60017 | 60017 | NULL |
| 60018 | oldboyedu60018 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60018 | 60018 | NULL |
| 60019 | oldboyedu60019 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60019 | 60019 | NULL |
| 60020 | oldboyedu60020 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60020 | 60020 | NULL |
| 60021 | oldboyedu60021 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60021 | 60021 | NULL |
| 60022 | oldboyedu60022 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60022 | 60022 | NULL |
| 60023 | oldboyedu60023 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60023 | 60023 | NULL |
| 60024 | oldboyedu60024 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60024 | 60024 | NULL |
| 60025 | oldboyedu60025 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60025 | 60025 | NULL |
| 60026 | oldboyedu60026 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60026 | 60026 | NULL |
| 60027 | oldboyedu60027 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60027 | 60027 | NULL |
| 60028 | oldboyedu60028 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60028 | 60028 | NULL |
| 60029 | oldboyedu60029 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60029 | 60029 | NULL |
| 60030 | oldboyedu60030 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60030 | 60030 | NULL |
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
30 rows in set (0.03 sec)
mysql>
查询所有数据，但只从第60000条数据开始显示，只显示1行
mysql> SELECT * FROM oldboyedu.student LIMIT 60000,1;
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
| id | name | age | gender | time_of_enrollment | address | mobile_number | remarks |
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
| 60001 | oldboyedu60001 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60001 | 60001 | NULL |
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
1 row in set (0.02 sec)
mysql>
查询所有数据，但只从第60000条数据开始显示，只显示3行
mysql> SELECT * FROM oldboyedu.student LIMIT 60000,3;
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
| id | name | age | gender | time_of_enrollment | address | mobile_number | remarks |
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
| 60001 | oldboyedu60001 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60001 | 60001 | NULL |
| 60002 | oldboyedu60002 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60002 | 60002 | NULL |
| 60003 | oldboyedu60003 | 255 | Male | 2021-02-11 06:49:56 | oldboyedu-address-60003 | 60003 | NULL |
+-------+------------------+------+--------+---------------------+---------------------------+---------------+---------+
3 rows in set (0.02 sec)
mysql>
按照名字进行分组，查看前10个名字:
mysql> SELECT name,COUNT(id) FROM oldboyedu.student GROUP BY name LIMIT 10;
+------------------+-----------+
| name | COUNT(id) |
+------------------+-----------+
| oldboyedu1 | 1 |
| oldboyedu10 | 1 |
| oldboyedu100 | 1 |
| oldboyedu1000 | 1 |
| oldboyedu10000 | 1 |
| oldboyedu10001 | 1 |
| oldboyedu10002 | 1 |
| oldboyedu10003 | 1 |
| oldboyedu10004 | 1 |
| oldboyedu10005 | 1 |
+------------------+-----------+
10 rows in set (0.29 sec)
mysql>
按照名字进行分组，但只从第80000条数据开始显示，只显示10行：
mysql> SELECT name,COUNT(id) FROM oldboyedu.student GROUP BY name LIMIT 80000,10;
+------------------+-----------+
| name | COUNT(id) |
+------------------+-----------+
| oldboyedu82 | 1 |
| oldboyedu820 | 1 |
| oldboyedu8200 | 1 |
| oldboyedu82000 | 1 |
| oldboyedu82001 | 1 |
| oldboyedu82002 | 1 |
| oldboyedu82003 | 1 |
| oldboyedu82004 | 1 |
| oldboyedu82005 | 1 |
| oldboyedu82006 | 1 |
+------------------+-----------+
10 rows in set (0.31 sec)
mysql>
温馨提示:
如下图所示，凡是触发"log_queries_not_using_indexes"，"long_query_time"任意一个参数的阈值都会被记录在慢日志中哟~
mysql> SELECT @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 1 |
+---------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 0.100000 |
+-------------------+
1 row in set (0.01 sec)
mysql>
使用mysqldumpslow工具来分析慢日志
直接查看慢日志尽管也可以，但还是适用于日志量较少的情况下，日志较多后，可读性较差了，因此MySQL官方给咱们提供了"mysqldumpslow"工具来进行慢日志分析，常用的参数说明如下:
[root@mysql108.oldboyedu.com /oldboyedu/logs/mysql/slowlog]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
[root@mysql108.oldboyedu.com /oldboyedu/logs/mysql/slowlog]#
如下所示，我们可以使用mysqldumpslow工具来分析慢日志:
[root@docker201.oldboyedu.com ~]# mysqldumpslow -s c -t 3 /oldboyedu/logs/mysql23307/slowlog/slow.log
Reading mysql slow query log from /oldboyedu/logs/mysql23307/slowlog/slow.log
Count: 3 Time=0.02s (0s) Lock=0.00s (0s) Rows=17.0 (51), root[root]@localhost
SELECT * FROM oldboyedu.student LIMIT N,N
Count: 1 Time=0.31s (0s) Lock=0.00s (0s) Rows=10.0 (10), root[root]@localhost
SELECT name,COUNT(id) FROM oldboyedu.student GROUP BY name LIMIT N,N
Count: 1 Time=0.29s (0s) Lock=0.00s (0s) Rows=10.0 (10), root[root]@localhost
SELECT name,COUNT(id) FROM oldboyedu.student GROUP BY name LIMIT N
[root@docker201.oldboyedu.com ~]#
如上所述，我们看到了Top 3的SQL，身为DBA的你需要对这排行靠前的SQL语句进行优化:
(1)SELECT * FROM oldboyedu.student LIMIT N,N:
很明显，这是一款全表扫描的语句，尽管使用了LIMIT限制输出的信息，但底层依旧是全表扫描操作，遇到这种情况，DBA没法直接进行优化，而是应该找开发人员，问他为啥写这样的SQL，咨询它的需求，可以帮助他改写SQL以达到优化的目的。
(2)SELECT name,COUNT(id) FROM oldboyedu.student GROUP BY name LIMIT N,N:
我们可以借助EXPLAIN或者DESC工具来分析SQL的执行情况。而后考虑是否建议使用创建索引来达到优化的目的。
温馨提示:
如果大家觉得mysqldumpslow工具不太好用，可以考虑使用"pt-query-digest"工具来分析慢日志，而后借助"Amemometer"工具来进行可视化展示。
根据慢日志查询记录，以建立索引的方式达到优化的目的。
mysql> SHOW INDEX FROM oldboyedu.student;
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 98500 | NULL | NULL | | BTREE | | |
| student | 0 | mobile_number | 1 | mobile_number | A | 99189 | NULL | NULL | | BTREE | | |
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql>
mysql> CREATE INDEX myindex ON oldboyedu.student (name,age);
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> SHOW INDEX FROM oldboyedu.student;
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 98500 | NULL | NULL | | BTREE | | |
| student | 0 | mobile_number | 1 | mobile_number | A | 99189 | NULL | NULL | | BTREE | | |
| student | 1 | myindex | 1 | name | A | 99716 | NULL | NULL | | BTREE | | |
| student | 1 | myindex | 2 | age | A | 99716 | NULL | NULL | YES | BTREE | | |
+---------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql>
mysql> SELECT name,age FROM oldboyedu.student LIMIT 60000,3;
+-----------------+------+
| name | age |
+-----------------+------+
| oldboyedu64 | 64 |
| oldboyedu640 | 255 |
| oldboyedu6400 | 255 |
+-----------------+------+
3 rows in set (0.01 sec)
mysql>
生产环境汇总，如何定位待优化SQL语句的轻重缓急呢?
我们以生产环境的数据库为例，如果慢日志记录有多个维度的日志，比如SQL的执行次数，SQL的执行时间等，我们优化时应该优先优化最影响用户体验的语句。
综上所述，如果有SQL的执行次数和SQL的执行时间，我们通常会优先考虑优化执行次数较多的SQL进行优化，因为这将直接影响用户体验!
五.小彩蛋-配置MySQL的时区
(1)检查时区表
SELECT COUNT(*) FROM mysql.time_zone_name;
(2)导入时区表
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -S /tmp/mysql3309.sock mysql
(3)修改MySQL的时区
SET time_zone = '+08:00';
(4)查看时区命令
SHOW VARIABLES LIKE '%time_zone%';
推荐阅读:
https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
六.扩展内容-binlog2sql应用
1.binlog2sql是什么
用来帮助咱们查看二进制日志的工具,该工具使用python开发.2.安装binlog2sql软件包
(1)安装python3环境
yum install python3 -y
(2)编写依赖软件包文件版本
vi requirements.txt
PyMySQL==0.9.3
wheel==0.29.0
mysql-replication==0.13
(3)安装依赖环境
pip3 install -r requirements.txt
pip3 show pymysql
(4)解压binlog2sql压缩包即可
unzip binlog2sql-master.zip3.使用案例
单独过滤某张表的binlog:
python3 binlog2sql.py -P 3309 -d zabbix -t --start-file='oldboyedu_mysql_logbin.000016'
python3 binlog2sql.py -h 10.0.0.108 -P3306 -uroot -p123 -d test1 -t t1 --start-file='mysql-bin.000003'
单独过滤某些类型的binlog
python3 binlog2sql.py -h 10.0.0.108 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=delete --start-file='mysql-bin.000003'
生成指定事件回滚语句---> 应用场景： 3000万数据，误删10行数据，怎么恢复？
python3 binlog2sql.py -h 10.0.0.108 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete --start-position=932 --stop-position=1198 -B>/tmp/flashback.sql