mysqldump备份与基于bin-log实现完全恢复

MySQL数据库备份是一项非常重要的工作，mysql的备份主要分为逻辑备份和物理备份，同时，不同的生产环境要备份的策略也不会不同。下面先说一说备份时要考虑到的一些因素，然后再实际操作进行不同方式的数据备份。

备份要注意的地方

在备份工作中，定制合理的备份策略是很重要的，一下是备份恢复过程中要注意的一些因素：

确定使用全备还是增量备份，全备的优点是备份保持最新备份恢复的时候可以一次恢复，花费的时间比较少，缺点是如果数据量大，那么会花费比较大的时间，长时间的备份操作会对系统造成负载；而增量备份，刚好相反，只需要备份在原来备份基础上增加的内容，备份时间少，对系统负载也小，比较麻烦的是，恢复时，需要全备份加上增量备份的部分，恢复时间比较长，而且要保证每次的增量备份数据可恢复，因为每一次增量恢复都需要依赖前一次的备份为基础。
要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间，备份操作要在系统负载较小的时候进行
确保mysql的bin-log日志的打开的，使用bin-log日志mysql可以在必要的时候做完整的恢复，或基于时间点的恢复，或基于pos位置的恢复。
要经常做备份恢复测试，确保备份是有效的，并且是可以恢复的
定期或不定期进行灾难演练，从演练过程中发现存在的问题和隐患

逻辑备份

在mysql中逻辑备份的最大优点是对不同的存储引擎都可以使用同样的方法来备份，而物理备份则不同的存储引擎有不同的备份方法。
mysql的逻辑备份是将数据库中的数据备份为一个文本文件，在mysqldump工具完成逻辑备份。

备份指定的数据库或者指定数据库中的表

shell > mysqldump -u user -p password dbname > /path/backupdb.sql

shell > mysqldump -u user -p password dbname tbname > /path/backupdb.sql

示例：备份db4库中的tb1表到/opt，没有指定用户密码是因为没有设置密码

[root@localhost ~]# mysqldump db4 tb1 > /opt/db4-tb1.sql

备份一个或者多个库

shell > mysqldump -u user -p password db1 db2 db3 > /path/backupdb.sql

备份所有库

shell > mysqldump -u user -p password --all-database > /path/all-db.sql

如果没有指定数据库中的任何表，默认导出数据库中所有的表。

mysqldump工具的使用

-A ： --all-databases 备份所有数据库，含create database
-B ：-databases db_name… 指定备份的数据库，包括create database语句
-E ： --events：备份相关的所有event scheduler
-R ： --routines：备份所有存储过程和存储函数
--triggers：备份表相关的触发器，默认启用,用--skip-triggers，不备份触发器
--master-data[=#]：此选项须启用二进制日志
1：所备份的数据之前加一条记录为change master to语句，非注释，不指定#，默认为1
2：记录为注释的change master to语句
此选项会自动关闭--lock-tables功能，自动打开--lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A时，会导致刷新多次数据库，在同一时刻执行转储和日志刷新，则应同时使用--
flush-logs和-x，--master-data或-single-transaction,此时只刷新一次
建议：和-x，--master-data或 --single-transaction一起使用
--compact 去掉注释，适合调试，生产不使用
-d ：--no-data 只备份表结构
-t ： --no-create-info 只备份数据,不备份create table
-n ： --no-create-db 不备份create database，可被-A或-B覆盖
--flush-privileges 备份mysql或相关时需要使用
-f ： --force 忽略SQL错误，继续执行
--hex-blob使用十六进制符号转储二进制列（例如，“abc”变为0x616263），受影响的数据类
型包括BINARY， VARBINARY，BLOB，BIT
-q： --quick 不缓存查询，直接输出，加快备份速度

备份与恢复数据库示列

将db4数据库整库备份，然后将其删除后恢复：

备份db4库

[root@localhost ~]# mysqldump -uroot -pcentos -l -F db4 > /opt/db4.sql
[root@localhost ~]# ls /opt/
db4.sql

删除db4数据库：

[root@localhost ~]# mysqladmin -uroot -pcentos DROP db4
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'db4' database [y/N] y
Database "db4" dropped

查看验证

[root@localhost ~]# mysqlshow -uroot -pcentos
+--------------------+
|     Databases      |
+--------------------+
| db10               |
| db5                |
| hellodb            |
| mysql              |
| test               |
+--------------------+

恢复库

[root@localhost ~]# mysql -uroot -pcentos < /opt/db4.sql 
ERROR 1046 (3D000) at line 22: No database selected

看到这里有错误，是以为在上面备份的时候没有 -B 参数，-B 参数作用是在备份时加上create database的建库语句，因为没有加这个参数，所以恢复时没有这个数据库错，这样只能登录到mysql创建一个同名的数据库然后执行恢复

MariaDB [(none)]> create database db4;
Query OK, 1 row affected (0.08 sec)

MariaDB [db4]> use db4;     #创建后必须要指定到库

MariaDB [(none)]> source /opt/db4.sql;   在mysql终端里使用 source 指令执行导入

MariaDB [db4]> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| tb1           |
+---------------+
1 row in set (0.00 sec)

MariaDB [db4]> select * from tb1 limit 3;
+------+-------+
| id   | name  |
+------+-------+
|    1 | demo  |
|    2 | demo2 |
|    3 | demo3 |
+------+-------+
3 rows in set (0.00 sec)

以上的备份恢复是基于整库备份和恢复的，下面基于全量备份 + bin-log 日志实现完全恢复

在db4数据库中，先进行一次全量备份，备份后创建表 tb2 ，写入数据，然后将数据库db4删掉进行恢复

备份整库数据

[root@localhost ~]# mysqldump -u root -pcentos -B -l -F db4 > /opt/full_db4.sql

参数说明：

-B :备份时添加建库语句
-l （小写l）：给数据库表添加只读锁
-F：刷新bin-log日志文件，相当于执行flush logs指令，生成新的bin-log日志文件，这些备份后的写入数据日志都放在新的bin-log日志文件中，利于查找

创建新表写入数据

MariaDB [db4]> create table tb2 (
    -> id int,
    -> name varchar(20)
    -> ) charset=utf8;
Query OK, 0 rows affected (2.04 sec)

MariaDB [db4]> insert into tb2 values (1,'demo1'),(2,'demo2');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

删除数据库

MariaDB [db4]> drop database db4;
Query OK, 2 rows affected (0.05 sec)

恢复数据

刚才在上面备份时没有使用 -B 参数，直接恢复失败了，现在加上 -B 选项直接在命令行用mysql指令恢复

[root@localhost ~]# mysql -uroot -pcentos < /opt/full_db4.sql

查看数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db10               |
| db4                |   #备份被恢复
| db5                |
| hellodb            |
| mysql              |
| test               |
+--------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> use db4;
Database changed

MariaDB [db4]> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| tb1           |
+---------------+
1 row in set (0.00 sec)

增量恢复

mysqlbinlog使用比较简单，可以查看前面文章使用

恢复备份后的数据要结合bin-log日志文件，查看当前使用的bin-log文件：

MariaDB [db4]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000009 |     2198 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

使用mysqlbinlog工具查看 master-bin.000009内容，恢复bin-log数据选择是pos值位置恢复，查找对应位置的pos值

# at 245
#180612 21:29:11 server id 1  end_log_pos 370 	Query	thread_id=25	exec_time=2	error_code=0
use `db4`/*!*/;
SET TIMESTAMP=1528810151/*!*/;
SET @@session.pseudo_thread_id=25/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table tb2 (
id int,
name varchar(20)
) charset=utf8
/*!*/;
# at 370
#180612 21:29:52 server id 1  end_log_pos 442 	Query	thread_id=25	exec_time=0	error_code=0
SET TIMESTAMP=1528810192/*!*/;
BEGIN
/*!*/;
# at 442
#180612 21:29:52 server id 1  end_log_pos 555 	Query	thread_id=25	exec_time=0	error_code=0
SET TIMESTAMP=1528810192/*!*/;
insert into tb2 values (1,'demo1'),(2,'demo2')
/*!*/;
# at 555

#180612 21:29:52 server id 1  end_log_pos 582 	Xid = 281
COMMIT/*!*/;       
# at 582

注意上面最后三行，结束的position位置要包含COMMIT，如果不包含登录没有提交事务

恢复bin-log日志数据

mysqlbinlog的用法比较简单，可以查看前面文章

[root@localhost mysql]# mysqlbinlog --start-position=245 --stop-position=582 /var/lib/mysql/master-bin.000009 | mysql -uroot -pcentos

查看数据

MariaDB [db4]> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| tb1           |
| tb2           |
+---------------+
2 rows in set (0.00 sec)

MariaDB [db4]> select * from tb2;
+------+-------+
| id   | name  |
+------+-------+
|    1 | demo1 |
|    2 | demo2 |
+------+-------+
2 rows in set (0.00 sec)

细说mysqldump几个参数

在上面的备份恢复过程中，看似完成了整个备份恢复的过程，但是上面的方式并不严谨，使用mysqldump备份的过程默认是会加读锁的，只有在备份MyISAM引擎的数据时才需要加锁，而现在数据据库基本使用的都是InnoDB存储引擎。

下面说一说mysqldump几个关键参数的作用：

-l： --lock-tables：

对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致，备份数据量大的表库时间会比较长，并且在锁表过程中数据是不可写入的

--master-data= # ：

mysqldump导出数据时，当这个参数的值为1的时候mysqldump出来的文件就会包括CHANGE MASTER TO这个语句CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1 ，当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。

master-data参数在建立slave数据库的时候会经常用到，因为这是一个比较好用的参数，默认值为1，默认情况下，会包含change master to，这个语句包含file和position的记录始位置。master-data=2的时候，在mysqldump出来的文件包含CHANGE MASTER TO这个语句，处于被注释状态。

--single-transaction ：

该选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务此选项通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥备份大型表时，建议将--single-transaction选项和--quick结合一起使用

上面的几个参数是比较重要的参数，尤其是--single-transaction和--master-data参数，在备份InnoDB引擎数据的时候这两个参数是常放一起组合使用，看一下备份过程中的日志内容

只使用了--single-transaction参数备份过程：

[root@localhost mysql]# cat localhost.log 
180613 21:45:24	   10 Connect	root@localhost as anonymous on 
		   10 Query	/*!40100 SET @@SQL_MODE='' */
		   10 Query	/*!40103 SET TIME_ZONE='+00:00' */
		   10 Query	FLUSH TABLES
180613 21:45:26	   10 Query	FLUSH TABLES WITH READ LOCK
		   10 Refresh	
/usr/libexec/mysqld, Version: 5.5.56-MariaDB (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
		   10 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
		   10 Query	START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
		   10 Query	UNLOCK TABLES
		   10 Query	set optimizer_switch='semijoin=off'

加 --singing-transaction和--master-data参数的备份过程

180613 21:47:51	   11 Connect	root@localhost as anonymous on 
		   11 Query	/*!40100 SET @@SQL_MODE='' */
		   11 Query	/*!40103 SET TIME_ZONE='+00:00' */
		   11 Query	SHOW STATUS LIKE 'binlog_snapshot_%'
		   11 Query	FLUSH /*!40101 LOCAL */ TABLES
		   11 Query	FLUSH TABLES WITH READ LOCK
		   11 Refresh	
/usr/libexec/mysqld, Version: 5.5.56-MariaDB (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
		   11 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
		   11 Query	START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
		   11 Query	SHOW STATUS LIKE 'binlog_snapshot_%'
		   11 Query	UNLOCK TABLES
		   11 Query	set optimizer_switch='semijoin=off'

对比上面的日志开头内容，在执行了FLUSH TABLES WITH READ LOCK后，添加START TRANSACTION 语句，用来开启单一事务，这个时候的加锁，仅仅是为了确定master-data中的binlog的具体位置和开启事务，开启事务后，就已经把读锁释放了

可靠的备份方式如下：

[root@localhost mysql]# mysqldump -uroot -pcentos -B -F --single-transaction --master-data=1 db5 > /opt/db5.sql