使用xtrabackup恢复数据库
使用xtrabackup恢复数据库
1、创建备份目录并授权
mkdir /data/backup
chown -R mysql:mysql /data/backup/
2、全备数据库
1、数据库全备
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/db_`date +%F` --user=root --password=root --socket=/data/data/mysql.sock>/data/backup/db_full_`date +%F`.log 2>&1
部分备份日志如下：
备份过程如下：
```language
[root@node1 backup]# cat db_full_2024-05-18.log
---
2024-05-18T15:06:24.060430+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /data/data/mysql.sock
2024-05-18T15:06:24.081369+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.33
2024-05-18T15:06:24.086956+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
...
2024-05-18T15:06:24.533828+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19730331)
2024-05-18T15:06:24.592403+08:00 0 [Note] [MY-012953] [InnoDB] Disabling background ibuf IO read threads.
2024-05-18T15:06:24.798005+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2024-05-18T15:06:24.798132+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2024-05-18T15:06:24.803212+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.
2024-05-18T15:06:24.809533+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 2 for testdb/testtab, old maximum was 0
2024-05-18T15:06:24.810468+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2024-05-18T15:06:24.815650+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2024-05-18T15:06:24.820294+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2024-05-18T15:06:24.821113+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./ibdata1 to /data/backup/db_2024-05-18/ibdata1
2024-05-18T15:06:24.990789+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibdata1 to /data/backup/db_2024-05-18/ibdata1
2024-05-18T15:06:24.991996+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./sys/sys_config.ibd to /data/backup/db_2024-05-18/sys/sys_config.ibd
2024-05-18T15:06:24.993218+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./sys/sys_config.ibd to /data/backup/db_2024-05-18/sys/sys_config.ibd
2024-05-18T15:06:24.994635+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./testdb/testtab.ibd to /data/backup/db_2024-05-18/testdb/testtab.ibd
2024-05-18T15:06:24.995276+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./testdb/testtab.ibd to /data/backup/db_2024-05-18/testdb/testtab.ibd
2024-05-18T15:06:24.996214+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./mysql.ibd to /data/backup/db_2024-05-18/mysql.ibd
2024-05-18T15:06:25.147866+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql.ibd to /data/backup/db_2024-05-18/mysql.ibd
2024-05-18T15:06:25.148829+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./undo_002 to /data/backup/db_2024-05-18/undo_002
2024-05-18T15:06:25.201926+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./undo_002 to /data/backup/db_2024-05-18/undo_002
2024-05-18T15:06:25.202414+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./undo_001 to /data/backup/db_2024-05-18/undo_001
2024-05-18T15:06:25.255174+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./undo_001 to /data/backup/db_2024-05-18/undo_001
2024-05-18T15:06:25.534302+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19730331)
2024-05-18T15:06:25.820806+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting to backup non-InnoDB tables and files
2024-05-18T15:06:25.821785+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log_213.sdi to /data/backup/db_2024-05-18/mysql/general_log_213.sdi
2024-05-18T15:06:25.824461+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log_213.sdi to /data/backup/db_2024-05-18/mysql/general_log_213.sdi
2024-05-18T15:06:25.825196+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log.CSM to /data/backup/db_2024-05-18/mysql/general_log.CSM
2024-05-18T15:06:25.825535+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log.CSM to /data/backup/db_2024-05-18/mysql/general_log.CSM
2024-05-18T15:06:25.827105+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log.CSV to /data/backup/db_2024-05-18/mysql/general_log.CSV
2024-05-18T15:06:25.827236+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log.CSV to /data/backup/db_2024-05-18/mysql/general_log.CSV
2024-05-18T15:06:25.828050+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log_214.sdi to /data/backup/db_2024-05-18/mysql/slow_log_214.sdi
2024-05-18T15:06:25.829828+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log_214.sdi to /data/backup/db_2024-05-18/mysql/slow_log_214.sdi
2024-05-18T15:06:25.830837+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log.CSM to /data/backup/db_2024-05-18/mysql/slow_log.CSM
2024-05-18T15:06:25.831838+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log.CSM to /data/backup/db_2024-05-18/mysql/slow_log.CSM
2024-05-18T15:06:25.832810+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log.CSV to /data/backup/db_2024-05-18/mysql/slow_log.CSV
2024-05-18T15:06:25.832857+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log.CSV to /data/backup/db_2024-05-18/mysql/slow_log.CSV
2024-05-18T15:06:25.833627+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying performance_schema/cond_instances_82.sdi to /data/backup/db_2024-05-18/performance_schema/cond_instances_82.sdi
2024-05-18T15:06:25.834642+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying performance_schema/cond_instances_82.sdi to /data/backup/db_2024-05-18/performance_schema/cond_instances_82.sdi
2024-05-18T15:06:25.835810+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying performance_schema/error_log_83.sdi to /data/backup/db_2024-05-18/performance_schema/error_log_83.sdi
...
2024-05-18T15:06:26.023267+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files
2024-05-18T15:06:26.023347+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
2024-05-18T15:06:26.040899+08:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status
2024-05-18T15:06:26.054285+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /data/data/mybinlog.000004 to /data/backup/db_2024-05-18/mybinlog.000004 up to position 237
2024-05-18T15:06:26.054671+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/data/mybinlog.000004 to /data/backup/db_2024-05-18/mybinlog.000004
2024-05-18T15:06:26.055494+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db_2024-05-18/mybinlog.index
2024-05-18T15:06:26.055680+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db_2024-05-18/mybinlog.index
2024-05-18T15:06:26.059364+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db_2024-05-18/xtrabackup_binlog_info
2024-05-18T15:06:26.059562+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db_2024-05-18/xtrabackup_binlog_info
2024-05-18T15:06:26.060911+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
2024-05-18T15:06:26.093716+08:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '19730331'
2024-05-18T15:06:26.093745+08:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 19730331
2024-05-18T15:06:26.093976+08:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 19729933
2024-05-18T15:06:26.094655+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2024-05-18T15:06:26.094812+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2024-05-18T15:06:26.094847+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ib_buffer_pool to /data/backup/db_2024-05-18/ib_buffer_pool
2024-05-18T15:06:26.095270+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ib_buffer_pool to /data/backup/db_2024-05-18/ib_buffer_pool
2024-05-18T15:06:26.095576+08:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/data/backup/db_2024-05-18/'
2024-05-18T15:06:26.095588+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'mybinlog.000004', position '237', GTID of the last change '6e7ba49a-1010-11ef-915f-00505623430f:1-11,70d72f44-1012-11ef-b21a-0050563f9c81:1-11'
2024-05-18T15:06:26.095630+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db_2024-05-18/backup-my.cnf
2024-05-18T15:06:26.095685+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db_2024-05-18/backup-my.cnf
2024-05-18T15:06:26.099996+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db_2024-05-18/xtrabackup_info
2024-05-18T15:06:26.100295+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db_2024-05-18/xtrabackup_info
2024-05-18T15:06:27.100965+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (19730331) to (19730341) was copied.
2024-05-18T15:06:27.324850+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
3、创建测试表tab1，并插入5条数据
root@node1 15:52: [(none)]> use testdb
Database changed
root@node1 15:52: [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtab |
+------------------+
1 row in set (0.02 sec)
root@node1 15:52: [testdb]> create table tab1(id int);
Query OK, 0 rows affected (0.06 sec)
root@node1 15:52: [testdb]> insert into tab1 values(1);
Query OK, 1 row affected (0.02 sec)
root@node1 15:52: [testdb]> insert into tab1 values(2);
Query OK, 1 row affected (0.02 sec)
root@node1 15:52: [testdb]> insert into tab1 values(3);
Query OK, 1 row affected (0.01 sec)
root@node1 15:53: [testdb]> insert into tab1 values(4);
Query OK, 1 row affected (0.02 sec)
root@node1 15:53: [testdb]> insert into tab1 values(5);
Query OK, 1 row affected (0.02 sec)
root@node1 15:54: [testdb]> select * from tab1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.01 sec)
4、模拟数据库故障
--查看数据文件目录
root@node1 15:57: [testdb]> show variables like 'datadir';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| datadir | /data/data/ |
+---------------+-------------+
1 row in set (0.01 sec)
--mv数据文件目录
[root@node1 backup]# cd /data
[root@node1 data]# mv data data.bak
5、恢复数据库
# xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/db_2024-05-18
# xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/db_2024-05-18
6、启动数据库
--修改数据文件目录权限
# chown -R mysql:mysql /data/data
--启动数据库
[root@node1 data]# systemctl start mysqld
[root@node1 data]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active:active (running) since Sat 2024-05-18 16:22:09 CST; 6s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 41145 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 41175 (mysqld)
Status: "Server is operational"
Tasks: 41
CGroup: /system.slice/mysqld.service
└─41175 /usr/sbin/mysqld
May 18 16:22:04 node1 systemd[1]: Starting MySQL Server...
May 18 16:22:09 node1 systemd[1]: Started MySQL Server.
7、初次验证数据
因为是在全备份之后创建的tab1表，所有备份恢复回来是没有tab1表的。
[root@node1 data]# mysql -uroot -p'root' testdb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@node1 16:23: [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtab |
+------------------+
1 row in set (0.00 sec)
8、解析binlog
--查看备份是的binlog位点
[root@node1 db_2024-05-18]# cat xtrabackup_binlog_info
mybinlog.000004 237 6e7ba49a-1010-11ef-915f-00505623430f:1-11,70d72f44-1012-11ef-b21a-0050563f9c81:1-11
--解析mv之前的binlog
[root@node1 data]# mysqlbinlog -vv --start-position=237 /data/data.bak/mybinlog.000004 > recoverdata.sql
9、使用binlog恢复数据
[root@node1 data]# mysql -uroot -p'root'< recoverdata.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
10、再次验证数据
[root@node1 data]# mysql -uroot -p'root' testdb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@node1 16:36: [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tab1 |
| testtab |
+------------------+
2 rows in set (0.02 sec)
root@node1 16:36: [testdb]> select * from tab1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
恭喜，数据全部恢复回来了。
六、碰到的问题
安装perl-DBD-mysql包报错
# rpm -ivh perl-DBD-mysql
...
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be installed
Removing mariadb-libs.x86_64 1:5.5.68-1.el7 - u due to obsoletes from installed mysql-community-libs-8.0.33-1.el7.x86_64
--> Restarting Dependency Resolution with new changes.
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be installed
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be installed
Removing mariadb-libs.x86_64 1:5.5.68-1.el7 - u due to obsoletes from installed mysql-community-libs-8.0.33-1.el7.x86_64
...
解决办法：rpm -ivh mysql-community-libs-compat-8.0.33-1.el7.x86_64.rpm
