Automatized online backup of MySQL databases using LVM snapshots
Tue, 2008.07.08 - 11:04 — müzso
This is again a script that people could write easily if they understood some shell scripting, but it is quite well implemented with logging and all and maybe spares a couple of minutes/hours of your time. 
You should use it with crontab on a daily basis. For details take a look at the desc. of the MySQL backup script.
The main difference between this one and its predecessor is that this script creates a backup of an LVM volume (with everything on it ... eg. the complete MySQL data directory with all databases). Using LVM snapshots we can avoid to lock the database (tables) for minutes (while creating the backup), thus this is a suitable method for creating online backups (ie. save the database state while the system is running and is being used).
However, this is not equivalent to an SQL dump! The compressed form of the database directory cannot be moved between different architectures (eg. a 32 bit MySQL instance and a 64 bit version), while an SQL dump can be used even in this case.
The idea came from Dennis Guba's comment (the first on the page) on the backup page of the MySQL 5.0 documentation.
You should use it with crontab on a daily basis. For details take a look at the desc. of the MySQL backup script.
The main difference between this one and its predecessor is that this script creates a backup of an LVM volume (with everything on it ... eg. the complete MySQL data directory with all databases). Using LVM snapshots we can avoid to lock the database (tables) for minutes (while creating the backup), thus this is a suitable method for creating online backups (ie. save the database state while the system is running and is being used).
However, this is not equivalent to an SQL dump! The compressed form of the database directory cannot be moved between different architectures (eg. a 32 bit MySQL instance and a 64 bit version), while an SQL dump can be used even in this case.
The idea came from Dennis Guba's comment (the first on the page) on the backup page of the MySQL 5.0 documentation.
| Attachment | Size |
|---|---|
| mysql_lvm_backup-1.0.0.zsh | 4.02 KB |
| mysql_lvm_backup-1.0.1.zsh | 3.99 KB |
| mysql_lvm_backup-1.1.0.zsh | 3.99 KB |
| mysql_lvm_backup-2.0.0.sh | 5.9 KB |
All of my own contents here (if not noted otherwise) are licensed under the
Creative Commons Attribution-NonCommercial-ShareAlike 4.0 License. My posts reflect my personal opinion and shall not be associated with any of my employers.
Creative Commons Attribution-NonCommercial-ShareAlike 4.0 License. My posts reflect my personal opinion and shall not be associated with any of my employers.
Comments
Automatized online backup of MySQL databases using LVM snapshots
umasksetting was completely screwed up and backup files created by the script were world-writable. Of course the problem only affected those who had bad permissions on the backup directory too.MySQL LVM backup v1.1.0
find ... -mtime +${days}returned${days}+2 items. Replaced it with anls -1t+tail -n +$((days+1))combo.MySQL LVM backup v2.0.0