26

I want to change the datadir path in the my.ini file.

The existing datadir path is C:/ProgramData/MySQL/MySQL Server 5.1/Data/

The size of my C: drive is 30 GB and the MySQL data folder is occupying 25 GB. So I want to change the datadir to F:/naveen/data.

Is this possible?

My machine is running Windows Server 2003.

ypercubeᵀᴹ
  • 92,106
  • 13
  • 189
  • 284
naveen
  • 261
  • 1
  • 3
  • 3

1 Answers1

33

From your paths, I can safely assume the following:

  • You are running MySQL in Windows
  • You used the MySQL MSI to install

What you need to do is establish the file my.ini

When installing MySQL for Windows using the MSI, the location of my.ini is expected to be C:/ProgramData/MySQL/MySQL Server 5.1.

Please run the following in a DOS Window:

cd C:/ProgramData/MySQL/MySQL Server 5.1
dir *.ini

You will see some sample my.ini files. However, if there is no file named my.ini in that folder, you must create one. Whether one exists or not, please run this:

cd C:/ProgramData/MySQL/MySQL Server 5.1
notepad my.ini

If you are asked to create it, please do so.

Next, create the following entry under the [mysqld] group header in my.ini:

[mysqld]
datadir=F:/naveen/data

Save my.ini

Next, stop mysql from the DOS command line like this:

C:\> net stop mysql

Next, make a copy of the entire data folder in the new location

C:\> xcopy "C:\ProgramData\MySQL\MySQL Server 5.1\data" F:\naveen\data /s

Last step, start up mysql

C:\> net start mysql

Try logging into mysql. Once you can login to mysql successfully, run this command:

show variables like 'datadir';

If F:\naveen\data shows up as the datadir, CONGRATULATIONS, YOU HAVE DONE IT RIGHT !!!

Once you are satisfied all your apps hitting MySQL works, you can delete everything in C:\ProgramData\MySQL\MySQL Server 5.1\data\*

Give it a Try !!!

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
  • May I know the above steps will work for windows server 2008 ? –  Sep 19 '12 at 09:38
  • Works, but most likely you will get error 1067. Check the permissions on the new directory –  Jul 20 '14 at 12:19
  • Thaaaaaank you. – Alejandro Bastidas Oct 16 '15 at 21:05
  • 2
    Sometimes the service is registered as `MySQL57`, you must check first. – giannis christofakis Dec 01 '15 at 13:19
  • 2
    I made a noob mistake and didn't xcopy permissions. mysql wouldn't restart. I tried to give NETWORK SERVICE full access to the datadir (as per other online suggestions), and it threw permission errors. If this happens to you, give "Everybody" full control of the data subdirectories and inherit to children. Then go to the parent (datadir) folder, enable inheritance via the "Advanced" permissions and apply to all children. You can then add NETWORK SERVICE to your datadir, giving it full control and that will propagate to children. – murraybiscuit Apr 11 '16 at 06:38
  • @murraybiscuit your comment solved my problem, thanks! – waaz May 10 '16 at 10:36
  • 5
    You should use `xcopy /O/X/E/H/K` instead of `xcopy /S` in this answer. – Ethan Allen Aug 15 '16 at 20:20