Exporting, Backup, Restore, Importing Big Huge MySQL Tables can be a problem. Here’s how to successfully backup and restore
What if your WordPress website has a table or set of tables that are huge for no reason? They’ve become bloated with orphaned records. You want to try to clean up the mess, but what if you damage the database and need to restore it?
Tables can become so big that it’s problematic to backup and restore them.
The last thing you want is a site that’s down and you can’t seem to get a database or even just a single table restore to work.
You should backup your database(s) occasionally and absolutely before making any changes that can require a restore if something goes wrong.
When managing WordPress, a lot of people tread with the fools and will load a plugin and allow it to make modifications to the WordPress database without having a valid backup that can be restored.
If you have tables in your database that have grown really large, you may have trouble restoring them (importing). The time to know this is before you need to do the restore!
See my Recommended WordPress Plugins list for plugins helpful with backup/restore.
My best practice is to use a plugin such as BackWPup to make a backup of WordPress including the database, and then to use cPanel’s phpMyAdmin to make an export of either the complete WordPress database, or, just the tables that will be changed by whatever you’re working on.
Steps I use to export tables using phpMyAdmin:
- Select database in left column
- Click “Export” across top
- Select “Custom – display all possible options”
- Uncheck “Structure” and “Data” across top of table list
- Check box to left of tables you need to backup/export
- In “Object Creation Options” section
- Check “Add Drop Table / View…”
- In “Data Creation Options” section
- Select “both of the above”
- Change “Maximal length of created query to 5000
- Click “GO”
- Give your sql export a name that makes sense and remember where you saved it.
- After it’s finished, use a text editor (Notepad++) to take a quick look at it to make sure it actually contains what you expect it to contain.
You now have a sql file you can import to restore your table(s).
But what if receive mySQL Import Errors because it’s too big?
You can use a utility/script to get around this problem. There’s a very nice script called “BigDump: Staggered MySQL Dump Importer” (I’m not affiliated to this person or utility in any way other than I’ve used it). Basically you copy your export and this script (after you edit it) to your website and then run the script from a browser. It has saved my tail a few times.
Steps to use BigDump correctly:
- Rename the downloaded php file something unique so that robots won’t find it on your site (you should remove it afterwards but still take some precautions).
- Edit the php file to reflect your database, database user, database password, and exported sql file name.
- Zip the sql export and the php script file and then upload them to a folder under your website.
- Use a browser to execute the script.
- Delete the script!
I get an error on import about duplicate primary keys:
This can happen if your table(s) need repairing and if your sql dump includes recreating primary keys and you have duplicate records that are trying to create duplicate primary keys. You can edit your SQL file to ignore the duplicates. Look at the end of your sql dump for the “alter table” command that’s creating the primary key and change it to “alter ignore table”.
I hope this saves your biscuit!
The Backstory…
So you may wonder what prompted this blog post. I found that a plugin I use on several WordPress sites is leaving orphaned records in certain tables. It creates a few of it’s own tables, and they get some of this bloat, as does the wp_post and wp_postmeta tables. I set out to clean this up, but on the worst site, the tables had become so bloated that I wanted to test restoring in case the cleanup caused a catastrophic failure. That’s when I hit the issues about importing (or restoring) mySQL dumps that are over a certain size or take over your script time limits. I’ve alerted the creator of the plugin and I’m also writing a plugin to cleanup the mess (more to come soon on that)!
Until next time,
Fred
[addtoany]