如何按需截取日志 #### 1. 基于position号的截取 * —start-position=### —stop-position=### 截取二进制日志核心在于，找起点和终点 [root@db01 ~]# mysqlbinlog —start-position=219 —stop-position=322 /data/binlog/mysql-bin.000002 >/tmp/bin.sql 恢复： mysql> drop database oldboy1; mysql> show databases; mysql> set sql_log_bin=0; mysql> source /tmp/bin.sql #### 2. 基于时间点的截取(了解) —start-datetime —stop-datetime for example: 2004-12-25 11:25:56 #### 案例恢复详解案例: 使用binlog日志进行数据恢复模拟: #1. create database binlog charset utf8mb4; #2. use binlog; create table t1(id int); #3. insert into t1 values(1); commit; insert into t1 values(2); commit; insert into t1 values(3); commit; #4. drop database binlog; 恢复: #1. 找到起点和终点 mysql> show master status; +—————————+—————+———————+—————————+—————————-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +—————————+—————+———————+—————————+—————————-+ | mysql-bin.000002 | 2196 | | | | +—————————+—————+———————+—————————+—————————-+ 1 row in set (0.00 sec) #2. 查看事件 mysql> show binlog events in ‘mysql-bin.000002’; | mysql-bin.000002 | 980 | Query | 6 | 1096 | create database binlog charset utf8mb4 | | mysql-bin.000002 | 2098 | Query | 6 | 2196 | drop database binlog 980-2098 # 3. 截取日志 [root@db01 ~]# mysqlbinlog —start-position=980 —stop-position=2098 /data/binlog/mysql-bin.000002 >/tmp/bak.sql # 4. 进行恢复 mysql> set sql_log_bin=0; mysql> source /tmp/bak.sql # 5. 验证数据 mysql> use binlog Database changed mysql> select * from t1; +———+ | id | +———+ | 1 | | 2 | | 3 | +———+
模拟 # 1 create database binlog charset utf8mb4; # 2. use binlog; create table t1(id int); #3. insert into t1 values(1); commit; insert into t1 values(2); commit; insert into t1 values(3); commit; # 4 drop database binlog; # 找到起点和终点 mysql> show master status; # 确认当前再用binlog mysql> show binlog events in ‘mysql-bin.000001’; | mysql-bin.000001 | 610 | Query | 6 | 726 | create database binlog charset utf8mb4 | mysql-bin.000001 | 1728 | Query | 6 | 1826 | drop database binlog #截取日期 [root@oldboy data]# mysqlbinlog —start-position=610 —stop-position=1728 /data/binlog/mysql-bin.000001 >/tmp/back.sql #恢复 mysql>set sql_Log_bin=0; mysql> source /tmp/back.sql;

日志管理
7.1 错误日志(log_error)

1.1 作用记录启动\关闭\日常运行过程中,状态信息,警告,错误 1.2 错误日志配置默认就是开启的: /数据路径下/hostname.err 手工设定: Master [(none)]>select @@log_error; [root@oldboy ~]# cat /etc/my.cnf log_error=/data/mysql/mysql.log 使用方法查看【error】上下文

7.2 binlog(二进制日志)

7.2.1 作用

数据恢复主从复制

7.2.2 如何配置

log_bin ： 1. 开关 2. 设定存放位置 server_id 1. 5.6 中不需要 2. 5.7 使用以上参数时必须加server_id 生产要求：日志要和数据分开存放 [root@db01 ~]# mkdir /data/binlog -p [root@db01 ~]# vim /etc/my.cnf log_bin=/data/binlog/mysql-bin server_id=6

7.2.3 如何查看配置

mysql> show variables like ‘%log_bin%’;

7.2.4 binlog记录了什么?

记录了数据库中所有变更类的操作 DDL DCL DML 详细的说明（1）对于DDL和DCL语句，记录发生过的语句（2）DML(IUD) 前提：已经提交的事务IUD 关于记录格式： ROW ：RBR 行记录模式，记录的是行的变化 STATEMENT ：SBR 语句记录模式，记录操作语句 MIXED ：MBR 混合记录模式电话面试的题目： delete from city where id>1000 RBR,逐行记录日志,日志量很大，可读性差。但是够严谨，不会出现记录错误 SBR,只记录语句本身，日志量很少，可读性较强。对于函数类的操作，将来恢复时会造错误。 5.7 版本默认是RBR，是企业建议模式查看: mysql> select @@binlog_format; +————————-+ | @@binlog_format | +————————-+ | ROW | +————————-+ 1 row in set (0.00 sec) mysql>

7.2.5 二进制日志事件（event）

简介二进制日志的最小记录单元对于DDL,DCL,一个语句就是一个event 对于DML语句来讲:只记录已提交的事务。例如以下列子,就被分为了4个event position号码 begin; 120 - 340 DML1 340 - 460 DML2 460 - 550 commit; 550 - 760 event的组成三部分构成: (1) 事件的开始标识 (2) 事件内容 (3) 事件的结束标识 Position: 开始标识: at 194 结束标识: end_log_pos 254 194? 254? 某个事件在binlog中的相对位置号位置号的作用是什么？为了方便我们截取事件

7.2.6 二进制日志位置的查看

查看二进制日志所在位置 mysql> show variables like ‘%log_bin%’; [root@db01 /data/binlog]# ls -l /data/binlog/ 总用量 12 -rw-r——- 1 mysql mysql 177 6月 25 09:35 mysql-bin.000001 -rw-r——- 1 mysql mysql 154 6月 25 09:35 mysql-bin.000002 -rw-r——- 1 mysql mysql 60 6月 25 09:35 mysql-bin.index #### 查看正在使用的二进制日志 mysql> show binary logs; +—————————+—————-+ | Log_name | File_size | +—————————+—————-+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 154 | +—————————+—————-+ 2 rows in set (0.00 sec) mysql> log_name : 目前MySQL存在的二进制日志名字 file_size: 目前mysql用到哪个position号 mysql> show master status ; +—————————+—————+———————+—————————+—————————-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +—————————+—————+———————+—————————+—————————-+ | mysql-bin.000002 | 322 | | | | +—————————+—————+———————+—————————+—————————-+ 1 row in set (0.00 sec) mysql>

7.2.7 查看二进制文件内容 *

查看二进制日志事件 mysql> show master status ; ## 确认当前再用的binlog mysql> show binlog events in ‘mysql-bin.000002’; ## 查看2号binlog的事件注释：每一行都是一个事件 Log_name ：日志名 Pos ：事件开始的position * Event_type ：事件类型 Server_id ：发生在哪台机器的事件 End_log_pos：事件结束的位置号 * Info ：事件内容 * 查看二进制日志内容 [root@db01 /data/binlog]# mysqlbinlog mysql-bin.000002 |grep -v “SET” >/tmp/aa.txt [root@db01 /data/binlog]# mysqlbinlog —base64-output=decode-rows -vvv mysql-bin.000002

7.2.8 基于二进制日志数据恢复案例

如何按需截取日志 #### 1. 基于position号的截取 * —start-position=### —stop-position=### 截取二进制日志核心在于，找起点和终点 [root@db01 ~]# mysqlbinlog —start-position=219 —stop-position=322 /data/binlog/mysql-bin.000002 >/tmp/bin.sql 恢复： mysql> drop database oldboy1; mysql> show databases; mysql> set sql_log_bin=0; mysql> source /tmp/bin.sql #### 2. 基于时间点的截取(了解) —start-datetime —stop-datetime for example: 2004-12-25 11:25:56 #### 案例恢复详解案例: 使用binlog日志进行数据恢复模拟: #1. create database binlog charset utf8mb4; #2. use binlog; create table t1(id int); #3. insert into t1 values(1); commit; insert into t1 values(2); commit; insert into t1 values(3); commit; #4. drop database binlog; 恢复: #1. 找到起点和终点 mysql> show master status; +—————————+—————+———————+—————————+—————————-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +—————————+—————+———————+—————————+—————————-+ | mysql-bin.000002 | 2196 | | | | +—————————+—————+———————+—————————+—————————-+ 1 row in set (0.00 sec) #2. 查看事件 mysql> show binlog events in ‘mysql-bin.000002’; | mysql-bin.000002 | 980 | Query | 6 | 1096 | create database binlog charset utf8mb4 | | mysql-bin.000002 | 2098 | Query | 6 | 2196 | drop database binlog 980-2098 # 3. 截取日志 [root@db01 ~]# mysqlbinlog —start-position=980 —stop-position=2098 /data/binlog/mysql-bin.000002 >/tmp/bak.sql # 4. 进行恢复 mysql> set sql_log_bin=0; mysql> source /tmp/bak.sql # 5. 验证数据 mysql> use binlog Database changed mysql> select * from t1; +———+ | id | +———+ | 1 | | 2 | | 3 | +———+

案例: 使用binlog日志进行数据恢复

模拟 # 1 create database binlog charset utf8mb4; # 2. use binlog; create table t1(id int); #3. insert into t1 values(1); commit; insert into t1 values(2); commit; insert into t1 values(3); commit; # 4 drop database binlog; # 找到起点和终点 mysql> show master status; # 确认当前再用binlog mysql> show binlog events in ‘mysql-bin.000001’; | mysql-bin.000001 | 610 | Query | 6 | 726 | create database binlog charset utf8mb4 | mysql-bin.000001 | 1728 | Query | 6 | 1826 | drop database binlog #截取日期 [root@oldboy data]# mysqlbinlog —start-position=610 —stop-position=1728 /data/binlog/mysql-bin.000001 >/tmp/back.sql #恢复 mysql>set sql_Log_bin=0; mysql> source /tmp/back.sql;

7.3 binlog日志的GTID新特性

思考问题？下面怎么恢复？ #1. create database binlog charset utf8mb4; #2. use binlog; create table t1(id int); #3. insert into t1 values(1); commit; insert into t1 values(2); commit; truncate table t1; insert into t1 values(3); commit; #4. drop database binlog; 基于position号恢复需要多次截取，找起点和终点过程很复杂。 #### 什么是GTID （全局事务编号） 5.6 版本新加的特性,5.7中做了加强 5.6 中不开启,没有这个功能. 5.7 中的GTID,即使不开也会有自动生成 SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ 是对于一个已提交事务的编号，并且是一个全局唯一的编号。它的官方定义如下： GTID = source_id ：transaction_id 7E11FA47-31CA-19E1-9E56-C43AA21293967:29 说明： DDL DCL，一条语句（事件）就是一个事务，占一个GTID号 DML：一个完整的事务(begin—》commit)，是一个事务，占一个GTID号 #### 开启GTID vim /etc/my.cnf gtid-mode=on enforce-gtid-consistency=true systemctl restart mysqld #### 查看本机GTID信息 mysql> create database gg; mysql> show master status; mysql> use gg; mysql> create table t1 (id int); mysql> insert into t1 values(1); mysql> commit; mysql> insert into t1 values(2); mysql> commit; mysql> insert into t1 values(3); mysql> commit; drop database gg; #### 基于GTID截取二进制日志 [root@db01 ~]# mysqlbinlog —include-gtids=’ee956c61-9653-11e9-8518-000c29099eb6:1-5’ /data/binlog/mysql-bin.000003 >/tmp/gtid.sql 注意：以上日志截取出来的日志不能直接恢复？为啥呢？ gtid的幂等性？ #### 正确的截取方法是 [root@db01 ~]# mysqlbinlog —skip-gtids —include-gtids=’ee956c61-9653-11e9-8518-000c29099eb6:1-5’ /data/binlog/mysql-bin.000003 >/tmp/gtids.sql #### 跳过某些gtid不截取 mysqlbinlog —skip-gtids —include-gtids=’ee956c61-9653-11e9-8518-000c29099eb6:1-5’ —exclude-gtids=’ee956c61-9653-11e9-8518-000c29099eb6:2,ee956c61-9653-11e9-8518-000c29099eb6:4’ /data/binlog/mysql-bin.000003 >/tmp/gtids.sql

7.4 二进制日志其他操作

临时关闭 set sql_log_bin=0; 说明：临时关闭二进制日志记录，退出mysql窗口可以恢复做数据恢复之前，使用以上参数 #### 自动清理参数： mysql> select @@expire_logs_days; 设置依据？至少是一个全备周期+1，企业建议至少2个全备周期+1 怎么设置？临时设置，重启失效 mysql> set global expire_logs_days=8; 永久设置，重启生效 vim /etc/my.cnf expire_logs_days=8 #### 手工清理 PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day; PURGE BINARY LOGS TO ‘mysql-bin.000003’; 注意:不要手工 rm binlog文件 1. my.cnf binlog关闭掉,启动数据库 2.把数据库关闭,开启binlog,启动数据库删除所有binlog,并从000001开始重新记录日志删除所有binlog，从000001开始（危险！！！！） mysql> reset master; #### 日志滚动重启数据库 flush logs mysqladmin -uroot -p flush-logs show variables like ‘%max_binlog_size%’; 备份加一些参数，会触发滚动日志

7.5 优化相关日志-slowlog

1.3.1 作用记录慢SQL语句的日志,定位低效SQL语句的工具日志 ### 1.3.2 开启慢日志(默认没开启) 开关: slow_query_log=1 文件位置及名字 slow_query_log_file=/data/mysql/slow.log 设定慢查询时间: long_query_time=0.1 没走索引的语句也记录: log_queries_not_using_indexes vim /etc/my.cnf slow_query_log=1 slow_query_log_file=/data/mysql/slow.log long_query_time=0.1 log_queries_not_using_indexes systemctl restart mysqld

7.6 mysqldumpslow 分析慢日志

mysqldumpslow -s c -t 10 /data/mysql/data/slow.log # 1.3.4 第三方工具(自己扩展) https://www.percona.com/downloads/percona-toolkit/LATEST/ yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5 toolkit工具包中的命令: ./pt-query-diagest /data/mysql/slow.log Anemometer基于pt-query-digest将MySQL慢查询可视化