MySQL - Move Data Files To New Location (Windows)

ben himself's picture

After setting up MySQL, I realized that I put my data files on the C drive.  Since I don't want database growth to end up taking down the entire OS, I had to move them.  My struggles consumed a couple of hours, so I wanted to clear up and document the process.

Default Database Files

MySQL (5.7) stores the default configuration files at:
C:\ProgramData\MySQL\MySQL Server 5.7\

To verify where your files are stored, simply run this MySQL script:
 

select @@datadir;

Copy the result.

Stop the MySQL services.

Now navigate to the location you just copied above and you'll see a folder called "Data".  Copy the entire folder and all its contents to the location where you want it to be.  Also, to avoid confusion, rename the original "Data" folder to something like "DataX".  That will ensure that you aren't still reading the old folder after the change.

Next, within the original folder you'll see a file called my.ini.  Open it in notepad, or any text editor.

Search for "datadir" in that file.  The default value is:
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

Comment out that line by preceding it with a hash/pound sign (#).

Add a new line that looks like this that points to the newly copied Data folder:
datadir=E:/New Data Drive/Data

A few points to make here. 

  1. Do not try to use quotes around the path.
  2. Do not use any spaces after the equals sign, otherwise it will be interpreted as a part of the path name.
  3. All back-slashes (\) need to become forward-slashes (/).

Save the file.

Restart MySQL.

If it does not start, make sure that the account running the MySQL service has access to the new location of the Data folder.

Once everything is working, you can delete the old folder that you renamed to "DataX".

 

Tags: