创建备份账号
这里给了all 权限
grant all on *.* to backup@'%' identified by 'backup';
在主库上进行全备
xtrabackup --defaults-file=/home/storage/mysql_3306/mysql_3306.cnf --backup --user=backup --password=backup --port=3306 --target-dir=/home/backups/all_xtrabp
备份成功提示
xtrabackup: Transaction log of lsn (5818829675886) to (5818863183403) was copied.
241023 18:03:35 completed OK!
传输备份到从库
scp -r /home/backups/all_xtrabp root@10.79.21.30:/home/backups/all_xtrabp/
准备备份
xtrabackup --prepare --target-dir=/home/backups/all_xtrabp/all_xtrabp
准备备份成功
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 5818863228968
241023 19:16:45 completed OK!
关闭从数据库
恢复数据之前需要关闭数据库 ，同时删除数据文目录
shutdown;
删除数据目录文件
rm -rf /home/storage/mysql_3306/data/*
同时删除其他目录下的文件，比如 logs run tmp
恢复备份
因为空间不够了，使用move-back 方式 ，这种方式速度也比较快
xtrabackup --defaults-file=/home/storage/mysql_3306/mysql_3306.cnf_bak --move-back --target-dir=/home/backups/all_xtrabp/all_xtrabp
241024 10:56:26 completed OK! 恢复成功
修改数据目录用户属组
修改数据目录用户属组 为mysql:mysql
chown -R mysql:mysql /home/storage/mysql_3306/
启动数据库
/usr/local/mysql/bin/mysqld_safe --defaults-file=/home/storage/mysql_3306/mysql_3306.cnf --user=mysql &
启动数据库报错
2024-10-24T03:02:08.865339Z mysqld_safe error: log-error set to '/home/storage/mysql_3306/logs/error.log', however file don't exists. Create writable for user 'mysql'.
解决
touch /home/storage/mysql_3306/logs/error.log
chown -R mysql:mysql /home/storage/mysql_3306/
再次启动
启动成功
查看文件中的GTID 点
binlog_pos = filename 'mysql-bin.002282', position '987703651', GTID of the last change '90086649-4098-11ed-93e2-b4055d109a8c:1-182115271'
设置从库的主从复制开始的点
reset master;
SET GLOBAL gtid_purged='90086649-4098-11ed-93e2-b4055d109a8c:1-182115271';
设置主从
CHANGE MASTER TO MASTER_HOST='10.79.21.29',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
启动主从成功
start slave
