MySQL 日常维护 - XtraBackup全量备份

今天药忘吃喽~ 2023-06-10 09:29 22阅读 0赞

前言

本文收集关于 XtraBackup全量备份的介绍与恢复
喜欢Git阅读风格，点击这里，内容一模一样

什么是热备份

  • 热备份时在数据库运行的状态下备份数据，也是难度最大的备份
  • 场景的热备份有LVM和XtraBackup两种方法（PXC集群都适合）
  • 建议使用 XtraBackup 热备MySQL

流式压缩备份

XtraBackup 支持流式压缩，这也是他的特点之一

非流式备份：

​ 我们先备份数据文件，再进行压缩那么就需要执行两次磁盘IO操作，可能影响其他程序的IO请求

流式备份：

​ 流式压缩的备份技术，是把数据直接压缩后，写入到压缩文件里面，这样省掉了，备份再压缩的步骤

XtraBackup

XtraBackup是一种物理备份工具，通过协议连接到MySQL服务端，然后读取并复制底层的文件，完成物理备份

优势

  • XtraBackup 备份过程中加读锁，数据可读，但是不可写（分以下情况）

    • Innodb引擎的备份是无阻塞的备份，不会影响表的读写操作
    • MyISAML引擎是要加读锁的，只能读不能写
  • XtraBackup 备份过程不会打断正在执行的事务
  • XtraBackup 能够基于压缩等功能节约硬盘空间和流量
  • XtraBackup 还可以将数据加密，让他更加安全

对于引擎的支持





















引擎 全量备份 增量备份
InnoDB √ √
MyISAM √ 不支持

全量备份和增量备份


















方式 说明
全量备份 备份时间长，占用空间大
增量备份 备份时间短，占用空间小

XtraBackup 命令种类


























命令 说明
xbcrypt 用于加密或解密备份的数据
xbstream 用于压缩或者解压缩xbstream文件
xtrabackup 备份InnoDB数据表
innobackupex 上面三种命令的perl脚本封装

基本参数

配置与数据库相关


































参数 说明
defaults-file 指名mysql的配置文件,是用来查看数据保存位置的
host 数据库链接主机 (默认本机)
user 数据库用户名
password 数据库密码
port 数据库端口 (默认3306)
datadir 数据库文件目录 (注意写对)

上面 host 连接的是主机，可不能远程备份从两方面讲

  • 备份数据的体积都不小，如果网络不稳定的情况下就会导致备份失败
  • xtrabackup 只能在锁表的时候进行远程连接，其余的时候热备份都是拷贝文件来完成的

全量备份

指定配置，会自己找datadir

  1. innobackupex --defaults-file=/etc/my.cnf \
  2. --host=127.0.0.1 --user=backup --password=123456 \
  3. --parallel=2 /home/vagrant/db_backup/full --no-timestamp

自定义目录，生成到 db_backup/full

  1. innobackupex --user=backup  --password=123456 \
  2. --host=127.0.0.1 --datadir=/home/vagrant/datadir \
  3. --parallel=2 /home/vagrant/db_backup/full --no-timestamp

全量恢复


















参数 说明
apply-log 回滚没有提交的事务，同步已经提交的事务到数据文件
copy-back 通过配置文件，copy备份目录到 mysql数据目录 (自行处理文件分区等)

备份一份现有数据，避免异常 生产环境中注意

  1. mv /home/vagrant/datadir data_bak

回滚没有提交的事务，同步已经提交的事务到数据文件

  1. innobackupex --apply-log /home/vagrant/db_backup/full

全量还原

  1. cp -rf /home/vagrant/db_backup/full/* /home/vagrant/datadir/

可选参数






















参数 说明
no-timestamp 目录不使用时间戳
parallel 多线程并发备份
stream=xbstream 开启流式压缩

压缩

  1. innobackupex --user=backup  --password=123456 \
  2. --host=127.0.0.1 --datadir=/home/vagrant/datadir \
  3. --no-timestamp --stream=xbstream -> /home/vagrant/db_backup/b0.xbstream

解压缩

  1. xbstream -x < /home/vagrant/db_backup/b0.xbstream -C /home/vagrant/db_backup/temp

备份一份现有数据，避免异常 生产环境中注意

  1. mv /home/vagrant/datadir data_bak

回滚没有提交的事务，同步已经提交的事务到数据文件

  1. innobackupex --apply-log /home/vagrant/db_backup/temp

全量还原

  1. cp -rf /home/vagrant/db_backup/temp/* /home/vagrant/datadir/

加密参数






























参数 说明
encrypt 加密的算法：AES128、AES192、AES256
encrypt-threads 执行加密的线程数
encrypt-chunk-size 加密线程的缓存大小（默认64KB，最大1M）
encrypt_key 密钥，用于加密解密（必须24个字符）
encrypt_key_file 密钥文件（可以使用openssl 生成）

加密

  1. innobackupex --user=backup  --password=123456 \
  2. --host=127.0.0.1 --datadir=/home/vagrant/datadir \
  3. --encrypt=AES256 --encrypt-threads=10 \
  4. --encrypt_key=111111111111111111111111 \
  5. --encrypt-chunk-size 512 \
  6. --no-timestamp /home/vagrant/db_backup/encrypt_demo

解密

  1. innobackupex --decrypt=AES256 \
  2. --encrypt-key=111111111111111111111111 /home/vagrant/db_backup/encrypt_demo

备份一份现有数据，避免异常 生产环境中注意

  1. mv /home/vagrant/datadir data_bak

回滚没有提交的事务，同步已经提交的事务到数据文件

  1. innobackupex --apply-log /home/vagrant/db_backup/encrypt_demo

全量还原

  1. cp -rf /home/vagrant/db_backup/encrypt_demo/* /home/vagrant/datadir/

压缩参数

compress 跟 流式备份是没有冲突的，流式备份会压缩所有内容，而compress只会压缩innodb

include 热备份的时候就可以选择执行的表了


































参数 说明
compress 压缩InnoDB数据文件
decompress 解压缩
compress-threads 执行压缩的线程数
compress-chunk-size 加密线程的缓存大小（默认64KB，最大1M）
include 需要备份的数据表的正则表达式
示例：test.t_key_1,test.t_key_2
galera-info 备份PXC节点状态文件

压缩 + 流式备份 + 加密

  1. innobackupex --user=backup  --password=123456 \
  2. --host=127.0.0.1 --datadir=/home/vagrant/datadir \
  3. --encrypt=AES256 --encrypt-threads=10 \
  4. --encrypt_key=111111111111111111111111 \
  5. --encrypt-chunk-size 512 \
  6. --compress \
  7. --compress-threads=10 \
  8. --galera-info \
  9. --no-timestamp --stream=xbstream -> /home/vagrant/db_backup/bc.xbstream

解压缩

  1. xbstream -x < /home/vagrant/db_backup/bc.xbstream -C /home/vagrant/db_backup/temp

解密

  1. innobackupex --decompress --decrypt=AES256 \
  2. --encrypt-key=111111111111111111111111 /home/vagrant/db_backup/temp

回滚没有提交的事务，同步已经提交的事务到数据文件

  1. innobackupex --apply-log /home/vagrant/db_backup/temp

备份一份现有数据，避免异常 生产环境中注意

  1. mv /home/vagrant/datadir data_bak

全量还原

  1. cp -rf /home/vagrant/db_backup/temp/* /home/vagrant/datadir/

环境

提示，这里使用 docker安装的mysql 进行演示，当然你也可以不用，相关文章可以查看

[Linux MySQL5.7 yum方式安装](…/…/Linux/Linux MySQL5.7 yum方式安装.md)

[Docker 实战系列 - MySQL环境](…/…/Docker/Docker 实战系列 - MySQL环境.md)

mysql.cnf

  1. [mysqld]
  2. pid-file = /var/run/mysqld/mysqld.pid
  3. socket    = /var/run/mysqld/mysqld.sock
  4. datadir    = /var/lib/datadir
  5. basedir = /var/lib/mysql
  6. log_bin = mysql-bin
  7. server-id = 1111
  8. secure_file_priv="/"
  9. # 数据字符集
  10. character_set_server = utf8
  11. # 允许远程访问的IP地址
  12. bind-address = 0.0.0.0
  13. # 跳过DNS解析
  14. skip-name-resolve
  15. symbolic-links= 0

docker启动

  1. docker run -d -p 3306:3306 --name my-mysql \
  2. -v $PWD/my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
  3. -v $PWD/db_backup:/data/db_backup \
  4. -v $PWD/mysql:/var/lib/mysql \
  5. -v $PWD/datadir:/var/lib/datadir \
  6. -e MYSQL_ROOT_PASSWORD=123456 \
  7. mysql:5.7.27

进入docker 容器内 非docker忽略这步

  1. docker exec -it my-mysql bash

登录mysql

  1. mysql -uroot -p123456

首先创建一个用户

  1. create user 'backup'@'%' identified by '123456';

进行授权

  1. grant select,reload,lock tables,replication client,show view,event,process,file on *.* to 'backup'@'%';

查看

  1. show grants for 'backup'@'%';

创建数据库，创建测试表

  1. create database test;
  3. use test;
  5. CREATE TABLE `student` (
  6.   `student_id` int(10) NOT NULL AUTO_INCREMENT,
  7.   `student_name` varchar(64) NOT NULL COMMENT '学生名字',
  8.   PRIMARY KEY (`student_id`)
  9. ) ENGINE=InnoDB;

插入数据

  1. INSERT INTO `student`(`student_id`, `student_name`) VALUES (1, 'A');
  2. INSERT INTO `student`(`student_id`, `student_name`) VALUES (2, 'B');

安装 - 宿主机

更多版本请查看官方网站

下载安装

  1. yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

通过以下命令确认包可用

  1. yum list | grep percona

安装

  1. yum -y install percona-xtrabackup-24

查看安装是否成功

  1. cd /usr/bin
  2. ls -lh xtrabackup
  4. ls -lh innobackupex
  6. -rwxr-xr-x. 1 root root 21M Jul  5 07:59 xtrabackup
  7. lrwxrwxrwx. 1 root root 10 Oct 15 08:46 innobackupex -> xtrabackup

查看帮助

  1. innobackupex --help

安装 qpress

没安装会使用 decompress 出现异常

  1. wget https://repo.percona.com/testing/centos/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm
  3. yum localinstall -y qpress-11-1.el7.x86_64.rpm

全量备份

因为我这边使用vagrant创建的虚拟机，这里用户是 vagrant 所以我都放这边，各位看官

普通备份

生成当前时间的目录，比如2018-10-01_18-06-05

  1. innobackupex --user=backup  --password=123456 \
  2. --host=127.0.0.1 --datadir=/home/vagrant/datadir \
  3. --parallel=2 /home/vagrant/db_backup/

不使用时间戳

这里为了防止新手混淆，我直接生成full目录好了

  1. innobackupex --user=backup  --password=123456 \
  2. --host=127.0.0.1 --datadir=/home/vagrant/datadir \
  3. --parallel=2 /home/vagrant/db_backup/full --no-timestamp
  5. cd /home/vagrant/db_backup/full

查看

  1. du -lh .
  3. 25M      ./mysql
  4. 676K    ./sys
  5. 112K    ./test
  6. 1.1M    ./performance_schema
  7. 103M    .

查看与数据源差异了一点

  1. du -lh /home/vagrant/datadir/ib*
  3. 4.0K    /home/vagrant/datadir/ib_buffer_pool
  4. 76M     /home/vagrant/datadir/ibdata1
  5. 48M      /home/vagrant/datadir/ib_logfile0
  6. 48M      /home/vagrant/datadir/ib_logfile1
  7. 12M      /home/vagrant/datadir/ibtmp1

全量恢复

回滚没有提交的事务，同步已经提交的事务到数据文件

InnoDB时在运行中备份出来文件的事务里可能存在未提交的事件，也可能存在已提交未写入

  1. innobackupex --apply-log /home/vagrant/db_backup/full

发现 ib 的文件被重建了

  1. du -lh ib*
  3. 4.0K    ib_buffer_pool
  4. 76M      ibdata1
  5. 48M      ib_logfile0
  6. 48M      ib_logfile1
  7. 12M      ibtmp1

先停止mysql服务

  1. mysqld stop

备份一份现有数据，避免异常 生产环境中注意

  1. mv /home/vagrant/datadir data_bak

全量恢复 - 复制方式（任选其一）

copy备份数据过去

  1. mv /home/vagrant/db_backup/full/* /home/vagrant/datadir/*

修改所有者

  1. chown -R mysql:mysql data

重启

  1. mysqld start

全量备份 - 通过配置（任选其一）

执行备份

  1. innobackupex --default-file=/etc/my.cnf --copy-back /data/db_backup/2018-10-16

修改所有者

  1. chown -R mysql:mysql /var/lib/mysql/*

重启

  1. mysqld start

定时任务

创建目录

  1. mkdir shell

创建脚本

  1. touch shell/demo.sh
  3. time=$(date "+%Y-%m-%d %H:%M:%S")
  4. echo "执行全量热备份 ${time}"
  5. innobackupex --defaults-file=/etc/my.cnf \
  6. --host=192.168.99.151 --user=admin --password=123456 \
  7. --port=3306 --encrypt=AES256 --encrypt-threads=10 \
  8. --encrypt_key=24个字符，自己随便写 \
  9. --encrypt-chunk-size 512\
  10. --port=3306 --stream=xbstream -> /home/vagrant/demo.xbstream

给予权限

  1. chmod -R 755 demo.sh

创建目录，存放日志

  1. mkdir -p /home/vagrant/log

定时全量热备脚本 每周一执行一次shell

  1. crontab -e
  3. 0 0 * * 1 /home/shell/demo.sh > /home/vagrant/log/demo.log 2>&1

为了测试，我们可以先改为一分钟执行一次，来测试脚本是否正常

  1. */1 * * * * /home/shell/demo.sh > /home/vagrant/log/demo.log 2>&1

发表评论

表情：
评论列表 （有 0 条评论，22人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MySQL备份（一）全量

    概要 引言 全量备份 恢复全量备份 定时备份 引言 在产品上线之后，我们的数据是相当重要的，容不得半点闪失，应该做好万全的准备，搞不好哪一

    野性酷女野性酷女/ 0 赞/ 274 阅读