Simple solution to mysqldump: Got error: 1290 and secure-file-priv option

Today, a netizen asked, I encountered 1290 of the errors when backing up with mysqldump. Here is the error message I simulated.

[root@potato Desktop] # mysqldump-uroot-proot-S / tmp/mysql.sock-- tab=/data/mysql/mytest_3306/data/backup lala Warning: Using a password on the command line interface can be insecure. 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'

You can clearly see from the prompt that it cannot be executed because the mysql service is enabled-- secure-file-priv. So-- what is secure-file-priv and how to solve it so that it can be backed up? Secure-file-priv=name: Limit LOAD DATA, SELECT... OUTFILE, and LOAD_FILE () to files within specified directory can see that the secure-file-priv parameter is used to restrict LOAD DATA, SELECT. OUTFILE, and LOAD_FILE () to which specified directory. When the value of secure_file_priv is null, mysqld is not allowed to import | Export when the value of secure_file_priv is / tmp/, it means to restrict the import of mysqld | Export can only occur in the / tmp/ directory. When the value of secure_file_priv has no specific value, it means that the import of mysqld is not restricted | Export is not restricted.

View the current value of this parameter in the database, root@localhost:mysql.sock 00:14:52 [(none)] > show global variables like'% secure%' +-+-+ | Variable_name | Value | +-+-+ | secure_auth | ON | | secure_file_priv | NULL | +-+-+ 2 rows in set (0.00 sec)

You can clearly see that the value of secure_file_priv is NULL, indicating that the import and export is restricted at this time, so you should change this parameter. However, if you look at the fact that this parameter is not set in mysql.cnf, it means that this parameter defaults to null, so [mysqld] in mysql.cnf adds secure_file_priv = restart the mysql service.

Then check again the value of the parameter root@localhost:mysql.sock 00:28:30 [(none)] > show global variables like'% secure%' +-+-+ | Variable_name | Value | +-+-+ | secure_auth | ON | | secure_file_priv | | +-+-+ 2 rows in set (0.00 sec) is already the desired result

Start exporting [root@potato Desktop] # mysqldump-uroot-proot-S / tmp/mysql.sock-- tab=/data/mysql/mytest_3306/data/backup lala Warning: Using a password on the command line interface can be insecure. You can see the success.