详解使用Percona XtraBackup进行单库、单表备份并快速恢复到其他环境下

最新推荐文章于 2023-09-10 10:50:27 发布

搬砖码农SmallNNN

最新推荐文章于 2023-09-10 10:50:27 发布

阅读量5.4k

点赞数 1

分类专栏： Xtrabackup 文章标签： Percona Xtrabackup Xtrabackup 单库单表备份

本文链接：https://blog.csdn.net/u014180504/article/details/90756604

版权

Xtrabackup 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

这里直接开门见山，代码糊上由于安全问题使用的账户以及库均为虚拟，方式有两种一种是innobackupex ，一种是XtraBackup

innobackupex 步骤如下：

备份单表：
1 制作备份
[root@localhost ~]# innobackupex --user=backup --password=123456 --databases="cumcm.cumcm_sys_country" /home/pack
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql/ --server-id=53 --log_bin=mysql-bin --innodb_flush_log_at_trx_commit=2 --innodb_buffer_pool_size=3016M --innodb_log_file_size=500M --innodb_log_buffer_size=8M 
xtrabackup: recognized client arguments: --datadir=/var/lib/mysql/ --server-id=53 --log_bin=mysql-bin --innodb_flush_log_at_trx_commit=2 --innodb_buffer_pool_size=3016M --innodb_log_file_size=500M --innodb_log_buffer_size=8M 
190531 13:17:57 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!".

190531 13:17:57  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/lib/mysql/mysql.sock' as 'backup'  (using password: YES).
190531 13:17:57  version_check Connected to MySQL server
190531 13:17:57  version_check Executing a version check against the server...
190531 13:18:53  version_check Done.
190531 13:18:53 Connecting to MySQL server host: localhost, user: backup, password: set, port: 3306, socket: /var/lib/mysql/mysql.sock
Using server version 5.6.25-73.0-log
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 /var/lib/mysql/
...
...
...
xtrabackup: Transaction log of lsn (40349402124) to (40349402124) was copied.
190531 13:19:02 completed OK!
查看备份
[root@localhost 2019-05-31_13-17-57]# ls
backup-my.cnf  cumcm  ibdata1  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile
2 准备恢复文件
[root@localhost 2019-05-31_13-17-57]# innobackupex --apply-log --export /home/pack/2019-05-31_13-17-57/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=524288000 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=53 --redo-log-version=0 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=524288000 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=53 --redo-log-version=0 
190531 13:21:07 innobackupex: Starting the apply-log operation
...
...
...
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 40349444648
190531 13:21:15 completed OK!
查看恢复出来的文件
[root@localhost cumcm]# ls
cumcm_sys_country.cfg  cumcm_sys_country.exp  cumcm_sys_country.frm  cumcm_sys_country.ibd
3 创建表（库）
mysql> CREATE TABLE `cumcm_sys_country` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `AreaCode` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=193 DEFAULT CHARSET=utf8;
4 丢弃表空间
mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
5 恢复文件拷贝到对应表或者库目录下
[root@localhost cumcm]# cp /home/pack/2019-05-31_13-17-57/cumcm/cumcm_sys_country.* /var/lib/mysql/cumcm
cp：是否覆盖"/var/lib/mysql/cumcm/cumcm_sys_country.frm"？ y
查看覆盖情况
[root@localhost cumcm]# ls
cumcm_sys_country.cfg  cumcm_sys_country.exp  cumcm_sys_country.frm  cumcm_sys_country.ibd
[root@localhost cumcm]# cd /var/lib/mysql
6 授权文件
[root@localhost cumcm]# chown -R mysql:mysql /var/lib/mysql
7 加载表空间
mysql> ALTER TABLE cumcm.cumcm_sys_country IMPORT TABLESPACE;

[root@localhost cumcm]# 
完成

说明指定单个表或者库的方式这里补充说明下Percona XtraBackup的部分备份与恢复/单库备份/单表备份/指定库备份/指定表备份可以参考这位大神的分享，也可以去看官方文档，

XtraBackup命令备份单库、单表和全备区别就在于备份时指定库名和表名恢复的时候多一个–export单数具体参考Percona XtraBackup 2.4新特性之恢复单个表数据,Percona Document

需要满足的条件：

innodb_file_per_table开启

innodb存储引擎

表有对应的.ibd文件

满足以上条件就可以通过物理备份集生成以表为单位的export文件

测试 

物理全备

/usr/local/bin/xtrabackup --defaults-file=/etc/3309.cnf --user=root --socket=/tmp/3309.sock --parallel=4 --backup --target-dir=/data0/sql/20171016.bak

export表：还原，加选项--export
xtrabackup --prepare --export --target-dir=/data0/sql/20171016.bak/
# 如果备份时时加密的，那么还原时还需指定密钥文件，选项--keyring-file-data

查看某个表对应的备份文件
find /tmp/20171016.bak/ -name 't1*'
/data0/sql/20171016.bak/test/t1.frm
/data0/sql/20171016.bak/test/t1.cfg
/data0/sql/20171016.bak/test/t1.exp
/data0/sql/20171016.bak/test/t1.ibd
import 表

复制代码
# 删除ibd文件
ALTER TABLE test.t1 DISCARD TABLESPACE;
# .cfg文件可以不拷贝并重新授权
cp /data0/sql/20171016.bak/test/t1.* /data0/mysql/3309_test/test/ 

ll /data0/mysql/3309_test/test/t1.*  
-rw-r----- 1 mysql mysql  16384 Oct 16 23:11 /data0/mysql/3309_test/test/t1.exp
-rw-r----- 1 mysql mysql   8676 Oct 16 23:11 /data0/mysql/3309_test/test/t1.frm
-rw-r----- 1 mysql mysql 163840 Oct 16 23:12 /data0/mysql/3309_test/test/t1.ibd

# 导入
 ALTER TABLE test.t1 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.11 sec)
复制代码

###########操作期间遇到的报错###################

ALTER TABLE test.t1 IMPORT TABLESPACE;          
ERROR 1812 (HY000): Tablespace is missing for table `test`.`t1`.
# 是未拷贝ibd文件导致
 ALTER TABLE test.t1 IMPORT TABLESPACE;
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table `test`.`t1` : Tablespace not found
# 是文件未授权导致

这里感谢西橙这位作者的分享如有侵权问题请联系我立马删除