MySQL备份方案之Xtrabackup详细解答
我们熟知的MySQL备份方案有:mysqldump、Xtrabackup、主从复制、文件系统快照等方案,而我们要使用哪一种需要根据具体情况去选择。mysqldump是MySQL自己提供的逻辑备份工具,能导出数据库中的数据、表结构和其他信息,但是会对数据库性能产生一定的影响,尤其是在数据量大的情况下,因为它需要执行SQL查询并将结果写入备份文件,这可能会对数据库服务器负载增加,当然也有优点,备份的数据库文件可移植性好,可以在不同版本和架构的 MySQL 服务器上使用。而Xtrabackup是一种物理备份工具,物理备份就是直接复制数据库文件的方式来进行备份,这种备份方法相对于逻辑备份(如mysqldump)来说通常更快速、占用的存储空间较小。
在这里我们使用Xtrabackup备份,Xtrabackup主要有两个工具:xtrabackup、innobackupex
xtrabackup下载安装
# 下载到服务器
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/tarball/percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
# 解压
tar zxvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
# 把文件放到要存储的位置
mv percona-xtrabackup-2.4.7-Linux-x86_64 /usr/local/xtrabackup
# 创建软连接(可以不操作)
ln -s /usr/local/xtrabackup/bin/* /usr/bin/
全量备份方案
1.这里备份执行备份操作命令,按照步骤来。指定mysql配置文件,输入账号密码,指定sock文件 ,指定数据备份目录
⚠️⚠️⚠️注意:(./percona-xtrabackup-2.4.7-Linux-x86_64) 我是把名字改了,改成了(xtrabackup),所以要用(./xtrabackup)⚠️⚠️⚠️
# 正常情况下命令
./percona-xtrabackup-2.4.7-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='password' -S /var/lib/mysql/mysql.sock /data/mysql_bak/
# 改了文件名字之后
./xtrabackup/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='password' -S /var/lib/mysql/mysql.sock /data/mysql_bak/
2.tar zcvf 备份文件名.tar.gz 备份文件名
tar zcvf 2024-01-08_00-05-12.tar.gz 2024-01-08_00-05-12
全量数据恢复(停机恢复)
第一种
1.先解压数据备份文件
tar xf 2024-01-08_00-05-12.tar
2.数据恢复我们还是使用xtrabackup,数据备份文件解压完之后,我们需要对全量备份进行事务的处理
./xtrabackup/bin/xtrabackup --apply-log /data/mysql_bak/2024-01-08_00-05-12
3.事务处理完之后把mysql停掉
# 我这里系统是centos6.9,所以用如下命令
service mysqld stop
4.处理完事务的备份文件mv(mv:mv命令可以用来移动文件或者将文件改名)到mysql的数据文件中。然后需要对这个文件进行授权
# 将数据移动到数据库文件中 或者 修改一个名字
mv /data/mysql_bak/2024-01-08_00-05-12 dbfiles-2024.01.08
# 授权,对当前文件进行授权,这个文件我一般和mysql数据文件目录放在一起
chown -R mysql. dbfiles-2024.01.08
第二种
./xtrabackup/bin/innobackupex --defaults-file=/etc/my.cnf --copy-back /data/mysql_bak/2024-01-08_00-05-12
--copy-back:做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir 配置文件指向的数据目录
⚠️⚠️⚠️注意:--copy-back参数需要数据目录为空⚠️⚠️⚠️
全量数据恢复(在线恢复)
1.使用 --apply-log 选项,执行事务日志的恢复
./percona-xtrabackup-2.4.7-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log /data/mysql_bak/
2.把MySQL数据目录删除,然后直接执行以下命令
./xtrabackup/bin/innobackupex --defaults-file=/etc/my.cnf --copy-back /data/mysql_bak/2024-01-08_00-05-12
--copy-back:做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir 配置文件指向的数据目录
⚠️⚠️⚠️注意:--copy-back参数需要数据目录为空⚠️⚠️⚠️
3.更改 MySQL 数据目录的所有者
chown -R mysql:mysql /var/lib/mysql
4.重启 MySQL 服务
重启 MySQL 服务,使更改生效:
service mysql restart
增量备份方案
⚠️⚠️⚠️注意:xtrabackup和MySQL版本不匹配需要添加: --no-server-version-check ⚠️⚠️⚠️
1.这里使用xtrabackup进行全量备份 ,前面全量备份是使用innobackupex进行全量备份
# 这里要写用户名和密码,并且要自己指定文件夹名称(20240108)
./xtrabackup/bin/xtrabackup --backup -S /var/lib/mysql/mysql.sock -u root -p password --target-dir=/data/mysql_bak/full/20240108
2.在/data/mysql_bak/文件中创建一个增量备份目录
cd /data/mysql_bak
mkdir increment
3.进行增量备份
进行增量备份要指定是依照那一个全量备份进行增量备份,说白了就是增量备份是在全量备份的基础之上进行
./xtrabackup/bin/xtrabackup --backup -S /var/lib/mysql/mysql.sock -u root -p password --target-dir=/data/mysql_bak/increment/inc-20240108 --incremental-basedir=/data/mysql_bak/full/20240108
4.增量备份文件备份好了之后需要对全量备份进行事务处理,再将增量备份 和 全量备份文件进行合并
⚠️⚠️⚠️注意:这里只给全量文件做事务处理,这里用的是xtrabackup做事务处理⚠️⚠️⚠️
# 对全量备份做事务处理
./xtrabackup/bin/xtrabackup --prepare --applay-log-only --target-dir=/data/mysql_bak/full/20240108
# 进行数据合并 -> 将增量数据合并到全量数据中
./xtrabackup/bin/xtrabackup --prepare --target-dir=/data/mysql_bak/full/20240108 --incemental-dir=/data/mysql_bak/increment/inc-20240108
5.数据合并成功之后进行恢复数据
# 先停止MySQL
service mysqld stop
# 修改配置文件my.cnf,将MySQL数据目录指向新的数据备份文件
vim /etc/my.cnf
# 将数据移动到数据库文件中 或者 修改一个名字
mv /data/mysql_bak/full/20240108 dbfiles-20240108
# 授权,对当前文件进行授权,这个文件我一般和mysql数据文件目录放在一起
chown -R mysql. dbfiles-20240108
# 启动MySQL
service mysqld start
增量备份(停机恢复)
# 先停止MySQL
service mysqld stop
# 修改配置文件my.cnf,将MySQL数据目录指向新的数据备份文件
vim /etc/my.cnf
# 将数据移动到数据库文件中 或者 修改一个名字
mv /data/mysql_bak/full/20240108 dbfiles-20240108
# 授权,对当前文件进行授权,这个文件我一般和mysql数据文件目录放在一起
chown -R mysql. dbfiles-20240108
# 启动MySQL
service mysqld start
怎么恢复单独某个表的数据
这里讲两种方式,其实都是一样的套路
第一种:
a.从全备份中提取特定表的文件:
Percona XtraBackup创建的备份文件通常存储在目标目录中,其中包含完整的数据库备份。你可以在备份目录中找到所有数据库表的 .ibd 文件。对于 InnoDB 表,这些文件通常在
`../datadir/{database_name}/{table_name}.ibd`
cp /path/to/backup/datadir/your_database/your_table_name.ibd /path/to/restore
替换
/path/to/backup/datadir/your_database/your_table_name.ibd
为备份中你想要恢复的表的路径,/path/to/restore 为你想要还原表的目标目录
b.在 MySQL 中创建目标数据库和表
在 MySQL 中创建目标数据库和表,确保表的结构与你要恢复的表一致。
CREATE DATABASE your_database;
USE your_database;
CREATE TABLE your_table_name (...); -- 包括表结构
c.更正表文件的权限
如果表文件的权限不正确,需要更正它们的所有权:
sudo chown mysql:mysql /path/to/restore/your_table_name.ibd
d.重建表索引
如果你导出的是 InnoDB 表,可能需要重新建立表的索引。在 MySQL 命令行中执行:
ALTER TABLE your_table_name DISCARD TABLESPACE;
ALTER TABLE your_table_name IMPORT TABLESPACE;
替换 your_table_name 为你的表名。
第二种:
a.直接删除数据存储文件中的对应的idb文件
rm -rf table.idb
b.然后把备份文件中对应的文件copy过去,和第一种一样
cp /path/to/backup/datadir/your_database/your_table_name.ibd /path/to/restore
c.更正表文件的权限
如果表文件的权限不正确,需要更正它们的所有权:
sudo chown mysql:mysql /path/to/restore/your_table_name.ibd
d.重建表索引
如果你导出的是 InnoDB 表,可能需要重新建立表的索引。在 MySQL 命令行中执行:
ALTER TABLE your_table_name DISCARD TABLESPACE;
ALTER TABLE your_table_name IMPORT TABLESPACE;
替换 your_table_name 为你的表名。
⚠️⚠️⚠️注意:我们这里都是以InnoDB存储引擎来测试⚠️⚠️⚠️
全量备份脚本
第一种
1.shell脚本写好之后,再写一个定时任务就OK了,这里是直接备份到`/data/mysql_bak/full/`目录下,然后把数据打包,并且把备份的数据删掉
#!/bin/bash
#0 3 * * * /bin/sh /data/mysql_bak-full.sh /dev/null 2>&1
# crazyitboy 20240108
export PATH=/bin:/usr/bin:/usr/local/bin:/sbin:/usr/sbin:/usr/local/sbin
MySQL_USER=root
MYSQL_PWD=password
BAKDIR=/data/mysql_bak/full
MONTH=$(date +%m)
DAY=$(date +%d)
YEAR=$(date +%Y)
HOUR=$(date +%H)
MIN=$(date +%M)
DRINAME=$YEAR$MONTH$DAY$HOUR$MIN
BAKDIR_MYSQL=$BAKDIR/
BAK_TAR=${DRINAME}.tar.gz
TEMP_DIR=$BAKDIR/temp_dir/
if [ ! -d "$TEMP_DIR" ]; then
mkdir -p $TEMP_DIR
fi
/usr/local/xtrabackup/bin/innobackupex --defaults-file=/etc/my.cnf --user=${MySQL_USER} --password=${MYSQL_PWD} -S /var/lib/mysql/mysql.sock ${TEMP_DIR}
if [ $? -eq 0 ]; then
echo "innobackupex 备份完成!(OK!)"
else
echo "innobackupex 备份失败!(FAIL!)"
exit
fi
cd ${TEMP_DIR}
PWDS=$(pwd)
if [ "${TEMP_DIR}" == "${PWDS}/" ]; then
BAK_TAR_NAME=${BAK_TAR}
tar -czf /data/mysql_bak/full/${BAK_TAR_NAME} *
[ $? != 0 ] && echo "tar -czf ${BAK_TAR_NAME} ${TEMP_DIR}* -R" && exit
echo "$TEMP_DIR 打包成功!"
cd $BAKDIR
RM_PWDS=$(pwd)
if [ "${BAKDIR}/" == "${RM_PWDS}/" ]; then
rm -rf temp_dir
fi
else
echo "$TEMP_DIR 打包失败!"
fi
2.然后执行定时任务,每天3点执行
0 3 * * * /bin/sh /data/mysql_bak/mysql_bak-full.sh /dev/null 2>&1
第二种
1.使用vim /etc/mysql_bak-full.sh命令创建并编辑脚本
#!/bin/bash -
rm -rf /data/mysql_bak/full
xtrabackup --login-path=bkpuser_local --backup --target-dir=/data/mysql_bak/full
2.使用chomd命令赋予/etc/mysql_bak-full.sh脚本执行权限
chomd 755 /etc/mysql_bak-full.sh
3.我们计划每周日23点对MySQL进行一次全量备份
# 使用 crontab -e 添加周期备份任务
crontab -e
0 23 * * 0 /bin/bash /etc/mysql_bak-full.sh
# 使用 crontab -l 查看周期任务
crontab -l
增量备份脚本
⚠️⚠️⚠️注意:这里编写用于第N次(这里以第2次为例)增量备份脚本,因为增量备份我这里是每天都需要备份⚠️⚠️⚠️
1.使用vim /etc/mysql_bak-inc2.sh命令创建并编辑脚本
#!/bin/bash -
rm -rf /data/mysql_bak/increment/inc2
xtrabackup --login-path=bkpuser_local --backup --target-dir=/data/mysql_bak/increment/inc2 --incremental-basedir=/data/mysql_bak/increment/inc1
2.使用chomd命令赋予/etc/mysql_bak-inc2.sh脚本执行权限
chomd 755 /etc/mysql_bak-inc2.sh
3.我们计划每周一到周六23点对MySQL进行一次增量备份
# 使用 crontab -e 添加周期备份任务
crontab -e
# 周一
0 23 * * 1 /bin/bash /etc/mysql_bak-inc1.sh
# 周一,以此类推
0 23 * * 2 /bin/bash /etc/mysql_bak-inc2.sh
# 使用 crontab -l 查看周期任务
crontab -l
这里周二到周六脚本命名以此类推,1改成2,2改成3
# 查看定时任务状态/停止定时任务/启动定时任务/重启定时任务
service crond status/stop/start/restart
版权所属:SO JSON在线解析
原文地址:https://www.sojson.com/blog/484.html
转载时必须以链接形式注明原始出处及本声明。
如果本文对你有帮助,那么请你赞助我,让我更有激情的写下去,帮助更多的人。