Is It Safe To Backup MySQL Without Lock And Flush?

Question:

Is it safe to backup MySQL without Lock and Flush?

Solution:

It can be done, but we do not recommend it.

Lock and Flush is done by the MySQL Agent add-on.

If you backup MySQL without locking tables and flushing in memory open tables, then you are likely to get corruption if the database is being updated or changed when you backup.

MySQL is very good at recovering from unclean shutdowns. On startup it will detect that its an unclean shutdown when you recover from an unclean backup and automatically repair your corrupt MyISAM tables.

Without the MySQL Add-on, you will loose whatever changes were being made to the database at the time of the backup.

Note that the R1Soft MySQL backup process is different from a normal hot backup.

MySQL Hot Backup Process (mysqlhotcopy)

1. Lock all tables.
2. Flush all table changes to disk.
3. Copy all raw database files (could take a long time).
4. Unlock tables.

R1Soft MySQL Hot Backup Process

1. Lock all tables.
2. Flush all table changes to disk.
3. Snapshot the raw partition using the r1soft near-CDP device driver /dev/backupdriver.
   Note: This takes less than a second.
4. Unlock tables.
5. If seed copy: All raw MySQL files from point-in-time snapshot of volume/partition.
   If not a seed copy: Only changed blocks from point-in-time snapshot of volume/partition.

Note this step can take a long time if you are doing a seed or have a lot of changes to your database like importing a large amount of data. This is fine since we are not holding table locks anymore. Also, you get the benefits of a totally consistent copy of MySQL raw table files by reading from the block level point-in-time copy-on-write snapshot that is maintained by /dev/backupdriver.

This is significant because your application and queries can continue while the backup is taking place (step 5).

With typical hot backup queries, they will timeout, web pages will fail to load, and systems will be down for the duration of the backup operation.

If you are backing up MySQL in a hotbackup fashion and are not locking and flushing tables, you could experience serious data loss.

Related Articles