Physical backups: XtraBackup and mariadb-backup

An operator must replace a dying database host that holds 220 GB of InnoDB files, and reloading last night’s dump would rebuild every row and index before the new host could serve. A physical copy skips that work: the new host starts on the pages the old one already wrote and catches up from a saved binary-log position. A physical backupphysical backup: A backup that copies the server's binary data files — InnoDB tablespaces, redo logs, binary logs — and is restored by copying them back; faster than a logical reload but tied to the server's version and layout. is the files the server is already writing, carried to another folder or host.

Three tools make one: the clone plugin inside MySQL, Percona XtraBackupPercona XtraBackup: Percona's open-source program for physical hot copies of MySQL and Percona Server; the 8.4 line supports MySQL 8.4 and Percona Server 8.4 and runs on Linux as packages, tarballs or Docker images. on the host, and mariadb-backupmariadb-backup: MariaDB's physical backup program, forked from Percona XtraBackup 2.3.8 and shipped with MariaDB. It copies InnoDB, Aria and MyISAM tables on Linux and Windows. for MariaDB servers. All three take a hot copyA copy made while the server keeps writing, so the tool must also capture the InnoDB redo written during the copy; the opposite of a cold copy where the server is stopped first., and the server and its host decide which one fits.

A hot copy is files plus the redo written meanwhile

A physical backup is not a stream of SQL. It is the data files themselves: InnoDB tablespaces, the undo logs, the redo log and the server’s metadata, read while the server runs. One idea underpins every hot-copy tool: the files copied one at a time, with the server still writing to them, do not match any single moment. The copy, on its own, is inconsistent. Step through the five stages, and the shortcut that skips one:

The drawing shows the whole scene; its 6 steps are listed below.

Every step as text

1. The files, copied while in use. The tool reads each tablespace file while the server keeps writing to it. Pages changed before the tool reached them arrive new and pages changed after arrive old, so the copied files on their own match no single moment.
2. The redo written meanwhile. InnoDB writes every change to its redo log before the change reaches a data file. The tool keeps copying the redo written from the start of the file copy to its end, so every change the files may have missed is in the copy as well.
3. Where the copy stands. The copy ends at one point of the server's history. The tool records the binary log file, the offset in it and the GTID set of that point, which is where a new replica or a point-in-time restore carries on from.
4. A shortcut that fails. Started straight on the copied files, InnoDB finds pages from different moments and stops rather than run on damaged data. Percona's documentation says so for an unprepared XtraBackup copy.
5. The redo, applied. InnoDB's crash recovery replays the copied redo onto the copied pages and rolls back what was not committed. XtraBackup runs it as a separate prepare step; a clone runs it when a server first starts on the copy.
6. A data directory again. After recovery the copy is an ordinary data directory, consistent at the moment the copy ended. A server started on it finds the rows of that moment, and not a row written later.

The redo logredo log: InnoDB's on-disk log of the changes made to pages, written before the changed pages reach the data files. After a crash, recovery replays it, so committed changes that were missing from the data files are restored. More in “Redo log, doublewrite and crash recovery” closes the window. InnoDB writes every change to redo first, before the change reaches a data file. A tool that keeps copying the redo written from the start of the file copy to its end carries, with it, every change the files may have missed. After the redo is applied, the folder is a consistent data directory of one point in the server’s history.

The server and its host choose the tool

The clone pluginclone plugin: A server plugin, mysql_clone.so, that copies a running donor server's data over the network into a recipient server: CLONE INSTANCE FROM removes the recipient's own schemas, tables and binary logs and writes the donor's data in their place. More in “Set up GTID replication with TLS” is mysql_clone.so (.dll on Windows), loaded by INSTALL PLUGIN clone and driven by CLONE LOCAL DATA DIRECTORY or CLONE INSTANCE FROM ...; it copies InnoDB only, and MyISAM or CSV tables arrive on the copy as empty tables. Percona XtraBackup is a Linux program on the database host that reads the files directly; its 8.4 line supports MySQL 8.4 and Percona Server 8.4 and refuses older or newer servers by default. MariaDB’s mariadb-backup, forked from Percona XtraBackup 2.3.8 and shipped by MariaDB, is the physical tool a MariaDB server pairs with; it runs on Linux and Windows and copies InnoDB, Aria and MyISAM tables alike. Answer the table’s questions for your own server:

Which server do you need to copy?

MySQL 8.4 MariaDB

Which operating system runs the database server?

Linux Windows macOS

What is the copy for?

Backups kept for days or weeks A second server, today

Clear the answers

Answer the questions above: the table then marks what fits your case and says why the rest does not.

The clone plugin, Percona XtraBackup and mariadb-backup against the four facts that decide between them.

Tool	Servers it copies	Where it runs	What you are left with	Engines it copies
Clone plugin Runs as: a plugin inside MySQL, driven by SQL statements	Servers it copies Good MySQL 8.4, with donor and recipient in the same release series	Where it runs Good wherever MySQL runs, with donor and recipient on the same platform	What you are left with Mixed a full copy each time: a data directory or a running server, no backup chain	Engines it copies Mixed InnoDB only; MyISAM and CSV tables arrive as empty tables
Percona XtraBackup 8.4 Runs as: a program on the database host	Servers it copies Good MySQL 8.4 and Percona Server 8.4; it refuses 8.0 and 9.x servers	Where it runs Mixed Linux: Percona ships packages, tarballs and Docker images	What you are left with Good backup folders to keep: full or incremental, compressed or encrypted	Engines it copies Good InnoDB, MyISAM and MyRocks
mariadb-backup Runs as: a program on the database host	Servers it copies Mixed MariaDB only, run in the version that matches the server	Where it runs Good Linux and Windows	What you are left with Good backup folders to keep: full, incremental or partial	Engines it copies Good InnoDB, Aria, MyISAM and MyRocks

How the answers decide

• “Which server do you need to copy?” MariaDB: rules out Clone plugin and Percona XtraBackup 8.4. The clone plugin and XtraBackup 8.4 copy MySQL 8.4 servers; MariaDB documents mariadb-backup for its own servers.
• “Which server do you need to copy?” MySQL 8.4: rules out mariadb-backup. mariadb-backup is documented for MariaDB servers; for MySQL 8.4 the documented tools are the clone plugin and XtraBackup 8.4.
• “Which operating system runs the database server?” Windows: rules out Percona XtraBackup 8.4. Percona documents XtraBackup for Linux hosts only.
• “Which operating system runs the database server?” macOS: rules out Percona XtraBackup 8.4 and mariadb-backup. Neither program is documented for macOS. The clone plugin runs inside the server, so it copies wherever MySQL itself runs.
• “What is the copy for?” Backups kept for days or weeks: adds a caveat to Clone plugin. Every clone is a full copy with no incremental mode, so two weeks of daily copies means fourteen full data directories.
• “What is the copy for?” A second server, today: adds a caveat to Percona XtraBackup 8.4 and mariadb-backup. A backup folder is prepared and copied back before a server runs on it; a clone from a donor ends with the recipient running on the copy.

A MySQL 8.4 server on macOS has one fit: the clone plugin, which runs wherever MySQL runs. A MySQL server on Linux has two: the clone plugin, or XtraBackup for backup folders kept for weeks. A MariaDB server needs mariadb-backup: MariaDB ships no clone plugin, and XtraBackup 8.4 supports only MySQL and Percona Server 8.4.

Why physical restores are faster for large databases

A logical restore pays for every row: the SQL file is parsed, every INSERT runs through the optimiser, every secondary index is built again from the rows. A physical restore pays once, for the bytes. Take the 220 GB host of the opening and a disk that writes 400 MB per second: 220,000 MB ÷ 400 MB/s = 550 s, a little over nine minutes to put the pages back, before the redo is applied. The table compares the two restores of that host:

The 220 GB host	Physical restore	Dump reload
Secondary indexes	already in the pages	built again from the rows
Time to write the data	550 s at 400 MB/s	no formula: it depends on the schema

Both times stay estimates until a restore drillrestore drill: A rehearsed restore of a real backup into a throwaway server, timed and checked against the source, so the backup and its recovery time are proven before a real failure. More in “Backup strategy, RPO, RTO and restore drills” measures them on your own hardware. A dump still wins its own jobs. It is human SQL, so a reader may edit it, move one schema into a test server, or restore the shape of a dropped table alone, and it crosses storage engines and major versions a physical copy will not. Teams keep both: a nightly dump for selective restores, a weekly physical copy for a fast full restore.

Clone a running MySQL 8.4 instance, local and remote Pro: opens with a Pro, Premium or Ultimate pass

The clone plugin is MySQL’s own hot-copy tool, driven by SQL. Two statements cover the common cases: CLONE LOCAL DATA DIRECTORY = '/absolute/path' writes a copy on the same server, and CLONE INSTANCE FROM 'user'@'host':port copies one running server, the donorclone donor: In a MySQL clone, the running server whose InnoDB data files are copied, either into a folder on the same host or to another server., into another, the recipientclone recipient: In a remote MySQL clone, the server that receives the donor's copy of the data files in place of its own data.. Both run below on throwaway MySQL 8.4.11 servers, with the grants, the donor-list guard, the restart and the clone-status row a replica reads to catch up.

In this part:

• A local clone, start to finish
• Remote clone: the grants, the donor list, the restart

2 runnable examples (clone_instance_session.py, clone_local_session.py, mysql_session.py) · about 6 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

• Pro pass · 1 week · ₹79 · $2.99
• 30 days · ₹179 · $5.99
• 1 year · ₹1,699 · $45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a passI have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

XtraBackup and mariadb-backup: backup, prepare, restore Pro: opens with a Pro, Premium or Ultimate pass

Percona XtraBackup and mariadb-backup copy the same kind of files as the clone plugin, but outside the server process, as programs on the host. The workflow is three verbs plus a start: xtrabackup --backup reads the files and the redo as the server runs, xtrabackup --prepare applies the copied redo and rolls back uncommitted work, xtrabackup --copy-back writes the prepared folder into an empty data directory, and mysqld starts on it. The version-match rule decides whether a backup is safe to take and prepare at all.

In this part:

• A full and an incremental backup, on a Linux host
• Match XtraBackup to the server, and when the version-check flag is risky
• mariadb-backup runs the same three steps on MariaDB
• Key takeaways from these parts

4 runnable examples (Python, Shell) · about 7 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

• Pro pass · 1 week · ₹79 · $2.99
• 30 days · ₹179 · $5.99
• 1 year · ₹1,699 · $45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a passI have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Practise

The adaptive session asks about the hot copy, the choice of tool and restore speed.

Practice that adapts to you

Practise this lesson

6 questions about 5 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.

1. Question 1 of 6 A hot copy of a running MySQL server has finished the file copy. The server has kept writing. Which pair of actions makes the copy consistent for a fresh server to start on?

   Choose one answer.

   Replay the binary log on top of the files Run REPAIR TABLE on every InnoDB table Copy the InnoDB redo written during the file copy, then apply it onto the copied pages

   Show the answer: Question 1 of 6

   Answer: Copy the InnoDB redo written during the file copy, then apply it onto the copied pages

   The redo written while the files were read is the record of every change the files may have missed. Applied to the copied pages it brings the folder to one moment; the binary log is for carrying on from that moment.

2. Question 2 of 6 A database stores a payments ledger in a MyISAM table and the rest of its tables in InnoDB. The team runs the clone plugin for a nightly copy. What arrives on the copy?

   Choose one answer.

   Both tables, in full Only the InnoDB tables; the MyISAM table does not appear at all The InnoDB tables with their data; the MyISAM payments table as an empty table

   Show the answer: Question 2 of 6

   Answer: The InnoDB tables with their data; the MyISAM payments table as an empty table

   The clone plugin is InnoDB-only. A shop that still uses MyISAM for one table pairs the nightly clone with a targeted mysqldump of that table, or converts it to InnoDB.

3. Question 3 of 6 You run MySQL 8.4 on Windows Server and want a physical backup folder you can keep for a week. Which of the three tools fits?

   Choose one answer.

   The clone plugin mariadb-backup Percona XtraBackup 8.4

   Show the answer: Question 3 of 6

   Answer: The clone plugin

   The platform narrows the choice before any other criterion. The clone plugin is the one that runs on Windows, because it is a MySQL plugin and lives inside the server.

4. Question 4 of 6 A company runs its billing database on MySQL 8.4 on a Windows Server host and its website on MariaDB on Linux. Which physical tool fits each server?

   Choose one answer.

   Percona XtraBackup 8.4 for both servers The clone plugin for the MySQL server, and mariadb-backup for the MariaDB server The clone plugin for both servers mariadb-backup for both, since it also runs on Windows

   Show the answer: Question 4 of 6

   Answer: The clone plugin for the MySQL server, and mariadb-backup for the MariaDB server

   Two facts settle each server: the server family and the platform it runs on. MySQL 8.4 on Windows leaves only the clone plugin, and a MariaDB server takes mariadb-backup on Linux or Windows alike.

5. Question 5 of 6 Which pair of jobs usually makes a logical dump the better pick, even though a physical restore is faster for a full database?

   Choose one answer.

   Restoring the definition of one dropped table, and moving a schema to an older major version Shipping the binary log off the host every night, and seeding a 1 TB replica in a hurry Restoring a full database as fast as possible, and a next-hour recovery for a 1 TB server

   Show the answer: Question 5 of 6

   Answer: Restoring the definition of one dropped table, and moving a schema to an older major version

   A physical copy wins the "put a server back online" job; a logical dump wins the "bring back one table's definition" and "move this across versions" jobs. Teams keep both and run the right one for the job.

6. Question 6 of 6 A 400 GB InnoDB database is streamed by a physical backup across a 1 Gbit/s link at an effective 100 MB/s. How many minutes does the raw byte copy take, to the nearest minute?

   Type a number.

   Your answer minutes

   Show the answer: Question 6 of 6

   Answer: 67 minutes (anything from 65 to 69 counts)

   400 GB divided by 100 MB/s is 4,000 seconds, or about 66.7 minutes, so the raw copy is close to 67 minutes. The restore on the target adds the redo apply and the server start; a dump of the same data would still re-run every INSERT and rebuild every index, a cost that depends on the schema and that a restore drill measures.

Interview questions

Warm-up, for freshers: what does a physical backup copy, and what must it also capture when the server keeps writing during the copy?

A physical backup copies the data files: the InnoDB tablespaces (ibdata1, each .ibd), the undo logs, the system data dictionary and the server metadata, read while the server runs. For a hot copy the pages do not match any single moment, so the tool also copies the InnoDB redo written between the first file read and the last. On restore the server replays that redo onto the pages, rolls back uncommitted work, and the folder matches the moment the copy ended.

Three harder questions follow. Decide how you would open each answer before you read its first step.

Key takeaways

• A hot physical backup is the data files plus the redo written during the copy; applied on restore, the redo makes the files match one moment of the database.
• The clone plugin runs inside MySQL wherever MySQL runs and copies InnoDB only; Percona XtraBackup is a Linux program for MySQL 8.4 and Percona Server 8.4; mariadb-backup is MariaDB’s own tool.
• A physical restore writes the copied pages once, about 550 s for 220 GB at 400 MB/s, while a dump reload re-runs every INSERT and rebuilds every index; teams still keep a dump for selective and cross-version restores.

Terms in this lesson

clone donor

In a MySQL clone, the running server whose InnoDB data files are copied, either into a folder on the same host or to another server.

clone recipient

In a remote MySQL clone, the server that receives the donor's copy of the data files in place of its own data.

mariadb-backup

MariaDB's physical backup program, forked from Percona XtraBackup 2.3.8 and shipped with MariaDB. It copies InnoDB, Aria and MyISAM tables on Linux and Windows.

Percona XtraBackup

Percona's open-source program for physical hot copies of MySQL and Percona Server; the 8.4 line supports MySQL 8.4 and Percona Server 8.4 and runs on Linux as packages, tarballs or Docker images.