第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) [[email protected] ~]# 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 [email protected] 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的形式记录到二进制文件中，由于记录了所有的操作，所以可以提供基于时间点的数据库备份和还原以及主从同步等。
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 操作系统层面查看二进制日志文件
[[email protected] ~]# 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 直接查看二进制日志内容
[[email protected] ~]# 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 @[email protected]@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 [email protected]_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 导出二进制日志问文件
[[email protected] ~]# cd /usr/local/mysql/data/ [[email protected] data]# mysqlbinlog --start-position=120 --stop-position=1028 mysql-bin.000010 > /tmp/rollback5.sql [[email protected] data]# mysqlbinlog --start-position=120 --stop-position=823 mysql-bin.000010 > /tmp/rollback3.sql
[[email protected] ~]# 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 还原数据库
[[email protected] ~]# 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) [[email protected] ~]# 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
[[email protected] ~]# cd /server/tools/ [[email protected] tools]# unzip mysqlbinlog_flashback-master.zip [[email protected] tools]# cd mysqlbinlog_flashback-master [[email protected] 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 查看生成的文件
[[email protected] 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
[[email protected] 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;
[[email protected] 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');
[[email protected] 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帮助信息
[[email protected] 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
[[email protected] ~]# yum install -y python-pip
2.4.2.2 安装binlog2sql
[[email protected] ~]# cd /server/tools/ [[email protected] tools]# unzip binlog2sql-master.zip [[email protected] tools]# cd binlog2sql-master [[email protected] binlog2sql-master]# pip install -r requirements.txt
2.4.2.3 配置mysql配置文件
[[email protected] ~]# 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
[[email protected] ~]# /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
[[email protected] 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
[[email protected] ~]# 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
[[email protected] 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
[[email protected] ~]# 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 还原数据库
[[email protected] ~]# mysql < create.sql [[email protected] ~]# 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语句：
[[email protected] ~]# mysqldumpslow -s -c -t 5 /tmp/mysql-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句：
[[email protected] ~]# 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
未完待续
我的微信
如果有技术上的问题可以扫一扫我的微信
