背景

已经有数据库,做主从复制改造测试。

服务器	备注	数据库
docker1	Master	有1个或多个数据库需要同步,不同步mysql数据库
docker2	Slave1	没有数据库
docker3	Slave2	没有数据库

一、准备测试环境

1、启动数据库实例

这里使用 docker 直接启动三个实例：

docker run -d \
--name docker1.frame-mysql \
--restart unless-stopped \
--privileged=true \
-p 10003:3306 \
-e MYSQL_ROOT_PASSWORD='root1234' \
-e MYSQL_DATABASE='service_demo' \
-e MYSQL_USER='service_demo' \
-e MYSQL_PASSWORD='demo1234' \
-v /data/frame/mysql/data:/var/lib/mysql \
-v /data/frame/mysql/log:/var/log/mysql \
-v /data/frame/mysql/my.cnf:/etc/mysql/my.cnf \
registry.virs.top/mysql:5.7.33 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

docker run -d \
--name docker2.frame-mysql \
--restart unless-stopped \
--privileged=true \
-p 10013:3306 \
-e MYSQL_ROOT_PASSWORD='root1234' \
-v /data/frame/mysql-slave1/data:/var/lib/mysql \
-v /data/frame/mysql-slave1/log:/var/log/mysql \
-v /data/frame/mysql-slave1/my.cnf:/etc/mysql/my.cnf \
registry.virs.top/mysql:5.7.33 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

docker run -d \
--name docker3.frame-mysql \
--restart unless-stopped \
--privileged=true \
-p 10013:3306 \
-e MYSQL_ROOT_PASSWORD='root1234' \
-v /data/frame/mysql-slave1/data:/var/lib/mysql \
-v /data/frame/mysql-slave1/log:/var/log/mysql \
-v /data/frame/mysql-slave1/my.cnf:/etc/mysql/my.cnf \
registry.virs.top/mysql:5.7.33 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

2、建立测试库

在 Master 数据库中，新建多两个数据库：

mysql -u root -p'root1234'
show databases;

CREATE DATABASE `service_demo1` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
CREATE USER 'service_demo1'@'%' IDENTIFIED BY 'demo1234';
GRANT ALL ON service_demo1.* TO 'service_demo1'@'%';

CREATE DATABASE `service_demo2` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
CREATE USER 'service_demo2'@'%' IDENTIFIED BY 'demo1234';
GRANT ALL ON service_demo2.* TO 'service_demo2'@'%';

FLUSH PRIVILEGES;
exit

其反向操作为：

mysql -u root -p'Ht12@34!'
use mysql;
show databases;
select host,user from user;

REVOKE ALL ON service_demo1.* FROM 'service_demo1'@'%';
DROP USER 'service_demo1'@'%';
drop database service_demo1;

REVOKE ALL ON service_demo2.* FROM 'service_demo2'@'%';
DROP USER 'service_demo2'@'%';
drop database service_demo2;

FLUSH PRIVILEGES;

show databases;
select host,user from user;

二、搭建主从架构

1、搭建 Master

第1步：编辑/etc/my.cnf文件，在[mysqld]下增加如下如下设置：
-------------------------------------------------------------------------------------------------------------------------------------------
[mysqld]
log-bin = mysql-bin
server-id = 1
expire-logs-days = 60
max-binlog-size = 500M
binlog-do-db = service_demo
binlog-do-db = service_demo1
binlog-do-db = service_demo2
-------------------------------------------------------------------------------------------------------------------------------------------

第2步：创建用于数据同步的账户
-------------------------------------------------------------------------------------------------------------------------------------------
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl1234';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
-------------------------------------------------------------------------------------------------------------------------------------------

第3步：查看master状态
-------------------------------------------------------------------------------------------------------------------------------------------
show variables like 'log_bin%';
show master status;
-------------------------------------------------------------------------------------------------------------------------------------------

第4步：导出数据库
-------------------------------------------------------------------------------------------------------------------------------------------
# 锁定数据库,不让写入数据
终端1 MySQL： FLUSH TABLES WITH READ LOCK;

# 备份需要做主从同步的数据库
终端2 命令行：
mysqldump -uroot -p'root1234' -B --master-data=1 --events service_demo service_demo1 service_demo2 > /var/lib/mysql/database-backup.sql

# 解除锁定
终端1 MySQL：UNLOCK TABLES;
-------------------------------------------------------------------------------------------------------------------------------------------

2、搭建 Slave1

第1步：编辑/etc/my.cnf文件，在[mysqld]下增加如下如下设置：
-------------------------------------------------------------------------------------------------------------------------------------------
[mysqld]
server-id = 2
-------------------------------------------------------------------------------------------------------------------------------------------

第2步：导入数据从 Master 上导出的数据库
-------------------------------------------------------------------------------------------------------------------------------------------
mysql -uroot -p'root1234' < /var/lib/mysql/database-backup.sql
-------------------------------------------------------------------------------------------------------------------------------------------

第3步：查看当前的同步日志位置
-------------------------------------------------------------------------------------------------------------------------------------------
head -n 30 /var/lib/mysql/database-backup.sql

记下以下信息：
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1738426;
-------------------------------------------------------------------------------------------------------------------------------------------

第4步：执行同步语句
-------------------------------------------------------------------------------------------------------------------------------------------
CHANGE MASTER TO 
    MASTER_HOST='192.168.1.2',
    MASTER_PORT=10003,
    MASTER_USER='repl',
    MASTER_PASSWORD='repl1234',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=1738426;
		
start slave;
-------------------------------------------------------------------------------------------------------------------------------------------

第5步：查看slave状态
-------------------------------------------------------------------------------------------------------------------------------------------
show slave status\G;

出现以下信息则表示成功：
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
-------------------------------------------------------------------------------------------------------------------------------------------

3、搭建 Slave2

第1步：编辑/etc/my.cnf文件，在[mysqld]下增加如下如下设置：
-------------------------------------------------------------------------------------------------------------------------------------------
[mysqld]
server-id = 3
-------------------------------------------------------------------------------------------------------------------------------------------

第2步：导入数据从 Master 上导出的数据库
-------------------------------------------------------------------------------------------------------------------------------------------
mysql -uroot -p'root1234' < /var/lib/mysql/database-backup.sql
-------------------------------------------------------------------------------------------------------------------------------------------

第3步：查看当前的同步日志位置
-------------------------------------------------------------------------------------------------------------------------------------------
head -n 30 /var/lib/mysql/database-backup.sql

记下以下信息：
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1738426;
-------------------------------------------------------------------------------------------------------------------------------------------

第4步：执行同步语句
-------------------------------------------------------------------------------------------------------------------------------------------
CHANGE MASTER TO 
    MASTER_HOST='192.168.1.2',
    MASTER_PORT=10003,
    MASTER_USER='repl',
    MASTER_PASSWORD='repl1234',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=1738426;
		
start slave;
-------------------------------------------------------------------------------------------------------------------------------------------

第5步：查看slave状态
-------------------------------------------------------------------------------------------------------------------------------------------
show slave status\G;

出现以下信息则表示成功：
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
-------------------------------------------------------------------------------------------------------------------------------------------

4、为 Salve 设置只读

后续 Slave1、Slave2 只用于做数据读取

-------------------------------------------------------------------------------------------------------------------------------------------
CREATE USER 'service_read'@'%' IDENTIFIED BY 'read1234';
GRANT SELECT ON service_demo.* TO 'service_read'@'%';
GRANT SELECT ON service_demo1.* TO 'service_read'@'%';
GRANT SELECT ON service_demo2.* TO 'service_read'@'%';
FLUSH PRIVILEGES;

set password for 'service_read'@'%' = password('read1234'); 
-------------------------------------------------------------------------------------------------------------------------------------------

5、测试是否生效

在 Master 上新增表和数据，然后在 Slave1、Slave2 上观察数据是否同步即可。

原创不易，如需转载，请标明出处！

---