Getting MySQL to work on Windows can be a headache but not impossible. I was recently given the task of transferring a WordPress installation using Windows for the server OS. WordPress offers tools for exporting and importing posts, pages, comments, custom fields, categories, tags and more from a WordPress export file, but a tool to perfectly transfer everything stored in the MySQL database is hard to come by.
Introducing MySQL Workbench
MySQL Workbench is the gold standard for MySQL database management. Released by the Oracle Corporation, it’s the successor to DBDesigner 4, and replaces the MySQL GUI Tools Bundle. You can download installers for Windows, Linux, and OS X for free. Thank you Oracle!
MySQL Workbench allows you to do almost anything you want to an installation of MySQL. Need to easily transfer a database? No problem. Or you might need to replicate your database to another MySQL server for redundancy. It has you covered. You will find no better tool to manage MySQL on Windows than MySQL Workbench.
Let’s start transferring your WordPress database.
1. Connect to Your SQL Server
MySQL Workbench will scan the computer it’s installed on for MySQL instances and create the server connection for it.
Click the connection box (pictured above), enter your password, and click [OK] to complete the connection.
2. Backup WordPress Database to SQL File
On the left hand side of the application you will see a panel labeled “Navigator”. Under the list of options you will see one called “Data Export”. This is the one we want.
A new tab will open on the right side of the application after clicking “Data Export”. Click the checkbox next to your WordPress database to select the entire WordPress database for export.
Further down on the screen you will see a section called “Options”. There will be the option to “Export to Dump Project Folder” or “Export to Self-Contained File”. I like working with the self-contained file and that’s the option we will be using today. Go ahead and click “Export to Self-Contained File” if it’s not already clicked. If you’re not satisfied with the pre-selected location or file name you can change it now by clicking the button […].
Make sure the 4 checkboxes at the bottom of the tab are not checked. If everything looks good, go ahead and press the [Start Export] button to start exporting. A new tab called “Export Progress” will open showing you when the process has completed.
3. Edit Schema Name in SQL Backup
If you find yourself importing to a database that has a different schema name than the database you backed up, you will need to alter the SQL backup file to use the new schema name.
The database I backed up was named “wordpress514” but I want to import to a database name “wordpress629”. MySQL Workbench doesn’t allow you to export data without the schema included. This is a problem, but there is a quick solution.
Find the SQL backup file and open it up in your text editor of choice. My favorite is Notepad++.
CREATE DATABASE IF NOT EXISTS `wordpress514` /*!40100 DEFAULT CHARACTER SET utf8 */;
-- MySQL dump 10.13 Distrib 5.6.17, for Win32 (x86)
-- Host: localhost Database: wordpress514
-- Server version 5.1.72-community-log
The first seven lines of the file need editing in three spots. Any instances of “wordpress514” need to be replaced with “wordpress629”. Remember, your two database names will most likely be different. Make the changes and save the file.
4. Connect to Your New SQL Server
Now we can start moving the data over to your new MySQL database (“wordpress629” in this example) with our SQL file backup. Open up MySQL Workbench on your new server and connect to your database like you did earlier during the first step of this tutorial.
5. Drop Existing Tables
If your new database already has tables in it, now is the time to drop them like a bad habit. There is a section labeled “SCHEMAS” under the “Navigator” pane. Find your WordPress database and expand it. Now expand the “Tables” object if it exists. Select all items inside “Tables” and right click. Click the option to drop them.
6. Import SQL File
Under the section “MANAGEMENT” in the “Navigator” pane, click “Data Import/Restore”. You’re going to want to select the radio button for “Import from Self-Contained File” on the tab that just opened. Browse to your SQL backup file by clicking the […] button. Leave all other options set to their defaults. Scroll down to the bottom of the tab and click the [Start Import] button. Your entire WordPress database will be imported under the existing schema name.
7. Test for Success in WordPress
Go visit your WordPress site in a browser. With any luck, you should have a installation of WordPress running with all of your existing content, user information, the whole shebang.
That’s all there is to transferring your MySQL WordPress database on a Windows server. I’ve enjoyed sharing the steps I took to transfer my WordPress database, and hope this was helpful for you if you have been struggling.
Have a better method? Tell me how in the comments below.