MySQL日志--二进制日志(binlog)

(一)什么是二进制日志

二进制日志(binlog)记录了数据库中所有的DDL和DML操作,但是不包括select语句,语句以“事件”的形式保存,记录了数据库的更改变化,在主从复制(replication)和数据恢复中起着重要的作用。对比来看,MySQL的二进制日志作用相当于Oracle数据库的redo log + archive log。

 

(二)开启二进制日志及参数配置

二进制日志开启需要在配置文件里面配置相关参数,然后重启数据库,或者开启数据库的时候添加相关参数。主要参数如下:

(2.1)log_bin

该参数控制binlog文件存放的位置,用法log_bin=path/file_name。如果没有给出路径,则默认路径为数据文件存放地址(datadir),如果没有给出文件名称,默认文件名为“主机名-bin.num”。初始文件是hostname-bin-000001,每次启动MySQL服务或者刷新时,自动递增编号创建二进制日志文件。

(2.1)binlog_format

该参数控制二进制日志文件的格式,可选格式有:

  • STATEMENT:日志中记录的是执行的SQL语句,日志量小,但是复制会出现主从不一致的情况
  • ROW:日志记录每一行数据的变化,日志量大,好处是主从复制数据不会出现不一致的情况,推荐使用这种方式
  • MIXED:结合STATEMENT和ROW的日志格式。

(2.3)expire_logs_days

该参数控制二进制日志保留天数,过了指定的天数后,日志将会自动删除。

(2.4)max_binlog_size

该参数控制二进制日志文件的大小,当日志文件达到该参数指定的大小时,就会创建新的二进制日志文件。不过,实际上二进制日志文件可能会超过该值,比如当二进制日志快要写满时,执行一个超大事物,由于事物特性决定相关事件必须连续,这种情况下,事件必须写到同一个日志文件,就会出现日志大于 max_binlog_size值的现象。

(2.5)binlog_do_db

该参数控制要保存二进制日志的数据库,如果有多个数据库,需要多次使用该参数,不能使用逗号分隔。

image

(2.6)binlog_ignore_db

该参数控制要不保存二进制日志的数据库,如果有多个数据库,需要多次使用该参数,不能使用逗号分隔。

(2.7)sync_binlog

该参数控制二进制日志刷新到磁盘的频率,单位为秒(s)。

(2.8)server_id

如果要在MySQL5.7版本中开启binlog,必须设置该参数,否则实例无法启动。


一个简单的参数配置如图:

image


(三)二进制日志的作用

(3.1)复制

MySQL Master端的二进制日志发送到slave端,slave端根据日志进行重做,达到主从复制的目的。

(3.2)恢复

不管是使用mysqldump还是xtrabackup,我们都只能将数据库恢复到有备份的时刻,如果要将数据库恢复到任何时刻,则需要使用二进制日志。

 

(四)相关命令

(4.1)binlog清理

如果业务量较大,binlog日志增长很快,需要定期清理,有好几种方法可以清理日志。

方法1:执行“reset master”命令,该命令将删除所有的binlog日志,新日志编号从“000001”开始

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

方法2:执行“purge master logs to ‘hostname-bin.******’ ”命令,该命令将删除******之前的日志

# 删除'master-bin.000007'之前的日志 
 
[root@masterdb binlog]# ls -lrt
total 36
-rw-r----- 1 mysql mysql 202 Feb 13 00:20 master-bin.000001
-rw-r----- 1 mysql mysql 202 Feb 13 00:20 master-bin.000002
-rw-r----- 1 mysql mysql 202 Feb 13 00:20 master-bin.000003
-rw-r----- 1 mysql mysql 202 Feb 13 00:20 master-bin.000004
-rw-r----- 1 mysql mysql 202 Feb 13 00:20 master-bin.000005
-rw-r----- 1 mysql mysql 202 Feb 13 00:20 master-bin.000006
-rw-r----- 1 mysql mysql 202 Feb 13 00:20 master-bin.000007
-rw-r----- 1 mysql mysql 154 Feb 13 00:20 master-bin.000008
-rw-r----- 1 mysql mysql 256 Feb 13 00:20 master-bin.index
[root@masterdb binlog]# 

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


[root@masterdb binlog]# ls -lrt
total 12
-rw-r----- 1 mysql mysql 202 Feb 13 00:20 master-bin.000007
-rw-r----- 1 mysql mysql 154 Feb 13 00:20 master-bin.000008
-rw-r----- 1 mysql mysql  64 Feb 13 00:22 master-bin.index

方法3:执行“purge master logs before‘yyyy-mm-dd hh24:mi:ss’ ”命令,该命令将删除指定日志之前的日志

# 删除2020-02-13 00:24:00之前的日志

mysql> system ls -lrt /mysql/binlog
total 36
-rw-r----- 1 mysql mysql 202 Feb 13 00:20 master-bin.000007
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000008
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000009
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000010
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000011
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000012
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000013
-rw-r----- 1 mysql mysql 154 Feb 13 00:25 master-bin.000014
-rw-r----- 1 mysql mysql 256 Feb 13 00:25 master-bin.index
mysql> purge master logs before '2020-02-13 00:24:00';
Query OK, 0 rows affected (0.00 sec)

mysql> system ls -lrt /mysql/binlog
total 32
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000008
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000009
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000010
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000011
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000012
-rw-r----- 1 mysql mysql 202 Feb 13 00:25 master-bin.000013
-rw-r----- 1 mysql mysql 154 Feb 13 00:25 master-bin.000014
-rw-r----- 1 mysql mysql 224 Feb 13 00:29 master-bin.index

方法4:在配置文件中添加“expire_logs_days”参数,指定过期天数,到期会自动删除。


(4.2)binlog切换

可以使用flush logs或者flush binary logs切换日志。

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000017 |      154 | db1,db2,db3  | db4              |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000018 |      154 | db1,db2,db3  | db4              |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000019 |      154 | db1,db2,db3  | db4              |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

(4.3)查看binlog events

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

 

(4.4)查看当前biblog位置

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000019 |      154 | db1,db2,db3  | db4              |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)




相关文档集合:

1.MySQL日志--二进制日志(binlog)                  
2.使用mysqlbinlog查看二进制日志                  
3.MySQL使用mysqldump+binlog完整恢复被删除的数据库
4.使用binlog2sql工具来恢复数据库                 
5.MySQL闪回工具—MyFlash                          

posted @ 2020-02-13 00:37  gegeman  阅读(780)  评论(0编辑  收藏  举报