Sysadmin Backup files and database script

My backup scripts are specifically custom written for each individual paying private clients I consult for server optimisation work etc so I can't really share mine.

But you can check out some other folks scripts if you google. For example, Vivek's mysql backup script at http://bash.cyberciti.biz/backup/backup-mysql-database-server-2/ was what I based my early mysqlmybackup.sh script on which combines, mysqldump, mydumper, percona xtrabackup and rsync for all in one backup tool for mysql data and vB forum site files themselves using multi-threaded pigz and lbzip2 compression methods http://vbtechsupport.com/974/.

Script got abandoned as I was trying to do too much all at once and got too messy to handle at the time due to still being new to bash shell scripting. I ended up breaking my mysqlmybackup.sh script down into it's elements focusing on specific backup tools for specific paying private clients' backup needs.

This evolved into scripts like my mandbbackup.sh (below) which email notify on successful backup with the following sample backup email notification. Script has full error detection at each backup process stage. If any errors are detected, auto retention deletion routine aborts before needlessly deleting old backups

Then I combine custom written backup scripts with rsnapshot rsync tool http://www.rsnapshot.org for pull based remote server backups. So the flow would go

1. use custom written backup script to locally backup files and mysql databases (data, schema only and mysql grant backups)
2. on successful local backup the backup script sends email (and now pushover.net) notifications to me outlining the backup details (for email) or summary for pushover.net
3. setup a remote backup server with rsnapshot setup to automatically pull login and pull backups from local server to remote backup server. I use a few backup providers including Backupsy http://centminmod.com/backupsy - you can get a VPS with 250GB storage for $10/month ! Rsnapshot does backups every 4hrs, daily, weekly, and monthly.

Code:

/root/tools/mandbbackup_xf.sh backup dbname

Successfully backed up database: dbname
Successfully backed up web root directory for dbname
Successfully backed up database schema: dbname
pigz -4R -p4 /home/nginx/domains/domain.com/backup_dir/dbname_140614-195610.sql
pigz -4R -p4 /home/nginx/domains/domain.com/backup_dir/dbname_schema_140614-195610.sql
Number of backup files in /home/nginx/domains/domain.com/backup_dir before backup or deletion: 44
Number of backup files in /home/nginx/domains/domain.com/backup_dir after backup or deletion: 47

----------------------------------------------------------------------------
/root/tools/mandbbackup_xf.sh 0.1.0
----------------------------------------------------------------------------
/home/nginx/domains/domain.com/backup_dir resides on partition which has
62553 Megabytes of disk space available
----------------------------------------------------------------------------
MySQL data directory located at: /var/lib/mysql/
MySQL data directory size: 775 Megabytes
----------------------------------------------------------------------------
[PASS] you have enough free disk space to store MySQL directory size on:
/home/nginx/domains/domain.com/backup_dir
----------------------------------------------------------------------------

----------------------------------------------------------------------------
Backup dbname Data: real: 1.09s cpu: 60% maxmem: 14544 KB cswaits: 4795
----------------------------------------------------------------------------
Backup dbname associated web root directory: real: 5.73s cpu: 99% maxmem: 49008 KB cswaits: 4
----------------------------------------------------------------------------
Backup dbname Schema: real: 0.50s cpu: 17% maxmem: 12960 KB cswaits: 4375
----------------------------------------------------------------------------
Pigz dbname database: real: 0.26s cpu: 352% maxmem: 14688 KB cswaits: 415
----------------------------------------------------------------------------
Backup MySQL user GRANT permissions for:
#mysqlusername root @ 127.0.0.1

*************************************************************************
created MySQL database backups at: /home/nginx/domains/domain.com/backup_dir
*************************************************************************
Jun 14  19:56   6.5M   dbname_140614-195610.sql.gz
Jun 14  19:56   19K    dbname_schema_140614-195610.sql.gz
Jun 14  20:03   6.5M   dbname_140614-200334.sql.gz
Jun 14  20:03   19K    dbname_schema_140614-200334.sql.gz

*************************************************************************
created web root directory backups at: /home/nginx/domains/domain.com/backup_dir
*************************************************************************
Jun 14  19:56   48M    backup_public_140614-195610.zip
Jun 14  20:03   48M    backup_public_140614-200334.zip

*************************************************************************
created MySQL user GRANT backups at: /home/nginx/domains/domain.com/backup_dir
*************************************************************************
Jun 14  19:56   413    backup_mysqlgrants_140614-195610.sql.gz
Jun 14  20:03   413    backup_mysqlgrants_140614-200334.sql.gz
------------------------------------------------------------------------
Last backup sql / file(s) created at:
/home/nginx/domains/domain.com/backup_dir/backup_public_140614-200334.zip
/home/nginx/domains/domain.com/backup_dir/dbname_schema_140614-200334.sql.gz
/home/nginx/domains/domain.com/backup_dir/backup_mysqlgrants_140614-200334.sql.gz
------------------------------------------------------------------------
Backup Log:
/home/nginx/domains/domain.com/backup_dirlogs/dbname_backup_140614-200334.log
------------------------------------------------------------------------

and now includes pushover.net notifications to mobile devices thanks to @Matt heads up at https://community.centminmod.com/threads/pushover.142/



Also with this forum's Linode VPS http://centminmod.com/linode, you can signup for their automatic backup process at 1/4 of the price of the VPS so US$40/month VPS adds US$10/month for backups.

 

moved the off topic posts to https://community.centminmod.com/threads/backup-files-and-database-script-off-topic.498/ so folks can continue discussion and sharing of their backup strategies and policies or scripts they came across etc.

@pamamolf Vivek's mysql backup script at http://bash.cyberciti.biz/backup/backup-mysql-database-server-2/ is a good start for mysql backups.

 

Give it time, some members may. Have you tried the script I recommended http://bash.cyberciti.biz/backup/backup-mysql-database-server-2/ ? It's what I used before I started writing my own scripts.

Also I was reminded recently by someone that Backupsy VPS back up servers http://centminmod.com/backupsy also have an addon service for US$25 fee they can setup your Backupsy VPS server for backing up your servers. Not sure what the addon service entails or what it covers and whether you have to already have your own scripts to backup databases but might be one option to look into. If it doesn't cover database backups, you can use the script i linked to by Vivek to backup databases locally, and then have Backupsy VPS http://centminmod.com/backupsy setup to grab the backups from your server for files + databases.

 

See Centmin Mod's included multi-threaded compression tools such as pigz, bzip2 and lbzip2 https://community.centminmod.com/th...sion-tools-pigz-pbzip2-lbzip2-plzip-p7zip.92/

 

Just change line 10 in Matt's script from bzip2 to pigz. I use pigz at level 4 (-4) for best speed with decent compression ratio. If you have alot of cpu power, could go with pbzip2 or lbzip2 for better compression ratios at expense of longer compression times.

 

yeah looks okay to me.. of course always do tests before live production usage

 

Might want to ask in Matt's resource thread at https://community.centminmod.com/threads/auto-database-backup-script.561/