Saturday, May 21st, 2022

Partial database restores from a backup

So maybe you accidentally deleted 600,000 customer records and related user meta from a WordPress site. Or maybe I did. Regardless, it is a problem and they need to be restored now.

Thankfully, there’s a backup (boy Howdy, you better hope so). Our site is hosted with WP Engine, so we restored a snapshot to a new environment before the accidental deletion.

We did not delete all user records, only a subset. But the easiest way to restore them from a backup was to export all users and usermeta within a specific range—but then restore only those that were missing. We did it using WP CLI.

This is how we exported the partial users table:

wp db export user-range-1.sql --tables=wp_qftw_users --where="ID > 200000 AND ID 

This is how we exported a partial usermeta table:

wp db export usermeta-range-1.sql --tables=wp_qftw_usermeta --where="user_id > 200000 AND user_id 

The "--no-create-info=true" flag is important! This tells MySQL not to drop the existing table before re-importing. Otherwise you would be deleting all the existing users on your site instead of just restoring the missing ones.

Now, you'll need to get those .sql files onto your production site and run the following to restore the users and usermeta that is missing.

wp db import user-range-1.sql --insert-ignore
wp db import usermeta-range-1.sql --insert-ignore

The "insert-ignore" flag allows us to import only users and usermeta that do not have an existing ID on the site. Existing user and user meta will not be touched.

This will also work for restoring other types of data, such as orders or products or posts—as long as you're correctly restoring data from all tables that have been deleted, and assuming that your data changes between backup and production. IDs are unique.

Good luck if you find yourself in this situation!

Source link