Kiss your MySQL website goodbye
by Mark Rais, senior editor

Readers of this article also read: So You'd Like To Use MySQL 

In this article Mark Rais offers beginning Linux web administrators some guidance with regard to proper back up and restore of a dynamic website to help prevent a catastrophic moment.

Maybe it is just coincidence. I meet a colleague for lunch and he tells me that he does not regularly backup the business website. Later on the same day, I visit a client site and the tech staff joke about who backs up their MySQL DB. It seems the engineer named "who knows" performs their backups.

When I arrive at home, there are a few emails about a website outage, and pleas for my assistance since they did not backup the website. I can tell you these folks can kiss their dynamic database driven website goodbye.

The fact is that if you run a business website, you need to perform proper backups, and not just the stuff that gets put on an external USB hard drive and thrown in a closet each evening.

I am writing this article specifically for those beginning Linux web administrators who need to know how to properly backup their website. Just because you're new to Linux and web serving does not mean it is difficult or that you should try to avoid performing proper website backups.

As always, use these tips as a general guideline only. The actual steps you need to take are highly dependent on your own context.

Outages occur for many reasons, but one of the leading reasons is a server failure and when that happens trying to configure and rebuild a dynamic website is painful.

This may sound idiotic to some Linux admins with lots of time, experience, and the MONEY to operate fully redundant backup servers. But most new Linux administrators running websites do not have the luxury of such options.

Again, the purpose for such a static backup is to buy you time. It relieves pressure so that without the intense urgency, you can systematically restore the production website.

This method is especially helpful if the outage is caused by severe load. Being able to switch over to a backup static website will ensure you don't offset the load on to another server, replicating the problem. Dynamic websites do put much more load on a server.

Having a complete static working rendition of your website gives peace of mind, should a major server failure occur. It also provides a failsafe mode that allows you extra time to restore your dynamic MySQL website.

Backup all your website files

I don't know why this is not clear to some new administrators, but the files used to deliver the templates, css and other styles for your website are distinct from the actual content displayed.

For this reason, you need to ensure you perform a backup of your website files, not just backup individual files you think are of value such as the Themes files (Drupal, Joomla, etc).

The most effective backup is to precisely mirror ALL your files located under http, not just ones you think relate to your theme.

Too many times I've gone to an office where someone has backed up all their CSS files or all of their theme files, but not paid enough attention to modifications that may have been made to files.

It is easy and reasonable to perform a complete and absolute mirror backup of all files under http (some servers as httpdocs) directory.

This second tip may seem trite but will help ensure you have a comprehensive backup and don't end up having to tweak key files once you restore the website.

Backup your MySQL Database

I am offering suggestions for how to perform a MySQL backup mainly because the majority of websites I deal with are using MySQL.

For those who are new to Linux and dynamic website administration, there are several ways to perform a database backup. One method is to simply access your server through the shell (bash, ssh etc) and type commands to perform backups and restores.

However, I've found that the majority of new Linux website administrators have access to some handy tools and interfaces. For that reason I prefer to encourage and emphasize the use of the highly useful tool called phpMyAdmin.

This quality tool is really a result of the personal initative and expertise of guys like Peter Kuppelwieser and Tobias Ratschiller, who both contributed a lot of blood and sweat to deliver code that could then be sourced into the phpMyAdmin project by Loïc Chapeaux, Marc Delisle, and Olivier Müller.

They deserve a lot of thanks!

Now what they allowed is a perfect tool for new web administrators to manage their MySQL database driven websites.

The best part is that the phpMyAdmin tool is available by default on almost all major hosted Linux servers. Or you can download and install it for your own business server here: Download phpMyAdmin.


Start the phpMyAdmin tool and select the appropriate database.

Now from within the phpMyAdmin tool, choose Export.

On the Export phpMyAdmin window ensure you have selected the following:

1. Export window should specify the ENTIRE database, not just a table/s

5. Select Data and check use "hexadecimal for binary field" and ensure Export type is set to "INSERT".

6. Check "Save as file", do not change the file name, but you can use compression if you want. I often backup both compressed and uncompressed versions of smaller DBs.

7. Now press "GO" to download the backup file to your local computer.

Be sure to save the backup MySQL database file on to a backup Disc for future use!

Restore your MySQL Database after a failure

This section is only for a situation where your existing database has been corrupted and you need to restore it. Please use this strictly for situations where a full database restore is needed.

1. Start the phpMyAdmin tool and select the appropriate database. NOTE that if you do not yet have a database created (ie. in the case of a total failure and restore), you instead need to CREATE the database with the exact same DB NAME, User, and Password as you had for the previous DB. I assume you already have the database created and that it is listed.

2. Choose the Import option

3. Properly choose Import options

On the Import tab, press the Browse button to find the backup MySQL file you had stored. Keep the Character set as indicated but be absolutely sure to unselect the Partial import option as shown.

Please be careful if your database is extraordinarily large as the "Max File Size" for uploading the SQL may have been set to limit your upload. You can check this size beside the Browse button.

4. Select SQL as the format type (you may only have one choice) and then press the Go button.

It will take several minutes for the upload and MySQL import.

I recommend you not panic unless the process has exceeded 15 minutes. Then you can assume something went wrong and may need to retry the process.

If all works correctly, you will return to the phpMyAdmin window and a message will appear confirming you have successfully executed the SQL query. This confirmation indicates the database is up and functioning with the back-up content.

Troubleshooting a MySQL Restore

Error indicating query failure

It is possible that either the original database backup you created is corrupt or you are trying to import the file that may be compressed as uncompressed or vice-versa. Please ensure that your Import settings match the backup file you created. In most cases this error is caused by a wrong configuration setting during your Export.

Error regarding temp file

The selected file D:\temp\fileXXXXX.tmp could not be uploaded, because the destination XXXXXX is not properly configured.

This message is most likely due to a configuration issue with your Content Management System not the MySQL database. For instance you may try to go into your Drupal CMS and ensure that under Administration -> Settings -> File System, the file system is set to Private.

Unable to restore due to DB size

If you are dealing with a particularly massive MySQL db, you may prefer to use a very good tool called BigDump. See: Big Dump Database tool.

By reading this article and using its information, you understand and agree that neither nor the author of this article shall be liable or held liable for any indirect or consequential loss or damage arising out of the performance use of any of this information, including data loss or business disruption. Use this information as a general guide and at your own risk.