Move the MySQL data files

If the drive the data is currently stored on is running out of room and you have another drive with lots of room - you might want to move there.

Step-by-step guide

The process isn't too hard but does require stopping and restarting the MySQL database service.

1. Locate your my.ini file 
   
   

   1. Example: C:\ProgramData\MySQL\MySQL Server 5.6

2. In the my.ini file find the datadir and then locate the current directory 

   1. Usually defaults to: C:/ProgramData/MySQL/MySQL Server 5.6/data

3. Create a new destination folder for the MySQL Data

   1. If NOT using the XCopy variation which I recommend then you need to manually set some permissions

      1. Verify permissions are the same on the old and the new directory

         1. Be sure that NETWORK SERVICE has full permissions (the owner of the service as of MySQL 5.6)

            1. Before V5.6 it was owned by SYSTEM which already had full permissions on ALL folders

4. Change the datadir value to the name of the new location

   1. end the new location path with a slash /

      1. This doesn’t seem to be needed in MySQL

5. Save the my.ini

   1. DO NOT save your edits with NOTEPAD. It often corrupts the file though it might look okay. Using Notepad++ in Administrator mode is recommended.

6. Stop the MySQL service

7. Copy all the data files to the new location - use the xcopy command below is quite easy

   1. include the ibdata* and ib_logfile* files (required for INNODB type tables)

8. Restart the MySQL service

Variation- Use XCopy

The use of xcopy allows the permissions to be copied as well.

Be sure to run the xcopy command in a cmd window using run as Administrator to avoid a permission denied error.

Why not simply copy? Well because that's not COOL!, this helps you not lose permissions on the copied folder, so that when you restart MySQL80, it won't give a stupid error: "The MySQL80 service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs." - Courtesy:Microsoft

https://stackoverflow.com/a/52540174/5749464

Related articles