每天进步一点达mysqldump_MySQL

一、简介

mysqldump是客户端用来备份数据库或者在不通数据库之间进行数据迁移的工具，备份内容包含创建表或者装载表的SQL语句

二、命令格式

备份单个数据库或者数据库中部分数据表

mysqldump [参数] 数据库名 [表名]

备份指定的一个或者多个数据库

mysqldump [参数] --database db1 [db2db3]

备份所有数据库

mysqldump [参数] --all-database

三、常用参数

-u--user=name 用户名

-p --password 密码

-h --host=hostname 指定服务器IP或者可解析到的域名

-P--port 端口

--add-drop-database 每个数据库创建语句前加上drop database，用于创建新库前将旧库删掉

--add-drop-table 在每个创建表前加上drop table语句，用于创建新表前删除旧表
-n--no-create-db 不包含数据库创建语句

-t --no-create-info 不包含数据表创建语句

-d --no-data 不包含数据

--compact 将输出的结果简洁，不包括默认选项中的各种注释

-c--complete-insert 在insert语句中加上字段名（默认不加）
-F --flush-logs 备份前刷新日志，使得方便恢复。

-l --lock-tables 给所有表添加读锁保证数据一致性一般与-F配合使用

-T 此参数与下面参数配合使用，此参数是将数据备份为淡出的数据文本文档和建表的SQL两个文件

--filelds-terminated-by=name(域分隔符)

--filelds-enclosed-by=name（域引用符）

--fields-optionally-enclosed-by=name(域可选引用符）

--fields-escaped-by=name(转义字符）

--default-characte-set=字符类型此选项可以设置导出的客户端字符集，系统默认客户端字符集可以通过 mysql --verbose --help |grep 'default-character-set'|grep-v name来查看所以一般导出的时候一定要将字符集设置对。

四、常用例子

1) 备份T4表到test4.txt

[root@localhost data]# mysqldump -h10.22.19.44 -uroot-p test2 t4 >test4.txt

2) 备份教务表

[root@localhostdata]# mysqldump -h10.22.19.44 -uroot -p jiaowu >jiaowu.txt

3) 只导出表的创建语句，不包含其他信息

[root@localhostdata]# mysqldump -h10.22.19.44 -uroot -p -d test2 t4 >nodata.txt

Enter password:

[root@localhost data]# cat nodata.txt

-- MySQL dump 10.13 Distrib 5.6.12, for linux-glibc2.5 (x86_64)

-- Host: 10.22.19.44 Database: test2

--------------------------------------------------------

-- Server version 5.6.12-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 `t4`

DROP TABLE IF EXISTS `t4`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `t4` (

`id` int(10) NOT NULL DEFAULT '0',

`str_number` 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 */;

/*!40014 SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-07-17 14:53:49

4) 只导出表的创建语句，并且添加删除旧表的语句

[root@localhostdata]# mysqldump -h10.22.19.44 -uroot -p -d --add-drop-table jiaowu tutors>nooldtable.txt

Enter password:

[root@localhostdata]# cat nooldtable.txt

-- MySQL dump 10.13 Distrib 5.6.12, for linux-glibc2.5 (x86_64)

-- Host: 10.22.19.44 Database: jiaowu

-- ------------------------------------------------------

-- Server version 5.6.12-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 `tutors`

DROP TABLE IF EXISTS `tutors`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `tutors` (

`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`Tname` varchar(50) NOT NULL,

`Gender` enum('F','M') DEFAULT 'M',

`Age` tinyint(3) unsigned DEFAULT NULL,

UNIQUE KEY `TID` (`TID`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULTCHARSET=latin1;

/*!40101 SET character_set_client =@saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-07-17 14:57:03

5) 将上面的结果通过简洁的方式输出

[root@localhostdata]# mysqldump -h10.22.19.44 -uroot -p -d --compact --add-drop-table jiaowututors >compactnooldtable.txt

Enter password:

[root@localhostdata]# cat compactnooldtable.txt

DROP TABLE IF EXISTS `tutors`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `tutors` (

`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`Tname` varchar(50) NOT NULL,

`Gender` enum('F','M') DEFAULT 'M',

`Age` tinyint(3) unsigned DEFAULT NULL,

UNIQUE KEY `TID` (`TID`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULTCHARSET=latin1;

/*!40101 SET character_set_client =@saved_cs_client */;

6) 通过简洁方式将tutors导出，并且在insert into语句上添加字段名

[root@localhostdata]# mysqldump -h10.22.19.44 -uroot -p -c jiaowu tutors>completetutors.txt

Enterpassword:

[root@localhostdata]# cat completetutors.txt

-- MySQL dump 10.13 Distrib 5.6.12, for linux-glibc2.5 (x86_64)

-- Host: 10.22.19.44 Database: jiaowu

--------------------------------------------------------

-- Server version 5.6.12-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 `tutors`

DROP TABLE IF EXISTS `tutors`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `tutors` (

`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`Tname` varchar(50) NOT NULL,

`Gender` enum('F','M') DEFAULT 'M',

`Age`tinyint(3) unsigned DEFAULT NULL,

UNIQUE KEY `TID` (`TID`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULTCHARSET=latin1;

/*!40101 SET character_set_client =@saved_cs_client */;

-- Dumping data for table `tutors`

LOCK TABLES `tutors` WRITE;

/*!40000 ALTER TABLE `tutors` DISABLE KEYS*/;

INSERT INTO `tutors` (`TID`, `Tname`,`Gender`, `Age`) VALUES(1,'HongQigong','M',93),(2,'HuangYaoshi','M',63),(3,'Miejueshitai','F',72),(4,'OuYangfeng','M',76),(5,'YiDeng','M',90),(6,'YuCanghai','M',56),(7,'Jinlunfawang','M',67),(8,'HuYidao','M',42),(9,'NingZhongze','F',49),(10,'TOM','F',30),(11,'DingDian','M',25),(12,'HuFei','M',31),(13,'Xuzhu','M',26),(18,'DingDian','M',25),(19,'HuFei','M',31),(20,'Xuzhu','M',26),(23,'stu0010','M',NULL),(24,'stu0012','M',NULL),(25,'str000023','M',NULL),(26,'str000024','M',NULL),(27,'str9999','M',NULL),(28,'str9991','M',NULL),(29,'str9992','M',NULL),(30,'str9993','M',NULL);

/*!40000 ALTER TABLE `tutors` ENABLE KEYS*/;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS*/;

/*!40101 SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-07-17 15:03:30

[root@localhost data]# mysqldump-h10.22.19.44 -uroot -p --compact -c jiaowu tutors >completetutors.txt

Enter password:

[root@localhost data]# catcompletetutors.txt

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `tutors` (

`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`Tname` varchar(50) NOT NULL,

`Gender` enum('F','M') DEFAULT 'M',

`Age` tinyint(3) unsigned DEFAULT NULL,

UNIQUE KEY `TID` (`TID`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULTCHARSET=latin1;

/*!40101 SET character_set_client =@saved_cs_client */;

7) 将tutors导出为单纯的文本文件和建表SQL

[root@localhostdata]# mkdir bak

[root@localhostdata]# chown mysql:mysql ./bak/

[root@localhostdata]# mysqldump -uroot -p123456 jiaowu tutors -T ./bak/

Warning: Using a password on the commandline interface can be insecure.
[root@localhost data]# cat./bak/tutors.sql

-- MySQL dump 10.13 Distrib 5.6.12, for linux-glibc2.5 (x86_64)

-- Host: localhost Database: jiaowu

--------------------------------------------------------

-- Server version 5.6.12-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' */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0 */;

-- Table structure for table `tutors`

DROP TABLE IF EXISTS `tutors`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `tutors` (

`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`Tname` varchar(50) NOT NULL,

`Gender` enum('F','M') DEFAULT 'M',

`Age` tinyint(3) unsigned DEFAULT NULL,

UNIQUE KEY `TID` (`TID`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULTCHARSET=latin1;

/*!40101 SET character_set_client =@saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40101 SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-07-17 15:14:21

[root@localhostdata]# cat ./bak/tutors.txt

1 HongQigong M 93

2 HuangYaoshi M 63

3 Miejueshitai F 72

4 OuYangfeng M 76

5 YiDeng M 90

6 YuCanghai M 56

7 Jinlunfawang M 67

8 HuYidao M 42

9 NingZhongze F 49

10 TOM F 30

11 DingDian M 25

12 HuFei M 31

13 Xuzhu M 26

18 DingDian M 25

19 HuFei M 31

20 Xuzhu M 26

23 stu0010 M /N

24 stu0012 M /N

25 str000023 M /N

26 str000024 M /N

27 str9999 M /N

28 str9991 M /N

29 str9992 M /N

30 str9993 M /N

在向目录中导出文件时，有时候会提示下面错误

[root@localhost data]# mysqldump -uroot-p123456 test2 t4 -T ./bak/

Warning: Using a password onthe command line interface can be insecure.

mysqldump: Got error: 1: Can'tcreate/write to file '/usr/local/mysql/data/bak/t4.txt' (Errcode: 13 -Permission denied) when executing 'SELECT INTO OUTFILE'

报出此错是由于目录没有足够权限导致，只需给目录足够权限即可

[root@localhost data]# chownmysql:mysql ./bak/
[root@localhost data]# mysqldump -uroot -p123456 jiaowu tutors -T ./bak/
Warning: Using a password on the command lineinterface can be insecure.

8) 导出含有中文的数据，不加参数会导致数据中有乱码

[root@localhostdata]# mysqldump -uroot -p123456 --compact jiaowu tutors >test5

Warning: Using a password on the commandline interface can be insecure.

[root@localhostdata]# cat test5

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `tutors` (

`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`Tname` varchar(200) DEFAULT NULL,

`Gender`enum('F','M') DEFAULT 'M',

`Age` tinyint(3) unsigned DEFAULT NULL,

UNIQUE KEY `TID` (`TID`)

) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULTCHARSET=latin1;

/*!40101 SET character_set_client =@saved_cs_client */;

INSERT INTO `tutors` VALUES (1,'HongQigong','M',93),(2,'HuangYaoshi','M',63),(3,'Miejueshitai','F',72),(4,'OuYangfeng','M',76),(5,'YiDeng','M',90),(6,'YuCanghai','M',56),(7,'Jinlunfawang','M',67),(8,'HuYidao','M',42),(9,'NingZhongze','F',49),(10,'TOM','F',30),(11,'DingDian','M',25),(12,'HuFei','M',31),(13,'Xuzhu','M',26),(18,'DingDian','M',25),(19,'HuFei','M',31),(20,'Xuzhu','M',26),(23,'stu0010','M',NULL),(24,'stu0012','M',NULL),(25,'str000023','M',NULL),(26,'str000024','M',NULL),(27,'str9999','M',NULL),(28,'str9991','M',NULL),(29,'str9992','M',NULL),(30,'str9993','M',NULL),(32,'??-???','M',21),(33,'?–ˉ?-','M',21),(34,'????-','M',21);

可以看到导出的数据是乱码

[root@localhostdata]# mysqldump -uroot -p123456 --compact --default-character-set=latin1jiaowu tutors >test6

Warning: Using a password on the commandline interface can be insecure.

[root@localhostdata]# cat test6

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `tutors` (

`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`Tname` varchar(200) DEFAULT NULL,

`Gender` enum('F','M') DEFAULT 'M',

`Age` tinyint(3) unsigned DEFAULT NULL,

UNIQUE KEY `TID` (`TID`)

) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULTCHARSET=latin1;

/*!40101 SET character_set_client =@saved_cs_client */;

INSERT INTO `tutors` VALUES(1,'HongQigong','M',93),(2,'HuangYaoshi','M',63),(3,'Miejueshitai','F',72),(4,'OuYangfeng','M',76),(5,'YiDeng','M',90),(6,'YuCanghai','M',56),(7,'Jinlunfawang','M',67),(8,'HuYidao','M',42),(9,'NingZhongze','F',49),(10,'TOM','F',30),(11,'DingDian','M',25),(12,'HuFei','M',31),(13,'Xuzhu','M',26),(18,'DingDian','M',25),(19,'HuFei','M',31),(20,'Xuzhu','M',26),(23,'stu0010','M',NULL),(24,'stu0012','M',NULL),(25,'str000023','M',NULL),(26,'str000024','M',NULL),(27,'str9999','M',NULL),(28,'str9991','M',NULL),(29,'str9992','M',NULL),(30,'str9993','M',NULL),(32,'中国','M',21),(33,'疯子','M',21),(34,'傻子','M',21);

可以看到设置编码后，导出的数据为正确的中文字符

9) 备份前锁定表并且刷新备份日志

备份前查看二进制日志号：

mysql>show master status;

+--------------+----------+--------------+------------------+-------------------+

+--------------+----------+--------------+------------------+-------------------+

| mysql.000009 | 120 | | | |

+--------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

[root@localhostdata]# mysqldump -uroot -p123456 --compact -F -l jiaowu >jiaowu.sql

Warning: Using a password on the commandline interface can be insecure.

备份后查看二进制日志号

mysql>show master status;

+--------------+----------+--------------+------------------+-------------------+

+--------------+----------+--------------+------------------+-------------------+

| mysql.000011 | 120 | | | |

+--------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

我靠这里居然滚动了俩日志，我一直没搞明白为什么滚动俩日志，下将12导出请大家解决小弟问题

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

#at 4

#150717 16:06:16 server id 1 end_log_pos 120 CRC32 0x8f464488 Start: binlog v 4, server v 5.6.12-log created 150717 16:06:16

BINLOG '

eLeoVQ8BAAAAdAAAAHgAAAAAAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYhE

Ro8=

'/*!*/;

#at 120

#150717 16:06:16 server id 1 end_log_pos 163 CRC32 0xf497faf6 Rotate to mysql.000013 pos: 4

DELIMITER ;

#End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

上一篇：学会读懂MySql的慢查询日志_MySQL
下一篇：浅谈MySQL中的触发器_MySQL