Embedded Files
Move file per table .ibd files between instances
Physical Backups
Physical Backups
Cold Backup
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.
Hot Backup
Hot Backup
Enterprise Edition only. Provides physical hot backup
Free, non-blocking, tightly compressed, highly secure hot backups for Percona Server for MySQL, MySQL and MariaDB.
Logical Backups
Logical Backups
Available in all editions and versions. Provides logical backup.
Introduced in MySQL 5.7. Provides parallel logical backup
Opensource alternative for logical backups
Introduced in MySQL Shell 8.0.21. Provides big performance gains for logical backups
Logical Backup Performance
Logical Backup Performance
VM Level Snapshots
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.
Bibliography & References
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/https://www.percona.com/blog/mysql-backup-and-recovery-best-practices/
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
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
Page updated
Google Sites
Report abuse