Sign up ×
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.

I'm using MySQL 5.5 on a local machine to analyze a large amount of government data. I've created a local database that resides on my default drive (Win7 C: drive). I'd like to store the data on my E: drive, a large eSATA external drive.

What steps should I take?

share|improve this question

migrated from Mar 3 '12 at 1:52

This question came from our site for professional and enthusiast programmers.

cant you export the dB as a sql file, then save the file do e: then in the future if you want to restore the dB you can import this file –  t q Mar 1 '12 at 19:03
@tq - That won't work. He wants to change the MySQL server data directory. If he exports it and imports it, it'll still be on the same drive! –  Flukey Mar 1 '12 at 19:06
A word of caution when you move the database files to the external drive, you will have to keep it plugged into your computer otherwise your database will crash since it will not be able to find the data files –  ssmusoke Mar 1 '12 at 19:36

3 Answers 3

  1. Shutdown mysql.
  2. Move all the files in your current data directory to the new location (check out the location in step 3 - datadir parameter).
  3. Locate my.ini file (it is in the mysql installation directory). Change datadir parameter value to point to the new location.
  4. Start mysql.
share|improve this answer
This gave me errors when trying to start mysql (permissions issues). I settled on the solution I post here‌​, which I hope helps save someone some time. –  snapfractalpop Aug 20 at 22:38

Solution 1:

1, First of all, backup your data using mysqldump.
2, If you install Mysql Server from official package, you can do it by running program MySQLINstanceConfig.exe from bin directory of your server.
3, Import data


Second solution (not recommended, but works on same machine)

1, Stop Mysql server.
2, Copy data from your data directory to your new directory
3, Open my.cnf file (In my case "C:\ProgramData\MySQL\MySQL Server 5.5\my.ini")
4, find row like datadir="C:\ProgramData\MySQL\MySQL Server 5.5\data\" and change path to your new location
5, Start your server

As of version 5.6 MySQLInstanceConfig.exe is no longer distributed. This functionality is being replaced by the installer.

share|improve this answer

Although you are asking for windows, here are some good directions for those ending up here looking for linux/ubuntu directions:

  • Stop MySQL with:
    sudo /etc/init.d/mysql stop.
  • Make a backup of the current (clean) MySQL data directory with:
    sudo cp -Rp /var/lib/mysql /var/lib/mysql.backup.
  • Then copy the old data directory from the old drive with:
    sudo cp -Rp /mountpoint/var/lib/mysql /var/lib/mysql
    where mountpoint is the path to the mount.
  • Now restart MySQL with:
    sudo /etc/init.d/mysql start

With any luck, MySQL should restart, and you should have your old databases back.


share|improve this answer
There was a suggested edit stating the 'copy old data directory' command should be sudo mv /var/lib/mysql /mountpoint/mysql. Please incorporate that if it is valid. –  Paul White 6 hours ago

Your Answer


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