Moving SQL to another drive

JWolfe

Member
Oct 23, 2018
6
1
3
USA
cPanel Access Level
Root Administrator
Hello everyone.

I'm on a VPS with everything running off /dev/sda1 except my www (/home) which is on /dev/sdb.

I'm running low on disk space due to growing databases and was wanting to move my SQL (maria) over to /dev/sdb which is my second drive.

Has anyone successfully attempted to move /var/lib/mysql to another drive?

I tried a search but found some conflicting ideas of whats best and what is not, but they are older posts.

I'm a intermediate user and capable of most things, but i'm a little cautious when it comes to SQL data lol.

Hope i have posted in the right place, any help or pointers in the right direction would be great, thank you.
 

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,740
301
363
Chesapeake, VA
cPanel Access Level
DataCenter Provider
Easiest way to do this:

Go into service manager in WHM and disable monitoring for mysql

Code:
service mysql stop
mkdir /home/var
cp -rav /var/lib/mysql /home/var/
mv /var/lib/mysql /var/lib/mysql.old
ln -s /home/var/mysql /var/lib/mysql
service mysql start
Make sure mysql starts and all databases are visible. You can then archive or delete the mysql.old fodler once you are sure everything is working as expected.
 
  • Like
Reactions: JWolfe

JWolfe

Member
Oct 23, 2018
6
1
3
USA
cPanel Access Level
Root Administrator
Many thanks for your reply and help GOT.
Seems simple enough and wanted to double check, just in case.
Thought there might be a little more to it, but seems not the case. :)
 

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,740
301
363
Chesapeake, VA
cPanel Access Level
DataCenter Provider
There's certainly more than one way to skin a cat as they say, but this is how we normally do it for our clients, unless mysql is moving to a dedicated drive, then its a bit different, but this would work fine in your case.
 
  • Like
Reactions: cPanelLauren

JWolfe

Member
Oct 23, 2018
6
1
3
USA
cPanel Access Level
Root Administrator
Decided to take the plunge and try, ended up with errors and maria failed to start.
'Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.'
I tried renaming the the access_control file and checked permissions but still all failed, so had to roll back to original settings.
Do i need to make any changed to /etc/my.cnf ?

Many thanks
 

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,740
301
363
Chesapeake, VA
cPanel Access Level
DataCenter Provider
There are special steps if you were running cloudlinux with cagefs and mariadb but I've never seen an issue where this didn't work. You would need to go through the steps again and then check the logs and the journalctl command to see what the error was. Tough to try to diagnose in a forum style environment since no one wants mysql down for an extended period of time.

Using this method you would not need to change anything in your my.cnf for it to work right generically speaking.
 

JWolfe

Member
Oct 23, 2018
6
1
3
USA
cPanel Access Level
Root Administrator
Thanks for the reply.

That's what i thought and wouldn't have to change anything in my.cnf by creating a symbolic link.
Your right, didn't want SQl down for too long lol.
Will give it another shot tomorrow.

Thanks once again.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,203
363
Hello @JWolfe,

MariaDB uses the default systemd unit configuration that enables the "ProtectSystem" and "ProtectHome" features. Thus, configuring MariaDB to use a data directory within /home, /usr, /etc, /boot, or /root is unsupported and will prevent MariaDB from starting. You can read more about this on the following thread:

mysql 5.6 to mariadb 10.1.x centos 7.x datadir issue

Thank you.
 
  • Like
Reactions: JWolfe