Posts Tagged ‘storage server’

Make daily Linux MySQL database backups with shell script

Thursday, May 23rd, 2013

Creating database backup with MySQL with mysqlbackupper and mysqlback shell scripts easy create mysql backups

Some time ago, I've written a tiny shell script which does dumps of Complete (SQL Script) MySQL databases. There are plenty of ways to backup MySQL database and plenty of scripts on the net but I like doing it my own way. I have few backup scripts. I prefer script database over keeping binary logs, or using some un-traditional backup methods like backing all binary data in /var/lib/mysql.

One was intended to backup with mysqldump whole database and later upload to a central server running tsh (shell). Using tsh maybe not the best method to upload, but the script can easily be modified to use ssh passwordless authentication as a method to upload.

I'm not a pro shell scripter, but MySQLBackupper script can be used as useful for learning some simple bash  shell scripting.

To use the script as intended you will have to build tsh from source. Tsh is in very early development stage (ver 0.2) but as far as I tested it before some years it does great what it is intended for. You can script from here.
Earlier, I used to upload all SQL dumps to /backups directory on central backup storage server, thus I had written secondary script to classify uploaded backups based on backup archive name. Script used is and can be viewed here. Though this way of making backups, needs a bit of custom work for managing backups up to 10 / 20 servers it worked well.

I have written also another mysqlbackup script which is much more simplistic and only dumps with mysqldump and stores copies on hard disk in tar.gz archive. You can download my other simple here.

Only inconvenient thing about above scripts is they dump all SQL databases. Hence whether necessary to get content for single database from (complete) All database SQL (script backup), I use SED (stream editor) one liner script.

It is interesting to hear how others prepare their MySQL db backups.