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

本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » mysqldump –tab产生文本格式备份与mysql启动选项–secure-file-priv的一点渊源

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址