How To Move MySQL Data Directory (datadir)

Create a directory in the drive you want to store MySQL datadir.

In this example, we create mysql folder in /backup partition.

mkdir /backup/mysql
rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/
chown -R mysql:mysql /backup/mysql

Now we have MySQL data copied, but we don’t have all data as MySQL service is running and the data folder is keep changing. So to make sure MySQL data get copied properly, we need to stop MySQL and run rsync command again.

On Cpanel Server, disable MySQL from auto satrting with

sed -i "s/mysql:1/mysql:0/g" /etc/chkserv.d/chkservd.conf
/scripts/restartsrv_chkservd

Stop MySQL

service mysql stop

Now rsync again, so all data will be transferred, run this command few times to make sure no files needed to be copied.

rsync -vrplogDtH /var/lib/mysql/ /backup/mysql/

Rename old database directory

mv /var/lib/mysql/ /var/lib/mysql_old

Create a symbolic link from the old database directory to the new one for any programs that rely on the default location

ln -s /backup/mysql /var/lib/mysql

Set the correct owner and group on the symbolic link

chown -R mysql:mysql /var/lib/mysql

Edit /etc/my.cnf

vi /etc/my.cnf

Comment out the old settings and add a line for the new one as you can see below

[mysqld]
#datadir=/var/lib/mysql
datadir=/backup/mysql

Save my.cnf and exit editor.

Start MySQL

service mysql restart

If this is cpanel server, Enable MySQL monitoring with

sed -i "s/mysql:0/mysql:1/g" /etc/chkserv.d/chkservd.conf
/scripts/restartsrv_chkservd

On CloudLinux Servers

/usr/sbin/cagefsctl --remount-all

Verify all set properly

[root@server46 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.35-cll-lve MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%socket%';
+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| performance_schema_max_socket_classes   | 10                        |
| performance_schema_max_socket_instances | -1                        |
| socket                                  | /var/lib/mysql/mysql.sock |
+-----------------------------------------+---------------------------+
3 rows in set (0.01 sec)

mysql> show variables like '%datadir%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| datadir       | /backup/mysql/ |
+---------------+----------------+
1 row in set (0.00 sec)

mysql>

Show MySQL Data Directory on SSH login

when you login to linux server, you get welcome message, this is from file /etc/motd, lets add a message, so you know where MySQL datadir is located.

echo "MySQL in /backup/mysql/" >> /etc/motd

Posted in MySQL