How to move MySQL to another drive in CentOS

This article covers the basic steps of moving a database to a new folder. It includes moving the db, as well as resetting SELinux/Apparmor policies.

Depending on the OS configuration, MySQL may be running the /root mount where it is installed by default. There may be more drive space available in the /home directory.

In this case, we’re simply going to move MySQL to /home. This process will also work if you’ve added another drive.

Check Drive Space

df -f or df -aTh

You will get something like this:

Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_servername-lv_root
50G 18G 29G 39% /
tmpfs 3.9G 0 3.9G 0% /dev/shm
/dev/sda1 477M 143M 309M 32% /boot
/dev/mapper/vg_servername-lv_home
144G 59G 78G 43% /home

There’s a lot more space in /home so let’s move it there.

Moving the Database

Edit my.cnf to check install location

sudo nano /etc/my.cnf

Default (CentOS) is usually:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

You can also check through mysql:

SELECT @@datadir;

We will move both datadir and socket

Exit nano ctrl-x

Our backup will be the original mysql folder for now, so no need.

Stop MySQL

sudo service mysqld stop
sudo systemctl stop mysqld

Permissions for the folders are critical so we’re going to move the folders with permissions using rsync

sudo yum -y install rsync

Copy it. Make sure to NOT include the slash on the destination folder

sudo rsync -av /var/lib/mysql /home/mysql

Check your work:

sudo ls -la /home/mysql

Note the mysql mysql ownership

drwxr-xr-x. 23 mysql mysql 4096 Jul 10 13:56 .
drwxr-xr-x. 7 root root 4096 Jul 2 13:55 ..
drwx------. 2 mysql mysql 4096 Apr 14 2016 somedb
drwx------. 2 mysql mysql 4096 Sep 27 2018 somedb2
drwx------. 2 mysql mysql 4096 Dec 14 2015 somedb3
etc…

If you don’t see that you will need to make sure it is owned by mysql:

sudo chown mysql:mysql -R /home/mysql/

Edit my.cnf

sudo nano /etc/my.cnf

Make sure to comment out the old directories and leave the data and your initials (ctrl+k ctrl+u to copy/paste in nano)

#datadir=/var/lib/mysql 7-10-20 JF
#socket=/var/lib/mysql/mysql.sock 7-10-20 JF
datadir=/home/mysql
socket=/home/mysql/mysql.sock

If you login locally you might get this error:

Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect

This is because mysql.sock has been moved. The simple solution is to create a symlink:

ln -s /home/mysql/mysql.sock /var/lib/mysql/mysql.sock

You can also try adding this to my.cnf (note that you must put this below the log-error and pid-file statements.

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

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

Make sure to stop and restart mysql

As usual, there’s another way:

usermod -d /home/mysql/ mysql

Modifying SeLinux

Before modifying SEL, let’s try to start MySQL without it.

Check SEL:

sudo getenforce
Enforcing
sudo setenforce 0
sudo getenforce
Permissive

sudo service mysqld start

sudo systemctl start mysqld

Starting mysqld: [ OK ]

Connect to mysql and make sure you can access it and also check an application that uses it. If that’s good, let’s set up SEL

sudo service mysqld stop

 sudo systemctl stop mysqld

Install a management tool for SEL:

sudo yum -y install policycoreutils-python

Check SEL permissions on the folder:

sudo ls -lZ /home/mysql/

The result will be something like:

drwxr-xr-x. mysql mysql unconfined_u:object_r:usr_t:s0 mysql

What is should be is:

drwx------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 abhms_wp

Update SEL:

semanage fcontext -a -t mysqld_db_t "/home/data(/.*)?"

sudo ls -lZ /home/mysql/

If that looks good:

restorecon -R -v /home/mysql

setenforce 1

sudo service mysqld start

Definitely do a reboot

Alternate – use SEL chcon

Instead of installing the tools, you can try:

chcon -R -t mysqld_db_t /home/data

Cleanup

We still have the old directly. If MySQL starts and everything is working, let’s move the original mysql folder (if room is available)

mv /var/lib/mysql /home/mysqlbu/mysql.bak

Troubleshooting

“ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)”

Restart MySQL
Make sure SELinux was updated correctly
Did you add the CLIENT section to my.cnf and restart mysql?