Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Aaron Brown 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
add comment

1 Answer

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

share|improve this answer
    
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... –  RolandoMySQLDBA 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) –  RolandoMySQLDBA 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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