MySQL实操(二)——MySQL主从同步实战

叁歲伎倆 2023-09-23 22:04 140阅读 0赞

**MySQL实操系列**

*  [MySQL实操(一)——CentOS7安装MySQL5.7及基础配置\_DreamEhome的博客-CSDN博客\_centos7 中安装mysql5.7配置表名忽略大小写][MySQL_CentOS7_MySQL5.7_DreamEhome_-CSDN_centos7 _mysql5.7]
 *  [MySQL实操(二)——MySQL主从同步实战\_DreamEhome的博客-CSDN博客][MySQL_MySQL_DreamEhome_-CSDN]
 *  [MySQL实操(三)——使用Mycat实现分库分表+读写分离\_DreamEhome的博客-CSDN博客][MySQL_Mycat_DreamEhome_-CSDN]
 *  [MySQL实操(四)——使用Haproxy+keeplived实现Mycat高可用\_DreamEhome的博客-CSDN博客][MySQL_Haproxy_keeplived_Mycat_DreamEhome_-CSDN]

--------------------

**目录**

整体架构

核心流程

实现目标

环境准备

环境搭建

1.Master（192.168.237.135）节点搭建

1.1 修改配置文件并重启MySql服务

1.2 授权哪些slave服务器可以方位到master

1.3 查看master节点状态

2.Slave（192.168.237.136）节点搭建

2.1 修改配置文件并重启MySql服务

2.2 设置主从关系

2.3 开启主从同步并校验从机状态（附带一个异常处理）

3.校验主从复制功能

3.1 在master中操作

3.2 查看slave中是否自动创建数据库并创建表并同步数据

--------------------

## 整体架构 ##

![ba3dd6d0b3fd429fbc325fe6549fb49f.png][]

## 核心流程 ##

*  Master节点的数据增删改操作，binlog线程会自动写入Binary log（二进制日志）;
 *  Slave节点的IO线程远程读取主节点的Binary log，并写入本节点的Relay Log;
 *  Slave节点的SQL线程读取本节点的Relay Log，根据Log内容将相关数据操作写入数据库。

## 实现目标 ##

*  搭建两个MySQL服务
 *  两个MySQL之间实现主从同步

## 环境准备 ##

*  虚拟机（演示用的CentOS7）
 *  已安装配置完成的MySQL两台虚拟机，确保开启远程访问功能（可参考[MySQL实操(一)——CentOS7安装MySQL5.7及基础配置(开机启动+初始密码修改+开启远程访问+不区分大小写)\_DreamEhome的博客-CSDN博客\_centos7 中安装mysql5.7配置表名忽略大小写][MySQL_CentOS7_MySQL5.7_DreamEhome_-CSDN_centos7 _mysql5.7]）
 *  相关服务虚拟机地址
    
     *  MySQL-Master：192.168.237.135
     *  MySQL-Slave： 192.168.237.136

> 演示使用的两个虚拟机是一台环境搭建完成后直接整个虚拟机复制过来的（所以启动从机的数据同步的时候会出现UUID重复的问题，单独装机不会出现此问题）

## 环境搭建 ##

### 1.Master（192.168.237.135）节点搭建 ###

#### 1.1 修改配置文件并重启MySql服务 ####

修改my.cnf配置文件

vim /etc/my.cnf

将以下配置加入到配置文件中

#master唯一标识id
    server-id=1
    #二进制日志文件存放目录,一般同data目录
    log-bin=/var/lib/mysql/mysql-bin
    #错误记录文件
    log-error=/var/lib/mysql/mysql-error
    #主从同步时忽略的数据库，即不同步的数据库，可有多个
    binlog-ignore-db=mysql
    binlog-ignore-db=sys
    #（可选项）制定主从同步时同步哪些数据库，可有多个
    #binlog-do-db=test

保存完成后重启MySQL服务

systemctl restart mysqld

#### 1.2 授权哪些slave服务器可以方位到master ####

进入Mysql命令行，输入密码登录到命令行模式

mysql -u root -p

![1204a77418f9441e975db36d13a96e19.png][]

执行以下sql

--#master授权哪台计算机中的数据库为slave
    --#IP为192.168.237.%下的root用户，密码为123456 
    --#如开启远程访问，需要将IP替换为%
    --grant replication slave,reload,super on *.* to 'root'@'192.168.237.%' identified by '123456';
    grant replication slave,reload,super on *.* to 'root'@'%' identified by '123456';
    --#刷新权限
    flush privileges;

#### 1.3 查看master节点状态 ####

show master status;

查看状态时要记录下**File**和**Position**两个的数据，配置slave时需要用到

![94f9a7be0c214eeeb8d4334382858e5b.png][]

### 2.Slave（192.168.237.136）节点搭建 ###

#### 2.1 修改配置文件并重启MySql服务 ####

修改my.cnf配置文件

vim /etc/my.cnf

将以下配置加入到配置文件中

#master唯一标识id，要注意与主节点的区分
    server-id=2
    #二进制日志文件存放目录,一般同data目录
    log-bin=/var/lib/mysql/mysql-bin
    #需要同步的数据库，可写多个
    replicate-do-db=mydb01
    replicate-do-db=mydb02

保存完成后重启MySQL服务

systemctl restart mysqld

#### 2.2 设置主从关系 ####

进入Mysql命令行，输入密码登录到命令行模式

mysql -u root -p

![1204a77418f9441e975db36d13a96e19.png][]

执行以下sql

--#slave授权哪台计算机中的数据库为master
    --#    master_log_file即master中的File属性
    --#    master_log_pos即master中的Position属性
    change master to 
    master_host='192.168.237.135',
    master_user='root',
    master_password='123456',
    master_port=3306,
    master_log_file='mysql-bin.000001',
    master_log_pos=154;
    --#
    flush privileges;

![a04022ba566948db8b267be9cdd86838.png][]

#### 2.3 开启主从同步并校验从机状态（附带一个异常处理） ####

在MySql命令行中执行以下SQL

#启动slave
    start slave;
    #关闭slave
    #stop slave;
    #查看从机状态
    #直接使用该命令输出会错行，不便于查看，推荐使用  show slave status \G
    show slave status;

通过show slave status可以查看到以下信息，如果**Slave\_IO\_Running**和**Slave\_SQL\_Running** 两个属性都为Yes，则表示从机状态成功，直接进行主从同步校验即可。

如果**Slave\_IO\_Running**和**Slave\_SQL\_Running** 两个属性不为Yes，则需要根据Last\_IO\_Error提示的问题进行处理，如出现以下问题：![92c1af8986964c2b9946225d700c04a5.png][]

问题描述：

> 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.

这个问题的原因是一个环境搭建完成后直接整个虚拟机复制过来的，导致主从两个MySQL的UUID是一样的，如果单独装机则不会出现此问题。**解决方案**为找到slave的auto.cnf文件修改uuid值或删除auto.cnf这个文件

在slave从机找到auto.cnf文件并删除，并重启mysql服务

#查找auto.cnf
    find / -name auto.cnf
    #删除auto.cnf
    rm -rf /var/lib/mysql/auto.cnf
    #重启mysql服务
    systemctl restart mysqld

![ab85b91bd0ac4562bc10328dd728177a.png][]

再次查看从机状态

![2fddf628c517482e925ad03a6156e417.png][]

### 3.校验主从复制功能 ###

#### 3.1 在master中操作 ####

目前master和slave中都没有test数据库。在master中新建test数据库，并在test中新建表user，并写入数据

create database mydb1;
    user mydb1;
    
    CREATE TABLE `user`  (
      `id` int(11) NOT NULL,
      `userName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `userPwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `age` int(11) NULL DEFAULT NULL,
      `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    INSERT INTO `user` VALUES (1, 'admin', '123', 100, '3');

#### 3.2 查看slave中是否自动创建数据库并创建表并同步数据 ####

![9d1bb57ef1634e0e8fb9f8700376101d.png][]

[MySQL_CentOS7_MySQL5.7_DreamEhome_-CSDN_centos7 _mysql5.7]: https://blog.csdn.net/DreamEhome/article/details/110858410
[MySQL_MySQL_DreamEhome_-CSDN]: https://blog.csdn.net/DreamEhome/article/details/128646598
[MySQL_Mycat_DreamEhome_-CSDN]: https://blog.csdn.net/DreamEhome/article/details/128670046
[MySQL_Haproxy_keeplived_Mycat_DreamEhome_-CSDN]: https://blog.csdn.net/DreamEhome/article/details/128682812
[ba3dd6d0b3fd429fbc325fe6549fb49f.png]: https://img-blog.csdnimg.cn/ba3dd6d0b3fd429fbc325fe6549fb49f.png
[1204a77418f9441e975db36d13a96e19.png]: https://img-blog.csdnimg.cn/1204a77418f9441e975db36d13a96e19.png
[94f9a7be0c214eeeb8d4334382858e5b.png]: https://img-blog.csdnimg.cn/94f9a7be0c214eeeb8d4334382858e5b.png
[a04022ba566948db8b267be9cdd86838.png]: https://img-blog.csdnimg.cn/a04022ba566948db8b267be9cdd86838.png
[92c1af8986964c2b9946225d700c04a5.png]: https://img-blog.csdnimg.cn/92c1af8986964c2b9946225d700c04a5.png
[ab85b91bd0ac4562bc10328dd728177a.png]: https://img-blog.csdnimg.cn/ab85b91bd0ac4562bc10328dd728177a.png
[2fddf628c517482e925ad03a6156e417.png]: https://img-blog.csdnimg.cn/2fddf628c517482e925ad03a6156e417.png
[9d1bb57ef1634e0e8fb9f8700376101d.png]: https://img-blog.csdnimg.cn/9d1bb57ef1634e0e8fb9f8700376101d.png