MySQL 二进制日志 binlog

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
+---------------------------------+---------------------------------+
## 常规的日志名
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
...
## 常规日志存储位置：
$datadir 数据目录下
## 常规日志默认是否开启
关闭
## 如何修改配置
### 开启binlog MySQL5.6
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row
#log-bin=/opt/zls-bin
### 开启binlog MySQL5.7
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server_id=1
binlog_format=row

				

					
				
				

					
				
				

					
				
				

					
				
			

binlog 的工作模式

statement 语句模式

记录MySQL的SQL语句 DDL DML DCL
 
## MySQL5.6 默认语句模式
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
 
# 优缺点
- 优点：
    - 易读
    - 占用磁盘空间小
- 缺点：
    - 不严谨

				

					
				
				

					
				
				

					
				
				

					
				
			

row 行级模式

记录MySQL的SQL语句 DDL、DCL，DML记录每一行的变化过程
 
### ## MySQL5.7 默认行级模式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
 
# 优缺点
- 优点：
    - 严谨
- 缺点
    - 不易读
    - 占用磁盘空间大

				

					
				
				

					
				
				

					
				
				

					
				
			

mixed 混合模式

statement 和 row 的混合
一般运维不用

				

					
				
				

					
				
				

					
				
				

					
				
			

查看 binlog

## 查看语句模式
mysqlbinlog binlog名字
 
[root@db01 data]# mysqlbinlog mysql-bin.000001
 
## 查看行级模式
[root@db03 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000013
 
# 查看当前的binlog
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
 
# 查看有哪些binlog，每个binlog的大小
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       622 |
| mysql-bin.000002 |       143 |
| mysql-bin.000003 |       387 |
| mysql-bin.000004 |       120 |
| mysql-bin.000005 |       120 |
| mysql-bin.000006 |       120 |
| mysql-bin.000007 |       120 |
| mysql-bin.000008 |       120 |
| mysql-bin.000009 |       120 |
| mysql-bin.000010 |       120 |
| mysql-bin.000011 |       120 |
| mysql-bin.000012 |       143 |
| mysql-bin.000013 |       143 |
| mysql-bin.000014 |       167 |
| mysql-bin.000015 |      2686 |
| mysql-bin.000016 |       120 |
+------------------+-----------+
 
# 查看binlog事件
mysql> show binlog events in 'mysql-bin.000015'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000015
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 120
       Info: Server ver: 5.6.50-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000015
        Pos: 120
 Event_type: Query
  Server_id: 1
End_log_pos: 220
       Info: create database binlog
*************************** 3. row ***************************
   Log_name: mysql-bin.000015
        Pos: 220
 Event_type: Query
  Server_id: 1
End_log_pos: 330
       Info: use `binlog`; create table test_binlog(id int)
*************************** 4. row ***************************
   Log_name: mysql-bin.000015
        Pos: 330
 Event_type: Query
  Server_id: 1
End_log_pos: 404
       Info: BEGIN
····等····

				

					
				
				

					
				
				

					
				
				

					
				
			

查看 binlog 内容和导出

mysqlbinlog --start-position=120 --stop-position=1035 mysql-bin.000007 > /tmp/test_binlog.sql
# -d db1 指定只查看db1库的操作
mysqlbinlog -d db1 --start-position=120 --stop-position=1035 mysql-bin.000007 > /tmp/test_binlog.sql

				

					
				
				

					
				
				

					
				
				

					
				
			

使用 binlog 恢复数据案例

模拟数据

# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 |      218 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 刷新一个新的binlog
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 创建一个binlog库
mysql> create database binlog;
Query OK, 1 row affected (0.00 sec)
# 使用binlog库
mysql> use binlog
Database changed
# 创建test_binlog表
mysql> create table test_binlog(id int);
Query OK, 0 rows affected (0.00 sec)
# 插入数据1
mysql> insert into test_binlog values(1);
Query OK, 1 row affected (0.00 sec)
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |      531 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 插入数据2
mysql> insert into test_binlog values(2);
Query OK, 1 row affected (0.00 sec)
# 插入数据3
mysql> insert into test_binlog values(3);
Query OK, 1 row affected (0.00 sec)
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |      933 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 查看test_binlog表内的内容
mysql> select * from test_binlog;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
# 更改数据1为10
mysql> update test_binlog set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看test_binlog表内的信息
mysql> select * from test_binlog;
+------+
| id   |
+------+
|   10 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |     1140 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 删除数据3
mysql> delete from test_binlog where id=3;
Query OK, 1 row affected (0.00 sec)
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看test_binlog表内的信息
mysql> select * from test_binlog;
+------+
| id   |
+------+
|   10 |
|    2 |
+------+
2 rows in set (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |     1341 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 查看库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| baixiong           |
| mysql              |
| performance_schema |
| test               |
| xx                 |
+--------------------+

				

					
				
				

					
				
				

					
				
				

					
				
			

模拟数据故障

# 删除test_binlog表
mysql> drop table test_binlog;
Query OK, 0 rows affected (0.00 sec)
# 删除binlog库
mysql> drop database binlog;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000017 |     1558 |
+------------------+----------+

				

					
				
				

					
				
				

					
				
				

					
				
			

恢复数据

# 1.查看当前使用的是哪个binlog？
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |     1558 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
 
# 一般binlog写入的都为最后一个
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       622 |
| mysql-bin.000002 |       143 |
| mysql-bin.000003 |       387 |
| mysql-bin.000004 |       120 |
| mysql-bin.000005 |       120 |
| mysql-bin.000006 |       120 |
| mysql-bin.000007 |       120 |
| mysql-bin.000008 |       120 |
| mysql-bin.000009 |       120 |
| mysql-bin.000010 |       120 |
| mysql-bin.000011 |       120 |
| mysql-bin.000012 |       143 |
| mysql-bin.000013 |       143 |
| mysql-bin.000014 |       167 |
| mysql-bin.000015 |      2686 |
| mysql-bin.000016 |       265 |
| mysql-bin.000017 |      1558 |
+------------------+-----------+
 
# 2.使用mysqlbinlog命令查看binlog内容
[root@db01 ~]# cd /app/mysql/data/
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000017
 
# 3.找到起点和结束的位置
120        和     1270
🐂例：---------------------模拟数据时未关闭自动提交。
# at 120
#230801 15:52:06 server id 1  end_log_pos 220 CRC32 0x88ddfc3c  Query   thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1690876326/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database binlog
/*!*/;
# at 220
 
## 上面为创建一个binlog库
mysql> create database binlog;
 
# at 1270
#230801 15:55:12 server id 1  end_log_pos 1310 CRC32 0x2964016b     Delete_rows: table id 71 flags: STMT_END_F
### DELETE FROM `binlog`.`test_binlog`
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 1310
 
## 上面为删除数据3
mysql> delete from test_binlog where id=3;
🐎----------------------------------------
 
# 4.截取binlog到文件
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=1270 mysql-bin.000017 > /tmp/2.sql
 
# 5.先临时关闭binlog记录
mysql> set sql_log_bin=0;
 
# 6.恢复数据
mysql> source /tmp/2.sql
 
# 7.开启binlog记录
mysql> set sql_log_bin=1;
 
# 8.查看数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| binlog             |
+--------------------+
mysql> use binlog
+------------------+
| Tables_in_binlog |
+------------------+
| test_binlog      |
+------------------+
mysql> select * from test_binlog;
+------+
| id   |
+------+
|   10 |
|    2 |
|    3 |
+------+

				

					
				
				

					
				
				

					
				
				

					
				
			

binlog 的刷新和删除

binlog 的刷新

1)重启数据库
/etc/init.d/mysqld restart
 
2)执行flush logs;
 
3)mysqladmin
[root@db01 ~]# mysqladmin -uroot -p123 flush-log
 
4)自动切割，当binlog达到1G

				

					
				
				

					
				
				

					
				
				

					
				
			

删除 binlog

# 1.根据时间删除
set global expire_logs_days = 7;
 
vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
 
# 2.根据时间删除
purge binary logs before now() - interval 7 day;
 
# 3.根据文件名删除(1到文件名那个)
purge binary logs to 'mysql-bin.000010';
 
# 4.删除binlog（all）
mysql> reset master;

				

					
				
				

					
				
				

					
				
				

					
				
			

企业存在的问题
1）企业中的 binlog 很大，位置点不好找 grep

2）企业中所有数据不会只在一个 binlog 中 配合全备（mysqldump）

3）想要恢复的库，中途有其他库表操作，如何只截取指定库的