.xb文件 mysql备份

软件准备：要安装两个软件
（1） Percona Xtrabackup
（2） qpress
mysql对应版本的xtrabackup，不同服务器安装不同的版本
mysql 5.1 – xtrabackup2.0以上
mysql5.6 --xtrabackup 2.2以上
mysql5.7 --xtrabackup2.4以上
mysql8 --xtrabackup8以上

安装 xtrabackup

我这里mysql版本是8.0.17
mysql8.0只能使用xtrabackup8.0
下载附件percona-xtrabackup-80-8.0.11-1.el7.x86_64.rpm

#下载
# wget https://repo.percona.com/yum/release/7/RPMS/x86_64/percona-xtrabackup-80-8.0.11-1.el7.x86_64.rpm

#安装
yum localinstall percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm

#校验
rpm -qa | grep -i xtrabackup
rpm -ql percona-xtrabackup-80

xtrabackup --version

qbpress

xtrabackup --compress 备份压缩使用
下载附件qpress-11-linux-x64.tar

#下载附件忽略此步骤
#wget http://www.quicklz.com/qpress-11-linux-x64.tar

#解压
tar -xvf qpress-11-linux-x64.tar

#复制
cp ./qpress /usr/bin

创建mysql备份用账号

创建具有完整备份所需的最低权限的数据库用户
用户密码可按需更改
数据库用户名: backup
数据库用户密码: backup@BACKUP2022
授权操作IP: localhost

CREATE USER 'backup'@'localhost' IDENTIFIED WITH mysql_native_password BY 'backup@BACKUP2022'; 

GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost'; 

GRANT SELECT ON performance_schema.log_status TO 'backup'@'localhost';

FLUSH PRIVILEGES;

定时备份脚本

#! /bin/bash

#删除历史
find /data/wxcepbak/xtrabackup/backup/ -mtime +2 -type f -name "*.xbstream" -exec rm -rf {} \;

#备份
xtrabackup --user=backup --password=backup@BACKUP2022 --backup --stream=xbstream --compress --compress-threads=4 --target-dir=/data/wxcepbak/xtrabackup/backup/ > /data/wxcepbak/xtrabackup/backup/backup_`date +\%Y\%m\%d`.xbstream

远程COPY传输到备份机器存档

注意: 不用用操作系统root账号, 我这里创建了一个 wxcepbak 账号指向copy job

#! /bin/bash
#远程copy
nohup scp -P 22 /data/wxcepbak/xtrabackup/backup/backup_`date +\%Y\%m\%d`.xbstream wxcepbak@10.128.7.130:/backup/xtrabackup/xtream/ &

mysql恢复实例

mysql官网下载mysql-8.0.17-el7-x86_64.tar.gz
安装位置预览

[sunmj@localhost mysql3316]$ pwd
/home/sunmj/mysql/mysql3316
[sunmj@localhost mysql3316]$ ll
总用量 729216
drwxr-xr-x.  7 sunmj root       148 6月  14 17:29 3316
-rwxr-xr-x.  1 sunmj root      1881 6月  13 17:19 my.cnf.bak
drwxr-xr-x. 11 sunmj root       194 6月  13 17:17 mysql
-rw-r--r--.  1 sunmj root 746682358 6月  13 17:15 mysql-8.0.17-el7-x86_64.tar.gz
-rw-r--r--.  1 sunmj root     18168 6月  14 09:18 mysql.out
-rw-------.  1 sunmj root      1575 6月  14 10:53 nohup.out
-rw-r--r--.  1 sunmj root       548 6月  13 17:47 readme.txt
[sunmj@localhost mysql3316]$ 

my.cnf

[client]
port = 3316
socket = /home/sunmj/mysql/mysql3316/3316/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = sunmj
port = 3316
socket = /home/sunmj/mysql/mysql3316/3316/mysql.sock
basedir = /home/sunmj/mysql/mysql3316/3316/
datadir = /home/sunmj/mysql/mysql3316/3316/data
pid-file = /home/sunmj/mysql/mysql3316/3316/mysql.pid
relay-log = /home/sunmj/mysql/mysql3316/3316/relay-bin
relay-log-info-file = /home/sunmj/mysql/mysql3316/3316/relay-log.info
server-id = 13
lower_case_table_names=0
[mysqld_safe]
log-error = /home/sunmj/mysql/mysql3316/3316/mysql.err
pid-file = /home/sunmj/mysql/mysql3316/3316/mysql.pid

cd /home/sunmj/mysql/mysql3316/

# 安装

#解压
tar -zxvf mysql-8.0.17-el7-x86_64.tar.gz
mv mysql-8.0.17-el7-x86_64 mysql

#初始化
./mysql/bin/mysqld  --defaults-file=/home/sunmj/mysql/mysql3316/3316/my.cnf --initialize-insecure  --user=sunmj --datadir=/home/sunmj/mysql/mysql3316/3316/data/ --lc-messages-dir=./mysql/share/english/

#启动
./mysql/bin/mysqld --defaults-file=/home/sunmj/mysql/mysql3316/3316/my.cnf --lc-messages-dir=./mysql/share/english/

#更改密码
./mysql/bin/mysqladmin -S /home/sunmj/mysql/mysql3316/3316/mysql.sock -u root password 123@456 

#连接
./mysql/bin/mysql -u root -p 123@456 -S /home/sunmj/mysql/mysql3316/3316/mysql.sock

#数据恢复
#恢复使用mysql恢复实例及端口3316数据库实例

#首先关闭数据库(冷恢复)
./mysql/bin/mysqladmin -uroot -p123@456 -P 3316 -S /home/sunmj/mysql/mysql3316/3316/mysql.sock shutdown

#清空data
cd /home/sunmj/mysql/mysql3316/3316
mv data data_`date +%Y%m%d`
mkdir data

xtrabackup --defaults-file=/home/sunmj/mysql/mysql3316/3316/my.cnf --user=root --prepare --target-dir=/home/sunmj/xtrabackup/xtrabackup-8.0.11/full_`date +%Y%m%d`

xtrabackup --defaults-file=/home/sunmj/mysql/mysql3316/3316/my.cnf --user=root --copy-back --target-dir=/home/sunmj/xtrabackup/xtrabackup-8.0.11/full_`date +%Y%m%d`

#恢复验证

cd /home/sunmj/mysql/mysql3316/

#检查 data目录归属用户 用户归属不对执行下方命令
#chown -R sunmj:root ./data

#mysql恢复实例重启启动
./mysql/bin/mysqld --defaults-file=/home/sunmj/mysql/mysql3316/3316/my.cnf --lc-messages-dir=./mysql/share/english/

#连接 使用备份数据账户密码登录
./mysql/bin/mysql -u root -p root@ROOT456 -S /home/sunmj/mysql/mysql3316/3316/mysql.sock

备份与恢复命令汇总

默认使用数据库默认配置

#全量备份
xtrabackup  --user=backup --password=backup@BACKUP2022 --backup --target-dir=/home/sunmj/xtrabackup/xtrabackup-8.0.11/full_`date +%Y%m%d`

#压缩备份 
xtrabackup  --user=backup --password=backup@BACKUP2022 --backup --compress --target-dir=/home/sunmj/xtrabackup/xtrabackup-8.0.11/full_`date +%Y%m%d`

#增加线程数
xtrabackup  --user=backup --password=backup@BACKUP2022 --backup --compress --compress-threads=4 --target-dir=/home/sunmj/xtrabackup/xtrabackup-8.0.11/full_`date +%Y%m%d`

#解压压缩备份
xtrabackup --decompress=1 --target-dir=/home/sunmj/xtrabackup/xtrabackup-8.0.11/full_`date +%Y%m%d`

#流式备份
xtrabackup --user=backup --password=backup@BACKUP2022 --backup --stream=xbstream --compress --compress-threads=4 --target-dir=/home/sunmj/xtrabackup/xtrabackup-8.0.11/stream_`date +%Y%m%d` > backup_`date +%Y%m%d`.xbstream

#解压压缩备份
xbstream -x <  backup_`date +%Y%m%d`.xbstream -C /home/sunmj/xtrabackup/xtrabackup-8.0.11/stream_`date +%Y%m%d`


#数据恢复
#恢复使用mysql恢复实例及端口3316数据库实例

#首先关闭数据库(冷恢复)
./mysql/bin/mysqladmin -uroot -p123@456 -P 3316 -S /home/sunmj/mysql/mysql3316/3316/mysql.sock shutdown

#清空data
cd /home/sunmj/mysql/mysql3316/3316
mv data data_`date +%Y%m%d`
mkdir data

xtrabackup --defaults-file=/home/sunmj/mysql/mysql3316/3316/my.cnf --user=root --prepare --target-dir=/home/sunmj/xtrabackup/xtrabackup-8.0.11/full_`date +%Y%m%d`

xtrabackup --defaults-file=/home/sunmj/mysql/mysql3316/3316/my.cnf --user=root --copy-back --target-dir=/home/sunmj/xtrabackup/xtrabackup-8.0.11/full_`date +%Y%m%d`

#恢复验证

cd /home/sunmj/mysql/mysql3316/

#检查 data目录归属用户 用户归属不对执行下方命令
#chown -R sunmj:root ./data

#mysql恢复实例重启启动
./mysql/bin/mysqld --defaults-file=/home/sunmj/mysql/mysql3316/3316/my.cnf --lc-messages-dir=./mysql/share/english/

#连接 使用备份数据账户密码登录
./mysql/bin/mysql -u root -p root@ROOT456 -S /home/sunmj/mysql/mysql3316/3316/mysql.sock

远程备份(仅供参考)

需配置ssh免密登录

#ssh免密
echo "192.168.66.101 initnode" >> /etc/hosts
ssh-keygen # 一路回车
ssh-copy-id initnode

xtrabackup --defaults-file=/etc/my.cnf --user=databak  -S /tmp/mysql.sock --parallel=2 --backup --stream=xbstream --target-dir=/home/sunmj/xtrabackup/xtrabackup-8.0.11/stream_`date +%Y%m%d` | ssh root@initnode \ "cat - > /data/backup/xtra_full_`date +%Y%m%d%H%M%S`.xbstream"