MySQL主从复制binlog实现

1 引言

MySQL主从复制的意义在于实现数据的同步和备份，保证数据的安全性和高可用性。主从复制的主要好处包括：

• 数据备份：从服务器上可以有一个备份数据库，实现数据的备份和故障转移。
• 负载均衡：通过将查询分配到不同的服务器上，可以分散数据库负载。
• 读写分离：主服务器负责处理写操作，从服务器处理读操作，减少主服务器的压力。
• 高可用和故障转移：当主服务器出现故障时，可以快速切换到从服务器继续提供服务。
• 扩展性：通过增加从服务器可以提高系统的扩展性。

2 准备工作

如图，准备两个MySQL数据库：

• 主库：33066，从库：33067。
• 分别在主库和从库创建数据库：test。
• 记录下主库支持远程访问的用户名和密码。
  

3 修改配置文件

3.1 主库my.cnf

编辑主库my.cnf文件，在[mysqld]节点下添加配置，然后重启数据库。如下图。

# mysql服务ID，保证整个集群环境中唯一，取值范围1~2^32_1，默认为1。
server_id=1
# 是否只读，1代表只读，0代表读写
read_only=0
# 启动binlog日志，并指定文件名前缀
log_bin=mysql-bin
#超过5天定时清理,0表示Binlog文件永不过期，即不自动清除Binlog文件。
expire_logs_days=5
 #binlog每个日志文件大小
max_binlog_size=100m
# 忽略的数据，指不需要同步的数据库
binlog_ignore_db=test_ignore_001
binlog_ignore_db=test_ignore_002
# 指定同步的数据库
binlog_do_db=test

3.2 从库my.cnf

编辑从库my.cnf文件，在[mysqld]节点下添加配置，然后重启数据库。如下图。

# mysql服务ID，保证整个集群环境中唯一，取值范围1~2^32_1，和主库不一样即可。
server_id=2
# 是否只读，1代表只读，0代表读写
read_only=1
# 启动binlog日志，并指定文件名前缀
log_bin=mysql-bin
#超过5天定时清理,0表示Binlog文件永不过期，即不自动清除Binlog文件。
expire_logs_days=5
 #binlog每个日志文件大小
max_binlog_size=100m

4 从库同步配置

4.1 登录主库， 查看binlog日志文件名和位置。

show master status;

4.2 登录从库，进行同步配置

#8.0.22之前 
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_PORT=3306,
MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=624;
#8.0.22之后
CHANGE REPLICATION SOURCE TO SOURCE_HOST='127.0.0.1', SOURCE_USER='root', SOURCE_PORT=3306,
SOURCE_PASSWORD='123456', SOURCE_LOG_FILE='mysql-bin.000002',
SOURCE_LOG_POS=624;

4.3 登录从库，开启同步操作

#8.0.22之前 
start slave; 
#8.0.22之后
start replica; 

4.4 登录从库，查看主从同步状态

 #8.0.22之前
show slave status;
#8.0.22之后
show replica status; 

5 同步测试

5.1 在主库创建表t_user_a

可以看到从库同时也创建了t_user_a表。

CREATE TABLE `t_user_a` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5.2 在主库t_user_a表新增数据

可以看到从库同样也新增了这条数据，说明主从复制成功。

INSERT INTO test.t_user_a
(id, name, phone, create_time, update_time)
VALUES(1, '张三', '1', '2021-01-01', '2021-01-01');