Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 8 down vote favorite

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

share|improve this question
does your mysql server can be shutdown during the mogration? – Book Of Zeus Jan 29 '12 at 18:29
prefereble no, but if there's no other way then yes – Vladimir Kakazsky Jan 29 '12 at 18:34
are you using innodb? – abdelsaid Jan 29 '12 at 18:43
@abdelsaid no, im using myisam – Vladimir Kakazsky Jan 29 '12 at 18:49

migrated from stackoverflow.com Jan 30 '12 at 5:43

2 Answers

up vote 9 down vote accepted

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.

share|improve this answer
he doesn't want downtime or at least minimal. cp -R will take forever. and changing the directory might result in data loss in case of clients online. – abdelsaid Jan 29 '12 at 18:57
2  
he said: prefereble no, but if there's no other way then yes. Also, if you have a lot of inserts in a tables you currently copying using MyISAM (table locking) you might have errors or data that are not copied over. – Book Of Zeus Jan 29 '12 at 19:00
there are other ways :) – abdelsaid Jan 29 '12 at 19:07
up vote 0 down vote

If you don't want shutdown during migration, I would setup a master-master replication to chroot environment on 2tb with mysql (same version) in it, then when on sync turn the 1tb off and you got a working chroot environment.

If you don't want to use chroot environment, change the my.cnf (not the chroot one) to point to where you want to store your mysql files on 2tb disk and disable replication. Then turn off the chroot environment and move the data to what you have in my.cnf, the move here will not take even a second because it's on the same disk. Restart mysql.

This is little bit complex to do, you can play with this in local vm enironment first.

share|improve this answer
6  
yes it complicated for me. I don't understand chroot and replication is complex for me. – Vladimir Kakazsky Jan 29 '12 at 19:09
then you will have downtime and what Book Of Zeus described might work. – abdelsaid Jan 29 '12 at 19:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.