The Microsoft Web Platform Installer (WPI) has made installing WordPress and MySQL on your Windows servers incredibly easy. With just a few clicks one can deploy a new WordPress site in minutes. When it comes to managing a MySQL database you may first think of MySQL Workbench which is a great tool however depending on your technical savy installing that application may be challenging. Additionally, using it requires remote access to your server and what if you need to manage your MySQL database and don’t have access to Remote Desktop? Fortunately phpMyAdmin is a wonderful alternative with a browser-based GUI and fits any budget because it’s free.\r\n
Installing phpMyAdmin
\r\nInstalling phpMyAdmin on your Windows cloud server is easy and straight forward. Here are the basic steps:\r\n
- \r\n
- Download the latest version of phpMyAdmin to your server
- Using IIS Manger create a site
- Unzip the phpMyAdmin archive into the root of the new site
- Using a browser go to the new site where you’ll see the login screen
- Enter your MySQL root user and password
\r\n
\r\n
\r\n
\r\n
\r\n
\r\n
\r\n\r\nThe current version of phpMyAdmin requires at least PHP 5.5 and MySQL 5.5. If your server isn’t running the correct specs you’ll receive an error message.\r\n\r\n
\r\n\r\n \r\n\r\nThere’s a phpinfo.php page in the root of site so you can check which version your server has in case you are not sure.\r\n\r\n
\r\n\r\n \r\n\r\nAfter logging in with the root username and password you will arrive at the administration dashboard as seen below. From here you can perform nearly every admin task necessary to manage your MySQL instance. Adding, deleting, and editing, databases, users, and tables is only a few clicks away as well as exporting and restoring databases.\r\n\r\n
\r\n\r\n \r\n
Backing up a MySQL Database
\r\nBacking up a MySQL database with phpMyAdmin is pretty straight forward. From the admin dashboard click on the Databases menu and you will see all the databases available to your user as shown below. If you are logged in as the root user you’ll be able to select all the databases in the MySQL instance. Select the database you want to backup and click on the Export menu. Alternatively if you are already on a database’s details page, the Export menu will be there as well. In my example I am just going to backup one user database called pvtest1.\r\n\r\n
\r\n\r\n \r\n\r\nOn the following screen you need to choose the Quick or Custom Export method. Choosing the Custom method allows you to set specific options such as reformatting the output, using compression, exporting the data as well as the structure, and Object creation options to drop existing objects when the database is restored.\r\n\r\n
\r\n\r\n \r\n\r\nSelecting the Custom export method also enables you to specify if you want to rename the database and structure in the export file.\r\n\r\n
\r\n\r\n \r\n\r\nUpon clicking the Go button the database will be exported to a flat file using the options you’ve selected. The file is automatically downloaded to your browser’s Downloads folder. The text file contains serialized data so be aware that you can corrupt the contents. I have had issues restoring the files after editing them with Notepad so I prefer to use Notepad++ which hasn’t caused any issues for me.\r\n\r\n
\r\n\r\n \r\n\r\nOne can see this is an easy and straight forward process when the occasional backup is needed. However if more frequent backups are needed, then a more robust process is required . Here is a recent blog post on how to automate MySQL backups on your server.\r\n\r\n \r\n
Restoring a MySQL Database
\r\nRestoring a MySQL database is just as simple as backing it up was. From the home dashboard click the Import menu and then choose the MySQL backup file to be imported. By default the export file be hardcoded to create a database with the same name from which it was exported. If the export file contains the Object creation options it will drop the existing database before creating it again. As noted above you can also have the database renamed before exporting it. Additionally you can just edit the export file and specify the new database name by changing the CREATE DATABASE and USE statements. In this example we’re backing it up into a new a new database called pvtest2.\r\n\r\n
\r\n\r\nDepending on how big your backup file is the import process will take a few minutes. Once the process completes you’ll see a detailed message with the results. If there were any errors they will be noted here as well. Your new database is now ready to be used.\r\n\r\n \r\n\r\n
\r\n
In Summary
\r\nBacking up and restoring MySQL databases on Windows Server 2012 R2 is easy with PhpMyAdmin. Previously one had to use Remote Desktop to do MySQL administration. Now using only a web browser you have nearly complete control over your MySQL databases. Thanks for reading!
