MySQL5.7主从同步实战

准备工作

环境

虚拟机201 1核 1G IP：192.168.0.201
虚拟机202 1核 1G IP：192.168.0.202 克隆自201，需要修改ip地址和MySQL server UUIDs

201防火墙对202开启3306端口

# 在201上执行
[root@192.168.0.201 ~]# firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.0.202" port protocol="tcp" port="3306" accept"
[root@192.168.0.201 ~]# firewall-cmd --reload

修改MySQL server UUIDs

由于202这台服务器是从201克隆过来的，所有他们的MySQL server UUIDS是一样的，我们需要重新生成一个

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

#修改202上面的uuid

mv /db/mysql/auto.cnf /db/mysql/auto.cnf.back
service mysqld restart

理论知识

MySQL主从复制原理图

MySQL主从复制原理图

binlog日志

1、日志格式

binlog有三种格式：statement、row以及mixed，默认为ROW

基于SQL语句的复制（statement-based replication,SBR）
基于行的复制（row-based replication,RBR）（默认）
混合模式复制（mixed-based replication,MBR）

2、查看binlog格式

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

3、查看binlog中的SQL

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000009
mysqlbinlog --base64-output=decode-rows -v --start-date="2019-05-03 12:30:00" --stop-date="2019-05-03 17:30:00" mysql-bin.000009

一、设置配置文件

1）master服务器201配置文件

[mysqld]
basedir = /usr/local/mysql
datadir = /db/mysql
port = 3306
socket = /tmp/mysql.sock
explicit_defaults_for_timestamp=true
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#可选
max_connections = 1000
back_log = 1024
innodb_buffer_pool_size = 268435456
innodb_log_file_size = 64M
ft_min_word_len = 2


#开启binlog
log-bin=/db/binlog/mysql-bin
max_binlog_size=100M
expire_logs_days=30

#主从配置
server_id=201
binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=db3

设置binlog日志路径

log-bin是设置binlog日志的位置并开启binlog日志的记录
mkdir /db/binlog
chown mysql:mysql /db/binlog

2）slave从服务器202配置文件

[mysqld]
basedir = /usr/local/mysql
datadir = /db/mysql
port = 3306
socket = /tmp/mysql.sock
explicit_defaults_for_timestamp=true
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#可选
max_connections = 1000
back_log = 1024
innodb_buffer_pool_size = 268435456
innodb_log_file_size = 64M
ft_min_word_len = 2

#主从配置
server_id=202
replicate-do-db=db1
replicate-do-db=db2
replicate-do-db=db3

#从库开启binlog
log-bin=/db/binlog/mysql-bin
max_binlog_size=100M
expire_logs_days=30
binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=db3

slave可不开启binlog日志

如果slave不开启binlog日志，那么，下面选项不用配置，可以在前面加#号注释掉

log-bin=/db/binlog/mysql-bin
max_binlog_size=100M
expire_logs_days=30
binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=db3

3）重启主、从MySQL服务

service mysqld restart

4）说明

binlog-do-db和binlog-ignore-db说明

binlog-do-db 需要记录binlog日志的数据库
binlog-ignore-db 不需要记录binlog日志的数据库
两个是对立的，一般只使用其中一个就可以了，如果数据库多，我们可以使用binlog-ignore-db来忽略其中几个，其它没被忽略的就会记录binlog日志

replicate-do-db和replicate-ignore-db说明

replicate-do-db 需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
replicate-ignore-db 不需要复制的数据库，如果多个请重复设置这个选项即可

5）实战测试

1、配置了binlog-do-db=db1，但是像CREATE USER这样的DDL语句是会被记录的，所以从库需要同步哪些数据库就用replicate-do-db来指定

三、在主MySQL服务器操作

1）​创建复制用户并授权

mysql> grant replication slave on *.* to 'repl'@'192.168.0.202' identified by '27OP3B0R9@C?4ex>';

说明：创建syncslave用户并分配replication slave权限（可以show grants;来查看权限​），用户名可以随机生成一个字符串。密码是27OP3B0R9@C?4ex>，可以随机生成一个字符串

mysql> flush privileges;

说明：刷新权限表

2）导出数据

//导出单个数据库
[root@192.168.0.201 ~]# mysqldump -uroot -p --flush-logs --single-transaction --quick --master-data=2 db1 > /data/sql/db.sql

//导出多个数据库
[root@192.168.0.201 ~]# mysqldump -uroot -p --flush-logs --single-transaction --quick --master-data=2 --databases db1 db2 db3 > /data/sql/db.sql

说明：
--lock-all-tables 锁住所有数据库的所有表
--lock-tables 锁住当然导出数据库的所有表（默认）
--skip-lock-tables 不锁表，相当于关闭--lock-tables
--flush-logs 在开始导出前刷新服务器的日志文件，生成新的binlog日志文件
--master-data=2 在导出的时候同时生成binlog文件名和位置在导出的文件开头。这个很重要。binlog的文件和位置可以从这里拿到。
--single-transaction 通过将导出操作封装在一个事务内来使得导出的数据是一个一致性快照，这个选项开启后会自动关闭lock-tables

3）打包压缩

[root@192.168.0.201 ~]# tar -zcPf /data/sql/db.sql.tar.gz /data/sql/db.sql

4）复制到从库

[root@192.168.0.201 ~]# scp /data/sql/db.sql.tar.gz root@192.168.0.202:/data/sql/db.sql.tar.gz

四、在从数据库上操作

1）解压

[root@192.168.0.202 ~]# tar -zxPf /data/sql/db.sql.tar.gz

2）导入主库数据

//导入一个数据库，导入之前请建立好该数据库
[root@192.168.0.202 ~]# mysql -uroot -p db < /data/sql/db.sql

//导入多个数据库
[root@192.168.0.202 ~]# mysql -uroot -p
mysql> source /data/sql/db.sql

3）配置slave同步参数

mysql> change master to master_host='192.168.0.201',master_user='repl',master_password='27OP3B0R9@C?4ex>',master_log_file='mysql-bin.000010',master_log_pos=154;

说明：master_log_file和master_log_pos请看sql文件顶部有
head -n 30 db.sql

4）启动slave同步功能

mysql> start slave;

5）查看salve同步状态

mysql> show slave status\G;

说明：Slave_IO_Running： Connecting表示正在链接Master，正常应该是Yes，这里是因为我Master没启动，Slave_SQL_Running: Yes，正常，保证这两个都是Yes

问答

问：主服务器重启，从服务器会怎样？

答：从服务器会自动每隔60s重试连接主服务器

问：主从都开启了binlog日志，slave会解析从master同步过来的binlog，并执行这些SQL语句，那slave会记录这些SQL语句到binlog吗？

答：默认不会记录，需要在slave上设置log_slave_updates选项，在/etc/my.cnf中加入log_slave_updates=1，这slave才会记录

--log-slave-updates Tells the slave to log the updates from the slave thread

错误处理

出错后，可以尝试重置master和slave

mysql> reset master;
mysql> stop slave;
mysql> reset slave;

在执行3~5步骤

当binlog里面出现SQL级别错误导致主从不能同步的时候，可以用下面方法掠过该错误语句行，继续同步：

mysql> stop slave;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

在进行查看。

一些操作

mysql> PURGE MASTER LOGS TO 'mysql-bin.000099'; //清除mysql-bin.000099之前的日志

mysql> PURGE MASTER LOGS BEFORE '2019-10-01 00:00:00'; //清除2019-10-01 00:00:00之前的日志

彻底清除主从同步

# 在从库上执行
mysql> stop slave; //暂停同步
mysql> reset slave all; //彻底清除同步信息

# 在主库上执行
mysql> reset master;

参考文档

Mysql 5.7从节点配置多线程主从复制的方法详解

mysql主从同步 binlog-do-db replicate-do-db

Mysql5.7 主从复制读写分离

Mysql 5.7 从节点配置多线程主从复制

Mysql 5.7 主从复制的多线程复制配置方式

reset master和reset slave使用场景和说明，以及清除主从同步关系

搭建 MySQL 5.7.19 主从复制，以及复制实现细节分析

mysqldump — A Database Backup Program