MySQL数据迁移、热备份及冷还原

发布时间 2023-05-31 16:26:56作者: 往事已成昨天

MySQL篇(二)MySQL数据迁移、热备份及冷还原

图片

【摘要】MySQL数据迁移、热备份及冷还原。

一、前言

在日常开发中我们经常需要将MySQL数据从一个库迁移到另一个库,或者每日定时热备份以及冷还原等操作,一般对于小批量的数据来说,用Navicat的导出、导入或者数据同步,或者使用mysqldump命令即可完成。但是对于接近近千万的数据来说,以上的方式就显得力不从心了,今天来盘点一下比较好的数据迁移及热备份方式。

二、方案概述

2.1 Navicat的导出、导入

Navicat的导出、导入一般是我最常用的工具,对于小批量的数据来说比较友好,导出和导入的时间都可接受。数据量量小推荐此方式。

2.2 mysqldump

mysql 自带的命令mysqldump导出是极快的,我做的每日定时备份,导出800万的数据时间大概在5分钟左右。如下:

#!/bin/bash
#完成数据库的定时备份
#备份的路径
BACKUP=/home/db
#当前的时间作为文件名
DATETIME=$(date +%Y_%m_%d_%H%M%S)
#可以输出变量调试
#echo ${DATETIME}
echo "==========开始备份==========="
echo "备份的路径是 $BACKUP/$DATETIME.tar.gz"

#主机
HOST=localhost
#用户名
DB_USER=root
#密码
DB_PWD=Youpassword
#备份数据库名
DATABASE1=keycloak
#创建备份的路径
#如果备份的路径文件夹存在就使用,否则创建
[ ! -d "$BACKUP/$DATETIME"  ]  && mkdir -p "$BACKUP/$DATETIME" 
#执行mysql的备份数据库的指令
mysqldump --single-transaction --quick --socket=/tmp/mysql.sock -u${DB_USER} -p${DB_PWD} --host=$HOST --databases  $DATABASE1 | gzip  > $BACKUP/$DATETIME/$DATETIME.sql.gz
#打包备份文件
cd $BACKUP
tar -zcvf  $DATETIME.tar.gz  $DATETIME

#远程发送到另一台服务器
sshpass -p Youpassword scp $BACKUP/$DATETIME.tar.gz root@192.168.0.1:/data/backup/kcdb
#删除临时目录
rm -rf  $BACKUP/$DATETIME

#删除3天前的备份文件(-exec rm -rf {} \是固定写法,删除查询出来的数据)
find $BACKUP -mtime +2 -name  "*.tar.gz" -exec rm -rf {} \;
echo "==========备份完成==========="

对于大数据量的sql用source 命令导入还是比较占优势的。

mysql -uroot -pYoupassword
use database;
source /database/yourdb.sql;

2.3 Navicat自带的备份和还原备份

Navicat自带的备份生成的是nb3文件,对于备份800万的数据时间大概在40分钟左右,还原备份的时间大概2小时左右。无论是导入还是导出都不可取,时间太长了。

2.4 XtraBackup热备份和还原

Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品。热备份是在系统运行的状态下去备份数据,也是难度最大的备份。Mysql常见的热备份有LVM和XtraBackup两种方案,建议采用XtraBackup热备份数据。

XtraBackup的优势:XtraBackup备份过程不锁表,快速可靠 XtraBackup备份过程不会打断正在执行的事务 XtraBackup能够基于压缩等功能节约磁盘空间和流量

XtraBackup分为全量备份和增量备份:全量备份是备份全部数据,备份时间长,占用空间大。如下所示:图片

增量备份只备份变化的那部分数据,备份时间短,占用空间小。如下所示:图片

第一次备份采用全量备份,后续采用增量备份。

三、XtraBackup还原到InnoDB Cluster集群方案

一开始尝试过很多方案,利用Navicat的导入、还原备份时间都太长了,将近2小时才能导入。利用XtraBackup可以在一分钟内进行热备份,一分钟内还原。目标:将keycloak的单实例Mysql5.7.27数据迁移到MySQL5.7.27 InnoDB Cluster集群中。

3.1 先组建InnoDB Cluster集群再还原

经过多次实践,在只还原InnoDB Cluster集群主节点的情况下,可以还原成功。但是集群其余两个节点在恢复的过程中失败了,两个备用节点的状态都是失踪的状态,且重启也无效。推测可能是数据量太多,导致集群同步失败,后续将测试数据量小的时候此方式是否可行。

3.2 先对1主2备都进行还原,再组建InnoDB Cluster集群

经过实践,在对3个mysql实例分别进行数据还原后再组建InnoDB Cluster集群很丝滑,中间无故障发生,效率上也可以接受。

四、XtraBackup部署

Percona XtraBackup是基于MySQL的开源热备份实用程序 在备份期间不锁定数据库的服务器。它可以备份数据 来自 MySQL 5.1、5.5、5.6 和 5.7 服务器上的 InnoDB、XtraDB 和 MyISAM 表,以及带有 XtraDB 的 Percona Server。Percona XtraBackup 2.4不支持备份在MySQL 8.0,Percona Server for MySQL 8.0或Percona XtraDB Cluster 8.0中创建的数据库。将Percona XtraBackup 8.0用于8.0版数据库。

1. 二进制安装

1.1 查看libgcrypt 版本

[root@qy50128 test]# rpm -aq | grep libgcrypt
libgcrypt-1.5.3-14.el7.x86_64
[root@qy50128 test]#

1.2 下载对应安装包

下载安装包,官网:https://www.percona.com/download,如下所示:图片

要保证libgcrypt的版本一致,都是libgcrypt-1.5.3。从percona-xtrabackup-2.4.20开始已经没有libgcrypt153了。Percona XtraBackup2.4的版本应该是向下Mysql兼容的,添加--no-server-version-check参数不进行版本校验。xtrabackup version 2.4.14 based on MySQL server 5.7.19 Linux (x86_64) (revision id: ef675d4) xtrabackup version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5) xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056) xtrabackup version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263) xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)

1.3 安装

tar -xzvf percona-xtrabackup-2.4.21-Linux-x86_64.glibc2.12.tar.gz -C /usr/local/    #解压复制到/usr/local/
cd /usr/local/
mv percona-xtrabackup-2.4.21-Linux-x86_64.glibc2.12/ xtrabackup       # 更改文件名
ln -fs  /usr/local/xtrabackup/bin/* /usr/bin/              # 创建软链接

这里我们下载xtrabackup version 2.4.21.

1.4 查看版本

xtrabackup -v
xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data 
xtrabackup version 2.4.20 based on MySQL server 5.7.27 Linux (x86_64) (revision id: ef675d4)

2. YUM 包管理器安装 Percona XtraBackup 2.4

$ yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
$ yum install percona-xtrabackup-24

[root@xiaohezi ~]# xtrabackup -v
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
xtrabackup version 2.4.27 based on MySQL server 5.7.40 Linux (x86_64) (revision id: aae8e82)
[root@xiaohezi ~]#

3. docker 安装

1. docker create

docker使用percona-xtrabackup不要使用--restart参数,因为不需要保持启动状态,如果使用--restart参数启动容器,会报错xtraback-logfile已存在

docker create --name xtraBackup \
        -e TZ=Asia/Shanghai \
        --network host \
        --volumes-from bdyh-mysql-kc \
        -v /data/backup:/backup \
        percona/percona-xtrabackup:2.4.24 \
        xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/backup \
        --user=root --password=123456 --port=3306 --host=bdyh-mysql-kc --socket=/var/run/mysqld/mysqld.sock --no-server-version-check

执行备份:

docker start -ai xtraBackup

其中其中--volumes-from表示和docker mysql容器使用相同的容器卷信息,而--datadir是docker容器内mysql数据存放的地方,--socket是启动时套接字信息,因为我的mysql容器使用的是自定义的pkulaw_net网络,备份信息可以查看xtrabackup_checkpoints文件。

2. docker run

docker run -e TZ=Asia/Shanghai \
       --name xtraBackup \
       --volumes-from bdyh-mysql-kc \
       -v /data/backup:/backup \
       --network host\
       percona/percona-xtrabackup:2.4.24 \
       xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/backup \
       --user=root --password=123456 --port=3306 --host=bdyh-mysql-kc --no-server-version-check

3. 利用centos7制作一个xtrabackup镜像

1.编写dockerfile

下载rpm包,如下所示:图片

FROM centos:7.8.2003
ADD percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm /
RUN yum install -y /percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm && \
    yum clean all && \
    rm -rf /percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm

将dockerfile与rpm包放在同一个路径下。

2.打包镜像

docker build -t xtrabackup:v1 .

3. 启动xtrabackup

docker run -it -d -e TZ=Asia/Shanghai --name xtraBackup --volumes-from mysql-server-1 -v /mnt/backup:/backup -v /mnt/rebackup/my.cnf:/etc/my.cnf xtrabackup:v1

利用容器部署,易于迁移,更加灵活。若是还原同一个mysql,需要将数据目录(/var/lib/mysql)、配置文件(my.cnf)挂载到宿主机的目录上,并且Xtrabackup容器也做相同的目录挂载,这样就等于在宿主机上直接操作数据的备份与还原。若是还原到别的数据上,需要将备份好的数据挂载到某个宿主机路径下,且需要将目标mysql的my.cnf也挂载到xtrabackup容器中。

五、XtraBackup热备份

对于源mysql来说有两种情况,一种源mysql采用容器化方式部署的,另一种是二进制方式部署的。我们来分别进行备份。对于XtraBackup的部署方式,选用自己最合适、最擅长的部署方式即可。比如若是源mysql是二进制部署,那么XtraBackup可以采用非容器化的部署方式,源mysql是容器化方式部署,那么XtraBackup也可以采用容器化的部署方。无论XtraBackup采用哪种部署方式都可以完成对源mysql的备份和还原。

5.1 二进制部署的源mysql

5.1.1 XtraBackup采用二进制方式部署

tar -xzvf percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12.tar.gz -C /usr/local/    #解压复制到/usr/local/
cd /usr/local/
mv percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12/ xtrabackup       # 更改文件名
ln -fs  /usr/local/xtrabackup/bin/* /usr/bin/              # 创建软链接
#查看版本
xtrabackup -v
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
xtrabackup version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263)

5.1.2 全量备份

1.查看/etc/my.cnf文件

cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
# 对本地的mysql客户端的配置
default-character-set = utf8
# # 对其他远程连接的mysql客户端的配置
# [mysql]
default-character-set = utf8
[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES
max_allowed_packet=256M
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

2.调整XtraBackup备份语句

xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/mnt/backup --host=127.0.0.1 --user=root  --password=125678qq --skip-ssl --port=3306 --socket=/var/lib/mysql/mysql.sock  --no-server-version-check

按照上面my.cnf的内容调整,--datadir表示源mysql的数据目录,--target-dir表示要备份到哪个目录,--host表示localhost或者127.0.0.1即可,--socket表示源mysql的mysql.sock路径--skip-ssl。表示以非SSL方式连接数据库。

出现completed OK!即备份完成,进入/mnt/backup查看备份的文件,如下:图片

5.1.2 XtraBackup采用容器化部署

1.利用原生的percona/percona-xtrabackup:2.4.24镜像备份

docker run -e TZ=Asia/Shanghai \
       --name xtraBackup \
       -v /mnt/backup:/backup \
              -v /var/lib/mysql:/var/lib/mysql \
       --network host\
       percona/percona-xtrabackup:2.4.24 \
       xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/backup \
       --user=root --password=123456qq --port=3306 --host=192.168.50.128 --no-server-version-check

参数说明:-v /mnt/backup:/backup表示将备份的路径挂载到宿主机上。-v /var/lib/mysql:/var/lib/mysql表示将源mysql的数据路径挂载到容器中,便于xtraBackup在容器中操作这个目录。--network host表示使用宿主机的网络,这个用自定义网络也可以,只要在容器中能访问宿主机上的mysql即可。--datadir表示源mysql的数据目录。--target-dir表示要备份到哪个目。--host=192.168.50.128这里需要写宿主机的ip。

出现completed OK!即备份完成,进入/mnt/backup查看备份的文件,如下:图片

5.1.3 XtraBackup采用自制镜像备份

1.启动自制XtraBackup

docker run -it -d -e TZ=Asia/Shanghai \
--name xtraBackup \
-v /mnt/backup:/backup \
-v /var/lib/mysql:/var/lib/mysql \
--network pkulaw_net \
xtrabackup:v1

镜像采用上面制作的xtrabackup:v1镜像。

2.全量备份

#进入容器
docker exec -it xtraBackup bash
#执行备份
xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/backup --user=root --password=125678qq --port=3306 --host=192.168.50.128 --no-server-version-check

#或者一次性执行
docker exec -i xtraBackup xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/backup --user=root --password=125678qq --port=3306 --host=192.168.50.128 --no-server-version-check

5.2 容器化部署的源mysql

5.2.1 XtraBackup采用二进制方式部署的全量备份

xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/mnt/backup --host=127.0.0.1 --user=root  --password=125678qq --skip-ssl --port=3306 --socket=/var/lib/mysql/mysql.sock  --no-server-version-check

按照mysql容器中my.cnf的内容调整,--datadir表示源mysql的数据目录,--target-dir表示要备份到哪个目录,--host表示localhost或者127.0.0.1即可,--socket表示源mysql的mysql.sock路径--skip-ssl。表示以非SSL方式连接数据库。

5.2.2 XtraBackup采用自制镜像备份

1.启动自制XtraBackup

docker run -it -d -e TZ=Asia/Shanghai \
--name xtraBackup \
--volumes-from bdyh-mysql-kc \
-v /mnt/backup:/backup \
--network pkulaw_net \
xtrabackup:v1

镜像采用上面制作的xtrabackup:v1镜像。参数说明:--volumes-from表示和mysql容器使用相同的容器卷信息。-v /mnt/backup:/backup表示将备份的路径挂载到宿主机上。

或者如下:

docker run -it -d -e TZ=Asia/Shanghai \
--name xtraBackup \
-v /mnt/mysql-kc/data:/var/lib/mysql \
-v /mnt/backup:/backup \
--network pkulaw_net \
xtrabackup:v1

参数说明:-v /mnt/mysql-kc/data:/var/lib/mysql表示将源容器的mysql路径挂载进去

2.全量备份

#进入容器
docker exec -it xtraBackup bash
#执行备份
xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/backup --user=root --password=bdyh@2020 --port=3306 --host=bdyh-mysql-kc --no-server-version-check

#或者一次性执行
docker exec -i xtraBackup xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/backup --user=root --password=bdyh@2020 --port=3306 --host=bdyh-mysql-kc --no-server-version-check

六、xtraBackup还原到InnoDB Cluster集群实战

方案在上面已经阐述了,需要分别对1主2备都进行还原,再组建InnoDB Cluster集群。在执行完docker-compose -f docke-compose-mysqlCluster.yml up -d后三个实例的mysql已经启动了。

6.1 准备备份文件

我的备份文件在/mnt/backup路径下,如下所示:图片

6.2 准备my.cnf文件

正常来说生产环境中,需要将my.cnf映射到宿主上,以便与调整此配置文件。从其中一个容器化mysql实例中拷贝出一份。

docker cp mysql-server-1:/etc/my.cnf /mnt/rebackup

6.3 全量备份还原

6.3.1还原mysql-server-1 方式1

1.启动自制xtrabackup

#停止目标mysql
docker stop mysql-server-1
#删除目标mysql的数据目录
rm -rf *
#启动自制xtrabackup
docker run -it -d -e TZ=Asia/Shanghai \
--name xtraBackup \
--volumes-from mysql-server-1 \
-v /mnt/backup:/backup \
-v /mnt/rebackup/my.cnf:/etc/my.cnf \
--network pkulaw_net \
xtrabackup:v1

2.没有提交的事务进行回滚

#进入xtraBackup容器
docker exec -it xtraBackup bash
#未提交的事务进行回滚
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/backup --user=root --password=bdyh@2020 --port=3306 --host=mysql-server-1 --no-server-version-check

参数说明:--prepare表示对没有提交的事务进行回滚。

3.全量恢复

#还原
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/backup --user=root --password=bdyh@2020 --port=3306 --host=mysql-server-1 --no-server-version-check

按照上面步骤依次还原mysql-server-2和mysql-server-3

6.3.2还原mysql-server-1 方式2

1.启动自制xtrabackup

#停止目标mysql
docker stop mysql-server-1
#删除目标mysql的数据目录
rm -rf *
#启动自制xtrabackup
docker run -it -d -e TZ=Asia/Shanghai \
--name xtraBackup \
-v /mnt/mysqlcluste/data/mysql-1:/var/lib/mysql \
-v /mnt/backup:/backup \
-v /mnt/rebackup/my.cnf:/etc/my.cnf \
--network pkulaw_net \
xtrabackup:v1

/mnt/mysqlcluste/data/mysql-1表示目标mysql的数据目录

2.没有提交的事务进行回滚

#进入xtraBackup容器
docker exec -it xtraBackup bash
#未提交的事务进行回滚
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/backup --user=root --password=bdyh@2020 --port=3306 --host=mysql-server-1 --no-server-version-check

参数说明:--prepare表示对没有提交的事务进行回滚。

3.全量恢复

#还原
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/backup --user=root --password=bdyh@2020 --port=3301 --host=mysql-server-1 --no-server-version-check

按照上面步骤依次还原mysql-server-2和mysql-server-3

6.4 组建MySQL InnoDB Cluster集群搭建

按照MySQL篇(一)MySQL InnoDB Cluster集群搭建中方法组建InnoDB Cluster集群即可。



· END ·

如果这篇文章对您有帮助或者有所启发的话,请帮忙三连暴击点赞、转发和在看。您的支持是我坚持更新的最大动力。

架构至美
专注于互联网基础架构知识分享,内容不限,可能写Java技术相关的干货、架构设计、DevOps、k8s、Docker、CICD、自动化、脚本编程、国产化软件适配、运维等,可能写生活,也可能写八卦。
28篇原创内容
最后,欢迎关注架构至美微信公众号,查看历史文章,希望我的文章可以给您带来收获,哪怕只是一点点。

 

 

键客小盒子

知识分享,我是认真的。

收录于合集 #mysql
 4
上一篇MySQL篇(一)MySQL InnoDB Cluster集群搭建下一篇MySQL篇(三)MySQL数据实时增量迁移方案
阅读 706
架构至美
28篇原创内容