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帐户的用户名。
选项文件选项
仅使用给定的选项文件。如果文件不存在或无法访问，则发生错误。
不要读取任何选项文件。
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.sqlCREATE 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。
用于客户端/服务器通信的缓冲区的初始大小。当创建多行 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
