mysql 物理机配置 mysql物理备份工具

转载

智慧编织者 2023-12-20 16:37:39

文章标签 mysql 物理机配置 mysql 数据增量备份 文章分类 MySQL 数据库

------物理备份工具Xtrabackup------

官方Manual：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

innobackupex主要用于热备采用InnoDB、MyISAM等引擎存储的数据。

注：2.3版本开始，运行xtrabackup或innobackupex均能提供相同的功能，并且innobackupex将会在之后的某个大版本中移除。依据个人使用习惯，本文依旧称之为innobackupex。

一、innobackupex全备原理

1、启用文件：xtrabackup_logfile。用于在整个热备过程中，InnoDB存储引擎下新的DML操作产生数据变更时，可以在xtrabackup_logfile中实时记录这些新的数据变化，记录格式同redo log

2、以page为单位复制InnoDB存储的数据文件，包括了共享表空间ibdata1和.ibd文件。由于复制时page可能正在被写入，page的头、尾checksum值将会不同。故之后生成备份文件在使用前先需要apply log，修复部分不完整的page。

3、flush tables with read lock。对MyISAM表加读锁，用于复制非事务引擎MyISAM存储的数据

4、复制 .frm、.MYD、.MYI文件。

5、获取备份完成那一刻，binlog走到的最新位置点：xtrabackup_binlog_info（InnoDB数据文件可能有更新）。

6、unlock tables;

7、将备份信息汇总到各自文件中

（1）备份结束，记录启动备份所需要的最小参数到backup-my.cnf

（2）记录LSN到xtrabackup_logfile。

（3）记录备份类型（full-backuped：全量、incremental：增量；已经apply log过的备份将会修改为full-prepared）等信息到xtrabackup_checkpoints。

（4）记录其他一些备份信息：xtrabackup_info

二、备份文件释疑

下面整理一下除了拷贝数据目录下的库表数据、表空间文件（ibdata）、redo log（ib_logfile）之外，全备生成的文件：

1、backup-my.cnf

包含备份所需的部分my.cnf配置信息（例如，innodb_data_file_path、innodb_log_files_in_group、innodb_log_file_size、innodb_fast_checksum、innodb_page_size、innodb_log_block_size）

mysql 物理机配置 mysql物理备份工具_mysql 物理机配置

2、xtrabackup_binlog_info：备份结束时的binlog位点和GTID信息。夹杂MyISAM进行数据备份时，较xtrabackup_binlog_pos_innodb更准缺

mysql 物理机配置 mysql物理备份工具_mysql_02

3、xtrabackup_binlog_pos_innodb：apply log之后新生成的文件，只记录innodb事务的binlog位点，而不会计算MyISAM产生的binlog

mysql 物理机配置 mysql物理备份工具_增量备份_03

4、xtrabackup_checkpoints

mysql 物理机配置 mysql物理备份工具_数据_04

5、xtrabackup_info

mysql 物理机配置 mysql物理备份工具_数据_05

6、xtrabackup_logfile（核心文件）

包含了redo，--apply-log时，会使用到

7、xtrabackup_slave_info：若需要在备份时加上--slave-info选项，将在该文件中记录 “change master to ...” 信息。实际，也可以从 xtrabackup_info 或 xtrabackup_binlog_info 中直接读取相关信息，不是很有必要。

三、innobackupex增量备份原理

innobackupex在增量备份InnoDB表数据的时候，相较于全备的过程，增备在复制page的时候，将会对比备份文件与当前数据的page的LSN，有变更的数据相关的page，其LSN就会增长。所以innobackupex只需要备份LSN有变化的page。

而备份MyISAM时，仍然执行的是全备的操作。

四、全备与恢复操作示例

备份账户需要的权限：RELOAD、LOCK TABLES、REPLICATION CLIENT

1、全备：

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=backup --password='backup_PW' --host=【HOST】--port=【PORT】(或使用 --socket=XXX.sock登录) --no-timestamp /path/to/backup

注：备份目录会由innobackupex创建，无需事先手工创建，配合--no-timestamp选项可以自由设定目录名称，便于备份的监控与管理。

2、恢复：

1、恢复前关闭实例

2、备份并清空原有数据目录（redo log、undo log分立出去的话也需要备份）

3、innobackupex --apply-log，目的是从xtrabackup_log获取redo日志，更新部分不完整的page，使头尾checksum值，而LSN更新到备份过程中最新的LSN号；

innobackupex --defaults-file=/$innobackup_dir/backup-my.cnf --apply-log /path/to/backup

4、直接拷贝prepared过的备份文件到data目录下，或使用--copy-back选项指定defaults-file依据my.cnf配置的路径自动放置

innobackupex --defaults-file=/usr/local/mysql/my.cnf --copy-back /path/to/backup

5、修改数据目录权限，并启动。

五、部分备份（Partial Backups）与恢复操作示例

**仅支持开启innodb_file_per_table后，拥有独立表空间的表**

(The source doesn’t have to be XtraDB or or MySQL 5.6, but the destination does)

1、备份过程：

（方法1）备份指定单表。需要在全备语句的基础上，使用--include配合正则表达式。以表mydatabase.mytable为例：

innobackupex --include='^mydatabase[.]mytable' /path/to/backup

（方法2）备份多个表。需要在全备语句的基础上，使用--tables-file配合一份记录完整表名的txt文件（每行一个表名，格式为mydatabase.mytable）

innobackupex --tables-file=/tmp/tables.txt /path/to/backup

（方法3）混合备份多个库、表。需要在全备语句的基础上，使用--databases指定库、表（例如备份表：mydatabase.mytable和库：mysql）

innobackupex --databases="mydatabase.mytable mysql" /path/to/backup

2、恢复过程

（1）prepare部分备份：

innobackupex --apply-log --export /path/to/backup/

（备份过程中未指定的库表，在prepare阶段会提示“does note exist”，可以忽略此信息）

（2）restore到数据目录：

不能直接使用--copy-back，而应该利用5.6引入的Transportable Tablespace特性，避免出现数据不一致的情况。操作流程如下：

【step1】在将表导入到目的DB之前，先要在目的DB上创建与源DB上相同的表结构

可以利用mysql-utilities工具集中的 mysqlfrm --diagnostic 从备份文件XXX.frm中读取表结构

mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;（5.6迁移至5.7需在建表时添加：row_format=compact）

【step2】再将目的DB上该表的表空间discard掉

mysql> set FOREIGN_KEY_CHECKS=0;

mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

【step3】复制备份目录下的mytable.ibd、mytable.exp、mytable.cfg到目的DB对应路径下，并使用import导入

mysql> set FOREIGN_KEY_CHECKS=0;

mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

mysql> set FOREIGN_KEY_CHECKS=1;

if necessary，在discard之前，lock tables mydatabase.mytable write;最后再unlock tables;

注：恢复过程同样支持，从已有全备中导出单表数据进行恢复

Once a full backup is created, prepare it with the --export option:

$ innobackupex --apply-log --export /path/to/backup

This will create for each InnoDB with its own tablespace a file with .exp extension.The –export option is the magic trick that will help us to get a consistent backup with complete independent ibd files without shutting down the service. The use of –export option runs a recovery process on the backup with innodb_fast_shutdown=0 and therefore merging all the insert buffers.

innodb_fast_shutdown=0（The ibd file must be from a consistent backup with all insert buffer entries merged and have no uncommitted transactions in order to not be dependent of the shared tablespace ibdata）

也支持从线上数据库导单表数据到其他数据库，方法就是 mysql> FLUSH TABLES t FOR EXPORT;后（注意及时unlock tables），拷贝ibd及cfg文件到其他库，再import

六、增量备份与Prepare操作示例

1、全备+N次增量备份（假设已有全备，路径：$FULLBACKUP）

第一次增量备份（基于全备）：innobackupex --incremental $INCREMENTALBACKUP_1 --incremental-basedir=$FULLBACKUP --user=USER --password=PASSWORD

第二次增量备份（基于第一次增量备份）：innobackupex --incremental $INCREMENTALBACKUP_2 --incremental-basedir=NCREMENTALBACKUP_1 --user=USER --password=PASSWORD

（......）

第N次

注：

1、--incremental 和 --incremental-basedir 赋值的形式不同，前者留空格后者是等号，特别留意

2、一般建议基于全备做增备，而不是基于增备再做增备

2、prepare

操作思路：先利用--redo-only，逐个replay已经commit的事务，再统一回滚悬挂事务（ pending transactions）

innobackupex --apply-log --redo-only $FULLBACKUP --use-memory=1G --user=USER --password=PASSWORD

innobackupex --apply-log --redo-only $FULLBACKUP--incremental-dir=$INCREMENTALBACKUP_1 --user=USER --password=PASSWORD

innobackupex --apply-log --redo-only $FULLBACKUP --incremental-dir=$INCREMENTALBACKUP_2 --user=USER --password=PASSWORD

（......）

innobackupex --apply-log--redo-only $FULLBACKUP --incremental-dir=$INCREMENTALBACKUP_N --user=USER --password=PASSWORD

innobackupex --apply-log $FULLBACKUP --user=$USERNAME --password=$PASSWORD

七、流式备份操作示例

--stream=tar | xbstream

区别于常规备份是要拷贝到一个目录下，指定--stream可以实现归档、或传送到远程服务器

1、tar包归档：

仅归档 $ innobackupex --defaults-file=XXX/my.cnf --user=backup --password='backup_PW' --host=【HOST】--port=【PORT】(或使用 --socket=XXX.sock登录) --stream=tar /tmp > backup.tar

归档并压缩 $ innobackupex --defaults-file=XXX/my.cnf --user=backup --password='backup_PW' --host=【HOST】--port=【PORT】(或使用 --socket=XXX.sock登录) --stream=tar /tmp | gzip - > backup.tar.gz

归档并压缩并加密 $ innobackupex --defaults-file=XXX/my.cnf --user=backup --password='backup_PW' --host=【HOST】--port=【PORT】(或使用 --socket=XXX.sock登录) --stream=tar /tmp | gzip - | openssl des3 -salt -k "password" > backup.tar.gz.des3

注：解压tar.gz文件时，需额外多加一个-i参数，即$ tar izxvf XXX.tar.gz。否则只能解压出一个backup-my.cnf

2、传送到远程服务器

$ innobackupex --stream=tar ./ | ssh user@desthost "cat - > /data/backups/backup.tar"

若未打通ssh互信，则需要将上述语句修改为 sshpass -p 'userpassword' ssh user@desthost，

否则备份将一直卡在：

>> log scanned up to (XXXXXXX)

--tmpdir：在stream到远程机器前，暂存的目录位置

流量限速（例如10MB/sec）：$ innobackupex --stream=tar ./ | pv -q -L10m | ssh user@desthost "cat - > /data/backups/backup.tar"

注：该种流量限速方法需要安装pv，也可使用innobackupex自带的 --throttle，但是仅支持InnoDB。此外，也可以采用双网卡。

3、仅--stream=xbstream 支持 --compress 和 --encryption

xtrabackup: error: compressed and encrypted backups are incompatible with the 'tar' streaming format. Use --stream=xbstream instead.

搭配使用：--stream=xbstream --parallel=4 --compress --compress-threads=8 > backup.xbstream（xbstream选项会逐个对表的ibd文件进行compress和stream，故需要innodb-file-per-table参数打开）

注：

（1）关于xbstream文件的解压：xbstream -x < backup.xbstream

（2）同样支持传送到远程服务器及限速，方法同 xbstream=tar

八、其他一些常用参数

--use-memory：指定prepare时可以使用的内存，加快prepare的速度

--parallel：备份并发数（指的是并行拷贝ibd文件的线程数，区别于compress-threads是执行压缩的线程数）。需同时开启innodb_file_per_table或innodb_data_file_path。基于文件级实现，会增加磁盘IO。

--slave-info：在原有复制架构下，需要多加一台从库。直接在主库上生成备份负载过大，可以在某个从库上使用 --slave-info 进行备份，在生成的xtrabackup-slave-info文件中，包含了直接指向主库的 change master 信息。搭配--safe-slave-backup可以实现一致性的“从库克隆”，会导致备份期间从库的sql线程临时关闭，需注意。

--kill-long-queries-timeout=SECONDS及--kill-long-query-type=all|update：--kill-long-queries-timeout为innobackupex在获取FTWRL时的最长等待时间，超出则将block的queries kill掉，默认为0，表示不会主动去kill长查询；--kill-long-query-type具体限定kill的queries的类型，默认为all

--galera-info：会额外创建xtrabackup_galera_info文件，用于PXC架构下，恢复新节点所需的grastate.dat的手工填写。