MySQL备份与恢复

MySQL备份与恢复

一、环境准备

1、操作系统：Ubuntu 14.04.4
2、数据库：MySQL5.5
3、起始数据：test数据库有user表和address表，二者无关联关系
user表：

address表：

二、MySQL备份

    MySQL备份分为全量备份与增量备份，**全量备份**是对指定数据库或表的**所有数据**都进行备份，**增量备份**是指对某个时间区间增加或者修改的数据进行备份。他们的区别是：全量备份要对数据库/表的所有数据进行备份，而增量备份只需对**部分数据**进行备份，而不是全盘备份。

三、全量备份与恢复

1、对数据库进行全量备份

语法：mysqldump -u 用户名 -p 数据库名 > 备份文件名字

mysqldump -u root -p test > /backup/test.sql

此时，/backup文件夹下就会出现test.sql文件，查看test.sql文件的内容，如下图：

由此可见，全量备份实质上就是一个建表和插入的脚本，而且上了表锁，当进行恢复时其他线程不可以对数据库中正在恢复的表进行操作。

2、对数据库进行恢复

    当测试数据库的恢复时，不可以删除整个数据库，而是删除这个数据库的所有表，如果删除了数据库，则可以用“create dababase 数据库名”进行创建，但必须要跟之前备份的数据库同名。另一种解决办法就是修改之前备份的sql脚本，选择需要恢复的数据库，如下图：

语法：mysql -u 用户名 -p 数据库名< 备份文件名字

mysql -u root -p test< /backup/test.sql

恢复之后，test数据库的数据又恢复了，如下图：

3、对表进行全量备份与恢复

备份语法：mysqldump -u 用户名 -p 数据库名 表名 > 备份文件名字

mysqldump -u root -p test user > /backup/user.sql

恢复语法：mysql -u 用户名 -p 数据库名 < 备份文件名字

mysql -u root -p test < /backup/user.sql

四、增量备份与恢复

1、前提条件

    增量备份需要依赖数据库的二进制日志文件，因此需要在配置文件（我的配置文件是：/etc/mysql/my.cnf）中开启二进制日志，如下图：

日志文件在log_bin对应的文件夹下，以mysql_bin为前缀的文件都是二进制日志文件

2、查看二进制日志文件及相关拓展

查看语法：mysqlbinlog 二进制日志文件名
转化为sql文件语法：mysqlbinlog 二进制日志文件名 > sql文件名字
转化为sql文件后，查看sql文件内容：

由上图可以看出，我们的每一个操作在二进制日志文件中都被记录为一个事务。增量备份有一个缺点，对同一条记录，若更改前为A，更改后为B，全量恢复时只需要插入B即可，但是增量恢复先插入A，再将A修改为B，比较耗时；全量恢复的缺点是要对数据库/表的所有数据进行恢复，数据量大时比较耗时，增量备份相对来说比较灵活，可以控制恢复区间。

3、增量恢复

（1）按照时间区间恢复语句：mysqlbinlog –start-datetime=”yyyy-MM-dd hh:mm:ss” –stop-datetime=”yyyy-MM-dd hh:mm:ss” 二进制日志文件名 | mysql -u 用户名 -p

mysqlbinlog --start-datetime="2017-05-07 11:00:00" --stop-datetime="2017-05-07 11:30:00" mysql-bin.000016 | mysql -u root -p

address表的name已经恢复为“天河”了：

（2）按照位置区间恢复语句：mysqlbinlog –start-position=起始行数 –stop-position=结束行数 二进制日志文件名 | mysql -u 用户名 -p

mysqlbinlog --start-position=294 --stop-position=303 mysql-bin.000016 | mysql -u root -p

4、优化

    当日志文件过多时，需要移除一部分日志文件，减少空间消耗。如果移除了正在记录的二进制日志文件（如上文中的mysql-bin.000016文件），接下来的操作数据库不会生成二进制日志文件，直到重启服务才会生成，但是每次都重启不仅影响用户体验，还很麻烦，所以应先执行一下语句：

mysqladmin flush-logs -u用户名 -p密码

mysqladmin flush-logs -uroot -proot

此时生成了一个新的bin日志文件：

mysql-bin.000001是真正的日志文件
mysql-bin.index是日志文件的索引文件，如果将其删除则无法正常记录日志

为防止数据丢失，移除日志文件前应先进行全量备份，对应的shell脚本如下：

#!/bin/sh
mysqldump -uroot -proot --flush-logs --all-databases > all.sql
rm -rf mysql-bin*
mysqladmin flush-logs -uroot -proot