FreeBSD has had snapshot capability since around 2001 allowing administrators to take a frozen
image of a filesystem at a given instant in time with minimal impact on the server / filesystem. So how does this help with MySQL Backups?
If like me you have loads of space on your database volume and often find yourself making frequent risky modifications to your databases, a snapshot can save you loads of time and headaches by creating a point in time marker that you can fallback on if needed.
Snapshots provide the following benefits:
Efficient – Only as blocks in the active filesystem are modified and written to new locations on disk does the snapshot begin to consume extra space.
Fast – It takes about 30 seconds to create a snapshot of an 8Gb filesystem. Of that time 25 seconds is spent in preparation; filesystem activity is only suspended for the final 5 seconds of that period.
Reliable – FreeBSD ensures that no disk write activity can take place during the setup of the snapshot (typically less than a second). The result is a consistent replica.
If you are running MySQL on FreeBSD, snapshots can certainly help improve your backup strategy. So how do you get started?
Well if you head over to the Wiki you’ll find a detailed guide to creating your first snapshot, but before you click that link there is another new FreeBSD feature that you may not be aware of – Security Event Auditing.
Protecting your data requires more than just regular backups and the security auditing feature in FreeBSD 6.2+ can help assist with the logging of activity on your MySQL server.
Based on Sun’s published Basic Security Module (BSM), the de facto industry standard for auditing, FreeBSD Security Event Auditing provides reliable, fine-grained, and configurable logging of a variety of security-relevant system events, including logins, configuration changes, and file and network access. These log records can be invaluable for live system monitoring, intrusion detection, and postmortem analysis.
At the moment the stable release of FreeBSD will require a Kernel compile to enable audit capability, but future releases should have audit enabled in the generic kernel. You never know by the time FreeBSD 7.0 is available I may have figured out a way to audit MySQL logins using auditd.
If your feeling brave you can download the latest monthly snapshot of the upcoming FreeBSD 7.0 release and put auditing to the test.
Right – I’m off to get a working NetUnit jar ready for the end of the week – I hope
Posted in: BinaryStor | FreeBSD | MySQL | OpenSource | Security
6 Responses to “MySQL Snapshots on FreeBSD” - join in - Click Here
Paul M - October 10th, 2007 at 1:33 am
1
Thanks for the link.
If I find the time and there is FreeBSD available for EC2 I will give this a whirl.
Alan Snelson - October 10th, 2007 at 7:57 am
2
Looks like you may need to cast your vote: http://developer.amazonwebservices.com/connect/thread.jspa?messageID=48392봈
Pythian Group Blog » Blog Archive » Log Buffer #66: a Carnival of the Vanities for DBAs - October 12th, 2007 at 5:36 pm
3
[...] blog has an item on MySQL Snapshots on FreeBSD: backing up your MySQL stuff using the FreeBSD OS’s system snapshot. The item also mentions [...]
Chen Shapira - October 12th, 2007 at 10:21 pm
4
Thanks for the information, I was looking for this solution for quite some time. Good thing Pythian linked here.
Small question:
The procedure includes flushing tables to get a consistent snapshot:
FLUSH TABLES WITH READ LOCK;
However, IIRC, tables kept on innodb storage will ignore the FLUSH command, so the snapshot may still be inconsistent.
Did I get it wrong, or will snapshots really give inconsistent results for INNODB?
Alan Snelson - October 13th, 2007 at 8:26 am
5
Hi Chen,
As far as I am aware an InnoDB snapshot can be taken without locking the tables. On restore InnoDB will recover as if there was a system crash, rolling the data forward from the binary logs. Although any uncommitted transactions that were present at the time of the snapshot would be rolled back.
Be aware however this can potentially take some time…
Future releases of MySQL may take innodb filesystem snapshots into consideration to prevent the recovery step.
Keep watching http://forge.mysql.com/wiki/OnlineBackup as the API develops.
Rory Arms - April 4th, 2009 at 9:20 pm
6
A few years ago, I wrote a tool to be used from the system crontab(5), written in bourne shell, which only depends on mysql-client & mysql-server packges. It’s purpose is to easily generate periodic snapshots of /var (default location for mysql data) which are mounted. This mount point would then be available to an asynchronous backup process (such as rsync, tar, etc) which would perform the backup on that mounted snapshot. Perhaps someone reading, might find it useful: http://www.truestep.com/free_code/BSD/mysql_ufs_snapshot/
No comments:
Post a Comment