Learn about Centmin Mod LEMP Stack today
Become a Member

Auto Database Backup Script 1.0

Export and Upload your Database to Offsite location with Cron

  1. Matt

    Matt Moderator Staff Member

    691
    319
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +444
    Local Time:
    1:07 PM
    1.7.1
    MariaDB 10
    Matt submitted a new resource:

    Auto Database Backup Script - Export and Upload your Database to Offsite location with Cron

    Read more about this resource...
     
    • Like Like x 2
  2. eva2000

    eva2000 Administrator Staff Member

    30,168
    6,784
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    10:07 PM
    Nginx 1.13.x
    MariaDB 5.5
    Thanks Matt thanks for sharing and adding such a useful resource (y)

    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.
     
  3. pamamolf

    pamamolf Well-Known Member

    2,723
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    3:07 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    How i can adjust this line if i want to use it as a simple cron job line and not use the script?

    I mean on normal mysqldump i was give some info for the db like user and pass and which db to get but how i can do it on this?
     
  4. eva2000

    eva2000 Administrator Staff Member

    30,168
    6,784
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    10:07 PM
    Nginx 1.13.x
    MariaDB 5.5
    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
     
  5. pamamolf

    pamamolf Well-Known Member

    2,723
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    3:07 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Yes i understand how to adjust the script variables but i don't know how to end with a single line that it will have the user and pass and dbname and use pigz so i can add it as a cronjob like:

    Code:
    /usr/bin/pigz -4 DATABASE_NAME -u DATABASE_USERNAME -p$PASSWD > $DIR/NAMEOFCHOICE.$(/bin/date +%Y-%m-%d).sql
     
  6. eva2000

    eva2000 Administrator Staff Member

    30,168
    6,784
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    10:07 PM
    Nginx 1.13.x
    MariaDB 5.5
    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
     
  7. pamamolf

    pamamolf Well-Known Member

    2,723
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    3:07 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Sorry for my bad english :(

    I just need to replace this simple line that i use now in cronjob to use the pigz -4

    Code:
    mysqldump -u root -h localhost -pMYPASSHERE database_name | gzip > mydatabase-$(date +%d-%m-%Y).sql.gzip
     
  8. eva2000

    eva2000 Administrator Staff Member

    30,168
    6,784
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    10:07 PM
    Nginx 1.13.x
    MariaDB 5.5
    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.
     
  9. pamamolf

    pamamolf Well-Known Member

    2,723
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    3:07 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    So the script is one way road to follow?

    Or is it ok to run two commands on cronjob?
     
  10. eva2000

    eva2000 Administrator Staff Member

    30,168
    6,784
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    10:07 PM
    Nginx 1.13.x
    MariaDB 5.5
    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 :)
     
  11. pamamolf

    pamamolf Well-Known Member

    2,723
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    3:07 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Is this ok?

    Code:
    #!/bin/bash
    PASSWD="mydatabase password"
    DIR="/home/backup"
    # Dump the database(s)
    /usr/bin/mysqldump --opt --single-transaction DATABASE_NAME -u DATABASE_USERNAME -p$PASSWD > $DIR/mysqlbackup.$(/bin/date +%Y-%m-%d).sql
    # Compress them with pigz
    /usr/bin/pigz -4 $DIR/*.$(/bin/date +%Y-%m-%d).sql
    
    Is it better to use a slash at the end?

    Code:
    DIR="/home/backup/"
    Also what are these for and do you recommend me to use them?

    Code:
    --opt 
    Code:
    --single-transaction
     
  12. eva2000

    eva2000 Administrator Staff Member

    30,168
    6,784
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    10:07 PM
    Nginx 1.13.x
    MariaDB 5.5
    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
     
  13. pamamolf

    pamamolf Well-Known Member

    2,723
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    3:07 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    And last question how to unzip the .sql file that pigz will create?
     
  14. eva2000

    eva2000 Administrator Staff Member

    30,168
    6,784
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    10:07 PM
    Nginx 1.13.x
    MariaDB 5.5
    -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.
     
  15. pamamolf

    pamamolf Well-Known Member

    2,723
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    3:07 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    --opt

    Why i do need this as i think this loads the mysqldumb defaults?

    I f i do not use it it will use defaults anyway or not?
     
  16. eva2000

    eva2000 Administrator Staff Member

    30,168
    6,784
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    10:07 PM
    Nginx 1.13.x
    MariaDB 5.5
  17. pamamolf

    pamamolf Well-Known Member

    2,723
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    3:07 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Ok as it loads by default i will not use it as parameter then :)

    Thanks for your great help George !!!!
     
  18. eva2000

    eva2000 Administrator Staff Member

    30,168
    6,784
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    10:07 PM
    Nginx 1.13.x
    MariaDB 5.5
    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
     
    • Like Like x 1
  19. pamamolf

    pamamolf Well-Known Member

    2,723
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    3:07 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Ok now i have to check if i will use rsync or ftp solution from Matt.

    On Matt's script can i just change port and use sftp or i must adjust all parts of the script?
     
    Last edited: Jun 24, 2014
  20. pamamolf

    pamamolf Well-Known Member

    2,723
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    3:07 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    What is the cswaits on the benchmarks that you did George?