PointyHair -- Engineering, Technology : Free hot backups for MySQL on Linux

Home

Stats

Wiki

Blogs

File Galleries

Last blog posts

1)	VoIP, telephony: Doorbell 2.0 Fri 11 of Dec, 2009 [04:11 UTC]
2)	VoIP, telephony: Unlimited incoming trunks... Fri 11 of Dec, 2009 [03:50 UTC]
3)	VoIP, telephony: Howto: recognizing one of many phone numbers and routing Fri 11 of Dec, 2009 [01:40 UTC]
4)	Network Security: denyhosts -- almost best thing since sliced bread Thu 19 of Nov, 2009 [08:25 UTC]
5)	Networking: Networking Asterisk with multiple uplinks Thu 19 of Nov, 2009 [08:19 UTC]
6)	Programming: Oracle 10g Express Edition vs. 64 bit client drivers Thu 19 of Nov, 2009 [07:45 UTC]
7)	Programming: C++ programming with Ubuntu 9.10 and Boost 1.40 Thu 19 of Nov, 2009 [07:35 UTC]
8)	Network Security: Using Pion for network security Mon 20 of Jul, 2009 [09:07 UTC]
9)	Network Security: Russian (caravan.ru) link spammers Mon 03 of Sep, 2007 [17:11 UTC]
10)	Network Security: SPAM on the rise... Sun 15 of Oct, 2006 [17:00 UTC]

Last changes

1)	HomePage
2)	VoIP Success Story
3)	Programming Language
4)	Floyd
5)	RoadWarrior
6)	lookupd
7)	Projects
8)	Software Engineering
9)	Etheric
10)	Code Reviews

Viewing blog post - My-Ess-Que-Ell

Return to blog

Free hot backups for MySQL on Linux

posted by TaneliOtala on Wed 03 of May, 2006 [23:41 UTC]

"Hot Backups" of your MySQL Database on Linux

Would you like to take consistent snapshot backups of your entire database without a SAN or external software, and without slowing or locking your database for the duration?

RedHat? Enterprise Linux (and Centos Linux) install an LVM by default.
LVM, Logical Volume Manager is a layer that sits on top of any file system (default ext3).

You can tell if you're already running on an LVM, by checking your file system with "df -h" — an entry like "/dev/mapper/VolGroup01-LogVol00" shows that your disk is mapped through the LVM.

LVM brings you features such as the ability to grow and shrink volumes, add hard disks without migrating data, RAID0, and for the purpose of this article: file system SNAPSHOTS.

FLUSH TABLES WITH READ LOCK;
\! lvcreate --size 100m --snapshot --name snap /dev/VolGroup01/LogVol00
UNLOCK TABLES;

Will lock and flush all tables in the current database to disk.
As soon as lock and flush is acquired, a snapshot called "snap" is created (instantaneously), and table locks are released.

You can use lvdisplay(8) to look at active volumes.
Remember, that you must have dm-snapshot.ko module loaded (modprobe dm-snapshot)

Next mount the new snapshot volume, backup it up, and release the snapshot

mkdir /snap
mount /dev/VolGroup01/snap /snap
# This is where you back up whatever you need from /snap, I prefer using rsync(1)
umount /snap
lvremove /dev/VolGroup01/snap
rmdir /snap

While you back up the snapshot, your database is running its normal business, slowing down only as much as you read from the file system, i.e. the disk access is the only slowing factor — meaning that you can back up at file system speed!

Snapshotting is also a handy undo feature, when doing schema changes (ALTER TABLE), or other database maintenance work, such as upgrades.

Permalink (referenced by: 0 posts references: 0 posts)

1 comment

Posted messages

Style

Sort

Threshold

Find

Hide all

author

message

is retroactively implementing snapshots possible?

on: Sat 28 of Oct, 2006 [21:05 UTC]

score: 0.00

even if the machine is already using lvm you may not be able to take advantage of snapshots. for more info, see my comment on mike kruckenberg's post about using lvm for backups
http://mike.kruckenberg.com/archives/2006/05/mysql_backups_u.html