MySQL数据库的备份与恢复（4）——mysqldump参数详解

mysqldump是MySQL自带的逻辑备份命令，备份文件包含一组SQL语句，可以通过执行这些语句来生成备份前的数据库对象定义和表数据。mysqldump命令还可以生成CSV，其他分隔文本或XML格式的输出。

mysqldump命令的格式如下：

mysqldump [options] [db_name [tbl_name ...]]

常用的[options]参数如下：

1、指定登录用户名

-uuser_name --user=user_name

说明：指定与服务器连接时，MySQL使用的用户名。

2、指定登录主机

--host = host_name -h host_name

说明：该参数可以省略，默认主机是localhost。

3、指定登录密码

-pyour_pass --password[=your_pass]

说明：与服务器连接时使用的口令。

4、指定连接主机使用的端口号

-P port_num --port=port_num

说明：指定与主机连接时使用的TCP/IP端口号（用于连接到localhost以外的主机）。

5、备份所有的数据库

--all-databases -A

说明：与使用–databases选项并命名所有数据库相同。

6、备份指定的数据库

--databases db_name1 db_name2 ... -B db_name1 db_name2 ...

说明： （1）如果省略该选项，mysqldump将第一个名称参数视为数据库名称，将后续名称视为表名称，并且包含CREATE DATABASE和USE语句。 （2）使用该选项，则将所有名称参数视为数据库名称，CREATE DATABASE和USE语句包含在每个新数据库之前的输出中。 （3）该选项可用于备份INFORMATION_SCHEMA和performace_schema数据库，这两个数据库即使使用–all-databases选项也不会进行备份。

7、只备份表结构

-d --no-data

说明：只导出表结构，不包含表中的数据。

8、只备份表中的数据

-t --no-create-info

9、导出前刷新日志文件（生成新的binlog文件）

-F --flush-logs

说明：导出数据之后生成一个新的binlog文件，便于恢复数据。

10、导出满足指定条件的记录

-w --where='where-condition'

说明： （1）导出满足指定查询条件的记录。 （2）如果条件中包含单引号，则该参数可以使用双引号，比如：

[root@Mysql11 tmp]# mysqldump -uroot -p123456 hist stu --where="name='jack'" > /tmp/stu_name.sql; mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 tmp]# cat /tmp/stu_name.sql .......... -- -- Table structure for table `stu` -- DROP TABLE IF EXISTS `stu`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `stu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `phone` char(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `stu` -- -- WHERE: name='jack' LOCK TABLES `stu` WRITE; /*!40000 ALTER TABLE `stu` DISABLE KEYS */; INSERT INTO `stu` VALUES (3,'jack',20,'Zhengzhou','13675871454',1); /*!40000 ALTER TABLE `stu` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; .............. -- Dump completed on 2020-07-02 12:16:55

11、导出文件格式为文本文件

-T --tab=path-to-some-directory

说明：对于每个给定的表，创建一个table_name.sql文件（包含SQL CREATE 命令）和一个table_name.txt文件（包含数据）。

12、指定导出的文本文件的格式

--fields-enclosed-by=... --fields-optionally-enclosed-by=... --fields-escaped-by=... --fields-terminated-by=...

说明：这些选择与-T选择一起使用，并且有相应的LOAD DATA INFILE子句相同的含义。

例如：

[root@Mysql11 tmp]# mysqldump -uroot -p123456 hist stu --tab='/tmp' --fields-terminated-by=',' mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 tmp]# ls /tmp stu.sql stu.txt [root@Mysql11 tmp]# cat /tmp/stu.txt 1,zhangsan,20,Xinxiang,15578941258,1 2,tom,20,Xinxiang,13778942222,1 3,jack,20,Zhengzhou,13675871454,1 4,john,21,Zhengzhou,13937681111,2 5,mark,22,Aanyang,13055882233,2

13、保证导出数据的一致性状态

--single-transaction

说明： （1）该参数适合innoDB引擎的数据库备份； （2）innoDB表在备份时，通常启用选项–single-transaction来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：repeatable read，以确保本次会话备份时，不会看到其他会话已经提交了的数据。

14、指定导出文件的字符集

--default-character-set=name

例如：

[root@Mysql11 tmp]# mysqldump -uroot -p123456 --databases hist --default-character-set=utf8 > /tmp/hist.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.

15、将当前服务器binlog的位置和文件名追加到输出文件

--master-data=[1,2]

说明：该参数记录当前服务器的binlog，相当于执行show master status，状态（file，position）的值。

例如：

[root@Mysql11 tmp]# mysqldump -uroot -p123456 --master-data=1 hist > /tmp/hist.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@Mysql11 tmp]# cat /tmp/hist.sql ............. CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154; -- -- Table structure for table `course` -- ......................

16、将主库的binlog位置和文件名追加到导出数据的文件中

--dump-slave=[1,2]

说明： （1）如果当前服务器是从服务器，使用该命令会执行stop slave来获取master binlog的文件和位置，等备份完后会自动执行start slave启动从服务器。使用–dump-slave获取到的只是当前的从服务器的数据执行到的主的binglog的位置，即：relay_mater_log_file，exec_master_log_pos，而不是主服务器当前的binlog执行的位置，取决于主从的数据延时。 （2）该参数在从服务器上执行，相当于执行show slave status。 （3）当设置为1时，将会以CHANGE MASTER命令输出到数据文件，设置为2时，会在change前加上注释。