Backup, restore and PITR with Percona XtraBackup

Physical backups with Percona XtraBackup: installing the matching XtraBackup version, a backup user with auth_socket, a compressed/encrypted full + incremental backup script, scheduling with a systemd timer or cron, binlog copying and a restore drill. Scenario cards cover full restore, restore from an incremental chain, recovery to a specific point in time (PITR), single-table restore, building a replica from a backup and restoring to another server. Use this guide for daily backups of large databases and for disaster recovery; for migrations, single schema/table dumps or moving between versions use the logical backup (mysqldump / MySQL Shell) guide.

Topology: 1 server (standalone, source, replica or InnoDB Cluster secondary); backup storage on a local disk or NFS

Scenarios

• Set up backups, schedule them and test a restore (full flow) — XtraBackup installation, backup user, full + incremental backup script, scheduling and a restore drill (including PITR). Choose this if you are setting it up for the first time. The backup is taken while the server is running; no downtime, only extra disk/CPU load.
• Full restore: return to the latest full backup — The data directory is lost/corrupted or the server does not start; returning to the chain's full backup is enough. MySQL is down for the whole restore.
• Restore from a full + incremental chain (up to the latest incremental) — You want to return to the time of the latest incremental backup (or an incremental of your choice); the incrementals are applied on top of the full backup in order with --apply-log-only. MySQL is down for the whole restore.
• Recover to a point in time (PITR): before a wrong DELETE / DROP — Data deleted or corrupted by mistake: the latest backup is restored and the binlogs are applied up to just before the faulty transaction (by time, position or GTID). MySQL is down for the whole restore and writes made after the faulty transaction are rolled back too; if only one table is affected, consider the single-table scenario first.
• Restore a single table (--export + IMPORT TABLESPACE) — You only need the backed-up version of one table; without restoring the whole server, the table is imported from the backup as t_restore and the data is fixed from there. The server stays up.
• Build a new replica from a backup — You want to prepare a new (or rebuilt) replica from an existing XtraBackup backup instead of cloning over the network, and connect it to the source (large data, load on the source). The source is not affected; the target server's data is replaced.
• Restore the backup to another server (migration, test, clone) — You want to open a production backup on a test/development server, move a server, or verify on a separate server that the backup really restores. The source server is not affected.

Before you start

• MySQL … is running (if it was installed with the DBAtlas MySQL single-server installation guide, the paths are the same); the operating system root user can connect without a password using the mysql command (auth_socket or ~/.mylogin.cnf).
• The backup directory … is on a separate disk/NFS with at least (data size × number of chains) of space… (zstd usually shrinks it 3–6 times)…. A restore needs at least twice the data size free under … (decompression + prepare).
• Binary logging is enabled and GTID is on (for PITR and incremental consistency).
• The server can reach repo.percona.com over HTTPS, or an internal mirror is configured.
• A safe place outside the server (vault, password manager) is ready to keep a copy of the encryption key. If the key is lost, the backups cannot be opened.
• Preferably there is a separate test server for the restore test (installed with the same MySQL version and the same my.cnf). The restore steps replace the data directory of the server they run on.
• This server is a healthy replica: IO and SQL threads are Yes, lag is low. If binlogs are copied (PITR), log_replica_updates must be enabled (on by default in 8.x); otherwise the replica's binlog does not contain the transactions coming from the source.
• This server is an InnoDB Cluster member and is currently SECONDARY / ONLINE. The backup is scheduled on this member; if the member later becomes primary, the script logs a warning and still takes the backup.
• The backup chain to restore is under …… (on server …)… and complete: the full and the incrementals to be used, together with the -lsn metadata directories…; encryption key at …….
• MySQL … and XtraBackup … are installed on this server; the MySQL minor version is the same as or newer than that of the server the backup was taken on (the backup's server_version value is shown in the diagnostic step).
• …There is no data to keep on the target server: the data directory is replaced with the backup (the old one is moved aside as .old-<timestamp>).…The maintenance window and application downtime are approved: MySQL is down for the duration of the restore; the application must not connect to this server.…
• The table is InnoDB and in its own .ibd file with innodb_file_per_table = ON (the 8.x default); the table structure (columns, indexes) has not changed since the backup was taken. The server stays up.

Phases of the guide

• XtraBackup installation — Add the Percona repository · Install XtraBackup
• Backup user and key — Check the auth_socket plugin · Create the backup user · Test the connection · Create the encryption key
• Backup script and scheduling — Create the backup directory · Write the backup script · Make the script executable · Take the first full backup · Try an incremental backup · Write the systemd service …
• Restore — Copy the chain to the working directory · Decrypt / decompress · Prepare the full backup (prepare, apply-log-only) · Apply the incrementals in order · Final prepare · Stop MySQL and move the existing data aside …
• Point-in-time recovery (PITR) — Determine the target time · Apply the binlogs up to the target time · Verify the result and take a new backup
• Cleanup — Delete the moved-aside data and the working directory
• 1. Diagnosis: which backup, which method? — Check the server state and versions · List the backup chains and their contents
• 2. Safety net — Record the current state and configuration
• 3. Apply: prepare the backup — Copy the chain to the working directory · Copy the chain to the working directory · Decrypt / decompress · Prepare the full backup (prepare, apply-log-only) · Apply the incrementals in order · Final prepare
• 3. Apply: restore the data — Stop MySQL and move the existing data aside · Copy the backup to the data directory (copy-back) · Start MySQL and verify · Check the replication configuration restored from the backup
• 3. Apply: apply the binlogs up to the target (PITR) — Verify the faulty transaction and the target point · Apply the binlogs up to the target
• 4. Verification — Verify that the faulty transaction was undone · Verify the data
• 5. Afterwards — Take a new full backup · Delete the moved-aside data and the working directory · Root cause and topology
• 1. Diagnosis: are the table and the backup suitable? — Show the current state and structure of the table · List the backup chains and their contents
• 2. Safety net — Back up the current state of the table
• 3. Apply: prepare the backup with --export — Copy the chain to the working directory · Decrypt / decompress · Prepare the full backup (prepare, apply-log-only) · Apply the incrementals in order · Final prepare (--export)
• 3. Apply: import the table into a staging table — Create the staging table and discard its tablespace · Copy the files from the backup and import them (IMPORT TABLESPACE)
• 4. Verification — Check the staging table and compare it with the original table
• 5. Afterwards: copy the data and clean up — Copy the required rows into the original table · Drop the staging table and delete the working directory
• 1. Diagnosis: are the target server and the backup suitable? — Check the target server's version, identity and disk · List the backup chains and their contents (…)
• 2. Safety net — Record the current state and configuration
• 3. Apply: pull and prepare the backup — Pull the chain from the backup server · Copy the chain to the working directory · Decrypt / decompress · Prepare the full backup (prepare, apply-log-only) · Apply the incrementals in order · Final prepare
• 3. Apply: restore the data — Stop MySQL and move the existing data aside · Copy the backup to the data directory (copy-back) · Start MySQL and verify · Check the replication configuration restored from the backup
• 3. Apply: start replication from the backup's position — Determine the starting point · Configure and start replication
• 4. Verification — Verify the data
• 5. Afterwards — Delete the moved-aside data and the working directory · Put the server into service in its new role