MySQL学习笔记十二：数据备份与恢复

reference：http://www.cnblogs.com/zmxmumu/p/4437667.html

数据备份

1.物理备份与逻辑备份

物理备份

物理备份就是将数据库的数据文件，配置文件，日志文件等复制一份到其他路径上，这种备份速度一般较快，因为只有I/O操作。进行物理备份时，一般都需要关闭mysql服务器，或者对需要备份的对象进行锁定，要不很容易造成备份的不一致性，恢复时可能会丢失数据。物理备份的方式有很多，如操作系统命令copy(cp)，scp，mysqlbackup，以及MyISAM表的mysqlhotcopy。

逻辑备份

逻辑备份是对数据库的逻辑结构(create database,create table)，以及其所存储的数据(转换为insert into)进行备份。这种备份非常的灵活，但是对于大型系统来讲，逻辑备份进行恢复时性能较低。逻辑备份工具有mysqldump，select...into outfile等。

2.联机备份与脱机备份

联机备份又称热备份，即在mysql服务器运行时进行备份，脱机备份，即冷备份，在mysql服务器停止运行后进行备份。

热备份的优点是不会影响其他客户端访问mysql服务，仍可以正常读取数据。但是在进行热备份时需要发生避免数据的修改操作，以至于影响备份的一致性和完整性。

冷备份的有点是没有客户端的连接，直接进行备份即可。但为了降低对业务的影响，尽量在slave端进行备份。

3.完整备份和增量备份

完整备份是对整个数据库所有的数据进行的备份，而增量备份就是指定时间点后发送的修改，增量备份基于mysql binary log来实现。

4.MyISAM表的物理备份

方法一:先锁表在复制，示例

mysql> flush tables stu with read lock;Query OK, 0 rows affected (0.00 sec)copy "D:\ProgramData\MySQL\MySQL Server 5.5\data\test\t2.*"  f:\backupD:\ProgramData\MySQL\MySQL Server 5.5\data\test\t2.frmD:\ProgramData\MySQL\MySQL Server 5.5\data\test\t2.MYDD:\ProgramData\MySQL\MySQL Server 5.5\data\test\t2.MYI已复制         3 个文件。mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)

方法二：使用mysqlhotcopy命令

Windows平台下使用mysqlhotcopy需要安装perl以及DBD-MYSQL，DBI模块，但是呢，mysqlhotcopy命令也是调用操作系统命令(cp,scp)来复制文件，因此在Windows平台下无法使用，故大家可以在Linux平台上实验。

-----------------------DBD模块的安装--------------------C:\Users\Lenovo>ppm install DBD::mysqlDownloading DBD-mysql-4.029...doneUnpacking DBD-mysql-4.029...doneGenerating HTML for DBD-mysql-4.029...doneUpdating files in site area...done  11 files installed----------------mysqlhotcopy命令使用------------------C:\Users\Lenovo>mysqlhotcopy.pl -u root -p 123456 test  f:/backupFlushed 11 tables with read lock (`test`.`b`, `test`.`emp_date`, `test`.`t1`, `test`.`t2`, `test`.`t3`, `test`.`tb_1`, `test`.`tb_2`, `test`.`tb_3`, `test`.`tb_5`, `test`.`tb_6`, `test`.`users`) in 1 seconds.Locked 0 views () in 0 seconds.Copying 22 files...------------------在Ubuntu上验证结果---------------------root@zhumuxian-machine:/# mysqlhotcopy -u root -p 123456 test /data/mysql/backupWarning: /usr/bin/mysqlhotcopy is deprecated and will be removed in a future version.Flushed 1 tables with read lock (`test`.`stu`) in 0 seconds.Locked 0 views () in 0 seconds.Copying 4 files...Copying indices for 0 files...Unlocked tables.mysqlhotcopy copied 1 tables (4 files) in 0 seconds (0 seconds overall).root@zhumuxian-machine:/# ls /data/mysql/backup/test

5.使用mysqldump进行逻辑备份

使用mysqldump工具创建的备份集有两种格式，一是标准的SQL语句，二是定界格式的文件。

备份单个数据库(可指定具体那个表，在数据库名后面加上表名即可)

PS C:\Users\Lenovo> mysqldump -u root -p123456 --default-character-set gbk zz > f:\zz.sql

备份多个数据库

PS F:\> mysqldump -u root -p7758520 --default-character-set gbk --databases zz,test > zztest.sql

备份所有的数据库（如果有不存在的数据库，则会报错）

PS F:\> mysqldump -u root -p7758520 --default-character-set gbk --all-databases > db_all.sql

输出成定界格式文件

PS F:\> mysqldump -u root -p123456 --default-character-set gbk --tab=f:\mysql_backup --fields-terminated-by ',' --lines-terminated-by '\r\n' zz stu---------------如果指定的路径不存在，则会报错errorCode2-------------------------------------PS F:\mysql_backup> dirMode                LastWriteTime     Length Name----                -------------     ------ -----a---         2015/4/21      9:18       1482 stu.sql-a---         2015/4/21      9:18        108 stu.txt

6.恢复mysqldump命令创建的备份，使用MYSQL命令

SQL格式恢复

F:\mysql_backup>mysql -u root -p123456 --default-character-set=utf8  zz < .\zz_stu.sql

定界格式恢复

由于这种备份的分为表对象的备份和其数据的备份，首先将表对象恢复，然后利用LOAD DATA INFILE语句导入表数据。

mysql> drop table stu; --删除数据库Query OK, 0 rows affected (0.00 sec)---------------------------------------------F:\mysql_backup>mysql -u root -p123456  --default-character-set=utf8 zz < .\stu.sql-------------------------------------------------mysql> show tables;+--------------+| Tables_in_zz |+--------------+| sql_users    || stu          || users        |+--------------+3 rows in set (0.00 sec)mysql> select * from stu;Empty set (0.00 sec)------------------------------------------------------mysql> load data infile 'f:/mysql_backup/stu.txt' into table  stu character set gbk fields terminated by ',' lines terminated by '\r\n';Query OK, 4 rows affected (0.00 sec)Records: 4  Deleted: 0  Skipped: 0  Warnings: 0mysql> select * from stu;+---------+--------+-------+------+--------+| sno     | sname  | class | age  | gender |+---------+--------+-------+------+--------+| 4010404 | 祝小贤 | A1012 |   20 |      1 || 4010405 | 肖小杰 | A1013 |   22 |      0 || 4010406 | 钟小喜 | A1014 |   24 |      1 || 4010407 | 钟小惠 | A1015 |   26 |      0 |+---------+--------+-------+------+--------+4 rows in set (0.00 sec)

 7.增量备份的创建

Mysql的增量备份主要借助于二进制日志，因此必须开启二进制日志功能，可以在配置文件中设置log-bin的参数或者在开启mysql服务时指定--log-bin参数的值开启该功能。所谓增量备份就是将冷备份之后生成的二进制日志备份到指定路径，在需要恢复时，先进行冷备份恢复，再对备份的二进制日志进行分析，然后拿到mysql命令中执行。

分析二进制日志并输出到SQL文件中

root@zhumuxian-machine:/home/zhumuxian# mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 > /data/backup/000001.sql

利用mysql命令执行分析得到的SQL文件内容

root@zhumuxian-machine:/data/backup# mysql -u root -p123456 < ./000001.sql 

上面两条命令可以利用管道合并在一起写

root@zhumuxian-machine:/home/zhumuxian# mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 | mysql -u root -p123456

如果执行mysqlbinlog命令时出现类似"unknown variable  default-character-set=utf8" 错误时，一般都是客户端识别不了配置文件的某个变量，解决方法如下：

方法一：在配置文件中，将该变量注释掉

方法二：执行mysqlbinlog命令时，加上--no-defaults选项

方法三：将变量改为loose-variable-name=xxx（我这里是loose-default-character-set=utf8）

由于mysql二进制日志采用ROW格式记录，使用base64编码，几乎看不懂，可以利用--base64-output选项参数来解决，该参数有三个内置值：none(不做ROW格式处理)，decode-rows(解码处理，一般于-v组合使用)，auto(默认，采用ROW格式)，例示如下：

-----------------------base64编码的信息---------------------------mOA1VQ8BAAAAdAAAAHgAAAAAAAQANS42LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACY4DVVEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUNe----------------------使用decode-rows显示-------------------------root@zhumuxian-machine:/data/backup# mysqlbinlog --no-defaults --base64-output=decode-rows -v /data/mysql/mysql-bin.000001

8.第三方联机备份工具XtraBackup

XtraBackup是一个热备份工具，支持Innodb，MyISAM，CSV等引擎对象的备份，还是比较流行的。

XtraBackup的安装：

首先到Percona官网下载最新版的XtraBackup，我使用的是percona-xtrabackup_2.2.10-1.trusty_i386.deb，安装非常的简单，只需一条命令即可，如下所示。

root@zhumuxian-machine:/# dpkg -i percona-xtrabackup_2.2.10-1.trusty_i386.deb

查看安装是否成功

root@zhumuxian-machine:/usr/bin# ls xtra* inno* innobackupex  innochecksum  xtrabackup-----------------或者----------------------root@zhumuxian-machine:/usr/bin# xtrabackup --versionxtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (i686) (revision id: )

XtraBackup工具提供了两个命令，xtrabackup与innobackupex。

xtrabackup有两种模式，backup（备份）与prepare(恢复)，并且只能备份Innodb，xtradb引擎对象。

使用xtrabackup命令创建备份

root@zhumuxian-machine:/data/mydata/backup# xtrabackup --defaults-file=/etc/mysql/my.cnf --backup --terget-dir=/data/mydata/backup

上面执行xtrabbackup命令使用了三个参数：

--defaults-files：指定mysql配置文件的路径，最重要的功能时获取datadir的值

--backup ： 指定为备份

--target-dir：指定保存备份集的路径

查看备份集的路径

root@zhumuxian-machine:/# ls /data/mydata/backup/ibdata1  test                    xtrabackup_checkpointsmysql    xtrabackup_backupfiles  xtrabackup_logfile

从上面的结果可以看出，xtrabackup只备份数据文件，不会备份表的结构文件(.frm)，因此还需手动复制表结构文件。

innobackupex命令，支持多种引擎对象，该命令创建备份，需要连接数据库，可以创建一个用户专门用于备份，如下所示。

mysql> create user innobk@'localhost' identified by '123456';Query OK, 0 rows affected (0.00 sec)mysql> grant reload,lock tables,replication client,super on *.* to innobk@'localhost';Query OK, 0 rows affected (0.00 sec)

使用innobackupex命令创建完整备份，如下所示。

root@zhumuxian-machine:/# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password='123456' /data/mydata/backup/

上面命令使用了四个参数：--defaults-file 指定配置文件路径 --user 用户名  --password　用户密码   [backup_dir]　指定备份集保存的路径

使用innobackupex创建增量备份

root@zhumuxian-machine:/# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password='123456' --incremental --incremental-basedir=/data/mydata/backup/2015-04-21_22-18-27 /data/mydata/incremental/

上面创建增量备份的命令中，新增了2个参数：--incremental  指定备份为增量备份  --incremental-basedir 指定增量备份基于的全量备份的路径。

其实只有Innodb引擎对象才能算是增量备份，因为只有该引擎对象才有LSN（日志序列号），增量备份又是基于LSN来实现的，其它的引擎对象增量备份都是完整备份。

使用innobackupex全量恢复

----------------------应用日志，使数据文件达到一致性----------------root@zhumuxian-machine:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password='123456' --apply-log /data/mydata/backup/2015-04-21_22-18-27/------------------------执行恢复--------------------------------root@zhumuxian-machine:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password='123456' --copy-back /data/mydata/backup/2015-04-21_22-18-27/--执行完毕后，查看权限是否正确

使用innobackupex增量恢复

-------------------------应用日志------------------------root@zhumuxian-machine:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password='123456' --apply-log --redo-only /data/mydata/backup/2015-04-21_22-18-27///有增量备份必须指定--redo-only参数，强制xtrabackup只进行redo而不进行回滚------------------------应用增量备份----------------------root@zhumuxian-machine:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password='123456' --apply-log  /data/mydata/backup/2015-04-21_22-18-27/ --incremental-dir=/data/mydata/incremental/2015-04-21_22-33-40/----------------------------回滚-------------------------root@zhumuxian-machine:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password='123456' --apply-log  /data/mydata/backup/2015-04-21_22-18-27/----------------------------拷贝-------------------------root@zhumuxian-machine:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password='123456' --copy-back  /data/mydata/backup/2015-04-21_22-18-27/

使用innobackupex打包和压缩备份集，可以使用--stream参数来指定流格式，只支持tar,xbstream。

root@zhumuxian-machine:/tmp# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password='123456' --stream=tar /tmp | gzip > /data/mydata/backup/bk.tar.gz//流格式输出数据会临时保存在/tmp目录下，然后利用管道保存在指定路径，解压时需要使用-i参数