MySQL Backup & Recovery

Databases

MySQL

Physical Backups

Cold Backup

Hot Backup

Logical Backups

Logical Backup Performance

VM Level Snapshots

Bibliography & References

MySQL & Avamar

MySQL Import Tablespace

Move file per table .ibd files between instances

MySQL PITR

Physical Backups

Cold Backup

Perform a slow shutdown of the MySQL server and make sure that it stops without errors.
Copy all InnoDB data files (ibdata files and .ibd files) into a safe place.
Copy all InnoDB redo log files (#ib_redoN files in MySQL 8.0.30 and higher or ib_logfile files in earlier releases) to a safe place.
Copy your my.cnf configuration file or files to a safe place.

https://dev.mysql.com/doc/mysql-backup-excerpt/8.0/en/innodb-backup.html

Hot Backup

MySQL Enterprise Backup

Enterprise Edition only. Provides physical hot backup

Percona XtraBackup

Free, non-blocking, tightly compressed, highly secure hot backups for Percona Server for MySQL, MySQL and MariaDB.

mariabackup

MySQL-Matrix

Logical Backups

mysqldump

Available in all editions and versions. Provides logical backup.

mysqlpump

Introduced in MySQL 5.7. Provides parallel logical backup

mydumper

Opensource alternative for logical backups

MySQL Shell Dump & Load

Introduced in MySQL Shell 8.0.21. Provides big performance gains for logical backups

Logical Backup Performance

VM Level Snapshots

MySQL on Linux does not integrate with VM level snapshots. Recovery from a VM level snapshot should work but will rely on the MySQL crash recovery mechanism. There is a small but real risk that recovery could fail (1). You should not rely on VM level snapshots for running databases.

Options...

Shutdown the database before taking the snapshot.
Take a traditional backup before taking the snapshot. If recovery fails using the snapshot you would need to recover from the backup files.
Use clustering (e.g. InnoDB cluster) to protect the database. This also means you could use option 1 on one of the replicas to avoid an outage to the primary database.

vSphere Data Protection (VDP)

Bibliography & References

http://sarahdba.blogspot.com/2009/08/best-mysql-backup-strategy.html https://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/ https://severalnines.com/database-blog/comparing-percona-xtrabackup-mysql-enterprise-backup-part-one (also talks about their Cluster Control product)https://blog.yannickjaquier.com/mysql/mysql-backup-solutions-comparison.htmlhttps://dev.mysql.com/doc/mysql-backup-excerpt/8.0/en/innodb-backup.htmlhttps://www.red-gate.com/simple-talk/blogs/backing-up-mysql-part-5-big-data/https://www.red-gate.com/simple-talk/blogs/backing-up-mysql-part-6-physical-vs-logical-backups/https://www.red-gate.com/simple-talk/blogs/backing-up-mysql-part-8-zmanda-backups-for-mysql-other-backup-tools/
http://www.lenzg.net/mylvmbackup/
Cloninghttps://stackoverflow.com/questions/2972453/how-to-copy-a-database-from-one-server-to-another-server-in-phphttps://digitalfortress.tech/sql/duplicate-a-mysql-database-tutorial/https://gist.github.com/christopher-hopper/8431737https://gist.github.com/csonuryilmaz/3f8f92fdad007f97986e61ad79aeb514
VMWare(1) https://blogs.vmware.com/vsphere/2014/12/mysql-backup-with-vdp.html