十五、Mysql数据库日志

一、日志分类

MySql日志类型	解析说明
错误日志（error log）	当数据库启动、运行、停止时产生该日志
普通查询日志（general query log）	客户端连接数据库执行语句时产生该日志
二进制日志（binary log）	当数据库内容发生改变时产生该日志，也被用来实现主从复制功能
中继日志（relay log）	从库上收到主库的数据更新时产生该日志
慢查询日志（show query log）	SQL语句在数据库查询超过指定时间时产生该日志
DDL日志（metadata log）	执行DDL语句操作元数据时产生该日志

1、错误日志

错误日志说明

Mysql的错误日志用于记录mysql服务进程mysqld在启动、关闭或运行过程中遇到的错误信息。
通常由mysqld或mydql_safe程序产生。
记录启动关闭日常运行过程中,状态信息,警告,错误

错误日志的配置

默认就是开启的:  /数据路径下/hostname.err
手工设定:
查看log_error路径
[(none)]>select @@log_error;
配置文件配置log_error
vim /etc/my.cnf
log_error=/var/log/mysql.log
log_timestamps=system
重启生效
show variables like 'log_error';

查看日志结果：

[root@mysql-2 ~]# tail -n 4 /application/mysql-5.6.36/data/mysql-2.err 
2019-06-15 03:08:26 28735 [Note] Server socket created on IP: '::'.
2019-06-15 03:08:26 28735 [Note] Event Scheduler: Loaded 0 events
2019-06-15 03:08:26 28735 [Note] /application/mysql-5.6.36/bin/mysqld: ready for connections.
Version: '5.6.36-log'  socket: '/application/mysql-5.6.36/tmp/mysql.sock'  port: 3306  Source distribution

错误日志轮询

管理员可以使用命令来轮询错误日志，按天轮询
#进入日志目录
[root@mysql-2 ~]# cd /application/mysql-5.6.36/data/
#将错误日志移动改名
[root@mysql-2 data]# mv mysql-2.err mysql-2_$(date +%F).err
#执行刷新日志命令
[root@mysql-2 data]# mysqladmin flush-logs
#查看新的错误日志
[root@mysql-2 data]# ls -l *.err
-rw-rw---- 1 mysql mysql 28491 Jun 15 03:08 mysql-2_2019-06-15.err
-rw-rw---- 1 mysql mysql     0 Jun 15 10:17 mysql-2.err

安装数据库故障排除

安装完成新数据库无法启动
1、先清空错误日志，启动mysql数据库，查看错误日志内的错误信息经行处理
2、初始化参数错误，删除数据文件，重新初始化数据库

2、普通查询日志

普通查询日志说明

普通查询日志的作用是记录客户端的连接信息及执行的SQL语句信息。

普通查询日志的配置

普通查询日志的重要性很低，默认情况下该日志是关闭的
mysql> show variables like '%general_log%';
+------------------+--------------------------------------------+
| Variable_name    | Value                                      |
+------------------+--------------------------------------------+
| general_log      | OFF                                        |
| general_log_file | /application/mysql-5.6.36/data/mysql-2.log |
+------------------+--------------------------------------------+
2 rows in set (0.00 sec)

如果希望永久开启，在my.cnf的[mysqld]模块下配置
[mysqld]
general_log=on
general_log_file=/application/mysql-5.6.36/data/mysql_gerenal.log

执行几条命令后，查看普通日志
[root@mysql-2 ~]# tail /application/mysql/data/mysql-2_general.log 
/application/mysql-5.6.36/bin/mysqld, Version: 5.6.36-log (Source distribution). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
[root@mysql-2 ~]# tail /application/mysql/data/mysql-2_general.log 
190615 15:13:03        1 Connect    root@localhost on 
            1 Query    select @@version_comment limit 1
190615 15:13:10        1 Query    SELECT DATABASE()
            1 Init DB    test
            1 Query    show databases
            1 Query    show tables
            1 Field List    stu 
            1 Field List    test 
            1 Field List    test1 
190615 15:13:18        1 Query    show tables

在高并发数据库的场景下，建议关闭普通查询日志（默认为关闭），因为查询日志的信息量很大，容易导致磁盘I/O性能问题。当访问量不是很大，企业又有审计执行的SQL语句的需求时，可以考虑开启。

3、二进制日志

二进制日志的说明

二进制日志的时记录数据库里的数据被修改的SQL语句，一般为DDL、DCL和DML语句，例如：grant、insert、update、delete、create、drop、alter等关键字的语句。
DDL ：原封不动的记录当前DDL(statement语句方式)。
DCL ：原封不动的记录当前DCL(statement语句方式)。
DML ：只记录已经提交的事务DML
具体作用：
1、记录mysql数据的增量数据，用来做增量数据库恢复，没有二进制日志功能，mysql的备份将无法完成的还原数据
2、实现主从复制

注意：MySQL默认是没有开启二进制日志的。
基础参数查看:
开关:
[(none)]>select @@log_bin;
日志路径及名字
[(none)]>select @@log_bin_basename;
服务ID号:
[(none)]>select @@server_id;
二进制日志格式:
[(none)]>select @@binlog_format;
双一标准之二:
[(none)]>select @@sync_binlog;

Event事件

什么是事件

事件是二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
create database ywx;
grant all on *.* to root@'%' identified by '123456';
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
begin;      120  - 340     一个event
DML1        340  - 460     一个event
DML2        460  - 550     一个event
commit;     550  - 760     一个event

event的组成

 

三部分构成:
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识
Position:
开始标识: at 194
结束标识: end_log_pos 254
194? 254?
某个事件在binlog中的相对位置号
下一个event的开始标识号=上一个event的结束标识号
位置号的作用是什么？
为了方便我们截取事件，来恢复数据

二进制日志的配置

二进制日志实在my.cnf的[mysqld]模块下配置
[mysqld]
server_id=201
log_bin=/application/mysql-5.6.36/data/mysql-bin
binlog_format=row  

binlog_format
mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
 

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON  #记录binlog开关                                   |
| sql_log_bin                     | ON  #临时不记录binlog开关                               
+---------------------------------+-----------------------------------------+

sql_log_bin可是临时关闭binlog，只是当前session窗口有效，不影响其他会话

mysql> set sql_log_bin=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
| log_bin_basename                | /application/mysql/data/mysql-bin       |
| log_bin_index                   | /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                     |
| log_bin_use_v1_row_events       | OFF                                     |
| sql_log_bin                     | OFF                                     |
+---------------------------------+-----------------------------------------+
6 rows in set (0.00 sec)

重新开启一个session会话窗口
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
| log_bin_basename                | /application/mysql/data/mysql-bin       |
| log_bin_index                   | /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                     |
| log_bin_use_v1_row_events       | OFF                                     |
| sql_log_bin                     | ON                                      |
+---------------------------------+-----------------------------------------+
6 rows in set (0.00 sec)

该会话下sql_log_bin=ON开启的

查看sql_log_bin=OFF是否生效

mysql> set sql_log_bin=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       143 |
| mysql-bin.000002 |       376 |
| mysql-bin.000003 |       143 |
| mysql-bin.000004 |       120 | ###最新的binlog文件及位置点
+------------------+-----------+
4 rows in set (0.00 sec)

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

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       143 |
| mysql-bin.000002 |       376 |
| mysql-bin.000003 |       143 |
| mysql-bin.000004 |       120 |###最新的binlog文件及位置点
+------------------+-----------+
4 rows in set (0.00 sec)

binlog日志没有变化

mysql> system mysqlbinlog mysql-bin.000004|grep king;
mysql>
过滤binlog文件，没有记录binlog

开启sql_log_bin=ON

mysql> set sql_log_bin=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database king;
Query OK, 0 rows affected (0.17 sec)

mysql> system mysqlbinlog mysql-bin.000004|grep king;
drop database king
mysql> 
发现了有记录的binlog

sql_bin_log的功能实在用户使用mysql恢复数据时，不希望恢复的数据SQL记录到binLog里面

二级制日志文件的刷新条件

1、数据库重启会自动刷新binlog新文件
2、执行"mysqldump -F" 或"mysqladmin flush-logs"会将binlog刷新为新文件
3、binlog文件达到1GB时，会自动刷新binlog为新文件
4、人为配置切割及调整
binlog最大值控制参数及默认大小查看：

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

二进制日志索引文件

 

二进制日志除了很多按序列生成的binlog文件列表外，还有一个索引文件

[root@mysql-2 data]# pwd
/application/mysql-5.6.36/data
[root@mysql-2 data]# ls -l mysql-bin*
-rw-rw---- 1 mysql mysql 143 Jun 15 03:08 mysql-bin.000001
-rw-rw---- 1 mysql mysql 376 Jun 15 10:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 Jun 15 15:12 mysql-bin.000003
-rw-rw---- 1 mysql mysql 299 Jun 15 15:49 mysql-bin.000004
-rw-rw---- 1 mysql mysql 164 Jun 15 15:12 mysql-bin.index

索引文件名与binlog文件一样，只是扩展名为index，索引文件的内容
[root@mysql-2 data]# cat mysql-bin.index 
/application/mysql/data/mysql-bin.000001
/application/mysql/data/mysql-bin.000002
/application/mysql/data/mysql-bin.000003
/application/mysql/data/mysql-bin.000004

binlog索引文件的控制参数、
mysql> show variables like 'log_bin_index';
+---------------+-----------------------------------------+
| Variable_name | Value                                   |
+---------------+-----------------------------------------+
| log_bin_index | /application/mysql/data/mysql-bin.index |
+---------------+-----------------------------------------+
1 row in set (0.00 sec)

删除二进制日志的方法（谨慎操作！！！）

 

binlog日志很重要，不要随意删除。
确定什么时候可以删除binlog
每天的数据库**全备时刻**,以前的binlog无用了，实际企业中需要保留3-7天的本地binlog文件按。

方法1：
设置参数自动删除binlog
expire_logs_days = 7 ##设置删除7天前的日志
默认该参数是没有配置的
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

在全局下设置

mysql> set global expire_logs_days=7;
Query OK, 0 rows affected (0.00 sec)

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

方法2：
从最开始一直删除到指定的文件位置（不含指定文件）
一般用于处理临时的需求

做一个备份
[root@mysql-2 data]# cp mysql-bin.* /tmp

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       143 |
| mysql-bin.000002 |       376 |
| mysql-bin.000003 |       143 |
| mysql-bin.000004 |       299 |
+------------------+-----------+
4 rows in set (0.00 sec)

删除到mysql-bin.000002的binlog(不包含mysql-bin.000002)
只删除mysql-bin.000001

mysql> purge binary logs to 'mysql-bin.000002';
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       376 |  #####000002以前的就没有了
| mysql-bin.000003 |       143 |
| mysql-bin.000004 |       299 |
+------------------+-----------+
3 rows in set (0.00 sec)

方法3：
按照时间删除binlog日志
用于处理临时需求

[root@mysql-2 data]# ls -l --time-style=long-iso mysql-bin*
-rw-rw---- 1 mysql mysql 376 2019-06-15 10:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 2019-06-15 15:12 mysql-bin.000003
-rw-rw---- 1 mysql mysql 299 2019-06-15 15:49 mysql-bin.000004
-rw-rw---- 1 mysql mysql 123 2019-06-15 16:15 mysql-bin.index

删除"2019-06-15:23"删除mysql-bin.000002和mysql-bin.000003
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       376 |
| mysql-bin.000003 |       143 |
| mysql-bin.000004 |       299 |
+------------------+-----------+
3 rows in set (0.00 sec)

mysql> system ls -l --time-style=long-iso mysql-bin*;
-rw-rw---- 1 mysql mysql 376 2019-06-15 10:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 2019-06-15 15:12 mysql-bin.000003
-rw-rw---- 1 mysql mysql 299 2019-06-15 15:49 mysql-bin.000004
-rw-rw---- 1 mysql mysql 123 2019-06-15 16:15 mysql-bin.index

mysql> purge master logs before '2019-06-15:23';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> system ls -l --time-style=long-iso mysql-bin*;
-rw-rw---- 1 mysql mysql 299 2019-06-15 15:49 mysql-bin.000004
-rw-rw---- 1 mysql mysql  41 2019-06-15 16:22 mysql-bin.index

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

方法4：
删除所有的binlog,并从000001开始重新记录
reset master命令可以清楚所欲的binlog文件，并从000001开始重新记录

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

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

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

mysql> system ls -l --time-style=long-iso mysql-bin*;
-rw-rw---- 1 mysql mysql 120 2019-06-15 16:26 mysql-bin.000001
-rw-rw---- 1 mysql mysql  41 2019-06-15 16:26 mysql-bin.index

binlog的常用相关参数

查看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 |
+-----------------------------------------+--------------+
12 rows in set (0.00 sec)

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
| log_bin_basename                | /application/mysql/data/mysql-bin       |
| log_bin_index                   | /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                     |
| log_bin_use_v1_row_events       | OFF                                     |
| sql_log_bin                     | ON                                      |
+---------------------------------+-----------------------------------------+
6 rows in set (0.00 sec)

1、binlog_cache_size:
二进制日志缓存时数据库为每一个客户连接分配的内存空间。对于事务引擎来说，适当调整该参数可以获得更好的性能
默认值：
mysql> show variables like '%binlog_cache%';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| binlog_cache_size     | 32768                |
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
2 rows in set (0.00 sec)

2、max_binlog_size:
该参数用于设置binlog日志的最大大小，默认为1GB，但是该值并不能严格控制binlog的大小。若binlog大小接近1GB，而此时又在执行一个较大的事务，那么为了保证事务的完整性，数据库不会做日志刷新动作，而是直到该事务的日志全部记录进入当前binlog日志后才会进行刷新。
默认值：
mysql> show variables like '%max_binlog_size%';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

3、sync_binlog
这个参数的作用是控制binlog什么时候同步到磁盘。对数据库来说，这是很重要的参数，它不仅会影响数据库的性能，还会影响数据库数据的完整性。
对于“sync_binlog”参数的说明具体如下。
·“sync_binlog=0”表示在事务提交之后，数据库不会将binlog_cache中的数据刷新到磁盘，而是让文件系统自行决定什么时候来做刷新或者在缓存满了之后才刷新到磁盘。
·“sync_binlog=n”表示每进行n次事务提交之后，数据库都会进行一次将缓存数据强制刷新到磁盘的操作。
该参数默认的设置是0，示例如下：
mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)
设置为0时数据库的性能是最好的，但数据风险也是最大的，对于数据安全性要求较高的数据库，应该调整该参数将其改为1，值得注意的是，即使参数设置为1，仍然有binlog记录的内容与数据库的实际内容不一致的风险。

可以在my.cnf下的[mysqld]模块设置
[mysqld]
sync_binlog=1

记录二进制日志的三种模式

(1)语句（statement-based）模式是MySQL5.6版本默认的模式，简单地说，就是每一条被修改的数据的SQL语句都会记录到master的binlog中。在复制slave库的时候，SQL进程会解析成与原来master端执行过的相同的SQL来再次执行。
该模式的优点是不需要记录细到每一行数据的更改变化，因此，可减少binlog日志量，实际上是减少了很多，节约了磁盘I/O，提高了系统性能。
但该模式同样有一些缺点，由于语句模式记录的是执行的SQL语句，所以，对于某些具有特殊功能的SQL语句来说，就可能会导致无法在从库上正确执行，从而导致主从库数据不一致的问题。
例如，当特殊的函数被执行时，当触发器、存储过程等特殊功能被执行时，而row level模式是基于每一行来记录变化的，所以不会出现类似的问题

(2)行级模式
简单地说，行级（row-based）模式就是将数据被修改的每一行的情况记录为一条语句。
优点：在行级模式下，binlog中可以不记录执行的SQL语句的上下文相关信息，仅仅记录哪一条记录被修改了，修改成什么样了即可，所以row level的日志内容会非常清楚地记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题。
缺点：行级模式下，所有的执行语句都将根据修改的行来记录，而这就可能会产生大量的日志内容，例如一条语句修改了100万行，语句模式就用一条语句即可搞定，而行级模式执行之后，日志中记录的就是100万行的修改记录，binlog日志的量可能会大得惊人。

（3）混合模式
混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于以下情况。
当函数中包含UUID()时。
当表中有自增列（AUTO_INCREMENT）被更新时。
·当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。
·当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。

SBR与RBR模式的对比

 

STATEMENT：可读性较高，日志量少，但是不够严谨
ROW      ：可读性很低，日志量大，足够严谨
update t1 set xxx=xxx where id>1000   ? -->一共500w行，row模式怎么记录的日志
为什么row模式严谨？
statement默认仅仅只记录update t1 set xxx=xxx where id>1000这一条语句，日志少，不严谨
ROW：记录500w行数据的变化，日志多，严谨

严谨举例说明
id  name    intime
insert into t1 values(1,'zs',now())
在使用now()函数时，时间为当前时间
statement模式记录为insert into t1 values(1,'zs',now())语句，恢复数据时，now()调用是当前时间，儿非数据当时的时间，数据有问题。
row记录的是行的变化，在恢复时，恢复时间的时间与原时间一致。

我们建议使用：row记录模式

二进制日志模式的选择

在互联网公司中，使用MySQL的特殊功能比较少（存储过程、触发器、函数），此时可以选择默认的语句模式。
如果公司较多用到MySQL的特殊功能，如存储过程、触发器、函数等，并且需要做主从复制请首选行级模式，次选mixed模式。

二进制日志模式的配置

 

临时配置
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

永久配置
在my.cnf的mysqld模块下配置
[mysqld]
binlog_format=statement
binlog_format=row
binlog_format=mixed

行模式二进制日志实际读取

 

mysql> use test;
mysql> INSERT INTO test1 values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

[root@mysql-2 data]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001
#<==--base64-output=decode-rows -v以行级模式解析binlog日志。
.......
BEGIN
/*!*/;
# at 192
#190615 19:17:34 server id 1  end_log_pos 243 CRC32 0x35c8b48a     Table_map: `test`.`test1` mapped to number 72
# at 243
#190615 19:17:34 server id 1  end_log_pos 292 CRC32 0x3d5c2369     Write_rows: table id 72 flags: STMT_END_F
### INSERT INTO `test`.`test1`
### SET
###   @1=1
###   @2='a'
### INSERT INTO `test`.`test1`
### SET
###   @1=2
###   @2='b'
# at 292
#190615 19:17:34 server id 1  end_log_pos 323 CRC32 0x3e0a646e     Xid = 61
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mysql-2 data]# 

4、日志文件查看

查看日志的开启情况

log_bin参数设置的路径,可以找到二进制日志
[(none)]>show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------+
| log_bin                         | ON                           |
| log_bin_basename                | /data/binlog/mysql-bin       |
| log_bin_index                   | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                          |
| log_bin_use_v1_row_events       | OFF                          |
| sql_log_bin                     | ON                           |
+---------------------------------+------------------------------+
6 rows in set (0.01 sec)

查看一共多少个binlog

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

#切日志
Master [(none)]>flush logs;
Query OK, 0 rows affected (0.03 sec)

Master [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)

Master [(none)]>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)

[(none)]>

查看mysql正在使用的日志文件

[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
Master [(none)]>

file：当前MySQL正在使用的文件名

Position：最后一个事件的结束位置号

日志内容查看

[binlog]>show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.20-log, Binlog ver: 4  |
| mysql-bin.000003 | 123 | Previous_gtids |         6 |         154 |                                        |
| mysql-bin.000003 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 | 219 | Query          |         6 |         319 | create database binlog                 |
| mysql-bin.000003 | 319 | Anonymous_Gtid |         6 |         384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 | 384 | Query          |         6 |         486 | use `binlog`; create table t1 (id int) |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+

Log_name：binlog文件名
Pos：开始的position    *****
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：mysql服务号标识
End_log_pos：事件的结束位置号 *****
Info：事件内容*****
补充:
SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]
[root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop

binlog文件内容详细查看

mysqlbinlog /data/mysql/mysql-bin.000006
#查看row模式的二进制日志
mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000003
#查看binlog库的二进制日志
mysqlbinlog  -d binlog /data/binlog/mysql-bin.000003
#查看某个时间的二进制日志
[root@db01 binlog]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00'  /data/binlog/mysql-bin.000004 

基于Position号进行日志截取

 

核心就是找截取的起点和终点
--start-position=321
--stop-position=513
 mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql

案例: 使用binlog日志进行数据恢复
模拟:
1. 
[(none)]>create database binlog charset utf8;
2. 
[(none)]>use binlog;
[binlog]>create table t1(id int);
3. 
[binlog]>insert into t1 values(1);
[binlog]>commit;
[binlog]>insert into t1 values(2);
[binlog]>commit;
[binlog]>insert into t1 values(3);
[binlog]>commit;
4. 
[binlog]>drop database binlog;
恢复:
[(none)]>show master status ;
[(none)]>show binlog events in 'mysql-bin.000004';
[root@db01 binlog]# mysqlbinlog --start-position=1227 --stop-position=2342 /data/binlog/mysql-bin.000004 >/tmp/bin.sql
[(none)]>set sql_Log_bin=0;
[(none)]>source /tmp/bin.sql

案例:
1. 备份策略每天全备,有全量的二进制日志
2.业务中一共10个库,其中一个被误drop了
3. 需要在其他9个库正常工作过程中进行数据恢复

5、binlog日志的GTID新特性

GTID 介绍

5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

GTID(Global Transaction ID)

 

是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它的官方定义如下：

GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29

source_id为数据库初始化后生成的唯一标识，默认在数据目录中的auto.cnf中。删除后，重启数据库会自动生成。

配置及重要参数介绍

在mysql的配置文件中配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
systemctl restart mysqld

[(none)]>create database gtid charset utf8;
Query OK, 1 row affected (0.01 sec)

Master [(none)]>show master status ;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000004 |      326 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

[(none)]>use gtid
Database changed
Master [gtid]>create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

[gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |      489 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

[gtid]>create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

[gtid]>create table t3 (id int);
Query OK, 0 rows affected (0.02 sec)

[gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |      815 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

Master [gtid]>begin;
Query OK, 0 rows affected (0.00 sec)

Master [gtid]>insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

Master [gtid]>commit;
Query OK, 0 rows affected (0.00 sec)

Master [gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |     1068 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

[gtid]>begin;
Query OK, 0 rows affected (0.00 sec)

[gtid]>insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)

[gtid]>commit;
Query OK, 0 rows affected (0.01 sec)

[gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |     1321 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

基于GTID进行查看binlog

具备GTID后,截取查看某些事务日志:
--include-gtids 
--exclude-gtids  排除
mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4'  /data/binlog/mysql-bin.000004

排除多个gtid
mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4,dff98809-55c3-11e9-a58b-000c2928f5dd:5'  /data/binlog/mysql-bin.000004

GTID的幂等性

 

开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了
就想恢复?怎么办?
--skip-gtids
mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:4' /data/binlog/mysql-bin.000004 /data/binlog/mysql-bin.000004
set sql_log_bin=0;
source /tmp/binlog.sql
set sql_log_bin=1;

6、慢查询日志

慢查询日志说明

慢查询日志（slow query log）是记录执行时间超出指定值（long_query_time）或其它地址条件（如：没有使用索引，结果集大于10000行）的SQL语句

慢查询日志的参数说明

慢查询参数	解释说明
slow_query_log	慢查询开启开关，默认为”OFF“
slow-query-log-file	记录慢查询语句的文件，文件名如"hostname-slow.log"
long_query_time	记录大于指定N秒的SQL语句，默认为10s，也可以使用微秒单位
log_queries_not_using_indexes	记录没有使用到索引的SQL语句，默认为"OFF"
min_examined_row_limit	记录结果大于N行的SQL语句，默认为0行
log_slow_admin_statements	记录管理的慢SQL语句，如：alter table, analyze table, check table, create table, create index, drop index, optimize table, repair table
log_throttle_queries_not_using_indexes	限制每分钟写入记录的慢SQL语句的数量，默认为"0",表示没有限制

企业中常见的慢查询配置参数

在my.cnf的[mysqld]模块下配置
[mysqld]
slow-query-log = ON                        #<==慢查询开启开关
long_query_time = 2                        #<==记录大于2秒的SQL语句。
log_queries_not_using_indexes = ON         #<==没有使用到索引的SQL语句。
slow-query-log-file = /application/mysql-5.6.36/slow.log  #<==记录SQL语句的文件。
min_examined_row_limit = 800               #<==记录结果集大于800行的SQL语句。

测试检查

mysql> show variables like 'slow_query%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | ON                          | #开启慢查询日志
| slow_query_log_file | /application/mysql-5.6.26/slow.log | #文件路径
+---------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql> show variables like '%long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 | #记录大于2s的查询
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like '%log_queries_not%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    | #记录没有使用索引的查询
+-------------------------------+-------+
1 row in set (0.00 sec)


mysql> show variables like '%min_examined_row_limit%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 800   | #记录查询结果大于800行的SQL语句
+------------------------+-------+
1 row in set (0.00 sec)

慢查询日志的刷新方法

cd /application/mysql-5.6.36/
mv slow.log slow.log.$(date +%F)
mysqladmin flush-log

制作脚本放入定时任务
脚本：
在/server/scripts下创建脚本
vim /erver/scripts/cut_slow_log.sh

#!/bin/bash
export PATH=/application/mysql-5.6.36/bin:/sbin:/bin:/usr/bin:/usr/sbin
cd /application/mysql-5.6.35
mv slow.log slow.log.$(date +%F)
mysqladmin flush-log

放入定时任务
[root@mysql-2 data]# crontab -e
#cut mysql slow log
00 00 * * * /bin/sh /erver/scripts/cut_slow_log.sh

分析慢查询日志的工具案例

 

(1)mysqlsla安装
请提前下载好mysqlsla-2.03.tar.gz到指定目录下，然后执行如下命令安装：
yum install  perl-devel perl-DBI perl-DBD-MySQL -y
rpm -qa perl-devel perl-DBI perl-DBD-MySQL
tar xf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL
make && make install

（2）利用mysqlsla工具分析慢查询
mysqlsla命令的默认路径为：/usr/local/bin/mysqlsla。
简单语法如下：
mysqlsla -lt slow [SlowLogFilePath] > [ResultFilePath]
在实际工作中，老男孩通常使用脚本调用mysqlsla工具进行分析，然后每天早晨8点，把分析结果发给企业的核心人员（DBA、运维总监、CTO、研发总监、核心开发），最后由DBA配合核心开发共同优化这些棘手的SQL慢查询。
简单的案例脚本如下，注意切割日志和分析合并为一个脚本了：

vim /server/scripts/slow_log_analyze.sh

#!/bin/bash
export PATH=/application/mysql/bin:/bin:/sbin:/usr/bin:/usr/sbin
Date=$(date +%F -d -1day)
#cut slow log
cd /application/mysql/
mv slow.log slow.log_$Date
mysqladmin flush-log
#analyze slow log
Path=/usr/local/bin/mysqlsla
cd /application/mysql
$Path/mysqlsla -lt slow slow.log_$Date > analyzed_slow_$Dste.log 2>&1
#rsync analyzed_slow to backup server 省略此步
#send analyzed slow log to administrator on backup server by mail. 省略此步

定时任务
crontab -e
00 00 * * * /bin/sh /server/scripts/slow_log_analyze.sh

mysqldumpslow命令

mysqldumpslow -s c -t 10 /data/slow/slow.log
mysqldumpslow -s at -t 10 /data/slow/slow.log


这会输出记录次数最多的10条SQL语句，
其中：
-s
是表示按照何种方式排序
c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序。
ac、at、al、ar，表示相应的倒叙。
-t
是top n的意思，即为返回前面多少条的数据；

二、使用二进制日志恢复数据

1、故障环境介绍

创建了一个库  db, 导入了表t1 ,t1表中录入了很多数据
一个开发人员,drop database db;
没有备份,日志都在.怎么恢复?
思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式)
故障案例模拟:
(0) drop database if exists db ;
(1) create database db charset utf8;     
(2) use db;
(3) create table t1 (id int);
(4) insert into t1 values(1),(2),(3);
(5) insert into t1 values(4),(5),(6);
(6) commit
(7) update t1 set id=30 where id=3;
(8) commit;
(9) delete from t1 where id=4;
(10)commit;
(11)insert into t1 values(7),(8),(9);
(12)commit;
(13)drop database db;
========================
drop database if exists db ;
create database db charset utf8; 
use db;
create table t1 (id int);
insert into t1 values(1),(2),(3);
insert into t1 values(4),(5),(6);
commit;
update t1 set id=30 where id=3;
commit;
delete from t1 where id=4;
commit;
insert into t1 values(7),(8),(9);
commit;
drop database db;
=======
运行以上语句，模拟故障场景
需求：将数据库恢复到以下状态（提示第9步和第13步是误操作，其他都是正常操作）

2、恢复过程(无GTID时的恢复)

1）查看当前使用的 binlog文件

 

[db]>show master status ;
+------------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000006 |    1873 |              |                  |                  |

+------------------+----------+--------------+------------------+-------------------+

2)查看事件并截取除delete以外的二进制日志

[db]>show binlog events in 'mysql-bin.000006'; ;
第一段：
| mysql-bin.000006 |  813 | Query      |        1 |        907 | use `db`; create table t1 (id int)                  |

| mysql-bin.000006 |  907 | Query      |        1 |        977 | BEGIN                                              |

| mysql-bin.000006 |  977 | Table_map  |        1 |        1020 | table_id: 77 (db.t1)                                |

| mysql-bin.000006 | 1020 | Write_rows  |        1 |        1070 | table_id: 77 flags: STMT_END_F                      |

| mysql-bin.000006 | 1070 | Table_map  |        1 |        1113 | table_id: 77 (db.t1)                                |

| mysql-bin.000006 | 1113 | Write_rows  |        1 |        1163 | table_id: 77 flags: STMT_END_F                      |

| mysql-bin.000006 | 1163 | Xid        |        1 |        1194 | COMMIT /* xid=74 */                                |

| mysql-bin.000006 | 1194 | Query      |        1 |        1264 | BEGIN                                              |

| mysql-bin.000006 | 1264 | Table_map  |        1 |        1307 | table_id: 77 (db.t1)                                |

| mysql-bin.000006 | 1307 | Update_rows |        1 |        1353 | table_id: 77 flags: STMT_END_F                      |

| mysql-bin.000006 | 1353 | Xid        |        1 |        1384 | COMMIT /* xid=77 */   

mysqlbinlog --start-position=813 --stop-position=1384 /data/mysql/mysql-bin.000006 >/tmp/bin1.sql 

| mysql-bin.000006 | 1568 | Query      |        1 |        1638 | BEGIN                                              |

| mysql-bin.000006 | 1638 | Table_map  |        1 |        1681 | table_id: 77 (db.t1)                                |

| mysql-bin.000006 | 1681 | Write_rows  |        1 |        1731 | table_id: 77 flags: STMT_END_F                      |

| mysql-bin.000006 | 1731 | Xid        |        1 |        1762 | COMMIT /* xid=81 */ 

mysqlbinlog --start-position=1568 --stop-position=1762 /data/mysql/mysql-bin.000006 >/tmp/bin2.sql

3）恢复

set sql_log_bin=0;
source /tmp/bin1.sql
source /tmp/bin2.sql
set sql_log_bin=1;
[db]>select * from t1;

+------+

| id  |

+------+

|    1 |

|    2 |

|  30 |

|    4 |

|    5 |

|    6 |

|    7 |

|    8 |

|    9 |

3、有GTID的恢复

 (1)截取

mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12' --exclude-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:7-10' mysql-bin.000004> /tmp/bin.sql

(2)恢复

 

set sql_log_bin=0;
source /tmp/bin.sql