实现 MySQL 主从复制配置

MySQL 的主从复制

主从复制主要操作是让一台服务器 Master 的数据和其他服务器 Slave 保持同步。一台 Master 数据可以同步到多台备库 Slave 上，同时一台 Slave 本身也可以配置另外一台服务器的 Master，Master 库和 Slave 库可以有多种不同的组合。

MySQL 主从复制的作用

在企业实际的开发中，后端 MySQL 数据库只有一台的时候，会有以下问题：

• 单台 MySQL 服务故障不可用
• 无法处理大量的并发数据请求
• 数据会有丢失的风险

如果是通过主从复制，它的优点就很明显，如下：

• 如果 Master 主节点出现故障，那么我们就直接将服务切到 Slave 从节点，来保证服务立马可用
• 如果并发请求特别大的时候，我们可用进行读写分离操作，让 Master 主库负责写，Slave 从库负责读
• 如果 Master 主库数据丢失，Slave 从库还保存一份，减少数据丢失的风险

主从复制原理

主从复制流程图：

执行流程说明：

1. Master 主库进行 update、insert、delete 操作，按照顺序写入 bin log 中。当 Slave 连接到 Master 主库后,Master 主库会为 Slave 开启 binlog dump thread 线程,该线程会去读取 bin log日志
2. Slave 从库生成两个线程，一个 I/O thread 线程，一个 SQL thread 线程
3. I/O thread 线程去请求 Master 主库的 big log，请求通过 binlog dump thread 读取 bin log日志，并将得到的 big log 日志写入到 relay log(中继日志)文件中，中继日志记录数据更新的信息
4. Slave 从库的 SQL thread 线程会实时监控 relay log 日志中内容是否有更新，如果有就会将更新的内容同步到 Slave 从库数据库中，这样就保证了主从的数据的同步

准备环境

IP	操作系统	主机名称	内核	安装软件
192.168.2.125	centos7	master-host	3.10	MySQL8.0
192.168.2.126	centos7	slave-host	3.10	MySQL8.0

这里注意，需要修改主机名称，使用这个命令 hostnamectl set-hostname xxx（主机名称）


MySQL 安装

192.168.2.125 和 192.168.2.126 安装 MySQL 的步骤都是一样的，以安装在 192.168.2.125 为例

下载地址：https://dev.mysql.com/downloads/mysql/


这里最好复制链接之后采用迅雷进行下载，速度会很快。

4、移除 mariadb

4.1、安装之前先检测有没有mariadb，如果有则移除，执行如下命令

rpm -qa | grep mariadb

存在进行移除

rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps

再次查看有没有，没有则表示移除干净了

rpm -qa | grep mariadb

4.2、我们使用 cd 跳转到 /usr/local 目录下，并执行 mkdir mysql 命令，创建 mysql 目录并上传至mysql文件下。

4.3、执行解压命令进行解压 tar -xvf xxx.tar

执行安装命令进行安装

1、安装common：

命令:rpm -ivh mysql-community-common-8.0.29-1.el7.x86_64.rpm --nodeps --force

2、安装libs：

命令:rpm -ivh mysql-community-libs-8.0.29-1.el7.x86_64.rpm --nodeps --force

3、安装client

  命令:rpm -ivh mysql-community-client-8.0.29-1.el7.x86_64.rpm --nodeps --force

4、安装server：

命令:rpm -ivh mysql-community-server-8.0.29-1.el7.x86_64.rpm --nodeps --force

5、查看已安装的包

命令:rpm -qa | grep myslq

6、通过以下命令完成 mysql 初始化操作

命令:mysqld --initialize

7、启动 mysql 服务

命令:systemctl start mysqld.service

这里会报一个错误出来

使用 cat /var/log/mysqld.log | grep ERROR 这个命令去查看报什么错误。

这是因为没有授权的问题，我们的 mysql 安装在 /var/lib/mysql 的，然后我们要给它授权

命令：chown mysql:mysql /var/lib/mysql -R;

8、配置开机启动

命令:systemctl enable mysqld.service

9、查看数据库的默认密码

命令:cat /var/log/mysqld.log | grep password

10、密码登录数据库

命令:mysql -u root -p

11、修改密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

修改密码后退出重新登录就好。

12、授权远程访问

create user 'root'@'%' identified with mysql_native_password by '123456';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

13、修改加密规则，目前可视化工具如Navicat普遍只支持MySQL5.X，所以我们需要调整8.0的加密规则

-- 修改命令
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER;
-- 刷新权限
flush privileges;

14、可视化客户端连接服务需要开放对外端口

14.1、查看防火墙状态

systemctl status firewalld

14.2、启动防火墙，防火墙启动后，除了 22 端口对外能够访问，其他端口均不能使用，所以需要添加

启动防火墙

systemctl start firewalld

添加端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

重新加载

firewall-cmd --reload

15、使用 Navicat 数据库管理工具测试是否可以连接，如果出现以下的结果，证明已经配置成功了。



16、在主库设置一个复制使用的账号，并授予 REPLICATION SLAVE 权限，REPLICATION SLAVE 常用于建立复制时所需要用到的用户权限，也就是 Slave 必须被 Master 授权具有该权限的用户，才能通过该用户复制，登录 Master 主库的 MySQL 执行以下的命令：

# 192.168.2.126 是从库 IP，创建账号允许 Slave 从库向 Master 主库请求 big log，IDENTIFIED WITH mysql_native_password用于设置密码
CREATE USER 'smysql'@'192.168.2.126' IDENTIFIED WITH mysql_native_password BY '123456'
# 给 smysql 账号赋予权限
GRANT REPLICATION SLAVE ON *.* TO 'smysql'@'192.168.2.126'
# 刷新
flush privileges;

使用 mysql -u root -p 登录时如果报以下的错误：

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

打开 my.cnf 配置文件，然后在里面找到 [mysqld] 这一项，在这项添加 skip-grant-tables 这个配置，然后保存文件。

为了使上一步的配置项生效，我们需要重启 MySQL 的服务，重新登录就正常了

重新设置 root 的密码

# 进入 mysql 数据库
use mysql;
# 修改 user 表 root 用户的密码为 123456
alter user 'root'@'localhost' IDENTIFIED BY '123456';

执行修改密码会报一个错误

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

需要先执行下 flush privileges 命令，再执行该命令即可。

我们修改完默认密码后，再次进入到之前的配置文件中，将 skip-grant-tables 配置行给删除掉，变为系统原先的配置。重启 MySQL 服务，下次再登录的时候便可以解决掉这个问题了。

下面我们再执行赋予权限命令

# 192.168.2.126 是从库 IP，创建账号允许 Slave 从库向 Master 主库请求 big log，IDENTIFIED WITH mysql_native_password用于设置密码
CREATE USER 'smysql'@'192.168.2.126' IDENTIFIED WITH mysql_native_password BY '123456'
# 给 smysql 账号赋予权限
GRANT REPLICATION SLAVE ON *.* TO 'smysql'@'192.168.2.126'
# 刷新
flush privileges;

17、开启 big log并且设置 server-id 的值

# 创建存放 big log 日志的文件路径
mkdir /var/log/mysql
# 指定文件的拥有者，不然重启可能报错
chown mysql.mysql /var/log/mysql
#编辑 my.cnf 配置
vi /etc/my.cnf
加入以下参数：
log-bin=/var/log/mysql/mysql-bin.log
server-id=1
# 重启数据库服务
systemctl restart mysqld

18、获取 Master 主库上当前的二进制日志名和偏移量值，主要是为了在从库启动以后，从这个偏移量开始进行数据的恢复

show master status;

19、修改 Slave 从库的配置文件 my.cnf ，添加 server-id，这里的 server-id 的值必须唯一，也就是不能和主库的 server-id 相同，如果多 Slave 从库也是必须唯一的

# 编辑从库的 my.cnf 配置
vi /etc/my.cnf
#添加如下值：
server-id=2
# 重启数据库服务
systemctl restart mysqld

20、对 Slave 从库进行设置，登录从库的 MySQL ，指定复制使用的用户、Master 主库服务器IP、端口、开始复制的文件日志、偏移量

# 这里 MASTER_HOST 指定主库
CHANGE MASTER TO MASTER_HOST='192.168.2.125',MASTER_PORT=3306,MASTER_USER='smysql',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=157;
# 启动 Slave 线程
start slave;
# 查看配置是否正确
show slave status\G;

启动线程，但是 I/O 没有启动成功，并且是报了一个错误：

意思是：致命错误：从I/O线程停止，因为主服务器和从服务器具有相同的MySQL服务器UUID；这些UUID必须不同，复制才能工作。

这里我们知道了是 UUID 一致出现的问题，为了验证是不是 UUID 的问题我们需要对比一下，使用如下命令查看：

show variables like '%server_uuid%';

在这里插入图片描述


果然是 UUID 一样导致的报错，UUID 重复是因为我使用了 VMware 克隆了两台虚拟机作为主机和从机，所以就报了此错。

这里我们找到主机和从机的 auto.cnf 文件，我的在 rm -rf /var/lib/mysql/auto.cnf
修下，改 uuid 值或删除 auto.cnf 这个文件。

然后执行 systemctl restart mysqld 命令重启主机和从机的 mysql 服务

执行完删除操作，I/O 线程已经开启

21、最后验证主从复制

主从同步是没有问题的，我在 2.125 这个主库创建一个 myMaster 数据库，同步到了 2.126 从库当中。

到此为止，实现 MySQL 主从复制配置讲解已经讲完。