Moving MySQL databases to a Different Location

I decided to try to move my own MySQL database files to a different location on an external hard drive. This is for MySQL on Mac OS X 10.5. The original database files are located at /usr/local/mysql/data. Here are the steps I took:

1. Stopped the MySQL server. You can do this using the System Preferences MySQL pane, or you can do it like so:

mysqladmin -p shutdown

Then give it your MySQL root user password and the MySQL server will shut down.

2. Became the system root user.

3. copied the /usr/local/mysql/data directory and all of its files and subdirectories to the new location at /Volumes/Elements1TB/mydbs/ like so:

cd /Volumes/Elements1TB/mydbs
cp -R /usr/local/mysql/data .

4. Added a line to my /etc/my.cnf file. My /etc/my.cnf file now looks like this:

[mysqld]
max_allowed_packet=200M
connect_timeout=60
datadir=/Volumes/Elements1TB/mydbs/data/

5. Navigated to the /Volumes/Elements1TB/mydbs/ directory and executed this command:

chown -R _mysql:wheel data

6. Started the MySQL server. Again, you can do this with the System Preferences MySQL pane or you can do it like this:

mysqld_safe &

2 Responses to “Moving MySQL databases to a Different Location”

  1. torre says:

    Hello my friend,

    I am trying to configure different databases, on differents directories, on the same mysql instance…

    Can you help me? I am not finding on conf.d a parameter to discribe the database name for the dirrectories after [mysqld]

  2. John Seward says:

    The datadir variable lets you specify a path where MySQL keeps its database files, but I am not aware of a variable that lets you specify a particular database name. I am not really a MySQL expert, but as far as I know, you have to send a query to the MySQL server to open a particular database.

Leave a Reply