Xtrabackup 全备和还原以及增量备份和还原
目录
本文继Xtrabackup安装和参数详解，本次介绍
全备和还原以及增量备份和还原
MySQL环境介绍
阿里云云主机
系统信息以及数据库版本，数据存放目录具体如下：
[root@node ~]# uname -r
4.18.16-1.el7.elrepo.x86_64
[root@node ~]# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)
[root@node ~]# /opt/mysql/bin/mysql -V
/opt/mysql/bin/mysql Ver 14.14 Distrib 5.6.39, for linux-glibc2.12 (x86_64) using EditLine wrapper
[root@node ~]# ls /opt/mysql/data/
auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
[root@node ~]# ls -ld /opt/mysql/my.cnf
-rw-r--r-- 1 mysql mysql 1468 Dec 6 21:37 /opt/mysql/my.cnf
全备和还原
准备备份目录
[root@node ~]# cd /opt/
[root@node opt]# ls
mysql mysql-5.6 src
[root@node opt]# mkdir backup/{full,incr} -p
[root@node opt]# ls backup/
full incr
- full： 文件中存放全量备份
- incr： 文件中存放增量备份
创建测试数据
创建一个测试数据库，库名为：xtra_test
在xtra_test库中，创建一个friends表，字段只有id和name，并插入一条数据
具体操作如下：
[root@node opt]# mysql -uroot -p234567
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 19
Server version: 5.6.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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.
mysql> create database xtra_test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xtra_test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use xtra_test;
Database changed
mysql> create table friends (
-> id int(3) not null,
-> name varchar(8) not null
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------------+
| Tables_in_xtra_test |
+---------------------+
| friends |
+---------------------+
1 row in set (0.00 sec)
mysql> desc friends;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(3) | NO | | NULL | |
| name | varchar(8) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO friends( id, name ) VALUES ( 1, 'jack' ) ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from friends;
+----+------+
| id | name |
+----+------+
| 1 | jack |
+----+------+
1 row in set (0.00 sec)
全量备份
这里的具体参数含义请查看：Xtrabackup安装和参数详解
[root@node opt]# innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=234567 /opt/backup/full/
......
181211 11:18:44 [01] ...done
181211 11:18:44 Finished backing up non-InnoDB tables and files
181211 11:18:44 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1645416'
xtrabackup: Stopping log copying thread.
.181211 11:18:44 >> log scanned up to (1645416)
181211 11:18:44 Executing UNLOCK TABLES
181211 11:18:44 All tables unlocked
181211 11:18:44 Backup created in directory '/opt/backup/full/2018-12-11_11-18-42/'
181211 11:18:44 [00] Writing /opt/backup/full/2018-12-11_11-18-42/backup-my.cnf
181211 11:18:44 [00] ...done
181211 11:18:44 [00] Writing /opt/backup/full/2018-12-11_11-18-42/xtrabackup_info
181211 11:18:44 [00] ...done
xtrabackup: Transaction log of lsn (1645416) to (1645416) was copied.
181211 11:18:44 completed OK!
最后提示 OK 了，查看备份目录中
[root@node opt]# ls /opt/backup/full/
2018-12-11_11-18-42
[root@node opt]# ls /opt/backup/full/2018-12-11_11-18-42/
backup-my.cnf ibdata1 mysql performance_schema xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile xtra_test
备份完后，备份目录下有几个文件需要注意：
xtrabackup_binlog_info
记录了binlog的position，若开启了GTID，也会将GTID取出。在用于备份+binlog恢复或建立slave的场景里十分有用
注意 如果MySQL没有开启log-bin参数，则不会生产次文件
backup-my.cnf
记录了备份时可能涉及到的选项参数，比如系统表空间信息，独立undo表空间信息，redo-log信息等
xtrabackup_checkpoints
记录了此次备份的类型和lsn号的起始值，是否压缩等
xtrabackup_info
记录了备份工具的信息，时间，备份对象（是针对全实例还是某库表），是否是增量，binlog位置等
模拟删除数据
直接删除
xtra_test数据库；
此操作为模拟，请勿真是在线上操作
[root@node opt]# mysql -uroot -p234567
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 4
Server version: 5.6.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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.
mysql> drop database xtra_test;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
还原数据操作
还原步骤：
- 先把xtrabackup备份下来的目录进行再次备份，防止在还原的时候误操作，导致备份不可用；
- 关闭要回复的数据库实例；
- 移除需要恢复数据的MySQL实例下的data目录，建议
mv移动到别的目录下； - 使用
--apply-log参数进行恢复前准备操作，如事物回滚等操作； - 使用
--copy-back和--rsync参数进行恢复； - 授权MySQL实例下的data目录后，启动数据库
- 验证恢复数据
第一步 备份备份文件
[root@node ~]# cd /opt/backup/full/
[root@node full]# ls
2018-12-11_14-04-15
[root@node full]# cp -a 2018-12-11_14-04-15 2018-12-11_14-04-15_back
[root@node full]# ll
total 8
drwxr-x--- 5 root root 4096 Dec 11 14:04 2018-12-11_14-04-15
drwxr-x--- 5 root root 4096 Dec 11 14:04 2018-12-11_14-04-15_back
第二步 关闭数据库
[root@node full]# /etc/init.d/mysqld stop
Shutting down MySQL..
[root@node full]# ps aux|grep mysql
root 19368 0.0 0.1 112720 2232 pts/1 S+ 15:01 0:00 grep --color=auto mysql
[root@node full]# netstat -lntup | grep 3306
第三步 移除数据库的data目录
[root@node full]# mv /opt/mysql/data /tmp/
[root@node full]# ls -ld /opt/mysql/data
ls: cannot access /opt/mysql/data: No such file or directory
[root@node full]# ls /tmp/data/
auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.index performance_schema
第四步 恢复前准备
[root@node full]# innobackupex --defaults=/opt/mysql/my.cnf --apply-log /opt/backup/full/2018-12-11_14-04-15/
.......
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1654036
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1654284
InnoDB: Doing recovery: scanned up to log sequence number 1654293 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 635, file name mysql-bin.000001
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.19 started; log sequence number 1654293
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1654312
181211 14:42:07 completed OK!
[root@node full]# ls 2018-12-11_14-04-15/ -l
total 294956
-rw-r----- 1 root root 481 Dec 11 14:04 backup-my.cnf
-rw-r----- 1 root root 12582912 Dec 11 14:42 ibdata1
-rw-r----- 1 root root 134217728 Dec 11 14:42 ib_logfile0
-rw-r----- 1 root root 134217728 Dec 11 14:42 ib_logfile1
-rw-r----- 1 root root 12582912 Dec 11 14:42 ibtmp1
drwxr-x--- 2 root root 4096 Dec 11 14:04 mysql
drwxr-x--- 2 root root 4096 Dec 11 14:04 performance_schema
-rw-r----- 1 root root 21 Dec 11 14:04 xtrabackup_binlog_info
-rw-r--r-- 1 root root 21 Dec 11 14:42 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 113 Dec 11 14:42 xtrabackup_checkpoints
-rw-r----- 1 root root 509 Dec 11 14:04 xtrabackup_info
-rw-r----- 1 root root 8388608 Dec 11 14:42 xtrabackup_logfile
-rw-r--r-- 1 root root 1 Dec 11 14:42 xtrabackup_master_key_id
drwxr-x--- 2 root root 4096 Dec 11 14:04 xtra_test
注意文件时间的变化，说明准备全备文件的操作只是对备份集本身做了相关处理，所以在准备恢复前最好对备份集再次备份
第五步 恢复数据
[root@node full]# innobackupex --defaults-file=/opt/mysql-5.6/my.cnf --copy-back /opt/backup/full/2018-12-11_14-04-15/
......
181211 15:02:41 [01] ...done
181211 15:02:41 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /opt/mysql/data/performance_schema/events_statements_summary_by_thread_by_event_name.frm
181211 15:02:41 [01] ...done
181211 15:02:41 [01] Copying ./performance_schema/mutex_instances.frm to /opt/mysql/data/performance_schema/mutex_instances.frm
181211 15:02:41 [01] ...done
181211 15:02:41 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /opt/mysql/data/performance_schema/events_waits_summary_global_by_event_name.frm
181211 15:02:41 [01] ...done
181211 15:02:41 [01] Copying ./xtrabackup_info to /opt/mysql/data/xtrabackup_info
181211 15:02:41 [01] ...done
181211 15:02:41 [01] Copying ./xtrabackup_master_key_id to /opt/mysql/data/xtrabackup_master_key_id
181211 15:02:41 [01] ...done
181211 15:02:41 completed OK!
[root@node full]# ls /opt/mysql/data/ -l
total 286748
-rw-r----- 1 root root 12582912 Dec 11 15:02 ibdata1
-rw-r----- 1 root root 134217728 Dec 11 15:02 ib_logfile0
-rw-r----- 1 root root 134217728 Dec 11 15:02 ib_logfile1
-rw-r----- 1 root root 12582912 Dec 11 15:02 ibtmp1
drwxr-x--- 2 root root 4096 Dec 11 15:02 mysql
drwxr-x--- 2 root root 4096 Dec 11 15:02 performance_schema
-rw-r----- 1 root root 21 Dec 11 15:02 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 509 Dec 11 15:02 xtrabackup_info
-rw-r----- 1 root root 1 Dec 11 15:02 xtrabackup_master_key_id
drwxr-x--- 2 root root 4096 Dec 11 15:02 xtra_test
第六步 授权data目录启动MySQL
[root@node full]# ls -l /opt/mysql/data/
total 286748
-rw-r----- 1 mysql mysql 12582912 Dec 11 15:02 ibdata1
-rw-r----- 1 mysql mysql 134217728 Dec 11 15:02 ib_logfile0
-rw-r----- 1 mysql mysql 134217728 Dec 11 15:02 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Dec 11 15:02 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Dec 11 15:02 mysql
drwxr-x--- 2 mysql mysql 4096 Dec 11 15:02 performance_schema
-rw-r----- 1 mysql mysql 21 Dec 11 15:02 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 509 Dec 11 15:02 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Dec 11 15:02 xtrabackup_master_key_id
drwxr-x--- 2 mysql mysql 4096 Dec 11 15:02 xtra_test
[root@node full]# /etc/init.d/mysqld start
Starting MySQL.. [ OK ]
[root@node full]# ps aux|grep mysql
root 19450 0.0 0.1 113324 3196 pts/1 S 15:04 0:00 /bin/sh /opt/mysql/bin/mysqld_safe --datadir=/opt/mysql/data --pid-file=/opt/mysql/mysqld.pid
mysql 20257 2.2 44.1 2299484 901804 pts/1 Sl 15:04 0:00 /opt/mysql/bin/mysqld --basedir=/opt/mysql --datadir=/opt/mysql/data --plugin-dir=/opt/mysql/lib/plugin --user=mysql --log-error=/opt/mysql/error.log --open-files-limit=4161 --pid-file=/opt/mysql/mysqld.pid --socket=/tmp/mysql.sock --port=3306
root 20288 0.0 0.1 112720 2244 pts/1 S+ 15:04 0:00 grep --color=auto mysql
[root@node full]# netstat -lntup | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 20257/mysqld
第七步 验证数据恢复操作
[root@node full]# mysql -uroot -p234567
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 1
Server version: 5.6.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xtra_test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use xtra_test;
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_xtra_test |
+---------------------+
| friends |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from friends;
+----+------+
| id | name |
+----+------+
| 1 | jack |
+----+------+
1 row in set (0.01 sec
至此，全量备份和还原完成；
技术男一枚，喜欢做技术分享，把学习的过程，以及遇到问题的解决过程都愿意分享给大家，博客中如有不足，请留言或者联系博主，感谢。
邮箱: sijiayong000@163.com
Q Q: 601566386
