ubuntu

[ubuntu] MySQL outfile

Posted by Jarxi on 2019-07-03

Purpose is to output some data from MySQL to txt file with Ubuntu 18.04

MySQL outfile command

1
2
3
SELECT *
FROM information_schema.processlist
INTO OUTFILE '/var/www/html/tmp/output.txt'

However, I spent hours trying to work out why this query gives me nothing. Here are the steps to solve it.

  1. secure_file_priv: it comes with aws ubuntu 18.04 and is used to limit data import and export operations. A file can be only exported to the path set by secure_file_priv.

To see the current setting, login to MySQL shell as root user

1
mysql -u root -p

Then:

1
SHOW VARIABLES LIKE "secure_file_priv";

The result:

1
2
3
4
5
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

It means MySQL can only export to /var/lib/mysql-files/

But, I want it to export to /var/www/html/tmp. First I need to make the direction and find mysql config files to change the secure_file_priv variable.

1
mkdir /var/www/html/tmp

Then

1
mysql --help | grep "Default options" -A 1

Result:

1
2
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

I open all three files, only /etc/mysql/my.cnf contains useful information and it’s responsible for global configuration. So this is the file to edit.

Open this file:

1
sudo vim /etc/mysql/my.cnf

Set the variable to:

1
2
[mysqld]
secure-file-priv=/var/www/html/tmp

Restart mysql and check

1
2
3
sudo /etc/init.d/mysql restart
mysql -u root -p
mysql> SHOW VARIABLES LIKE "secure_file_priv";

The output should look like:

1
2
3
4
5
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/www/html/tmp/    |
+------------------+-----------------------+

Now, mysql is set. But if we run the query above, we see

1
ERROR 1 (HY000): Can't create/write to file '/var/www/html/temp/output.txt' (Errcode: 13 - Permission denied)
  1. Solve permission issues

First we should give write and read permission to mysql

1
2
chown mysql:mysql /var/www/html/tmp
chmod a+rw /var/www/html/tmp

After this, we need to update AppArmor profile. AppArmor is a Linux Kernel security module that restrict or permit certain actions.

1
cd /etc/apparmor.d

You should be able to see “usr.sbin.mysqld”

1
sudo vim usr.sbin.mysqld

Add these two lines under “# Allow data files dir access”

1
2
/var/www/html/tmp/ rw,
/var/www/html/tmp/** rw,

Reparse the profile

1
sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld
1
2
mysql
mysql> SELECT * FROM information_schema.processlist INTO OUTFILE '/var/www/html/tmp/output.txt'

https://computingforgeeks.com/how-to-solve-mysql-server-is-running-with-the-secure-file-priv-error/
https://serverfault.com/questions/896653/how-do-i-get-the-right-apparmor-profile-for-mysql-on-ubuntu


赏 支付宝打赏 微信打赏

赞赏一下



FEATURED TAGS
ubuntu