Changing Data Directory on MySQL DB Server

Question

I have installed mysql server 5.6.24 on Windows Server 2012 R2.

I imported dump files and the data files are stored in C:\ProgramData\MySQL\MySQL Server 5.6\data

I want to change directory like D:\ProgramData\Data.

Can you please help me, anyone ?

Answer 1

1. Open Windows CLI as Administrator and Shutdown MySQL

   net stop mysql
   

2. Copy the Data to D:\ProgramData\Data

   xcopy /s C:\ProgramData\MySQL\MySQL Server 5.6\data D:\ProgramData\Data
   

3. Create or edit C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

   Add this to the my.ini

   [mysqld]
   datadir = D:/ProgramData/Data
   

4. Startup MySQL

   net start mysql
   

   If you can not execute net start mysql, try the Windows control panel.

5. Login to MySQL and verify everything is good

When you login to MySQL, run this

mysql> SHOW GLOBAL VARIABLES LIKE 'datadir';

Remember to give your new data directory the same full permissions for users Network Service and Administrator that the default data dir was using, otherwise it'll cause the dreaded "started and then stopped" error. Don't delete this dir until you're sure your new one is working.

If this does not work, here is the rollback plan:

net stop mysql
del "C:\ProgramData\MySQL\MySQL Server 5.6\my.ini"
net start mysql

Answer 2

Adding a current answer for MySQL server 8.0 on Windows 10 for how to change the database directory after installation (I searched for it on the internet and didn't find a solution and almost went crazy, so I hope this helps somebody).

The first steps are the same as in the usual other instructions for MySQL Server 5.6. The only difference is basically the 3rd step:

1. Go to services (e.g. press WIN+R, type services.msc, hit enter) and stop the MySQL80 service via right-click and clicking 'stop' (the service name is specified during the installation, so the name might be different for you).

2. Move the database folder to wherever you want. The initial location is usually C:\ProgramData\MySQL\MySQL Server 8.0\data. Inside the C:\ProgramData\MySQL\MySQL Server 8.0\folder, there should be a my.ini file. Open it to edit it with Notepad++ (or some other notepad) and search for the lines which mention the old location in some form. There should be two occurances of it (one for datadir, one for secure-file-priv). Rename both to the corresponding new location (possibly it is enough to just rename the datadir part, but better be safe than sorry). Initially, they would usually look like this:

   datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
   

   and

   secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
   

   So if the new location should be E:/MySQL/MySQL Server 8.0/..., then rename both entries like this:

   datadir=E:/MySQL/MySQL Server 8.0/Data
   

   and

   secure-file-priv="E:/MySQL/MySQL Server 8.0/Uploads"
   

3. Now, after moving the database and renaming the entries in the my.ini file, BEFORE you restart the service again, open the registry editor (e.g. press WIN+R, type "regedit", hit enter), navigate to Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL80 (once again, the MySQL80 is the service name of the MySQL service you chose during installation and might differ from MySQL80) and right-click the ImagePath entry and choose modify. There should be a string displayed similar to this:

   "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" MySQL80
   

   This is basically the link to the actual exe-file executed by the service with an additional config-file parameter, namely that my.ini file we modified earlier. Therefore, here, of course, the path to the config file needs to be updated too, since it was moved, so for this example, it would be changed to:

   "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="E:\MySQL\MySQL Server 8.0\my.ini" MySQL80
   

   Note that of course, only the path of the config file needs to be changed.

4. Now the service can be started up again! Go to services again and right-click on the MySQL80 service to choose the 'start' option again and it should restart without problems. If step 3 is skipped, the restart usually will not work!