mysql xtrabackup 部分复制

不念不忘少年蓝@ 2022-10-06 06:52 80阅读 0赞

--------------------

### mysql xtrabackup 部分复制 ###

**官方文档：[https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup\_bin/partial\_backups.html\#pxb-partial-backup][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

[https_www.percona.com_doc_percona-xtrabackup_LATEST_xtrabackup_bin_partial_backups.html_pxb-partial-backup]: https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/partial_backups.html#pxb-partial-backup