mysql主从同步

时间:2022-09-28 My2538772270 人气:1

基础知识

随着业务复杂度的增加，单台 MySQL 数据库服务器已不能满足实际的需求，取而代之的是数据库服务器集群。MySQL 具有支持分布式的特性，能轻松搭建一个支持高并发的 MySQL 数据库服务器集群。在集群中我们必须保证各个 MySQL 节点的数据是同步的。主从同步就是一种最为常见的同步方式。

主从同步是指，在数据同步过程中，一台服务器充当主服务器（Master），接收来自用户的内容更新，另一个或多个其它的服务器充当从服务器（Slave），接收来自主服务器的 binlog 内容，解析出 SQL 语句，更新到从数据库，使得主从服务器的数据达到一致。

MySQL 主从同步的主要应用场景有：

从服务器作为主服务器的备份节点，防止单点灾难；
后续，可以在主从同步的基础上，通过一些数据库中间件实现读写分离，从而大幅提高数据库的并发性能；
根据业务将多个从服务器进行拆分，实现专库专用。

从 MySQL5.6 版本开始，实现主从数据同步有两种方式：基于日志（binlog）和基于 GTID（全局事务标示符）。

原理

要实现 MySQL 主从同步，首先必须打开 Master 端的 binlog 记录功能，否则就无法实现。因为整个同步过程实际上就是 Slave 端从 Master 端获取 binlog 日志，然后再在 Slave 上以相同的顺序执行从 binlog 日志中所记录的各种 SQL，如下图所示。

主从同步原理：

主数据库中对数据的各种操作，都会自动写入 Binary Log 中；
从数据库会在一定时间间隔内探测主数据库的 Binary Log 是否发生变化，如有变化，则开始一个 IO 线程，请求访问主数据库的二进制日志文件并保存到从数据库的中继日志（Relay Log）中；
从数据库启动 SQL 线程从中继日志中读取二进制日志，在本地重放，使其数据与主数据库保持一致，完成后相关线程会陷入休眠，等待下一次唤醒。

设置主从同步，还有以下几个前提：

主库和从库的版本保持一致；
主从同步集群中每个数据库实例的 server-id 值不能重复；

实验环境模拟实现主从同步

我们在此使用 docker 这样的容器技术在一台主机上实现 mysql 的主从同步。这里需要做一点解释，docker 中存在容器的概念，当启动一个 mysql 容器时，其实可以理解为启动了一台仅仅只安装了 mysql 的服务器。

首先在 docker 中拉取 mysql 5.7 版本的镜像：

docker pull mysql:5.7

使用以下命令，启动一个 Master 容器，将其命名为 masterMysql，并设置 mysql 的 root 用户密码为 123456：

docker run -p 3339:3306 --name masterMysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

使用以下命令可以查看正在运行的容器：

dokcer ps

这里可以看到，主容器已经启动起来了。

同样的方式，启动一个 Slave 容器，将其命名为 slaveMysql, 并设置 mysql 的 root 用户密码为 123456：

docker run -p 3340:3306 --name slaveMysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

通过以下命令进入到 Master 容器内部：

docker exec -it masterMysql /bin/bash

也可以使用查看正在运行容器时查看到的 CONTAINER ID 来启动，例如笔者使用这种方式的启动命令如下：

docker exec -it c30b3528b8c8 /bin/bash

两种启动方式都可以。

由于容器环境下没有安装 vim，所以使用以下命令安装 vim：

sed -i 's/deb.debian.org/mirrors.ustc.edu.cn/g' /etc/apt/sources.list
apt update
apt install vim

使用以下命令切换到 /etc/mysql 目录下，修改配置文件 my.cnf：

cd /etc/mysql
vim my.cnf

在 my.cnf 中添加如下配置：

[mysqld]
server-id=100
sync-binlog=1
binlog-do-db=world
log-bin=mysql-bin

参数说明：

server-id：即主从集群中每个数据库实例 id，在多个服务器间该值不能重复，可以设置 1 - 255 之间的任意值。
sync-binlog：该参数控制数据操作与磁盘日志同步频率。该参数的值 n 表示，执行 n 次写入后，与磁盘同步一次，示例中设置为 1，是最安全的，但也是最慢的。
binlog-do-db：表示准备进行同步的数据库。
log-bin：开启二进制日志功能，可以随便取

之后使用以下命令重启 mysql 使配置文件生效。此时，docker 容器也会停止，还需要启动一次 masterMysql 容器：

service mysql restart

docker start masterMysql
docker exec -it masterMysql /bin/bash

接下来，进入数据库：

mysql -uroot -p123456

在 Master 上配置复制所需要的账户，这里创建一个 slave 用户， % 表示任何远程地址的 slave 用户都可以连接 Master 容器：

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

授予用户 slave replication slave 权限和 replication client 权限，用于在主从库之间同步数据：

GRANT replication slave, replication client ON *.* TO 'slave'@'%';

查看二进制日志状态信息，获取 position 的值，为从服务器配置做准备：

SHOW MASTER STATUS;

重新开启一个 Xfce Terminal，通过以下命令进入到 Slave 容器内部：

docker exec -it slaveMysql /bin/bash

由于容器环境下没有安装 vim，所以使用以下命令安装 vim：

sed -i 's/deb.debian.org/mirrors.ustc.edu.cn/g' /etc/apt/sources.list
apt update
apt install vim

使用以下命令切换到 /etc/mysql 目录下，修改配置文件 my.cnf:

cd /etc/mysql
vim my.cnf

在 my.cnf 中添加如下配置：

[mysqld]
server-id=101
log-bin=mysql-slave-bin
relay_log=edu-mysql-relay-bin

参数说明：

log-bin：开启二进制日志功能，以备 Slave 作为其它 Slave 的 Master 时使用
relay_log：配置中继日志

之后使用以下命令重启 mysql 使配置文件生效。

service mysql restart

docker start slaveMysql
docker exec -it slaveMysql /bin/bash

进入 MySQL：

mysql -uroot -p123456

接下来执行以下命令：

change master to
master_host='172.17.0.2',
master_user='slave',
master_password='123456',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos= 617,
master_connect_retry=30;

参数说明：

master_host：Master 的地址，指的是容器的独立 IP,可以通过docker inspect --format='{{.NetworkSettings.IPAddress}}' masterMysql 查询 Master 的 IP

master_user：Master 中设置的用户名

master_password：Master 中设置的用户名对应密码

master_port: Master 的端口号，指的是容器的端口号

master_log_file：二进制日志文件名(这里注意填写为实验者在之前使用 show master status 命令查询出来的值)

master_log_pos：二进制日志的 position 值(这里注意填写为实验者在之前使用 show master status 命令查询出来的值)

master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是 60 秒

执行以下命令，启动主从操作：

start slave;

执行以下命令，查询 Slave 状态：

show slave status\G

结果如下：

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 617
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

我们主要查看的是 Slave_IO_Running 和 Slave_SQL_Running ，如果它们的值都是 Yes，则表示主从环境配置成功。

测试主从复制就比较简单了，我们仿照之前的实验，在 Master 中新建一个 world 数据库，然后插入一些数据：

CREATE DATABASE world;
USE world;
CREATE TABLE student(
    stuId INT(10) NOT NULL,
    stuName VARCHAR(10) NOT NULL,
    stuAge INT(10) NOT NULL,
    PRIMARY KEY(stuId)
    );
INSERT INTO student(stuId, stuName, stuAge) VALUES(1, 'zhangsan', 18),(2, 'lisi', 19), (3, 'wangwu', 18);

在 Slave 中执行：

SHOW DATABASES;

可以看到我们在 Master 中新建的 world 数据库已经同步过来了。

再执行以下命令查看插入的数据是否也同步过来了：

USE world;
SELECT * FROM student;

可以看到插入的数据也已经同步过来了。

接下来再在 Master 删除一条数据：

DELETE FROM student
WHERE stuId = 1;

再在 Slave 中执行以下命令查看数据是否同步成功：

SELECT * FROM student;

此时主从复制情况良好。

加载全部内容