The proper way to move MySQL to a second drive

[Jeff P.]

After seeing so many BAD ANSWERS to people asking how to migrate MySQL to a new drive, I posted this to make your life a lot easier. 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/sdbb
/dev/hddb
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:
*You can run one line at a time, experts can run all at once.
*Once you run the first line, all database powered sites will stop working.

service mysql stop;
mv /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.)

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



Done.

 

[axita shah]

Recently, my server's only hard disk was almost full. I bought a new hard disk with bigger size and I decided to just add it as a second hard disk. Since I need to move it to the 2nd hard disk, I need to find a proper way to move the db with minimum downtime. So I googled around and found a solution.

First, I needed to format the 2nd hard disk and I chose xfs as the filesystem. I created 2 partitions using Linux's fdisk for this task. First partition is 10 GB and 2nd one is around 900 GB. That's approximately added up to 1 TB. Then I mounted the 2nd partition in current partition eg /media/hd2 as follows:

mount -t xfs /dev/sdb5 /media/hd2

where /dev/sdb5 is the partition and /media/hd2 is the mounting dir.

Stop mysql db before doing anything:

service mysql stop

Afterthat, I copied the entire db to newly mounted hard disk:

cp -rv /var/lib/mysql /media/hd2

It will take a while if you have huge databases.

Then, change the ownership of the dir to user and group of mysql:

chown -R mysql:mysql /media/hd2/mysql

You need to change the mysql config file in /etc/my.cnf to point to the dir:

[mysqld]
user = mysql
datadir = /media/hd2/mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

Now you can restart mysql db:

service mysql start

If there are no errors on startup, you can test by login to your mysql db and do sql query.

You can leave other settings as it is. If this doesn't work and if you use innodb, you may want to change these lines:

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/

 

[Jeff P.]

No, absolutely the wrong way. The long way, IS the wrong way. If you have to change paths in config files that will be overwritten, it is not good.

1. Too complicated, time consuming. Time is money.
2. Do not rename anything in my.cnf. If you do then you lose these changes when you update/upgrade mysql later.
3. Using rsync allows for compression, encryption and handles file permissions better. It also can restart a transfer where you left off. The copy command cannot do these things.
4. My method makes your entire new drive the cPanel default path of /var/lib/mysql
5. INNODB paths... don't touch them. There is no need.
6. Using SSD, EXT4 would be IDEAL for safety and speed balanced.


This is all you had to do, change the paths in my code to match yours:

service mysql stop;
mv /var/lib/mysql /var/lib/mysql.actual;
/sbin/mkfs.ext4 -L /var/lib/mysql /media/hd2;
mount /media/hd2 /var/lib/mysql;
rsync -avz /var/lib/mysql.actual/ /var/lib/mysql;
service mysql start;


/media/hd2 is now referred to as /var/lib/mysql

then in fstab add this to mount at boot:
LABEL=/var/lib/mysql /var/lib/mysql ext4 defaults 1 2

 

[cPanelMichael]

/sbin/mkfs.ext4 -L /var/lib/mysql /dev/xvdb;

Hi @Jeff P.,

Thank you for taking the time to share these workaround instructions on our forums. One improvement for these instructions might be to expand upon the command quoted above. It's possible it might confuse some inexperienced users who do not fully understand where "/dev/xvdb" comes from.

Thank you.

 

[Jeff P.]

Thanks, I made that change.

 

[Mauritz]

I was advised by a cPanel support agent to make use of the above steps to move our MySQL accounts. Although the steps worked in my test machine, I had to manually create /var/lib/mysql (mkdir) before I could actually mount the new partition with the path. Without this step you should be warned that /var/lib/mysql does not exist (as you're moving it from /var/lib/mysql to /var/lib/mysql.actual

 

[Jeff P.]

The path exists already in cpanel by default and then the path for the drive is defined by this line:

/sbin/mkfs.ext4 -L /var/lib/mysql /dev/xvdb;

- Removed -

 

[Ekushey]

Thanks Jeff, I'm saving your instructions on my Pastebin for future use!

 

[Jeff P.]

Oh yeah, Mauritz... also do not forget this line that goes in fstab so you boot the proper drive under the right label:

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

 

[smallbiz]

It would be awesome to have an official walkthrough of how to do this when moving from /var/lib/mysql to /home/mysql ...

All of OVH cpanel images (and I guess maybe all cpanel setups) have mysql on the smaller system partition, and it would be helpful to know how to correctly move mysql data to take advantage of the larger /home/ partition.

 

[Jeff P.]

Just read my instructions. Tells you how to move MySQL to a new spare drive.

 

[cPanelMichael]

All of OVH cpanel images (and I guess maybe all cpanel setups) have mysql on the smaller system partition, and it would be helpful to know how to correctly move mysql data to take advantage of the larger /home/ partition.

You may want to consult with them to see if it's possible to change the default partition setup. Otherwise you will face issues if later attempting to migrate to MariaDB:

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

Thank you.

 

[[JZ]]

I'm currently on a Cloud VPS running cPanel and CentOS 7, would the above instructions still apply?
I have everything setup on a 10GB partition including the mySQL (dev/sda1) and have an extended block of 100GB (dev/sdb) where my Home partition is containing the websites and need to move mySQL as i'm running out of disk space.
Many thanks.

 

[cPanelMichael]

I'm currently on a Cloud VPS running cPanel and CentOS 7, would the above instructions still apply?
I have everything setup on a 10GB partition including the mySQL (dev/sda1) and have an extended block of 100GB (dev/sdb) where my Home partition is containing the websites and need to move mySQL as i'm running out of disk space.

Hello,

The workaround instructions remain valid, though keep in mind they are unsupported. Generally, the better approach is to reach out to your VPS provider to see if they can facilitate a migration of your accounts to a VPS with better partition setup, or if they can resize the partitions from the VPS hardware node.

Thank you.

 

[blaineca]

After seeing so many BAD ANSWERS to people asking how to migrate MySQL to a new drive, I posted this to make your life a lot easier. 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/sdbb
/dev/hddb
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:
*You can run one line at a time, experts can run all at once.
*Once you run the first line, all database powered sites will stop working.

service mysql stop;
mv /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.)

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



Done.

how would I do this if I have a usb drive already formatted as ext4 and my system auto mounts it as /media/pi/usb?

 

[cPanelMichael]

how would I do this if I have a usb drive already formatted as ext4 and my system auto mounts it as /media/pi/usb?

Hello @blaineca,

Can you post the output from the following commands on this system?

cat /etc/fstab
mount

Thank you.

 

[blaineca]

Hello @blaineca,

Can you post the output from the following commands on this system?

cat /etc/fstab
mount

Thank you.

 cat /etc/fstab
proc            /proc           proc    defaults          0       0
/dev/mmcblk0p6  /boot           vfat    defaults          0       2
/dev/mmcblk0p7  /               ext4    defaults,noatime  0       1
# a swapfile is not a swap partition, no line here
#   use  dphys-swapfile swap[on|off]  for that

mount
/dev/mmcblk0p7 on / type ext4 (rw,noatime,data=ordered)
devtmpfs on /dev type devtmpfs (rw,relatime,size=470116k,nr_inodes=117529,mode=755)
sysfs on /sys type sysfs (rw,nosuid,nodev,noexec,relatime)
proc on /proc type proc (rw,relatime)
tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev)
devpts on /dev/pts type devpts (rw,nosuid,noexec,relatime,gid=5,mode=620,ptmxmode=000)
tmpfs on /run type tmpfs (rw,nosuid,nodev,mode=755)
tmpfs on /run/lock type tmpfs (rw,nosuid,nodev,noexec,relatime,size=5120k)
tmpfs on /sys/fs/cgroup type tmpfs (ro,nosuid,nodev,noexec,mode=755)
cgroup on /sys/fs/cgroup/systemd type cgroup (rw,nosuid,nodev,noexec,relatime,xattr,release_agent=/lib/systemd/systemd-cgroups-agent,name=systemd)
cgroup on /sys/fs/cgroup/freezer type cgroup (rw,nosuid,nodev,noexec,relatime,freezer)
cgroup on /sys/fs/cgroup/cpuset type cgroup (rw,nosuid,nodev,noexec,relatime,cpuset)
cgroup on /sys/fs/cgroup/cpu,cpuacct type cgroup (rw,nosuid,nodev,noexec,relatime,cpu,cpuacct)
cgroup on /sys/fs/cgroup/blkio type cgroup (rw,nosuid,nodev,noexec,relatime,blkio)
cgroup on /sys/fs/cgroup/net_cls type cgroup (rw,nosuid,nodev,noexec,relatime,net_cls)
cgroup on /sys/fs/cgroup/devices type cgroup (rw,nosuid,nodev,noexec,relatime,devices)
systemd-1 on /proc/sys/fs/binfmt_misc type autofs (rw,relatime,fd=28,pgrp=1,timeout=0,minproto=5,maxproto=5,direct)
sunrpc on /run/rpc_pipefs type rpc_pipefs (rw,relatime)
debugfs on /sys/kernel/debug type debugfs (rw,relatime)
mqueue on /dev/mqueue type mqueue (rw,relatime)
configfs on /sys/kernel/config type configfs (rw,relatime)
/dev/mmcblk0p6 on /boot type vfat (rw,relatime,fmask=0022,dmask=0022,codepage=437,iocharset=ascii,shortname=mixed,errors=remount-ro)
tmpfs on /run/user/1000 type tmpfs (rw,nosuid,nodev,relatime,size=94944k,mode=700,uid=1000,gid=1000)
fusectl on /sys/fs/fuse/connections type fusectl (rw,relatime)
gvfsd-fuse on /run/user/1000/gvfs type fuse.gvfsd-fuse (rw,nosuid,nodev,relatime,user_id=1000,group_id=1000)
/dev/sda1 on /media/pi/usb type ext4 (rw,nosuid,nodev,relatime,data=ordered,uhelper=udisks2)
/dev/mmcblk0p5 on /media/pi/SETTINGS type ext4 (rw,nosuid,nodev,relatime,data=ordered,uhelper=udisks2)

 

[cPanelMichael]

Hello @blaineca,

If /dev/sda1 is already in-use by the system, then you should not follow the steps referenced in the post you quoted. Are you able to contact your provider or system administrator to help you unmount /dev/sda1? Once you do that, you should be able to see it listed in the output from the following command:

lsblk

From there, you can follow the user-submitted workaround instructions on this thread.

Thank you.