mysqldump --tab产生文本格式备份与mysql启动选项
发布时间：2022-03-22 11:45:45 所属栏目：MySql教程 来源：互联网
导读：mysqldump --tab产生文本格式备份与mysql启动选项： 1,使用mysqldump --tab可以对数据库产生文本文件格式的备份 [root@mygirl ~]# /usr/local/mysql/bin/mysqldump --tab=/root test -u root -p Enter password: mysqldump: Got error: 1290: The MySQL se
|
mysqldump --tab产生文本格式备份与mysql启动选项： 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'. 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]# （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
热点阅读