MySQL通过备份恢复的方式搭建主从/重建从库

系列文章目录

MySQL5.7基于innobackupex工具进行全量备份/恢复
MySQL5.7.42安装

前言

在实际生产环境中,常用的数据库架构有单节点、主从、高可用三种架构,但是随着业务量的增长，数据库可能会面临着一些风险,例如:单节点数据库TPS、QPS性能跟不上业务量,随时出现数据库高负载进而导致业务数据加载失败，还有可能面临着事务量的增加导致主从架构的数据库面临着从库落后主库太多binlog，导致主从数据不一致问题，还可能由于主从同步延时过高导致从库同步数据失败等问题。因此面临着诸多问题，那么作为运维或DBA，就得需要想办法解决单点故障、恢复主从关系等问题。因此，本篇文章从这个角度出发，通过备份恢复的方式搭建主从/恢复从库两种情况进行分享。

以下分享基于MySQL5.7版本,同时采用innobackupex工具进行全量备份和恢复操作。且MySQL是基于GTID同步模式搭建的主从或单点

一、MySQL安装

数据库安装再次不再做过多描述,如果对数据库安装有疑问,请参考顶部链接MySQL5.7安装文章即可

二、单节点MySQL通过全量备份恢复的方式搭建主从

由单节点架构转换为主从复制架构

1.从库安装

数据库安装再次不再做过多描述,如果对数据库安装有疑问,请参考顶部链接MySQL5.7安装文章即可。
安装目录、数据目录如下所示

2.主库全量备份

采用innobackupex工具进行全量备份操作,以文件的形式备份至主库机器的备份目录下。执行备份命令，其中使用备份的用户是dbbackup。如果备份较大，可以考虑加上并行参数--parallel=n,n代表并发的线程

#命令执行完成后,会在/export/backup/目录下产生一个当前时间命令的目录,这个目录就是整个备份文件目录

[root@mysql1 ~]# /usr/bin/innobackupex --defaults-file=/data/my3306/my.cnf 
--user=dbbackup --password=dbbackup --backup --lock-ddl-per-table --
socket=/data/my3306/run/mysqld.sock --slave-info
/export/backup

#也可以参考顶部文章MySQL5.7基于innobackupex工具进行全量备份/恢复,对备份命令及参数有一定的了解

当执行完成后出现completed OK!字样则说明全量备份成功

备份完成后,检查备份目录,如下所示

[root@mysql1 ~]# ls -lrth  /export/backup/2024-09-14_15-53-33

3.将备份传输至目标服务器(从库)

1、从库创建备份目录
[root@mysql2 ~]# mkdir /export/backup

2、将主库备份拷贝至目标服务器(如果文件过大，可以考虑压缩)
[root@mysql1 ~]#scp -r /export/backup/2024-09-14_15-53-33 192.168.56.131:/export/backup

4.通过备份恢复的方式搭建从库步骤

4.1.检查从库是否有xtrbackup工具

如果不知道怎么安装请参考顶部文章MySQL5.7基于innobackupex工具进行全量备份/恢复

[root@mysql2 ~]#  rpm -qa |grep percon

4.2.停止从库

因为采用顶部文章MySQL5.7安装教程安装完成之后,会直接启动数据库,但是要恢复数据,必须停止从库，主库不能停，此时主库还要接着提供服务的

[root@mysql2 ~]# /etc/init.d/mysql3306.server stop
Shutting down MySQLEnter password:
SUCCESS!

4.3.清空从库相关数据目录

虽然从库是新部署的,为了保险起见,执行以下删除操作,确保恢复工作一次性完成
相关数据目录在数据库安装那里有截图所示

[root@mysql2 ~]# cd /data/my3306/data
[root@mysql2 data]#  rm -rf ./binlog/*
[root@mysql2 data]#  rm -rf ./data/*
[root@mysql2 data]#  rm -rf ./iblog/*
[root@mysql2 data]#  rm -rf ./ibdata/*

4.4.从库根据备份文件生成回滚日志

执行innobackupex生成回滚日志命令,完成后如下图所示,会有completed ok!字样

[root@mysql2 ~]# cd /export/backup/2024-09-14_15-53-33
[root@mysql2 2024-09-14_15-53-33]# /usr/bin/innobackupex --defaults-file=/data/my3306/my.cnf \
 --user=root --apply-log /export/backup/2024-09-14_15-53-33

4.5.将备份文件拷贝到实例的数据文件目录

这一步才是真正的将备份文件数据写入到实例的数据目录中去,前提是回滚日志生成成功才能执行这一步骤

[root@mysql2 ~]# /usr/bin/innobackupex --defaults-file=/data/my3306/my.cnf \
 --copy-back /export/backup/2024-09-14_15-53-33

4.6.修改从库数据目录权限

此处的属主属组取决于你安装mysql时创建的用户及组,如果你是用root用户安装则此处替换为root即可

[root@mysql2 ~]# chown mysql.myinstall /data/my3306/data/* -R

4.7.启动从库

[root@mysql2 ~]# /etc/init.d/mysql3306.server start
Starting MySQL........ SUCCESS!

至此,从库恢复数据完成,接下来是搭建基于GTID同步模式的主从关系

5.搭建新的主从

由之前的单节点架构提升为主从复制架构

5.1.主库创建复制用户

登陆主库,创建主从复制依赖的账户repl,并授权

mysql > grant replication slave on *.* to 'repl'@'%' identified by 'repl';
mysql >  flush privileges;

5.2.从库服务器查询xtrbackup_info文件中备份的GTID值

从数据备份目录中可以看到有一个xtrbackup_info文件,这个文件中包含了基于GTID搭建主从关系所需要用到的值

[root@mysql2 ~]# vim /export/backup/2024-09-14_15-53-33/xtrabackup_info

GTID值如下截图所示

5.3.执行搭建主从命令

在从库执行以下命令

mysql > reset master;
Query OK, 0 rows affected (0.35 sec)

#这个值就是上方查出来的值,如果这个值是多个,则使用单引号包起来‘’,中间使用逗号,分隔即可
mysql > SET GLOBAL gtid_purged='9e29462e-61e5-11ef-9509-0050563f4bf3:1-101281';
Query OK, 0 rows affected (0.15 sec)

替换下方主库ip、端口、主从复制账户、密码即可

mysql > change master to master_host='192.168.56.130', master_port=3306,master_user='repl' ,\
        master_password='repl' ,master_auto_position=1;
Query OK, 0 rows affected (0.05 sec)

#开启主从复制

mysql > start slave;
Query OK, 0 rows affected (0.28 sec)

5.4.查看主从状态

mysql > show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.56.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002037
          Read_Master_Log_Pos: 473170850
               Relay_Log_File: mysqld-relay-bin.005393
                Relay_Log_Pos: 345276015
        Relay_Master_Log_File: mysql-bin.001811
             Slave_IO_Running: YES
            Slave_SQL_Running: YES
            ..................

当Slave_IO_Running、Slave_SQL_Running参数对应的值为YES时,主从复制搭建完成

6.查看从库参数

如果在第一步安装完从库后,没有调整my.cnf配置文件中的参数,则在此时在从库中进行手动临时修改,修改完成后，再次修改my.cnf中对应的参数,然后等待下次数据库重启生效

mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |  #从库必须开启只读.需要修改
| super_read_only       | OFF   |  #从库必须开启只读.需要修改
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)

mysql> set global read_only=ON;
1 rows in set (0.01 sec)

mysql> set global super_read_only=ON;
1 rows in set (0.01 sec)

mysql> show variables like '%innodb_buffer_pool_size%'; #缓冲池大小.该值默认为服务器内存的60%~70%
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 | #如果该值没有占用到服务器内存的60%~70%。则进行修改。单位是字节进行换算
+-------------------------+------------+
1 row in set (0.01 sec)

#例如: 8G内存服务器,则8*0.7=5.6 .则此处的值为6G,换算为字节byte是6442450944
mysql> set global innodb_buffer_pool_size=6442450944; 
1 rows in set (0.01 sec)

至此,通过备份恢复的方式搭建主从架构完成

三、MySQL通过全量备份恢复的方式恢复主从

1.背景

有一个已经交付的项目,通过商务反馈需要运维支持对数据库问题处理
客户是纯内网环境,无法发送定时巡检报告，客户需要手动巡检,可能最近几个月没做巡检,导致数据库产生问题。
具体问题:
	客户反馈主从架构的从库binlog文件一直在增加没有删除,且已经达到了400G，占用了90%的数据盘。
	再查看从库的全量备份,发现从7月初开始就没有产生备份文件,想当于只有主库的备份是正常的
客户诉求:
	清理从库binlog并排查备份未产生原因

2.排查过程

	在客户提供特定远程连接方式后,登录从库服务器,进行排查操作。

2.1.检查数据库processlist

发现有锁表操作

mysql> select * from information_schema.processlist where info is not null;
+----+------+-----------+------+---------+------+-----------+---------------------------------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE     | INFO                                                                |
+----+------+-----------+------+---------+------+-----------+---------------------------------------------------------------------+
|  472803 | root | localhost | NULL | Query   |    0 | executing | select * from information_schema.processlist where info is not null |
+----+------+-----------+------+---------+------+-----------+---------------------------------------------------------------------+
|  208486 | dbbackup | localhost | NULL | Query   |   7927514 | Waitting for commit lock | FLUSH TABLES WITH READ LOCK |
+----+------+-----------+------+---------+------+-----------+---------------------------------------------------------------------+
2 row in set (0.00 sec)

通过processlist发现有一条正在等待提交锁（Waitting for commit lock）
执行的操作是 FLUSH TABLES WITH READ LOCK，这个操作通常用于备份，锁定所有表以确保数据一致性。
但是从执行时间TIME分析看执行了很长时间还没结束。因此在查看从库是否有大事务一直在运行导致的该现象

2.2.检查从库事务

执行大事务查看语句，发现了两条从6月中旬开始执行的事务,一直处于运行状态，且到现在没有运行完成。如下图所示

mysql > selcet * from information_schema.innodb_trx\G;

排查到此就已经确认是因为这两条大事务导致的备份失败以及binlog日志很大无法删除，因为没有同步数据

3.修复过程

3.1.kill两个大事务

因为从时间上看这两个事务已经夯死了,可直接kill并不会产生影响

mysql > kill 2769330967;
mysql > kill 2769330966;

3.2.观察从库状态

当kill掉两个大事务后,从库的io、sql线程也正常
但是从Master_Log_File和Relay_Master_Log_File两个参数值来看，相差非常大,将近三个月的差距
虽然让从库自动追数据也是一种方法，但是效率低,速度慢。防止再次出现问题，跟客户沟通后,采取重建从库的做法

3.3.重建从库

本次重建从库是基于已从在的主从关系重新恢复从库，因此可以采纳本篇文章的第二部分开始全量备份、全量恢复、主从关系重新搭建部分即可完成从库的重建工作。所以在此处不再过多描述重建从库的过程，详情看第二大部分即可

至此,重建从库后,客户的诉求已全部解决

总结

以上就是分享的通过备份恢复的方式搭建主从或重建从库的文章。希望对大家可以起到帮助作用。完成了这篇文章的编写，大体上对数据库主从关系的重建工作有了一定的了解。