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:

mysqlmybackup.sh mydumper backup

 

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)

Pages: 1 2 3 4 5 6 7 8 9


Previous:
Next:



MaxCDN Site Acceleration

Get Cost Effective Content Delivery
Unbeatable speed, stats, & price
http://www.maxcdn.com



8 Comments

  1. Deciding on which is better format for backup listing ?? Which would be your choice ?

    format #1

    *******************************************************
    MySQL database backups at: /mysqlbackup/mysqlsql
    *******************************************************
     
    Jun 25  23:16   483    db2.localhost.localdomain.25-06-2011-231658.gz
    Jun 25  23:16   153K   mysql.localhost.localdomain.25-06-2011-231658.gz
    Jun 25  23:17   134M   sbtest.localhost.localdomain.25-06-2011-231658.gz
    Jun 25  23:23   153K   mysql.localhost.localdomain.25-06-2011-232316.gz
    Jun 25  23:23   482    db2.localhost.localdomain.25-06-2011-232316.gz
    Jun 25  23:23   134M   sbtest.localhost.localdomain.25-06-2011-232316.gz
    Jun 25  23:31   153K   mysql.localhost.localdomain.25-06-2011-233124.gz
    Jun 25  23:31   483    db2.localhost.localdomain.25-06-2011-233124.gz
    Jun 25  23:31   134M   sbtest.localhost.localdomain.25-06-2011-233124.gz
     
    *******************************************************
    MySQL database schema only backups at: /mysqlbackup/mysqlschema
    *******************************************************
     
    Jun 25  23:16   1.3K   db2.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    Jun 25  23:16   23K    mysql.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    Jun 25  23:17   1.8K   sbtest.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    Jun 25  23:23   23K    mysql.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    Jun 25  23:23   1.3K   db2.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    Jun 25  23:23   1.8K   sbtest.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    Jun 25  23:31   23K    mysql.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    Jun 25  23:31   1.3K   db2.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    Jun 25  23:31   1.8K   sbtest.localhost.localdomain.25-06-2011-233124-schemanodata.sql

    format #2

    /mysqlbackup/
    |-- [     4096 Jun 25 23:31]  /mysqlbackup//mysqlschema
    |   |-- [     1843 Jun 25 23:31]  /mysqlbackup//mysqlschema/sbtest.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    |   |-- [     1281 Jun 25 23:31]  /mysqlbackup//mysqlschema/db2.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    |   |-- [    23069 Jun 25 23:31]  /mysqlbackup//mysqlschema/mysql.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    |   |-- [     1843 Jun 25 23:23]  /mysqlbackup//mysqlschema/sbtest.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    |   |-- [     1281 Jun 25 23:23]  /mysqlbackup//mysqlschema/db2.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    |   |-- [    23069 Jun 25 23:23]  /mysqlbackup//mysqlschema/mysql.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    |   |-- [     1843 Jun 25 23:17]  /mysqlbackup//mysqlschema/sbtest.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    |   |-- [    23069 Jun 25 23:16]  /mysqlbackup//mysqlschema/mysql.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    |   `-- [     1281 Jun 25 23:16]  /mysqlbackup//mysqlschema/db2.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    `-- [     4096 Jun 25 23:31]  /mysqlbackup//mysqlsql
        |-- [140086700 Jun 25 23:31]  /mysqlbackup//mysqlsql/sbtest.localhost.localdomain.25-06-2011-233124.gz
        |-- [      483 Jun 25 23:31]  /mysqlbackup//mysqlsql/db2.localhost.localdomain.25-06-2011-233124.gz
        |-- [   156245 Jun 25 23:31]  /mysqlbackup//mysqlsql/mysql.localhost.localdomain.25-06-2011-233124.gz
        |-- [140086699 Jun 25 23:23]  /mysqlbackup//mysqlsql/sbtest.localhost.localdomain.25-06-2011-232316.gz
        |-- [      482 Jun 25 23:23]  /mysqlbackup//mysqlsql/db2.localhost.localdomain.25-06-2011-232316.gz
        |-- [   156243 Jun 25 23:23]  /mysqlbackup//mysqlsql/mysql.localhost.localdomain.25-06-2011-232316.gz
        |-- [140086702 Jun 25 23:17]  /mysqlbackup//mysqlsql/sbtest.localhost.localdomain.25-06-2011-231658.gz
        |-- [   156245 Jun 25 23:16]  /mysqlbackup//mysqlsql/mysql.localhost.localdomain.25-06-2011-231658.gz
        `-- [      483 Jun 25 23:16]  /mysqlbackup//mysqlsql/db2.localhost.localdomain.25-06-2011-231658.gz
     
    2 directories, 18 files

    format #3

    /mysqlbackup/
    |-- [     4096 Jun 25 23:31]  mysqlschema
    |   |-- [     1843 Jun 25 23:31]  sbtest.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    |   |-- [     1281 Jun 25 23:31]  db2.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    |   |-- [    23069 Jun 25 23:31]  mysql.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    |   |-- [     1843 Jun 25 23:23]  sbtest.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    |   |-- [     1281 Jun 25 23:23]  db2.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    |   |-- [    23069 Jun 25 23:23]  mysql.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    |   |-- [     1843 Jun 25 23:17]  sbtest.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    |   |-- [    23069 Jun 25 23:16]  mysql.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    |   `-- [     1281 Jun 25 23:16]  db2.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    `-- [     4096 Jun 25 23:31]  mysqlsql
        |-- [140086700 Jun 25 23:31]  sbtest.localhost.localdomain.25-06-2011-233124.gz
        |-- [      483 Jun 25 23:31]  db2.localhost.localdomain.25-06-2011-233124.gz
        |-- [   156245 Jun 25 23:31]  mysql.localhost.localdomain.25-06-2011-233124.gz
        |-- [140086699 Jun 25 23:23]  sbtest.localhost.localdomain.25-06-2011-232316.gz
        |-- [      482 Jun 25 23:23]  db2.localhost.localdomain.25-06-2011-232316.gz
        |-- [   156243 Jun 25 23:23]  mysql.localhost.localdomain.25-06-2011-232316.gz
        |-- [140086702 Jun 25 23:17]  sbtest.localhost.localdomain.25-06-2011-231658.gz
        |-- [   156245 Jun 25 23:16]  mysql.localhost.localdomain.25-06-2011-231658.gz
        `-- [      483 Jun 25 23:16]  db2.localhost.localdomain.25-06-2011-231658.gz
     
    2 directories, 18 files

    format #4

    /mysqlbackup/
    |-- [root     root          4096 Jun 25 23:31]  mysqlschema
    |   |-- [root     root          1843 Jun 25 23:31]  sbtest.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    |   |-- [root     root          1281 Jun 25 23:31]  db2.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    |   |-- [root     root         23069 Jun 25 23:31]  mysql.localhost.localdomain.25-06-2011-233124-schemanodata.sql
    |   |-- [root     root          1843 Jun 25 23:23]  sbtest.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    |   |-- [root     root          1281 Jun 25 23:23]  db2.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    |   |-- [root     root         23069 Jun 25 23:23]  mysql.localhost.localdomain.25-06-2011-232316-schemanodata.sql
    |   |-- [root     root          1843 Jun 25 23:17]  sbtest.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    |   |-- [root     root         23069 Jun 25 23:16]  mysql.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    |   `-- [root     root          1281 Jun 25 23:16]  db2.localhost.localdomain.25-06-2011-231658-schemanodata.sql
    `-- [root     root          4096 Jun 25 23:31]  mysqlsql
        |-- [root     root     140086700 Jun 25 23:31]  sbtest.localhost.localdomain.25-06-2011-233124.gz
        |-- [root     root           483 Jun 25 23:31]  db2.localhost.localdomain.25-06-2011-233124.gz
        |-- [root     root        156245 Jun 25 23:31]  mysql.localhost.localdomain.25-06-2011-233124.gz
        |-- [root     root     140086699 Jun 25 23:23]  sbtest.localhost.localdomain.25-06-2011-232316.gz
        |-- [root     root           482 Jun 25 23:23]  db2.localhost.localdomain.25-06-2011-232316.gz
        |-- [root     root        156243 Jun 25 23:23]  mysql.localhost.localdomain.25-06-2011-232316.gz
        |-- [root     root     140086702 Jun 25 23:17]  sbtest.localhost.localdomain.25-06-2011-231658.gz
        |-- [root     root        156245 Jun 25 23:16]  mysql.localhost.localdomain.25-06-2011-231658.gz
        `-- [root     root           483 Jun 25 23:16]  db2.localhost.localdomain.25-06-2011-231658.gz
     
    2 directories, 18 files
  2. I did not find where to download?
    Where to download mysqlmybackup.sh?

  3. Hi thanks for interest in mysqlmybackup.sh.

    The script is still very much work in progress and needs further testing, so not available for download yet. Bookmark this page or subscribe for comment update notification for when I do release it to the public 🙂

  4. Can I apply for testing? thank you

  5. Script code is focused on backup stage, haven’t gotten to writing the restore stage features yet. But i’ll keep you in mind for when it’s ready for testing. I removed your email from public viewing to prevent spam bots picking it up.

  6. Hi eva2000

    I want to ask from you when mysqlmybackup.sh.script will going to be publish (Available for download and from where). I am urgently in need of this script. I am using mydumper db backup. I want script that run as cron job.

    Thanks and Kind Regards
    MRD

  7. Hi MRD, it’s hard to say, only working on mysqlmybackup.sh in my free time and there’s still alot to write (i.e. the entire restore end of the scripts not yet done as only been focusing on backup end). But keep checking back to this page for updates or follow me on twitter https://twitter.com/vbtechsupport or facebook http://www.facebook.com/pages/vbtechsupportcom/162015497186290 for updates and announcements when mysqlmybackup.sh will be released.

  8. Still no chance for a release? Sounds like a great tool to use!

Trackbacks/Pingbacks

  1. MySQL Backup Bash Shell Script | vbtechsupport.com - [...] used quite a few different ones all using some form of mysqldump.  Update: script has evolved into mysqlmybackup.sh details…
  2. MariaDB 5.3.0 Beta MySQL & Percona Xtrabackup 1.6.2 Released | vbtechsupport.com - [...] to detect and work with MariaDB 5.3.0 beta so I updated my Innobackupex-1.5.1 patch mentioned here to work with…
  3. Tungsten Replicator: vBulletin Suite v4.1.6 configured for MariaDB MySQL Master/Slave Replication | vbtechsupport.com - [...] doesn’t replace a proper backup procedure of which I’ll continue working on my mysqlmybackup.sh script.myreplicator.shI’ve also been working on…
  4. gziptest.sh - parallel compression benchmark tests: gzip vs bzip2 vs pigz vs pbzip2 vs lzip vs plzip | vbtechsupport.com - [...] I implemented parallel multi-threaded versions of gzip and bzip2 – pigz and pbzip2 in my mysqlmybackup.sh script I am…
  5. gziptest.sh part 2: multi-threaded compression benchmarks | vbtechsupport.com - [...] 9.1%. Definitely will look at adding plzip and lbzip2 as additional compression options for my mysqlmybackup.sh script. Raw gziptest.sh result…
  6. It's official, Percona XtraBackup does not support MariaDB 5.2 & 5.3 MySQL yet | vbtechsupport.com - [...] script with MariaDB 5.2 or 5.3 installed, the script will fail. I’ve mentioned this before on page 3 of…

Leave a Comment