mysqlmybackup.sh – multi feature MySQL database backup and restore script with Percona XtraBackup & Mydumper tool support
mysqlmybackup.sh started out as a mysqldump based MySQL back up script which I started on June 20th, 2011. I compiled the script based on the modified MySQL mysqldump backup script by Vivek Gite and merged it with Zarafa.com script.
Script Evolution:
The script has since evolved from the original Vivek Gite based script to also include additional features like separate MySQL database schema only backups, retention and automatic deletion of rotating MySQL back ups at prefined threshold settings, rotating rsync back ups of vBulletin web files as well – attachments, avatars etc.
I also added Percona XtraBackup support for those folks who are using a mixture of MyISAM and InnoDB tables or if your MySQL database is predominantly InnoDB storage engine based tables. The latest feature added is support for mydumper – a very fast MySQL backup and restore tool which is multi-threaded allowing mydumper to be much faster than traditional mysqldump backups. I did some quick tests and mydumper was nearly 3x times faster than mysqldump in backing up 550MB of database data on virtualbox virtualised Xeon W3550 with 2 cpu cores and SATA disks. More details below.
I’m still testing and adding to mysqlmybackup.sh script so it isn’t ready for release anytime soon. My intention for mysqlmybackup.sh is to be an all in one tool for my MySQL database backups as well as for my vBulletin forum file backups. The script can easily be expanded to backup multiple vBulletin and/or wordpress site files as well. Something planned for much later on.
Change log:
- v0.9.8: Nov 27th, 2011: Added more parallel (multi-threaded) compression options: lbzip2 and plzip
- v0.9.7: Aug 9th, 2011: Added parallel compression options for gzip and bzip2 via pigz and pbzip2
- v0.9.6: July 30th, 2011: Add retention and deletion check function for mydumper & XtraBackup backups. Revised all backup deletion routines to only run on successful backup of databases. If backup fails during mysqldump, mydumper or percona xtrabackup backups, the deletion routine will not run
- v0.9.5: July 30th, 2011: Percona Xtrabackup mode added –parallel=2 option (single disk with single cpu core + virtual core (2 threads) is ~15% faster)
- v0.9.4: July 2nd, 2011: Change default compression back from bzip2 to gzip
- v0.9.3: July 2nd, 2011: Improve mysqldump function added option of gzip or bzip2 compression method defaults to bzip2
- v0.9.2: June 26th, 2011: Fix mydumper and PV progress monitor links. Disabled PV progress monitor for cronjob.
- v0.9.1: June 26th, 2011: Minor fixes to XtraBackup directory listings
- v0.9: June 26th, 2011: Added clean up function, fixed disk check function, improved mydumper & XtraBackup function
- v0.8: June 25th, 2011: Added email notifications for Percona XtraBackup and mydumper.
- v0.7: Added progress bar monitor installation function to mysqldump function. Example:
Backing up database: [sbtest] if mysql user password is empty hit enter at prompt 134MB 0:00:08 [16.5MB/s] [ <=> ] Successfully backed up database: sbtest
- v0.6: Added Percona XtraBackup and Mydumper multi-threaded backup/restore support
- v0.5: First announced release overview
Screenshots:
Overview:
For now this blog post is for my own documentation purposes. Below I’ll outline the command line options available as well as post some example output results from these command line options.
Options available:
- ./mysqlmybackup.sh silent (mysql database/schema backup)
- ./mysqlmybackup.sh backup (mysql database/schema backup)
- ./mysqlmybackup.sh backupvb3dir (vb3 attachments etc backup)
- ./mysqlmybackup.sh backupvb4dir (vb4 attachments etc backup)
- ./mysqlmybackup.sh backupvb3dirtest (dry test run vb3 attachments etc backup)
- ./mysqlmybackup.sh backupvb4dirtest (dry test run vb4 attachments etc backup)
- ./mysqlmybackup.sh xtrabackup (Mixed InnoDB/MyISAM backups with Percona XtraBackup)
- ./mysqlmybackup.sh mydumper (multi-threaded mysql backup/restore)
