MYSQL数据库日志管理

第六章 日志管理

  1. 普通日志 1.1 介绍 默认是关闭的. 可以记录MySQL中发生过的所有操作日志.一般用来调试.

1.2 如何配置

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n6" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> general_log = ON
 general_log_file  = /data/3306/data/db01.log</pre>

1.3 作用 审计 调试

  1. 错误日志

2.1 介绍 记录数据库从启动以来,状态\报错\警告.

2.2 查询和配置

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n14" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> mysql> show variables like '%error%';</pre>

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n15" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> log_error = ./db01.err
 log_error_verbosity=3</pre>

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n16" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> mysql> set global log_error_verbosity=3;  ##进行调整binlog日志的记录数量,最大数量为3</pre>

2.3 怎么用 每天定时巡检.主要关注 [ERROR] ,[WARNING]

3.  二进制日志 (binlog)

3.1 介绍 以event形式,记录MySQL数据库中变更类的操作日志(DDL DCL DML).

3.2 作用 数据恢复.  复制

3.3 配置

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n25" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> mysql> show variables like '%bin%';  ###查询
 log_bin=1   ###开启 
 log_bin_basename=/data/3306/data/binlog ##存放位置
 sync_binlog=1  ###同步日志到磁盘 刷新日志
 binlog_format=row/statement/mixed  </pre>

**彩蛋1: sync_binlog=1 是双一期中一个1.保证事务提交理解刷新binlog到磁盘.**

彩蛋2: binlog_format=row /statement/ mixed  格式区别:

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="" cid="n28" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> 1\. row (RBR)      : 记录每个数据行的真实变化. 日志量会比较大.记录足够准确.
  update t1 set num=20 where id<10;</pre>

2.  statement(SBR) : 记录发生的是语句.日志量相对少.记录有可能不准确.

3.  mixed 混合模式

binlog_format 只影响DML语句.DDL和DCL都是Statement

3.4 binlog 应用

3.4.1 查看和分析binlog

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="" cid="n37" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> mysql> show binary logs;  ###进行查看binlog
 mysql> show master status ; ###进行查看binlog
  mysql> show binlog events  in 'binlog.000002';###进行查看binlog日志</pre>

[root@db01 data]# mysqlbinlog binlog.000007 [root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv binlog.000007

# 数据的损坏模拟跟恢复

第一步:进行创建oldboy的数据库t1表

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n42" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> mysql> create database oldboy;
 mysql> create table t1 (id int);</pre>

第二步:进行在oldboy数据库中写入数据

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n44" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> mysql> select * from t1;
 +------+
 | id   |
 +------+
 |    1 |
 |    2 |
 |    3 |
 |    1 |
 |    2 |
 |    3 |
 +------+</pre>

第三步:进行模拟删除数据库的操作:

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="shell" cid="n46" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> mysql> drop database oldboy;</pre>

# 进行数据库的恢复思路:

第一步:

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n49" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> mysql> show master status; ##进行查看当前数据库binlog
 +---------------+----------+--------------+------------------+-------------------+
 | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +---------------+----------+--------------+------------------+-------------------+
 | binlog.000002 |     2580 |              |                  |                   |
 +---------------+----------+--------------+------------------+-------------------+
 ​</pre>

第二步:

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n51" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> show binlog events in 'binlog.000002';  ##查看当前的binlog日志的内容 
 截取binlog日志中从创建数据库到删除数据库的binlog日志
 | binlog.000002 |  424 | Query          |         1 |         538 | create database oldboy /* xid=119 */ 
 | binlog.000002 | 2470 | Query          |         1 |        2580 | drop database oldboy /* xid=139 */ </pre>

第四步:进行恢复

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n53" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> 进行恢复,进binlog日志的文件输入到指定的目录中
 mysqlbinlog --start-position=424 --stop-position=2470 /data/3306/data/binlog.000002 >/tmp/bin.sql</pre>

第五步:进行在数据中进行恢复

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n55" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> source /tmp/bin.sql</pre>

第六步:进行查看:

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n57" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> mysql> show database;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | oldboy             |
 | oldguo             |
 | performance_schema |
 | sys                |
 | test               |
 ​
 进入数据库中进行数据库中的数据:
 mysql> use oldboy;
 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> select * from t1;
 +------+
 | id   |
 +------+
 |    1 |
 |    2 |
 |    3 |
 |    1 |
 |    2 |
 |    3 |
 ​</pre>

# 彩蛋: 生产中,使用binlog日志恢复数据会有什么痛点?

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n59" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> 1. 建库时间太久,日志量太多 , 日志有可能只剩部分了. 怎么破?
 备份+binlog可以恢复到.
 ​
 2. binlog 保存了多个不同库的日志. 只需要期中一个库的日志.怎么办? 
 ​
 mysqlbinlog -d   ##进行过滤单独数据库的日志 
 ​
 只需要期中一个表的日志.怎么办? 
 ​
 binlog2sql 
 ​
 3. 一张表10亿行,误删除10行数据. 怎么办 ? 
 binlog2sql 做数据闪回
 ​
 ​
 4. 我需要的日志跨了多个文件,怎么办?
 ​
 a.  单独截取多个文件日志,然后合并
 ​
 binlog.000005    1080   1-3
 binlog.000006           4-10
 binlog.000007    789    11-12
 ​
 b. gtid 日志记录模式 
 ​</pre>

# binlog2sql应用

第一步:安装

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="bash" cid="n62" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> [root@db01 opt]# vim requirements.txt 
 PyMySQL==0.9.3
 wheel==0.29.0
 mysql-replication==0.13
 [root@db01 opt]# yum install python3 -y
 pip3 install -r requirements.txt
 pip3 show pymysql
 [root@db01 opt]# unzip binlog2sql-master.zip </pre>

第二步:进行解析日志事件sql

a. 单独过滤某张表的binlog

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n65" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> [root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --start-file='mysql-bin.000003'
 INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1647 end 1891 time 2020-09-18 08:46:53 gtid 
 INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1922 end 2166 time 2020-09-18 08:46:54 gtid 
 INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 3125 end 3369 time 2020-09-18 08:47:50 gtid 
 INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3400 end 3644 time 2020-09-18 08:47:53 gtid </pre>

b. 单独过滤某些类型的binlog

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n67" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> [root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=delete  --start-file='mysql-bin.000003'
 DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5172 end 5416 time 2020-09-18 09:17:48 gtid 
 [root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=update  --start-file='mysql-bin.000003'
 UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4882 end 5141 time 2020-09-18 09:17:35 gtid 
 [root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=insert  --start-file='mysql-bin.000003'
 INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1647 end 1891 time 2020-09-18 08:46:53 gtid 
 INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1922 end 2166 time 2020-09-18 08:46:54 gtid 
 INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 3125 end 3369 time 2020-09-18 08:47:50 gtid 
 INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3400 end 3644 time 2020-09-18 08:47:53 gtid 
 [root@db01 binlog2sql-master]# 
 ​</pre>

c. 生成指定事件回滚语句

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n69" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> [root@db01 binlog2sql]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1  --start-file='mysql-bin.000003'   --sql-type=delete --start-position=932 --stop-position=1198 -B
 ​
 [root@db01 binlog2sql]# python3 binlog2sql.py  -h 10.0.0.51 -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</pre>

# 慢日志

介绍 记录执行较慢语句.

配置

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="powershell" cid="n73" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> mysql> set global slow_query_log=on;
 mysql> set global long_query_time=0.01;
 mysql> set global log_queries_not_using_indexes=1;
 ​
 mysql> select @@slow_query_log;
 mysql> select @@long_query_time;
 mysql> select @@log_queries_not_using_indexes;</pre>

模拟慢语句

分析慢日志

<pre spellcheck="false" class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" lang="shell" cid="n76" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, &quot;Andale Mono&quot;, &quot;DejaVu Sans Mono&quot;, monospace; margin-top: 0px; margin-bottom: 20px; font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; background: rgb(51, 51, 51); position: relative !important; padding: 10px 10px 10px 0px; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> [root@db01 data]# mysqldumpslow -s c -t 3   db01-slow.log 
 ​
 Reading mysql slow query log from db01-slow.log
 Count: 4  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=10.0 (40), root[root]@localhost
  select * from t100w where num<N limit N
 ​
 Count: 3  Time=0.55s (1s)  Lock=0.00s (0s)  Rows=20.0 (60), root[root]@localhost
  select count(*)  from t100w where num<N  group by k2,k1  limit N
 ​
 Count: 3  Time=0.53s (1s)  Lock=0.00s (0s)  Rows=13.3 (40), root[root]@localhost
  select count(*)  from t100w where num<N  group by num,k1  limit N
 ​</pre>
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,242评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,769评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,484评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,133评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,007评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,080评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,496评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,190评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,464评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,549评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,330评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,205评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,567评论 3赞 298
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,889评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,160评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,475评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,650评论 2赞 335