Physical backups

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.

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

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

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

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.

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 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

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;'

• 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

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 are not stored on disk and will be empty after physical backup restoration. Use mysqldump for MEMORY tables.

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