Mysql二进制日志文件binlog主从复制最佳实践

实践背景

最近加入了同学的技术分享小组，4 个人分两组，半月进行一次技术分享，现在一起搞 Mysql 我被分到讲解 Mysql 日志方面，上周已经讲完了，不过他们总是觉得对于日志这块了解不透彻，觉得不过关 … 于是大家自觉强制在加一轮分享作为对 mysql 这段时间的学习总结，结合实践来进行分享，之前分享的日志的分类和原理非常理论，我自己也不满意，于是花了一天把二进制日志的数据恢复和主从复制搞了一下，于是这篇文章产生了，纸上得来总觉浅，绝知此事要躬行，这句话突然从我脑海中冒出来了。

预先配置

我是将 Mac OS 本机的 Mysql 作为 master，虚拟机的 Mysql 作为 slave，首先修改 master 和 slave 的 my.cnf 数据库配置文件。内容如下：

master 的 my.cnf

[mysqld]
# 1. 主从备份相关配置
server-id = 1                # 服务器 id 号，不要和其他服务器重复
log-bin=mysql-bin            # 开启二进制日志
log_bin_index = mysql-bin.index        # 索引二进制日志的文件名
sync_binlog = 1                # 设为 1 就是把 MySql 每次发生的修改和事件的日志即时同步到硬盘上
binlog_format = Row            # 复制模式 Statement, Row, mixed
skip_slave_start = 1        # 防止从服务器在崩溃后自动开启，以给你足够的时间修复。max_binlog_size = 200M         # 指定二进制日志的大小

# 1.1 需要同步的二进制数据库名
binlog-do-db = test
# 1.2 不同步的二进制数据库名, 如果不设置可以将其注释掉
binlog-ignore-db = information_schema
binlog-ignore-db = mysql

slave 的 my.cnf

 # 1. 主从备份相关配置 - 从服务器
 # 不在这里配置主服务器信息，而是通过命令来配置
 server-id = 2                  # 服务器 id 号，不要和其他服务器重复
 read_only = 1                  # 让从服务器只读，可以防止有人误从服务器插入数据，导致主从数据不一致。log-bin=mysql-bin              # 开启二进制日志
 log_bin_index = mysql-bin.index        # 索引二进制日志的文件名
 log_slave_updates = 1
 relay_log = mysql-relay-bin    # 中继日志 
 relay_log_index = mysql-relay-bin.index
 skip_slave_start = 1           # 防止从服务器在崩溃后自动开启，以给你足够的时间修复。max_binlog_size = 200M         # 指定二进制日志的大小

 # 以下配置是为了方便以后，从库切换为主库
 # 1.1 需要同步的二进制数据库名
 binlog-do-db = test
 # 1.2 不同步的二进制数据库名, 如果不设置可以将其注释掉
 binlog-ignore-db = information_schema
 binlog-ignore-db = mysql

实践过程

配置文件修改完，需要将 mysql 服务重启，使得配置生效；然后在 master 上使用 root 用户创建同步账户并授权 slave；然后查看 master 状态，此时不要再进行 master 的操作，配置 slave 的跟踪 master 日志的位置；最后检查主从备份是否成功。具体实现过程如下：

我这里创建的用户允许任何主机使用正确密码登陆, 也可以指定 ip 地址登陆

// 创建同步账户 repl 并授权
create user 'repl'@'%' identified by 'password'; 
grant all privileges on *.* to 'repl'@'%';

查看 mster 状态

show master status\G

配置 slave 跟踪 master 日志的位置，本地 ip 地址是 192.168.1.90，slave 的 ip 地址是 192.168.92.141，配置过程不需要用到 slave 的 ip，repl 是我们创建的同步账户，接下来是密码，二进制日志文件，和二进制日志中查看到的 positon 5616。

CHANGE MASTER TO
             MASTER_HOST='192.168.1.90',
             MASTER_USER='repl',
             MASTER_PASSWORD='password',
             MASTER_LOG_FILE='mysq-bin.000003',
             MASTER_LOG_POS=5616;

接下来开启 slave, 检查 slave 主从备份的状态，这里 Slave_IO_State 状态是 Wait for master to send event 说明主从备份配置成功了

// 开启 slave
start slave;

// 查看状态
SHOW SLAVE STATUS\G

我们接下来在 test 数据库的 user 表添加一行数据 user4：

看下从库：

看到从库同步增加了一行数据，说明我们利用二进制日志 binlog 实现了主从备份成功了，最后提醒一下，我的数据库版本都是 mysql8,0+ 的版本，如果版本不一致配置会遇到很多麻烦，最好是统一，创建同步账户时，mysql8.0+ 版本的 sql 语句也不一样，如果你的版本在 mysql8.0 以下，可以直接参考其他人的教程，但是大致步骤都是相同的，这篇文章着重于实现二进制日志的主从复制，之后还会出关于事务日志（redo log 和 undo log）的实践，目的为了更好的了解 mysql 日志，本人技术有限，如有错误，欢迎批评指正，谢谢。