【MySQL 8.0】通过Percona Xtrabackup实现数据库的物理备份与恢复
原创
©著作权归作者所有:来自51CTO博客作者dbprofessional的原创作品,请联系作者获取转载授权,否则将追究法律责任
[root@node01 ~]# wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@node01 ~]# rpm -ivh percona-release-latest.noarch.rpm
[root@node01 ~]# yum install -y percona-xtrabackup-80
用户与权限
(root@node01) > create user xtrabackup@'localhost' identified with mysql_native_password by 'xtrabackup';
Query OK, 0 rows affected (0.01 sec)
(root@node01) > grant backup_admin,reload,process,lock tables,replication client on *.* to xtrabackup@'localhost';
Query OK, 0 rows affected (0.04 sec)
(root@node01) > grant select on performance_schema.log_status to xtrabackup@'localhost';
Query OK, 0 rows affected (0.04 sec)
(root@node01) > grant select on performance_schema.keyring_component_status to xtrabackup@'localhost';
Query OK, 0 rows affected (0.05 sec)
(root@node01) > grant select on performance_schema.replication_group_members to xtrabackup@'localhost';
Query OK, 0 rows affected (0.01 sec)
(root@node01) > grant select on mysql.component to xtrabackup@'localhost';
Query OK, 0 rows affected (0.01 sec)
(root@node01) > flush privileges;
Query OK, 0 rows affected (0.01 sec)
(root@node01) > show grants for 'xtrabackup'@'localhost';
+----------------------------------------------------------------------------------------------+
| Grants for xtrabackup@localhost |
+----------------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO `xtrabackup`@`localhost` |
| GRANT BACKUP_ADMIN ON *.* TO `xtrabackup`@`localhost` |
| GRANT SELECT ON `mysql`.`component` TO `xtrabackup`@`localhost` |
| GRANT SELECT ON `performance_schema`.`keyring_component_status` TO `xtrabackup`@`localhost` |
| GRANT SELECT ON `performance_schema`.`log_status` TO `xtrabackup`@`localhost` |
| GRANT SELECT ON `performance_schema`.`replication_group_members` TO `xtrabackup`@`localhost` |
+----------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
[mysql@node01 ~]$ ssh-keygen
[mysql@node01 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@node02
[mysql@node01 ~]$ ssh node02 date
Sun Apr 9 15:12:54 CST 2023
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --target-dir=/home/mysql/backup/full
[mysql@node01 ~]$ scp -r backup/full/* node02:/home/mysql/backup/full
[mysql@node02 ~]$ xtrabackup --prepare --target-dir=/home/mysql/backup/full
[mysql@node02 ~]$ xtrabackup --copy-back --target-dir=/home/mysql/backup/full
[mysql@node02 ~]$ /etc/init.d/mysql.server start
[mysql@node02 ~]$ /etc/init.d/mysql.server status
[mysql@node02 ~]$ cat backup/xtrabackup_binlog_info
mysql-bin.000016 95923365
[mysql@node02 ~]$ cd binlog
[mysql@node02 binlog]$ mysqlbinlog --start-position=95923365 mysql-bin.000016 mysql-bin.000017 | mysql -uroot -pabcd.1234
[mysql@node01 ~]$ cat full_backup.sh
#!/bin/bash
user='root'
passwd='abcd.1234'
backup_dir='/home/mysql/backup'
/bin/xtrabackup --backup --user=$user --password=$passwd --target-dir=$backup_dir/`date '+%Y-%m-%d_%H-%M-%S'`
last_day=$(date -d "7 days ago" +%Y-%m-%d)
filename=$(find $backup_dir -name "$last_day*" -print|awk -F / '{print $NF}')
if [ ! -n "$filename" ]; then
echo "null"
else
rm -rf $backup_dir/$filename
fi
[mysql@node01 ~]$ chmod a+x full_backup.sh
[mysql@node01 ~]$ crontab -l
0 2 * * * /home/mysql/full_backup.sh > /home/mysql/backup/full_backup.log 2>&1
压缩与解压备份
[root@node01 ~]# tar xvf qpress-11-linux-x64.tar
[root@node01 ~]# mv qpress /usr/bin/
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --compress --target-dir=/home/mysql/backup/compress
[mysql@node01 ~]$ xtrabackup --decompress --remove-original --target-dir=/home/mysql/backup/compress
加密与解密备份
[mysql@node01 ~]$ openssl rand -base64 24
fwjrZQgcAwAg3TD+lcgrNJuPCIYfdfGq
[mysql@node01 ~]$ echo -n "fwjrZQgcAwAg3TD+lcgrNJuPCIYfdfGq" > /home/mysql/backup/keyfile
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --encrypt=AES256 --encrypt-key-file=/home/mysql/backup/keyfile --target-dir=/home/mysql/backup/encrypt
[mysql@node01 ~]$ xtrabackup --decrypt=AES256 --encrypt-key-file=/home/mysql/backup/keyfile --remove-original --target-dir=/home/mysql/backup/encrypt
流式备份
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --stream=xbstream > /home/mysql/backup/stream/backup.xbstream
[mysql@node01 ~]$ xbstream -x -C /home/mysql/backup/stream < /home/mysql/backup/stream/backup.xbstream
压缩
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --stream=xbstream | lz4 > /home/mysql/backup/stream/backup.lz4
[mysql@node01 ~]$ lz4 -d /home/mysql/backup/stream/backup.lz4 | xbstream -x -C /home/mysql/backup/stream
远程
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --stream=xbstream | lz4 | ssh mysql@node02 "cat - > /home/mysql/backup/stream/backup.lz4 "
[mysql@node02 ~]$ lz4 -d /home/mysql/backup/stream/backup.lz4 | xbstream -x -C /home/mysql/backup/stream
限速
[root@node01 ~]# yum install -y pv
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --stream=xbstream | lz4 | pv -q -L 20m | ssh mysql@node02 "cat - > /home/mysql/backup/stream/backup.lz4 "
校验
[mysql@node01 ~]$ xtrabackup --user=xtrabackup --password=xtrabackup --backup --stream=xbstream | lz4 | tee >(sha1sum > /tmp/source_checksum) | ssh mysql@node02 \
"cat - | tee >(sha1sum > /tmp/destination_checksum) > /home/mysql/backup/stream/backup.lz4"
[mysql@node01 ~]$ cat /tmp/source_checksum
f4ff3f5af3fb865bdcf9a1e7de9113781097892f -
[mysql@node02 ~]$ cat /tmp/destination_checksum
f4ff3f5af3fb865bdcf9a1e7de9113781097892f -