mysqldump --tab產生文字格式備份與mysql啟動選項--secure-file-priv的一點淵源

1,使用mysqldump --tab可以對資料庫產生文字檔案格式的備份

[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 ~]# 

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