MySQL学习笔记十二：数据备份与恢复
数据备份
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:\backup D:\ProgramData\MySQL\MySQL Server 5.5\data\test\t2.frm D:\ProgramData\MySQL\MySQL Server 5.5\data\test\t2.MYD D:\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::mysql Downloading DBD-mysql-4.029...done Unpacking DBD-mysql-4.029...done Generating HTML for DBD-mysql-4.029...done Updating files in site area...done 11 files installed ----------------mysqlhotcopy命令使用------------------ C:\Users\Lenovo>mysqlhotcopy.pl -u root -p 123456 test f:/backup Flushed 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/backup Warning: /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> dir Mode 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 termi nated by '\r\n'; Query OK, 4 rows affected (0.00 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 mysql> 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编码的信息--------------------------- mOA1VQ8BAAAAdAAAAHgAAAAAAAQANS42LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAACY4DVVEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUNe ----------------------使用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 --version xtrabackup 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_checkpoints mysql 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参数
