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
