The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

The proper way to move MySQL to a second drive.

Discussion in 'Workarounds and Optimization' started by Jeff P., Aug 16, 2016.

  1. Jeff P.

    Jeff P. Member

    Joined:
    Feb 2, 2016
    Messages:
    24
    Likes Received:
    5
    Trophy Points:
    3
    Location:
    Palm Coast, Fl
    cPanel Access Level:
    DataCenter Provider
    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.
     
    #1 Jeff P., Aug 16, 2016
    Last edited: Aug 17, 2016
    mtindor likes this.
  2. axita shah

    axita shah Registered

    Joined:
    Aug 16, 2016
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    surat
    cPanel Access Level:
    Reseller Owner
    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/
     
  3. Jeff P.

    Jeff P. Member

    Joined:
    Feb 2, 2016
    Messages:
    24
    Likes Received:
    5
    Trophy Points:
    3
    Location:
    Palm Coast, Fl
    cPanel Access Level:
    DataCenter Provider
    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
     
    #3 Jeff P., Aug 17, 2016
    Last edited: Aug 17, 2016
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,713
    Likes Received:
    658
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  5. Jeff P.

    Jeff P. Member

    Joined:
    Feb 2, 2016
    Messages:
    24
    Likes Received:
    5
    Trophy Points:
    3
    Location:
    Palm Coast, Fl
    cPanel Access Level:
    DataCenter Provider
    Thanks, I made that change.
     
    cPanelMichael likes this.
Loading...

Share This Page