Shulou(Shulou.com)06/01 Report--

1. Use mysqldump-- tab to generate a backup of the database in text file format [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. Check-- secure-file-priv option meaning [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. Close mysql server [root@mygirl ~] # / usr/local/mysql/bin/mysqladmin shutdown-u root-p

Enter password:

4. Fixed option-- secure-file-priv restart 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. It seems that the result of the option modification is unreasonable. The backup still reported an error [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. Close mysql server [root@mygirl ~] # / usr/local/mysql/bin/mysqladmin shutdown-u root-p again

Enter password:

[root@mygirl ~] #

7. Modify-- secure-file-priv again, that is, if the configuration is empty, you can produce a backup [root@mygirl ~] # / usr/local/mysql/bin/mysqld_safe-- secure-file-priv= & in text format.

[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=/root test root tab can only specify directories with ownership of mysql users and groups, otherwise it will report an error [root@mygirl ~] # / usr/local/mysql/bin/mysqldump-- tab=/root test-u directory-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, the backup in text format is in the directory specified by-- tab, and each table in the database has two files with different extensions, each of which is .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, it can be seen that the above .sql and .txt files correspond to the script of the definition table of each table and the actual data of the table [root@mygirl ~] # cd / usr/local/mysql/data, respectively.

[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 _ room`

--

DROP TABLE IF EXISTS `t _ room`

/ *! 40101 SET @ saved_cs_client = @ @ character_set_client * /

/ *! 40101 SET character_set_client = utf8 * /

CREATE TABLE `t _ room` (

`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

one

two

three

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.