MySQL Logo

The other day I was testing a feature on my development box when I realized my local data was really out of date, and if I was going to get anywhere with my testing, I needed some recent data from production. The problem is that our main MySQL database is just enormous, and restoring a 10GB MySQL database takes hours on even the fastest box.

Sure, you can point and laugh about why we’re not using a better database, like maybe PostgreSQL, but since our sites are based on WordPress, we’re stuck with MySQL and have to make the best of it.

Luckily I remembered that command-line arguments exist, and looked to see if there was a way to export only a single table using mysqldump, and sure enough, there was. The syntax goes like this:

mysqldump [OPTIONS] database [tables]

Of course that example sucks, so here’s a better one — if you want to backup only the wp_users table from your WordPress database named wordpressdb, you’d do something closer to this, which would export it all into a file called users.bak.

mysqldump -uUser -pPassword -hHostname wordpressdb wp_users > users.bak

You’d want to replace User, Password, and Hostname with the values from your wp-config, of course.

If you wanted to export multiple tables — for instance, if you realized you probably need wp_usermeta if you’re going to export wp_users — you would simply add that to the command:

mysqldump -uUser -pPassword -hHostname wordpressdb wp_users wp_usermeta > users.bak

To import that backup later on, transfer it wherever you want to use it, and run something like this:

mysql -uUser -pPassword -hHostname databasename < users.bak

And that’s the story of how one table was exported and my local database is now up to date.

Profile Photo for Lowell Heddings Lowell Heddings
Lowell is the founder and CEO of How-To Geek. He’s been running the show since creating the site back in 2006. Over the last decade, Lowell has personally written more than 1000 articles which have been viewed by over 250 million people. Prior to starting How-To Geek, Lowell spent 15 years working in IT doing consulting, cybersecurity, database management, and programming work.
Read Full Bio »