mysql xtrabackup 部分复制

---

mysql xtrabackup 部分复制

 

官方文档：https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/partial_backups.html#pxb-partial-backup

 

 

************************

部分复制

 

实现方式

# 开启参数innodb_file_per_table后，可进行部分复制
xtrabackup supports taking partial backups when 
the innodb_file_per_table option is enabled. 

# 有三种实现方式
There are three ways to create partial backups:
matching the tables names with a regular expression  #正则表达式匹配表的名称
providing a list of table names in a file            #在文件中写需要复制的表的名称
providing a list of databases                        #在文件中写需要复制的数据库的名称

 

innodb_file_per_table 参数说明

# 默认情况下，所有的表、索引信息都存储在同一个系统表空间内的文件中
By default, all InnoDB tables and indexes are stored in the system tablespace on one file. 

# 使用该参数后，服务器会为每个表单独创建一个表空间文件
This option causes the server to create one tablespace file per table. To enable it, set it 
on your configuration file,

配置方式
[mysqld]
innodb_file_per_table

或者在启动服务器是添加参数：--innodb_file_per_table


# innodb_file_per_table 默认开启
mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

 

数据恢复时不要直接使用 --copy-back

Do not copy back the prepared backup.

# 恢复部分复制数据应该导入表数据，不应该使用--copy-back参数
# 不建议在部分复制后进行增量复制
Restoring partial backups should be done by importing the tables, not by using the --copy-
back option. It is not recommended to run incremental backups after running a partial backup.

# 尽管有些场景可以使用copy-back进行数据恢复，但这可能会导致数据不一致，不建议这么做
Although there are some scenarios where restoring can be done by copying back the files, 
this may lead to database inconsistencies in many cases and it is not a recommended way to do it

 

 

************************

相关参数

--tables：匹配需要复制的表，正则表达式

--databases="^db*"：匹配名称以db开头的数据库

--tables="^databasename[.].*"：匹配databasename下的所有表
--tables="databasename[.]test"：匹配databasename中的表test

--tables-file：文件中指定表的名称

# 一个文件中可包含多个表名，一行一个
The --tables-file option specifies a file that can contain multiple table names, one table 
name per line in the file. 

# 只有在文件中的表会被复制
Only the tables named in the file will be backed up. 

# 名称精确匹配，不使用正则表达式，区分大小写，格式为：databasename.tablename
Names are matched exactly, case-sensitive, with no pattern or regular expression matching. 
The table names must be fully qualified, in databasename.tablename format


# 示例
echo "mydatabase.mytable" > /tmp/tables.txt
xtrabackup --backup --tables-file=/tmp/tables.txt

 

--databases：匹配需要复制的数据库

# 多个数据库之间空格分隔，格式为：databasename[.tablename]
xtrabackup --databases accepts a space-separated list of the databases and tables to backup 
in the format databasename[.tablename]. 

# 如果使用--copy-back恢复数据，除了需要复制的数据库外，还需要添加数据库 mysql、sys、performance_schema
In addition to this list make sure to specify the mysql, sys, and performance_schema databases. 
These databases are required when restoring the databases using xtrabackup --copy-back


# 示例
xtrabackup --databases='mysql sys performance_schema ...'

 

--databases-file：文件中指定数据库的名称

# 文件中可包含多个数据库，每行一个，格式为：databasename[.tablename]
xtrabackup --databases-file specifies a file that can contain multiple databases and tables 
in the databasename[.tablename] form, one element name per line in the file. 

# 名称精确匹配，大小写敏感，不为通配符或者正则表达式
Names are matched exactly, case-sensitive, with no pattern or regular expression matching

 

--export：准备阶段新建文件用于导入另一个数据库中

create files to import to another database when prepare.

 

 

************************

示例

 

创建 mysql 容器

docker run -it  -d --net fixed --ip 172.18.0.3 -p 3306:3306 \
-v /usr/mysql/single/data:/var/lib/mysql \
--privileged=true -e  MYSQL_ROOT_PASSWORD=123456 --name mysql mysql


数据库     表
test       person、student
test2      person、student

 

备份数据

# --tables="^test.*[.].*"

xtrabackup -u root --password=123456 -H 192.168.57.120 -P 3306 --backup \
--log-bin=/usr/mysql/single/data/binlog --log-bin-index=/usr/mysql/single/data/binlog.index \
--tables="^test.*[.].*" --datadir=/usr/mysql/single/data --target-dir=/usr/mysql/single/backup

备份目录
[root@centos backup]# ls
backup-my.cnf  binlog.index    ibdata1    test   undo_001  xtrabackup_binlog_info  xtrabackup_info     xtrabackup_tablespaces
binlog.000015  ib_buffer_pool  mysql.ibd  test2  undo_002  xtrabackup_checkpoints  xtrabackup_logfile
[root@centos backup]# ls test
person.ibd  student.ibd
[root@centos backup]# ls test2
person.ibd  student.ibd


*********************
# --databases="^test.*"

xtrabackup -u root --password=123456 -H 192.168.57.120 -P 3306 --backup \
--log-bin=/usr/mysql/single/data/binlog --log-bin-index=/usr/mysql/single/data/binlog.index \
--databases="^test.*" --datadir=/usr/mysql/single/data --target-dir=/usr/mysql/single/backup

备份目录
[root@centos backup]# ls
backup-my.cnf  binlog.index    ibdata1    undo_001  xtrabackup_binlog_info  xtrabackup_info     xtrabackup_tablespaces
binlog.000018  ib_buffer_pool  mysql.ibd  undo_002  xtrabackup_checkpoints  xtrabackup_logfile
没有复制数据库，--databases不支持正则表达式


*********************
# --databases="test test2.person"

xtrabackup -u root --password=123456 -H 192.168.57.120 -P 3306 --backup \
--log-bin=/usr/mysql/single/data/binlog --log-bin-index=/usr/mysql/single/data/binlog.index \
--databases="test test2.student" --datadir=/usr/mysql/single/data --target-dir=/usr/mysql/single/backup

备份目录
[root@centos backup]# ls
backup-my.cnf  binlog.index    ibdata1    test   undo_001  xtrabackup_binlog_info  xtrabackup_info     xtrabackup_tablespaces
binlog.000019  ib_buffer_pool  mysql.ibd  test2  undo_002  xtrabackup_checkpoints  xtrabackup_logfile
[root@centos backup]# ls test
person.ibd  student.ibd
[root@centos backup]# ls test2
person.ibd

 

prepare 数据

xtrabackup --prepare --export --target-dir=/usr/mysql/single/backup


备份目录prepare前
[root@centos backup]# ls
backup-my.cnf  binlog.index    ibdata1    test   undo_001  xtrabackup_binlog_info  xtrabackup_info     xtrabackup_tablespaces
binlog.000024  ib_buffer_pool  mysql.ibd  test2  undo_002  xtrabackup_checkpoints  xtrabackup_logfile
[root@centos backup]# ls test
person.ibd  student.ibd
[root@centos backup]# ls test2
student.ibd

备份目录prepare后
[root@centos backup]# ls
backup-my.cnf  ib_buffer_pool  ib_logfile1   mysql.ibd  undo_001                xtrabackup_checkpoints  xtrabackup_master_key_id
binlog.000024  ibdata1         ibtmp1        test       undo_002                xtrabackup_info         xtrabackup_tablespaces
binlog.index   ib_logfile0     #innodb_temp  test2      xtrabackup_binlog_info  xtrabackup_logfile
[root@centos backup]# ls test
person.cfg  person.ibd  student.cfg  student.ibd
[root@centos backup]# ls test2
student.cfg  student.ibd

 

restore 数据

# 创建 mysql2
docker run -it  -d --net fixed --ip 172.18.0.4 -p 3307:3306 \
-v /usr/mysql/single/data2:/var/lib/mysql \
--privileged=true -e  MYSQL_ROOT_PASSWORD=123456 --name mysql2 mysql

# 创建数据库test2，建立相同数据结构的表person，查看数据：
mysql> use test2;
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> select * from person;
Empty set (0.00 sec)


# mysql2 删除表空间
mysql> alter table person discard tablespace;
Query OK, 0 rows affected (0.00 sec)


# 复制备份目录文件到表空间test2
cp test/* /usr/mysql/single/data2/test2


# 进入mysql2，执行命令：chown -R mysql:mysql /var/lib/mysql/test2，修改权限

# mysql2 命令行导入数据空间
mysql> alter table person import tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from person;
+----+-------+
| id | value |
+----+-------+
|  1 |     2 |
|  3 |     3 |
+----+-------+
2 rows in set (0.00 sec)

 

报错说明

# 表空间下没有对应的.idb文件
mysql> alter table person import tablespace;
ERROR 1812 (HY000): Tablespace is missing for table `test2`.`person`.

# 表空间数据文件没有权限
mysql> alter table person import tablespace;
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table `test2`.`person` : Tablespace not found