mysql 自动主从切换脚本 mysql 主从切换工具

转载

kcoufee 2024-06-16 14:37:39

文章标签 mysql 自动主从切换脚本 mysql 数据库 docker 文章分类 MySQL 数据库

在最近的一个项目中，读写分离发生了不同步的问题，造成不同步的原因较多，在此不一一分析，但重新同步对于较大的数据库来说，比较麻烦。

常见的重新同步方式

常见的同步方式不外乎下面几种：

导出-重新导入到读库-重新同步

首先将已经失去同步的从库数据库删除。
在主库运行SHOW MASTER STATUS;命令，以查询当前主库日志位置。
将主库数据用mysqldump工具导出
将导出的数据写到从库，并按照之前查询到的主库日志位置进行主从同步。

这种方法对于较小的主从数据库来说，问题不大，也简单易行，但是对于数据库较大的场景来说，就会存在一定问题。首先，如果数据库较大，dump的时间将会较长，对于正在运行的业务来说会有较大影响（mysqldump会有锁表）；其次，较大数据库的导入会比较久，经过测试，在10G以上的数据库，在8核，16G，SSD的服务器上的导入时间为5小时以上（如果有较多的关联，索引列，导入时间会更久），这种较长的导入过程对于维护来说相对麻烦；最后，如果导入的时间太长，超过了主库的日志保存时间，会造成导入之后无法在主库导出数据之前获取的日志点进行同步的问题，也就是说同步会失败。

总之，数据库较大的时候，利用导入并重新导入的方式进行数据库重新同步，难度较大。

直接拷贝数据文件到从库再重新同步

直接拷贝数据库文件的方式，比mysqldump导出之后再导入的速度要快很多，基本上就是网络或磁盘的IO速度，但是这种方式对于innodb类型的库，直接会造成问题，基本上拷贝过去之后无法重新启动起来。对于Myissm类型数据库问题不大，但是目前大多数生产系统都会较多的使用innodb方式，故这种方式的可行性也并不高。

xtrabackup工具介绍

xtrabackup是著名的Percona团队推出的一款Mysql(Percona, MariaDB) 数据库备份/恢复工具，该工具采用了直接拷贝的方式对数据库进行备份，然后在拷贝的过程中会读取数据库日志文件，保证数据库的正确性，解决了导入/导出的速度问题，也解决了直接拷贝的方式innodb数据库会出错的问题。

利用该工具，可以轻松的解决数10G以上规模数据库的导入导出，最重要的是，对主库几乎没有影响便能较快的完成从库的重新同步。

操作流程演示

本文将在接下来的篇幅中，通过Docker的方式创建两个数据库来模拟生产中失去同步的主从库，然后通过xtrabackup工具恢复同步

首先，需要一台已经配置好docker环境的宿主机。我们将在该机器上运行两个mysql容器。

主从库相关配置

规定目录结构如下：主库数据存储位置： /opt/local/mysql/master/data

主库相关配置(此处例子只做最简单的配置，该配置并不适宜生产环境)

[client]
default-character-set=utf8

[mysqld]
datadir=/opt/mysql/data
symbolic-links=0
log-bin=master-bin
server-id=0
log-bin-index=master-bin.index
binlog-do-db=simpledb
character-set-server=utf8

[mysqld_safe]

从库数据存储位置： /opt/local/mysql/slave/data

从库相关配置(此处例子只做最简单的配置，该配置并不适宜生产环境)

[client]
default-character-set=utf8

[mysqld]
datadir=/opt/mysql/data
symbolic-links=0
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
replicate-do-db=simpledb
character-set-server=utf8

[mysqld_safe]

主从库创建命令

通过下面的命令创建主从库，其中相关参数根据自己情况请自行修改调整。

docker create --name mysql56-master \
-v /opt/local/mysql/master/my.cnf:/etc/mysql/my.cnf \
-v /opt/local/mysql/master/data:/opt/mysql/data \
-e MYSQL_ROOT_PASSWORD=admin1234 -p 18992:3306 mysql:5.6

docker create --name mysql56-slave \
-v /opt/local/mysql/slave/my.cnf:/etc/mysql/my.cnf \
-v /opt/local/mysql/slave/data:/opt/mysql/data \
-e MYSQL_ROOT_PASSWORD=admin1234 -p 18993:3306 mysql:5.6

这样则创建了两个mysql数据库，并通过18992和18993端口映射到宿主机上。

构建测试数据

通过下面的SQL脚本，在宿主机上创建一些测试数据

DROP DATABASE if exists simpledb;

CREATE database simpledb;

USE simpledb;

DROP TABLE IF EXISTS tb_users;
CREATE TABLE tb_users ( 
	`accountId` INT NOT NULL,
	`userName` VARCHAR(50) NOT NULL,
	`realName` varchar(50) NOT NULL DEFAULT '',
	`phoneNumber` VARCHAR(25) NULL DEFAULT '',
	`email` VARCHAR(50) NULL DEFAULT '',
    PRIMARY KEY(`accountId`)
)DEFAULT CHARSET=utf8;

INSERT INTO tb_users (accountId, userName, realName, phoneNumber, email ) VALUES (0, 'admin','管理员', '13900000000', 'admin@admin.com');
INSERT INTO tb_users (accountId, userName, realName, phoneNumber, email ) VALUES (1, 'user01','用户', '13200000000', 'user01@ok.com');
INSERT INTO tb_users (accountId, userName, realName, phoneNumber, email ) VALUES (2, 'guest01','访客', '13100000000', 'guest@usa.com');

DROP TABLE IF EXISTS tb_hotels;
CREATE TABLE tb_hotels (
	`hotelId` 	INT NOT NULL,
	`hotelName` VARCHAR(200) NOT NULL,
	`address` 	VARCHAR(200) NOT NULL,
	`stars` 	tinyint(4) NOT NULL DEFAULT 1,
	PRIMARY KEY(`hotelId`)
)DEFAULT CHARSET=utf8;

INSERT INTO tb_hotels (hotelId, hotelName, address, stars ) VALUES (0, '四季酒店','解放路001号', 5);
INSERT INTO tb_hotels (hotelId, hotelName, address, stars ) VALUES (1, '洲际酒店','希望路002号', 5);

此时，我们假定从库已经失去同步，于是需要做的工作是，从主库进行数据导出，然后导入到从库中进行重新同步。

我们需要安装xtrabackup工具首先进行导出工作。本文将使用docker构建一个xtrabackup的简单镜像，如果需要在操作系统中安装，请访问xtrabackup的官方文档。

https://www.percona.com/software/mysql-database/percona-xtrabackup

xtrabackup容器创建

Dockerfile如下

FROM centos

RUN yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
RUN yum install -y percona-xtrabackup-24

在文件所在目录执行docker镜像的编译命令，构建一个xtrabackup的镜像

docker build -t nox/xtrabackup .

然后创建下面的目录，用以存放备份数据库的文件。

/opt/local/mysql/xtrabackup/backup-data

运行以下命令，创建一个xtrabackup容器，其中涉及到的文件和目录在前文已有提及，熟悉Docker的用户应该能轻易看懂。

docker run --name nox-xtrabackup -d -i -t \
-v /opt/local/mysql/master/data:/opt/master-data \
-v /opt/local/mysql/master/my.cnf:/opt/my-master.cnf \
-v /opt/local/mysql/slave/data:/opt/slave-data \
-v /opt/local/mysql/slave/my.cnf:/opt/my-slave.cnf \
-v /opt/local/mysql/xtrabackup/backup-data:/opt/backup-data \
nox/xtrabackup /bin/bash

此时，宿主机能搜索到的Docker容器如下：

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                            NAMES
6bc657cb9808        mysql:5.6           "docker-entrypoint.sh"   31 seconds ago      Up 22 seconds       0.0.0.0:18992->3306/tcp          mysql56-master
f50670c0999f        mysql:5.6           "docker-entrypoint.sh"   17 seconds ago      Created                                              mysql56-slave
f4b3af4169e7        nox/xtrabackup      "/bin/bash"              3 seconds ago       Up 3 seconds                                         nox-xtrabackup

上面的三个容器，状态分别假设为：

master库正常运行
slave库已经失去同步，等待恢复
xtrabackup容器已经关联好了两个容器相关目录，随时可以进行备份/恢复操作

利用xtrabackup进行数据备份

执行下面的命令即可对主库进行备份

docker exec -it nox-xtrabackup xtrabackup --defaults-file=/opt/my-master.cnf \
--backup --target-dir=/opt/backup-data/ \
--datadir=/opt/master-data \
--user=root --host=172.17.0.1 \
--port=18992 \
--password=admin1234

其中参数含义如下：

--defaults-file为主库的my.cnf配置文件，已经在创建xtrabackup容器的时候挂载到了xtrabackup容器中
--backup 备份
--target-dir 目标目录（数据需要备份到的目录，注意，该目录需要为空，否则备份程序会报错）
--datadir 源目录（主库的数据目录）
其余参数为连接主库的相关参数

执行命令之后，我们会看到一些备份信息输出，类似这样的输出信息，表明拷贝表：

......
170807 14:26:37 [01] Copying ./simpledb/tb_hotels.frm to /opt/backup-data/simpledb/tb_hotels.frm
170807 14:26:37 [01]        ...done
170807 14:26:37 [01] Copying ./simpledb/db.opt to /opt/backup-data/simpledb/db.opt
170807 14:26:37 [01]        ...done
......

最终，如果备份正常完成，会看到如下信息：

......
170807 14:26:37 Executing UNLOCK TABLES
170807 14:26:37 All tables unlocked
170807 14:26:37 Backup created in directory '/opt/backup-data/'
MySQL binlog position: filename 'master-bin.000005', position '120'
170807 14:26:37 [00] Writing /opt/backup-data/backup-my.cnf
170807 14:26:37 [00]        ...done
170807 14:26:37 [00] Writing /opt/backup-data/xtrabackup_info
170807 14:26:37 [00]        ...done
xtrabackup: Transaction log of lsn (1663331) to (1663331) was copied.
170807 14:26:37 completed OK!

最后一行completed OK表明复制结束

其中 MySQL binlog position这行表明了主库在备份结束时的日志位置，容后，我们在恢复从库的时候，可以从该日志点进行恢复，这就保证了，在备份结束 - 重新回复从库这段时间内，写入的数据，能够顺利的通过日志同步到从库中。这是因为在生产环境中，我们并没有停止主库运行，也没有对主库进行表锁定，目前依然会有数据进入到主库，我们需要保证在从库导入数据之后，能够将这段时间的数据顺利同步。

所以，记得一定要将 filename 'master-bin.000005', position '120' 保存下来，在从库进行导入之后，需要通过该信息进行数据同步，有过mysql主从同步经验的工程师应该会非常明白这一点。

此时，我们在对从库进行导入/同步之前，在主库运行以下脚本，以模拟生产环境中有数据进入主库的情况，稍后在从库进行成功导入/同步以后，进行验证。

INSERT INTO tb_users (accountId, userName, realName, phoneNumber, email ) VALUES (3, 'user02','用户2', '13900000002', 'user02@ok.com');
INSERT INTO tb_users (accountId, userName, realName, phoneNumber, email ) VALUES (4, 'user03','用户3', '13900000003', 'user03@ok.com');
INSERT INTO tb_users (accountId, userName, realName, phoneNumber, email ) VALUES (5, 'user04','用户4', '13900000003', 'user04@ok.com');

利用xtrabackup进行导入前的准备

xtrabackup导出之后的数据需要运行xtrabackup的一个命令进行整理，以保证数据的正确

docker exec -it nox-xtrabackup xtrabackup --prepare --target-dir=/opt/backup-data/

其中

--prepare参数表明进行导入前准备
--target-dir是之前备份数据所存储的目录

执行之后会看到如下输出

......
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 1663509
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1663528
170807 14:38:31 completed OK!

可以看到最终输出的 completed OK! 表明操作成功，已经可以进行导入了

利用xtrabackup将数据导入到从库

执行下面的命令即可将之前从主库导出的数据导入到从库中（此时应该停止从库容器）：

docker exec -it nox-xtrabackup xtrabackup \
--defaults-file=/opt/my-slave.cnf \
--copy-back --target-dir=/opt/backup-data --datadir=/opt/slave-data

其中：

--defaults-file是从库的my.cnf文件，在之前创建xtrabackup容器时挂载
--copy-back 表明导入
--target-dir 目录是之前备份主库时候的目标目录，此时，是导入到从库时的源目录
--datadir 是从库的数据存储目录，该目录要求为空（因为从库数据已经失去同步，本身也没有意义，删空即可）

执行之后，会看到大量的copy相关信息在屏幕上打印出来，表明正在导入数据

......
170807 14:56:04 [01] Copying ./simpledb/tb_hotels.frm to /opt/slave-data/simpledb/tb_hotels.frm
170807 14:56:04 [01]        ...done
170807 14:56:04 [01] Copying ./simpledb/tb_users.ibd to /opt/slave-data/simpledb/tb_users.ibd
170807 14:56:04 [01]        ...done
170807 14:56:04 [01] Copying ./simpledb/tb_hotels.ibd to /opt/slave-data/simpledb/tb_hotels.ibd
170807 14:56:04 [01]        ...done
170807 14:56:04 [01] Copying ./simpledb/db.opt to /opt/slave-data/simpledb/db.opt
170807 14:56:04 [01]        ...done
170807 14:56:04 [01] Copying ./xtrabackup_binlog_pos_innodb to /opt/slave-data/xtrabackup_binlog_pos_innodb
170807 14:56:04 [01]        ...done
170807 14:56:04 completed OK!

最终会看到 completed OK! 的输出，表明数据导入已经完成，此时，可以启动从库

docker start mysql56-slave

从库重新和主库同步

此时，我们可以对从库进行查询操作，发现数据已经同步过去，但是我们仔细观察，会发现在从主库将数据导出之后，我们为了模拟生产环境中，数据有写入状况，写入的三行数据，并没有出现在从库中。

mysql> select * from tb_users;
+-----------+----------+-----------+-------------+-----------------+
| accountId | userName | realName  | phoneNumber | email           |
+-----------+----------+-----------+-------------+-----------------+
|         0 | admin    | 管理员    | 13900000000 | admin@admin.com |
|         1 | user01   | 用户      | 13200000000 | user01@ok.com   |
|         2 | guest01  | 访客      | 13100000000 | guest@usa.com   |
+-----------+----------+-----------+-------------+-----------------+
3 rows in set (0.00 sec)

这并没有问题，因为按照我们的模拟，从库目前的状态的确只能是主库导出的状态。此时，我们只需要对从库进行同步即可，首先执行停止同步的命令：

STOP SLAVE;

需要知道的是，停止同步的命令是可以反复执行的，在重新同步之前执行该命令，只是为了保证从库状态正常。

随后，按照刚才我们所记录的，主库的日志位置 filename 'master-bin.000005', position '120'，进行同步：

CHANGE MASTER TO MASTER_HOST='172.17.0.1',MASTER_USER='root',MASTER_PASSWORD='admin1234',MASTER_PORT=18992,MASTER_LOG_FILE='master-bin.000005', MASTER_LOG_POS=120;

然后启动从库同步

START SLAVE;

此时我们再次查询tb_users表，会发现那三行数据已经同步过来

mysql> select * from tb_users;
+-----------+----------+-----------+-------------+-----------------+
| accountId | userName | realName  | phoneNumber | email           |
+-----------+----------+-----------+-------------+-----------------+
|         0 | admin    | 管理员    | 13900000000 | admin@admin.com |
|         1 | user01   | 用户      | 13200000000 | user01@ok.com   |
|         2 | guest01  | 访客      | 13100000000 | guest@usa.com   |
|         3 | user02   | 用户2     | 13900000002 | user02@ok.com   |
|         4 | user03   | 用户3     | 13900000003 | user03@ok.com   |
|         5 | user04   | 用户4     | 13900000003 | user04@ok.com   |
+-----------+----------+-----------+-------------+-----------------+
6 rows in set (0.00 sec)

我们在主库进行其他改变之后，从库也能够及时进行同步，说明主从同步重新一致。

至此，主从重新同步完成。

总结

xtrabackup确实是一个非常好用的工具，其解决了大数据量mysql数据库的备份、恢复问题，能够在极短的时间内完成较快的导入导出工作。同时，其还有增量备份等其他功能，本文没有进行讨论。这些功能和crontab结合起来，能够实现较好的数据库热备份功能。

参考文献（xtrabackup官方文档）

https://www.percona.com/software/mysql-database/percona-xtrabackup

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：python playsound插件下载 python插件库

下一篇：csv存入influxdb python influxdb导出csv

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯