目录
1.show binary logs 查看当前所有binlog文件列表编辑
3.show master status; 查看当前使用的binlog
一、作用
记录mysql运行过程中，变更类的语句（DDL,DML,DCL）
可以用来做数据恢复和主从复制
二、binlog配置
1.查看当前配置
2.修改配置文件
3.binlog配置参数解释
| server_id | 主机ID，主从复制时用来区分不同的节点 |
| log_bin | 二进制日志开关，也可以设置binlog存放位置和名称 |
| log_bin_basename | 设置binlog存放位置和名称 |
| binlog_format | 二进制日志记录格式，默认是row模式 |
| sync_binlog | binlog刷新策略，默认为1 |
| gtid_mode | |
| enforce_gtid_consistency |
三、binlog记录内容说明
1.记录内容
记录mysql运行过程中，变更类的语句（DDL,DML,DCL）
2.DDL、DCL记录格式
statement 语句格式记录，例如：create database
3.DML记录格式
a）只会记录已经提交的事务语句
b）记录格式受到bin_log_format影响，可以设置
4.记录内容查看
在日志文件夹下使用mysqlbinlog命令
[root@localhost binlog]# mysqlbinlog binlog.000003
查看DMl语句，基于row模式查看内容
[root@localhost binlog]# mysqlbinlog --base64-output=decode-rows -vvv binlog.000003
四、bin_log_format 记录模式
1.行模式 Row
例如：update set name=‘a’ where id<10，行模式记录的是9个数据行每行变化的日志
日志量相对较大，记录准确
2.语句模式 Statement
语句模式记录的是update语句本身
日志量相对较小，记录可能不准确
3.混合模式
五、binlog文件查看
1.show binary logs 查看当前所有binlog文件列表
2.flush logs 刷新binlog
3.show master status; 查看当前使用的binlog
六、binlog记录单元-event事件
1.event介绍
DDL、DCL：一条语句就是一个事件
DML：一个事务型操作由多个event构成
begin e1
DML1 e2
DML2 e3
commit e4
2.事件的主要结构
开始标记：position
事件内容 event
结束标记：position
3.事件查看
查看当前使用的binlog文件：show master status
例如对数据库做了更改：create database yizuo charset utf8mb4;
查看该事件：show binlog events in 'binlog.000003';
前两行是默认
POS是事件起始位置点，End_log_pos是结束位置点
七、binlog日志截取和数据恢复
1.模拟drop环境
2.截取二进制日志-截取到drop那一行的position号（与gtid截取不同）
查找create database起点：
[root@localhost binlog]# mysql -e "show binlog events in 'binlog.000003';" | grep "create database yizuo";
查找drop操作的起点：
[root@localhost binlog]# mysql -e "show binlog events in 'binlog.000003';" | grep "drop database yizuo";
截取日志：
mysqlbinlog --start-position=233 --stop-position=2256 binlog.000003 >/tmp/bin.sql;
3.恢复数据
set sql_log_bin=0; 设置在恢复的时候不要产生额外的日志了，该设置是会话级别的
source /tmp/bin.sql; 恢复数据
set sql_log_bin=1; 设置二进制日志记录
八、binlog日志跨多文件截取
1.模拟环境
mysql> create database yizuo charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> use yizuo;
Database changed
mysql> CREATE TABLE `city` (
-> `id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
-> `name` varchar(100) DEFAULT NULL COMMENT '城市名',
-> `province` varchar(100) DEFAULT NULL,
-> `population` varchar(100) DEFAULT NULL COMMENT '人口',
-> `district` varchar(100) DEFAULT NULL COMMENT '街区',
-> PRIMARY KEY (`id`),
-> KEY `idx_name` (`name`),
-> KEY `idx_nn` (`name`(5)) /*!80000 INVISIBLE */
-> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 | 3395 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 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 |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> insert into city (id,name,province,population,district) values(1,'沈阳','辽宁','109万','兴隆台区');
Query OK, 1 row affected (0.00 sec)
mysql> insert into city (id,name,province,population,district) values(2,'大连','辽宁','109万','兴隆台区');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> insert into city (id,name,province,population,district) values(3,'锦州','辽宁','109万','兴隆台区');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database yizuo;
Query OK, 1 row affected (0.01 sec)
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
5 rows in set (0.00 sec)
2.分段截取
1）截取003日志文件：
[root@localhost binlog]# mysql -e "show binlog events in 'binlog.000003';" | grep "create database yizuo";
[root@localhost binlog]# mysql -e "show binlog events in 'binlog.000003';" | grep "create database yizuo";
binlog.000003 233 Query 51 360 create database yizuo charset utf8mb4 /* xid=8 */
binlog.000003 2631 Query 51 2758 create database yizuo charset utf8mb4 /* xid=128 */
mysqlbinlog --start-position=2631 binlog.000003 >/tmp/bin1.sql;
2）截取004日志文件
mysqlbinlog binlog.000004 >/tmp/bin2.sql;
3）截取005号日志文件
[root@localhost binlog]# mysql -e "show binlog events in 'binlog.000005';" | grep "drop database yizuo";
[root@localhost binlog]# mysql -e "show binlog events in 'binlog.000005';" | grep "drop database yizuo";
binlog.000005 565 Query 51 672 drop database yizuo /* xid=144 */
mysqlbinlog --stop-position=565 binlog.000005 >/tmp/bin3.sql;
3.数据恢复
set sql_log_bin=0;
source /tmp/bin1.sql;
source /tmp/bin2.sql;
source /tmp/bin3.sql;
set sql_log_bin=1;
九、GTID模式下的binlog管理
1.介绍
5.6以后加入了GTID功能，用来唯一标识事件和事务，GTID具有幂等性
GLOBAL TRANSTATION ID 全局事务唯一编号，是从1开始有序生成的
表现方式：server_uuid:TID （server_uuid是在第一次初始化，启动数据库时，自动生成的，存于/data/auto.cnf中，一旦被删除或修改，下次重启数据库会重置）
DDL、DCL操作：一个事件记录一个GTID
DML：一个完整事务记录一个GTID
2.开启方式
默认是关闭的
修改配置文件，开启gtid：
3.基于GTID的binlog管理
查看GTID信息：show master status;
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000006 | 678 | | | 518de310-1806-11ef-b5d7-000c2912a662:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------+
查看GTID事件信息：show binlog events in 'binlog.000006';
mysql> show binlog events in 'binlog.000006';
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| binlog.000006 | 4 | Format_desc | 51 | 125 | Server ver: 8.0.20, Binlog ver: 4 |
| binlog.000006 | 125 | Previous_gtids | 51 | 156 | |
| binlog.000006 | 156 | Gtid | 51 | 235 | SET @@SESSION.GTID_NEXT= '518de310-1806-11ef-b5d7-000c2912a662:1' |
| binlog.000006 | 235 | Query | 51 | 311 | BEGIN |
| binlog.000006 | 311 | Table_map | 51 | 379 | table_id: 86 (yizuo.city) |
| binlog.000006 | 379 | Write_rows | 51 | 457 | table_id: 86 flags: STMT_END_F |
| binlog.000006 | 457 | Xid | 51 | 488 | COMMIT /* xid=11 */ |
| binlog.000006 | 488 | Gtid | 51 | 565 | SET @@SESSION.GTID_NEXT= '518de310-1806-11ef-b5d7-000c2912a662:2' |
| binlog.000006 | 565 | Query | 51 | 678 | use `yizuo`; create table t1(id int) /* xid=16 */ |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
9 rows in set (0.00 sec)
十、基于GTID模式截取和恢复数据
1.模拟环境
mysql> drop database yizuo;
Query OK, 1 row affected (0.01 sec)
mysql> show binlog events in 'binlog.000007';
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000007 | 4 | Format_desc | 51 | 125 | Server ver: 8.0.20, Binlog ver: 4 |
| binlog.000007 | 125 | Previous_gtids | 51 | 196 | 518de310-1806-11ef-b5d7-000c2912a662:1-3 |
| binlog.000007 | 196 | Gtid | 51 | 273 | SET @@SESSION.GTID_NEXT= '518de310-1806-11ef-b5d7-000c2912a662:4' |
| binlog.000007 | 273 | Query | 51 | 400 | create database yizuo charset utf8mb4 /* xid=28 */ |
| binlog.000007 | 400 | Gtid | 51 | 479 | SET @@SESSION.GTID_NEXT= '518de310-1806-11ef-b5d7-000c2912a662:5' |
| binlog.000007 | 479 | Query | 51 | 1037 | use `yizuo`; CREATE TABLE `city` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '编号', `name` varchar(100) DEFAULT NULL COMMENT '城市名', `province` varchar(100) DEFAULT NULL, `population` varchar(100) DEFAULT NULL COMMENT '人口', `district` varchar(100) DEFAULT NULL COMMENT '街区', PRIMARY KEY (`id`), KEY `idx_name` (`name`), KEY `idx_nn` (`name`(5)) /*!80000 INVISIBLE */ ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /* xid=34 */ |
| binlog.000007 | 1037 | Gtid | 51 | 1116 | SET @@SESSION.GTID_NEXT= '518de310-1806-11ef-b5d7-000c2912a662:6' |
| binlog.000007 | 1116 | Query | 51 | 1192 | BEGIN |
| binlog.000007 | 1192 | Table_map | 51 | 1260 | table_id: 93 (yizuo.city) |
| binlog.000007 | 1260 | Write_rows | 51 | 1338 | table_id: 93 flags: STMT_END_F |
| binlog.000007 | 1338 | Xid | 51 | 1369 | COMMIT /* xid=35 */ |
| binlog.000007 | 1369 | Gtid | 51 | 1448 | SET @@SESSION.GTID_NEXT= '518de310-1806-11ef-b5d7-000c2912a662:7' |
| binlog.000007 | 1448 | Query | 51 | 1524 | BEGIN |
| binlog.000007 | 1524 | Table_map | 51 | 1592 | table_id: 93 (yizuo.city) |
| binlog.000007 | 1592 | Write_rows | 51 | 1670 | table_id: 93 flags: STMT_END_F |
| binlog.000007 | 1670 | Xid | 51 | 1701 | COMMIT /* xid=38 */ |
| binlog.000007 | 1701 | Gtid | 51 | 1778 | SET @@SESSION.GTID_NEXT= '518de310-1806-11ef-b5d7-000c2912a662:8' |
| binlog.000007 | 1778 | Query | 51 | 1885 | drop database yizuo /* xid=42 */ 2.利用GTID截取日志-截取到drop操作之前的一个gtid号
1）普通截取：
[root@localhost binlog]# mysqlbinlog --skip-gtids --include-gtids='518de310-1806-11ef-b5d7-000c2912a662:4-7' binlog.000007 >/tmp/gtid1.sql
需要添加：--skip-gtids，在截取日志时，抹掉已有的GTID信息，防止GTID重复冲突，不添加该参数，gtid截取日志也不会成功的
2）跳过某些gtid信息
--include-gtids='518de310-1806-11ef-b5d7-000c2912a662:4-7'
--exclude-gtids='518de310-1806-11ef-b5d7-000c2912a662:6'
3.恢复数据
set sql_log_bin=0;
source /tmp/gtid1.sql;
set sql_log_bin=1;
扫一扫
713
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
