A WordPress plugin wiped the site’s settings. A WooCommerce update broke the product data. An import changed the wrong posts. The problem is almost always in a single table of the WordPress database, and restoring the whole database also undoes everything the site did since the last backup: orders, comments, sign-ups. Can you bring back only the table that broke? How fast? Does it come back the same?
Why it matters to whoever runs the server
- Restoring the whole WordPress database undoes the orders, comments and sign-ups made since the backup. Bringing back only the table that broke doesn’t.
- 3.3 s instead of 121 s for the wp_options of a 9 GB database: the site is back in seconds.
- Before touching anything, Salvo keeps a copy of the database as it is: if the restored table isn’t the right one, you can go back.
| The WordPress table | One file per database | One file per table |
|---|---|---|
| wp_options (1.7 MB), 9 GB database | 121 s | 3.3 s |
| wp_postmeta (99 MB), 9 GB database | 134 s | 14 s |
| wp_options (5.5 MB), 1.3 GB database | 17 s | 4.1 s |
| wp_postmeta, the largest table (361 MB, 28% of the 1.3 GB database) | 42 s | 32 s |
| wp_posts (95% of the 9 GB database) | 783 s | 829 s |
| The whole WordPress database (9 GB) | 893 s | 922 s |
| Account backup (5 sites, 17.6 GB of databases) | 691 s | 678 s |
| Space on the storage | 2.03 GB | 2.03 GB |
Method
Five databases from real WordPress sites, 1.2 to 13 GB and 63 to 282 tables each (a large blog and corporate sites; none was a WooCommerce store), in one account on the test server.
Salvo now has two formats for databases in the backup, chosen when creating the job: one file per database, the default, or one file per table: the same snapshot of the database, taken all at once (every table from the same instant, as WordPress expects), stored table by table.
The account was backed up in both formats, each to a separate storage, to measure the space of each one. All from the Salvo panel in WHM: creating the jobs, running the backup and restoring the tables.
Each table was restored to a new database, next to the original, and checked by checksum against the original: all identical, in both formats.
Why the gain changes: in the one-file format, Salvo has to fetch the whole database from the storage to take one table out of it; in the per-table format, it fetches only that table. The MySQL import takes the same time in both. So the gain grows the smaller the table is compared with the database, and disappears when the table is almost the whole database, like the wp_posts of a very large blog.
What we decided
The tables that break most often in WordPress (wp_options, wp_postmeta, users, comments) are small compared with the database, exactly where the gain is largest. But the per-table format weighs more on the server during the backup: it uses temporary space the size of the largest database and raises the load. That’s why it’s the admin’s choice, per job, and the default is still one file per database. On restore, from the panel, the table can go to a new database alongside, to compare before swapping, or replace just that table in the site’s database; before touching anything, Salvo keeps a copy of the database as it is.