MySQL: changing the storage location for databases

By default when you install MySQL it stores databases on the C drive, you can change this during installation but perhaps you forgot to change the data files path during installation or completely missed the option to do this, or perhaps you used the Web Platform Installer which doesn’t give you any option to change the data files path.

On windows 2008 the datadir is “C:\ProgramData\MySQL\MySQL Server 5.5\data”

This is generally not the best idea as your c drive should be only for the OS and installed programs, you should put all your data on a different drive and keep it separate from the OS. So if you find yourself needing to change this after installation, here is how.

Open your my.ini file

On windows 2008 this is in C:\ProgramData\MySQL\MySQL Server 5.5\”

On other windows version just look at the mysql service properties to find out where the defaults-file is.

 

 

 

now find the “datadir” parameter and change it to where you want to store your databases.

E.G.
datadir=”D:\MySQL\data\”

Now STOP the MySQL service.

Now you must copy the original data directory to the new location.

E.G.
copy “C:\ProgramData\MySQL\MySQL Server 5.5\data” to “D:\mysql\data”

now restart MySQL.

If the service fails to start, double check that you have entered the path correctly in my.ini and that the path exists, and that all the data files have copied across properly.

Check your new data folder permissions and make sure it has “NETWORK SERVICE” with full permissions. On Windows 2008 these permissions may not exist by default.

 

 

 

 

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 !!!