The proper way to move MySQL to a second drive

[NabiKAZ]

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]

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]

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

 

[cPanelLauren]

Same instructions for MariaDB as noted in the example.

 

[rasanjanad]

Same instructions for MariaDB as noted in the example.

Thanks for the reply. After following this steps, All databases in the MariaDB server will move to new hard drive without a problem?

 

[rasanjanad]

Same instructions for MariaDB as noted in the example.

Can you help me?

 

[andrew.n]

Whats the problem?

 

[rasanjanad]

Whats the problem?

Thanks for the reply. After following these steps, All databases in the MariaDB server will move to a new hard drive without a problem?

 

[andrew.n]

These are the steps yes but please be careful and adjust the steps to your OS and current environment if needed.