MySQL搭建主从架构

MySQL主从复制原理

1. 主库提交完事务后，写入binlog日志
2. 从库连接到主库，请求获取binlog日志
3. 主库创建一个dump线程，将binlog推送到从库
4. 从库开启一个IO线程读取同步过来的binlog，并记录到relay log中继日志中
5. 从库再开启一个sql线程读取relay log中的记录，同步到数据库中
6. 从库记录自己的binlog日志

环境配置

这里准备搭建一主二从的架构，所以需要准备三台服务器（本次版本CentOS7.8），然后在每台上面都安装好MySQL（本次版本5.7.31），不想手动编译安装的话也可以直接用宝塔安装，配置更方便点

MySQL安装可以看这里：Linux安装MySQL5.7.31

开放端口

为了避免后边配置主从复制发生错误，可以先将这三台服务器上的防火墙关闭或者开放防火墙端口。

Linux操作命令可以看这里：Centos7/8开放防火墙端口

MySQL配置

修改MySQL的配置文件，Linux下默认配置文件位置在：/etc/my.cnf 。在【mysqld】模块下加入以下配置（还有很多非必填的配置，可自行百度查看）

# 开启二进制日志（必填）
log-bin = mysql-bin
# 标识唯一id，值随便改，但是三台MySQL的值不能一样（必填）
server-id = 1
# 指定主从同步时忽略的数据库，可设置多个，比如可以把MySQL默认安装的4个库忽略掉
# 配置在主库，但是如果做了MySQL高可用架构的话，在每个库都需要配置
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
# 指定需要同步的数据库，可以设置多个
# 配置在主库，但是如果做了MySQL高可用架构的话，在每个库都需要配置
binlog-do-db = test
# 这个需要配置在从库，但是如果做了MySQL高可用架构的话，在每个库都需要配置
replicate-do-db = test

配置完成后需要重启MySQL service mysql restart

MySQL账号权限配置

创建用于主从复制的账号，也可以直接用已经存在的root账户，最好还是新建个（三个库都需要）

# create user '用户名'@'%' identified by '密码';
create user 'repl'@'%' identified by '123456';

给上边创建的账号授权，允许远程访问（三个库都需要）

# grant all privileges on *.* to '用户名'@'%' identified by '密码' with grant option;
grant all privileges on *.* to 'repl'@'%' identified by '123456' with grant option;

指定哪些库是自己的从库，分配权限（在主库配置）

# grant replication slave on *.* to '从库用户名'@'从库IP' identified by '从库密码'; 
grant replication slave on *.* to 'repl'@'192.168.241.102' identified by '123456';
grant replication slave on *.* to 'repl'@'192.168.241.103' identified by '123456';

配置完成刷新权限配置

flush privileges;

查看主库状态（记录好 File 和 Position，后面要用）

如果该命令执行后结果为 Empty set(0.00 sec)，那说明前面的 my.cnf 没配置对

show master status;

指定哪个是自己的主库（在两台从库中执行）

# CHANGE MASTER TO
# MASTER_HOST='主库IP',
# MASTER_USER='主库用户名',
# MASTER_PASSWORD='主库密码',
# MASTER_LOG_FILE='上述中得到的File',
# MASTER_LOG_POS=上述中得到的Position;
CHANGE MASTER TO
MASTER_HOST='192.168.241.101',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=2061;

PS：如果之前有开启过主从同步的话，这里运行上述SQL可能会报错，解决方法是：先运行 stop slave;，停止同步，再次运行上述SQL，成功后再开启同步 start slave;

开启主从同步

start slave;

同步开启后可检查从库状态

show slave status;
# 想美观点可用 \G 来做结束符
show slave status \G

搭建过程中可能踩的坑

1、报错信息：Slave_SQL_Running：No

2、分析：出现 No 就是同步中止了，可能产生的错误也很多。可能是在从库中插入了数据，且与主库不一致，会导致同步中断 .......

3、解决方案：如果是某一条数据出错导致同步中断，可使用 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 命令跳过这个错误，继续往下执行别的，然后根据错误信息手动去改这条出错的数据

1、报错信息：Slave_IO_Running: No 且 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

2、分析：这句话大概含义就是主从数据库的 server-id 值重复了

3、解决方案：先用 show variables like 'server_id'; 命令检查当前MySQL的 server-id 值，然后去my.cnf配置文件修改这个参数

4、注意：有时候配置文件里参数值不一样，但是用这个命令查看发现是一样的，如何重启MySQL还不行的话，可以先关闭同步（stop slave;），再通过全局变量设置server-id值（set global server_id = 1;），然后再开启同步（start slave），再次查看运行状态（show slave status \G）

1、报错信息：Slave_IO_Running: Connecting 且 Last_IO_Error: error connecting to master 'repl@192.168.241.101:3306' - retry-time: 60 retries: 1

2、分析：连接主机失败

3、解决方案：先查看主从库是否授权，再查看是否是 用户名/密码/IP地址 这几个参数写错了

1、报错信息：Slave_IO_Running: No 且 Last_IO_Error: 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.

2、分析：MySQL5.6之后主从复制引入了uuid的概念，主从MySQL中的server_uuid与server-id类似，也得保证不一样。这里由于整体copy的MySQL，所以主从上server_uuid是相同的，可用show variables like '%server_uuid%'; 命令查看

3、解决方案：找到data文件夹下的auto.cnf文件，直接删掉这个文件，然后重启MySQL服务

当 Slave_IO_Running 和 Slave_SQL_Running 参数都为 Yes 时，则主从同步配置成功