Recently, I’ve had to move MySQL database on Windows to another drive. This was the straight forward process I thought it would be, especially since there differences between MySQL versions.
YouTube Video
I wanted to do what I think I do best, and create a quick and easy to follow guide to show anyone how they can move MySQL to another drive or directory.
Before we start, you can also read this on Medium.com.
Introduction
When you install MySQL onto Windows, the database is installed into C:\ProgramData\MySQL\MySQL Server 8.0\Data. This goes against best practice however, as it’s recommended to have only the Windows operating system and essential software on the C drive.
All other applications and data should be stored on additional drives or network shares.
This also helps the C drive maintain a sensible size which greatly helps with any recovery efforts should the system experience issues. Having to run sfc on a 60GB C drive compared to a bloated 2TB C drive makes all the difference. The difference usually being if you pull your hair out or not…
Move MySQL Database on Windows
In this walk through, we’ll only be moving the data but you could also move the binary logs too. It’s the same procedure for both, just a different line in the my.ini file that would need updating.
How to Upgrade MySQL Community to MySQL Enterprise on Red Hat 9
Been a while since I made a post, so let’s jump straight back in with…
How to Setup WordPress Backups (FREE!)
Personally, I like my website. A lot. And I wouldn’t want one mistake to make…
Install NGINX on an Offline RHEL System – 2 Systems Required!
Been a while since my previous post, but let’s hope the magic hasn’t rubbed off…
MySQL 8.4 Replication with SSL on Ubuntu 22.02
So I recently wrote a post and made a YouTube video on setting up MySQL…
Gutenberg Just Broke my Website
More of a story and vent than an informative article. So to save you reading…
How to Design a Website in 5 Steps
I’ll show you how to move your MySQL database from C:\ProgramData\MySQL\MySQL Server 8.0\Data ➡️ D:\MySQL\Data.
Stop MySQL
First, you need to stop the MySQL service. Otherwise, the data will be locked and it’ll prevent you from moving the files/folders.
To do this, open the Services menu, find MySQL, right click and stop:
Copy The MySQL Data
Now you need to copy, or move, the Data folder inside C:\ProgramData\MySQL\MySQL Server 8.0\ to D:\MySQL\.
The speed of this depends on a number of factors, mainly the overall speed of your system and the size of the data that is being used. I would always recommend that MySQL databases are ran on SSDs rather than hard drives.
Assign Folder Permissions
Once the data has been migrated to the new location, we need to give permissions to the NETWORK SERVICE user that’s built into Windows. To do this, right click the new data folder and assign this user with at least modify permissions.
Most people just set full control as it’s easier, but I’d prefer not to. This is worth knowing if something fails down the road, you know to set full control permissions instead.
Depending on the amount of data/files, this could also take a bit of time. Coffee break!
Edit The my.ini File
Now that we’ve migrated the MySQL database, and set the relavant NETWORK SERVICE permissions, we can update the my.ini file to use this new location.
Head to C:\ProgramData\MySQL\MySQL Server 8.0\ and first make a copy of the my.ini file. This will allow us to quickly revert back should anything go wrong.
Open the my.ini file in your favourite text editor, and find the datadir line. Now you can change it from this
datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data
to this
datadir= D:\MySQL\Data
Start MySQL
At this point, you can start the MySQL service up again and it should come up without any issues.
I’d always recommend refreshing the service window to see if the service does stop. If it does, check for any error logs in the newly migrated data directory.
Conclusion
Whilst migrating a MySQL database isn’t the simplest thing to do it certainly helps to have the steps laid out infront of you.
It might also be worth checking if any software relies on knowing where the MySQL certificates are stored as these will be moved now.
If you do run into any issues, feel free to comment on this point and I’ll lend a hand!
Enjoy! 🎉
