MySQL 数据库备份和恢复探讨（全量mysqldump 和 增量mysqlbinlog）

背景介绍：

在开发、测试以及生产环境中，每次修改MySQL数据库，严格上来说，都应该事先备份数据库，以便修改错误后导致数据库挂掉或者不能用，能够及时还原为修改前的数据库；另外，在实际使用中也可能因为不正确的使用mysql数据库，导致数据库不可用。因此，做好MySQL数据库的备份和恢复具有重要的意义。

MySQL 相关信息：

【ip地址】：127.0.0.1

【用户名】：root

【密  码】：mima

Linux命令行连接mysql数据库的命令为：

[sql]  view plain  copy

1. mysql -h127.0.0.1 -uroot -pmima  

全量备份 MySQL数据库

1、备份MySQL数据库实例的所有database

命令：

[sql]  view plain  copy

1. mysqldump -h127.0.0.1 -uroot -pmima --all-databases > all_databases.sql  

恢复命令：

[sql]  view plain  copy

1. mysql-h127.0.0.1 -uroot -pmima <all_databases.sql  

2、备份MySQL数据库实例中的某些database

命令：

[sql]  view plain  copy

1. mysqldump-h127.0.0.1 -uroot -pmima --databases DB1 DB2> some_databases.sql  

这里的：--databasesDB1 DB2，其中DB1和DB2指定了需要备份的数据库名。

3、备份MySQL数据库实例中的一个database

命令：

[sql]  view plain  copy

1. mysqldump-h127.0.0.1 -uroot -pmima --databases DB1 >one_database.sql  

推荐 这种备份 ）

恢复命令：

[sql]  view plain  copy

1. mysql-h127.0.0.1 -uroot -pmima <one_database.sql (<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255); "><strong><span style="color:red;">推荐</span></strong></span><span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">)</span>  

或者：

[sql]  view plain  copy

1. mysql>source /data/backup/one_database.sql   

（注意：这里的source是在mysql命令中执行的，/data/backup/one_database.sql是mysql客户端服务器上存放的备份的数据）

区别如下语句：

命令：

[sql]  view plain  copy

1. mysqldump-h127.0.0.1 -uroot -pmima DB1>one_database.sql  

恢复命令：

[sql]  view plain  copy

1. mysql-h127.0.0.1 -uroot -pmima -e’use DB1;’ < one_database.sql  

(失败)              

[sql]  view plain  copy

1. mysql -h127.0.0.1 -uroot -pmima -e’useDB1;source /data/backup/one_database.sql’  

（ 成功,一般用于脚本中 ）

或者：

推荐如下：

[sql]  view plain  copy

1. mysql>useDB1;  
2. mysql>source /data/backup/one_database.sql   

这两句的区别是，--databases 会多了这句，表示如果不存在数据库DB1，则创建。

--

-- CurrentDatabase: `DB1

--

CREATEDATABASE/*!32312 IF NOT EXISTS*/ `DB1*!40100 DEFAULT CHARACTER SET latin1 */;

USE `DB1

4、备份MySQL数据库实例中某一张或几张表

命令：mysqldump -h127.0.0.1 -uroot -pmima DB1 TABLE1 TABLE2 > several_tables.sql

恢复命令: mysql>use DB1;

                   mysql>source/data/backup/serveral_tables.sql

增量备份 MySQL数据库

MySQL增量备份主要通过二进制日志文件进行增量备份。MySQL数据库会以二进制形式自动把用户对MySQL数据库的操作记录到文件，它记录了所有的DDL和DML（除了数据查询语句）语句，以事件形式记录，还包含语句执行所消耗的时间，当用户希望恢复的时候可以使用备份文件进行恢复。

一般来说开启二进制日志大概会有1%的性能损耗（参见MySQL官方中文手册5.1.24版本）。

二进制有两个最重要的使用场景：

其一：MySQLReplication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到Master-slave数据一致，即主从一致。

其二：自然就是数据恢复了，通过使用mysqlbinlog工具来恢复数据。

通过mysqlbinlog工具。

开启binlog日志

（1）vim编辑MySQL配置文件my.cnf

在[mysqld] 区域设置添加：

[plain]  view plain  copy

1. log_bin = mysql_bin  
2. server_id = 1  

注意：对于MySQL版本为5.7.18 一定要配置server_id的值，比如这里配置为1。

（2）重启mysqld服务使配置生效

[plain]  view plain  copy

1. service mysql restart  

常用binlog日志命令：

1、查看所有binlog日志列表

[sql]  view plain  copy

1. mysql> show master logs;  

2、查看master状态，即最后（最新）一个binlog日志的编号名称，及其最后一个操作时间pos结束点值

[sql]  view plain  copy

1. mysql> show master status;  

3、刷新log日志，自此刻开始产生一个新编号的binlog日志文件

[sql]  view plain  copy

1. mysql> flush logs;  

注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加-F选项也会刷新binlog日志。

4、重置（清空）所有binlog日志

[sql]  view plain  copy

1. mysql> reset master;  

全量备份与增量备份结合的栗子：

进行全量备份：

[sql]  view plain  copy

1. mysqldump -uroot -pmima -F --databases shop > shop.sql  

其中：-F是备份的是刷新binlog文件，即后面的操作会放在一个新的binlog文件中。--databases 可以简写为 -B，指定多个databases，如下：

[sql]  view plain  copy

1. mysqldump -uroot -pmima -F -B shop > shop.sql  

效果如下：

其中，shop.sql在8点42分时进行全量备份的数据，而mysql_bin.000002是全量备份shop.sql之后的操作记录的二进制文件，之后，我们进行如下操作：

[sql]  view plain  copy

1. mysql> use shop;  
2. mysql> insert into tb_role (name, status, note, mtime, ctime)values('tim1', 1, ' 哈哈', 123123, 123123);  
3. mysql> insert into tb_role (name, status, note, mtime, ctime)values('tim525', 0, '哈哈', 123123, 123123);  
4. Query OK, 1 row affected (0.00 sec)  

即插入了两条数据，之后，我们不小心将数据库shop删除掉，即执行了命令：

[sql]  view plain  copy

1. mysql> drop database shop;  

执行恢复操作：

（1）恢复全量备份的数据

[sql]  view plain  copy

1. mysql -uroot -pmima<shop.sql  

（2）恢复全量备份之后的增量数据（基于位置或者基于时间）

打开mysql_bin.000002文件，即通过mysqlbinlogmysql_bin.000002，查看到drop databaseshop的语句的位置在799，因此，我们只需恢复这个文件开始到删除数据库shop之前的位置。

或者通过命令：

[sql]  view plain  copy

1. mysql>show binlog events in 'mysql_bin.000002' \G;  

增量恢复语句如下：

[sql]  view plain  copy

1. mysqlbinlog --start-position=4 --stop-position=798 mysql_bin.000002|mysql -uroot -pmima  

注意：这里的开始位置和结束位置只要能够将自己想要的开始和结束的位置包括进去就可以，无需精确到具体的位置，比如这里我只取比799小的数值为798。

另一种恢复是通过时间：

[sql]  view plain  copy

1. mysqlbinlog --start-datetime=’ 2017-08-31 00:00:00’ --stop-datetime=’ 2017-08-31 12:00:00’ mysql_bin.000002|mysql -uroot -pmima  

补充知识点：

1、判断当前MySQL是否启用mysqlbinlog

方法一：

输入命令：whereis my.ini

找到对应的my.cnf的位置，我这里是/etc/my.cnf，my.cnf是用于配置MySQL相关的参数。

查看my.cnf中是否添加这行log_bin =mysql_bin，如果有添加则说明有开启，想停止掉的话就注释这行。

注意：我用的MySQL的版本是5.7.18，如果想开启mysqlbinlog，不仅要添加log_bin=mysql_bin，而且要指定server_id=1，否则，mysql启动会报：

[root@master data]# service mysql start

Starting MySQL. ERROR! The server quit without updating PID file(/data/program/mysql/data/master.pid).

补充知识点：这里log_bin写为log-bin也可以，另外，log_bin=具体要存放二进制文件的位置，上面log_bin=mysql_bin是存放在数据目录即datadir的目录下。

方法二：

输入命令：show variables like ‘%log_bin%’;

方法三：

查看datadir目录下是否有mysql_bin.index和mysql_bin.000000*（注意：这个方法的前提是配置的二进制目录在缺省的数据目录下）

其中，my_bin.index 类似一个目录，记录了有哪些mysql_bin.0000000*，而每个具体的mysql_bin.000000*，则记录了mysql的相关操作。