第1章 MySQL日志简介

日志文件	选项	文件名	程序
		表名称
错误	–log-error	host_name.err	N/A
常规	–general_log	host_name.log	N/A
		general_log
慢速查询	–slow_query_log –long_query_time	host_name-slow. log	mysqldumpslow
		slow_log
二进制	–log-bin –expire-logs-days	host_name-bin.000001	mysqlbinlog
审计	–audit_log –audit_log_file …	audit.log	N/A

1.1 错误日志

错误日志记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。

1.1.1 配置方法

[mysqld]
log-error=/tmp/mysql.err

1.1.2 查看配置方式

mysql> show variables like '%log%error%';
+---------------------+----------------+
| Variable_name       | Value          |
+---------------------+----------------+
| binlog_error_action | IGNORE_ERROR   |
| log_error           | /tmp/mysql.err |
+---------------------+----------------+
2 rows in set (0.06 sec)

1.2 一般查询日志

一般查询日志记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启，除非有特殊要求，如有ELK服务等。

1.2.1 配置方法

[mysqld]
general_log=on
general_log_file=/tmp/general.log

1.2.2 查看配置方式

mysql> show variables like '%gen%';
+------------------+------------------+
| Variable_name    | Value            |
+------------------+------------------+
| general_log      | ON               |
| general_log_file | /tmp/general.log |
+------------------+------------------+
2 rows in set (0.00 sec)

[root@db02 ~]# tail /tmp/general.log
171121 12:13:59     1 Query show variables like 'log_queries_not_using_indexes'
/usr/local/mysql/bin/mysqld, Version: 5.6.34-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
171121 12:16:08     1 Connect   root@localhost on leon
            1 Query show databases
            1 Query show tables
            1 Field List    test
            1 Field List    test2
            1 Query show variables like 'log_queries_not_using_indexes'

第2章二进制日志

二进制日志不依赖于存储引擎，依赖于SQL层，记录和SQL语句有关的信息（在SQL层已经执行完成的语句，如果是事务，应当是已完成的事务），并以event的形式记录到二进制文件中，由于记录了所有的操作，所以可以提供基于时间点的数据库备份和还原以及主从同步等。

图片[1]|MySQL 日志管理（八）|leon的博客

2.1 二进制记录格式

实例2-1 假如t1表有1000万行

执行 update t1 set name='a'

row：行模式，记录数据行的变化过程，将记录上例的1000万行变化，虽然记录的数据量大，但是可以保证之后的恢复准确（推荐方式）
statement：语句模式，记录量少，只记录上例执行的update语句，将来恢复的时候有可能有问题
mixed：以上两者的混合模式，自动判断如果是函数就用row模式，如果是其他的就使用statement模式（不推荐）

2.2 二进制日志的管理

2.2.1 开启二进制日志

2.2.1.1 临时修改：

set sql_log_bin=1                       # 在会话级别临时修改为开启 1 或关闭 0

2.2.1.2 永久修改

[mysqld]
log-bin=/usr/local/mysql/data/mysql-bin   # 在全局打开binlog

2.2.2 设置二进制日志记录格式（建议是ROW）

2.2.2.1 修改配置文件

[mysqld]
log-bin=/usr/local/mysql/data/mysql-bin
binlog_format = ROW

2.2.2.2 命令行修改

mysql> SET GLOBAL binlog_format = 'STATEMENT';      # statement模式
mysql> SET GLOBAL binlog_format = 'ROW';            # row模式
mysql> SET GLOBAL binlog_format = 'MIXED';          # mixed模式

2.2.3 查看binlog设置

mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_error_action                     | IGNORE_ERROR         |
| binlog_format                           | ROW                  |
| binlog_gtid_simple_recovery             | OFF                  |
| binlog_max_flush_queue_time             | 0                    |
| binlog_order_commits                    | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_rows_query_log_events            | OFF                  |
| binlog_stmt_cache_size                  | 32768                |
| binlogging_impossible_mode              | IGNORE_ERROR         |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| simplified_binlog_gtid_recovery         | OFF                  |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
19 rows in set (0.00 sec)

2.2.4 查询二进制日志文件

2.2.4.1 操作系统层面查看二进制日志文件

[root@db02 ~]# ls -l /usr/local/mysql/data/mysql-bin*
-rw-rw---- 1 mysql mysql 120 Nov 21 10:20 /usr/local/mysql/data/mysql-bin.000001
-rw-rw---- 1 mysql mysql  39 Nov 21 10:20 /usr/local/mysql/data/mysql-bin.index
2.2.4.2   
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.07 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)

2.2.5 获取二进制日志的内容及事件

2.2.5.1 直接查看二进制日志内容

[root@db02 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171121 10:20:01 server id 1  end_log_pos 120 CRC32 0x250a11c6 Start: binlog v 4, server v 5.6.34-log created 171121 10:20:01 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
UY0TWg8BAAAAdAAAAHgAAAABAAQANS42LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABRjRNaEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcYR
CiU=
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2.2.5.2 通过binlog事件查看二进制日志内容

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

mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info                                                              |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.34-log, Binlog ver: 4                                      |
| mysql-bin.000001 | 120 | Query | 1 | 238 | use `test`; create table t3 (id int(4),name varchar(20))                       |
| mysql-bin.000001 | 238 | Query | 1 | 310 | BEGIN                                                                          |
| mysql-bin.000001 | 310 | Table_map | 1 | 358 | table_id: 72 (test.t3)                                                     |
| mysql-bin.000001 | 358 | Write_rows | 1 | 403 | table_id: 72 flags: STMT_END_F                                            |
| mysql-bin.000001 | 403 | Xid | 1 | 434 | COMMIT /* xid=14 */                                                              |
| mysql-bin.000001 | 434 | Query | 1 | 506 | BEGIN                                                                          |
| mysql-bin.000001 | 506 | Table_map | 1 | 554 | table_id: 72 (test.t3)                                                     |
| mysql-bin.000001 | 554 | Write_rows | 1 | 601 | table_id: 72 flags: STMT_END_F                                            |
| mysql-bin.000001 | 601 | Xid | 1 | 632 | COMMIT /* xid=15 */                                                              |
| mysql-bin.000001 | 632 | Rotate | 1 | 679 | mysql-bin.000002;pos=4                                                        |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
11 rows in set (0.02 sec)

二进制日志文件是按照events 事件记录命令的，其中BEGIN到COMMIT之间是一个命令最小发生单元（即执行的一条命令），每个事件在整个二进制文件中的相对位置号就是position号。

2.2.6 滚动二进制日志文件的方式

提示：滚动二进制日志文件即重新开始生成二进制日志文件。

2.2.6.1 刷新二进制日志

mysql> flush logs;

2.2.6.2 重启数据库

/etc/init.d/mysqld restart
# 或
systemctl restart mysql

2.2.7 截取二进制日志

查看当前所在的二进制日志文件

show master status

查看1号文件中的所有事件

show binlog events in ‘mysql-bin.000001’

获取有用事件的position号：即上一条命令中的Pos和End_log_pos列中的号码

+------------------+-----+-------------+-----------+-------------+--------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info               |
+------------------+-----+-------------+-----------+-------------+--------------------+

mysqlbinlog命令截取有用的一段的二进制日志

mysqlbinlog --start-position=120 --stop-position=434 mysql-bin.000001 >/tmp/inc.sql

使用source命令恢复

mysql> source /tmp/inc.sql

2.2.8 删除二进制日志

默认情况下，不会删除旧的日志文件。

2.2.8.1 根据存在时间删除日志：

SET GLOBAL expire_logs_days = 7;
# 或者
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

2.2.8.2 根据文件名删除日志：

PURGE BINARY LOGS TO 'mysql-bin.000002';  # 删除000002以前的日志，不含000002，即只删除000001

2.3 二进制日志管理实战

2.3.1 题目要求

创建环境：

删掉leon数据库，再次创建
在leon中创建表test，表结构自定义（id name age）
插入任意5行数据备用
删除id为2的行
更新id为5的行的name列为小明
以上每个操作都要单独提交

测试要求：

将数据库恢复为5题之前的状态
将数据库恢复为3题的状态

2.3.2 创建测试环境

mysql> drop database leon;
Query OK, 1 row affected (3.55 sec)

mysql> create database leon;
Query OK, 1 row affected (0.59 sec)

mysql> use leon;
Database changed
mysql> create table test (              
    -> id int(4) not null auto_increment,
    -> name varchar(20),                
    -> age int(3),                      
    -> primary key (id)                 
    -> );                                
Query OK, 0 rows affected (1.90 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1,'leon',18),(2,'shadow',20),(3,'shuaige',26),(4,'meinv',19),(5,'wangzi',28);
Query OK, 5 rows affected (0.88 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id=2;
Query OK, 1 row affected (0.70 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='xiaoming' where id=5;
Query OK, 0 rows affected (0.04 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | leon     |   18 |
|  3 | shuaige  |   26 |
|  4 | meinv    |   19 |
|  5 | xiaoming |   28 |
+----+----------+------+
4 rows in set (0.00 sec)

2.3.3 进行测试

2.3.3.1 查看二进制日志

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

mysql> show binlog events in 'mysql-bin.000010';
+------------------+------+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info                                            |
+------------------+------+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000010 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.34-log, Binlog ver: 4                    |
| mysql-bin.000010 | 120 | Query | 1 | 212 | drop database leon                                           |
| mysql-bin.000010 | 212 | Query | 1 | 306 | create database leon                                         |
| mysql-bin.000010 | 306 | Query | 1 | 556 | use `leon`; create table test ( 
id int(4) not null auto_increment,
name varchar(20), 
age int(3), 
primary key (id) 
)                                                                                                         |
| mysql-bin.000010 | 556 | Query | 1 | 628 | BEGIN                                                        |
| mysql-bin.000010 | 628 | Table_map | 1 | 679 | table_id: 74 (leon.test)                                 |
| mysql-bin.000010 | 679 | Write_rows | 1 | 792 | table_id: 74 flags: STMT_END_F                          |
| mysql-bin.000010 | 792 | Xid | 1 | 823 | COMMIT /* xid=26 */                                            |
| mysql-bin.000010 | 823 | Query | 1 | 895 | BEGIN                                                        |
| mysql-bin.000010 | 895 | Table_map | 1 | 946 | table_id: 74 (leon.test)                                 |
| mysql-bin.000010 | 946 | Delete_rows | 1 | 997 | table_id: 74 flags: STMT_END_F                         |
| mysql-bin.000010 | 997 | Xid | 1 | 1028 | COMMIT /* xid=28 */                                           |
| mysql-bin.000010 | 1028 | Query | 1 | 1100 | BEGIN                                                      |
| mysql-bin.000010 | 1100 | Table_map | 1 | 1151 | table_id: 74 (leon.test)                               |
| mysql-bin.000010 | 1151 | Update_rows | 1 | 1221 | table_id: 74 flags: STMT_END_F                       |
| mysql-bin.000010 | 1221 | Xid | 1 | 1252 | COMMIT /* xid=30 */                                          |
+------------------+------+-------------+-----------+-------------+---------------------------------------+
16 rows in set (0.00 sec)

2.3.3.2 导出二进制日志问文件

[root@db02 ~]# cd /usr/local/mysql/data/
[root@db02 data]# mysqlbinlog --start-position=120  --stop-position=1028 mysql-bin.000010 > /tmp/rollback5.sql
[root@db02 data]# mysqlbinlog --start-position=120  --stop-position=823 mysql-bin.000010 > /tmp/rollback3.sql

[root@db02 ~]# ll -h /tmp/rollback*
-rw-r--r-- 1 root root 2.6K Nov 21 14:19 /tmp/rollback3.sql
-rw-r--r-- 1 root root 3.3K Nov 21 14:19 /tmp/rollback5.sql

2.3.3.3 还原数据库

[root@db02 ~]# mysql < /tmp/rollback5.sql

mysql> select * from test;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | leon    |   18 |
|  3 | shuaige |   26 |
|  4 | meinv   |   19 |
|  5 | wangzi  |   28 |
+----+---------+------+
4 rows in set (0.00 sec)

[root@db02 ~]# mysql < /tmp/rollback3.sql

mysql> select * from test;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | leon    |   18 |
|  2 | shadow  |   20 |
|  3 | shuaige |   26 |
|  4 | meinv   |   19 |
|  5 | wangzi  |   28 |
+----+---------+------+
5 rows in set (0.00 sec)

2.4 二进制日志翻转实现闪回数据

mysqlbinlog_flashback 更偏重于阿里云 RDS环境的使用。
binlog2sql偏重于通常MySQL的处理。从代码上来看，该项目更简洁一点。

2.4.1 使用mysqlbinlog_flashback

2.4.1.1 使用mysqlbinlog_flashback

[root@db02 ~]# cd /server/tools/
[root@db02 tools]# unzip mysqlbinlog_flashback-master.zip
[root@db02 tools]# cd mysqlbinlog_flashback-master
[root@db02 mysqlbinlog_flashback-master]# python mysqlbinlog_back.py --host="127.0.0.1" --username="root" --port=3306 --password="" --schema=leon --table="test"

2.4.1.2 查看生成的文件

[root@db02 mysqlbinlog_flashback-master]# ll -h log/
total 12K
-rw-r--r-- 1 root root 1.3K Nov 21 15:45 flashback_leon_20171121_154517.sql
-rw-r--r-- 1 root root  209 Nov 21 15:45 save_data_create_table_leon_20171121_154517.sql
-rw-r--r-- 1 root root 2.6K Nov 21 15:45 save_data_dml_leon_20171121_154517.sql

[root@db02 log]# cat save_data_create_table_leon_20171121_154517.sql
CREATE TABLE `_test_keep_data_` (op varchar(64),op_datetime datetime,bfr_id int(4),bfr_name varchar(20),bfr_age int(3),aft_id int(4),aft_name varchar(20),aft_age int(3)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

[root@db02 log]# cat save_data_dml_leon_20171121_154517.sql
#end_log_pos 292  2017-11-21T15:04:54 1511247894 mysql-bin.000011;
insert into `_test_keep_data_`(`bfr_name`,`bfr_age`,`bfr_id`,`op_datetime`,`op`) values('leon',18,1,'2017-11-21 15:04:54','delete');
#end_log_pos 1431  2017-11-21T14:14:36 1511244876 mysql-bin.000011;
insert into `_test_keep_data_`(`aft_age`,`aft_id`,`op_datetime`,`aft_name`,`op`) values(18,1,'2017-11-21 14:14:36','leon','insert');
insert into `_test_keep_data_`(`aft_age`,`aft_id`,`op_datetime`,`aft_name`,`op`) values(20,2,'2017-11-21 14:14:36','shadow','insert');
insert into `_test_keep_data_`(`aft_age`,`aft_id`,`op_datetime`,`aft_name`,`op`) values(26,3,'2017-11-21 14:14:36','shuaige','insert');
insert into `_test_keep_data_`(`aft_age`,`aft_id`,`op_datetime`,`aft_name`,`op`) values(19,4,'2017-11-21 14:14:36','meinv','insert');
insert into `_test_keep_data_`(`aft_age`,`aft_id`,`op_datetime`,`aft_name`,`op`) values(28,5,'2017-11-21 14:14:36','wangzi','insert');
#end_log_pos 1636  2017-11-21T14:14:47 1511244887 mysql-bin.000011;
insert into `_test_keep_data_`(`bfr_name`,`bfr_age`,`bfr_id`,`op_datetime`,`op`) values('shadow',20,2,'2017-11-21 14:14:47','delete');
#end_log_pos 2275  2017-11-21T15:33:40 1511249620 mysql-bin.000011;
insert into `_test_keep_data_`(`aft_age`,`aft_id`,`op_datetime`,`aft_name`,`op`) values(18,1,'2017-11-21 15:33:40','leon','insert');
#end_log_pos 2480  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
insert into `_test_keep_data_`(`aft_age`,`aft_id`,`op_datetime`,`aft_name`,`op`) values(20,2,'2017-11-21 15:33:41','shadow','insert');
#end_log_pos 2686  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
insert into `_test_keep_data_`(`aft_age`,`aft_id`,`op_datetime`,`aft_name`,`op`) values(26,3,'2017-11-21 15:33:41','shuaige','insert');
#end_log_pos 2890  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
insert into `_test_keep_data_`(`aft_age`,`aft_id`,`op_datetime`,`aft_name`,`op`) values(19,4,'2017-11-21 15:33:41','meinv','insert');
#end_log_pos 3095  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
insert into `_test_keep_data_`(`aft_age`,`aft_id`,`op_datetime`,`aft_name`,`op`) values(28,5,'2017-11-21 15:33:41','wangzi','insert');
#end_log_pos 3300  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
insert into `_test_keep_data_`(`bfr_name`,`bfr_age`,`bfr_id`,`op_datetime`,`op`) values('shadow',20,2,'2017-11-21 15:33:41','delete');
#end_log_pos 3524  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_age`,`aft_name`,`bfr_id`,`bfr_age`,`op`) values('2017-11-21 15:33:41','wangzi',5,28,'xiaoming',5,28,'update');

[root@db02 log]# cat flashback_leon_20171121_154517.sql
#end_log_pos 292  2017-11-21T15:04:54 1511247894 mysql-bin.000011;
insert into `test`(`age`,`id`,`name`) values(18,1,'leon');
#end_log_pos 1431  2017-11-21T14:14:36 1511244876 mysql-bin.000011;
delete from `test` where `id`=1;
delete from `test` where `id`=2;
delete from `test` where `id`=3;
delete from `test` where `id`=4;
delete from `test` where `id`=5;
#end_log_pos 1636  2017-11-21T14:14:47 1511244887 mysql-bin.000011;
insert into `test`(`age`,`id`,`name`) values(20,2,'shadow');
#end_log_pos 2275  2017-11-21T15:33:40 1511249620 mysql-bin.000011;
delete from `test` where `id`=1;
#end_log_pos 2480  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
delete from `test` where `id`=2;
#end_log_pos 2686  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
delete from `test` where `id`=3;
#end_log_pos 2890  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
delete from `test` where `id`=4;
#end_log_pos 3095  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
delete from `test` where `id`=5;
#end_log_pos 3300  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
insert into `test`(`age`,`id`,`name`) values(20,2,'shadow');
#end_log_pos 3524  2017-11-21T15:33:41 1511249621 mysql-bin.000011;
update `test` set`age`=28,`id`=5,`name`='wangzi' where `id`=5;

2.4.1.3 还原数据库

可以根据生成的命令直接还原或使用source进行还原。

2.4.1.4 mysqlbinlog_flashback帮助信息

[root@db02 mysqlbinlog_flashback-master]# python mysqlbinlog_back.py --help
===log will also  write to .//mysqlbinlog_flashback.log===
Usage: python mysqlbinlog_back.py [options]
sample1:python  mysqlbinlog_back.py --host="127.0.0.1" --username="root" --port=43306 --password="" --schema=test --table="test5"
sample2:python mysqlbinlog_back.py --host="127.0.0.1" --username="root" --port=43306 --password="" --schema=test --table="test5,test6" --binlog_end_time="2016-11-05 11:27:13" --binlog_start_file_name="mysql-bin.000024"  --binlog_start_file_position=4 --binlog_start_time="2016-11-04 11:27:13"  --skip_delete  --skip_insert --add_schema_name
sample3:python mysqlbinlog_back.py  --host="127.0.0.1" --username="root" --port=43306 --password="" --schema=test --table="test5,test6" --binlog_start_file_name="mysql-bin.000022"

Options:
  -h, --help            show this help message and exit
  -H HOST, --host=HOST  mandatory,mysql hostname
  -P PORT, --port=PORT  mysql port,default 3306
  -u USERNAME, --username=USERNAME
                        mandatory,username
  -p PASSWORD, --password=PASSWORD
                        password
  -s SCHEMA, --schema=SCHEMA
                        mandatory,mysql schema
  -t TABLES, --tables=TABLES
                        mandatory,mysql tables,suport multiple tables,use
                        comma as separator
  -N BINLOG_END_TIME, --binlog_end_time=BINLOG_END_TIME
                        binlog end time,format yyyy-mm-dd hh24:mi:ss,default
                        is current time
  -S BINLOG_START_FILE_NAME, --binlog_start_file_name=BINLOG_START_FILE_NAME
                        binlog start file name,default is current logfile of
                        db
  -L BINLOG_START_FILE_POSITION, --binlog_start_file_position=BINLOG_START_FILE_POSITION
                        binlog start file name
  -E BINLOG_START_TIME, --binlog_start_time=BINLOG_START_TIME
                        binlog start time,format yyyy-mm-dd hh24:mi:ss
  -l OUTPUT_FILE_PATH, --output_file_path=OUTPUT_FILE_PATH
                        file path that sql generated,,default ./log
  -I, --skip_insert     skip insert(WriteRowsEvent) event
  -U, --skip_update     skip update(UpdateRowsEvent) event
  -D, --skip_delete     skip delete(DeleteRowsEvent) event
  -a, --add_schema_name
                        add schema name for flashback sql
  -v, --version         version info

2.4.2 使用binlog2sql

2.4.2.1 安装pip

[root@db02 ~]# yum install -y python-pip

2.4.2.2 安装binlog2sql

[root@db02 ~]# cd /server/tools/
[root@db02 tools]# unzip binlog2sql-master.zip
[root@db02 tools]# cd binlog2sql-master
[root@db02 binlog2sql-master]# pip install -r requirements.txt

2.4.2.3 配置mysql配置文件

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id = 1
log-bin=/usr/local/mysql/data/mysql-bin
max_binlog_size = 10M
binlog_format = row
binlog_row_image = full

[root@db02 ~]# /etc/init.d/mysqld restart

2.4.2.4 用户最小授权

select, super/replication client, replication slave
建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

2.4.2.5 解析出标准SQL

[root@db02 tmp]# python /server/tools/binlog2sql-master/binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -dleon -t test --start-file='mysql-bin.000010' --start-position=120 --stop-position=1252 > /tmp/create.sql

[root@db02 ~]# cat /tmp/create.sql
USE leon;
drop database leon;
USE leon;
create database leon;
USE leon;
create table test (              
id int(4) not null auto_increment,
name varchar(20),                
age int(3),                      
primary key (id)                 
);
INSERT INTO `leon`.`test`(`age`, `id`, `name`) VALUES (18, 1, 'leon'); #start 556 end 792 time 2017-11-21 14:14:36
INSERT INTO `leon`.`test`(`age`, `id`, `name`) VALUES (20, 2, 'shadow'); #start 556 end 792 time 2017-11-21 14:14:36
INSERT INTO `leon`.`test`(`age`, `id`, `name`) VALUES (26, 3, 'shuaige'); #start 556 end 792 time 2017-11-21 14:14:36
INSERT INTO `leon`.`test`(`age`, `id`, `name`) VALUES (19, 4, 'meinv'); #start 556 end 792 time 2017-11-21 14:14:36
INSERT INTO `leon`.`test`(`age`, `id`, `name`) VALUES (28, 5, 'wangzi'); #start 556 end 792 time 2017-11-21 14:14:36
DELETE FROM `leon`.`test` WHERE `age`=20 AND `id`=2 AND `name`='shadow' LIMIT 1; #start 823 end 997 time 2017-11-21 14:14:47
UPDATE `leon`.`test` SET `age`=28, `id`=5, `name`='xiaoming' WHERE `age`=28 AND `id`=5 AND `name`='wangzi' LIMIT 1; #start 1028 end 1221 time 2017-11-21 14:14:59

2.4.2.6 解析出回滚SQL

[root@db02 tmp]# python /server/tools/binlog2sql-master/binlog2sql/binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -dleon -t test --start-file='mysql-bin.000010' --start-position=120 --stop-position=1252 > rollback.sql

[root@db02 ~]# cat /tmp/rollback.sql
UPDATE `leon`.`test` SET `age`=28, `id`=5, `name`='wangzi' WHERE `age`=28 AND `id`=5 AND `name`='xiaoming' LIMIT 1; #start 1028 end 1221 time 2017-11-21 14:14:59
INSERT INTO `leon`.`test`(`age`, `id`, `name`) VALUES (20, 2, 'shadow'); #start 823 end 997 time 2017-11-21 14:14:47
DELETE FROM `leon`.`test` WHERE `age`=28 AND `id`=5 AND `name`='wangzi' LIMIT 1; #start 556 end 792 time 2017-11-21 14:14:36
DELETE FROM `leon`.`test` WHERE `age`=19 AND `id`=4 AND `name`='meinv' LIMIT 1; #start 556 end 792 time 2017-11-21 14:14:36
DELETE FROM `leon`.`test` WHERE `age`=26 AND `id`=3 AND `name`='shuaige' LIMIT 1; #start 556 end 792 time 2017-11-21 14:14:36
DELETE FROM `leon`.`test` WHERE `age`=20 AND `id`=2 AND `name`='shadow' LIMIT 1; #start 556 end 792 time 2017-11-21 14:14:36
DELETE FROM `leon`.`test` WHERE `age`=18 AND `id`=1 AND `name`='leon' LIMIT 1; #start 556 end 792 time 2017-11-21 14:14:36

2.4.2.7 还原数据库

[root@db02 ~]# mysql < create.sql
[root@db02 ~]# mysql < rollback.sql

2.4.2.8 命令选项

mysql连接配置

-h host;
-P port;
-u user;
-p password

解析模式

–stop-never 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。
-K, –no-primary-key 对INSERT语句去除主键。可选。
-B, –flashback 生成回滚语句，可解析大文件，不受内存限制，每打印一千行加一句SLEEP SELECT(1)。可选。与stop-never或no-primary-key不能同时添加。

解析范围控制

–start-file 起始解析文件。必须。
–start-position/–start-pos start-file的起始解析位置。可选。默认为start-file的起始位置。
–stop-file/–end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
–stop-position/–end-pos stop-file的末尾解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
–start-datetime 从哪个时间点的binlog开始解析，格式必须为datetime，如’2016-11-11 11:11:11’。可选。默认不过滤。
–stop-datetime 到哪个时间点的binlog停止解析，格式必须为datetime，如’2016-11-11 11:11:11’。可选。默认不过滤。

对象过滤

-d, –databases 只输出目标db的sql。可选。默认为空。
-t, –tables 只输出目标tables的sql。可选。默认为空。

第3章慢日志管理

慢查询日志是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件，可以记录所有条件内的慢的SQL语句，过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。

慢日志是优化的一种工具日志，可以帮我们定位问题。

3.1 慢日志设置

3.1.1 配置参数

slow_query_log：指定是否开启慢查询日志
slow_query_log_file：指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
log_queries_not_using_indexes：不使用索引的慢查询日志是否记录到索引，缺省值为OFF
# 以下为判断条件，建议只使用一种判断条件，否则可能会导致日志记录不准确
long_query_time：设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志，缺省值未设置

3.1.2 查看配置方式

[mysqld]
slow_query_log = on
slow_query_log_file = /tmp/mysql-slow.log
long_query_time = 0.5
log_queries_not_using_indexes

3.1.3 查看配置结果

mysql> show variables like '%slow_query_log%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| slow_query_log      | ON                  |
| slow_query_log_file | /tmp/mysql-slow.log |
+---------------------+---------------------+
2 rows in set (0.56 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.500000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like '%row_limit%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)

3.2 处理慢日志

3.2.1 mysqldumpslow命令

输出记录次数最多的5条SQL语句：

[root@db02 ~]# mysqldumpslow -s -c -t 5 /tmp/mysql-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句：

[root@db02 ~]# mysqldumpslow -s -c -t 5 -g "|left join" /tmp/mysql-slow.log

命令说明：

-s：表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙
-t：top n的意思，即为返回前面多少条的数据
-g：后边可以写一个正则匹配模式，大小写不敏感的

3.2.2 sla

未完待续

温馨提示：本文最后更新于2022-12-20 20:57:52，已超过486天没有更新。某些文章具有时效性，若文章内容或图片资源有错误或已失效，请联系站长。谢谢！
转载请注明本文链接：https://blog.leonshadow.cn/763482/722.html

1. 本网站名称：Leon的博客
2. 本站永久网址：https://blog.leonshadow.cn
3. 本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ632113590进行删除处理。
4. 本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5. 本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6. 本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END