1.安装XtraBackup
1.1注意
MySQL5.7使用XtraBackup-2.4版本
其他版本请查看官网手册
1.2 centos8版本下载链接
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.17/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.17-1.el8.x86_64.rpm
1.3 centos7版本下载链接
https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.17/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.17-1.el7.x86_64.rpm
1.4 安装
yum install -y percona-xtrabackup-24-2.4.17-1.el8.x86_64.rpm
#或者
rpm -ivh percona-xtrabackup-24-2.4.17-1.el8.x86_64.rpm
2 备份
2.1 MySQL需要创建一个备份用的用户。
mysql -uroot -p
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser'; #回收此用户所有权限
GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; #授权刷新、锁定表、用户查看服务器状态
#如果需要远程建议all
GRANT ALL PRIVILEGES ON *.* TO 'bkpuser'@'localhost';
GRANT SELECT,PROCESS,RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
2.2 创建备份文件夹
建议找一个硬盘剩余比较多的
mkdir /Data/StorageDisk1/MySqlbkps/
2.3 备份
innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=snt2020 /Data/StorageDisk1/MySqlbkps/
如果修改过/etc/hosts文件,可能会报错,加上--host=127.0.0.1
就可以了
3.还原
3.1 停止mysql服务,并移除mysql目录下的文件
打包备份下以防万一
3.1.1 备份MySQL目录
cp rm -rf /var/lib/mysql/* /备份文件夹
停止服务,并删除文件
systemctl stop mysqld.service
rm -rf /var/lib/mysql/*
3.2 开始还原
mysql目录并不一定是/var/lib/mysql/,具体请查看/etc/my.cnf
innobackupex --defaults-file=/etc/my.cnf --user=root --password=password --use-memory=1G --apply-log /Data/StorageDisk1/MySqlbkps/2021-09-07_10-03-02
innobackupex --defaults-file=/etc/my.cnf --user=root --password=javasnt2021 --copy-back /Data/StorageDisk1/MySqlbkps/2021-09-07_10-03-02
–use-memory=1G是为了加快速度,apply-log之后目标文件下的文件已经准备就绪
3.2.1 重新设定mysql文件夹及子文件用户群组为mysql
chown -R mysql:mysql /var/lib/mysql/
3.2.2 启动服务
systemctl start mysqld.service
systemctl status mysqld.service
4. 参考链接
https://www.jianshu.com/p/eb723c7134d3?utm_campaign=maleskine&utm_content=note&utm_medium=seo_notes&utm_source=recommendation
4.1 故障排除
https://blog.csdn.net/yunhua12/article/details/81393572