Today I was upgrading MySQL 4.1 to MySQL 5.5 on the Windows 2003 Standard Edition acting as Post Master Enterprise email server. I performed custom installation of MySQL to install it in D:\Program Files\. After installation I created a database for my email server using “create database maildb;” command using MySQL Command Line Client, then stopped the MySQL service and was looking for database folder in D:\Program Files\MySQL\MySQL Server 5.5\data\. However there was no folder named maildb as it was found there in version 5.1 and earlier.
How to locate database root path (datadir)?
Open my.ini file from MySQL installation folder e.g. D:\Program Files\MySQL\MySQL Server 5.5. Search for datadir in my.ini file. You will find as shown below.
Database root path in Windows XP and Windows 2003 Server:
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"
Database root path in Windows 7 and Windows 2008 Server:
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
Path of database root is C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\Data\ in in Windows XP and Windows 2003 Server and C:\ProgramData\MySQL\MySQL Server 5.5\Data\ in Windows 7 and Windows 2008 Server. C:\ProgramData folder in Windows 7 and Windows 2008 Server is a hidden folder.
How to change database root path (datadir)?
I always perform custom installation of MySQL to store MySQL databases on the partition other than OS Partition and that’s why I needed to change the database root path (datadir). To change database root path follow below steps:
- Stop MySQL Service
- Check if you have read/write permissions of my.ini file. If not then add the user (you are logged on with) to NTFS permission list to have read/write permissions on this file.
- Now as per our discussion in previous section on locating database root path (datadir) search datadir in my.ini file. Replace the path with desired path. Now datadir statement will look as shown below:
datadir="D:/Program Files/MySQL/MySQL Server 5.5/data/"
Do not forget to replace backslashes with forward slashes while replacing path in my.ini. In Windows this path will be D:\Program Files\MySQL\MySQL Server 5.5\data while in my.ini file it will be D:/Program Files/MySQL/MySQL Server 5.5/data/.
- Save my.ini file.
- Copy all the contents from old database root path to the new database root path e.g. Copy all contents of C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\Data\ to D:/Program Files/MySQL/MySQL Server 5.5/data\.
- Start MySQL Service.