MySQL8.0主从复制的配置

数据库主从复制，就是通过配置把数据（指新写入的数据，旧的数据不会复制）从主数据库复制到从数据库，使得数据保持一致性！
配置主从复制，可以防止数据的丢失以及可实现读写分离提高效率！
前提：数据库版本需要一致！不同版本对应的配置项名称可能存在改动，请自行查看官网文档！

详细介绍可以查看[官方文档]！(https://dev.mysql.com/doc/refman/8.0/en/replication.html)

阅读官方文档可知，我们有两种方法配置主从复制，一种是传统的基于二进制日志文件的方法（需要配置同步日志文件及其位置），另外一种是基于全局事务标识符（GTID）的方法（实际也需要用到二进制日志文件，但不需要配置同步日志文件及其位置）。
以下基于MySQL8.0.27版本就两种不同的方法分别说明（注意：高版本与低版本的配置项会有所不同，详细可查看官网文档）。

（一）基于二进制日志文件的方法

主库的所有变更操作（写入更新删除）都会视为事件，被写入二进制日志文件中。从库通过读取主库的二进制日志文件，并在从库中执行这些事件，达到主从同步。
配置主数据库的 my.cnf 配置文件（只贴核心部分）：

###主从数据库配置核心部分
[mysqld]
# 设置同步的binary log二进制日志文件名前缀，默认为binlog；在MySQL 8.0中，无论是否指定--log bin选项，默认情况下都会启用二进制日志记录，并将log_bin系统变量设置为ON。
log-bin=mysql-bin
# 服务器唯一id，默认为1，值范围为1～2^32−1. ；主数据库和从数据库的server-id不能重复
server-id=1          

###可选配置
# 需要主从复制的数据库，如多个则重复配置
binlog-do-db=test
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步），如多个则重复配置
binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一）
binlog_format=row
# 配置二进制日志自动删除/过期时间，单位秒，默认值为2592000，即30天；8.0.3版本之前使用expire_logs_days，单位天数，默认值为0，表示不自动删除。
binlog_expire_logs_seconds=2592000
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断，默认OFF关闭，可选值有OFF、all、ddl_exist_errors以及错误码列表。8.0.26版本之前使用slave_skip_errors
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062

启动主数据库，创建一个同步复制用户（非必需，也可以用root用户，但不建议）：

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';

说明：

• MySQL创建用户命令：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
• username：指定创建的用户名
• host：指定用户登录的主机ip，% 表示任意主机都可远程登录，localhost 表示本地才能登录
• password：指定该用户的登录密码

接着给创建的用户授权同步复制权限：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

说明：

• MySQL创建授权命令：GRANT REPLICATION SLAVE ON database.table TO 'username'@'host'；
• privilege ：指定授权的权限，比如create、drop等权限，具体有哪些权限，可查看官网文档
• database：指定哪些数据库生效，*表示全部数据库生效
• table：指定所在数据库的哪些数据表生效，*表示所在数据库的全部数据表生效
• username：指定被授予权限的用户名
• host：指定用户登录的主机ip，%表示任意主机都可远程登录

最后刷新权限生效：

FLUSH PRIVILEGES

接着执行命令查看并记下binary log二进制日志文件名 File 以及位置 Position的值，需要在从数据库用到：

SHOW MASTER STATUS;

配置从数据库的my.cnf配置文件（只贴核心部分）：

###主从数据库配置核心部分
[mysqld]
# 设置同步的binary log二进制日志文件名前缀，默认是binlog
log-bin=mysql-bin
# 服务器唯一id，默认为1，值范围为1～2^32−1. ；主数据库和从数据库的server-id不能重复
server-id=2

###可选配置
# 需要主从复制的数据库 ，如多个则重复配置
replicate-do-db=test
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步） ，如多个则重复配置
binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存 
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一） 
binlog_format=row
# 配置二进制日志自动删除/过期时间，单位秒，默认值为2592000，即30天；8.0.3版本之前使用expire_logs_days，单位天数，默认值为0，表示不自动删除。
binlog_expire_logs_seconds=2592000
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断，默认OFF关闭，可选值有OFF、all、ddl_exist_errors以及错误码列表。8.0.26版本之前使用slave_skip_errors
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062
# relay_log配置中继日志，默认采用 主机名-relay-bin 的方式保存日志文件 
relay_log=replicas-mysql-relay-bin  
# log_replica_updates表示slave是否将复制事件写进自己的二进制日志，默认值ON开启；8.0.26版本之前使用log_slave_updates
log_replica_updates=ON
# 防止改变数据(只读操作，除了特殊的线程)
read_only=ON

启动从数据库，执行以下命令设置与主数据库的联系（不同版本语法有所区别）：

低于8.0.23版本的语法：

CHANGE MASTER TO MASTER_HOST='192.168.34.120',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=825;

自8.0.23版本后的语法：

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.34.120',SOURCE_PORT=3306,SOURCE_USER='repl',SOURCE_PASSWORD='123456',SOURCE_LOG_FILE='mysql-bin.000007',SOURCE_LOG_POS=825;

说明：

• MASTER_HOST/SOURCE_HOST：主数据库的主机ip
• MASTER_PORT/SOURCE_PORT：主数据库的端口，不设置则默认是3306
• MASTER_USER/SOURCE_USER：主数据库被授予同步复制权限的用户名
• MASTER_PASSWORD/SOURCE_PASSWORD：对应的用户密码
• MASTER_LOG_FILE/SOURCE_LOG_FILE：在主数据库执行命令show master status 查询到的二进制日志文件名称
• MASTER_LOG_POS/SOURCE_LOG_POS：在主数据库执行命令show master status 查询到的位置 Position的值

最后开启主从复制工作：
低于8.0.22版本的语法：

START SLAVE;

自8.0.22版本后的语法：

START REPLICA;

可执行命令查看详细信息以及状态：
低于8.0.22版本的语法：

SHOW SLAVE STATUS;

自8.0.22版本后的语法：

SHOW REPLICA STATUS;

假如显示 Slave_IO_Running/Replica_IO_Running和 Slave_SQL_Running/Replica_SQL_Running 为 Yes ，以及Slave_IO_State/Replica_IO_State 为 Waiting for master to send event/Waiting for source to send event，则证明主从复制成功！

假如需要停止主从复制工作，则执行以下命令：
低于8.0.22版本的语法：

STOP SLAVE;

自8.0.22版本后的语法：

STOP REPLICA;

假如需要重启主从复制工作，则执行以下命令：
低于8.0.22版本的语法：

RESTART SLAVE;

自8.0.22版本后的语法：

RESTART REPLICA;

测试：
在主数据库创建一个test数据库，并新建user表，然后在主数据库执行插入语句：

INSERT INTO `test`.`user`(`name`) VALUES ('Jack');

接着在从数据库发现已同步test库，查看数据发现已同步复制数据成功！

（二）基于全局事务标识符（GTID）的方法

配置主数据库的 my.cnf 配置文件（只贴核心部分）：

###主从数据库配置核心部分
[mysqld]
# 设置同步的binary log二进制日志文件名前缀，默认为binlog
log-bin=mysql-bin
# 服务器唯一id，默认为1，值范围为1～2^32−1. ；主数据库和从数据库的server-id不能重复
server-id=1     
     
#开启 GTID
gtid_mode=ON
enforce_gtid_consistency=ON

###可选配置
# 需要主从复制的数据库，如多个则重复配置
binlog-do-db=test
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步），如多个则重复配置
binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一）
binlog_format=row
# 配置二进制日志自动删除/过期时间，单位秒，默认值为2592000，即30天；8.0.3版本之前使用expire_logs_days，单位天数，默认值为0，表示不自动删除。
binlog_expire_logs_seconds=2592000
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断，默认OFF关闭，可选值有OFF、all、ddl_exist_errors以及错误码列表。8.0.26版本之前使用slave_skip_errors
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062

启动主数据库，创建一个同步复制用户（非必需，也可以用root用户，但不建议）：

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';

接着给创建的用户授权同步复制权限：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

最后刷新权限生效：

FLUSH PRIVILEGES;

接着执行命令查看状态，对比发现与基于二进制日志文件的方法不同的是Executed_Gtid_Set会有一个具体的全局事务id值：

SHOW MASTER STATUS;

配置从数据库的my.cnf配置文件（只贴核心部分）：

###主从数据库配置核心部分
[mysqld]
# 设置同步的binary log二进制日志文件名前缀，默认是binlog
log-bin=mysql-bin
# 服务器唯一id，默认为1，值范围为1～2^32−1. ；主数据库和从数据库的server-id不能重复
server-id=2

#开启 GTID
gtid_mode=ON
enforce_gtid_consistency=ON

###可选配置
# 需要主从复制的数据库 ，如多个则重复配置
replicate-do-db=test
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步） ，如多个则重复配置
binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存 
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一） 
binlog_format=row
# 配置二进制日志自动删除/过期时间，单位秒，默认值为2592000，即30天；8.0.3版本之前使用expire_logs_days，单位天数，默认值为0，表示不自动删除。
binlog_expire_logs_seconds=2592000
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断，默认OFF关闭，可选值有OFF、all、ddl_exist_errors以及错误码列表。8.0.26版本之前使用slave_skip_errors
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062
# relay_log配置中继日志，默认采用 主机名-relay-bin 的方式保存日志文件 
relay_log=replicas-mysql-relay-bin
# log_replica_updates表示slave是否将复制事件写进自己的二进制日志，默认值ON开启；8.0.26版本之前使用log_slave_updates
log_replica_updates=ON
# 防止改变数据(只读操作，除了特殊的线程)
read_only=ON

启动从数据库，执行以下命令设置与主数据库的联系（不同版本语法有所不同）：

低于8.0.23版本的语法：

CHANGE MASTER TO MASTER_HOST='192.168.34.120',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;

自8.0.23版本后的语法：

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.34.120',SOURCE_PORT=3306,SOURCE_USER='repl',SOURCE_PASSWORD='123456',SOURCE_AUTO_POSITION=1;

说明：

• MASTER_HOST/SOURCE_HOST：主数据库的主机ip
• MASTER_PORT/SOURCE_PORT：主数据库的端口，不设置则默认是3306
• MASTER_USER/SOURCE_USER：主数据库被授予同步复制权限的用户名
• MASTER_PASSWORD/SOURCE_PASSWORD：对应的用户密码
• MASTER_AUTO_POSITION/SOURCE_AUTO_POSITION：

最后开启主从复制工作：
低于8.0.22版本的语法：

START SLAVE;

自8.0.22版本后的语法：

START REPLICA;

可执行命令查看详细信息以及状态：
低于8.0.22版本的语法：

SHOW SLAVE STATUS;

自8.0.22版本后的语法：

SHOW REPLICA STATUS;

假如显示 Slave_IO_Running/Replica_IO_Running 和 Slave_SQL_Running/Replica_SQL_Running 为 Yes ，以及Slave_IO_State/Replica_IO_State 为 Waiting for master to send event/Waiting for source to send event，则证明主从复制成功！

测试：
在主数据库创建一个test数据库，并新建user表，然后在主数据库执行插入语句：

INSERT INTO `test`.`user`(`name`) VALUES ('Rose')

接着在从数据库发现已同步test库，查看数据发现已同步复制数据成功！

---

可能碰到的报错信息：

[ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction '457158dd-6eb9-11ec-b8c9-0242ac130003:1' at master log mysql-bin.000003, end_log_pos 343; Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'CREATE DATABASE `test`', Error_code: MY-001007

解决方法：从库已存在该数据库，先停止主从复制，删除从库的该数据库，最后重新开启主从复制即可。