Moving SQL to another drive

JWolfe

• October 23, 2018 12:06

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

  • October 23, 2018 17:24

  Easiest way to do this: Go into service manager in WHM and disable monitoring for mysql 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.

  0
• 

  JWolfe

  • October 23, 2018 20:53

  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. :)

  0
• 

  GOT

  • October 23, 2018 20:55

  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.

  0
• 

  JWolfe

  • October 24, 2018 21:06

  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

  0
• 

  GOT

  • October 24, 2018 22:07

  Are you running cloudlinux by chance?

  0
• 

  JWolfe

  • October 24, 2018 23:16

  No CentOS 7

  0
• 

  GOT

  • October 25, 2018 01:33

  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.

  0
• 

  JWolfe

  • October 25, 2018 01:50

  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.

  0
• 

  cPanelMichael

  • October 25, 2018 15:04

  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:

  0
• 

  JWolfe

  • October 25, 2018 21:16

  Many thanks for the reply Michael, that would certainly explain my problem and why.

  0

Please sign in to leave a comment.