xtrabackup整库全备恢复单库

1. 创建测试环境

建立测试库

mysql> create database testa;
Query OK, 1 row affected (0.00 sec)

mysql> create database testb;
Query OK, 1 row affected (0.00 sec)

mysql> create database testc;
Query OK, 1 row affected (0.00 sec)

mysql> create database testd;
Query OK, 1 row affected (0.01 sec)

为每个库创建测试表

mysql> use testa;
Database changed
mysql> CREATE TABLE `test` (                                 
 
           `id` int(11) NOT NULL AUTO_INCREMENT,               
 
           `str` varchar(255) NOT NULL,                                    
 
           `state` tinyint(1) unsigned zerofill DEFAULT NULL,  
 
           `state2` tinyint(2) unsigned zerofill DEFAULT NULL, 
 
           `state3` tinyint(3) unsigned zerofill DEFAULT NULL, 
 
           `state4` tinyint(4) unsigned zerofill DEFAULT NULL, 
 
           PRIMARY KEY (`id`)                                  
 
         ) ;
Query OK, 0 rows affected (0.14 sec)

mysql> insert test(id,str) values (1,'zzl');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

--其余三个库做相同操作，插入数据要求无限制
**testb:**
mysql> insert test(id,str,state) values (2,'zzl',2);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

**testc：**

mysql> insert test(id,str,state,state2) values (3,'zzl',3,3);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

**testd：**

mysql> insert test(id,str,state,state2,state3) values (4,'zzl',4,4,4);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

测试环境如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| testa              |
| testb              |
| testc              |
| testd              |
| work               |
+--------------------+
10 rows in set (0.00 sec)

---表信息
mysql> select * from test; --testa
+----+-----+-------+--------+--------+--------+
| id | str | state | state2 | state3 | state4 |
+----+-----+-------+--------+--------+--------+
|  1 | zzl |  NULL |   NULL |   NULL |   NULL |
+----+-----+-------+--------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from test; --testb
+----+-----+-------+--------+--------+--------+
| id | str | state | state2 | state3 | state4 |
+----+-----+-------+--------+--------+--------+
|  2 | zzl |     2 |   NULL |   NULL |   NULL |
+----+-----+-------+--------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from test; --testc
+----+-----+-------+--------+--------+--------+
| id | str | state | state2 | state3 | state4 |
+----+-----+-------+--------+--------+--------+
|  3 | zzl |     3 |     03 |   NULL |   NULL |
+----+-----+-------+--------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from test; --testd
+----+-----+-------+--------+--------+--------+
| id | str | state | state2 | state3 | state4 |
+----+-----+-------+--------+--------+--------+
|  4 | zzl |     4 |     04 |    004 |   NULL |
+----+-----+-------+--------+--------+--------+
1 row in set (0.00 sec)

2. 测试流程

PERCONA XTRABACKUP 安装方法
官网搬运：

！$ yum -y install libev　　#先安装libev包，缺少会导致失败报错。!

第一种方法
$ sudo root yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm #安装Percona yum存储库
$ yum install percona-xtrabackup-24　　#安装percona-xtrabackup 备份工具 --注意MySQL8不再被xtrabackup24支持

第二种方法
$ wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/ 　　#下载发行包
$ yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm　　#安装发行包
！可能会报缺少依赖包，缺少什么yum install 什么！

第三种方法
$ yum install cmake gcc gcc-c ++ libaio libaio-devel automake autoconf
bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel vim
$ git clone https://github.com/percona/percona-xtrabackup.git　
$ cd percona-xtrabackup
$ cmake -DBUILD_CONFIG = xtrabackup_release -DWITH_MAN_PAGES = OFF && make -j4
$ make install

1）确定安装软件包

[root@mysql8 ~]# rpm -qa | grep xtrabackup
percona-xtrabackup-80-8.0.14-1.el7.x86_64

2）创建备份目录

[root@mysql8 ~]# mkdir /data/backup -p
[root@mysql8 ~]# mkdir /data/backupInc -p
[root@mysql8 ~]# chown -R mysql.mysql /data/

3）创建备份用户

mysql> create user 'xtra'@'%' identified by '1234';
Query OK, 0 rows affected (0.01 sec)

mysql> grant backup_admin,process,reload,lock tables,replication client on *.* to 'xtra'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON performance_schema.log_status TO 'xtra'@'%';
Query OK, 0 rows affected (0.13 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

4）执行全备

[root@mysql8 backup]# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --host=192.168.103.67 --user=xtra --password=1234 --port=3306 --backup --parallel=3 --target-dir=/data/backup/
...
201118 12:00:32 [00]        ...done
201118 12:00:32 Backup created in directory '/data/backup/'
MySQL binlog position: filename 'mysql-bin.000040', position '195', GTID of the last change '32415cb9-d5ff-11ea-a561-000c29135205:1-104'
201118 12:00:32 [00] Writing /data/backup/backup-my.cnf
201118 12:00:32 [00]        ...done
201118 12:00:32 [00] Writing /data/backup/xtrabackup_info
201118 12:00:32 [00]        ...done
xtrabackup: Transaction log of lsn (212703909) to (212707801) was copied.
201118 12:00:34 completed OK!

5）查看当前备份文件

[root@mysql8 backup]# ll
total 1577028
-rw-r----- 1 root root        521 Nov 18 12:00 backup-my.cnf
-rw-r----- 1 root root      24064 Nov 18 12:00 ib_buffer_pool
-rw-r----- 1 root root 1073741824 Nov 18 12:00 ibdata1
-rw-r----- 1 root root  419430400 Nov 18 12:00 ibdata2
drwxr-x--- 2 root root        143 Nov 18 12:00 mysql
-rw-r----- 1 root root        195 Nov 18 12:00 mysql-bin.000040
-rw-r----- 1 root root         45 Nov 18 12:00 mysql-bin.index
-rw-r----- 1 root root   25165824 Nov 18 12:00 mysql.ibd
drwxr-x--- 2 root root       4096 Nov 18 12:00 performance_schema
drwxr-x--- 2 root root         28 Nov 18 12:00 sys
drwxr-x--- 2 root root        148 Nov 18 12:00 test
drwxr-x--- 2 root root         22 Nov 18 12:00 testa
drwxr-x--- 2 root root         22 Nov 18 12:00 testb
drwxr-x--- 2 root root         22 Nov 18 12:00 testc
drwxr-x--- 2 root root         22 Nov 18 12:00 testd
-rw-r----- 1 root root   54525952 Nov 18 12:00 undo_001
-rw-r----- 1 root root   41943040 Nov 18 12:00 undo_002
drwxr-x--- 2 root root         39 Nov 18 12:00 work
-rw-r----- 1 root root         64 Nov 18 12:00 xtrabackup_binlog_info
-rw-r----- 1 root root         97 Nov 18 12:00 xtrabackup_checkpoints
-rw-r----- 1 root root        645 Nov 18 12:00 xtrabackup_info
-rw-r----- 1 root root       6144 Nov 18 12:00 xtrabackup_logfile
-rw-r----- 1 root root         39 Nov 18 12:00 xtrabackup_tablespaces

6）进行删库误操作

mysql> drop database testa;
Query OK, 1 row affected (0.03 sec)

mysql> drop database testb;
Query OK, 1 row affected (0.02 sec)

mysql> drop database testc;
Query OK, 1 row affected (0.01 sec)

7）将备份文件中不需要恢复的库移出目录

[root@mysql8 backup]# mkdir /data/xtratest
[root@mysql8 backup]# mv testa /data/xtratest/
[root@mysql8 backup]# mv testb /data/xtratest/

8）应用日志准备

[root@mysql8 backup]# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --prepare --apply-log-only --target-dir=/data/backup
Log background threads are being closed...
Shutdown completed; log sequence number 212707801
Number of pools: 1
201118 14:11:46 completed OK

[root@mysql8 data]#  xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --prepare --target-dir=/data/backup
Shutdown completed; log sequence number 212707852
201118 14:13:24 completed OK!

9）清空数据文件，日志文件，undo文件，将相关文件拷贝回指定目录

报错信息如下：
未清理数据文件：
 [root@mysql8 data]# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf  --host=192.168.103.67 --user=xtra --password=1234 --port=3306 --datadir=/data/mysql/mysql_3306/data/ --copy-back --target-dir=/data/backup/
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql_3306/data --open_files_limit=65535 --server-id=3306 --log_bin=/data/mysql/mysql_3306/logs/mysql-bin --innodb_buffer_pool_size=2867M --innodb_data_file_path=ibdata1:1G;ibdata2:200M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=2G --innodb_log_files_in_group=3 --innodb_undo_directory=/data/mysql/mysql_3306/undolog --innodb_undo_tablespaces=95 --innodb_io_capacity=4000 --innodb_write_io_threads=8 --innodb_read_io_threads=8 --innodb_open_files=65535 --innodb_max_dirty_pages_pct=50 --innodb_flush_method=O_DIRECT --innodb_checksum_algorithm=crc32 --innodb_file_per_table=1 --datadir=/data/mysql/mysql_3306/data/ 
xtrabackup: recognized client arguments: --port=3306 --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock --host=192.168.103.67 --user=xtra --password=* --port=3306 --copy-back=1 --target-dir=/data/backup/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7)
Original data directory /data/mysql/mysql_3306/data/ is not empty!

未清理日志文件： 
[root@mysql8 undolog]# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf  --host=192.168.103.67 --user=xtra --password=1234 --port=3306 --datadir=/data/mysql/mysql_3306/data/ --copy-back --target-dir=/data/backup/
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql_3306/data --open_files_limit=65535 --server-id=3306 --log_bin=/data/mysql/mysql_3306/logs/mysql-bin --innodb_buffer_pool_size=2867M --innodb_data_file_path=ibdata1:1G;ibdata2:200M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=2G --innodb_log_files_in_group=3 --innodb_undo_directory=/data/mysql/mysql_3306/undolog --innodb_undo_tablespaces=95 --innodb_io_capacity=4000 --innodb_write_io_threads=8 --innodb_read_io_threads=8 --innodb_open_files=65535 --innodb_max_dirty_pages_pct=50 --innodb_flush_method=O_DIRECT --innodb_checksum_algorithm=crc32 --innodb_file_per_table=1 --datadir=/data/mysql/mysql_3306/data/ 
xtrabackup: recognized client arguments: --port=3306 --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock --host=192.168.103.67 --user=xtra --password=* --port=3306 --copy-back=1 --target-dir=/data/backup/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7)
201118 14:22:35 [01] Copying undo_001 to /data/mysql/mysql_3306/undolog/undo_001
201118 14:22:35 [01]        ...done
201118 14:22:35 [01] Copying undo_002 to /data/mysql/mysql_3306/undolog/undo_002
201118 14:22:35 [01]        ...done
201118 14:22:35 [01] Copying ib_logfile0 to /data/mysql/mysql_3306/data/ib_logfile0
201118 14:22:41 [01]        ...done
201118 14:22:41 [01] Copying ib_logfile1 to /data/mysql/mysql_3306/data/ib_logfile1
201118 14:22:49 [01]        ...done
201118 14:22:51 [01] Copying ib_logfile2 to /data/mysql/mysql_3306/data/ib_logfile2
201118 14:23:24 [01]        ...done
201118 14:23:29 [01] Copying ibdata1 to /data/mysql/mysql_3306/data/ibdata1
201118 14:23:41 [01]        ...done
201118 14:23:41 [01] Copying ibdata2 to /data/mysql/mysql_3306/data/ibdata2
201118 14:23:50 [01]        ...done
xtrabackup: Can't create/write to file '/data/mysql/mysql_3306/logs/mysql-bin.000040' (OS errno 17 - File exists)'
[01] error: cannot open the destination stream for mysql-bin.000040
[01] Error: copy_file() failed. 

未清理undo：
[root@mysql8 data]# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf  --host=192.168.103.67 --user=xtra --password=1234 --port=3306 --datadir=/data/mysql/mysql_3306/data/ --copy-back --target-dir=/data/backup/
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql_3306/data --open_files_limit=65535 --server-id=3306 --log_bin=/data/mysql/mysql_3306/logs/mysql-bin --innodb_buffer_pool_size=2867M --innodb_data_file_path=ibdata1:1G;ibdata2:200M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=2G --innodb_log_files_in_group=3 --innodb_undo_directory=/data/mysql/mysql_3306/undolog --innodb_undo_tablespaces=95 --innodb_io_capacity=4000 --innodb_write_io_threads=8 --innodb_read_io_threads=8 --innodb_open_files=65535 --innodb_max_dirty_pages_pct=50 --innodb_flush_method=O_DIRECT --innodb_checksum_algorithm=crc32 --innodb_file_per_table=1 --datadir=/data/mysql/mysql_3306/data/ 
xtrabackup: recognized client arguments: --port=3306 --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock --host=192.168.103.67 --user=xtra --password=* --port=3306 --copy-back=1 --target-dir=/data/backup/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7)
xtrabackup: Can't create/write to file '/data/mysql/mysql_3306/undolog/undo_001''(OS errno 17 - File exists)
[01] error: cannot open the destination stream for undo_001
[01] Error: copy_file() failed.

10）恢复后操作

—恢复完成后，注意文件权限

[root@mysql8 data]# pwd
/data/mysql/mysql_3306/data
[root@mysql8 data]# chown -R mysql.mysql /data/

重新启动数据库

3. 校验数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| testa              |
| testb              |
| testc              |
| testd              |
| work               |
+--------------------+
10 rows in set (0.00 sec)

mysql> use testa;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_testa |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)


mysql> select * from test;
ERROR 1812 (HY000): Tablespace is missing for table `testa`.`test`.
表结构信息存放在ibdata里面，一样会被恢复但是没有数据

mysql> show tables;
+-----------------+
| Tables_in_testd |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from test;
+----+-----+-------+--------+--------+--------+
| id | str | state | state2 | state3 | state4 |
+----+-----+-------+--------+--------+--------+
|  4 | zzl |     4 |     04 |    004 |   NULL |
+----+-----+-------+--------+--------+--------+
1 row in set (0.00 sec)

恢复完成

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。