使用mysqldump以SQL格式来dump数据

Using mysqldump for Backups
使用mysqldump工具可以以以下几种方式来生成dump文件:
.作为备份在数据丢失的情况下来执行数据恢复
.作为源数据用来配置复制从服务器
.作为源数据用来进行实验:
-为了复制数据库而不用改变源数据
-为了测试潜在的升级后不兼容的问题

mysqldump生成两种类型的输出，这依赖于是否使用–tab选项:
.不使用–tab选项，mysqldump将输出SQL语句到标准输出。这类输出由创建dump对象(数据库，表，存储过程等)的create语句组成，并且包含向表加载数据的insert语句。输出可以被保存大文件中并且之后可以使用mysql来重新加载来重新创建被dump的对象。有一些选项可用于修改SQL语句的格式，以及控制转储哪些对象。

.使用–tab选项，mysqldump将为每个被dump的对象生成两个输出文件。服务器以制表符分隔的文本形式写入一个文件用来存储表记录，每条记录一行。这个文件名为tbl_name.txt被保存输出目录中。服务器还会在输出目录中生成一个名为tbl_name.sql的文件用来存储create table语句。

使用mysqldump以SQL格式来dump数据
默认情况下，mysqldump以SQL语句来作为标准输出。可以将输出保存到文件中:

为了dump所有数据库，可以使用–all-databases选项来调用mysqldump

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 --all-databases > all_db_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
为了dump指定的数据库，可以使用–databases选项来指定

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 --databases mysql query_rewrite > dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
–databases选项会让命令行中的所有名字作为数据库名字来对待。不使用这个选项，mysqldump将第一个名字作为数据库名后面的作为表名对待。

使用–all-databases或–databases选项，mysqldump在dump每个数据库之前会写入create database和use语句。这确保了当dump文件被重新加载时，如果不存在数据库时会创建数据库并使它作为默认数据库使用数据库的内容被加载到相同的数据库中。如果想让dump文件在重建数据之前强制删除每个数据库，可以使用–add-drop-database选项。在这种情况下，mysqldump会在每个create database语句之前写一个drop database语句。

为了dump单个数据库，在命令行中指定数据库名:

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 --databases mysql > dump_mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
在dump单个数据库的情况下，可以忽略–databases选项:

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 mysql > dump_mysql_1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[mysql@localhost ~]$ more dump_mysql.sql
– MySQL dump 10.13 Distrib 5.7.26, for Linux (x86_64)

– Host: localhost Database: mysql

---

– Server version 5.7.26-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 */;

–
– Current Database: mysql

CREATE DATABASE /!32312 IF NOT EXISTS/ mysql /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE mysql;

–
– Table structure for table columns_priv

[mysql@localhost ~]$ more dump_mysql_1.sql
– MySQL dump 10.13 Distrib 5.7.26, for Linux (x86_64)

– Host: localhost Database: mysql

---

– Server version 5.7.26-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 */;

–
– Table structure for table columns_priv

上面两个命令之间的差异就在于不使用–databases选项时，dump输出不会包含create database或use语句。这有几种含义:
.当重新加载dump文件时，你必须指定一个默认数据库名因此服务器就会知道那个数据库会被加载。
.为了重新加载，可以指定一个不同于原始数据库名的数据库名，这能让你加载数据到不同的数据库中。
.如果被加载的数据库不存在，你必须先创建数据库。
.因为输出将不会包含create database语句，–add-drop-database选项没有影响。如果使用它，将不会生成drop database语句。

为了dump一个数据库中的指定表，在命令行中指定数据库名后指定表名:

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 mysql t t1 t2 > dump_tables.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ cat dump_tables.sql
– MySQL dump 10.13 Distrib 5.7.26, for Linux (x86_64)

– Host: localhost Database: mysql

---

– Server version 5.7.26-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 */;

–
– Table structure for table t

DROP TABLE IF EXISTS t;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t (
id int(11) NOT NULL,
name varchar(20) NOT NULL,
date date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t

LOCK TABLES t WRITE;
/*!40000 ALTER TABLE t DISABLE KEYS /;
INSERT INTO t VALUES (1,‘jingyong’,NULL),(2,‘yeyali’,NULL),(3,‘huangyan’,NULL),(4,‘wenyao’,NULL);
/!40000 ALTER TABLE t ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t1

DROP TABLE IF EXISTS t1;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t1 (
c1 char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t1

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

–
– Table structure for table t2

DROP TABLE IF EXISTS t2;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t2 (
i int(10) NOT NULL,
PRIMARY KEY (i)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t2

LOCK TABLES t2 WRITE;
/*!40000 ALTER TABLE t2 DISABLE KEYS /;
INSERT INTO t2 VALUES (0),(1);
/!40000 ALTER TABLE t2 ENABLE KEYS /;
UNLOCK TABLES;
/!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

– Dump completed on 2020-07-10 17:19:42

重新加载SQL格式备份
为了重新加载由mysqldump工具生成的由SQL语句组成的dump文件，使用这个dump文件作为mysql客户端的输入。如果dump文件是由使用–all-databases或–databases选项的mysqldump命令所生成，它将包含create database和use语句并且不需要为加载的数据指定默认数据库。

[mysql@localhost ~]$ mysql -uroot -pabcd1234 mysql < dump_tables.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
一种替代的方式是通过在mysql命令提示符下使用source命令:

mysql> source /var/lib/mysql/dump_tables.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
±—±---------±-----+
| id | name | date |
±—±---------±-----+
| 1 | jingyong | NULL |
| 2 | yeyali | NULL |
| 3 | huangyan | NULL |
| 4 | wenyao | NULL |
±—±---------±-----+
4 rows in set (0.00 sec)

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

mysql> select * from t2;
±–+
| i |
±–+
| 0 |
| 1 |
±–+
2 rows in set (0.00 sec)
如果dump文件是单数据库dump且不包含create database和use语句，那么首先需要创建数据库(必须的):

shell> mysqladmin create db1
然后在加载dump文件时指定数据库名:

shell> mysql db1 < dump.sql
另一种方式，在mysql提示符中，创建数据库，选它作为默认数据库，然后加载dump文件:

mysql> create database if not exists db1;
mysql> use db1;
mysql> source /var/lib/mysql/dump_tables.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)