MySQL逻辑备份mysqldump

一、官网对于mysqldump的说明

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

使用注意事项：

mysqldump默认是以0时区时间备份数据，在备份中它会加入

/*!40103 SET TIME_ZONE='+00:00' */;

这样一条命令，使用where条件中限制timestamp字段导出数据的同学注意了，

不要填写北京时间，要写0时区时间，也就是北京时间减去8小时。

二、创建测试环境

mysql> create database ceshi;
Query OK, 1 row affected (0.01 sec)

mysql> use ceshi;
Database changed
mysql> create table t1(id int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

二、参数含义（只选取了最常用的一些）：

连接选项

--host=host_name， -h host_name

从给定主机上的MySQL服务器中转储数据。默认主机为localhost。

--password[=password]， -p[password]

用于连接服务器的MySQL帐户的密码。密码值是可选的。

--port=port_num， -P port_num

对于TCP / IP连接，使用的端口号。

--user=user_name， -u user_name

用于连接到服务器的MySQL帐户的用户名。

选项文件选项

--defaults-file=file_name

仅使用给定的选项文件。如果文件不存在或无法访问，则发生错误。

--no-defaults

不要读取任何选项文件。

DDL选项

--add-drop-database

在每个CREATE DATABASE语句之前 写一个DROP DATABASE 语句。此选项通常与--all-databases或 --databases选项结合使用， 因为CREATE DATABASE语句只有在遇到这两个参数时，才会写入到备份文件中，否则不会写入CREATE DATABASE语句。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --add-drop-database >add-drop-database.sql
[root@host102 tmp]# cat add-drop-database.sql 


--
-- Current Database: `ceshi`
--

/*!40000 DROP DATABASE IF EXISTS `ceshi`*/;   #已经有DROP DATABASE命令了，如果不加--add-drop-database 参数，则不会显示这个命令。   /*!40000 的意思是当执行脚本的mysql数据库版本号大于4.0时，这个命令才会被执行。

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

--add-drop-table

在每个CREATE TABLE 语句之前 写一个DROP TABLE语句。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --add-drop-table  >add-drop-table.sql
[root@host102 tmp]# cat add-drop-table.sql 



DROP TABLE IF EXISTS `t1`;  #备份文件中有了drop table命令
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--add-drop-trigger

 在每个CREATE TRIGGER语句之前 写一个DROP TRIGGER语句。

效果同上。

--no-create-db， -n

如果在有--databases或 --all-databases选项时，加入此参数，则不会向脚本中写入CREATE DATABASE语句。 。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --no-create-db  >no-create-db.sql
[root@host102 tmp]# cat no-create-db.sql 


--
-- Current Database: `ceshi`
--
###没有create database命令了
USE `ceshi`;

--
-- Table structure for table `t1`
--

--no-create-info， -t

不要编写CREATE TABLE 创建每个转储表的语句。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --no-create-info  >no-create-info.sql
[root@host102 tmp]# cat no-create-info.sql 

USE `ceshi`;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
#没有了create table建表语句
INSERT INTO `t1` VALUES (1);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

--replace

以REPLACE方式写入备份文件中，而不是INSERT 陈述。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -B ceshi --replace >replace.sql
[root@host102 tmp]# cat replace.sql 


LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
REPLACE INTO `t1` VALUES (1);    #默认应该是insert into，现在变成了replace
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

调试选项

--force， -f

忽略所有错误；即使在表转储期间发生SQL错误，也要继续操作。

一般使用mysqldump导出视图时，视图已经无效了，此时导出会报错，导致备份命令终止，加入-f参数，会忽略报错，继续执行，同时将创建视图语句以注释的方式写入到

搭建测试环境

mysql> create table t2(id int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> create view v_t2 as select * from t2;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table t2;
Query OK, 0 rows affected (0.01 sec)

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi >ceshi.sql
mysqldump: Got error: 1356: View 'ceshi.v_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES

#报错后，命令就终止了。

[root@host102 tmp]#

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi -f >f.sql
mysqldump: Got error: 1356: View 'ceshi.v_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES
mysqldump: Couldn't execute 'SHOW FIELDS FROM `v_t2`': View 'ceshi.v_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

#虽然报错了，其实mysqldump还在执行。

[root@host102 tmp]# cat f.sql

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `v_t2`
--

DROP TABLE IF EXISTS `v_t2`;

-- failed on view `v_t2`: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_t2` AS select `t2`.`id` AS `id` from `t2`

 #以注释的方式显示出了 这个视图的创建语句

--log-error=file_name

通过将警告和错误附加到命名文件来记录它们。默认为不记录日志。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi -f  --log-error=error.log >f.sql

#备份时报错倣不会显示在控制台上，而是写入到日志文件中

[root@host102 tmp]# cat error.log 
mysqldump: Got error: 1356: View 'ceshi.v_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES
mysqldump: Couldn't execute 'SHOW FIELDS FROM `v_t2`': View 'ceshi.v_t2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
[root@host102 tmp]# 

--default-character-set=charset_name

使用charset_name作为默认字符集。请参见第10.15节“字符集配置”。如果未指定任何字符集，则 mysqldump使用utf8。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --default-character-set=gbk >gbk.sql
[root@host102 tmp]# cat gbk.sql 


-- MySQL dump 10.13  Distrib 5.7.29, for linux-glibc2.12 (x86_64)
--
-- Host: 127.0.0.1    Database: ceshi
-- ------------------------------------------------------
-- Server version    5.7.29-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES gbk */;   #默认这行是set names utf8，现在已经是GBK了

复制选项

 --apply-slave-statements

在备份开始前停掉主从同步线程，备份结束后开启复制线程。

这个参数不常用。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --apply-slave-statements >apply-slave-statements.sql
[root@host102 tmp]# cat apply-slave-statements.sql 
-- MySQL dump 10.13  Distrib 5.7.29, for linux-glibc2.12 (x86_64)
--
-- Host: 127.0.0.1    Database: ceshi
-- ------------------------------------------------------
-- Server version    5.7.29-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- stop slave statement to make a recovery dump)
--

STOP SLAVE; #停掉主从复制
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0; 

...
...
--
-- start slave statement to make a recovery dump)
--

START SLAVE; #开启主从复制
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

--delete-master-logs

在主从复制环境中，执行备份时会向数据库发送一条PURGE BINARY LOGS命令，删掉binlog文件，这个参数启用后，会自动启用 --master-data参数,会记录一条change master命令，这个位点是本机的。


mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       742 |
| mysql-bin.000003 |       632 |
| mysql-bin.000004 |       177 |
| mysql-bin.000005 |       177 |
| mysql-bin.000006 |       177 |
| mysql-bin.000007 |       177 |
| mysql-bin.000008 |       177 |
| mysql-bin.000009 |       341 |
| mysql-bin.000010 |       344 |
| mysql-bin.000011 |       217 |
| mysql-bin.000012 |      1328 |
| mysql-bin.000013 |       217 |
| mysql-bin.000014 |      3515 |  ##本地有14个binlog
+------------------+-----------+
14 rows in set (0.01 sec)

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --delete-master-logs >delete-master-logs.sql
[root@host102 tmp]# cat delete-master-logs.sql

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=234;
#多了一条change master命令，这个位点是本机的位点和show master status结果一样。
--
-- Current Database: `ceshi`
--


mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000015 |       234 |  #本地只剩一个binlog文件了
+------------------+-----------+
1 row in set (0.00 sec)

mysql> 

--dump-slave


在备份文件中记录一条change master命令，这个位点是主从复制架构中，主库的位点，

注意，已执行的中继日志中的事务顺序不一致会导致使用错误的位置。

备份文件是对单表加锁备份的，主库位点是在备份前拿到的，备库一直在回放数据，这个位点早已经不准确了。

想到拿到准确的位点，应该在没有主备延迟的情况下，先停掉主从复制线程，执行备份，最后再开启主从复制线程。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --dump-slave >dump_slave.sql
[root@host102 tmp]# cat dump_slave.sql 

--
-- Position to start replication or point-in-time recovery from (the master of this slave)
--

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=3439;
#这个位点不是本机的，是主从复制加构中，主库的位点
--
-- Current Database: `ceshi`
--

--include-master-host-port

在备份文件中的change master命令中，记录主从复制架构中，主库的IP和port

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --dump-slave --include-master-host-port >dump_slave_host_port.sql^C
[root@host102 tmp]# cat dump_slave_host_port.sql 

-- Position to start replication or point-in-time recovery from (the master of this slave)
--

CHANGE MASTER TO MASTER_HOST='192.168.150.101', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=3584;
#多记录了host和port信息
--
-- Current Database: `ceshi`
--

--master-data[=value]

这个参数最为常用，作用和dump-slave参数类似，都是在备份文件中加入change master，这个命令记录的位点是本机的，

使用该参数后，会自动关闭--lock-tables参数，开启--lock-all-tables参数，

如果参数中指定了--single-transaction，那么会关闭--lock-all-tables参数.

也就是在执行备份前，mysqldump会先执行一次【FLUSH TABLES WITH READ LOCK】命令，锁住全库，禁任何数据写入，这样拿到的备份位点就是一致的。缺点是要锁数据库，数据库在备份期间只能查询，下边会讲如何在不锁库的情况下拿到一致性的备份数据。

这个命令是在备份开始前执行的，所以在备份文件中看不到，开启general_loog后，可以看到

[root@host102 ~]# tail -f /usr/local/mysql/data/host102.log
/usr/local/mysql/bin/mysqld, Version: 5.7.29-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2020-05-31T00:14:29.494491Z       65 Connect    root@127.0.0.1 on  using SSL/TLS
2020-05-31T00:14:29.495023Z       65 Query    /*!40100 SET @@SQL_MODE='' */
2020-05-31T00:14:29.495588Z       65 Query    /*!40103 SET TIME_ZONE='+00:00' */
2020-05-31T00:14:29.496192Z       65 Query    FLUSH /*!40101 LOCAL */ TABLES
2020-05-31T00:14:29.497395Z       65 Query    FLUSH TABLES WITH READ LOCK
2020-05-31T00:14:29.497897Z       65 Query    SHOW VARIABLES LIKE 'gtid\_mode'
2020-05-31T00:14:29.501941Z       65 Query    SELECT @@GLOBAL.GTID_EXECUTED
2020-05-31T00:14:29.502467Z       65 Query    SHOW MASTER STATUS
...
...

设置的--master-data=2，所以change master命令前边有【-- 】注释的意思，
如果设置的--master-data=1，前边不会有-- ，这条语句就不再注释了。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --master-data=2 >master-data.sql
--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=693;
#设置的--master-data=2，所以change master命令前边有【-- 】注释的意思，
#如果设置的--master-data=1，前边不会有-- ，这条语句就不再注释了
--
-- Current Database: `ceshi`
--

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

USE `ceshi`;

--set-gtid-purged=value

 此选项通过指示是否向SET @@GLOBAL.gtid_purged输出添加语句来控制写入全局事务ID（GTID）信息 。此选项还可能导致将语句写入输出，从而在重新加载转储文件时禁用二进制日志记录。

• --set-gtid-purged=OFF：SET @@SESSION.SQL_LOG_BIN=0;不添加到输出中。

• --set-gtid-purged=ON：SET @@SESSION.SQL_LOG_BIN=0;添加到输出中。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --set-gtid-purged=off>set-gtid-purged.sql
[root@host102 tmp]# cat set-gtid-purged.sql 

#参数值为OFF，不记录gtid信息
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `ceshi`
--

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

USE `ceshi`;

--
-- Table structure for table `t1`
--

#参数值为ON，记录gtid信息

 --

-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED='9fef2262-97b1-11ea-92b5-000c29cd3ff3:1-6,
adc4403d-97b2-11ea-b803-000c298076e0:1-21';

--

-- Current Database: `ceshi`
--

格式选项

--hex-blob

使用十六进制表示法转储二进制列（例如， 'abc'变为 0x616263）。受影响的数据类型是 BINARY， VARBINARY， BLOB类型， BIT所有的空间数据类型，和其他非二进制数据类型与使用时 binary 的字符集。

如果数据库中有  BINARY， VARBINARY， BLOB这些类型，建议加个这个参数进行备份。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --hex-blob >hex-blob.sql
[root@host102 tmp]# cat hex-blob.sql 

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'2020-05-30 22:42:14',NULL),(2,'2020-05-31 00:39:01',0xE6),(3,'2020-05-31 00:42:06',0xE6),(4,'2020-05-31 00:42:31',0x61),(5,'2020-05-31 00:43:27',0x616263),(6,'2020-05-31 00:43:40',0xE6B58BE8AF95);
#每条记录最后一个字段都变成了0x xxxx之类的。

/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

--tab=dir_name， -T dir_name

产生制表符分隔的文本格式数据文件。对于每个转储的表，mysqldump创建一个tbl_name.sql 文件，该 文件包含CREATE TABLE创建表的语句，服务器写入一个tbl_name.txt 包含其数据的 文件。选项值是在其中写入文件的目录。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --databases ceshi --tab=dir_name=123 >1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: --databases or --all-databases can't be used with --tab.

#不能在--databases or --all-databases中使用--tab参数，看来只能对单表使用这个参数了

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 ceshi t1 --tab=/tmp >1.sql
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'

#对单表备份报错了，解决方法：修改my.cnf配置文件，加入  secure_file_priv='/'    重启mysql数据库。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 ceshi t1 --tab=/tmp >1.sql

[root@host102 tmp]#cat /tmp/t1.sql #存有建表语句

DROP TABLE IF EXISTS `t1`;

/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`sj` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`name` varbinary(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[root@host102 tmp]# cat /tmp/t1.txt  #存有数据
1 2020-05-30 22:42:14 N
2 2020-05-31 00:39:01
3 2020-05-31 00:42:06
4 2020-05-31 00:42:31 a
5 2020-05-31 00:43:27 abc
6 2020-05-31 00:43:40 测试
[root@host102 tmp]#

#文本间逗号隔开

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 ceshi t1 --tab=/tmp --fields-terminated-by=',' >1.sql
[root@host102 tmp]# cat /tmp/t1.txt
1,2020-05-30 22:42:14,N
2,2020-05-31 00:39:01,
3,2020-05-31 00:42:06,
4,2020-05-31 00:42:31,a
5,2020-05-31 00:43:27,abc
6,2020-05-31 00:43:40,测试
[root@host102 tmp]#

--tz-utc

此选项使TIMESTAMP 列可以在不同时区的服务器之间转储和重新加载。mysqldump将其连接时区设置为UTC并添加SET TIME_ZONE='+00:00'到备份文件中。如果没有此选项，则TIMESTAMP列将在源服务器和目标服务器本地的时区中转储并重新加载，如果服务器位于不同的时区中，则可能导致值更改。 --tz-utc还可以防止由于夏令时而导致的更改。--tz-utc默认情况下启用。要禁用它，请使用 --skip-tz-utc。

#以下是表中数据，注意看时间字段，类型是timestamp
mysql> select * from t1;
+----+---------------------+--------+
| id | sj                  | name   |
+----+---------------------+--------+
|  1 | 2020-05-31 06:42:14 | NULL   |
|  2 | 2020-05-31 08:39:01 |     |
|  3 | 2020-05-31 08:42:06 |     |
|  4 | 2020-05-31 08:42:31 | a      |
|  5 | 2020-05-31 08:43:27 | abc    |
|  6 | 2020-05-31 08:43:40 | 测试   |
+----+---------------------+--------+
6 rows in set (0.00 sec)

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 ceshi  >normal.sql  #--tz-utc参数默认是开启的
[root@host102 tmp]#  cat normal.sql 

-- MySQL dump 10.13 Distrib 5.7.29, for linux-glibc2.12 (x86_64)
--
-- Host: 127.0.0.1 Database: ceshi
-- ------------------------------------------------------
-- Server version 5.7.29-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;   #在备份文件中加入到设置0时区

...

...

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'2020-05-30 22:42:14',NULL),(2,'2020-05-31 00:39:01',_binary '(3,'2020-05-31 00:42:06',_binary '(4,'2020-05-31 00:42:31',_binary 'a'),(5,'2020-05-31 00:43:27',_binary 'abc'),(6,'2020-05-31 00:43:40',_binary '测试');  #注意看时间，都变成了0时区的时间，都少了8小时。
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

#关闭--tz-utc参数再看下，我本地是北京时间 时区是+08:00

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 ceshi --skip-tz-utc >skip-tz-utc.sql

[root@host102 tmp]# cat skip-tz-utc.sql 

-- MySQL dump 10.13 Distrib 5.7.29, for linux-glibc2.12 (x86_64)
--
-- Host: 127.0.0.1 Database: ceshi
-- ------------------------------------------------------
-- Server version 5.7.29-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

#中间这里已经没有了set time_zone='+00:00'

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

...

...

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'2020-05-31 06:42:14',NULL),(2,'2020-05-31 08:39:01',_binary '(3,'2020-05-31 08:42:06',_binary '(4,'2020-05-31 08:42:31',_binary 'a'),(5,'2020-05-31 08:43:27',_binary 'abc'),(6,'2020-05-31 08:43:40',_binary '测试');   #再看下数据，时间都已经是北京时间了，关闭tz-utc参数后，默认采的是操作系统时间。
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

筛选选项

--all-databases， -A

转储所有数据库中的所有表。这与使用该--databases选项并在命令行上命名所有数据库相同。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -A  >A.sql
[root@host102 tmp]# cat A.sql |grep "Current Database:"
-- Current Database: `ceshi`
-- Current Database: `chai`
-- Current Database: `mysql`
-- Current Database: `test`
#不会备份information_schema performance_schema 两个库，其它库都备份

--databases， -B

指定要备份的数据库，也可以指定information_schema  performance_schema 数据库，但备份这两个系统库需要加上 --skip-lock-tables参数。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -B ceshi information_schema  --skip-lock-tables  >B.sql  
[root@host102 tmp]# cat B.sql |grep "Current Database:" 
-- Current Database: `ceshi`
-- Current Database: `information_schema`
#查看到备份了两个库

--events， -E

加入此参数，会将evnet进行备份，但是只记录创建命令，这些语句不包括事件创建和修改时间戳记之类的属性。

--ignore-error=error[,error]...

忽略指定的起亚看，多个错误代码间用逗号隔开

--ignore-table=db_name.tbl_name

忽略要备份的表，必须同时使用数据库名和表名指定该表。要忽略多个表，请多次使用此选项。此选项也可用于忽略视图。

mysql> show tables;     #一共有3个表
+-----------------+
| Tables_in_ceshi |
+-----------------+
| t1              |
| t2              |
| t3              |
+-----------------+
3 rows in set (0.00 sec)


[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -B ceshi --ignore-table=ceshi.t3 --ignore-table=ceshi.t2 >it.sql  #忽略掉两个
[root@host102 tmp]# grep -E 't1|t2|t3' it.sql  #查看备份文件只有t1表
-- Table structure for table `t1`
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
-- Dumping data for table `t1`
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES xxxxx
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;

--no-data， -d

不备份数据，只备份表结构。

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -B ceshi -d >d.sql  
[root@host102 tmp]# grep 'INSERT' d.sql #文件中没有insert语句
[root@host102 tmp]# 

--routines， -R

加入此参数，会备份存储过程和函数。

--tables

需要使用--databases 或-B选项，--tables后边的所有参数，都将视为表名

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -B ceshi --tables t1 t2>d.sql    #只备份ceshi库的t1,t2表 
[root@host102 tmp]# grep -E 't1|t2|t3' d.sql  #备份文件中只有t1,t2没有t3
-- Table structure for table `t1`
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
-- Dumping data for table `t1`
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES xxxx/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
-- Table structure for table `t2`
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
-- Dumping data for table `t2`
LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;

--triggers

备份触发器，默认这个选项是开启的，--skip-triggers参数关闭备份触发器。

--where='where_condition'， -w 'where_condition'

只备份

WHERE条件选择的行 。如果条件中包含空格或其他命令解释程序特有的字符，则必须在条件周围加引号。 

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -B ceshi --tables t1 t2 --where="id>3">where.sql  #where条件可以对多个表生效，备份t1,t2表中id>3的记录。
[root@host102 tmp]# grep "INSERT" where.sql       
INSERT INTO `t1` VALUES (4,'2020-05-31 00:42:31',_binary 'a'),(5,'2020-05-31 00:43:27',_binary 'abc'),(6,'2020-05-31 00:43:40',_binary '测试');
INSERT INTO `t2` VALUES (4),(5),(6);
[root@host102 tmp]# 

--insert-ignore

写INSERT IGNORE语句而不是 INSERT语句

[root@host102 tmp]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -B ceshi --insert-ignore>ii.sql   #  
[root@host102 tmp]# grep 'INSERT' ii.sql
INSERT  IGNORE INTO `t1` VALUES (1,'2020-05-30 22:42:14',NULL),(2,'2020-05-31 00:39:01',_binary '(3,'2020-05-31 00:42:06',_binary '(4,'2020-05-31 00:42:31',_binary 'a'),(5,'2020-05-31 00:43:27',_binary 'abc'),(6,'2020-05-31 00:43:40',_binary '测试');
INSERT  IGNORE INTO `t2` VALUES (4),(5),(6);
INSERT  IGNORE INTO `t3` VALUES (1);

--max-allowed-packet=value

客户端/服务器通信缓冲区的最大大小。默认值为24MB，最大为1GB。

--net-buffer-length=value

用于客户端/服务器通信的缓冲区的初始大小。当创建多行 INSERT语句时（如使用--extended-insert或--opt选项）， mysqldump会创建长达--net-buffer-length字节的行 。如果增加此变量，请确保MySQL服务器net_buffer_length 系统变量的值至少为该值。

--opt

默认情况下启用的此选项是的组合的简写形式 。它提供了快速的转储操作并生成了一个转储文件，可以将其快速重新加载到MySQL服务器中。 --add-drop-table、 --add-locks、 --create-options、 --disable-keys、 --extended-insert、 --lock-tables、 --quick、 --set-charset

由于该--opt选项默认情况下处于启用状态，因此您只需指定其反选项 --skip-opt即可关闭多个默认设置。

事务设置

--add-locks

在备份表的时候加入 LOCK TABLES 和 UNLOCK TABLES语句。

 --flush-logs， -F

每次备份前刷新一下binlog文件，如果--all-databases 参数一起使用，每备份完一个数据库，会刷新一次binlog.

--lock-all-tables， -x

锁定所有数据库中的所有表。这是通过在整个转储期间获取全局读取锁定来实现的。此选项将自动关闭 --single-transaction和 --lock-tables。

--no-autocommit

将INSERT每个转储表的语句括在SET autocommit = 0和COMMIT 语句中。

--single-transaction

此选项将事务隔离模式设置为， REPEATABLE READ并START TRANSACTION在转储数据之前将SQL语句发送到服务器。它仅对诸如之类的事务表有用InnoDB，因为这样它在START TRANSACTION发布时转储数据库的一致状态， 而不会阻塞任何应用程序。

使用此选项时，应记住只有 InnoDB表以一致状态转储。例如，使用此选项时转储的任何表MyISAM或 MEMORY表可能仍会更改状态。

虽然 --single-transaction转储过程，以确保有效的转储文件（正确的表的内容和二进制日志坐标），没有其他的连接应使用以下语句： ALTER TABLE， CREATE TABLE， DROP TABLE， RENAME TABLE， TRUNCATE TABLE。一致的读取并非与这些语句隔离开，因此在要转储的表上使用它们可能会导致 mysqldump    SELECT执行的操作 检索表内容以获得不正确的内容或失败。

该--single-transaction选项和该 --lock-tables选项是互斥的，因为LOCK TABLES会导致任何未决事务隐式提交。

要转储大表，请将该--single-transaction选项与该--quick选项结合 使用 。

示例：

不锁表备份数据库所有数据， 同时将change master语句以注释的方式写入到备份文件中。

mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --all-databases --single-transaction --master-data=2>master.sql

备份所有数据库

mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --all-databases >all.sql

备份部分数据库

mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -B ceshi test >B.sql 

备份部分数据库表结构

mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -B ceshi test -d >Bd.sql

在使用mysqldump命令备份时，有一些参数选项默认是开启或关闭的，如不符合备份需求，需要手动指定参数值。

[root@host102 data]# mysqldump --help  #我只取了一部分内容，有一些参数默认是开启/关闭的。
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
all-databases                     FALSE
all-tablespaces                   FALSE
no-tablespaces                    FALSE
add-drop-database                 FALSE
add-drop-table                    TRUE
add-drop-trigger                  FALSE
add-locks                         TRUE
allow-keywords                    FALSE
apply-slave-statements            FALSE
bind-address                      (No default value)
character-sets-dir                (No default value)
comments                          TRUE
compatible                        (No default value)
compact                           FALSE
complete-insert                   FALSE
compress                          FALSE
create-options                    TRUE
databases                         FALSE
default-character-set             utf8
delete-master-logs                FALSE
disable-keys                      TRUE
dump-slave                        0
events                            FALSE
extended-insert                   TRUE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
flush-logs                        FALSE
flush-privileges                  FALSE
force                             FALSE
hex-blob                          FALSE
host                              (No default value)
ignore-error                      (No default value)
include-master-host-port          FALSE
insert-ignore                     FALSE
lines-terminated-by               (No default value)
lock-all-tables                   FALSE
lock-tables                       TRUE
log-error                         (No default value)
master-data                       0
max-allowed-packet                25165824
net-buffer-length                 1046528
no-autocommit                     FALSE
no-create-db                      FALSE
no-create-info                    FALSE
no-data                           FALSE
order-by-primary                  FALSE
port                              3306
quick                             TRUE
quote-names                       TRUE
replace                           FALSE
routines                          FALSE
set-charset                       TRUE
single-transaction                FALSE
dump-date                         TRUE
socket                            /tmp/mysql.sock
secure-auth                       TRUE
ssl                               TRUE
ssl-verify-server-cert            FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-crl                           (No default value)
ssl-crlpath                       (No default value)
tls-version                       (No default value)
server-public-key-path            (No default value)
get-server-public-key             FALSE
tab                               (No default value)
triggers                          TRUE
tz-utc                            TRUE
user                              (No default value)
verbose                           FALSE
where                             (No default value)
plugin-dir                        (No default value)
default-auth                      (No default value)
enable-cleartext-plugin           FALS