MySQL 日志管理

一、工具日志管理

1. 错误日志

配置：
默认文件存放在数据目录下，HOSTNAME.err
手动指定路径：

vim /etc/my.cnf
log_error=/data/mysql/data/mysql.log

2. 二进制日志

二进制形式，记录MySQL 工作过程中，所有变更类（除了select和show）的操作日志
可以理解为记录的是SQL语句，逻辑性质日志。
配置：

server_id=51
log_bin=/data/binlog/mysql-bin
binlog_format=row
sync_binlog=1
说明：/data/binglog  提前创建好，并且授权，和数据目录放在不同的位置 mysql-bin 叫做文件名前缀
binlog_format=row    二进制日志记录格式
sync_binlog=1          双一标准，每次事务提交立即刷写binlog

[root@db01 ~]# vim /etc/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
log_bin=/data/binlog/mysql-bin
binlog_format=row
sync_binlog=1

---- 重启 mysql
[root@db01 ~]# /etc/init.d/mysqld  restart 
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 

--- 可以看到/data/binlog 中多了文件
[root@db01 ~]# ll /data/binlog/
total 8
-rw-r----- 1 mysql mysql 154 Sep 11 11:20 mysql-bin.000001
-rw-r----- 1 mysql mysql  30 Sep 11 11:20 mysql-bin.inde

2.1 binlog存储内容介绍
DDL，DCL：create，drop，alter….. 记录方式为Statement(SQL语句)模式记录
DML ：insert update delete 记录已经提交的DML语句
记录模式：

• statement（SBR）：语句模式，记录的就是SQL 语句本身
• ROW （RBR）：行模式，记录的是数据行的变化
• Mixed（MBR）：混合模式，自动判断用什么模式存储日志
  建议使用：RBR（> =5.7.7默认ROW）

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

RBR和SBR 优缺点：
500w数据，
update t1 set name=’zs’ where id>400w;

• SBR：语句模式记录，可读性强，日志量小，记录数据不够严谨，有可能出现记录错误，比如存了个NOW()，恢复的时候并不是当时的时间
• RBR：记录行变化，可读性差，日志量大，足够严谨。
  MySQL一些新特性，必须要基于ROW模式，GTID, GZ, MTS

2.2 binlog的事件介绍
event 事件，binlog 记录的最小单元
start position（文件中的相对位置号），开始事件
事件内容
stop position（文件中的相对位置号），结束事件

2.3 binlog的查看
1）日志文件位置：

mysql> select @@log_bin_basename;
+------------------------+
| @@log_bin_basename     |
+------------------------+
| /data/binlog/mysql-bin |
+------------------------+
1 row in set (0.00 sec)

2）查看所有日志文件名

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> 
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> flush logs;      
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)

3）查看Mysql正在使用的binglog

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

4）查看正在使用的binlog事件信息

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |        51 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids |        51 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)


---介绍
---Pos：事件开始的位置点（position）
---End_log_pos：事件结束的位置点（position）
---Info：事件内容

5）查看binlog内容

[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000003|grep -v 'SET'

语句看不懂，查看详细语句

[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvvv /data/binlog/mysql-bin.000003|grep -v 'SET'

2.4 binlog的截取
模拟环境准备：

mysql> use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> create table test(id int)charset utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(3);
Query OK, 1 row affected (0.00 sec)

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

mysql> 
mysql> insert into test values(4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(6);
Query OK, 1 row affected (0.00 sec)

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

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

要求：使用binlog 恢复 test表的数据
截取日志

mysql> show binlog events in 'mysql-bin.000003';
--- 起点：967
| mysql-bin.000003 | 967 | Query | 51 | 1083 | use `world`; create table test(id int)charset utf8mb4 |
--- 终点：2014
| mysql-bin.000003 | 2014 | Query | 51 | 2133 | use `world`; DROP TABLE `test` /* generated by server */ |
--- 截取：
mysqlbinlog --start-position=967--stop-position=2014  /data/binlog/mysql-bin.000003 >/tmp/t.sql

2.5 数据恢复

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/t.sql
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> use world
Database changed
mysql> 
mysql> select *from test
    -> ;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.01 sec)

补充：
200G 每天（23：00）全备，每天备份binlog，周三上午10点误删的表t1
恢复思路：
停业务，挂维护页
找临时库，恢复全备
截取全备之后，一直到删除之前的所有binlog
导出表，导入到生产
2.6 binlog扩展-gtid模式（5.6.9）
1）gtid（global trans）介绍
在传统模式的基础上，加入对于每个事物唯一的编号
DDL,DCL一条语句就是一个事务，会分配一个唯一的GTID号码
DML，从begin；xxx; commit；作为一个完整的事务，分配一个唯一的gtid号
这些号码是唯一，连续，具备幂等性的
2）gtid格式
server_uuid：TID

[root@db01 ~]# cd /data/mysql/data/
[root@db01 data]# cat auto.cnf 
[auto]
server-uuid=1e91fb5a-cd38-11e9-b843-000c29303796

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 1e91fb5a-cd38-11e9-b843-000c29303796 |
+--------------------------------------+
1 row in set (0.00 sec)

3）配置

vim /etc/my.cnf
gtid_mode=no
enforce_gtid_consistency=on

--- 重启
/etc/init.d/mysqld restart 

mysql>  show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

mysql> create database x;
Query OK, 1 row affected (0.01 sec)
  
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000005 |      304 |              |                  | 1e91fb5a-cd38-11e9-b843-000c29303796:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> create database ha;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 |      457 |              |                  | 1e91fb5a-cd38-11e9-b843-000c29303796:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

4）gtid日志截取

[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='a7f67e4b-cd4e-11e9-aaa3-000c292e8458:1-4' --exclude-gtids='a7f67e4b-cd4e-11e9-aaa3-000c292e8458:2' /data/binlog/mysql-bin.000004 >/tmp/1.sql

结论：在开启GTID 模式的binlog截取时，都要加–skip-gtids

2.7 binlog的删除

1）什么时候可以删除binlog
保证有一份可用的全备，看全备的周期，
2）删除
自动删除：
日志过期时间，自动清理

mysql> select @@expire_logs_days 
    -> ;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

vim /etc/my.cnf
expire_logs_days=15

一般会保留两个全备周期+1天数，比如7天全备一次，保留时间15天
手动删除：

Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |      2340 |
| mysql-bin.000004 |       177 |
| mysql-bin.000005 |       613 |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> purge binary logs to 'mysql-bin.000003';
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |      2340 |
| mysql-bin.000004 |       177 |
| mysql-bin.000005 |       613 |
+------------------+-----------+
3 rows in set (0.00 sec)

reset master;
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

2.8 binlog滚动

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> flush logs;      
Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

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

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       154 |
+------------------+-----------+
5 rows in set (0.00 sec)

重启会滚动

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> flush logs;      
Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

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

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       154 |
+------------------+-----------+
5 rows in set (0.00 sec)

3、慢日志

3.1 介绍
以文本格式，记录MySQL 较慢的SQL语句，追加记录方式
3.2 配置

slow_query_log=1
slow_query_log_file=/data/mysql/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes=1

mysql> show variables like '%slow%';
+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | OFF                            |
| slow_query_log_file       | /data/mysql/data/db01-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.01 sec)

mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+
1 row in set (0.00 sec)

在配置文件中写入以下配置

vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/mysql/data/db01-slow.log 
long_query_time=0.1
log_queries_not_using_indexes=1

1.3.4
测试语句查询时间

[root@db01 ~]# mysqldumpslow -s c  -t 3  /data/mysql/data/db01-slow.log

Reading mysql slow query log from /data/mysql/data/db01-slow.log
Count: 3  Time=0.63s (1s)  Lock=0.00s (0s)  Rows=1.3 (4), root[root]@localhost
  select * from t100w where num=N

Count: 1  Time=0.37s (0s)  Lock=0.00s (0s)  Rows=265.0 (265), root[root]@localhost
  select k2 from t100w where k2='S'

Count: 1  Time=0.38s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  select * from t100w where k1='S'