Physical backups

Last reviewed: 2026-02-11 / Plakar v1.1.0

Overview

Physical backups copy raw database directories and files directly from the MySQL data directory. This approach is faster than logical backups and produces more compact output, but requires MySQL to be stopped or locked during backup.

For a deeper understanding of physical backups and backup methods, refer to the official MySQL documentation on backup methods.

Prerequisites

• Running MySQL server with accessible data directory
• Root or mysql user privileges
• MySQL server stopped or ability to apply read locks

Data Consistency

Copying the data directory while MySQL is running without proper locking produces inconsistent backups.

Back Up with MySQL Stopped

Stop MySQL, back up the data directory, then restart:

sudo systemctl stop mysql.service
sudo plakar at /var/backups backup /var/lib/mysql
sudo systemctl start mysql.service

Data Directory Location

Check datadir in /etc/mysql/my.cnf or /etc/my.cnf if your data directory differs.

Back Up with Read Lock

Minimize downtime using FLUSH TABLES WITH READ LOCK:

mysql -u root -p << EOF
FLUSH TABLES WITH READ LOCK;
SYSTEM sudo plakar at /var/backups backup /var/lib/mysql
UNLOCK TABLES;
EOF

Write Operations Blocked

All write operations are blocked during backup. Lock releases automatically if connection drops.

Back Up Specific Databases

Back up individual database directories:

sudo systemctl stop mysql.service
sudo plakar at /var/backups backup /var/lib/mysql/<dbname>
sudo systemctl start mysql.service

Replace <dbname> with the target database name.

Restore Physical Backup

Before Restoring

Stop MySQL and back up or move the current data directory.

List snapshots:

plakar at /var/backups ls

Restore:

sudo systemctl stop mysql.service
sudo mv /var/lib/mysql /var/lib/mysql.old
sudo plakar at /var/backups restore -to /var/lib/mysql <SNAPSHOT_ID>
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql.service

Restore Specific Databases

Restore individual database directories:

sudo systemctl stop mysql.service
sudo rm -rf /var/lib/mysql/<dbname>
sudo plakar at /var/backups restore -to /var/lib/mysql/<dbname> <SNAPSHOT_ID>
sudo chown -R mysql:mysql /var/lib/mysql/<dbname>
sudo systemctl start mysql.service

Run MySQL in Docker from Backup

Restore backup and run MySQL in Docker (requires matching MySQL version):

plakar at /var/backups restore -to ./mydb <SNAPSHOT_ID>
sudo chown -R 999:999 ./mydb
docker run --rm -ti --name mysql \
  -v ./mydb:/var/lib/mysql \
  mysql:8.0

Connect:

docker exec -ti mysql mysql -u root -p -e 'SHOW DATABASES;'

Considerations

Physical vs Logical Backups

• Logical backups (mysqldump): Machine-independent, portable across MySQL versions and architectures
• Physical backups: Faster backup/restore, more compact, but require identical hardware and MySQL version

InnoDB Consistency

Ensure backups include all InnoDB files:

• ibdata*
• ib_logfile* (or #ib_redo* in MySQL 8.0.30+)
• Individual .ibd files

InnoDB performs automatic crash recovery on startup if backup was consistent.

MEMORY Tables

MEMORY tables are not stored on disk and will be empty after physical backup restoration. Use mysqldump for MEMORY tables.

References

• MySQL Backup and Recovery
• MySQL Backup Methods
• MySQL FLUSH Statement
• MySQL InnoDB Storage Engine