1,使用MySQLdump --tab可以對(duì)數(shù)據(jù)庫(kù)產(chǎn)生文本文件格式的備份

[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選項(xiàng)含義

[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，關(guān)閉mysql server

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

4,修正選項(xiàng)--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,看來(lái)選項(xiàng)修改的結(jié)果不合理，備份仍舊報(bào)錯(cuò)

[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，再次關(guān)閉mysql server

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

7,再次修改--secure-file-priv,即配置為空，可以產(chǎn)生文本格式的備份

[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只能指定所有權(quán)為mysql用戶(hù)及組的目錄，否則報(bào)錯(cuò)

[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指定的目錄，并且數(shù)據(jù)庫(kù)中每個(gè)表皆有2個(gè)不同擴(kuò)展名的文件，各為.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,可見(jiàn)上述.sql和.txt文件分別對(duì)應(yīng)每個(gè)表的定義表的腳本及表的實(shí)質(zhì)數(shù)據(jù)

[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





網(wǎng)頁(yè)標(biāo)題：mysqldump--tab產(chǎn)生文本格式備份與mysql啟動(dòng)選項(xiàng)--secure-file-priv的一點(diǎn)淵源
鏈接分享：http://weahome.cn/article/jpchje.html