2019-04-15 MySQL数据库备份与恢复基础实践（1）

作者: 阿丧小威 | 来源:发表于2019-04-16 22:31 被阅读0次

1. MySQL数据库的备份与恢复

1.1 备份数据的意义

对于绝大多数企业来讲，失去数据就相当于失去商机、失去产品、失去客户，甚至会造成公司倒闭。

1.2 使用mysqldump进行数据库备份实践

1. 不带参数备份单个数据库

[root@oldboy ~]# mysql < /opt/bak.sql     ---先还原数据，由于前面在my.cnf配置了用户密码，所以这里和后面的例子都不用输账号密码

范例：不加任何参数备份名字为oldboy的库

[root@oldboy ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.41 Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use oldboy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test;    ---查看备份前的数据
+----+---------+
| id | name    |
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
|  3 | inca    |
|  4 | zuma    |
|  5 | kaka    |
+----+---------+
5 rows in set (0.00 sec)

执行备份oldboy库的命令：

[root@oldboy ~]# mysqldump oldboy > /opt/mysql_bak.sql

用egrep检查备份结果：

[root@oldboy ~]# egrep -v "#|\*|--|^$" /opt/mysql_bak.sql     ---去掉注释等特殊字符
DROP TABLE IF EXISTS `test`;    ---删除表语句
CREATE TABLE `test` (    ---建表语句
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');    ---插入语句
UNLOCK TABLES;

2. 加-B参数备份的实践

[root@oldboy ~]# mysqldump -B oldboy > /opt/mysql_bak_B.sql 
[root@oldboy ~]# diff /opt/mysql_bak.sql /opt/mysql_bak_B.sql     ---使用diff或vimdiff比较不同得地方
18a19,26
> -- Current Database: `oldboy`
> --
> 
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */;
> 
> USE `oldboy`;
> 
> --
51c59
< -- Dump completed on 2019-02-07  7:18:33
---
> -- Dump completed on 2019-02-07  7:24:31

可以看到，加“B”参数的作用是增加创建数据库和连接数据库的语句。即如下这两条语句：

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `oldboy`;

3. 使用gzip压缩备份数据库的实践

[root@oldboy ~]# mysqldump -B oldboy | gzip > /opt/mysql_bak_B.sql.gz    ---注意压缩命令前要加管道
[root@oldboy ~]# ll /opt/
total 16
-rw-r--r--. 1 root root 2051 Feb  6 16:43 bak.sql
-rw-r--r--. 1 root root 2051 Feb  7 07:24 mysql_bak_B.sql    ---没有压缩的备份数据较大
-rw-r--r--. 1 root root  779 Feb  7 07:29 mysql_bak_B.sql.gz    ---压缩过的数据库备份小了很多
-rw-r--r--. 1 root root 1908 Feb  7 07:18 mysql_bak.sql

小结：

使用-B参数备份数据库，会在备份的数据中增加建库及use库的语句
使用-B参数备份数据库，后面还可以直接接多个库名，实现同时备份多个库
使用gzip命令压缩备份的数据，备份的压缩包是原来的1/3大小

4. musqldump命令工作原理

利用mysqldump命令备份数据的过程，实际上就是把数据（包括库表）从MySQL库里以SQL语句的形式直接输出或者生成备份文件的过程，这种备份成SQL语句得方式称为逻辑备份。
把备份的数据过滤掉注释信息，剩下的都是SQL语句：

[root@oldboy ~]# egrep -v "#|\*|--|^$" /opt/mysql_bak.sql
DROP TABLE IF EXISTS `test`;    ---删除表语句
CREATE TABLE `test` (    ---建表语句
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');    ---插入语句
UNLOCK TABLES;

mysqldump这种逻辑备份的方式效率不是很高，在当下的生产场景中，多用于数据量不是很大得备份情况，例如30GB以内的数据。若在数据库数据很大的时候采用此备份方式，所用的时间会很长，恢复的时间也会很长，因此，当数据大于30GB（参考值）后，建议选择其他得诸如Xtrabackup的物理方式进行备份和恢复。

5. 备份多个库

（1）备份多个库的实践

mysql> show databases;    ---查看当前数据库里的库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oldboy             |
| oldboy_utf8        |
| performance_schema |
+--------------------+
5 rows in set (0.01 sec)
mysql> quit
Bye
[root@oldboy ~]# mysqldump -B oldboy oldboy_utf8 | gzip > /opt/test.sql.gz    ---同时备份两个库
---使用-B参数表示后面可接多个库，并且在备份文件里会增加use db，和create database db的信息
[root@oldboy ~]# mysqldump -B oldboy oldboy_utf8 mysql | gzip > /opt/all.sql.gz
---mysql5.5及以前版本备份mysql库时需要加一个--events参数，否则会有警告信息
[root@oldboy ~]# ll /opt/all.sql.gz 
-rw-r--r--. 1 root root 181236 Feb  7 07:53 /opt/all.sql.gz
---备份的数据库不能太小（例如几字节），否则可能会由于命令或参数等导致备份结果有问题

（2）如何做分库备份
分库备份实际上就是每次只执行一个mysqldump备份命令语句备份一个库，如果数据库里有多个库，就执行多条相同的语句来备份各个库。

[root@oldboy ~]# mysql -e "show databases;" | egrep -v "_schema|Database"    ---取出需要备份的库名列表
mysql
oldboy
oldboy_utf8
[root@oldboy ~]# mysql -e "show databases;" | egrep -v "_schema|Database" | sed -r 's#^(.*)#mysqldump -B \1 | gzip > /tmp/\1.sql.gz#g'    ---利用命令拼接成多个mysqldump备份
mysqldump -B mysql | gzip > /tmp/mysql.sql.gz
mysqldump -B oldboy | gzip > /tmp/oldboy.sql.gz
mysqldump -B oldboy_utf8 | gzip > /tmp/oldboy_utf8.sql.gz
[root@oldboy ~]# mysql -e "show databases;" | egrep -v "_schema|Database" | sed -r 's#^(.*)#mysqldump -B \1 | gzip > /tmp/\1.sql.gz#g' | bash
---最后管道对bash执行这些备份命令，就是一次分开备份多个库了
[root@oldboy ~]# ll /tmp/*.sql.gz
-rw-r--r--. 1 root root 181064 Feb  7 08:03 /tmp/mysql.sql.gz
-rw-r--r--. 1 root root    779 Feb  7 08:03 /tmp/oldboy.sql.gz
-rw-r--r--. 1 root root    511 Feb  7 08:03 /tmp/oldboy_utf8.sql.gz

6. 备份单个表

当不加-B参数备份数据库时，例如“mysqldump oldboy test”，mysqldump命令默认就会把oldboy当作库，把test当作表，如果后面还有多个字符串，例如“mysqldump oldboy test test1”，那么除了oldboy为库之外，其他的test、test1都是oldboy库的表。

[root@oldboy ~]# mysqldump oldboy test > /tmp/oldboy_test.sql    ---仅备份oldboy库里的test表

7. 备份多个表

[root@oldboy ~]# mysqldump mysql user db > /tmp/mysql.sql
[root@oldboy ~]# egrep -v "#|\*|--|^$" /tmp/mysql.sql    ---这里不能用-B参数，因为库后面的两个都是表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (    ---user表
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';
LOCK TABLES `user` WRITE;
UNLOCK TABLES;
DROP TABLE IF EXISTS `db`;
CREATE TABLE `db` (    ---db表
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`Db`,`User`),
  KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges';
LOCK TABLES `db` WRITE;
INSERT INTO `db` VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y'),('%','test\\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y'),('localhost','oldboy','test','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'),('192.168.9.%','blog','blog','Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N');
UNLOCK TABLES;

企业中可能会存在这样的情况，一个库里有大表也有小表，有时可能需要只恢复某一个小表，上面实现的多表备份文件很难拆开，就像没有分库那样会导致恢复某一个小表也很麻烦。
这里的思路和分库备份一样，每执行一条语句就备份一个表，生成不同的数据文件即可：

mysqldump oldboy test > oldboy_test.sql
mysqldump oldboy test1 > oldboy_test1.sql
......

分表备份的缺点：数据文件多，很碎，一旦需要全部恢复又很麻烦。
解决办法如下：

做一个完整全备，再做一个分库分表备份
虽然文件多、碎，但可以利用脚本批量操作多个SQL文件

8. 企业备份案例解析

如果多个库或多个表备份到了一个文件里，那么这种情况下，如何恢复单个库或者单个表？
解决办法如下：

找个第三方测试库，将所有备份都导入到这个测试库里，然后把需要的单库或表再备份出来，最后恢复到需要恢复的正式库里
如果是单表恢复，还可以执行“grep -w 表名 bak.sql > 表名.sql”命令

当然，最好是备份时提前采用分库分表备份

9. 备份数据库表结构（不包含数据）

利用mysqldump的-d参数可以只备份表的结构，即建表的语句

[root@oldboy ~]# mysqldump -d oldboy > /opt/oldboy.sql    ---备份oldboy库的所有表结构，也可以指定某一个表
[root@oldboy ~]# egrep -v "#|\*|--|^$" /opt/oldboy.sql 
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

10. 只备份数据库的数据（不包含表结构）

利用-t参数备份数据库表的数据（SQL语句形式）：

[root@oldboy ~]# mysqldump -t oldboy > /opt/oldboy1.sql 
[root@oldboy ~]# egrep -v "#|\*|--|^$" /opt/oldboy1.sql 
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');
UNLOCK TABLES;

11. 同时将数据和表结构分离导出

利用-T参数可以实现将数据和表结构同时分离备份。不过有可能会碰到如下情况：

[root@oldboy ~]# mysqldump oldboy test --compact -T /tmp/
---5.6版本因为安全权限问题不能直接导出了
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
---提示是因为--secure-file-priv选项所导致

如果依然想以此方式备份数据，那么可以调整配置文件参数并重启MySQL：

[root@oldboy ~]# vi /etc/my.cnf
secure_file_priv=''    ---在[mysqld]模块下增加
:wq
[root@oldboy ~]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS! 
Starting MySQL...... SUCCESS!

备份的命令如下：

[root@oldboy ~]# mysqldump oldboy test --compact -T /tmp/
--compact是减少无用输出的意思
[root@oldboy ~]# ll /tmp/test*
-rw-r--r--. 1 root  root  331 Feb  7 13:55 /tmp/test.sql
-rw-rw-rw-. 1 mysql mysql  40 Feb  7 13:55 /tmp/test.txt
[root@oldboy ~]# cat /tmp/test.sql     ---建表的语句
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
[root@oldboy ~]# cat /tmp/test.txt     ---纯文本数据，非SQL语句
1   oldboy
2   oldgirl
3   inca
4   zuma
5   kaka

mysqldump参数小结：

-d参数的作用是只备份库表的结构（SQL语句形式）
-t参数的作用是只备份表内的数据（SQL语句形式）
-T将建表和数据分离成不同的文件，数据是纯文本，表结构是SQL语句

12. 刷新binlog文件参数（-F）

（1）binlog是什么
Binlog是一个二进制格式的文件，用于记录用户对数据库更新得SQL语句信息，例如更改数据库库表和更改表内容得SQL语句都会记录到binlog里。
（2）binlog对于备份的作用
假设每天0点对数据库进行备份，那么在两次备份之间就有24小时的数据没有备份，在这期间如果数据库发生故障，使用mysqldump全量恢复也只能恢复到当日0点，但是有了binlog文件，就可以将两次完整备份间隔之间的数据还原，因为binlog文件里的数据就是写入数据库的数据，使用binlog文件恢复数据，我们称之为二进制增量数据恢复。
（3）为什么要刷新binlog
刷新（切割）binlog日志的目的就是确定全备和增量备（binlog文件）的临界点，当全备完成后，全备时刻以前的binlog文件就没用了，但是全备以后到下一次全备之前的数据就是十分重要的，这部分数据就存在于binlog文件里，因此在进行全备时需要找到全备之后和binlog增量之间的临界点，使得恢复时，需要的binlog文件数据一条不多（不能和全备的内容重合），一条不少（全备后的所有数据都要有）。

使用-F刷新日志的原理图

（4）如何开启binlog功能
binlog文件生效需要一个参数：log_bin，编辑配置文件增加log_bin参数即可，注意，MySQL5.6版本变成了下划线连接得格式（MySQL5.6以前是中杠）。

[root@oldboy ~]# vi /etc/my.cnf
log_bin    ---把默认的注释去掉即可，默认binlog文件名前缀为“主机名-bin”，也可以自定义名字，例如log_bin = mysql_bin
:wq
[root@oldboy ~]# /etc/init.d/mysqld restart    ---重启mysql服务
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@oldboy ~]# ll -rt /application/mysql/data/oldboy-bin*
-rw-rw----. 1 mysql mysql  20 Feb  7 21:35 /application/mysql/data/oldboy-bin.index    ---索引文件
-rw-rw----. 1 mysql mysql 120 Feb  7 21:35 /application/mysql/data/oldboy-bin.000001    ---日志文件

binlog日志切割就是确定全备和binlog增量备份的临界点
（5）使用-F刷新binlog日志
使用-F将会从备份后的时刻起重新记录binlog日志文件，将来增量恢复从新的binlog日志文件开始即可。
例如，早上10点丢失数据需要恢复数据，则数据恢复步骤具体如下：
1）将0:00点时刻备份的全备数据还原到数据库，这个时候数据就恢复到了当日0:00点
2）0:00点-10:00点丢失的数据，就要从全备后当天的所有binlog里恢复，而使用-F切割日志，就是找到0:00点这个时刻全备和binlog接缝的起始binlog文件。

[root@oldboy ~]# mysqldump -F -B oldboy | gzip > /opt/bak_$(date +%F).sql.gz    ---带-F备份
[root@oldboy ~]# ll -rt /application/mysql/data/oldboy-bin*
-rw-rw----. 1 mysql mysql 168 Feb  7 21:46 /application/mysql/data/oldboy-bin.000001
-rw-rw----. 1 mysql mysql 120 Feb  7 21:46 /application/mysql/data/oldboy-bin.000002    ---新binlog
-rw-rw----. 1 mysql mysql  40 Feb  7 21:46 /application/mysql/data/oldboy-bin.index
[root@oldboy ~]# mysqldump -F -B oldboy | gzip > /opt/bak_$(date +%F).sql.gz
[root@oldboy ~]# ll -rt /application/mysql/data/oldboy-bin*
-rw-rw----. 1 mysql mysql 168 Feb  7 21:46 /application/mysql/data/oldboy-bin.000001
-rw-rw----. 1 mysql mysql 168 Feb  7 21:47 /application/mysql/data/oldboy-bin.000002
-rw-rw----. 1 mysql mysql  60 Feb  7 21:47 /application/mysql/data/oldboy-bin.index
-rw-rw----. 1 mysql mysql 120 Feb  7 21:47 /application/mysql/data/oldboy-bin.000003    ---新binlog
可以看到，使用-F参数后，每备份一次，都会重新生成新的binlog记录数据

13. 记录binlog位置的特殊参数（--master-data）

mysqldump里提供了一个参数，使得管理员不用刷新binlog，也可以找到全量和增量的临界点，这就是“--master-data”参数。使用这个参数备份后，在备份的文件对应得SQL语句里会添加CHANGE MASTER语句及binlog文件及位置点信息。
当“--master-data=1”时，备份结果为可执行的“CHANGE MASTER...”语句；当“--master-data=2”时，备份结果为注释的“--CHANGE MASTER...”语句，“--”在SQL语句里为注释的意思。“--master-data”参数除了确定增量恢复和全备之间的临界点之外，进行主从复制时的作用更大。

[root@oldboy ~]# mysqldump --master-data=1 oldboy --compact | head -1
CHANGE MASTER TO MASTER_LOG_FILE='oldboy-bin.000003', MASTER_LOG_POS=120;
[root@oldboy ~]# mysqldump --master-data=2 oldboy --compact | head -1
-- CHANGE MASTER TO MASTER_LOG_FILE='oldboy-bin.000003', MASTER_LOG_POS=120;

14. 锁定所有表备份（-x参数）

默认情况下，在使用mysqldump命令备份期间，数据库是可以写入数据的，此时，备份的数据就不是某一个时刻的一致性备份了。如果备份时使用-x锁表备份就会取得0点时刻的完整备份，即在0点时刻停止所有写入操作，然后导出备份数据，备份完毕，放开写入。

锁表与不锁表备份示意图

15. innodb表特有的备份参数（--single-transaction）

当使用mysqldump的“--single-transaction”对innodb表进行备份时，会开启一个事务，并将整个备份过程放到一个事务里，以确保执行本次dump会话时，不会看到其他连续会话已经提交了的数据，即备份开始时刻的数据是什么样，备份出来就是什么样。相当于是锁表之后备份的数据，但是这个参数允许在备份期间写入数据的，而不是在使用“-x”参数锁表之后，备份期间无法写入任何数据。

mysqldump -B --master-data=2 --single-transaction oldboy | gzip > /opt/all.sql.gz

--single-transaction备份原理示意图

“--single-transaction”是innodb表的特有备份参数，只有事务型引擎，才能支持这样得备份功能。

1.3 mysqldump重要关键参数说明

-B,--databases：会在备份的数据中增加建库（create）及“use库”的语句，可以直接接多个库名，同时备份多个库*
-A，--all-databases：备份所有的数据库*
-d,--no-data：只备份库表结构（SQL语句形式），没有行数据
-t,--no-create-info：只备份表内行数据（SQL语句形式），没有表结构
-T,--tab=name：将库表和数据分离成不同的文件，行数据是纯文本，表结构是SQL语句，5.6版本默认没有权限操作，需要修改my.cnf参数
-F,--flush-logs：刷新binlog日志，生成binlog文件，将来增量恢复从这个新binlog文件开始，当备份多个库时，每个库都会刷新一次binlog，如果想只刷新一次binlog，可加“--lock-all-tables”或“--master-data”参数*
--master-data={1|2}：在备份结果中增加binlog日志文件名及对应的binlog位置点（即CHANGE MASTER...语句）。值为1时是不注释状态，值为2时是注释状态，该参数执行时会打开“--lock-all-tables”功能，除非有“--single-transaction”存在，使用该参数时会关闭“--lock-tables”功能*
-x,--lock-all-tables：备份时对所有数据库的表执行全局读锁，期间同时禁止“--single-transaction”和“--lock-tables”参数功能*
-I,--lock-tables：锁定所有的表为只读
--single-transaction：在备份InnoDB引擎数据表时，通常会启用该选项来获取一个一致性的数据快照备份，它的工作原理是设定本次备份会话的隔离级别为REPEATABLE READ，并将整个备份放在一个事务里，以确保执行本次dump会话时，不会看到其他连续会话已经提交了数据，即备份开始时刻的数据是什么样，备份出来就是什么样子。相当于锁表备份数据，但是这个参数是允许在备份期间写入数据的，而不是-x锁表后的备份期间无法写入任何数据，启用该参数会关闭“--lock-tables”*
-R,--routines：备份存储过程和函数数据
--triggers：备份触发器数据
--compact：只显示很少的有用输出，适合学习和测试环境调试用
提示：标*的参数为工作中比较重要的参数。

1.4 生产场景下，不同引擎的mysqldump备份命令

innodb引擎的备份命令：

mysqldump -A -B --master-data=2 --single-transaction | gzip > /opt/all.sql.gz    ---备份期间依然可以写入数据

多引擎混合（比如myisam与innodb混合）的备份命令：

mysqldump -A -B --master-data=2 | gzip > /opt/all_$(date +%F).sql.gz    ---会锁表

注意：如果数据库用到了存储过程和函数特殊功能，就加上“-R”备份，如果用到了触发器功能则加上“--triggers”备份（--triggers选项默认开启）；如果考虑切割binlog日志也可以加“-F”；如果不想备份所有库，也可以取消“-A”，独立指定多个库备份。

1.5 利用SQL语句方式对表进行导入导出

1. 导出表

mysql> use oldboy;
Database changed
mysql> select * from test;
+----+---------+
| id | name    |
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
|  3 | inca    |
|  4 | zuma    |
|  5 | kaka    |
+----+---------+
5 rows in set (0.08 sec)
mysql> select * from test into outfile "/tmp/oldboy_test1.txt";    ---将oldboy库里的test表导出为纯文本
Query OK, 5 rows affected (0.03 sec)
mysql> system cat /tmp/oldboy_test1.txt
1   oldboy
2   oldgirl
3   inca
4   zuma
5   kaka

导出时设置字符集：

select * from test into outfile "/tmp/oldboy_test2.txt" character set utf8;

以指定的分隔符导出，这里指定“-”为分隔符：

mysql> select * from test into outfile "/tmp/oldboy_test3.txt" fields terminated by "-";
Query OK, 5 rows affected, 1 warning (0.00 sec)
mysql> system cat /tmp/oldboy_test3.txt
1-oldboy
2-oldgirl
3-inca
4-zuma
5-kaka

导出时设置对字段内容进行引用，使用双引号：

mysql> select * from test into outfile "/tmp/oldboy_test4.txt" fields enclosed by '"';
Query OK, 5 rows affected (0.00 sec)
mysql> system cat /tmp/oldboy_test4.txt
"1" "oldboy"
"2" "oldgirl"
"3" "inca"
"4" "zuma"
"5" "kaka"

2. 导入表

先清空test表：

mysql> delete from test;
Query OK, 5 rows affected (0.14 sec)

将前面的oldboy_test1.txt格式的数据导入数据库：

mysql> system cat /tmp/oldboy_test1.txt
1   oldboy
2   oldgirl
3   inca
4   zuma
5   kaka
mysql> load data infile '/tmp/oldboy_test1.txt' into table test;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from test;
+----+---------+
| id | name    |
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
|  3 | inca    |
|  4 | zuma    |
|  5 | kaka    |
+----+---------+
5 rows in set (0.00 sec)