What is the optimal backup method for MySQL databases?

Goal of a MySQL system administrator is to have regular, consistent backups of the
databases with minimal impact on the database application. The backup process should
also limit the use of CPU, memory, network resources so that application is not impacted.
Optimal backup method must be choosen to keep the backup window as small as possible.
The backup parameters depends on various factors – number and size of databases, number
of MySQL servers, MySQL configuration, data security.

ZRM for MySQL provides consolidated backup and recovery for MySQL databases. ZRM
for MySQL provides extensive list of options to tune the backup process for the user
environment. It also tracks lots of backup parameters that are available to the administrator
to tune the backup process.

ZRM for MySQL wiki shows how to use the backup reports and adjust the backup
parameters to create optimal backup method. In the example – local, full backup of 6GB
InnoDB database, backing up raw database using LVM snapshot works the best. The backup
time, restoration time, time to verify backup and backup size were considered as the criteria
to determine the optimal backup method. Using LVM snapshots to backup MySQL databases
using InnoDB storage engine is a hot backup (does not require locks). The raw backup
method requires less restoration time compared to logical backup of the database.

It is important to measure the parameters that are important to the administrator in your
specific MySQL configuration. The example shows how to do it with ZRM for MySQL and
the optimal backup method for your configuration may vary.

Comments are closed.