Mysql8 搭建主从复制集群以及binlog恢复数据案例 —— 筑梦之路

环境说明：

192.168.20.100   master
192.168.20.101   slave

master上创建用户并授权：

CREATE USER `rep1`@`192.168.20.101` IDENTIFIED WITH caching_sha2_password BY '12345678';

GRANT Replication Slave ON *.* TO `rep1`@`192.168.20.101`;

master修改配置

[mysqld]
log-bin=/var/lib/mysql/binlog
server-id=128
#binlog-do-db = cmdb

systemctl restart mysqld

查看主服务器当前二进制日志名和偏移量

show master status;

slave修改配置

[mysqld]
servre-id=130
log-bin=/var/lib/mysql/binlog

配置主从：

change master to master_host='192.168.20.100',master_port=3306,master_user='rep1',master_password='12345678',master_log_file='binlog.000001',master_log_pos=120,get_master_public_key=1;

#开启复制
start slave;

#检查状态
show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

------------------------------------------
至此主从复制集群搭建完成，和8之前的版本对比，还是有些地方不一样，这是需要注意的

binlog 是 MySQL Server 层的日志，而不是存储引擎自带的日志，它记录了所有的 DDL 和 DML(不包含数据查询语句)语句，而且是以事件形式记录，还包含语句所执行的消耗的时间等，需要注意的是：

• binlog 是一种逻辑日志，他里边所记录的是一条 SQL 语句的原始逻辑，例如给某一个字段 +1，注意这个区别于 redo log 的物理日志（在某个数据页上做了什么修改）。

• binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的。

• 一般来说，我们在配置 binlog 的时候，可以指定 binlog 文件的有效期，这样在到期后，日志文件会自动删除，这样避免占用较多存储空间。

根据 MySQL 官方文档的介绍，开启 binlog 之后，大概会有 1% 的性能损耗，不过这还是可以接受的，一般来说，binlog 有两个重要的使用场景：

• MySQL 主从复制时：在主机上开启 binlog，主机将 binlog 同步给从机，从机通过 binlog 来同步数据，进而实现主机和从机的数据同步。

• MySQL 数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将数据恢复到过去的某一时刻。

查看是否开启binlog

show variables like '%log_bin%';

log_bin_basename： binlog文件名前缀，记录所有的 DDL 和 DML 语句事件

log_bin_index： binlog索引文件，保存了所有 binlog 的目录，因为 binlog 可能会有多个

开启binlog

# 这个参数表示启用 binlog 功能，并指定 binlog 的存储目录
log-bin=mysql-bin

# 设置一个 binlog 文件的最大字节
# 设置最大 100MB
max_binlog_size=104857600

# 设置了 binlog 文件的有效期（单位：天）
expire_logs_days = 7

# binlog 日志只记录指定库的更新（配置主从复制的时候会用到）
#binlog-do-db=cmdb

# binlog 日志不记录指定库的更新（配置主从复制的时候会用到）
#binlog-ignore-db=cmdb_no_db

# 写缓存多少次，刷一次磁盘，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
# 1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
sync_binlog=0

# 为当前服务取一个唯一的 id（MySQL5.7 之后需要配置）
server-id=1

重启服务
systemctl restart mysqld

查看所有binlog日志

show master logs;

查看master状态,最新的日志文件

show master status;

刷新binlog

flush logs;

重置binlog

reset master;

#需要注意的是：日志重新从 000001 开始记录，不过如果当前主机有一个或者多个从机在运行，那么该命令就运行不了（因为从机是通过 binlog 来实现数据库同步的，主机把 binlog 清空了，从机会报找不到 binlog 的错误）

查看binlog日志文件内容

mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000001

#日志文件中有一个 end_log_pos 是日志文件的 pos 点，这个将来在数据恢复的时候有用。

按照事件的方式查看binlog内容

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

---
log_name：可以指定要查看的 binlog 日志文件名，如果不指定的话，表示查看最早的 binlog 文件。
pos：从哪个 pos 点开始查看，凡是 binlog 记录下来的操作都有一个 pos 点，这个其实就是相当于我们可以指定从哪个操作开始查看日志，如果不指定的话，就是从该 binlog 的开头开始查看。
offset：这是是偏移量，不指定默认就是 0。
row_count：查看多少行记录，不指定就是查看所有。

---

示例： show binlog events in 'mysql-bin.000001';

 

数据恢复实例

场景说明：

每天凌晨三点对数据库有一个备份，执行导出操作生成sql文件，现在是上午10点钟，3:00-10:00这一段时间已经写了一些数据，10:10在主库上执行了删库操作（drop database xxx;），该库数据全部丢失。

备份时使用的命令：

mysqldump -uroot -p --flush-logs --lock-tables -B cmdb > /root/cmdb_backup.sql

参数说明：
--flush-logs：这个表示在导出之前先刷新 binlog，刷新 binlog 之后将会产生新的 binlog 文件，后续的操作都存在新的 binlog 中。
--lock-tables：这个表示开始导出前，锁定所有表。需要注意的是当导出多个数据库时，--lock-tables 分别为每个数据库锁定表，因此这个选项不能保证导出文件中的表在数据库之间的逻辑一致性，不同数据库表的导出状态可以完全不同。
-B：这个表示指定导出的数据库名称，如果使用 --all-databases 或者 -A 代替 -B 表示导出所有的数据库。

分析解决办法：

数据库可以使用凌晨三点备份的sql文件立即恢复数据，但是3:00-10:10之间的数据没有了

因此3:00-10:10这一段时间的数据需要借助binlog来恢复

#查看binlog内容，找到删库操作，查看pos信息

show binlog events in 'mysql-bin.000002';

#这里我查看到的pos是764-865，执行了删库操作，由于 mysql-bin.000002 文件是凌晨三点备份之后产生的新文件，因此这个文件从起始到 764 这个 Pos 之间的操作，就是凌晨三点到删库之前的操作了。

那么我们来看下通过 binlog 来恢复数据的命令：

mysqlbinlog /var/lib/mysql/mysql-bin.000002 --stop-position=764 --database=cmdb | mysql -uroot -p

参数说明：
--stop-position=764 表示恢复到 764 这个 Pos，不指定的话就把按整个文件恢复了，如果按当前文件恢复的话，由于这个 binlog 文件中有删除数据库的语句，那么就会导致执行完该 binlog 之后，cmdb 库又被删除了。
--database=cmdb 表示恢复 cmdb 这个库。

--start-position，这个表示起始的 Pos，不指定的话表示从头开始数据恢复。

检查是否恢复成功：
show databases;
use cmdb;
show tables;