Last Updated on
It is important to understand which types of backups are available for MySQL and MariaDB databases, and to choose the best ones to use for a given use case. The webinar also gave hints to plan proper backup strategies, and to test them.
- Planning Backup Strategies
- Backup Types
- Backups and Replication
- Logical Backups: mysqldump, mysqlpump, myloader
- Physical Backups
- InnoDB Transportable Tablespaces
- Xtrabackup, Mariabackup
- Binary Log
- Testing Backups
- Thank You
Notes and Follow Up
- UPDATE: Xtradb 8.0.12 was released the same day of the webinar. Note that it won’t work with MySQL 8.0.19 and earlier.
- Question: mydumper is available by default in 8.0?
- No, it is not bundled with MySQL or any fork. This community project can be found on GitHub.
- Question: xtrabackup does not support mysql 8.0.20 due to redo log changes?
- According to Percona: redo log, undo log, data dictionary.
- Question: is xtrabacp free of cost to use with mysql ? or involves licencing/cost?
- It is free and open source.
- Question: what is difference between mysql enterprise backup and xtrabackup?
- The former is a commercial proprietary tool by Oracle, the latter is a free tool by Percona. Until now, I never seen a technical reason to even consider MySQL Enterprise Backup. There is a feature comparison by Percona.
- Question: Is –prepare is like a preview of restore ? but it doesnot actually restore ?
- No. It is a necessary step that we must follow before restoring the backup.
- Question: what is default mode of binlog format ROW or STATEMENT?
- In Mysql 5.6 and MariaDB, it is MIXED. In MySQL 5.7 and newer, fortunately it is ROW.
- Question: any opensource backup tool to take incremental backups?
- As mentioned, Xtrabackup does. I forgot to mention that dumps can be incremental with the –where option. But such option applies to a single table, and the incremental logic is up to the user.
- Question: is it possible to take incremental backup in mysql community version?
- Yes. All of the mentioned techniques an tools are usable with MySQL community version and with MariaDB.
- Question: what is the best way to backup and restore 1 TB size of database backup?
- Definitely a snapshot (lvm or virtual machine level). But, as mentioned, it’s better to have multiple backup techniques. Maybe consider a cold backup with rsync on a slave.
- Question: How to speed up restoration backup of 200GB taken by logical backup(mysqldump)?
- Use mysqlpump instead, and restore the files in parallel.
- Question: We are taking RDS automatic backup enabled but any disaster happening, How to retrieve or move the snapshot another region without adding any cost? Any plan or idea?
- There is no way to move data between different regions without an additional cost. Check Cross-Region Snapshot Copy for Amazon RDS.