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./yum/percona-release-latest.noarch.rpm #安装Percona yum存储库
$ yum install percona-xtrabackup-24 #安装percona-xtrabackup 备份工具 --注意MySQL8不再被xtrabackup24支持
第二种方法
$ wget https://www./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)
恢复完成
|
