How to Create Snapshot Backups in MySQL

Question

I've seen a lot of people talking about snapshots backup, even I'm reading a book about mysql but nobody explain the code how to make a snapshots backup. This is what the book says:

1. flush tables with read lock

2. take a snapshot

3. unlock tables;

THAT's SO NULL!....

Can someone give me MORE information about the snapshots in MySQL?

Answer 1

I have written posts of this nature before

• How can I optimize a mysqldump of a large database?
• recover a single mysql database on a busy master-slave replicated system
• Set up a MySQL slave using mysqldump to get the initial data

You will find code samples and additional advice on its usage

I shy away from Snapshots as done by LVM if

• 90%+ Data is InnoDB
• innodb_file_per_table is disabled
• All Data Exceeds 200GB
• Heavy Write, Heavy Read

I wrote about this before : Ibdata usage and Recommendations?

While my personal preference is mysqldumps, if you have a small data volume, LVM snapshots would be just fine. If you are running MySQL 5.0/5.1, I would recommend running

SET GLOBAL innodb_max_dirty_pages_pct = 0;

to flush up to 99% of the InnoDB Buffer Pool before performing a file-system snapshot. Then, set it back to

SET GLOBAL innodb_max_dirty_pages_pct = 90;

This is not required for MySQL 5.5

An in-between solution would be Percona's XtraBackup. Percona XtraBackup is free, open-source, and GPL