DB-유

Changing the MySQL Data Directory on Linux

· mysql, linux, storage

My root partition filled up because /var/lib/mysql kept growing, and the fix was a new disk mounted at /data. Changing datadir is the easy part. Then SELinux (CentOS) or AppArmor (Ubuntu) quietly refuses to let mysqld touch the new location, and that's where I lose the evening.

This is what I do on CentOS 7 and Ubuntu 16.04 with MySQL 5.7; MariaDB 10.1 is the same with minor path differences. Have a mounted target filesystem with room to grow, a recent backup (a typo in rsync is the real risk), and a maintenance window, since the server has to be stopped for the copy.

Stop MySQL and note where it is now

SELECT @@datadir;

Then stop the service cleanly so InnoDB flushes everything:

# CentOS 7
sudo systemctl stop mysqld
# Ubuntu 16.04
sudo systemctl stop mysql
sudo systemctl status mysqld   # confirm "inactive (dead)"

Copy the data

Use rsync -a to preserve ownership, permissions and timestamps. Note the trailing slash on the source; without it you would end up with /data/mysql/mysql.

sudo mkdir -p /data/mysql
sudo rsync -av /var/lib/mysql/ /data/mysql/
sudo chown -R mysql:mysql /data/mysql

Don't delete the old directory yet. I always rename it so I can fall back quickly:

sudo mv /var/lib/mysql /var/lib/mysql.bak

Point the server at the new directory

On CentOS 7, edit /etc/my.cnf; on Ubuntu 16.04, edit /etc/mysql/mysql.conf.d/mysqld.cnf. Change or add these lines under [mysqld]:

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock

If you change the socket path, the client needs to know about it too. Add a [client] section in the same file:

[client]
socket=/data/mysql/mysql.sock

Honestly I prefer to leave the socket where it was (/var/lib/mysql/mysql.sock on CentOS, /var/run/mysqld/mysqld.sock on Ubuntu), because then no application config has to change. In that case keep the socket setting unchanged and only move datadir; create an empty /var/lib/mysql owned by mysql so the socket has somewhere to live.

SELinux on CentOS 7

With SELinux in enforcing mode, mysqld may only write to files labelled mysqld_db_t. Label the new path permanently and apply it:

sudo semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
sudo restorecon -Rv /data/mysql

If semanage is missing, install it with sudo yum install policycoreutils-python. Check the result with ls -Z /data/mysql; the files should show mysqld_db_t.

AppArmor on Ubuntu 16.04

AppArmor confines mysqld with the profile in /etc/apparmor.d/usr.sbin.mysqld. Rather than editing that file (it gets replaced on package upgrades), add an alias in /etc/apparmor.d/tunables/alias:

echo 'alias /var/lib/mysql/ -> /data/mysql/,' | sudo tee -a /etc/apparmor.d/tunables/alias
sudo systemctl restart apparmor

The alias tells AppArmor that every rule written for /var/lib/mysql/ also applies to /data/mysql/.

Start it and check

sudo systemctl start mysqld     # or mysql on Ubuntu
sudo systemctl status mysqld

SELECT @@datadir, @@socket;
SHOW TABLES FROM mysql;

@@datadir must show /data/mysql/. Run a quick write to be sure the server can create files:

CREATE DATABASE dirtest; CREATE TABLE dirtest.t (id INT); INSERT INTO dirtest.t VALUES (1); DROP DATABASE dirtest;

I wait a few days before removing the old copy:

sudo rm -rf /var/lib/mysql.bak

If the service won't start

  • mysqld: Can't create/write to file '/data/mysql/ib_logfile0' (Errcode: 13 - Permission denied) in the error log on CentOS: SELinux is blocking access. Check sudo ausearch -m avc -ts recent and redo Step 4. Setting setenforce 0 confirms the diagnosis but is not a fix.
  • The same error on Ubuntu, with apparmor="DENIED" lines in /var/log/syslog: the alias was not loaded. Run sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld and try again.
  • ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock': the server started, but the client is looking at the old socket path. Add the [client] socket line or leave the socket where it was.
  • On Ubuntu, mysqld starts and immediately exits with [ERROR] Could not open file '/var/log/mysql/error.log': unrelated to the move, but it means the log directory was lost. Recreate it with mkdir -p /var/log/mysql && chown mysql:adm /var/log/mysql.

Check the audit log first. It's almost always the security framework.