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
