转载

mysqldump --tab产生文本格式备份与mysql启动选项--secure-file-priv的一点渊源

1,使用mysqldump --tab可以对数据库产生文本文件格式的备份

2,查看--secure-file-priv选项含义

[root@mygirl ~]# /usr/local/mysql/bin/mysqld --verbose --help|grep -i --color secure-file-priv
180103 20:45:01 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
180103 20:45:01 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.58) starting as process 4922 ...
180103 20:45:01 [Note] Plugin 'FEDERATED' is disabled.
--secure-file-priv=name
secure-file-priv NULL
[root@mygirl ~]#

3，关闭mysql server

[root@mygirl ~]# /usr/local/mysql/bin/mysqladmin shutdown -u root -p
Enter password:

4,修正选项--secure-file-priv重启mysql server

[root@mygirl ~]# /usr/local/mysql/bin/mysqld_safe --secure-file-priv=/usr/local/mysql &
[1] 5335
[root@mygirl ~]# 180103 21:25:57 mysqld_safe Logging to '/usr/local/mysql/data/mygirl.err'.
180103 21:25:57 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

5,看来选项修改的结果不合理，备份仍旧报错

[root@mygirl ~]# /usr/local/mysql/bin/mysqldump --tab=/root test -u root -p
Enter password:
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@mygirl ~]#

mysql> show variables like '%secure_file%';
+------------------+-------------------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------------------+
| secure_file_priv | /usr/local/mysql-5.5.58-linux-glibc2.12-x86_64/ |
+------------------+-------------------------------------------------+
1 row in set (0.00 sec)

6，再次关闭mysql server

[root@mygirl ~]# /usr/local/mysql/bin/mysqladmin shutdown -u root -p
Enter password:
[root@mygirl ~]#

7,再次修改--secure-file-priv,即配置为空，可以产生文本格式的备份

[root@mygirl ~]# /usr/local/mysql/bin/mysqld_safe --secure-file-priv= &
[1] 5488
[root@mygirl ~]# 180103 21:30:23 mysqld_safe Logging to '/usr/local/mysql/data/mygirl.err'.
180103 21:30:23 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@mygirl ~]#

+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | |
+------------------+-------+
1 row in set (0.00 sec)

8，--tab只能指定所有权为mysql用户及组的目录，否则报错

[root@mygirl ~]# /usr/local/mysql/bin/mysqldump --tab=/root test -u root -p
Enter password:
mysqldump: Got error: 1: Can't create/write to file '/root/t_commit.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
[root@mygirl ~]#

9，文本格式的备份在--tab指定的目录，并且数据库中每个表皆有2个不同扩展名的文件，各为.sql and .txt

[root@mygirl ~]# /usr/local/mysql/bin/mysqldump --tab=/usr/local/mysql/data test -u root -p
Enter password:
[root@mygirl ~]#

[root@mygirl ~]# ll /usr/local/mysql/data/t_*
-rw-r--r--. 1 root root 1311 Jan 3 21:31 /usr/local/mysql/data/t_commit.sql
-rw-rw-rw-. 1 mysql mysql 6 Jan 3 21:31 /usr/local/mysql/data/t_commit.txt
-rw-r--r--. 1 root root 1308 Jan 3 21:31 /usr/local/mysql/data/t_other.sql
-rw-rw-rw-. 1 mysql mysql 0 Jan 3 21:31 /usr/local/mysql/data/t_other.txt

10,可见上述.sql和.txt文件分别对应每个表的定义表的脚本及表的实质数据

[root@mygirl ~]# cd /usr/local/mysql/data
[root@mygirl data]# more t_commit.sql
-- MySQL dump 10.13 Distrib 5.5.58, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.5.58

/*!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 `t_commit`
--

DROP TABLE IF EXISTS `t_commit`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_commit` (
`a` int(11) 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 2018-01-03 21:31:42
[root@mygirl data]#

[root@mygirl data]# more t_commit.txt
1
2
3

正文到此结束