MySQL备份方案之Xtrabackup详细解答

JSON 2024-01-08 17:06:19 880

我们熟知的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

转载时必须以链接形式注明原始出处及本声明。

本文主题：

MySQL

如果本文对你有帮助，那么请你赞助我，让我更有激情的写下去，帮助更多的人。

综合技术交流：点击加入--> [SO JSON官方交流①群][收费]

综合技术交流：点击加入--> [SO JSON官方交流②群][免费]

要求：不能发广告、暴力、政治、付费教程，违者直接踢出。

备注：入群费用5元，没有QQ钱包，可以先支付宝 or 微信扫码支付5元赞助后，我拉您进去。QQ联系我。

扫码赞助：赞助二维码。