mysqldump(--tab)参数使用

1.尝试导出整个库
[root@localhost data]# mysqldump --set-gtid-purged=OFF --tab=/tmp/data db_hxl -u root -pmysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED='0b23e9fb-0ef2-11e9-8450-525400f3712a:1-32';
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
[root@localhost data]#


[root@localhost data]# mysqldump --set-gtid-purged=OFF --tab=/tmp/data db_hxl -u root -pmysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

secure-file-priv的值有三种情况：
secure_file_prive=null    ––限制mysqld 不允许导入导出
secure_file_priv=/path/   --限制mysqld的导入导出只能发生在默认的/path/目录下
secure_file_priv=''       --不对mysqld 的导入 导出做限制

2.修改初始化参数加入如下项
secure_file_priv=''

3.重启动
[root@localhost ~]# mysqladmin -h localhost -uroot -pmysql shutdown
/opt/mysql5730/bin/mysqld --defaults-file=/opt/mysql5730/conf/my.cnf --user=mysql &



4.继续报错
[root@localhost ~]# mysqldump --set-gtid-purged=OFF --tab=/tmp/data db_hxl -u root -pmysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1: Can't create/write to file '/tmp/data/flush_test.txt' (Errcode: 13 - Permission denied) when executing 'SELECT INTO OUTFILE'

修改目录权限给mysql用户
chown -R mysql:mysql /tmp/data

5.再次导出
[root@localhost ~]# mysqldump --set-gtid-purged=OFF --tab=/tmp/data db_hxl -u root -pmysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost data]# ls -al
total 24
drwxrwxr-x. 2 mysql mysql  176 Nov  9 18:22 .
drwxrwxrwt. 9 root  root   172 Nov  9 18:18 ..
-rw-rw-r--. 1 root  root  2468 Nov  9 18:22 flush_test.sql
-rw-rw-rw-. 1 mysql mysql   82 Nov  9 18:22 flush_test.txt
-rw-rw-r--. 1 root  root  2738 Nov  9 18:22 rule_01.sql
-rw-rw-rw-. 1 mysql mysql    0 Nov  9 18:22 rule_01.txt
-rw-rw-r--. 1 root  root  2485 Nov  9 18:22 tb_index_test.sql
-rw-rw-rw-. 1 mysql mysql    0 Nov  9 18:22 tb_index_test.txt
-rw-rw-r--. 1 root  root  2342 Nov  9 18:22 tb_test.sql
-rw-rw-rw-. 1 mysql mysql   66 Nov  9 18:22 tb_test.txt

可以看到整个库下的表都导出了

文件说明:
*.sql --创建表的语句
*.txt --文件内容,格式如下
[root@localhost data]# more flush_test.txt
1       name1
2       name2
3       name3
4       name4
5       name5
6       name6
7       name7
8       name8
9       name9
10      name10

 

说明:

导出单个表

mysqldump --set-gtid-purged=OFF --tab=/tmp/data db_hxl flush_test -u root -pmysql

库名:db_hxl

表名:flush_test