MYSQL - Moving data and database properly

Having to move data from one location to another, or one drive to another in a database can be quite a hair-raising expericene. With MySql, it’s really not that bad. I have a database on a Window 2003 server, but the steps for Linux and Mac OSX are very similar.

But just to be sure that we won’t be shooting ourselves in the foot, lets make a backup of our databases. Whichever way you choose to do it is your call… ( mysqldump, using a 3rd party tool to create a csv files of your tables…etc)

First, lets shutdown the database:

Open up command prompt. Type in "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" -u root -p shutdown” or substitute your appropriate installation path. You will be prompted for your root password. Your mysql service should now be down. Test that by trying to log in.

Next, make copy all the files under the C:\Program Files\MySQL\MySQL Server 5.0\data folder accept files that start with “ib_”.

Now that your files are in their new location, rename the old directory to something like “data_old”, just so we will be sure that MySql isn’t still using the old location.

Open the "my.ini" or "my.cfg" file which is located under C:\Program Files\MySQL\MySQL Server 5.0\. Again save a copy of it, just in case you need to revert back.
Find the line that has the current datadir.

Again, default will be C:/Program Files/MySQL/MySQL Server 5.0/data/.
NOTE for Windows users: In the ini/cfg file, the path should have '/' NOT '\', forward slashes, not back slashes.

The line in your  my.ini or my.cfg file should looks something like this once you’ve changed it.

datadir=”D:/MySQL Datafiles/data/”

You’re all done. Start up the MySQL service again, and everything should be good to go.

Create a new database and check whether the appropriate files have been created in the new location. Keep your old files for a few days to make sure that everything is fully functional.