mysql mysqldump 恢复_MySQL备份恢复之mysqldump的使用

Mysqldump是MySQL数据库逻辑备份的常用工具，在日常的维护工作中经常会用到。这里对这个工具的使用做一个简单的介绍。有以下3种方法来调用mysqldump：

(1)备份指定的数据库，或者此数据库中某些表。

shell> mysqldump [options] db_name [tables]

(2)备份指定的一个或多个数据库。

shell> mysqldump [options] ---database DB1 [DB2 DB3...]

(3)备份所有数据库。

shell> mysqldump [options] –all--databases

如果没有指定数据库中的任何表，默认导出所有数据库中所有表。

根据备份选项中是否带有--tab,mysqldump工具支持2种备份格式。

l不带--tab，备份内容全部都在一个sql文件中，包括库、表等对象的创建，以及表内的数据。数据是以SQL文本的格式存放。

l带--tab，备份内容按表分成2个文件。建表语句在.sql文件中，而数据则以纯文本的方式按特定的分割符存储在一个后缀为.txt的文本中。

一、SQL文本格式

1.1备份

默认情况下，mysqldump会将备份内容输出到标准输出设备。我们可以将其保存到一个文件中。

shell> mysqldump --databases kevin > dump.sql

示例如下(省略部分输出的注释)：

[root@oracle11g ~]# mysqldump -uroot –proot --databases kevin > a.sql

[root@oracle11g ~]# more a.sql

-- MySQL dump 10.13Distrib 5.6.21, for Linux (x86_64)

--

-- Host: localhostDatabase: kevin

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

-- Server version5.6.21-enterprise-commercial-advanced-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: `kevin`

--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `kevin` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `kevin`;

--

-- Table structure for table `emp`

--

DROP TABLE IF EXISTS `emp`;

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

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `emp` (

`ename` varchar(10) DEFAULT NULL,

`hiredate` date DEFAULT NULL,

`sal` decimal(10,2) DEFAULT NULL,

`deptno` int(2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--

-- Dumping data for table `emp`

--

LOCK TABLES `emp` WRITE;

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

INSERT INTO `emp` VALUES ('kevin',NULL,12.00,1);

/*!40000 ALTER TABLE `emp` 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 2015-04-07 18:23:41

如果只需要导出一个数据库，--databases选项是可以省略的。

但是要注意一点，如果不带--databases选项，输出中是不会包含创建数据库的语句的。如果需要输出建库语句，则一定要带--databases选项。

shell> mysqldump kevin > dump.sql

如果只导出特定的几张表，在数据库名后面带上所需要导出的表名就可以了。

shell> mysqldump kevin t1 t3 t7 > dump.sql

1.2恢复

如果在使用mysqldump备份时，使用了--databases或者--all-databases选项，那么导出的文本中会带有create database和use语句。这样我们就不需要指定导入的数据库了。

我们可以用mysql客户端直接导入：

shell>mysql < dump.sql

或者在mysql里面，使用source命令导入：

mysql>source dump.sql

如果我们导出的文本中没有数据库创建命令，那么我们需要先创建数据库，并在导入时指定数据库名：

shell>mysqladmin create db1

shell>mysql db1 < dump.sql

或者在mysql客户端内：

mysql>CREATE DATABASE IF NOT EXISTS db1;

mysql>USE db1;

mysql>source dump.sql

二、分割文本格式

2.1备份

如果在调用mysqldump进行备份的时候使用了--tab=dir_name选项，那么mysqldump会在dir_name指定的目录下面为每张表创建2个文件。比如以表emp为例，备份后会生成emp.sql和emp.txt。其中.sql文件包含建表语句，.txt文件包含表的数据，一个数据代表表中的一行。

[root@oracle11g mysql]# mysqldump -uroot -proot --tab=/tmp/mysql kevin

mysqldump: Got error: 1: Can't create/write to file '/tmp/mysql/emp.txt' (Errcode: 13 - Permission denied) when executing 'SELECT INTO OUTFILE'

这里看到报了个权限错误，这是因为导出时的.txt文件是由mysql服务调用select into outfile命令来写入的，因此服务启动的用户必须有指定目录的写入权限才行。

[root@oracle11g mysql]# ps -ef | grep mysql

root382410 Mar31 ?00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/oracle11g.pid

mysql393738240 Mar31 ?00:00:14 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/oracle11g.err --pid-file=/var/lib/mysql/oracle11g.pid

root532152890 17:55 pts/100:00:00 mysql -uroot -p

root549142590 19:08 pts/000:00:00 grep mysql

[root@oracle11g mysql]# cd ..

[root@oracle11g ~]# chown -R mysql:mysql mysql

[root@oracle11g ~]# cd mysql

(1)将kevin库下面的emp表备份到/tmp/mysql目录下。

[root@oracle11g mysql]# mysqldump -uroot -proot --tab=/tmp/mysql kevin emp

(2)可以发现生成了两个文件，一个是.sql，另一个是.txt。

[root@oracle11g mysql]# ll

total 8

-rw-r--r-- 1 mysql mysql 1430 Apr7 19:19 emp.sql

-rw-rw-rw- 1 mysql mysql35 Apr7 19:19 emp.txt

(3)查看两个文件的内容，.sql文件存放了建表语句，而.txt文件存放了实际的数据。

[root@oracle11g mysql]# more emp.sql

-- MySQL dump 10.13Distrib 5.6.21, for Linux (x86_64)

--

-- Host: localhostDatabase: kevin

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

-- Server version5.6.21-enterprise-commercial-advanced-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 `emp`

--

DROP TABLE IF EXISTS `emp`;

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

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `emp` (

`ename` varchar(10) DEFAULT NULL,

`hiredate` date DEFAULT NULL,

`sal` decimal(10,2) DEFAULT NULL,

`deptno` int(2) DEFAULT NULL

) ENGINE=InnoDB 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 2015-04-07 19:19:45

[root@oracle11g mysql]# more emp.txt

kevin\N12.001

gideon\N15.002

使用以下几个选项可以格式化txt文本中的数据输出风格。

?--fields-terminated-by=name#域分隔符

?--fields-enclosed-by=name#域引用符

?--fields-optionally-enclosed-by=name#域可选引用符

?--fields-escaped-by=name#转义字符

[root@oracle11g mysql]# mysqldump -uroot -proot --tab=/tmp/mysql --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a kevin emp

[root@oracle11g mysql]# more emp.txt

"kevin",\N,"12.00","1"

"gideon",\N,"15.00","2"

2.2恢复

对于分割文本的方式备份出来的数据，包含有2个文件。因此恢复的时候，一般是先处理.sql文件，生成一个空表。然后再将.txt文本里面的具体数据导入到表中。

shell> mysql kevin < emp.sql

shell> mysqlimport kevin emp.txt

导入数据部分，也可以在mysql客户端中用LOAD DATA INFILE命令处理：

mysql> USE kevin;

mysql> LOAD DATA INFILE 'emp.txt' INTO TABLE emp;

注意，如果备份使用使用了特殊的格式化处理，那么在导入的时候，也要使用相同的格式。

shell> mysqlimport --fields-terminated-by=,

--fields-enclosed-by='"' --lines-terminated-by=0x0d0a kevin emp.txt

或者

mysql> USE kevin;

mysql> LOAD DATA INFILE 'emp.txt' INTO TABLE emp

-> FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"'

-> LINES TERMINATED BY '\r\n';

三、常用选项介绍

3.1连接选项

–u , --user=name#指定用户名

–p , --password[=name]#指定密码

–h , --host=name#指定服务器IP或者域名

–P , --port=##指定连接端口

这4个选项经常一起配合使用，如果客户端位于服务器上，通常不需要指定host。如果不指定端口，默认连接到3306端口，以下是一个远程客户端连接到服务器的例子：

Shell > mysqldump –h localhost-P3306 -uroot -p kevin > a.sql

Enter password: **********

3.2输出内容选项

--add-drop-database#每个数据库创建语句前加上DROP DATABASE语句

--add-drop-table#在每个表创建语句前加上DROP TABLE语句

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

-n, --no-create-db#不包含数据库的创建语句

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

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

这3个选项分别表示备份文件中不包含数据库的创建语句、不包含数据表的创建语句、不包含数据，在不同的场合下，我们可以根据实际需求来进行选择。

3.3输出格式选项

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

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

下面的例子中对kevin数据库中的表emp使用此选项进行导出：

[root@oracle11g mysql]# mysqldump -uroot -p -c --compact kevin emp > emp.sql

Enter password:

[root@oracle11g mysql]# more emp.sql

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

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `emp` (

`ename` varchar(10) DEFAULT NULL,

`hiredate` date DEFAULT NULL,

`sal` decimal(10,2) DEFAULT NULL,

`deptno` int(2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `emp` (`ename`, `hiredate`, `sal`, `deptno`) VALUES ('kevin',NULL,12.00,1),('gideon',NULL,15.00,2);

--fields-terminated-by=name#域分隔符

--fields-enclosed-by=name#域引用符

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

--fields-escaped-by=name#转义字符

这几个选项我们在前面2.1小节部分已经介绍过了。一般和-T选项配合使用。

3.4字符集选项

--default-character-set=name #选项可以设置导出的客户端字符集。

系统默认的客户端字符集，可以通过以下命令来查看：

[root@oracle11g mysql]# mysql -verbose --help | grep 'default-character-set' | grep -v name

default-character-setauto

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

下面是一个字符集导出中文的例子：

(1)测试表emp的字符集为latin1，里面有一条中文记录。

mysql> show create table emp\G;

*************************** 1. row ***************************

Table: emp

Create Table: CREATE TABLE `emp` (

`ename` varchar(10) DEFAULT NULL,

`hiredate` date DEFAULT NULL,

`sal` decimal(10,2) DEFAULT NULL,

`deptno` int(2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> select * from emp;

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

| ename| hiredate | sal| deptno |

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

| kevin| NULL| 12.00 |1 |

| gideon | NULL| 15.00 |2 |

|项| NULL| 20.00 |3 |

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

3 rows in set (0.00 sec)

(2)用默认客户端字符集导出表emp。

[root@oracle11g mysql]# mysqldump --verbose --help | grep 'default-character-set' | grep -v name

default-character-setutf8

[root@oracle11g mysql]# mysqldump -uroot -p --compact kevin emp > emp.sql

Enter password:

[root@oracle11g mysql]# more emp.sql

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

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `emp` (

`ename` varchar(10) DEFAULT NULL,

`hiredate` date DEFAULT NULL,

`sal` decimal(10,2) DEFAULT NULL,

`deptno` int(2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `emp` VALUES ('kevin',NULL,12.00,1),('gideon',NULL,15.00,2),('é??',NULL,20.00,3);

可以看到，“项”已经是乱码了。

(3)手工设置客户端字符集为latin1，重新导出。

[root@oracle11g mysql]# mysqldump -uroot -p --compact --default-character-set=latin1 kevin emp > emp.sql

Enter password:

[root@oracle11g mysql]# more emp.sql

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

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `emp` (

`ename` varchar(10) DEFAULT NULL,

`hiredate` date DEFAULT NULL,

`sal` decimal(10,2) DEFAULT NULL,

`deptno` int(2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `emp` VALUES ('kevin',NULL,12.00,1),('gideon',NULL,15.00,2),('项',NULL,20.00,3);

这次中文字符已经可以成功导出。

3.5其他常用选项：

-F--flush-logs#备份前刷新日志

加上此选项后，备份前将关闭旧日志，生成新日志。使得进行恢复的时候直接从新日志开始进行重做，大大方便了恢复过程。

-x, --lock-all-tables

在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction和--lock-tables选项。

-l--lock-tables#给所有表加读锁

它和--lock-all-tables类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。可以在备份期间使用，使得数据无法被更新，从而使备份的数据保持一致性，可以配合-F选项一起使用。本选项只适用于MyISAM表，如果是Innodb表可以用--single-transaction选项。

-q, --quick #快速导出

该选项在导出大表时很有用，它强制mysqldump从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

--single-transaction

该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如InnoDB和BDB。本选项和--lock-tables选项是互斥的，因为LOCK TABLES会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick选项。