Failed to dump MySQL Database /tmp file permission denied

Posted on

In this article, the main point of the discussion is about solving the problem occurred. Actually the problem is concerning on the failure process of dumping a MySQL database file. There is a failure which is caused by the permission problem on creating the temporary file as the destination target of the file. Based on the log file generated named ‘mysqld.log located in /var/log, it can be clearly well-informed that the problem lies on the permission of the target of the dump file for creating or writing a temporary file fo dumping the database :

[root@hostname ~]# tail -f /var/log/mysqld.log
/usr/libexec/mysqld: Can't create/write to file '/tmp/ib8zunMA' (Errcode: 13)
170510 14:58:11  InnoDB: Error: unable to create temporary file; errno: 13
170510 14:58:11 [ERROR] Plugin 'InnoDB' init function returned error.
170510 14:58:11 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170510 14:58:11 [ERROR] Unknown/unsupported storage engine: InnoDB
170510 14:58:11 [ERROR] Aborting

170510 14:58:11 [Note] /usr/libexec/mysqld: Shutdown complete

170510 14:58:11 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

The location of the dump file is actually depends on the parameter given when the dump process is executed. But on the above context, it is believed that before assembling the real database dump file, a temporary file is being generated for the sake of setting up the real MySQL database dump file. The file is located in /tmp which is normally u use to generate a temporary file. But based on the above output, generating a temporary file for dumping database is forbidden.

To solve the problem, the only logic way is by changing the default temporary folder used to generate temporary file in the database dumping process. To change that folder location,  in this context, in order to solve the problem, just try to edit the file which is used as a MySQL Database Configuration file. It is a file named my.cnf normally located in /etc. Below is the line which is added to specify temporary directory to generate temporary backup database as follows :

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
sql_mode        =
skip-external-locking

Change the following line :

tmpdir = /tmp

into the following :

tmpdir = /home/user

But apparently, the output of dumping MySQL Database file is still generating the same result. But there is a different with the error message generated above. The output generated can be viewed and can be shown as seen below :

[root@hostname mysql]# tail -f /var/log/mysqld.log
/usr/libexec/mysqld: Can't create/write to file '/home/user/ib8zunMA' (Errcode: 13)
170510 14:58:11  InnoDB: Error: unable to create temporary file; errno: 13
170510 14:58:11 [ERROR] Plugin 'InnoDB' init function returned error.
170510 14:58:11 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170510 14:58:11 [ERROR] Unknown/unsupported storage engine: InnoDB
170510 14:58:11 [ERROR] Aborting

170510 14:58:11 [Note] /usr/libexec/mysqld: Shutdown complete

170510 14:58:11 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

The above output still has the same problem which is being unable to dump MySQL Database file  despite of the target location of the directory which has been changed from /tmp to /home/user. It is quite understandable since the owner of /home/user is not mysql user. So the complete solution is not only changing the folder of the temporary directory for generating database dump file but also selecting the one which is appropriate and it is permitted with the proper permission. In this case, a folder which has a full permission for mysql user is located in /var/log/mysql. So, change the value of the parameter tmpdir in my.cnf as follows :

tmpdir = /var/log/mysql

Don’t forget to restart the service after changing MySQL Configuration file. It can be down as shown below :

root@hostname:/var/lib/mysql# systemctl restart mysql.service
root@hostname:/var/lib/mysql# 

After successfully restart MySQL service, execute the following program just to test whether the dump process can be normally executed :

[root@hostname mysql]# mysqldump -uuser -p dbname > /root/dbdump_file_name.sql
Enter password:
[root@hostname mysql]#

As it can be shown above, the process of dumping MySQL database has completely success.

One thought on “Failed to dump MySQL Database /tmp file permission denied

Leave a Reply