Mysql – Changing Data Directory on MySQL DB Server

datadirMySQLmysql-5.6windowswindows-server

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 ?

Best Answer

  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
Related Question