March 23, 2007

How to create MySQL snapshot

Because people always ask me how to backup their database, I decided to write this minimalistic howto. It will do correct snapshot even if you have MyISAM or InnoDB tables, version of MySQL doesn't matter (3.23, 4.0, 4.1, 5.0). Howto has only 4 steps, so read on..

I assume your MySQL data dir is /var/lib/mysql and you have enough space in /tmp. Open two terminals, in the first one connect to MySQL and do these steps:

1. (optional) read third section of page
2. terminal one: FLUSH TABLES WITH READ LOCK; #do NOT exit mysql prompt
3. terminal two: tar -cvzf /tmp/mysql-snapshot.tar.gz /var/lib/mysql
4. terminal one: UNLOCK TABLES;

Now you have correct snapshot (/tmp/mysql-snapshot.tar.gz) of your MySQL DB.

No comments:

Post a Comment