mysql8.0主从复制

发表于 更新于 分类于

1.传统方式：
基于主库的bin-log将日志事件和事件位置复制到从库，从库再加以应用来达到主从同步的目的。

2.Gtid方式（MySQL>=5.7推荐使用）：
基于GTID的复制中，从库会告知主库已经执行的事务的GTID的值，然后主库会将所有未执行的事务的GTID的列表返回给从库，并且可以保证同一个事务只在指定的从库执行一次。

准备工作

  • 在做主从同步时，如果从机需要主机上原有数据，就要先复制一份到从机。
1
2
3
4
5
# 1. 收集主机原有数据
$ mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql

# 2. 从机复制主机原有数据
$ mysql -uroot -p123456 -h127.0.0.1 --port=3306 < ~/master_db.sql
  • 别忘了开启3306端口,不然会连不上
1
2
3
4
# centos7以上
firewall-cmd --add-port=3306/tcp --permanen # success 表示成功
# 重新载入配置  
firewall-cmd --reload

传统方式

master配置

修改master配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
###主从数据库配置核心部分
# 设置同步的binary log二进制日志文件名前缀，默认为binlog
log-bin=mysql-bin
# 服务器唯一id，默认为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
# 设置二进制日志自动删除/过期的天数，避免占用磁盘空间。默认值为0，表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

创建一个用户，用来同步（也可以是root）

1
create user 'repl'@'%' identified by '123456'

修改用户权限

1
grant replication slave on *.* to 'repl'@'%'

刷新权限

1
flush privileges

查看日志以及位置

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

1
show master status

image-20210421200137174

slave配置

修改配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
###主从数据库配置核心部分
# 设置同步的binary log二进制日志文件名前缀，默认是binlog
log-bin=mysql-bin
# 服务器唯一ID  主数据库和从数据库的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
# 设置二进制日志自动删除/过期的天数，避免占用磁盘空间。默认值为0，表示不自动删除。 
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。 
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致 
slave_skip_errors=1062
# relay_log配置中继日志，默认采用 主机名-relay-bin 的方式保存日志文件 
relay_log=replicas-mysql-relay-bin
# log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
# 防止改变数据(只读操作，除了特殊的线程)
read_only=1

与master建立联系

1
change master to MASTER_HOST='master_ip',MASTER_PORT=master_port,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=116990;
  • MASTER_HOST：主数据库的主机ip
  • MASTER_PORT：主数据库的端口，不设置则默认是3306
  • MASTER_USER：主数据库被授予同步复制权限的用户名
  • MASTER_PASSWORD：对应的用户密码
  • MASTER_LOG_FILE：在主数据库执行命令show master status 查询到的二进制日志文件名称
  • MASTER_LOG_POS：在主数据库执行命令show master status 查询到的位置 Position的值

开启主从复制工作

1
start slave

查看详细信息

1
show slave status \G

image-20210421201109852

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

停止主从复制

1
stop slave

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

master配置

修改master 配置文件

1
2
3
4
5
6
7
8
9
###主从数据库配置核心部分
# 设置同步的binary log二进制日志文件名前缀，默认为binlog
log-bin=mysql-bin
# 服务器唯一id，默认为1  主数据库和从数据库的server-id不能重复
server-id=1     
     
#开启 GTID
gtid_mode=on
enforce_gtid_consistency=true

创建同步用户(与上面一样)

1
create user 'repl'@'%' identified by '123456'

授权用户

1
grant replication slave on *.* to 'repl'@'%'

刷新权限

1
flush privileges

查看状态

1
show master status \G

image-20210421201724219

对比发现与基于二进制日志文件的方法不同的是Executed_Gtid_Set会有一个具体的全局事务id值

slave配置

修改配置文件

1
2
3
4
5
6
7
8
9
###主从数据库配置核心部分
# 设置同步的binary log二进制日志文件名前缀，默认是binlog
log-bin=mysql-bin
# 服务器唯一ID  主数据库和从数据库的server-id不能重复
server-id=2

#开启 GTID
gtid_mode=on
enforce_gtid_consistency=true

创建关联

1
change master to MASTER_HOST='master_ip',MASTER_PORT=master_port,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;

查看状态

1
show slave status

image-20210421202116234

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