MySQL Backup
摘要
一、备份基础
二、备份工具
2.1 mysqldump
2.2 xtrabackup
一、备份基础
为什么需要备份？由于灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击；误操作等等原因，我们需要数据恢复。
有时候为了测试，我们可以从服务器copy一份数据下来。
备份时要注意什么？备份中总有可能数据丢失，我们能够容忍丢失多少数据，恢复数据的时长多少？需要恢复什么样的数据？都是我们需要考虑的
持锁的时长
备份过程时长
备份负载
恢复过程时长
因此，需要：
（1） 做还原测试，测试备份的可用性
（2） 还原演练，整个流程的演练
备份类型：从多个角度来看待备份
完全备份和部分备份：部分备份只备份只备份数据子集。
全量备份、增量备份：增量备份只备份上次备份之后变化的数据部分，即增量
完全备份和差异备份：差异备份只备份差异，差异备份比增量备份更浪费空间，但是更方便还原操作
热备份、温备份、冷备份：MyISAM可以做温备，InnoDB可以做热备
热备：在线备份，读写操作都不受影响；
温备：在线备份，读操作可以继续进行，但写操作不允许；
冷备：离线备份，数据库服务器离线，备份期间不能为业务提供读写服务
物理备份和逻辑备份：
物理备份：直接复制数据文件进行的备份，只需要文件复制工具即可，速度快，与存储引擎相关，热备困难
逻辑备份：直接从数据库导出数据并且另存，需要基于MySQL协议的专用工具，速度慢，与存储引擎无关、热备相对容易
备份内容：
数据、额外数据(比如二进制日志文件和InnoDB的事务日志)、代码(存储过程以及函数等等...)、服务器配置文件等等
常用备份工具：
mysqldump：逻辑备份工具，适用于所有存储引擎，温备；支持完全备份，部分备份；对InnoDB存储引擎支持热备
cp,tar...：物理备份，只能冷备
lvm2的快照：几乎热备；借助于文件系统工具实现物理备份
mysqlhotcopy：几乎冷备；仅适用于MyISAM存储引擎；
备份工具的选择：
mysqldump and copy binlog：较小的数据集
mysqldump: 完全备份
复制binlog中指定时间范围的event进行增量备份
lvm2快照+复制binlog:
lvm2快照: 使用cp或者tar等做物理的完全备份
复制binlog中指定时间范围的event进行增量备份
xtrabackup: 开源工具
由Percona提供的支持对InnoDB做热备(物理备份)的工具；
支持完全备份，增量备份
二、备份工具
2.1 mysqldump
#Dumping structure and contents of MySQL databases and tables. Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS]
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.-l, --lock-tables Lock all tables for read.
--single-transaction 只支持InnoDB，热备方案
--events, -E 备份指定数据库相关的所有event scheduler
-R, --routines Dump stored routines (functions and procedures).
--triggers Dump triggers for each dumped table.
--ignore-table=name Do not dump the specified table. To specify more than one table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both database and table names, e.g., --ignore-table=database.table.--master-data[=#]
1: 记录为CHANGE MASTER TO 语句，此语句不被注释；
2：记录为注释的CHANGE MASTER TO 语句；--flush-logs:
锁定表之后，执行flush logs命令，二进制文件进行滚动
演示：备份基础操作
准备：随意准备一个数据库，这里用的MySQL官方提供的employees.sql
mysql> set sql_log_bin=OFF #关闭会话级别的二进制日志
mysqldump -uroot -p111111 -B employees --single-transaction --master-data=2 >/root/employees.sql delete from employees where emp_no=10001; less employees.sql #发现有一行注释CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=168405641; mysqlbinlog --start-position=168405641 /mydata/data/mysql-bin.000004 > incre.sql # 假设此时MySQL中只有这一个库，而且数据库文件employees.sql和二进制日志文件都没有损坏 # 在另外的虚拟机上 使用employees.sql和incre.sql就可以还原
mysqldump适合数据量很小的数据库，备份简单，还原速度慢。
注意：二进制日志文件不应该与数据文件放在同一磁盘；一般都要用RAID10进行数据冗余。InnoDB的事务日志也应该放在RAID10上
2.2 xtrabackup
percona: MySQL领域最专业的第三方机构 ，网址是www.percona.com，在MariaDB上InnoDB实际上用的就是Percona的XtraDB
安装： 官网下载即可
1. 简介
Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：
(1)备份过程快速、可靠；
(2)备份过程不会打断正在执行的事务；
(3)能够基于压缩等功能节约磁盘空间和流量；
(4)自动实现备份检验；
(5)还原速度快；
2. 备份的实现
(1) 完全备份
# innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：
mysql> CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’s3cret’; mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’; mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’; mysql> FLUSH PRIVILEGES;
使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中。
在备份的同时，innobackupex还会在备份目录中创建如下文件：
- xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
- 每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
- xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
- xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
- xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；
- backup-my.cnf —— 备份命令用到的配置选项信息；
(2) 准备(prepare)一个完全备份
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
innobakupex命令的--apply-log选项可用于实现上述功能。如下面的命令：
# innobackupex --apply-log /path/to/BACKUP-DIR
如果执行正确，其最后输出的几行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120407 9:01:36 InnoDB: Starting shutdown...
120407 9:01:40 InnoDB: Shutdown completed; log sequence number 92036620
120407 09:01:40 innobackupex: completed OK!
在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。
(3) 从一个完全备份中恢复数据
注意：恢复不用启动MySQL
innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。
# innobackupex --copy-back /path/to/BACKUP-DIR
如果执行正确，其输出信息的最后几行通常如下：
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2012-04-07_08-17-03'
innobackupex: back to original InnoDB log directory '/mydata/data'
innobackupex: Finished copying back files.
120407 09:36:10 innobackupex: completed OK!
请确保如上信息的最行一行出现“innobackupex: completed OK!”。
当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，在启动mysqld之前还需要事先修改数据文件的属主和属组。如：
# chown -R mysql:mysql /mydata/data/
(4) 使用innobackupex进行增量备份
每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。
要实现第一次增量备份，可以使用下面的命令进行：
# innobackupex --incremental /backup --incremental-basedir=BASEDIR
其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。
需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。
“准备”(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是：
需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。
基于所有的备份将未提交的事务进行“回滚”。
于是，操作就变成了：
# innobackupex --apply-log --redo-only BASE-DIR
接着执行：
# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
而后是第二个增量：
# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
其中BASE-DIR指的是完全备份所在的目录，而INCREMENTAL-DIR-1指的是第一次增量备份的目录，INCREMENTAL-DIR-2指的是第二次增量备份的目录，其它依次类推，即如果有多次增量备份，每一次都要执行如上操作；
(5) Xtrabackup的“流”及“备份压缩”功能
Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用--stream选项即可。如：
# innobackupex --stream=tar /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz
甚至也可以使用类似如下命令将数据备份至其它服务器：
# innobackupex --stream=tar /backup | ssh user@www.magedu.com "cat - > /backups/`date +%F_%H-%M-%S`.tar"
此外，在执行本地备份时，还可以使用--parallel选项对多个文件进行并行复制。此选项用于指定在复制时启动的线程数目。当然，在实际进行备份时要利用此功能的便利性，也需要启用innodb_file_per_table选项或共享的表空间通过innodb_data_file_path选项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。其简单使用方法如下：
# innobackupex --parallel /path/to/backup
同时，innobackupex备份的数据文件也可以存储至远程主机，这可以使用--remote-host选项来实现：
# innobackupex --remote-host=root@www.magedu.com /path/IN/REMOTE/HOST/to/backup
(6) 导入或导出单张表
默认情况下，InnoDB表不能通过直接复制表文件的方式在mysql服务器之间进行移植，即便使用了innodb_file_per_table选项。而使用Xtrabackup工具可以实现此种功能，不过，此时需要“导出”表的mysql服务器启用了innodb_file_per_table选项（严格来说，是要“导出”的表在其创建之前，mysql服务器就启用了innodb_file_per_table选项），并且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import选项。
“导出”表
导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过--export选项将某表导出了：
# innobackupex --apply-log --export /path/to/backup
此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器。
“导入”表
要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：
mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;
然后将此表的表空间删除：
mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
接下来，将来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”：
mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;
(7) 使用Xtrabackup对数据库进行部分备份
Xtrabackup也可以实现部分备份，即只备份某个或某些指定的数据库或某数据库中的某个或某些表。但要使用此功能，必须启用innodb_file_per_table选项，即每张表保存为一个独立的文件。同时，其也不支持--stream选项，即不支持将数据通过管道传输给其它程序进行处理。
此外，还原部分备份跟还原全部数据的备份也有所不同，即你不能通过简单地将prepared的部分备份使用--copy-back选项直接复制回数据目录，而是要通过导入表的方向来实现还原。当然，有些情况下，部分备份也可以直接通过--copy-back进行还原，但这种方式还原而来的数据多数会产生数据不一致的问题，因此，无论如何不推荐使用这种方式。
- 创建部分备份:
创建部分备份的方式有三种：正则表达式(--include), 枚举表文件(--tables-file)和列出要备份的数据库(--databases)。
(a)使用--include
使用--include时，要求为其指定要备份的表的完整名称，即形如databasename.tablename，如：
# innobackupex --include='^mageedu[.]tb1' /path/to/backup
(b)使用--tables-file
此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称；如：
# echo -e 'mageedu.tb1\nmageedu.tb2' > /tmp/tables.txt
# innobackupex --tables-file=/tmp/tables.txt /path/to/backup
(c)使用--databases
此选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；同时，在指定某数据库时，也可以只指定其中的某张表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。如：
# innobackupex --databases="mageedu testdb" /path/to/backup
- 整理(preparing)部分备份:
prepare部分备份的过程类似于导出表的过程，要使用--export选项进行：
# innobackupex --apply-log --export /pat/to/partial/backup
此命令执行过程中，innobackupex会调用xtrabackup命令从数据字典中移除缺失的表，因此，会显示出许多关于“表不存在”类的警告信息。同时，也会显示出为备份文件中存在的表创建.exp文件的相关信息。
- 还原部分备份:
还原部分备份的过程跟导入表的过程相同。当然，也可以通过直接复制prepared状态的备份直接至数据目录中实现还原，不要此时要求数据目录处于一致状态。
浙公网安备 33010602011771号