move data folder and drive mysql centos

i need to move the data to a different folder and drive on my cent os db server.

i added new hard drive (since we ran low on space),

i have a 1tb of hard drive and i have a new 2tb i want to put the data on on the same server (dedicated server hosted in a datacenter)

i would like to know what is the easy way to migrate the data without losing any data?

currently i have the data located in: /var/lib/mysql/ on cent os. mysql version is 5.1

Step 1

First setup your drive (assumed it's mounted and setup correctly in the fstab) and create new MySQL directory:

sudo mkdir /path/to/new/mysql

Make sure this folder has the right permissions:

chown -R mysql:mysql /data/mysql

Now here you can shutdown the service while copying data. Copy the data from the current data directory to new data directory, example:.

sudo cp -R /var/lib/mysql/* /path/to/new/mysql/

or you can copy individually each databases if you prefer or if you feel safe, you can move the folder using the mv command.

Step 2

You can change the location of your MySQL data in your my.cnf. If you don't know where this file is, you can run the command:

locate my.cnf

This will give you the path of where the my.cnf is located. Then edit the file:

sudo vi /etc/mysql/my.cnf (of course you have to replace with your path)

Now look for datadir = /var/lib/mysql and update to datadir = /path/to/new/mysql.

Then restart mysql

sudo /etc/init.d/mysqld restart

After few days/weeks, if you feel comfortable you can remove the old folder (/var/lib/mysql) or backup everything inside.

StackExchange Database Administrators Q#11884, answer score: 12