本文最后更新于 65 天前，其中的信息可能已经过时，如有错误请发送邮件到wuxianglongblog@163.com
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=rowbinlog的工作模式
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）想要恢复的库，中途有其他库表操作，如何只截取指定库的