linux 恢复备份bak文件,xtrabakup备份与恢复

内核方面：

$ cat /etc/centos-release

CentOS Linux release 7.4.1708 (Core)

$ uname -r

3.10.0-693.el7.x86_64

下载安装xtrabackup：

$ wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar

$ tar -xf Percona-XtraBackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar

$ yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

$ which xtrabackup

/usr/bin/xtrabackup

$ innobackupex -v

xtrabackup: recognized server arguments: --innodb_buffer_pool_size=1024M --datadir=/data/mysql/data --server-id=1

创建测试数据：

mysql> mysql -uroot -p

mysql> create database opop charset='utf8mb4';

mysql> use opop;

mysql> create table user(id int unsigned primary key auto_increment,name varchar(30));

mysql> show tables;

mysql> insert into user(name) values('jack'),('tom'),('lily'),('lucy');

mysql> select from user;

mysql> commit;

mysql> select from user;

Xtrabackup全量备份与恢复

$ innobackupex --defaults-file=/etc/my.cnf --user=sstuser --password="123456" --backup . 　　　　　　 #最后有个点,是当前目录

xtrabackup: recognized server arguments: --innodb_buffer_pool_size=1024M --datadir=/data/mysql/data --server-id=1

xtrabackup: recognized client arguments: --innodb_buffer_pool_size=1024M --datadir=/data/mysql/data --server-id=1 --backup=1

181025 08:27:43 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.

At the end of a successful backup run innobackupex

prints "completed OK!".

181025 08:27:43 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'sstuser' (using password: YES).

181025 08:27:43 version_check Connected to MySQL server

181025 08:27:43 version_check Executing a version check against the server...

181025 08:27:43 version_check Done.

181025 08:27:43 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: not set

Using server version 5.7.23-23-57

innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /data/mysql/data

xtrabackup: open files limit requested 0, set to 1024

xtrabackup: using the following InnoDB configuration:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup: innodb_log_group_home_dir = ./

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

181025 08:27:43 >> log scanned up to (2605686)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0

181025 08:27:44 [01] Copying ./ibdata1 to /root/2018-10-25_08-27-43/ibdata1

181025 08:27:44 [01] ...done

....

....

181025 08:27:46 Executing LOCK TABLES FOR BACKUP... 　　　　 #翻译：执行锁定表以进行备份

181025 08:27:46 Starting to backup non-InnoDB tables and files

181025 08:27:46 [01] Copying ./mysql/db.opt to /root/2018-10-25_08-27-43/mysql/db.opt

181025 08:27:46 [01] ...done

181025 08:27:46 [01] Copying ./mysql/db.frm to /root/2018-10-25_08-27-43/mysql/db.frm

181025 08:27:46 [01] ...done

....

....

181025 08:27:47 [01] Copying ./opop/user.frm to /root/2018-10-25_08-27-43/opop/user.frm

181025 08:27:47 [01] ...done

181025 08:27:47 Finished backing up non-InnoDB tables and files

181025 08:27:47 Executing LOCK BINLOG FOR BACKUP... 　　　　 #执行锁定二进制文件开始备份

181025 08:27:47 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... 　　 #执行flush no_write_to_binlog引擎日志

xtrabackup: The latest check point (for incremental): '2605677'

xtrabackup: Stopping log copying thread. 　　　　　　　　　　　　 #停止日志复制线程。

.181025 08:27:47 >> log scanned up to (2605686) #日志扫描到(2605686)

181025 08:27:47 Executing UNLOCK BINLOG 　　　　　　　　　　 #执行解锁binlog

181025 08:27:47 Executing UNLOCK TABLES 　　　　　　　　　　 #执行解锁表

181025 08:27:47 All tables unlocked 　　　　　　　　　　　　　　 #所有表解锁

181025 08:27:47 [00] Copying ib_buffer_pool to /root/2018-10-25_08-27-43/ib_buffer_pool

181025 08:27:47 [00] ...done

181025 08:27:47 Backup created in directory '/root/2018-10-25_08-27-43/' #在/root/2018-10-25_08-27-43/目录中创建备份

181025 08:27:47 [00] Writing /root/2018-10-25_08-27-43/backup-my.cnf

181025 08:27:47 [00] ...done

181025 08:27:47 [00] Writing /root/2018-10-25_08-27-43/xtrabackup_info

181025 08:27:47 [00] ...done

xtrabackup: Transaction log of lsn (2605677) to (2605686) was copied. #已复制lsn(2605677)到(2605686)的事务日志。

181025 08:27:48 completed OK!　　　　　　 #完成好了！

在当前目录下会生成一个以当前时间为名字的一个目录:

#这里面就是相关的备份文件，同样也可以看到我们创建的库的名称

使用以下命令使相关数据性文件保持一致性状态

[root@localhost ~]# innobackupex --apply-log /root/2018-10-25_08-27-43/

xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1

xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1

181025 08:57:41 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.

At the end of a successful apply-log run innobackupex

prints "completed OK!".

innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)

xtrabackup: cd to /root/2018-10-25_08-27-43/

xtrabackup: This target seems to be not prepared yet.

InnoDB: Number of pools: 1

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2605677)

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup: innodb_log_group_home_dir = .

xtrabackup: innodb_log_files_in_group = 1

xtrabackup: innodb_log_file_size = 8388608

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup: innodb_log_group_home_dir = .

xtrabackup: innodb_log_files_in_group = 1

xtrabackup: innodb_log_file_size = 8388608

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Uses event mutexes

InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority: -20

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 2605677

InnoDB: Doing recovery: scanned up to log sequence number 2605686 (0%)

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

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 2605686

xtrabackup: Recovered WSREP position: bbe63ad7-d767-11e8-9ffc-073dc1824390:8

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 2605783

InnoDB: Number of pools: 1

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup: innodb_log_group_home_dir = .

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 50331648

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Uses event mutexes

InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority: -20

InnoDB: Setting log file ./ib_logfile101 size to 48 MB

InnoDB: Setting log file ./ib_logfile1 size to 48 MB

InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

InnoDB: New log files created, LSN=2605783

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 2606092

InnoDB: Doing recovery: scanned up to log sequence number 2606101 (0%)

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

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 2606101

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 2606120

181025 08:57:48 completed OK!

恢复

先删除相应的表

注：这里需要注意的是没有删除库，因为删除库后恢复时会报错，因为它找不到相应的库，要新建出来相应名字的库再恢复

mysql -uroot -p

mysql> drop table opop.user;

mysql> select * from opop.user;

ERROR 1146 (42S02): Table 'opop.user' doesn't exist

mysql> use opop;

mysql> show tables;

mysql> exit

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2018-10-25_08-27-43/

注：这里一定要记得重启，且在Percona XtraDB集群中主节点和其他节点的启动、重启方式不一样，因为该文档是接上一篇文档

登陆到数据库中查看恢复情况

Xtrabackup增量备份与恢复

增量备份仅能应用于InooDB或XtraDB表，对于MyISAM表，增量与全备相同，在上面的基础上继续创建一个opop2的库然后在opop-2的库里建一张表并插入数据

mysql> create database opop2 charset='utf8mb4';

mysql> use opop2;

mysql> create table user2(id int unsigned primary key auto_increment,name varchar(30));

mysql> insert into user2(name) values('zhangsan'),('lisi'),('wangwu'),('maliu');

mysql> commit;

mysql> select from user2;

备份开始

略去中间部分…………

#--incremental /backup/ 　　指定增量备份文件备份的目录

#--incremental-basedir 　　 指定上一次全备或增量备份的目录

删除一条数据来测试增量恢复 (这里删除的是增量备份的数据)

mysql -uroot -p

mysql> use opop2;

mysql> show tables;

mysql> desc user2;

mysql> delete from user2 where name='lisi';

mysql> select from user2; #确认lisi用户已经删除

mysql> exit

增量恢复操作过程如下

[root@localhost ~]# innobackupex --apply-log --redo-only /root/2018-10-25_08-27-43/ 　　#第一次的全局备份数据目录

[root@localhost ~]# innobackupex --apply-log --redo-only /root/2018-10-25_08-27-43/ --incremental-dir=/root/2018-10-25_11-00-25/ #第二次的增量备份数据目录

注：这里相当于一个合并的动作

恢复全部数据：

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2018-10-25_08-27-43/

[root@localhost data]# systemctl restart mysql@bootstrap.service

#因为该实验中是做的Percona XtraDB Cluster集群，所以使用该命令，如不是Percona XtraDB Cluster 集群，在centos上则使用 systemctl 或 service 启动或重启

[root@localhost data]# mysql -uroot -p