The proper way to move MySQL to a second drive

NabiKAZ

Active Member
Jun 18, 2007
26
1
53
In the case of moving the MySQL path to the other partition, almost all threads will end on this topic. But this topic is locked and I can not write on that. That's why I opened this new thread.

First of all, I would like to know that, given how many years have passed since then, is it still valid and verifiable?

And a few things I think about that topic are important and need to be corrected:

1) The command `mkfs.ext4` is very dangerous and the entire partition will be lost and formatted. Therefore, it might be better to warn the user in this case.

2) Must go to WHM, choose `Service Manager` menu and uncheck on Monitoring `MySQL Server` option then click save. It’s an important step to prevent MySQL services to being automatically restart by WHM service monitor.

3) Before `mount` command, must run `mkdir /var/lib/mysql` for create new `mysql` folder, because before it, the folder moved.

4) This solution is for move to new partition and not a new path directory.
 

cPanelLauren

Product Owner
Staff member
Nov 14, 2017
13,273
1,282
313
Houston
Hi @NabiKAZ

First, yes those instructions are still valid, @Jeff P. had at one point (just this last October) added the following but it was subsequently removed and the thread was locked for reasons that were unrelated to the validity of his instructions:

This method will make the switch perfectly in seconds. This is for red hat, centos, etc.

___________________________________________________

CONFIRM ALL PATHS PRIOR TO RUNNING!!


cpanel MySQL/MariaDB Path: /var/lib/mysql
Linux non-cPanel MySQL/MariaDB Path: /var/bin/mysql

The path to your harddrive may be different than mine.
So, use the command lsblk to list all unmounted drives.
It may be any of these, the final letter means which drive it is, "b" would mean drive #2 and "c" would mean drive #3 and so-forth:
/dev/xvdb
/dev/sdb
/dev/hdb

So find out which is yours and replace all instances of /dev/xvdb with your path in the blue code below.

___________________________________________________


Now to the actual commands:
*Once you run the first line, all database powered sites will stop working.

In this process, all permissions, ownership, paths and all that jazz are preserved. mysql.actual will also servce as a backup!

Then the actual process, run these commands:

service mysql stop;
mkdir /var/lib/mysql.actual;
rsync -avz /var/lib/mysql/ /var/lib/mysql.actual;
/sbin/mkfs.ext4 -L /var/lib/mysql /dev/xvdb;
mount /dev/xvdb /var/lib/mysql;
rsync -avz /var/lib/mysql.actual/ /var/lib/mysql;
service mysql start;


then in fstab add this to mount at boot:
(DO NOT DO THIS YOURSELF IF YOU DON'T UNDERSTAND fstab!!! YOU MIGHT NOT BOOT BACK ONLINE IF DONE WRONG.)

Run command:

vi /ect/fstab;

then find a blank line and enter the letter s

then find a blank line or make one and paste this by right clicking.

LABEL=/var/lib/mysql /var/lib/mysql ext4 defaults 1 2

Now just hit :wq to save and exit. Reboot and check everything. Do this during your host's business hours so you can get help in case you set the paths wrong in fstab and be sure to contact your host to verify it FIRST if needed.
For anything else I need to point out that this is unsupported and when you do this or perform a task like this you do so at your own peril.
 

rasanjanad

Member
Jun 3, 2016
10
0
51
SriLanka
cPanel Access Level
Root Administrator
Hi @NabiKAZ

First, yes those instructions are still valid, @Jeff P. had at one point (just this last October) added the following but it was subsequently removed and the thread was locked for reasons that were unrelated to the validity of his instructions:



For anything else I need to point out that this is unsupported and when you do this or perform a task like this you do so at your own peril.

How to do it when i use MariaDB?

Thanks