Mysql之主从复制

1.概述

MySQL

的主从复制（

Master-Slave Replication

）是一种数据复制解决方案，将主数据库的

DDL

和

DML

操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而是的从库和主库的数据保存同步。

MySQL

支持将数据从一个

MySQL

服务器（主服务器）复制到一个或多个其他

MySQL

服务器（从服务器），从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL

主从复制的优点主要包含以下三个方面：

• 主库出现问题，可以快速切换到从库提供服务；
• 实现读写分离，降低主库的访问压力；
• 可以在从库中执行备份，以避免备份期间影响主库服务；

需要注意的是，

MySQL

的主从复制是异步的，这意味着从服务器的数据可能会与主服务器的数据存在一定的延迟。因此，在使用主从复制时，需要根据具体的业务场景和需求来选择合适的配置和策略。

2.工作原理

从上图来看，主从复制分成三步：

1. Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中；
2. 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log；
3. Slave重做中继日志中的事件，将改变数据更新同步到从库中；

简单说就是

Master

主库上执行的增删改的

SQL

语句同步到对应的

Slave

从库上，然后再在

Slave

从库中同样再次执行一遍

SQL

语句以作备份。

3.综合案例

3.1前期准备

准备两台虚拟机，需要提前安装好

MySQL

数据库（必须要开启二进制日志）。如下所示：
主从库IP地址主库192.168.233.137从库192.168.233.138

注意：以上只是示例说明，具体以自己的虚拟机情况为主。

3.2主库配置

1）修改主库服务器的

MySQL

核心配置文件

/etc/mysql/mysql.conf.d/mysqld.cnf

，并添加如下配置信息：

[mysqld]
...
# 开启二进制日志（必须）
log-bin = mysql-bin
# MySQL服务ID，保证整个集群环境中唯一，默认为1（必须）
server-id = 1
# 二进制日志格式，默认ROW（可选）
binlog_format = ROW
# 忽略的数据，不需要同步的数据库
# binlog-ignore-db = mysql
# 指定同步的数据库
# binlog-do-db = db01

注意：这里

binlog-ignore-db

和

binlog-do-db

配置项没有指定，默认同步所有数据库信息。

2）重启

MySQL

服务器。

systemctl restart mysql

3）登录

MySQL

数据库，创建远程连接的账号，并授予主从复制权限。

# 创建zking用户，并设置密码，该用户可在任意主机连接该MySQL服务
create user 'zmsaw'@'%' identified with mysql_native_password by 'zmsaw1234';
# 为'zking'@'%'用户分配主从复制权限
grant replication slave on *.* to 'zking'@'%';

4）通过指令，查看二进制日志坐标

show master status;

字段含义说明：
字段说明File当前正在写入的二进制日志文件名Position当前正在写入的二进制日志文件的位置Binlog_Do_DB指定需要写入二进制日志的数据库名Binlog_Ignore_DB指令不需要写入二进制日志的数据库名

3.3从库配置

1）修改从库服务器的

MySQL

核心配置文件

/etc/mysql/mysql.conf.d/mysqld.cnf

，并添加如下配置信息：

[mysqld]
...
# 开启二进制日志（必须）
log-bin = mysql-bin
# MySQL服务ID，保证整个集群环境中唯一，默认为1（必须）
server-id = 2
# 二进制日志格式，默认ROW（可选）
binlog_format = ROW
# 是否只读，1代表只读，0代表读写
read-only = 1

2）重启

MySQL

服务器。

systemctl restart mysql

3）登录

MySQL

数据库，设置主库配置。

• MySQL8.0.23之前的版本，执行如下SQL语句：

change master to master_host='xxx.xxx.xxx.xxx',master_user='xxx',master_password='xxx',master_log_file='xxx',master_log_pos=xxx;

记得自行修改！！

• MySQL8.0.23之后的版本，执行如下SQL语句：

change replication source to source_host='xxx.xxx.xxx.xxx',source_user='xxx',source_password='xxx',source_log_file='xxx',source_log_pos=xxx;

记得自行修改 ！！

参数说明：
参数名含义8.0.23之前source_host主库IP地址master_hostsource_user连接主库的用户名master_usersource_password连接主库的密码master_passwordsource_log_filebinlog日志文件名master_log_filesource_log_posbinlong日志文件位置master_log_pos
4）开启同步操作

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

5）查看主从同步状态

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

上述图中显示

Slave_IO_Running: No

，很明显主从复制开启失败。经过问题分析之后，发现是虚拟机是克隆的，导致主库和从库的

MySQL

的

server id

都是一样的。解决方案：修改任意主库和从库的

server id

即可解决问题。

修改

/var/lib/mysql/auto.cnf

文件。将

server-uuid

属性修改为唯一值即可。

[auto]
server-uuid = 任意uuid

修改完毕保存并退出，最后重启

MySQL

服务后，并再次登录

MySQL

查看主从复制是否成功。

3.4常见问题

3.4.1主从同步出现一下错误：Slave_IO_Running: No

主要原因是由于从机的虚拟机是克隆的主机的虚拟机，因此会出现主机和从机的UUID相同，Slave_IO_Running的状态为No

也可以通过vim /var/log/mysql/error.log找到发生错误的原因

生成一个新的UUID，修改从机的UUID

select uuid();

打开cd /var/lib/mysqlm目录里面的auto.cnf文件修改从机的UUID保存后重启，就可以解决该问题

3.4.1主从同步出现一下错误：Slave_IO_Running: Connecting

#去主库新建一个二进制文件
flush logs;

#去从库停止

stop slave;

#重新授权，记得修改新的二进制文件名和二进制文件位置

change master to master_host=‘xxxxx’,master_port=xx,master_user=‘xxx’,master_password=‘xxx’,master_log_file=‘mysql-bin.000001’,master_log_pos=858

#启动

start slave;

#再次查看

show slave status; 

3.5数据测试

1）登录主库

MySQL

，并执行以下

SQL

语句：

# 切换数据库
use db1;
# 创建数据表t_student
create table t_student(sid int primary key auto_increment,sname varchar(20) not null,sage int default 0,ssex varchar(2) default '1');
# 批量添加数据
insert into t_student(sname,sage,ssex) values('张三',26,'男'),('王五',22,'女'),('小七',23,'女');

2）登录从库

MySQL

，查看主从复制结果：

# 切换数据库
use db1;
# 查看是否存在t_student表
show tables;
# 查看t_student表中是否存在数据
select * from t_student;

存在数据即

MySQL

主从复制同步成功。