Auto Database Backup Script 1.0

Thanks Matt thanks for sharing and adding such a useful resource

For folks who do not have multi-threaded compression version of bzip2 called pbzip2, Centmin Mod has a menu option 17 to install a variety of multi-threaded compression tools outlined at https://community.centminmod.com/th...sion-tools-pigz-pbzip2-lbzip2-plzip-p7zip.92/. You can see the differences in compression times and compression ratios in a comparison benchmark article I wrote on my blog at http://vbtechsupport.com/1614/

Generally, you'd either use pigz for multi-threaded gzip and pbzip2 or lbzip2 for multi-threaded bzip2 compression. You can alter line 10 of Matt's script to change it from the default pbzip2.

 

Lines 7 and 8 of Matt's script show the usual commands you're use to so changing them accordingly

Code:

-u DATABASE_USERNAME -p$PASSWD

or adding new variables for each database to replace line 4

i.e.

Code:

DBUSER_A=mysqluser1
DBUSER_B=mysqluser2

DBPASS_A=mysqluser1password
DBPASS_B=mysqluser2password

then changing lines 7 and 8

Code:

-u $DBUSER_A -p$DBPASS_A
-u $DBUSER_B -p$DBPASS_B

Pretty much an extension of bash scripting links I posted at https://community.centminmod.com/threads/create-a-bash-script.552/. Particularly, the ultimate bash scripting guide https://community.centminmod.com/threads/create-a-bash-script.552/#post-2431. Look into assigning variables section http://www.tldp.org/LDP/Bash-Beginners-Guide/html/sect_03_02.html

 

pigz needs to be run after mysqldump

no you just run the actual backup script via cron

i.e. run Matt's dbbackup.sh script at 2:15 am every day

Code:

15 2 * * * /path/to/dbbackup.sh >/dev/null 2>&1

or create a new shell script and only run lines 1 to 10 if you don't need the upload.pl

 

You could but personally, I like to separate mysqldump backup from pigz compression. That way the actual backup time is as fast as possible to not interfere with the web app's usage during mysqldump.

 

Use a script to bundle all the commands in.. long term much easier to edit and evolve the script to expand it's feature set. In time with more bash scripting know how, you'll end up having a custom script of your own

 

no extra slash as you'll duplicate it on the $DIR/mysqlbackup line

I'd leave --opt and remove --single-transaction if any of your database's tables are non-InnoDB i.e. MyISAM.

I suppose you could check if MyISAM tables exist (using find command *.MYD files in database directory) and only use --single-transaction if no MyISAM tables are detected

Code:

#!/bin/bash
DATABASE_NAME='your_database_name'
DATABASE_USERNAME='your mysql username'
PASSWD="mydatabase password"
DIR="/home/backup"
# Dump the database(s)
if [[ -z $(find /var/lib/mysql/${DATABASE_NAME} -type f -name "*.MYD" -print ) ]]; then
    /usr/bin/mysqldump --opt --single-transaction $DATABASE_NAME -u $DATABASE_USERNAME -p$PASSWD > $DIR/mysqlbackup.$(/bin/date +%Y-%m-%d).sql
else
    /usr/bin/mysqldump --opt $DATABASE_NAME -u $DATABASE_USERNAME -p$PASSWD > $DIR/mysqlbackup.$(/bin/date +%Y-%m-%d).sql
fi
# Compress them with pigz
/usr/bin/pigz -4 $DIR/*.$(/bin/date +%Y-%m-%d).sql

 

-d option for pigz or gzip

Code:

pigz -h
Usage: pigz [options] [files ...]
  will compress files in place, adding the suffix '.gz'.  If no files are
  specified, stdin will be compressed to stdout.  pigz does what gzip does,
  but spreads the work over multiple processors and cores when compressing.

Options:
  -0 to -9, -11        Compression level (11 is much slower, a few % better)
  --fast, --best       Compression levels 1 and 9 respectively
  -b, --blocksize mmm  Set compression block size to mmmK (default 128K)
  -c, --stdout         Write all processed output to stdout (won't delete)
  -d, --decompress     Decompress the compressed input
  -f, --force          Force overwrite, compress .gz, links, and to terminal
  -F  --first          Do iterations first, before block split for -11
  -h, --help           Display a help screen and quit
  -i, --independent    Compress blocks independently for damage recovery
  -I, --iterations n   Number of iterations for -11 optimization
  -k, --keep           Do not delete original file after processing
  -K, --zip            Compress to PKWare zip (.zip) single entry format
  -l, --list           List the contents of the compressed input
  -L, --license        Display the pigz license and quit
  -M, --maxsplits n    Maximum number of split blocks for -11
  -n, --no-name        Do not store or restore file name in/from header
  -N, --name           Store/restore file name and mod time in/from header
  -O  --oneblock       Do not split into smaller blocks for -11
  -p, --processes n    Allow up to n compression threads (default is the
                       number of online processors, or 8 if unknown)
  -q, --quiet          Print no messages, even on error
  -r, --recursive      Process the contents of all subdirectories
  -R, --rsyncable      Input-determined block locations for rsync
  -S, --suffix .sss    Use suffix .sss instead of .gz (for compression)
  -t, --test           Test the integrity of the compressed input
  -T, --no-time        Do not store or restore mod time in/from header
  -v, --verbose        Provide more verbose output
  -V  --version        Show the version of pigz
  -z, --zlib           Compress to zlib (.zz) instead of gzip format
  --                   All arguments after "--" are treated as files

Code:

gzip -h
Usage: gzip [OPTION]... [FILE]...
Compress or uncompress FILEs (by default, compress FILES in-place).

Mandatory arguments to long options are mandatory for short options too.

  -c, --stdout      write on standard output, keep original files unchanged
  -d, --decompress  decompress
  -f, --force       force overwrite of output file and compress links
  -h, --help        give this help
  -l, --list        list compressed file contents
  -L, --license     display software license
  -n, --no-name     do not save or restore the original name and time stamp
  -N, --name        save or restore the original name and time stamp
  -q, --quiet       suppress all warnings
  -r, --recursive   operate recursively on directories
  -S, --suffix=SUF  use suffix SUF on compressed files
  -t, --test        test compressed file integrity
  -v, --verbose     verbose mode
  -V, --version     display version number
  -1, --fast        compress faster
  -9, --best        compress better
    --rsyncable   Make rsync-friendly archive

With no FILE, or when FILE is -, read standard input.

 

Yeah it loads by default, just use to specifically adding it

altered your example at https://community.centminmod.com/threads/auto-database-backup-script.561/#post-2492

 

Oh one note if you are using any rsync/rsnapshot form of backup for the resulting mysql database sql.gz file, you might want to change

from

Code:

/usr/bin/pigz -4

to

Code:

/usr/bin/pigz -4R

-R option

Code:

-R, --rsyncable      Input-determined block locations for rsync