Long story short, I recently had a client who has a Apache-PHP-MySQL site running off of a Windows server, and while DBA stuff usually isn’t in my wheelhouse, I nonetheless ended up having to figure out a way to transfer a MySQL database from the current server to another Windows machine. I… didn’t have a good time.
Eventually, though, I scrapped together a (no doubt incredibly backwards and inefficient) solution of sorts from the following resources:
- https://dev.mysql.com/doc/refman/5.7/en/copying-databases.html
- https://stackoverflow.com/a/20086949
- https://stackoverflow.com/a/27744820
So for the edification of the public — as well as myself in case I ever have the misfortune of having to do this again at some point — here’s the process that wound up working for me. Please note that these instructions assume that (a) you have remote access to both machines, (b) you have the root password for both MySQL installs, and (c) the MySQL folder is not on either machine’s system path (as one or both of these was a production server and I didn’t want to risk messing with the settings.)
So here’s how to copy a MySQL database from Server 1 to Server 2:
1. Open up a Command Prompt on Server 1.
2. Navigate to the MySQL bin folder. In other words, cd C:\{{whatever the path is}}\MySQL\bin.
3. Run the following command: mysqldump {{dbname}} --user=root --password={{password}} -r C:\{{whatever path}}\dump.sql. Add in the --verbose flag if you want to make sure it’s working.
And no, before you ask, you can’t just do .\mysqldump {{dbname}} > dump.sql from within PowerShell. Turns out that PowerShell mucks with the file encoding when using the > operator. So don’t be like me and waste a half-hour or so on that.
4. Copy dump.sql (or whatever you called the file) from Server 1 to Server 2. Put it somewhere low in the file hierarchy so it’s easy to get to.
5. Open up a Command Prompt on Server 2.
6. Navigate to the MySQL bin folder. Once again, cd C:\{{whatever the path on Server 2 is}}\MySQL\bin.
7. Enter the mysql shell via the following command: mysql --user=root --password={{password}}.
8. Run these SQL commands in order:
CREATE DATABASE {{dbname}};
USE {{dbname}};
source C:\{{path to file}}\dump.sql;
Is there an easier way of doing all of this? No doubt. But this way seems to have worked. And at two or three in the morning, that in itself is worth its weight in gold.