mysqldump one-liner to export WordPress tables in a shared database

Depending on your web hosting situation, you may find yourself having to share a database among multiple WordPress installations, or perhaps other tables that have nothing to do with WordPress whatsoever. This is usually not a significant issue for your day-to-day because WordPress allows you to prefix your WP tables to be anything you like.

The challenge comes with backup and migration, particularly if you’re doing it manually via the shell command line.

Suppose you have a whole bunch of WP installations on a staging server awaiting review, all sharing a database. Now client XYZ has approved the site and it’s time to go live. We’ll take a mysqldump of just those tables and migrate that to the new server.

mysqldump takes, in addition to the authentication (-u and -p – and possibly -h) and database arguments, can take a list of tables to be included in the export. Once you supply even one table, any table not named is ignored. The problem is, particularly if you use plugins that create their own tables, the fixed list of tables can be a moving target. Can’t we select all tables with a given prefix?

Selecting all tables with a given prefix

Don’t you love my redundant heading? But here’s how we get a list of tables given a specific prefix (in this example, the prefix is ‘wpXYZ_’)

mysql -uUSERNAME -pPASSWORD --skip-column-names -e "select table_name from information_schema.TABLES where TABLE_NAME like 'wpXYZ_%';"

This will output a list of all the tables that start with our prefix ‘wpXYZ_’. So far so good. Note the –skip-column-names option.

We know how to perform a database dump for selected tables using:

mysqldump -uUSERNAME -pPASSWORD DATABASENAME TABLE1 TABLE2 TABLE3 etc... > filename.sql

Putting it all together

We’re going to use the unix pipe “|” along with xargs to “glue” these two parts together. Essentially what we want to d is use our earlier “select” statement to get a list of all the tables we want to export, and tack that to the end of our mysqldump statement. Here’s how it works:

mysql -uUSERNAME -pPASSWORD --skip-column-names -e "select table_name from information_schema.TABLES where TABLE_NAME like 'wpXYZ_%';" | xargs mysqldump -uUSERNAME -pPASSWORD DATABASENAME > filename.sql

Bonus marks: gzipping the sucker

Hey, since we’re on a one-liner roll, let’s compress the database dump using gzip so that it’s a smaller file to scp up to our production server. Save some bandwidth, save a tree, save the world. Save the cheerleader. Whatever.

mysql -uUSERNAME -pPASSWORD --skip-column-names -e "select table_name from information_schema.TABLES where TABLE_NAME like 'wpXYZ_%';" | xargs mysqldump -uUSERNAME -pPASSWORD DATABASENAME | gzip -c > filename.sql.gz

Happy migration!