From time to time it becomes necessary to optimize your system by changing where your data files and/or logs are located on the filesystem. While SQL Server is my primary platform, occasionally one has to use other systems; in this case mysql. Instructions are included for both database servers running on Windows OS.
Action Plan SQL Server
- Choose Method ( backup/restore OR detach/attach)
- Backup / Detach Database
- Restore Database / Move Data Files & Attach Database
- Test the Database
Action Plan MySQL
- Shutdown Service
- Change Configuration Options
- Restart Service
- Test the Service
This is how:
I opted to use the backup and restore method.
Backup the Database with the following command:
BACKUP DATABASE database_name TO DISK = N'T:\Backups\database_name.bak'
Restore the database; the MOVE option will handle the copying of the files. You need to know the names logical names of the data files and log files. Use the sp_helpfile command to get this information prior to attempting the restore operation.
Restore the Database using the following syntax:
RESTORE DATABASE database_name
WITH MOVE 'database_name_data' TO 'D:\SQLData\database_name_data.mdf',
MOVE 'database_name_log' TO 'E:\SQLLogs\database_name_log.ldf',
Test connectivity to the database.
Shutdown the mysql server
Open the my.ini file found in the directory where you installed mysql usually “C:\Program Files\MySQL\MySQL Server 5.0\my.ini”
find the line
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
Comment out the line, by putting a # sign in front of it.
Add a comment to document the change(date, time, reason).
Add a new line with the desired configuration
Save the file.
Move/Copy the files to the new disk/location.
xcopy /D /E /C /V /Y /Z /H / "C:/Program Files/MySQL/MySQL Server 5.0/Data/" "D:/MySQL Datafiles/data/"
Start the mysql service.
Test the service, to ensure that all the databases are up, and accessible.
Delete files from the original location, if you copied files instead of moving them.
For me the great perk of using SQL Server is that this configuration is done at the individual database level. Therefore, there is no downtime on other hosted databases during a move of a database file. With mysql this configuration is managed for the entire instance, therefore all files for the instance must be contained in the same folder, so the move requires all your files be moved, and in doing so, downtime on all hosted databases.