R1Soft single MySQL database restore from a recovery point

The single most common restore ticket on a busy hosting fleet is not "my server is gone, restore everything" — it is "I deleted the wrong WordPress database, can you pull it back". R1Soft can do this without touching any other database on the host or rolling an account, but the path you take depends on whether the recovery point was taken application-aware and whether the source MySQL is still healthy.

This guide walks both restore paths — the SBM Database Browser wizard for application-aware recovery points, and the side-by-side mysqld workaround for everything else — including verification and the InnoDB gotchas that surprise operators on their first attempt. End-to-end on a 20 GB database: about 15 minutes with the wizard, closer to 25 with the side-by-side path because of the second-instance startup.

Which restore mode to use

The two paths are not equivalent.

• Database Browser restore (SBM UI → Recovery Points → Browse → Database Browser tab). Requires the recovery point to have been taken with the MySQL application-aware add-on. The agent connects to the destination MySQL, recreates the schema, and replays inserts. Safe on a live cPanel host because it operates above the filesystem.
• Side-by-side mysqld restore (file-level). Pulls the raw /var/lib/mysql tree out of any recovery point, starts a second mysqld against it on a different port, and mysqldumps the database you actually want. Works regardless of how the snapshot was taken; the second instance crash-recovers the data files exactly as InnoDB would after a power cut.

Default to the Database Browser when you have it. Drop to the side-by-side path when the recovery point predates your application-aware deployment, when you are restoring across server versions, or when the production MySQL is too sick to talk to.

Prerequisites

• An R1Soft recovery point dated before the data loss event
• Root on the SBM host and on the destination cPanel server
• MySQL or MariaDB running on the destination at the same major version as the source (5.7→5.7, 10.6→10.6, etc.)
• 10–30 GB of free space on the destination — the side-by-side path needs the full /var/lib/mysql tree on disk temporarily
• A 5–20 minute maintenance window — large InnoDB imports briefly lock tables in sibling databases

If the destination is the same server the backup came from, take a fresh recovery point first. A two-minute incremental costs almost nothing and guards against the operator mistake of restoring the wrong database, because you can then walk it back.

Path 1 — Database Browser wizard

This is the supported path; default to it whenever the recovery point is application-aware. Application-awareness is the metadata flag the SBM sets when the agent successfully held FLUSH TABLES WITH READ LOCK at snapshot time.

Open the SBM at https://<sbm-host>:8443/, navigate to Recovery Points for the agent, and find the point you want. The icon column tells you the mode — a green database glyph means application-aware; nothing means filesystem-only and you should drop to path 2.

Click Browse, switch to Database Browser, expand the MySQL instance, and pick the database. The wizard asks for:

• Destination MySQL host — defaults to the same agent, but accepts any reachable address
• Destination credentials with CREATE, DROP, INSERT, ALTER, and RELOAD on the target database (the root credentials from /root/.my.cnf on a cPanel host satisfy all of these)
• Overwrite behaviour — leave Drop and recreate ticked unless you have a reason not to
• Restore scope — data only, schema only, or both

The wizard streams the dump from the disk safe through the agent to the destination MySQL without writing an intermediate file. On a 20 GB database with a 1 GbE link between SBM and agent, expect 12–18 minutes; almost all of that is MySQL replaying inserts, not network transfer.

Path 2 — Side-by-side mysqld from a file-level restore

When the recovery point is not application-aware, do not try to drop .ibd files into production. The cleanest workflow is to restore the entire /var/lib/mysql tree to a staging path, start a second mysqld against it on port 3307, dump the database you want, and import that into production. The second instance handles InnoDB crash recovery transparently — you get the same data the source had at snapshot time, with no manual tablespace gymnastics.

Browse the recovery point in the SBM File Browser, select the entire /var/lib/mysql/ directory, and restore to a staging path on the destination:

mkdir -p /root/restore/mysqld_data
# Use the SBM File Browser to push the contents of /var/lib/mysql here.
chown -R mysql:mysql /root/restore/mysqld_data

Start a side-by-side mysqld against the staging directory:

mysqld --no-defaults \
  --datadir=/root/restore/mysqld_data \
  --port=3307 \
  --socket=/tmp/mysql-restore.sock \
  --skip-grant-tables \
  --skip-networking \
  --user=mysql &

--skip-grant-tables is intentional — you do not know which root password the snapshot was taken with, and the restored mysql.user table may not match the current one. --skip-networking keeps the recovery instance off the network so cPanel scripts and customer apps cannot accidentally hit it.

Once the instance is up (watch the log at /var/log/mysqld-restore.log or wherever your distro places per-pid logs — InnoDB crash recovery on a 20 GB dataset is usually under a minute), dump the database you need:

mysqldump --socket=/tmp/mysql-restore.sock \
  --single-transaction --no-tablespaces \
  --routines --triggers \
  wp_oldsite > /root/wp_oldsite.sql

Then import into production, ideally under a recovery-suffixed name first so you can diff before clobbering anything:

mysql -e "CREATE DATABASE wp_oldsite_recovered;"
mysql wp_oldsite_recovered < /root/wp_oldsite.sql

When you are satisfied, drop the broken production database, rename wp_oldsite_recovered to wp_oldsite, and re-grant the cPanel user against it:

mysql -e "DROP DATABASE wp_oldsite; \
          RENAME DATABASE wp_oldsite_recovered TO wp_oldsite;"
/scripts/setupdbmap

Shut down the side-by-side instance and remove the staging directory:

mysqladmin --socket=/tmp/mysql-restore.sock shutdown
rm -rf /root/restore/mysqld_data

Verification

Whichever path you took, verify in this order:

1. Row counts against the customer's expectation, or against the source if it still exists.
2. CHECK TABLE <t> EXTENDED on every table — InnoDB should return status: OK; MyISAM should report no warnings.
3. For WordPress and similar PHP apps, hit the site and tail /usr/local/cpanel/logs/error_log for wpdb errors that signal column-type mismatches.
4. Foreign-key resolution: SELECT * FROM information_schema.INNODB_FOREIGN WHERE FOREIGN_TABLE_SCHEMA = '<dbname>'; — every row should resolve to a real referenced table.

Take a fresh recovery point immediately after a successful restore so the new state is itself part of your retention chain. Without this, a follow-up restore to a different point will silently undo your work.

Common pitfalls

• Wizard restore into an existing same-named database without overwrite checked. Database Browser fails in the UI but logs Table already exists in /usr/sbin/r1soft/log/cdp.log. Always tick Drop and recreate, or drop the target by hand first.
• AUTO_INCREMENT regression after file-level restores. InnoDB persists its AUTO_INCREMENT counter irregularly; a crash-recovered instance resets it to the highest value in the data file, which may be lower than the application expects. Bump it explicitly with ALTER TABLE <t> AUTO_INCREMENT = <n> if the application is about to insert.
• Cross-major-version restores. A mysqldump from MariaDB 10.11 will import into MySQL 8.0 only with hand-edits — collation defaults, utf8mb3 vs utf8mb4, and DEFINER clauses all bite. Restore to a matching-version side-by-side instance, dump with --compatible=mysql80 or --compact --no-create-db, then import.
• Restoring mysql.* system tables. If you mysqldump --all-databases on the side-by-side instance and import that whole dump, you will overwrite cPanel's mysql.user, mysql.db, and mysql.proxies_priv tables — usually wiping the destination's grants. Always name the specific database in both the dump and the import.
• Forgetting /scripts/setupdbmap after a rename. cPanel maintains its own database-to-user mapping; after RENAME DATABASE, the cPanel UI will not show the database to the owner until setupdbmap (or /usr/local/cpanel/bin/dbmaptool) is run.

Next steps

• R1Soft MySQL application-aware backups — wire up the add-on so future restores all use path 1.
• R1Soft cPanel plugin self-service restore — let customers restore their own databases through cPanel and stop generating these tickets.
• JetBackup restore single file (cPanel) — the JetBackup equivalent for hosts running both backup stacks.

Need more agents in the fleet, or a second SBM for offsite replication? See R1Soft licensing.