Get the most out of your Centmin Mod LEMP stack
Become a Member

Sysadmin Backup files and database script

Discussion in 'System Administration' started by pamamolf, Jun 18, 2014.

  1. pamamolf

    pamamolf Well-Known Member

    2,726
    243
    63
    May 31, 2014
    Ratings:
    +434
    Local Time:
    7:13 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Hi

    Can you please post your script or any other automated method for backup of the database and files?

    Do you get also any notifications about it on email or mobile phones?

    Thank you
     
  2. eva2000

    eva2000 Administrator Staff Member

    30,195
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    2:13 PM
    Nginx 1.13.x
    MariaDB 5.5
    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/

    pushover_backup_notification.png

    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.
     
    • Like Like x 1
  3. eva2000

    eva2000 Administrator Staff Member

    30,195
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    2:13 PM
    Nginx 1.13.x
    MariaDB 5.5
    • Like Like x 1
  4. Matt

    Matt Moderator Staff Member

    691
    319
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +444
    Local Time:
    5:13 AM
    1.7.1
    MariaDB 10
    I use my own bash script to back up the databases, compress them, and then scp them offsite to my backup server.
     
  5. pamamolf

    pamamolf Well-Known Member

    2,726
    243
    63
    May 31, 2014
    Ratings:
    +434
    Local Time:
    7:13 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Ok it seems that no one will share anything :)
     
  6. eva2000

    eva2000 Administrator Staff Member

    30,195
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    2:13 PM
    Nginx 1.13.x
    MariaDB 5.5
    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.
     
  7. Matt

    Matt Moderator Staff Member

    691
    319
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +444
    Local Time:
    5:13 AM
    1.7.1
    MariaDB 10
  8. pamamolf

    pamamolf Well-Known Member

    2,726
    243
    63
    May 31, 2014
    Ratings:
    +434
    Local Time:
    7:13 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Download not available as i am not Xenforo user :(
     
  9. Matt

    Matt Moderator Staff Member

    691
    319
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +444
    Local Time:
    5:13 AM
    1.7.1
    MariaDB 10
    • Like Like x 1
  10. pamamolf

    pamamolf Well-Known Member

    2,726
    243
    63
    May 31, 2014
    Ratings:
    +434
    Local Time:
    7:13 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Great thanks Matt :)

    Is bzip2 the best multithread solution?

    How can i extract the .bz2 file that it will create as i was use zip or gz ..... ?
     
  11. Dan

    Dan New Member

    14
    4
    3
    Jun 22, 2014
    Ratings:
    +5
    Local Time:
    12:13 AM
    Use the following command to extract bz2 files

    Code:
    bunzip2 your_file_name.bz2
     
    • Informative Informative x 1
  12. eva2000

    eva2000 Administrator Staff Member

    30,195
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    2:13 PM
    Nginx 1.13.x
    MariaDB 5.5
  13. pamamolf

    pamamolf Well-Known Member

    2,726
    243
    63
    May 31, 2014
    Ratings:
    +434
    Local Time:
    7:13 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    From George's benchmarks i can see that pigz is very good also.

    So in your opinion is it better to use pigz or bzip2 or lbzip2 ?

    How can i adjust Matt's script to use the best one?

    Thanks !
     
  14. eva2000

    eva2000 Administrator Staff Member

    30,195
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    2:13 PM
    Nginx 1.13.x
    MariaDB 5.5
    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.
     
  15. pamamolf

    pamamolf Well-Known Member

    2,726
    243
    63
    May 31, 2014
    Ratings:
    +434
    Local Time:
    7:13 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    I prefer to have a safe backup than try to get a better compression ..... Safety first :)

    Code:
    /usr/bin/pigz -4 $DIR/*.$(/bin/date +%Y-%m-%d).sql
    Is this line ok like this?
     
  16. eva2000

    eva2000 Administrator Staff Member

    30,195
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    2:13 PM
    Nginx 1.13.x
    MariaDB 5.5
    yeah looks okay to me.. of course always do tests before live production usage :)
     
    • Agree Agree x 1
  17. pamamolf

    pamamolf Well-Known Member

    2,726
    243
    63
    May 31, 2014
    Ratings:
    +434
    Local Time:
    7:13 AM
    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?
     
  18. eva2000

    eva2000 Administrator Staff Member

    30,195
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    2:13 PM
    Nginx 1.13.x
    MariaDB 5.5
  19. pamamolf

    pamamolf Well-Known Member

    2,726
    243
    63
    May 31, 2014
    Ratings:
    +434
    Local Time:
    7:13 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Ok i post there and i will wait for him to help :)

    Thanks
     
  20. ahmed

    ahmed Member

    201
    15
    18
    Feb 21, 2017
    Ratings:
    +21
    Local Time:
    6:13 AM
    speaking about backups, I managed to make my WordPress backup script with George help, However, there is a weird bug:

    WP-CLI DB export works fine in the sh running manually and fails while it runs from cron

    my post is here: https://community.centminmod.com/posts/48524/