文章目录
1.全量备份
#全量备份
xtrabackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password='rootroot' --port=3306 --backup --target-dir=/data/backup/full
#查看备份位置点位
cat /data/backup/full/xtrabackup_binlog_info
binlog.000002 197 b679baa5-eb07-11ee-875d-525400329a89:1
cat /data/backup/full/xtrabackup_info
uuid = 3681c2d2-eb08-11ee-875d-525400329a89
name =
tool_name = xtrabackup
tool_command = --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=... --port=3306 --backup --target-dir=/data/backup/full
tool_version = 8.0.28-21
ibbackup_version = 8.0.28-21
server_version = 8.0.28
start_time = 2024-03-26 08:31:37
end_time = 2024-03-26 08:31:40
lock_time = 1
binlog_pos = filename 'binlog.000002', position '197', GTID of the last change 'b679baa5-eb07-11ee-875d-525400329a89:1'
innodb_from_lsn = 0
innodb_to_lsn = 88049580
partial = N
incremental = N
format = file
compressed = N
encrypted = N
2.增量备份
2.1 模拟全量备份后数据变化
#模拟数据变化
mysql> create database bbb;
mysql> use bbb;
mysql> create table t1 (id int);
2.2 增量备份
#增量备份
xtrabackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password='rootroot' --port=3306 --backup --target-dir=/data/backup/inc --incremental-basedir=/data/backup/full
#查看备份位置点位:
cat /data/backup/inc/xtrabackup_binlog_info
binlog.000003 197 b679baa5-eb07-11ee-875d-525400329a89:1-3
cat /data/backup/inc/xtrabackup_info
uuid = 6dc78717-eb08-11ee-875d-525400329a89
name =
tool_name = xtrabackup
tool_command = --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=... --port=3306 --backup --target-dir=/data/backup/inc --incremental-basedir=/data/backup/full
tool_version = 8.0.28-21
ibbackup_version = 8.0.28-21
server_version = 8.0.28
start_time = 2024-03-26 08:33:09
end_time = 2024-03-26 08:33:12
lock_time = 1
binlog_pos = filename 'binlog.000003', position '197', GTID of the last change 'b679baa5-eb07-11ee-875d-525400329a89:1-3'
innodb_from_lsn = 88049580
innodb_to_lsn = 88063778
partial = N
incremental = Y
format = file
compressed = N
encrypted = N
3.binlog增量备份
3.1 模拟增量备份后数据变化
#模拟数据变化
mysql> use bbb;
mysql> insert into t1 values (1);
mysql> insert into t1 values (2);
#查看位置
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 | 739 | | | b679baa5-eb07-11ee-875d-525400329a89:1-5 |
+---------------+----------+--------------+------------------+------------------------------------------+
#切换日志
mysql> flush logs;
mysql> insert into t1 values (3);
#查看位置
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000004 | 468 | | | b679baa5-eb07-11ee-875d-525400329a89:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+
3.2 备份binlog文件
#可以根据前面xtrabackup增量备份的节点,备份增量的binlog文件
cp /data/3306/data/binlog.* /data/backup/binlog/
4.模拟数据库奔溃
杀掉mysql进程,删除数据目录文件和binlog日志
kill -9 ....
rm -rf /data/3306/data/*
5.恢复
5.1 准备阶段
#准备阶段:
1 准备全备份的日志:
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full
2 准备增量备份的日志:
xtrabackup --prepare --target-dir=/data/backup/full --incremental-dir=/data/backup/inc
#注意:
--apply-log-only 此参数的目的是未提交的事务不回滚,在存在多个增量备份时,准备阶段的最后一个增量备份时,不要加此参数
5.2 拷贝数据回目录
#拷贝数据回数据目录
xtrabackup --host=127.0.0.1 --user=root --password='rootroot' --port=3306 --datadir=/data/3306/data --copy-back --target-dir=/data/backup/full
5.3 修改权限
2.5 修改数据目录的权限和属性:
chown -R mysql:mysql /data/3306/data
chmod -R 755 /data/3306/data
6.启动数据库
cd /usr/local/mysql/support-files
./mysql.server start
7.恢复增量binlog
#根据最后一个增量备份的节点位置,恢复后面的binlog日志
mysqlbinlog /data/backup/binlog/binlog.000003 --start-position=197 | mysql -uroot -p'rootroot'
mysqlbinlog /data/backup/binlog/binlog.000004 | mysql -uroot -p'rootroot'
8.验证
mysql -uroot -p -e "select * from bbb.t1;"
Enter password:
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
#数据完整,恢复完成