Mysql – Can’t start thesqld service after changing datadir

datadirlinuxMySQLredhat

I have an AWS EC2 instance with RHEL 7 on which I installed mysql-5.5 on xvda. Its datadir is under /var/lib/mysql (as is the default).

I am importing dumps from a database on version 5.5.29 and the currently installed version is 5.5.60

A larger volume xvdf has been attached since I was running out of space and mounted it at /data_drive

Then followed these steps to change the data directory:

  • Stop mysqld and verify if it had stopped
  • used rsync -av /var/lib/mysql /data_drive to move folder with permissions
  • moved the older mysql data directory to a backup file on the xvda (original volume)

  • Edited /etc/my.cnf to change the [mysqld] block to reflect the new location in both datadir and socket

    datadir=/data_drive/mysql
    socket=/data_drive/mysql/mysql.sock

  • added the client block at the end of the file

    [client]
    port=3306
    socket=/data_drive/mysql/mysql.sock

  • saved the file and then tried to restart the mysqld service as root user and non-root user but it won't start.

Here is the /var/log/mysqld.log output from the relevant timestamps:

180713 19:27:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
180713 20:20:37 mysqld_safe Logging to '/var/log/mysqld.log'.
180713 20:20:37 mysqld_safe Starting mysqld daemon with databases from /data_drive/mysql
180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test
180713 20:20:37 [Note] /usr/sbin/mysqld (mysqld 5.5.60) starting as process 18669 ...
180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test
180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test
180713 20:20:37 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
180713 20:20:37 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
180713 20:20:37 InnoDB: The InnoDB memory heap is disabled
180713 20:20:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180713 20:20:37 InnoDB: Compressed tables use zlib 1.2.3
180713 20:20:37 InnoDB: Using Linux native AIO
180713 20:20:37 InnoDB: Initializing buffer pool, size = 128.0M
180713 20:20:37 InnoDB: Completed initialization of buffer pool
180713 20:20:37  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

When I ran sudo mysql_upgrade it gave the following output:

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Error: Failed while fetching Server version! Could be due to unauthorized access.
FATAL ERROR: Upgrade failed

This is how the /data_drive/mysql directory looks at the moment:

[ec2-user@ip-10-0-0-180 mysql]$ ls -al /data_drive/mysql/
total 5921812
drwxr-xr-x. 4 mysql    mysql       4096 Jul 13 18:45 .
drwxr-xr-x. 5 ec2-user root        4096 Jul 13 18:46 ..
-rw-rw----. 1 mysql    mysql 6053429248 Jul 13 18:45 ibdata1
-rw-rw----. 1 mysql    mysql    5242880 Jul 13 18:45 ib_logfile0
-rw-rw----. 1 mysql    mysql    5242880 Jul 13 18:35 ib_logfile1
drwx------. 2 mysql    root        4096 Jul 12 19:42 mysql
drwx------. 2 mysql    mysql       4096 Jul 12 19:42 performance_schema

Best Answer

This is likely to be an issue with SELinux. If SELinux is running in enforcing mode, you need to to persuade it to allow mysqld to use the non-default datadir. Or alternatively, put SELinux into permissive mode, or disable it entirely. Run sestatus to see which mode it's currently running in. Run grep mysql /var/log/audit/audit.log to see if SELinux has blocked any mysql file access attempts or similar. (But note that this is fine if running in permissive mode, because then it doesn't actually block anything, it just logs what it would have blocked if you were running in enforcing mode.)

Run setenforce permissive to put SELinux into permissive mode, though note you need to edit /etc/sysconfig/selinux (or /etc/selinux/config) to make the change permanent.

If you go the route of trying to persuade SELinux to allow the new datadir, you need to make sure the SELinux context is kept for the files. I suspect this may be lost when using rsync, but not sure.

Here's a page explaining in detail how to deal with SELinux when moving the MySQL datadir: How to move MySQL data directory on CentOS and Ubuntu

Related Question