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