Why am I getting an access denied error on the second file (a text file) for mysqldump using the tab option?

Question

I am trying to backup a mysql db on Ubuntu using the --tab option using the following:

mysqldump -umy_user my_database -p --tab=/path/to/backup/dir

I enter the password and then get the following error:

mysqldump: Got error: 1045: Access denied for user 'my_user'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE'

The password is okay, and File is in the list from show privileges for the user. Additionally it looks like I can produce .sql files, but it dies on the first .txt file. Also a mysqldump to just a plain .sql file works.

After running the command and seeing the error, if I ls the backup dir I see one sql file that looks correct of structure, but it seems to have died trying to write the first txt file of table contents.

I created an empty dir for the dump ahead of time. I tried chmoding it to 777 and before that I tried chowning it to mysql:mysql.

Answer 1

While this works on my end for a problem with the exact same symptoms, this apparently does not solve the OP's problem. I'm leaving this here for reference and for other people who get this error.

Problem:

You will get this error if your MySQL user does not have FILE PRIVILEGE_TYPE, which is separate from file system permissions and not listed by show privileges (see documentation below). You can check all of your user's permissions by running the following query after connecting using the mysql command.

SHOW GRANTS FOR CURRENT_USER();

Or, enter this handy one-liner into your shell.

echo 'SHOW GRANTS FOR CURRENT_USER();' | mysql -u my_user -p

If you have these permissions, you will get a response containing GRANT FILE or GRANT ALL ON *.*, on a line resembling the following.

GRANT FILE ON *.* TO 'user'@'host' IDENTIFIED BY PASSWORD '*hash'

If you do not receive a line like this, you will need to grant the user this permission.

Alternately, you can check that your user has these permissions in the USER_PRIVILEGES table of the information_scheme database.

Solution:

To permission your user, log in as a sufficiently-privileged user, and run the following command.

GRANT FILE ON *.* to 'my_user'@'localhost';

Documentation:

From the mysqldump documentation for --tab=path, -T path:

Note

This option should be used only when mysqldump is run on the same machine as the mysqld server. You must have the FILE privilege, and the server must have permission to write files in the directory that you specify.

Answer 2

Looks like apparmor is causing this.

Since the mysql user has the correct permission, and you tried setting the file permissions and owners, the thing that is left is something other than mysql or file permissions. On Ubuntu this would be apparmor.

To verify that mysqld is effected, try:

> sudo aa-status

...
2 processes are in enforce mode.
/usr/sbin/mysqld (1182)
...

Once verified, you have to tell apparmor to allow mysqld to write to your backup dir. The permissions are stored in: /etc/apparmor.d/usr.sbin.mysqld

...
/usr/sbin/mysqld {
    ....
    /run/mysqld/mysqld.sock w,
    /path/to/backup/dir/** w,
}

You can add write permission to the directory you want to backup as in the example above.

And just for reference, here is the directory ownership that was required in addition to the apparmor changes:

chown -R mysql:mysql /path/to/backup/dir

As a final note that sheds a little more light on why the first file was written and then errored out, the ownership of the dumped files was not uniform. For whatever reason the sql files were owned by the shell user running the command and the txt files were owned by mysql:

> ls -al /path/to/backup/dir
...
-rw-r--r-- 1 my_user my_user  1596 Feb  2 01:24 wp_terms.sql
-rw-rw-rw- 1 mysql   mysql    5117 Feb  2 01:24 wp_terms.txt
...