Backups

• Taking backups using mysqldump
• Taking backups using mysqlpump
• Taking backups using mydumper
• Taking backups using flat files
• Taking backups using XtraBackup
• Locking instances for backup
• Binary log backup

Introduction

There are two main ways to perform a backup. One is logical backup, which exports all the databases, table structures, data, and stored routines into a set of SQL statements that can be executed again to recreate the state of the database. The other type is physical backup, which contains all the files on the system that the databases used to store all the database entities:

• Logical backup utilities: mysqldump, mysqlpump, and mydumper (not shipped with MySQL)
• Physical backup utilities: XtraBackup (not shipped with MySQL) and flat file backup

For point-in-time recovery, the backup should be able to provide the binary log positions up to which the backups are taken. This is called a consistent backup.

It is highly recommended to take backups from a slave onto a filer mounted on it.