MySQL工具【mysqldump 数据导出、mysqlimport 数据导入】

01. mysqldump 数据导出

mysqldump 客户端工具用来备份数据库或在不同的数据库之间进行数据迁移。备份内容包含创建表或装载表的sql语句。mysqldump目前是mysql中最常用的备份工具。

常见的3种备份方式：

# 备份单个数据库或者库中部分数据表
mysqldump [options] db_name [tables]
# 备份指定的一个或多个数据库
mysqldump [options] --databases DB1 [DB2,DB3,...]
# 备份所有数据库
mysqldump [options] --all-databases

01. 连接选项

-u, --user=name			指定用户名
-p, --password[=name]	指定密码
-h, --host=name			指定服务器IP或域名
-P, --port=N			指定连接端口

这几个选项通常组合使用（库名可没有参数）：

mysqldump -h192.168.7.55 -P3306 -uroot -pAAA test > test.sql

/.../mysqldump -uroot -pAAA -S/data/.../mysql.sock --hex-blob --opt --routines --triggers --single-transaction --master-data=2 --set-gtid-purged=OFF --databases db1,db2,... > /backup/mysqldump_xxx.sql

02. 输出内容选项

--add-drop-database		每个数据库创建语句前加上 drop database 语句
--add-drop-table   		每个表创建语句前加上 drop table 语句

以上两个选项可以在导入数据库时不用先手工删除旧的数据库，而是会自动删除，提高导入效率，但是导入前一定要做好备份并且确认旧数据库的确已经删除，否则误操作将会造成数据的损失。在默认情况下，这两个参数都会自动加上。

-n, --no-create-db		不包含数据库的创建语句
-t, --no-create-info	不包含数据表的创建语句
-d, --no-data			不包含数据

根据不同的场景，可以选择不同的参数。例如只导出创建表的语句，不导出数据。

mysqldump -uroot --compact -d test t2 > 666.sql

03. 输出格式选项

--compact 选项使得输出结果简洁，不包含默认选项中的各种注释（ --add-drop-database，--add-drop-table 的语句也会隐藏）。

[root@ufo128 mysql]# mysqldump -uroot --compact test t2 > 666.sql
[root@ufo128 mysql]# 
[root@ufo128 mysql]# cat 666.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t2` VALUES (1),(1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3);

-c 或 --complete-insert 选项使得输出文件中的insert语句包括字段名称，默认是不包括字段名称的。

[root@ufo128 mysql]# mysqldump -uroot --compact -c test t2 > 666.sql
[root@ufo128 mysql]# cat 666.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t2` (`id`) VALUES (1),(1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3);

-T 选项将指定数据表中的数据备份为单纯的数据文本和建表SQL两个文件，经常和下面几个选项一起配合使用，将数据导出为指定格式显示。

-T, --tab=name						备份数据和建表语句，指定导出的位置
--fields-terminated-by				字段分割符          
--fields-enclosed-by             	字段引号
--fields-optionally-enclosed-by  	可选的字段引号
--fields-escaped-by              	转义符号

示例：备份到指定目录下（XXX.sql、XXX.txt）

[mysql@ufo128 ~]$ mysqldump -uroot -p test t2 -T ./bak
Enter password: 


[mysql@ufo128 ~]$ ls -l ./bak/
total 8
-rw-rw-r-- 1 mysql mysql 1295 Feb 17 18:54 t2.sql
-rw-rw-rw- 1 mysql mysql   40 Feb 17 18:54 t2.txt


[mysql@ufo128 ~]$ cat ./bak/t2.sql 
-- MySQL dump 10.13  Distrib 5.1.73, for redhat-linux-gnu (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.1.73
/*!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' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- 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` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!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 2021-02-17 18:54:01


[mysql@ufo128 ~]$ cat ./bak/t2.txt 
1
1
3
1
3
1
2147483647
3
1
2147483647
3

04. 字符集选项

--default-character-set 可以设置导出的客户端字符集，系统默认的客户端字符集可以通过下面命令查看：

[mysql@ufo128 ~]$ mysqldump --help | grep -E '^default-character-set'
default-character-set             utf8

这个选项在导出数据库的时候非常重要，如果客户端字符集和数据库字符集不一致，数据在导出的时候就需要进行字符集转换，将数据库字符集转换为客户端字符集，经过转化后的数据很可能成为乱码或"?"等特殊字符，使得备份文件无法恢复。

mysql> show create table ufo123;
+--------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                      |
+--------+-----------------------------------------------------------------------------------------------------------------------------------+
| ufo123 | CREATE TABLE `ufo123` (
  `id` varchar(30) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from ufo123;
+--------+--------+
| id     | name   |
+--------+--------+
| 中国	 | 美国	  |
+--------+--------+
1 row in set (0.00 sec)

中文乱码

[mysql@ufo128 ~]$ mysqldump -uroot -p --compact test ufo123 > ufo123.sql
Enter password: 
[mysql@ufo128 ~]$ 
[mysql@ufo128 ~]$ cat ufo123.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ufo123` (
  `id` varchar(30) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `ufo123` VALUES ('中国','美国');

中文正常

[mysql@ufo128 ~]$ mysqldump -uroot -p --compact --default-character-set=latin1 test ufo123 > ufo123.sql
Enter password: 
[mysql@ufo128 ~]$ cat ufo123.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ufo123` (
  `id` varchar(30) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `ufo123` VALUES ('中国','美国');

当前数据库的字符集

mysql> show variables like '%chara%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

05. 其他选项

# 备份前将关闭旧日志，生成新日志。使得恢复的时候直接从新日志开始进行重做，大大方便了恢复过程（有大事务进行时，刷新会影响性能）
-F, --flush-logs		备份前刷新日志

# 可以在备份期间使用，使得数据无法被更新，从而使备份的数据保持一致，可以配合-F选项一起使用（不停库，只读操作，进行临时一致性备份）
-l, --lock-tables		给所有表加读锁

02. mysqlimport 数据导入

mysqlimport 是客户端数据导入工具，用来导入 mysqldump 加-T选项后导出的文本文件。它实际上是客户端 load data infileql 语句的一个命令行接口。

详见：备份与恢复

注意：也可以直接加载 source mysqldump_xxx.sql 文件。