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;


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

  • Snapshots usually imply file system snapshots. Usually, this is done either with LVM or SAN snapshots. Another option is EBS snapshots in EC2. If you tell us more about your environment, maybe we can help better. Jul 16 '12 at 0:33
  • @AaronBrown thanks a lot for your comment, I did this question just to learn and not because I've a issue. My environment is Mysql 5.2, localhost, MAC, Thanks
    – jcho360
    Jul 16 '12 at 12:25

I have written posts of this nature before

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

  • thanks, but in the first link is the only one that I found usefull, the other 2 not even have the word snapshot. What I got from those links is that I need a third party like TopNotch or MySQL Module Option (both payed) to be able to make snapshots??
    – jcho360
    May 16 '12 at 19:40
  • I updated my answer... May 16 '12 at 19:51
  • that's a lot better, does the Snapshots-back up can be made only in linux? the software that you said (Percona) + a couple of examples that I found, they all are based on linux platform
    – jcho360
    May 16 '12 at 20:50
  • Yes, Percona XtraBackup is Linux-based (Red Hat, CentOS, Debian, and Ubuntu systems : percona.com/software/percona-xtrabackup/downloads) May 16 '12 at 20:58
  • Did you see the new online method of Mysql 6 where you can make snapshots? is that better than this one I posted?
    – jcho360
    May 17 '12 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.