Webinar: MySQL Backups

Last Updated on

MySQL Backups by Federico Razzoli - cover slide
First slide: “MySQL Backups, Federico Razzoli

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.

Webinar Recording

Topics index:

Slides

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?

See also

See future and past Webinars.

Articles

Courses

Comments (2)

  1. Good overview Federico.
    I would also add 2 points:
    – there are full and incremental backups but also differential that are very popular in production. This means you take a full backup on Sundays for example and then a backup every other days but with the changes always from the full backup of Sunday. So if you need to restore the backup of Wednesday, you only need to restore the full backup and the differential of Wednesday. Those backups are of course usually larger than incremental but faster to restore. MEB supports that.
    – In MySQL 8.0 it’s possible to snapshot when using only InnoDB directly from MySQL using Clone.

    Cheers,

    1. Thanks Le Fred, these are useful additions.
      I didn’t mention differential backups because, once you understand incremental backups, any combination of full + incremental is possible. You can also make them with Xtrabackup, even if there is no explicit support.
      The Clone plugin would have been good to mention. But I’ll cover it in a separate article or webinar, stay tuned 🙂
      Cheers,
      Federico

Leave a Reply

Your email address will not be published. Required fields are marked *