MySQL备份与恢复

MySQL的备份恢复方法(或者叫工具)一般分为以下四种：

1. mysqldump
2. mysqlbackup
3. mysqlhotcopy
4. xtrabackup/innobackupex

mysqldump

在日常工作中，我们会使用mysqldump命令创建sql格式的转储文件来备份数据库。或者我们把数据导出后做数据迁移，主备搭建等操作。mysqldump是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。 默认情况下,它会生成insert语句，也能生成其它分隔符的输出或XML格式的文件。

shell> mysqldump [arguments] > file_name

备份所有的数据库：

shell> mysqldump --all-databases > dump.sql 

(不包含INFORMATION_SCHEMA,performance_schema,sys，如果想要导出的话还要结合–skip-lock-tables和–database一起用)
备份指定的数据库：

shell> mysqldump --databases db1 db2 db3 > dump.sql

当我们只备份一个数据的时候可以省去 –databases 直接写成：

mysqldump test > dump.sql  

不过有一些细微的差别，如果不加的话，数据库转储输出不包含创建数据库和use语句，所以可以不加这个参数直接导入到其它名字的数据库里
当然我们也可以只备份某个表 ：

mysqldump --user [username] --password=[password] [database name] [table name]  table_name.sql  

重要的几个参数：
–master-data 获取备份数据的Binlog位置和Binlog文件名，用于通过备份恢复的实例之间建立复制关系时使用，该参数会默认开启。
–dump-slave 用于在slave上dump数据，建立新的slave。因为我们在使用mysqldump时会锁表，所以大多数情况下，我们的导出操作一般会在只读备库上做，为了获取主库的Relay_Master_Log_File和Exec_Master_Log_Pos，需要用到这个参数，不过这个参数只有在5.7以后的才会有
–no-data, -d 不导出任何数据，只导出数据库表结构

在使用mysqldump的时候会锁表，我们来详细的看一下它的锁机制。
我们开两个窗口，在第一个里面执行：

mysqldump -uroot -pxxxxx  --master-data=2 --databases dbname  > /tmp/dbname`date +%F`.sql

然后第二个窗口登陆进去，使用show process的命令可以看到目前dump的session正在执行。

SELECT /*!40001 SQL_NO_CACHE */ * FROM table_name; 

可以看到这条sql正在以no_cache的模式查询数据。然后我们在同样的表上执行一下select，发现被阻塞了。光标一直不返回。
我们把具体的general_log打开，然后看一下当时的操作：

Query FLUSH /*!40101 LOCAL */ TABLES
Query FLUSH TABLES WITH READ LOCK  

关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行unlock tables，该操作常常用于数据备份的时候。

Query SHOW MASTER STATUS

（这是因为我用了–master-data=2）
如果我不加–master-data参数：

mysqldump -uroot -pxx --databases db > /tmp/dbname`date +%F`.sql) 

mysql会显示的对每一张要备份的表执行

LOCK TABLES `table_name1` READ,LOCK TABLES `table_name2` READ

并且也不会有读的阻塞。
使用–single-transaction把备份的操作放在一个事务里去进行，使用这种方法可以实现对表在不加锁的情况下进行备份。
mysqldump的优势是可以查看或者编辑十分方便，它也可以灵活性的恢复之前的数据。它也不关心底层的存储引擎，既适用于支持事务的，也适用于不支持事务的表。
不过它不能作为一个快速备份大量的数据或可伸缩的解决方案。如果数据库过大,即使备份步骤需要的时间不算太久,但有可能恢复数据的速度也会非常慢,因为它涉及的SQL语句插入磁盘I/O,创建索引等等。
对于大规模的备份和恢复,更合适的做法是物理备份,复制其原始格式的数据文件。

mysqlbackup

mysqlbackup是ORACLE公司提供的针对企业的备份软件，全名叫做MySQL Enterprise Backup，是一个收费的软件。可以试用下载。 下载地址：https://www.mysql.com/products/enterprise/backup.html 我们简单的来看一下这个工具的使用。
全量备份的语法

mysqlbackup --user=root --password=ucjmh --databases='t1' --encrypt-password=1 --with-timestamp  --backup-dir=/u01/backup/ backup

其中：
解释一下参数：
–databases 要备份的数据库
–with-timestamp 产生一个当前时间的备份目录。mysqlbackup这个工具要求一个空目录才能做备份。所以这个会常用
–backup-dir 备份的目录
–compress：压缩备份 这个提供了多种压缩方法和压缩级别。1–9,压缩比依次递增
backup 是备份的方式，
一共有如下几种方式：

Backup operations: backup, backup-and-apply-log, backup-to-image

Update operations: apply-log, apply-incremental-backup

Restore operations: copy-back, copy-back-and-apply-log

Validation operation: validate

Single-file backup operations: image-to-backup-dir, backup-dir-to-image, list-image, extract

在大多数情况下,单个文件备份,使用backup-to-image命令创建,性能优于backup。buckup这个命令只执行一个完整的备份过程的初始阶段。需要通过再次运行mysqlbackup运用apply-log 命令,使备份一致。
命令如下：

mysqlbackup --user=root --password=ucjmh --databases='t1' --encrypt-password=1 --with-timestamp  --backup-dir=/u01/backup/2017-04-28_12-49-35/ apply-log

当然你可以直接用backup-and-apply-log 不过这个时候的备份将不能用于增量了。
我们再来看一下增量备份的语法：

mysqlbackup --user=root --password=ucjmh --databases='t1' --encrypt-password=1 --with-timestamp  --backup-dir=/u01/backup/  --incremental --incremental-base=dir:/u01/backup/2017-04-28_12-49-35 --incremental-backup-dir=/u01/backup/incremental backup

这个是基于上次的备份做的备份，当然也可以基于某一个log position之后做。
参数说明：
解释一下参数：
–incremental：代表增量备份；
–incremental-base：上次全备的目录；
–incremental-backup-dir：增量备份的保存的目录

关于image的备份:
使用如下命令可以进行备份

mysqlbackup --user=root --password=ucjmh --databases='t1' --encrypt-password=1 --with-timestamp  --backup-dir=/u01/backup/  --backup-image=all.mbi backup-to-image

备份之后可以很清楚的发现这个比backup要节省很多空间，把所有的文件都以二进制的方式放在了all.mbi这个文件里，可以使用list-image来查看具体内容。
大致的恢复原理：
首先检测并应用全备事务日志文件（这里是因为我备份的时候用的是backup而不是backup-and-apply-log），然后基于全备去应用增量的log。这个时候如果有多次增量备份也可以（基于LSN点向后应用）。 所有的都应用完成之后就是一个可以直接cp到数据库了。

mysqlhotcopy

mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,但只能用于备份MyISAM存储引擎和ARCHIVE引擎，并且是一个服务器命令，只能运行在数据库目录所在的机器上.与mysqldump备份不同,mysqldump属于逻辑备份,备份时是执行的sql语句.使用mysqlhotcopy命令前需要要安装相应的软件依赖包.
大致用法：
备份一个库

mysqlhotcopy db_name [/path/to/new_directory]

备份一张表

mysqlhotcopy db_name./table_name/ /path/to/new_directory

更详细的使用可以使用perldoc mysqlhotcopy查看

xtrabackup/innobackupex

1. xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表

2. innobackupex则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁

   xtrabackup基于innodb的crash-recovery（实例恢复）功能，先copy innodb的物理文件（这个时候数据的一致性是无法满足的），然后进行基于redo log进行恢复，达到数据的一致性。
   全备：

xtrabackup --backup --target-dir=/data/backup/base

在备份过程中,可以看到很多输出显示数据文件被复制,以及日志文件线程反复扫描日志文件和复制。同样的它也输出了当前的binlog filename和position，如果有gtid(同样也会输出) 可以用于搭建主从。最后一行一定会是你的lsn被copy的信息。这是因为每次启动备份，都会记录一个位置，然后开始拷贝文件，一般来讲数据库越大拷贝文件是要花费越长的时间，所以说这期间一般情况都会有新的操作，所以说所有文件也可能记录的并不是一个时间点的数据，为了解决数据这个问题，XtraBackup 就会启动一个后台进程来每秒1次的观测mysql的事务日志，直到备份结束。而且把事务日志中的改变记录下来。我们知道事物日志是会重用的（redo log）,所以这个进程会把redolog写到自己的日志文件xtrabackup_log，这个后台监控进程会记录所有的事务日志的改变，用于保证数据一致性。
增量备份：
当我们做过全量备份以后会在目录下产生xtrabackup_checkpoints的文件 这里面记录了lsn和备份方式，我们可以基于这次的全量做增量的备份。

xtrabackup --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base

这个时候xtrabackup也是去打开了xtrabackup_checkpoints文件进行上一次备份的信息查看。这时去查看增量备份的xtrabackup_checkpoints也记录了这些信息。这也意味着你可以在增量的备份上继续增量的备份。
同样的xtrabackup也支持压缩（–compress）、加密（–encrypt）、并行(–parallel)等操作，但是和mysqlbackup不同的是这个没有同时的备份binlog,而mysqlbackup是备份了binlog的。

最后：
MySQL有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法。不过在此之前，要先将服务器停止，这样才可以保证在复制期间数据库的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。这种情况在开发环境可以，但是在生产环境中很难允许备份服务器。