How to Move MySQL Database on Windows

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.



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:

Move MySQL Database on Windows -Stop MySQL

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.

Move MySQL Database on Windows -Set folder permissions

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

email popup image
Mark Harwood
NEVER miss a blog post again! Subscribe for email notifications whenever a new post is live!
Subscribe
NEVER miss a blog post again! Subscribe for email notifications whenever a new post is live!
Fill Out This Form, And I Will Be In Touch Shortly
Contact form image
I'll Be In Touch Soon