基于mysqldump 、lvm-snapshot、 xtrabackup的备份

基于mysqldump 、lvm-snapshot、 xtrabackup的备份

本文主要讲述基于mysqldump、逻辑卷的快照和Xtrabackup做数据库的备份与复制及注意事项

做备份的目录目的为做灾难恢复、做审计、测试，要对备份的数据做恢复性测试

根据备份时，数据库服务器是否在线，可以分为

冷备：cold backup

温备：warm backup 全局施加共享锁，只能读，不能写

热备：hot backup

根据备份的数据集，可以分为：

完全备份 full backup

部分备份 partial backup

根据备份时的接口（直接备份数据文件还是通过mysql服务器导出数据）

物理备份：直接复制（归档）数据文件的备份方式

逻辑备份：把数据从库中提取出来保存为文本文件

根据备份时是备份整个数据还是仅备份变化的数据，可以分为

完全备份 full backup

增量备份 incremental backup

差异备份 differential backup 从上一次完全备份开始备份

考虑备份成本：施加锁的时间，备份时长，备份负载

代码：存储过程、存储函数、触发器

OS相关的配置文件，如crontab配置计划及相关脚本

跟复制相关的配置信息：二进制日志文件

mysqldump，单线程，是一个逻辑备份工具，可以对InnoDB热备、MyISAM温备、Aria温备

缺点：备份和恢复过程较慢，很难实现差异或增量备份

mysqldumper：多线程的mysqldump

缺点：很难实现差异或增量备份

基于逻辑卷快照：接近于热备的工具，因为要先请求全局锁，而后创建快照，并在创建完成后释放全局锁，可以使用cp、tar等工具进行物理备份，备份和恢复岁都很快

缺点：很难实现增量备份，并且请求全局锁需要等待一段时间，在繁忙的服务器上尤其如此

备份：select clause into outfile '/path/to/somefile'

恢复：load data infile '/path/to/somefile' into table 'tb_name'

Innobase:商业备份工具，

Xtarbackup: 由Percona提供的开源备份工具。

mysqlhostcopy：几乎冷备

本实验数据目录是放在逻辑卷组上的，目录为/mydata/data ,基于 10.0.10-MariaDB实现，MariaDB的安装可以参考本人Mysql的博客来实现

一、mysqldump

mysqldump仅适用于数据集较小场景

用法：mysqldump [options] [db_name [tbl_name ...]]

主要选项解释：

--all-databases, -A：备份所有数据库

--databases, -B：要备份的数据库，可以同时备份多个，使用空格分隔

--flush-logs, -F：备份前、请求到锁之后滚动日志，要记录下复制时的二进制日志

--flush-privileges：通知数据库重读授权表

--host=host_name, -h host_name：要备份的数据库的主机名，可以基于网络备份

--lock-all-tables, -x：请求锁定所有表之后再备份，对MyISAM，InnoDB,Aria做温备

--single-transaction：能够对InnoDB存储引擎实现热备

-u usename 备份的用户名

-p password 登陆数据库的密码

--events:备份事件调度器代码

--routines：备份存储过程和存储函数

--triggers：备份触发器

--master-date={0|1|2}，0表示不记录，1表示距离为change master 语句，2表示记录为注释的change master语句

备份实例：

本实验mysql是没有密码的，如果有则加-u -p选项

[root@master ~]# mysqldump --databases hellodb --lock-all-tables --flush-logs --master-data=2 >/tmp/db.sql

备份完之后，再修改插入一些数据库数据，

MariaDB [none]>use hellodb MariaDB [hellodb]> insert into classes values (13,'xx',12); Query OK, 1 row affected (0.40 sec)

把数据库全部删除。用DROP命令

MariaDB [(none)]> drop database hellodb

查看/tmp/db.sql，可以看见备份时的二进制日志位置

然后进行二进制日志备份：

[root@master ~]# mysqlbinlog --start-position=367 --stop-position=555 /binlog/mysql-bin000001 > /tmp/db1.sql

恢复数据：打开mysql

先关闭二进制日志，导入完全备份

MariaDB [(none)]> set session sql_log_bin=0 MariaDB [(none)]> source /tmp/db.sql //导入备份文件

然后导入二进制备份：

MariaDB [(none)]> source /tmp/db1.sql //导入备份文件

开启二进制日志：

MariaDB [(none)]> set session sql_log_bin=1

查看数据是否恢复：

注：在实际恢复时最好编辑my.cnf配置文件，添加如下项：

skip-networking //跳过网络功能来恢复数据

二、基于lvm逻辑卷的快照备份

1、事务日志跟数据文件必须在同一个卷上；

2、创建快照卷之前，要请求MySQL的全局锁；在快照创建完成之后释放锁；

3、请求全局锁完成之后，做一次日志滚动；做二进制日志文件及位置标记(手动进行)；

4、日志文件与数据库目录不在同一目录下

备份步骤：

1、请求全局锁，并滚动日志

MariaDB [(none)]> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush logs; Query OK, 0 rows affected (0.06 sec)

2、做二进制日志文件及位置标记(手动进行)；

[root@master ~]# mysql -e "show master status" > logs.txt [root@master ~]# cat logs.txt File Position Binlog_Do_DB Binlog_Ignore_DB master-bin.000003 367

3、创建快照卷

[root@master ~]# lvcreate -L 1G -s -n mydata-snap -p r /dev/myvg/lvl Logical volume "mydata-snap" created

4、释放全局锁

MariaDB [(none)]> unlock tables; Query OK, 0 rows affected (0.00 sec)

5、挂载快照卷并备份

[root@master ~]# mount /dev/myvg/mydata-snap /mnt -o ro [root@master ~]# cp -a /mnt/* /backup/2014-04-13

6、备份完成之后，删除快照卷

[root@master ~]# umount /mnt [root@master ~]# lvremove /dev/myvg/mydata-snap

把数据库目录删掉

恢复：

1、二进制日志保存好；

提取备份之后的所有事件至某sql脚本中；

mysqlbinlog --start-position=367 /binlog/mysql-bin000003 > /tmp/db2.sql

2、还原数据，修改权限及属主属组等，并启动mysql

# cp -a /backup/2014-04-13/* /mydata/data # chown -R mysql.mysql /mydata/data/

3、做即时点还原

MariaDB [(none)]> set session sql_log_bin=0 MariaDB [(none)]> source /tmp/db2.sql MariaDB [(none)]> set session sql_log_bin=1

三、基于Xtrabackup备份与恢复

1、简介

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：

(1)备份过程快速、可靠；

(2)备份过程不会打断正在执行的事务；

(3)能够基于压缩等功能节约磁盘空间和流量；

(4)自动实现备份检验；

(5)还原速度快；

2、安装

wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/RPM/rhel6/x86_64/percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm yum install percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm -y

3.完全备份的实现

如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

MariaDB [(none)]> create user 'backup'@'localhost' identified by '123'; MariaDB [(none)]> revoke all privileges,grant option from 'backup'@'localhost'; MariaDB [(none)]> grant reload,lock tables,replication client on *.* to 'backup'@'localhost'; MariaDB [(none)]> flush privileges;

3.1 完全备份

[root@master ~]# innobackupex --user=backup --password=123 /backup

//如果执行正确，其最后输出的几行信息通常如下： innobackupex: Backup created in directory '/backup/2014-04-13_16-03-47' innobackupex: MySQL binlog position: filename 'mysql-bin.000006', position 1409 140413 16:03:54 innobackupex: Connection to database server closed 140413 16:03:54 innobackupex: completed OK!

3.2、准备(prepare)一个完全备份

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

innobakupex命令的--apply-log选项可用于实现上述功能。如下面的命令：

[root@master ~]# innobackupex --apply-log /backup/2014-04-13_16-25-57 //如果执行正确，其最后输出的几行信息通常如下： xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1739798 140413 16:29:48 innobackupex: completed OK!

3.3、从一个完全备份中恢复数据

注意：恢复不用启动MySQ

innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。

# innobackupex --copy-back /backup/2014-04-13_16-25-57 //如果执行正确，其输出信息的最后几行通常如下： # innobackupex --copy-back /backup/2014-04-13_16-25-57 //如果执行正确，其输出信息的最后几行通常如下： innobackupex: Starting to copy InnoDB log files innobackupex: in '/backup/2014-04-13_16-25-57' innobackupex: back to original InnoDB log directory '/mydata/data' innobackupex: Copying '/backup/2014-04-13_16-25-57/ib_logfile0' to '/mydata/data/ib_logfile0' innobackupex: Copying '/backup/2014-04-13_16-25-57/ib_logfile1' to '/mydata/data/ib_logfile1' innobackupex: Finished copying back files. 140413 16:34:50 innobackupex: completed OK!

请确保如上信息的最行一行出现“innobackupex: completed OK!”。

当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户，如mysql，否则，

在启动mysqld之前还需要事先修改数据文件的属主和属组。如：

# chown -R mysql:mysql /mydata/data/

4、使用innobackupex进行增量备份

每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。

4.1要实现增量备份，可以使用下面的命令进行：

# innobackupex --incremental /backup --incremental-basedir=BASEDIR

其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

[root@station142 ~]# innobackupex --user=backup --password=123 /backup //生成完全备份目录/backup/2014-04-13_17-01-40/ [root@station142 ~]# innobackupex --incremental /backup --incremental-basedir=/backup/2014-04-13_17-01-40/ //生成第1个增量目录/backup/2014-04-13_17-03-30/ [root@station142 ~]# innobackupex --incremental /backup --incremental-basedir=/backup/2014-04-13_17-03-30/ //生成第2个增量目录/backup/2014-04-13_17-09-28

4.2“准备”(prepare)

增量备份与整理完全备份有着一些不同，尤其要注意的是：

(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。

(2)基于所有的备份将未提交的事务进行“回滚”。

于是，操作就变成了：

# 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指的是第二次增量备份的目录，其它依次类推，即如果有多次增量备份，每一次都要执行如上操作；

[root@station142 ~]# innobackupex --apply-log --redo-only /backup/2014-04-13_17-01-40/ [root@station142 ~]# innobackupex --apply-log --redo-only /backup/2014-04-13_17-01-40/ --incremental-dir=/backup/2014-04-13_17-03-30/ [root@station142 ~]# innobackupex --apply-log --redo-only /backup/2014-04-13_17-01-40/ --incremental-dir=/backup/2014-04-13_17-09-28/

4.3备份恢复

[root@station142 data]# innobackupex --copy-back /backup/2014-04-13_17-01-40/ [root@station142 data]# chown -R mysql.mysql /mydata/data/*

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选项。

6.1先完全备份

[root@station142 ~]# innobackupex --user=backup --password=123 /backup

6.2“导出”表

导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过--export选项将某表导出了：

[root@station142 hellodb]# innobackupex --apply-log --export /backup/2014-04-13_18-02-45/

此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器。

6.3“导入”表

要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：

本实验把hellodb.studnets表删掉

MariaDB [hellodb]> drop table students；

创建students表

MariaDB [hellodb]> CREATE TABLE `students` ( `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, `TeacherID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 |

然后将此表的表空间删除

MariaDB [test]> alter table students discard tablespace;

接下来，将来自于“导出”表的服务器的studnets表的studnets.ibd和studnets.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”：

[root@station142 test]# cp students.ibd students.exp /mydata/data/test/ [root@station142 test]# chown mysql.mysql students.exp students.ibd [root@station142 test]#mysql MariaDB [test]> alter table students import tablespace;

恢复students表成功

四、备份策略

1、将数据和备份放在不同的磁盘设备上；异机或异地备份存储较为理想；

2、备份的数据应该周期性地进行还原测试；

3、每次灾难恢复后都应该立即做一次完全备份；

4、针对不同规模或级别的数据量，要定制好备份策略；

5、二进制日志应该跟数据文件在不同磁盘上，并周期性地备份好二进制日志文件；

从备份中恢复应该遵循步骤：

1、停止MySQL服务器；

2、记录服务器的配置和文件权限；

3、将数据从备份移到MySQL数据目录；其执行方式依赖于工具；

4、改变配置和文件权限；

5、以限制访问模式重启服务器；mysqld的--skip-networking选项可跳过网络功能；

方法：编辑my.cnf配置文件，添加如下项：

skip-networking

socket=/tmp/mysql-recovery.sock

6、载入逻辑备份（如果有）；而后检查和重放二进制日志；

7、检查已经还原的数据；

8、重新以完全访问模式重启服务器；

注释前面在my.cnf中添加的选项，并重启；