/ Register

Want more timely Centmin Mod News Updates?
Become a Member

MariaDB dbbackup.sh - quick mysql database backups for Centmin Mod stack

Discussion in 'Beta release code' started by eva2000, Sep 28, 2015.

Tags:
Thread Status:
Not open for further replies.
Previous Thread Next Thread
Loading...
Page 1 of 2
  1. Sep 28, 2015 #1
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    Overview for dbbackup.sh



    Very very simple MySQL database only backup script specifically made for Centmin Mod LEMP stack users. However, it does have Amazon S3 upload support, remote FTP backup and remote MySQL server backup support :) It's basic as there's no Nginx vhost file backups, no email nor mobile device notifications (added in 0.3.6) on backup completion and no error checking for each database backup run nor is there any backups of mysql schema only or mysql grantsA combined Nginx vhost file + MySQL database backup script is in development for later release.

    The script is specific to a single Nginx vhost account. So if you have several Nginx vhosts like newdomain.com and newdomain2.com, you should make a copy of dbbackup.sh and name them, dbbackup_newdomain.com.sh and dbbackup_newdomain2.com.sh etc. It is done this way so you can have finer grain control over which Nginx vhost MySQL database sets are backed up and when they are scheduled as not all web sites on your server will have the same off peak traffic patterns - which is the best time to schedule backups.

    Premium Users

    Currently, this dbbackup.sh script is only available to Centmin Mod Premium Members ONLY and can be found in private Centmin Mod Premium Members ONLY forum.

    FYI, my normal rates for private paying clients for custom tailored written backup scripts starts at fixed rates of US$500+ and higher usually so you're getting good value for the price of Centmin Mod Premium Membership ;)

    Key Features dbbackup.sh



    As at August 28, 2018:
    • MySQL database and schema only backups + /etc/my.cnf backup
    • Multi-threaded parallel compression default pigz with options for lbzip2, pbzip2 and pxz. For single cpu core servers, falls back from pigz to gzip.
    • Amazon S3 Storage support and option to define one of three S3 storage classes (Standard, Standard – IA, and Glacier) to control your Amazon S3 storage costs and Amazon S3 availability and durability. Disabled by default.
    • Amazon S3 Upload Dry Run Mode support. Disabled by default.
    • Remote FTP/SFTP support - can define up to 3 separate FTP/SFTP locations to remotely transfer your backups for added redundancy and data safeguard.Disabled by default.
    • Backup Retention threshold set in days. Older than XX days deletion routine for both local backups and remote FTP/SFTP backups. Amazon S3 you can setup your own S3 bucket's lifecycle management in AWS Console. Example for retention threshold based deletion shown here.
    • Email and Pushover.net mobile notification options on backup runs. Disabled by default.
    • Persistent db-config.ini separate file option so custom settings survive dbbackup.sh script updates.
    • Full backup run logging to log file for diagnostic troubleshooting or historic records
    • Smarter per database character set and collation handling for backups to prevent database table corruption due to character set and mixed collations.

    Setup dbbackup.sh



    Instructions for setup are listed within the top of the actual dbbackup.sh file itself.
    MySQL database backups get saved into directory at /home/nginx/domains/newdomain.com/backup/mysql/
    dbbackup.sh log files get saved into directory at /home/nginx/domains/newdomain.com/backup/mysql-logs/
    dbbackup.sh setup instructions within file
    Code (Text):
    ###########################################################################
# very basic mysql database backup script for
# centminmod.com LEMP stack users
# written by George Liu (eva2000) centminmod.com
###########################################################################
# requirements
# dbbackup.sh assumes you have mysql root password set in /root/.my.cnf
# as centmin mod out of box sets this up already
###########################################################################
# instructions using newdomain.com as example
# 1). save this script to /root/tools/dbbackup.sh
# 2). edit VHOSTNAME variable to the nginx vhost you want to backup dbs for
# 3). create a .dblist file at /home/nginx/domains/newdomain.com/.dblist
# 4). enter one mysql database name per line into .dblist file for databases
#     you want to backup
# 5). chmod 0600 the .dblist file: chmod 0600 .dblist
# 6). setup cronjob every 12hrs
#     15 */12 * * * /root/tools/dbbackup.sh 2>/dev/null
###########################################################################


    .dblist file



    Contents of .dblist file at /home/nginx/domains/newdomain.com/.dblist 4 database names one entry per line. Set up this way so you have full control of which specifically named MySQL databases are grouped together for dbbackup.sh runs.
    Code (Text):
    db1
db2
db3
db4
db5
db6


    dbbackup.sh will process database backups in ascending order of database size.
    Code (Text):
    du ${MYSQLDATADIR} |sort -g | egrep 'db1|db2|db3|db4|db5|db6'
8       /var/lib/mysql/db1
8       /var/lib/mysql/db2
212     /var/lib/mysql/db3
9232    /var/lib/mysql/db5
14356   /var/lib/mysql/db6
17428   /var/lib/mysql/db4


    dbbackup.sh log file



    Save dbbackup.sh log contents for /home/nginx/domains/newdomain.com/backup/mysql-logs/db-backup_280915-044519.log. Includes mytimes cmd output so shows the backup timestamp in multiple timezone times.

    --single-transaction option is used if the script detects that there are no MyISAM tables in the database.

    Code (Text):
    [backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-053249.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 40% maxmem: 3244 KB cswaits: 42]
    pigz -3R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-053249.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 908 KB cswaits: 6]
    510 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-053249.sql.gz

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-280915-053249.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 20% maxmem: 3248 KB cswaits: 23]
    pigz -3R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-280915-053249.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 912 KB cswaits: 7]
    512 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-280915-053249.sql.gz

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-280915-053249.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3288 KB cswaits: 29]
    pigz -3R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-280915-053249.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1164 KB cswaits: 7]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-280915-053249.sql.gz

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-280915-053249.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 66% maxmem: 3296 KB cswaits: 28]
    pigz -3R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-280915-053249.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 200% maxmem: 2684 KB cswaits: 29]
    53K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-280915-053249.sql.gz

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-280915-053249.sql
    [real:0.05s user:0.04s sys:0.00s cpu: 91% maxmem: 3296 KB cswaits: 67]
    pigz -3R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-280915-053249.sql
    [real:0.01s user:0.04s sys:0.00s cpu: 281% maxmem: 3092 KB cswaits: 77]
    271K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-280915-053249.sql.gz

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-280915-053249.sql
    [real:0.07s user:0.06s sys:0.00s cpu: 92% maxmem: 3304 KB cswaits: 64]
    pigz -3R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-280915-053249.sql
    [real:0.02s user:0.06s sys:0.00s cpu: 264% maxmem: 3276 KB cswaits: 230]
    333K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-280915-053249.sql.gz

Mon Sep 28 05:32:49 UTC 2015    [UTC]
Mon Sep 28 15:32:49 AEST 2015   [Australia/Brisbane]
Sun Sep 27 22:32:49 PDT 2015    [America/Los_Angeles]
Mon Sep 28 00:32:49 CDT 2015    [America/Chicago]
Mon Sep 28 01:32:49 EDT 2015    [America/New_York]
Mon Sep 28 06:32:49 BST 2015    [Europe/London]


    Backup File Listing



    backups at /home/nginx/domains/newdomain.com/backup/mysql/
    Code (Text):
    ls -lAhrt /home/nginx/domains/newdomain.com/backup/mysql/
total 4.1M
-rw-r--r-- 1 root nginx  511 Sep 28 02:24 db4-backup-280915-022449.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 02:24 db3-backup-280915-022449.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 02:24 db2-backup-280915-022449.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 02:24 db1-backup-280915-022449.sql.gz
-rw-r--r-- 1 root nginx  512 Sep 28 02:27 db4-backup-280915-022739.sql.gz
-rw-r--r-- 1 root nginx  512 Sep 28 02:27 db3-backup-280915-022739.sql.gz
-rw-r--r-- 1 root nginx  512 Sep 28 02:27 db2-backup-280915-022739.sql.gz
-rw-r--r-- 1 root nginx  512 Sep 28 02:27 db1-backup-280915-022739.sql.gz
-rw-r--r-- 1 root nginx  512 Sep 28 02:29 db4-backup-280915-022917.sql.gz
-rw-r--r-- 1 root nginx  512 Sep 28 02:29 db3-backup-280915-022917.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 02:29 db2-backup-280915-022917.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 02:29 db1-backup-280915-022917.sql.gz
-rw-r--r-- 1 root nginx  510 Sep 28 03:15 db4-backup-280915-031501.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 03:15 db3-backup-280915-031501.sql.gz
-rw-r--r-- 1 root nginx  510 Sep 28 03:15 db2-backup-280915-031501.sql.gz
-rw-r--r-- 1 root nginx  510 Sep 28 03:15 db1-backup-280915-031501.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 03:54 db3-backup-280915-035420.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 03:54 db2-backup-280915-035420.sql.gz
-rw-r--r-- 1 root nginx  510 Sep 28 03:54 db1-backup-280915-035420.sql.gz
-rw-r--r-- 1 root nginx 271K Sep 28 03:54 db6-backup-280915-035420.sql.gz
-rw-r--r-- 1 root nginx  53K Sep 28 03:54 db5-backup-280915-035420.sql.gz
-rw-r--r-- 1 root nginx 333K Sep 28 03:54 db4-backup-280915-035420.sql.gz
-rw-r--r-- 1 root nginx 271K Sep 28 04:34 db6-backup-280915-043413.sql.gz
-rw-r--r-- 1 root nginx  53K Sep 28 04:34 db5-backup-280915-043413.sql.gz
-rw-r--r-- 1 root nginx 333K Sep 28 04:34 db4-backup-280915-043413.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 04:34 db3-backup-280915-043413.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 04:34 db2-backup-280915-043413.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 04:34 db1-backup-280915-043413.sql.gz
-rw-r--r-- 1 root nginx 271K Sep 28 04:39 db6-backup-280915-043913.sql.gz
-rw-r--r-- 1 root nginx  53K Sep 28 04:39 db5-backup-280915-043913.sql.gz
-rw-r--r-- 1 root nginx 333K Sep 28 04:39 db4-backup-280915-043913.sql.gz
-rw-r--r-- 1 root nginx  510 Sep 28 04:39 db3-backup-280915-043913.sql.gz
-rw-r--r-- 1 root nginx  510 Sep 28 04:39 db2-backup-280915-043913.sql.gz
-rw-r--r-- 1 root nginx  510 Sep 28 04:39 db1-backup-280915-043913.sql.gz
-rw-r--r-- 1 root nginx 271K Sep 28 04:41 db6-backup-280915-044135.sql.gz
-rw-r--r-- 1 root nginx  53K Sep 28 04:41 db5-backup-280915-044135.sql.gz
-rw-r--r-- 1 root nginx 333K Sep 28 04:41 db4-backup-280915-044135.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 04:41 db3-backup-280915-044135.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 04:41 db2-backup-280915-044135.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 04:41 db1-backup-280915-044135.sql.gz
-rw-r--r-- 1 root nginx 271K Sep 28 04:45 db6-backup-280915-044519.sql.gz
-rw-r--r-- 1 root nginx  53K Sep 28 04:45 db5-backup-280915-044519.sql.gz
-rw-r--r-- 1 root nginx 333K Sep 28 04:45 db4-backup-280915-044519.sql.gz
-rw-r--r-- 1 root nginx  512 Sep 28 04:45 db3-backup-280915-044519.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 04:45 db2-backup-280915-044519.sql.gz
-rw-r--r-- 1 root nginx  511 Sep 28 04:45 db1-backup-280915-044519.sql.gz
-rw-r--r-- 1 root nginx 271K Sep 28 05:32 db6-backup-280915-053249.sql.gz
-rw-r--r-- 1 root nginx  53K Sep 28 05:32 db5-backup-280915-053249.sql.gz
-rw-r--r-- 1 root nginx 333K Sep 28 05:32 db4-backup-280915-053249.sql.gz
-rw-r--r-- 1 root nginx  13K Sep 28 05:32 db3-backup-280915-053249.sql.gz
-rw-r--r-- 1 root nginx  512 Sep 28 05:32 db2-backup-280915-053249.sql.gz
-rw-r--r-- 1 root nginx  510 Sep 28 05:32 db1-backup-280915-053249.sql.gz


    backup logs at /home/nginx/domains/newdomain.com/backup/mysql-logs/
    Code (Text):
    ls -lAhrt /home/nginx/domains/newdomain.com/backup/mysql-logs/
total 40K
-rw-r--r-- 1 root nginx 1.7K Sep 28 02:27 db-backup_280915-022739.log
-rw-r--r-- 1 root nginx 2.0K Sep 28 02:29 db-backup_280915-022917.log
-rw-r--r-- 1 root nginx 2.0K Sep 28 03:15 db-backup_280915-031501.log
-rw-r--r-- 1 root nginx 2.9K Sep 28 03:54 db-backup_280915-035420.log
-rw-r--r-- 1 root nginx 5.3K Sep 28 04:34 db-backup_280915-043413.log
-rw-r--r-- 1 root nginx 2.9K Sep 28 04:39 db-backup_280915-043913.log
-rw-r--r-- 1 root nginx 2.9K Sep 28 04:41 db-backup_280915-044135.log
-rw-r--r-- 1 root nginx 2.9K Sep 28 04:45 db-backup_280915-044519.log
-rw-r--r-- 1 root nginx 3.0K Sep 28 05:32 db-backup_280915-053249.log


    Pushover Mobile/Tablet Device Notifications



    upload_2016-2-1_18-30-40.png

    upload_2016-2-1_18-30-58.png

    Email Notifications



    Send to my Zoho Mail

    upload_2016-2-1_18-31-19.png
     
    Last edited: Aug 28, 2018
  2. Sep 28, 2015 #2
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    lbzip2 multi-threaded bzip2 optional support.

    When COMPRESS_METHOD='lbzip2' is set, example log output for /home/nginx/domains/newdomain.com/backup/mysql-logs/db-backup_280915-064346.log
    Code:
    [backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-064346.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 40% maxmem: 3244 KB cswaits: 25]
    lbzip2 -2 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-064346.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1056 KB cswaits: 12]
    530 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-064346.sql.bz2

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-280915-064346.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 40% maxmem: 3244 KB cswaits: 2]
    lbzip2 -2 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-280915-064346.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1068 KB cswaits: 11]
    527 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-280915-064346.sql.bz2

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-280915-064346.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3292 KB cswaits: 5]
    lbzip2 -2 /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-280915-064346.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 66% maxmem: 1828 KB cswaits: 11]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-280915-064346.sql.bz2

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-280915-064346.sql
    [real:0.01s user:0.01s sys:0.00s cpu: 62% maxmem: 3296 KB cswaits: 29]
    lbzip2 -2 /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-280915-064346.sql
    [real:0.01s user:0.03s sys:0.00s cpu: 188% maxmem: 5760 KB cswaits: 87]
    71K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-280915-064346.sql.bz2

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-280915-064346.sql
    [real:0.05s user:0.03s sys:0.00s cpu: 88% maxmem: 3296 KB cswaits: 49]
    lbzip2 -2 /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-280915-064346.sql
    [real:0.07s user:0.24s sys:0.01s cpu: 346% maxmem: 7596 KB cswaits: 145]
    424K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-280915-064346.sql.bz2

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-280915-064346.sql
    [real:0.08s user:0.06s sys:0.00s cpu: 93% maxmem: 3300 KB cswaits: 67]
    lbzip2 -2 /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-280915-064346.sql
    [real:0.12s user:0.44s sys:0.02s cpu: 369% maxmem: 7556 KB cswaits: 180]
    770K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-280915-064346.sql.bz2

Mon Sep 28 06:43:46 UTC 2015    [UTC]
Mon Sep 28 16:43:46 AEST 2015   [Australia/Brisbane]
Sun Sep 27 23:43:46 PDT 2015    [America/Los_Angeles]
Mon Sep 28 01:43:46 CDT 2015    [America/Chicago]
Mon Sep 28 02:43:46 EDT 2015    [America/New_York]
Mon Sep 28 07:43:46 BST 2015    [Europe/London]
     
  3. Sep 29, 2015 #3
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    pbzip2 multi-threaded bzip2 optional support.

    When COMPRESS_METHOD='pbzip2' is set
    Code:
    [backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-172726.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 3248 KB cswaits: 40]
    pbzip2 -4 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-172726.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1244 KB cswaits: 6]
    538 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-172726.sql.bz2

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-280915-172726.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 60% maxmem: 3244 KB cswaits: 40]
    pbzip2 -4 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-280915-172726.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1248 KB cswaits: 4]
    539 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-280915-172726.sql.bz2

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-280915-172726.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3292 KB cswaits: 30]
    pbzip2 -4 /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-280915-172726.sql
    [real:0.01s user:0.01s sys:0.00s cpu: 84% maxmem: 2504 KB cswaits: 5]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-280915-172726.sql.bz2

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-280915-172726.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 62% maxmem: 3296 KB cswaits: 28]
    pbzip2 -4 /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-280915-172726.sql
    [real:0.12s user:0.11s sys:0.00s cpu: 98% maxmem: 5056 KB cswaits: 5]
    53K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-280915-172726.sql.bz2

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-280915-172726.sql
    [real:0.04s user:0.04s sys:0.00s cpu: 89% maxmem: 3296 KB cswaits: 63]
    pbzip2 -4 /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-280915-172726.sql
    [real:0.31s user:0.98s sys:0.00s cpu: 316% maxmem: 18228 KB cswaits: 82]
    326K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-280915-172726.sql.bz2

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-280915-172726.sql
    [real:0.07s user:0.06s sys:0.00s cpu: 93% maxmem: 3304 KB cswaits: 51]
    pbzip2 -4 /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-280915-172726.sql
    [real:0.32s user:1.21s sys:0.01s cpu: 378% maxmem: 20448 KB cswaits: 67]
    559K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-280915-172726.sql.bz2
     
  4. Sep 29, 2015 #4
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    dblist command support, running it will output the list of all MySQL database names on the server, so you can use that list to fill in .dblist file.

    Code:
    /root/tools/dbbackup.sh dblist
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| db3                |
| db4                |
| db5                |
| db6                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
    dbinfo command support, running it will output the list of all MySQL database names on the server + summary of index and data size

    Code:
    /root/tools/dbbackup.sh dbinfo  
                          db1 [idx: 0.00 MB data: 0.00 MB]
                          db2 [idx: 0.00 MB data: 0.00 MB]
                          db3 [idx: 0.08 MB data: 0.11 MB]
                          db4 [idx: 1.72 MB data: 7.52 MB]
                          db5 [idx: 0.25 MB data: 1.52 MB]
                          db6 [idx: 0.78 MB data: 5.52 MB]
  verylongdbnameexampletested [idx: 0.00 MB data: 0.00 MB]
     
  5. Sep 29, 2015 #5
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    Amazon S3 Upload Support



    Amazon AWS S3 storage support for backed up MySQL database files. Backed up files get transfered to Amazon AWS S3 via s3cmd and s3-parallel-put cmd line tools. Currently, only s3cmd is used.

    dbbackup.sh instructions within file itself

    Code:
    ###########################################################################
# very basic mysql database backup script for
# centminmod.com LEMP stack users
# written by George Liu (eva2000) centminmod.com
###########################################################################
# requirements
# dbbackup.sh assumes you have mysql root password set in /root/.my.cnf
# as centmin mod out of box sets this up already
###########################################################################
# instructions using newdomain.com as example
# 1). save this script to /root/tools/dbbackup.sh
# 2). edit VHOSTNAME variable to the nginx vhost you want to backup dbs for
# 3). create a .dblist file at /home/nginx/domains/newdomain.com/.dblist
# 4). enter one mysql database name per line into .dblist file for databases
#     you want to backup
# 5). chmod 0600 the .dblist file: chmod 0600 .dblist
# 6). setup cronjob every 12hrs
#     15 */12 * * * /root/tools/dbbackup.sh 2>/dev/null
############################################################################
# instructions for Amazon S3 setup
# 1). You need to create a separate Amazon IAM user with either full privileges
#     for Amazon S3 or specific privileges for a specific S3 bucketname as
#     outlined http://docs.aws.amazon.com/IAM/latest/UserGuide/id_users_create.html
# 
#     You can setup an Amazon AWS IAM Group
#     http://docs.aws.amazon.com/IAM/latest/UserGuide/id_groups.html
#     with specific S3 privileges and add a new IAM user to that IAM Group
# 
# 2). Creating IAM user in step 1, will give you your IAM credentials in the form
#     of an Access Key ID and Secret Access Key - copy and write these down:
# 
#     Access Key ID:
#     Secret Access Key:
# 
# 3). Setup s3cmd and s3-parallel-put cmd line tools which will be used to
#     transfer database backup files to your Amazon S3 defined YOUR_S3BUCKETNAME
# 
#     To set up run s3setup command option flag:
# 
#     ./dbbackup.sh s3setup
# 
#     You will be prompted to enter your ccess Key ID and Secret Access Key
#     & a s3cmd Pass Phrase for encryption. You can re-run ./dbbackup.sh s3setup
#     as many times as you want to change the details
# 
#     On completion an s3cmd config file: /root/.s3cfg is created with the s3cmd
#     settings which you can fine tune yourself. A common setting you may need to
#     change is the S3 region you want to create S3 buckets in if you plan to use
#     s3cmd to create buckets from command line rather than from AWS Console.
# 
#     Default bucket_location = us-west-2 (oregon) as per usage guide http://s3tools.org/usage.
#     Available regions include: us-east-1, us-west-1, us-west-2, eu-west-1,
#     eu-central-1, ap-northeast-1, ap-southeast-1, ap-southeast-2, sa-east-1
#     http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html
# 
#     bucket_location = us-west-2
# 
# 4). Create your S3 bucket for backed database files and set the variable
#     below to that S3 bucket name
# 
#     BUCKETNAME='YOUR_S3BUCKETNAME'
# 
#     You can create the S3 bucket via AWS Console or via s3cmd cmd line
# 
#     s3cmd mb s3://YOUR_S3BUCKETNAME
# 
#     where YOUR_S3BUCKETNAME has to be unique and lowercase - unique as in
#     all Amazon S3 bucket names ever created and in use are already taken by
#     other users, so you need to create the bucket with very unique names.
############################################################################
    S3 support is disabled by default. set to 'y' to enable AWS S3 support and set to your Amazon S3 bucket name.
    Code:
    AWSUPLOAD='n'
BUCKETNAME='YOUR_S3BUCKETNAME'
    sample dbbackup.sh run output

    Code:
    /root/tools/dbbackup.sh      

[backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-020312.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3248 KB cswaits: 2]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-020312.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 908 KB cswaits: 7]
    503 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-020312.sql.gz

----------------------------------------------------------------------------
upload to Amazon S3
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-020312.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-020312.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-020312.sql.gz' (503 bytes in 0.2 seconds, 2.34 kB/s) [1 of 1]

----------------------------------------------------------------------------

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-020312.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 44]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-020312.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 6]
    505 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-020312.sql.gz

----------------------------------------------------------------------------
upload to Amazon S3
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-020312.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-020312.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-020312.sql.gz' (505 bytes in 0.2 seconds, 2.40 kB/s) [1 of 1]

----------------------------------------------------------------------------

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-020312.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3292 KB cswaits: 67]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-020312.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1164 KB cswaits: 6]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-020312.sql.gz

----------------------------------------------------------------------------
upload to Amazon S3
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-020312.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-020312.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-020312.sql.gz' (12438 bytes in 0.2 seconds, 49.50 kB/s) [1 of 1]

----------------------------------------------------------------------------

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-020312.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 66% maxmem: 3296 KB cswaits: 63]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-020312.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 160% maxmem: 2680 KB cswaits: 19]
    38K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-020312.sql.gz

----------------------------------------------------------------------------
upload to Amazon S3
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-020312.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-020312.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-020312.sql.gz' (38068 bytes in 0.3 seconds, 114.27 kB/s) [1 of 1]

----------------------------------------------------------------------------

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-020312.sql
    [real:0.05s user:0.04s sys:0.00s cpu: 90% maxmem: 3296 KB cswaits: 66]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-020312.sql
    [real:0.02s user:0.05s sys:0.00s cpu: 220% maxmem: 3236 KB cswaits: 169]
    155K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-020312.sql.gz

----------------------------------------------------------------------------
upload to Amazon S3
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-020312.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-020312.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-020312.sql.gz' (158193 bytes in 0.7 seconds, 206.12 kB/s) [1 of 1]

----------------------------------------------------------------------------

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-020312.sql
    [real:0.07s user:0.05s sys:0.00s cpu: 91% maxmem: 3300 KB cswaits: 70]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-020312.sql
    [real:0.02s user:0.08s sys:0.00s cpu: 313% maxmem: 3256 KB cswaits: 244]
    253K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-020312.sql.gz

----------------------------------------------------------------------------
upload to Amazon S3
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-020312.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-020312.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-020312.sql.gz' (258344 bytes in 0.7 seconds, 347.25 kB/s) [1 of 1]

----------------------------------------------------------------------------
    listing Amazon S3 bucket's backed up and uploaded files
    Code:
    s3cmd ls -r s3://YOUR_S3BUCKETNAME
2015-09-29 01:34       501   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-013420.sql.gz
2015-09-29 02:03       503   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-020312.sql.gz
2015-09-29 01:34       503   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-013420.sql.gz
2015-09-29 02:03       505   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-020312.sql.gz
2015-09-29 01:34     12437   s3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-013420.sql.gz
2015-09-29 02:03     12438   s3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-020312.sql.gz
2015-09-29 01:34    258345   s3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-013420.sql.gz
2015-09-29 02:03    258344   s3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-020312.sql.gz
2015-09-29 01:34     38067   s3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-013420.sql.gz
2015-09-29 02:03     38068   s3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-020312.sql.gz
2015-09-29 01:34    158193   s3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-013420.sql.gz
2015-09-29 02:03    158193   s3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-020312.sql.gz
     
    Last edited: Sep 29, 2015
  6. Sep 29, 2015 #6
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.0.7 - Amazon S3 Improvement



    Moved Amazon S3 upload routine so it runs after all MySQL databases have been backed up rather than after each MySQL database backup.

    Code:
    /root/tools/dbbackup.sh          

[backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-025619.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 28% maxmem: 3248 KB cswaits: 39]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-025619.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 6]
    504 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-025619.sql.gz

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-025619.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 16% maxmem: 3244 KB cswaits: 22]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-025619.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 5]
    506 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-025619.sql.gz

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-025619.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3292 KB cswaits: 3]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-025619.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1164 KB cswaits: 6]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-025619.sql.gz

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-025619.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 64% maxmem: 3296 KB cswaits: 70]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-025619.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 114% maxmem: 2668 KB cswaits: 47]
    38K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-025619.sql.gz

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-025619.sql
    [real:0.04s user:0.03s sys:0.00s cpu: 89% maxmem: 3296 KB cswaits: 69]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-025619.sql
    [real:0.02s user:0.05s sys:0.00s cpu: 256% maxmem: 3236 KB cswaits: 228]
    155K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-025619.sql.gz

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-025619.sql
    [real:0.07s user:0.05s sys:0.01s cpu: 91% maxmem: 3300 KB cswaits: 52]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-025619.sql
    [real:0.03s user:0.08s sys:0.00s cpu: 283% maxmem: 3256 KB cswaits: 149]
    253K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-025619.sql.gz

----------------------------------------------------------------------------
Upload to Amazon S3 via s3cmd

[s3cmd upload: db1]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-025619.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-025619.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-025619.sql.gz' (504 bytes in 0.2 seconds, 2.06 kB/s) [1 of 1]
    [real:0.67s user:0.07s sys:0.01s cpu: 12% maxmem: 16304 KB cswaits: 10]

[s3cmd upload: db2]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-025619.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-025619.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-025619.sql.gz' (506 bytes in 0.2 seconds, 2.29 kB/s) [1 of 1]
    [real:0.64s user:0.07s sys:0.00s cpu: 12% maxmem: 16312 KB cswaits: 10]

[s3cmd upload: db3]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-025619.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-025619.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-025619.sql.gz' (12438 bytes in 0.3 seconds, 42.08 kB/s) [1 of 1]
    [real:0.67s user:0.06s sys:0.01s cpu: 11% maxmem: 16428 KB cswaits: 11]

[s3cmd upload: db5]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-025619.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-025619.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-025619.sql.gz' (38069 bytes in 0.3 seconds, 110.88 kB/s) [1 of 1]
    [real:0.74s user:0.07s sys:0.01s cpu: 11% maxmem: 16480 KB cswaits: 11]

[s3cmd upload: db6]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-025619.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-025619.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-025619.sql.gz' (158193 bytes in 1.4 seconds, 111.58 kB/s) [1 of 1]
    [real:1.77s user:0.07s sys:0.01s cpu: 4% maxmem: 16576 KB cswaits: 21]

[s3cmd upload: db4]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-025619.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-025619.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-025619.sql.gz' (258345 bytes in 2.1 seconds, 119.99 kB/s) [1 of 1]
    [real:2.55s user:0.08s sys:0.00s cpu: 3% maxmem: 16672 KB cswaits: 53]
     
  7. Sep 29, 2015 #7
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.0.8 - Amazon S3 Bucket Listing



    Added to Amazon S3 routine the currently backed up file listing from remote Amazon S3 bucket at end of backup routine. Listing is sorted by date ascending order.

    Code:
    /root/tools/dbbackup.sh          

[backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-081626.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 42]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-081626.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 6]
    505 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-081626.sql.gz

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-081626.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 43]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-081626.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 5]
    505 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-081626.sql.gz

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-081626.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3296 KB cswaits: 22]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-081626.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1164 KB cswaits: 7]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-081626.sql.gz

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-081626.sql
    [real:0.01s user:0.01s sys:0.00s cpu: 62% maxmem: 3296 KB cswaits: 57]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-081626.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 150% maxmem: 2668 KB cswaits: 18]
    38K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-081626.sql.gz

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-081626.sql
    [real:0.05s user:0.04s sys:0.00s cpu: 88% maxmem: 3300 KB cswaits: 57]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-081626.sql
    [real:0.01s user:0.05s sys:0.00s cpu: 321% maxmem: 3240 KB cswaits: 84]
    155K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-081626.sql.gz

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-081626.sql
    [real:0.07s user:0.05s sys:0.00s cpu: 91% maxmem: 3304 KB cswaits: 62]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-081626.sql
    [real:0.03s user:0.09s sys:0.00s cpu: 285% maxmem: 3252 KB cswaits: 192]
    253K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-081626.sql.gz

----------------------------------------------------------------------------
Upload to Amazon S3 via s3cmd

[s3cmd upload: db1]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-081626.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-081626.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-081626.sql.gz' (505 bytes in 0.3 seconds, 1748.74 B/s) [1 of 1]
    [real:0.70s user:0.07s sys:0.00s cpu: 11% maxmem: 16304 KB cswaits: 10]

[s3cmd upload: db2]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-081626.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-081626.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-081626.sql.gz' (505 bytes in 0.2 seconds, 2023.37 B/s) [1 of 1]
    [real:0.67s user:0.06s sys:0.01s cpu: 11% maxmem: 16296 KB cswaits: 10]

[s3cmd upload: db3]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-081626.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-081626.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-081626.sql.gz' (12438 bytes in 0.3 seconds, 45.56 kB/s) [1 of 1]
    [real:0.66s user:0.07s sys:0.00s cpu: 12% maxmem: 16428 KB cswaits: 10]

[s3cmd upload: db5]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-081626.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-081626.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-081626.sql.gz' (38069 bytes in 0.3 seconds, 113.73 kB/s) [1 of 1]
    [real:0.72s user:0.07s sys:0.00s cpu: 11% maxmem: 16480 KB cswaits: 11]

[s3cmd upload: db6]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-081626.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-081626.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-081626.sql.gz' (158193 bytes in 0.5 seconds, 282.12 kB/s) [1 of 1]
    [real:1.00s user:0.07s sys:0.00s cpu: 8% maxmem: 16576 KB cswaits: 16]

[s3cmd upload: db4]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-081626.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-081626.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-081626.sql.gz' (258345 bytes in 0.8 seconds, 305.86 kB/s) [1 of 1]
    [real:1.27s user:0.07s sys:0.00s cpu: 6% maxmem: 16672 KB cswaits: 28]

[Databases Uploaded to s3://YOUR_S3BUCKETNAME/dbbackups/ bucket]
2015-09-29 08:16       505   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-081626.sql.gz
2015-09-29 08:16       505   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-081626.sql.gz
2015-09-29 08:16     12438   s3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-081626.sql.gz
2015-09-29 08:16     38069   s3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-081626.sql.gz
2015-09-29 08:16    158193   s3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-081626.sql.gz
2015-09-29 08:16    258345   s3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-081626.sql.gz
     
  8. Sep 29, 2015 #8
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.0.9 - Local backup file listing



    Add current local backup directory listing in date ascending order.

    Code:
    /root/tools/dbbackup.sh                          

[backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-082551.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 44]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-082551.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 5]
    505 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-082551.sql.gz

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-082551.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3248 KB cswaits: 38]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-082551.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 6]
    505 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-082551.sql.gz

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-082551.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3292 KB cswaits: 3]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-082551.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1164 KB cswaits: 7]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-082551.sql.gz

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-082551.sql
    [real:0.01s user:0.01s sys:0.00s cpu: 75% maxmem: 3296 KB cswaits: 44]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-082551.sql
    [real:0.00s user:0.01s sys:0.00s cpu: 142% maxmem: 2548 KB cswaits: 16]
    38K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-082551.sql.gz

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-082551.sql
    [real:0.06s user:0.04s sys:0.00s cpu: 83% maxmem: 3296 KB cswaits: 59]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-082551.sql
    [real:0.02s user:0.05s sys:0.00s cpu: 260% maxmem: 3228 KB cswaits: 193]
    155K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-082551.sql.gz

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-082551.sql
    [real:0.07s user:0.05s sys:0.00s cpu: 91% maxmem: 3300 KB cswaits: 58]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-082551.sql
    [real:0.02s user:0.08s sys:0.00s cpu: 306% maxmem: 3276 KB cswaits: 202]
    253K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-082551.sql.gz

----------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
-rw-rw-r-- 1 root nginx 155K Sep 29 08:25 db6-backup-290915-082551.sql.gz
-rw-rw-r-- 1 root nginx  38K Sep 29 08:25 db5-backup-290915-082551.sql.gz
-rw-rw-r-- 1 root nginx 253K Sep 29 08:25 db4-backup-290915-082551.sql.gz
-rw-rw-r-- 1 root nginx  13K Sep 29 08:25 db3-backup-290915-082551.sql.gz
-rw-rw-r-- 1 root nginx  505 Sep 29 08:25 db2-backup-290915-082551.sql.gz
-rw-rw-r-- 1 root nginx  505 Sep 29 08:25 db1-backup-290915-082551.sql.gz

----------------------------------------------------------------------------
Upload to Amazon S3 via s3cmd

[s3cmd upload: db1]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-082551.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-082551.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-082551.sql.gz' (505 bytes in 0.2 seconds, 2.36 kB/s) [1 of 1]
    [real:0.57s user:0.06s sys:0.01s cpu: 13% maxmem: 16304 KB cswaits: 9]

[s3cmd upload: db2]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-082551.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-082551.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-082551.sql.gz' (505 bytes in 0.1 seconds, 3.30 kB/s) [1 of 1]
    [real:0.62s user:0.06s sys:0.01s cpu: 12% maxmem: 16312 KB cswaits: 9]

[s3cmd upload: db3]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-082551.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-082551.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-082551.sql.gz' (12439 bytes in 0.3 seconds, 47.28 kB/s) [1 of 1]
    [real:0.66s user:0.06s sys:0.01s cpu: 11% maxmem: 16428 KB cswaits: 10]

[s3cmd upload: db5]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-082551.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-082551.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-082551.sql.gz' (38068 bytes in 0.4 seconds, 105.78 kB/s) [1 of 1]
    [real:0.71s user:0.06s sys:0.01s cpu: 11% maxmem: 16484 KB cswaits: 10]

[s3cmd upload: db6]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-082551.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-082551.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-082551.sql.gz' (158193 bytes in 0.5 seconds, 282.06 kB/s) [1 of 1]
    [real:0.94s user:0.07s sys:0.01s cpu: 8% maxmem: 16576 KB cswaits: 16]

[s3cmd upload: db4]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-082551.sql.gz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-082551.sql.gz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-082551.sql.gz' (258345 bytes in 0.6 seconds, 421.18 kB/s) [1 of 1]
    [real:0.99s user:0.07s sys:0.01s cpu: 8% maxmem: 16668 KB cswaits: 23]

[Databases Uploaded to s3://YOUR_S3BUCKETNAME/dbbackups/ bucket]
2015-09-29 08:25       505   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-082551.sql.gz
2015-09-29 08:25       505   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-082551.sql.gz
2015-09-29 08:25     12439   s3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-082551.sql.gz
2015-09-29 08:25     38068   s3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-082551.sql.gz
2015-09-29 08:25    158193   s3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-082551.sql.gz
2015-09-29 08:25    258345   s3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-082551.sql.gz
     
  9. Sep 30, 2015 #9
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.1.0 - PXZ Compression Method



    COMPRESS_METHOD='pxz' for multi-threaded xz compression defaults to level 1 due to very high memory usage from level 2 onwards. On a 1.6GB Tarball file, pxz level 1 results in compression ratio of around 30.5% compared to gzip, pigz around 35.5% even at level 9 and 32.5% for pbzip2 and lbzip2 at level 9.

    Code:
    COMPRESS_METHOD='pxz'      # pigz, pbzip2, lbzip2 or pxz default is pigz
    Code:
    ###########################################################################
COMPRESS='y'                # gzip compress sql backups using pigz
COMPRESS_METHOD='pxz'      # pigz, pbzip2, lbzip2 or pxz default is pigz
PIGZ='pigz'                 # compression method pigz or gzip
PIGZOPT='-4R'               # level 3 compression and Rsyncable
LIBZIP2='lbzip2'            # lbzip2 multi-threaded bzip2
LIBZIP2OPT='-4'             # level 4 compression higher uses much more memory
PBZIP2='pbzip2'             # pbzip2 multi-threaded bzip2 uses less memory than lbzip2
PBZIP2OPT='-4'              # level 4 compression higher uses much more memory
PXZ='pxz'                   # pxz multi-threaded xz
PXZOPT='-1'                 # ALOT of memory keep to level 1 compresses better that others even at level 9
    Code:
    /root/tools/dbbackup.sh       

[backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-190325.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 44]
    pxz -1 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-190325.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 175% maxmem: 1240 KB cswaits: 1]
    532 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-190325.sql.xz

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-190325.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 44]
    pxz -1 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-190325.sql
    [real:0.00s user:0.02s sys:0.00s cpu: 275% maxmem: 1240 KB cswaits: 1]
    532 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-190325.sql.xz

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-190325.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3292 KB cswaits: 53]
    pxz -1 /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-190325.sql
    [real:0.00s user:0.01s sys:0.00s cpu: 225% maxmem: 1764 KB cswaits: 1]
    10K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-190325.sql.xz

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-190325.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 64% maxmem: 3300 KB cswaits: 70]
    pxz -1 /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-190325.sql
    [real:0.01s user:0.04s sys:0.00s cpu: 261% maxmem: 4668 KB cswaits: 60]
    43K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-190325.sql.xz

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-190325.sql
    [real:0.05s user:0.04s sys:0.00s cpu: 90% maxmem: 3296 KB cswaits: 55]
    pxz -1 /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-190325.sql
    [real:0.03s user:0.11s sys:0.00s cpu: 375% maxmem: 4940 KB cswaits: 136]
    250K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-190325.sql.xz

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-190325.sql
    [real:0.07s user:0.05s sys:0.00s cpu: 92% maxmem: 3304 KB cswaits: 69]
    pxz -1 /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-190325.sql
    [real:0.08s user:0.26s sys:0.01s cpu: 336% maxmem: 4984 KB cswaits: 150]
    472K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-190325.sql.xz

----------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
-rw-rw-r-- 1 root nginx 250K Sep 29 19:03 db6-backup-290915-190325.sql.xz
-rw-rw-r-- 1 root nginx  43K Sep 29 19:03 db5-backup-290915-190325.sql.xz
-rw-rw-r-- 1 root nginx 472K Sep 29 19:03 db4-backup-290915-190325.sql.xz
-rw-rw-r-- 1 root nginx  10K Sep 29 19:03 db3-backup-290915-190325.sql.xz
-rw-rw-r-- 1 root nginx  532 Sep 29 19:03 db2-backup-290915-190325.sql.xz
-rw-rw-r-- 1 root nginx  532 Sep 29 19:03 db1-backup-290915-190325.sql.xz

----------------------------------------------------------------------------
Upload to Amazon S3 via s3cmd

[s3cmd upload: db1]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-190325.sql.xz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-190325.sql.xz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-190325.sql.xz' (532 bytes in 0.2 seconds, 2.08 kB/s) [1 of 1]
    [real:0.65s user:0.07s sys:0.01s cpu: 12% maxmem: 15300 KB cswaits: 8]

[s3cmd upload: db2]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-190325.sql.xz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-190325.sql.xz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-190325.sql.xz' (532 bytes in 0.2 seconds, 2.09 kB/s) [1 of 1]
    [real:0.67s user:0.07s sys:0.00s cpu: 12% maxmem: 15300 KB cswaits: 9]

[s3cmd upload: db3]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-190325.sql.xz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-190325.sql.xz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-190325.sql.xz' (10152 bytes in 0.3 seconds, 32.11 kB/s) [1 of 1]
    [real:1.05s user:0.07s sys:0.00s cpu: 7% maxmem: 15408 KB cswaits: 10]

[s3cmd upload: db5]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-190325.sql.xz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-190325.sql.xz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-190325.sql.xz' (43880 bytes in 0.4 seconds, 99.46 kB/s) [1 of 1]
    [real:0.83s user:0.06s sys:0.01s cpu: 9% maxmem: 15476 KB cswaits: 13]

[s3cmd upload: db6]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-190325.sql.xz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-190325.sql.xz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-190325.sql.xz' (255172 bytes in 1.1 seconds, 228.81 kB/s) [1 of 1]
    [real:2.50s user:0.07s sys:0.00s cpu: 3% maxmem: 15668 KB cswaits: 22]

[s3cmd upload: db4]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
INFO: Sending file '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-190325.sql.xz', please wait...
File '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-190325.sql.xz' stored as 's3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-190325.sql.xz' (482616 bytes in 2.2 seconds, 216.74 kB/s) [1 of 1]
    [real:2.56s user:0.07s sys:0.01s cpu: 3% maxmem: 15804 KB cswaits: 56]

[Databases Uploaded to s3://YOUR_S3BUCKETNAME/dbbackups/ bucket]
2015-09-29 19:03       532   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-190325.sql.xz
2015-09-29 19:03       532   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-190325.sql.xz
2015-09-29 19:03     10152   s3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-190325.sql.xz
2015-09-29 19:03     43880   s3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-190325.sql.xz
2015-09-29 19:03    255172   s3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-190325.sql.xz
2015-09-29 19:03    482616   s3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-190325.sql.xz
     
  10. Sep 30, 2015 #10
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    Switch from s3cmd 1.5.x branch to 1.6.x branch



    Update to using s3cmd 1.6.x branch for additional S3 feature support and fixes:
    • Support signed URL content disposition type
    • Added 'ls -l' long listing including storage class
    • Added --limit-rate=RATE
    • Added --server-side-encryption-kms-id=KEY_ID
    • Added --storage-class=CLASS
    • Added --requester-pays, [payer] command
    • Added --[no-]check-hostname
    • Added --stop-on-error, removed --ignore-failed-copy
    • Added [setcors], [delcors] commands
    • Added support for cn-north-1 region hostname checks
    • Output strings may have changed. Scripts calling s3cmd expecting specific text may need to be updated.
    • HTTPS is now the default
    • Many unicode fixes
    • Many other bug fixes
    Code:
    s3cmd --version
s3cmd version 1.6.0
    of particular note is the support for --storage-class flag and the new Standard IA (Infrequent Access) class
    Code:
    --storage-class=CLASS
                        Store object with specified CLASS (STANDARD,
                        STANDARD_IA, or REDUCED_REDUNDANCY). Lower per-GB
                        price. [put, cp, mv]
     
    Last edited: Sep 30, 2015
  11. Sep 30, 2015 #11
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.1.2 - Add Storage Class Option



    With updated s3cmd 1.6 branch and new storage class flag available now, added support to dbbackup.sh. There are 3 storage class options, STANDARD, STANDARD_IA or REDUCED_REDUNDANCY and defaults to STANDARD S3 class storage.
    Code:
    ###########################################################################
# Amazon s3 support via s3cmd
AWSUPLOAD='n'
BUCKETNAME='YOUR_S3BUCKETNAME'

# s3cmd
S3CMD_VER='1.6.0'           # s3cmd from https://github.com/s3tools/s3cmd/releases
# https://community.centminmod.com/threads/4537/
STORAGECLASS='STANDARD'     # set to either STANDARD, STANDARD_IA or REDUCED_REDUNDANCY
    Code:
    /root/tools/dbbackup.sh        

[backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-201204.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 41]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-201204.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 4]
    501 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-201204.sql.gz

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-201204.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 1]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-201204.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 6]
    504 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-201204.sql.gz

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-201204.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3292 KB cswaits: 58]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-201204.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1168 KB cswaits: 7]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-201204.sql.gz

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-201204.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 64% maxmem: 3296 KB cswaits: 70]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-201204.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 133% maxmem: 2660 KB cswaits: 91]
    38K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-201204.sql.gz

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-201204.sql
    [real:0.05s user:0.04s sys:0.00s cpu: 91% maxmem: 3296 KB cswaits: 70]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-201204.sql
    [real:0.02s user:0.05s sys:0.00s cpu: 240% maxmem: 3236 KB cswaits: 160]
    155K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-201204.sql.gz

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-201204.sql
    [real:0.07s user:0.05s sys:0.01s cpu: 91% maxmem: 3304 KB cswaits: 71]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-201204.sql
    [real:0.02s user:0.07s sys:0.00s cpu: 296% maxmem: 3276 KB cswaits: 128]
    253K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-201204.sql.gz

----------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
-rw-r--r-- 1 root nginx 155K Sep 29 20:12 db6-backup-290915-201204.sql.gz
-rw-r--r-- 1 root nginx  38K Sep 29 20:12 db5-backup-290915-201204.sql.gz
-rw-r--r-- 1 root nginx 253K Sep 29 20:12 db4-backup-290915-201204.sql.gz
-rw-r--r-- 1 root nginx  13K Sep 29 20:12 db3-backup-290915-201204.sql.gz
-rw-r--r-- 1 root nginx  504 Sep 29 20:12 db2-backup-290915-201204.sql.gz
-rw-r--r-- 1 root nginx  501 Sep 29 20:12 db1-backup-290915-201204.sql.gz

----------------------------------------------------------------------------
Upload to Amazon S3 via s3cmd

[s3cmd upload (Storage Class: STANDARD): db1]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-201204.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-201204.sql.gz'  [1 of 1]
501 of 501   100% in    0s  1106.22 B/s  done
    [real:0.56s user:0.09s sys:0.01s cpu: 20% maxmem: 17332 KB cswaits: 21]

[s3cmd upload (Storage Class: STANDARD): db2]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-201204.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-201204.sql.gz'  [1 of 1]
504 of 504   100% in    0s  1039.17 B/s  done
    [real:0.59s user:0.10s sys:0.01s cpu: 19% maxmem: 17336 KB cswaits: 16]

[s3cmd upload (Storage Class: STANDARD): db3]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-201204.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-201204.sql.gz'  [1 of 1]
12437 of 12437   100% in    0s    25.30 kB/s  done
    [real:0.59s user:0.09s sys:0.02s cpu: 19% maxmem: 17444 KB cswaits: 20]

[s3cmd upload (Storage Class: STANDARD): db5]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-201204.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-201204.sql.gz'  [1 of 1]
38068 of 38068   100% in    0s    62.80 kB/s  done
    [real:0.70s user:0.08s sys:0.02s cpu: 16% maxmem: 17464 KB cswaits: 19]

[s3cmd upload (Storage Class: STANDARD): db6]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-201204.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-201204.sql.gz'  [1 of 1]
158193 of 158193   100% in    0s   167.99 kB/s  done
    [real:1.03s user:0.10s sys:0.01s cpu: 11% maxmem: 17520 KB cswaits: 29]

[s3cmd upload (Storage Class: STANDARD): db4]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-201204.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-201204.sql.gz'  [1 of 1]
258343 of 258343   100% in    1s   216.89 kB/s  done
    [real:1.27s user:0.10s sys:0.01s cpu: 9% maxmem: 17704 KB cswaits: 42]

[Databases Uploaded to s3://YOUR_S3BUCKETNAME/dbbackups/ bucket]
2015-09-29 20:12       501   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-201204.sql.gz
2015-09-29 20:12       504   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-201204.sql.gz
2015-09-29 20:12     12437   s3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-201204.sql.gz
2015-09-29 20:12     38068   s3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-201204.sql.gz
2015-09-29 20:12    158193   s3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-201204.sql.gz
2015-09-29 20:12    258343   s3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-201204.sql.gz
     
  12. Sep 30, 2015 #12
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.1.3 - s3cmd Dry Run Mode



    Added s3cmd DRYRUN='y' mode option. Disabled by default. Basically it just shows what will be uploaded to Amazon S3 but does not actually upload anything. The backup process still runs though.

    Code:
    ###########################################################################
# Amazon s3 support via s3cmd
AWSUPLOAD='n'
DRYRUN='n'
BUCKETNAME='YOUR_S3BUCKETNAME'

# s3cmd
S3CMD_VER='1.6.0'           # s3cmd from https://github.com/s3tools/s3cmd/releases
# https://community.centminmod.com/threads/4537/
STORAGECLASS='STANDARD'     # set to either STANDARD, STANDARD_IA or REDUCED_REDUNDANCY
    Code:
     /root/tools/dbbackup.sh        

[backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-203503.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 43]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-203503.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 6]
    503 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-203503.sql.gz

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-203503.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 43]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-203503.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 6]
    503 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-203503.sql.gz

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-203503.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3292 KB cswaits: 3]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-203503.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1164 KB cswaits: 7]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-203503.sql.gz

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-203503.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 62% maxmem: 3296 KB cswaits: 35]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-203503.sql
    [real:0.00s user:0.01s sys:0.00s cpu: 157% maxmem: 2668 KB cswaits: 83]
    38K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-203503.sql.gz

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-203503.sql
    [real:0.05s user:0.04s sys:0.00s cpu: 89% maxmem: 3296 KB cswaits: 70]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-203503.sql
    [real:0.02s user:0.05s sys:0.00s cpu: 269% maxmem: 3236 KB cswaits: 175]
    155K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-203503.sql.gz

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-203503.sql
    [real:0.07s user:0.06s sys:0.00s cpu: 92% maxmem: 3304 KB cswaits: 71]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-203503.sql
    [real:0.02s user:0.07s sys:0.00s cpu: 262% maxmem: 3256 KB cswaits: 147]
    253K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-203503.sql.gz

----------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
-rw-r--r-- 1 root nginx 155K Sep 29 20:35 db6-backup-290915-203503.sql.gz
-rw-r--r-- 1 root nginx  38K Sep 29 20:35 db5-backup-290915-203503.sql.gz
-rw-r--r-- 1 root nginx 253K Sep 29 20:35 db4-backup-290915-203503.sql.gz
-rw-r--r-- 1 root nginx  13K Sep 29 20:35 db3-backup-290915-203503.sql.gz
-rw-r--r-- 1 root nginx  503 Sep 29 20:35 db2-backup-290915-203503.sql.gz
-rw-r--r-- 1 root nginx  503 Sep 29 20:35 db1-backup-290915-203503.sql.gz

----------------------------------------------------------------------------
Upload to Amazon S3 via s3cmd

[s3cmd upload (Storage Class: STANDARD) ** DRY RUN MODE **: db1]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
upload: '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-290915-203503.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-290915-203503.sql.gz'
WARNING: Exiting now because of --dry-run
    [real:0.11s user:0.09s sys:0.01s cpu: 97% maxmem: 14988 KB cswaits: 14]

[s3cmd upload (Storage Class: STANDARD) ** DRY RUN MODE **: db2]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
upload: '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-290915-203503.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-290915-203503.sql.gz'
WARNING: Exiting now because of --dry-run
    [real:0.11s user:0.09s sys:0.01s cpu: 96% maxmem: 14984 KB cswaits: 12]

[s3cmd upload (Storage Class: STANDARD) ** DRY RUN MODE **: db3]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
upload: '/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-290915-203503.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-290915-203503.sql.gz'
WARNING: Exiting now because of --dry-run
    [real:0.11s user:0.09s sys:0.01s cpu: 98% maxmem: 14992 KB cswaits: 13]

[s3cmd upload (Storage Class: STANDARD) ** DRY RUN MODE **: db5]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
upload: '/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-290915-203503.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-290915-203503.sql.gz'
WARNING: Exiting now because of --dry-run
    [real:0.11s user:0.08s sys:0.02s cpu: 97% maxmem: 15012 KB cswaits: 15]

[s3cmd upload (Storage Class: STANDARD) ** DRY RUN MODE **: db6]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
upload: '/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-290915-203503.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-290915-203503.sql.gz'
WARNING: Exiting now because of --dry-run
    [real:0.11s user:0.09s sys:0.01s cpu: 97% maxmem: 15036 KB cswaits: 11]

[s3cmd upload (Storage Class: STANDARD) ** DRY RUN MODE **: db4]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
upload: '/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-290915-203503.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-290915-203503.sql.gz'
WARNING: Exiting now because of --dry-run
    [real:0.11s user:0.10s sys:0.00s cpu: 97% maxmem: 15036 KB cswaits: 15]

[Databases Uploaded to s3://YOUR_S3BUCKETNAME/dbbackups/ bucket]
** DRY RUN MODE **
     
  13. Sep 30, 2015 #13
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.1.4 - dbbackup.sh version checking



    Added a routine for dbbackup.sh version checking to alert you if you are running an older or different version of the script than the latest available version.

    Code:
    /root/tools/dbbackup.sh

------------------------------------------------------------------------------
Version Check:
------------------------------------------------------------------------------
!!! there maybe a newer version of /root/tools/dbbackup.sh available !!!
https://community.centminmod.com/posts/18935/

Always ensure Current Version is higher or equal to Latest Version
------------------------------------------------------------------------------
Current Version: 0.1.3
Latest Version: 0.1.4
------------------------------------------------------------------------------
     
    Last edited: Oct 2, 2015
  14. Oct 1, 2015 #14
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.1.5 - Add Remote FTP Backup Support



    Personally, I rarely use FTP transfers for backups as it can be relatively slower. However, added it via lftp and new variables. No handling of retention and deletions for remote FTP hosted files for now. Just getting basic FTP transfers working first.
    • Backups transfered to ftp.domain.com/backup via variable FTPHOST which is made up of ${FTPHOSTBASE}/${FTPHOSTDIR}.
    • Disabled by default FTP='n'

    Code:
    ###########################################################################
# ftp support via lftp http://lftp.yar.ru/lftp-man.html
LFTP_VER='4.6.4'
FTP='n'                                    # whether to enable ftp remote transfers
FTPHOSTBASE='ftp.domain.com'               # destination ftp hostname
FTPHOSTDIR='backup'                        # set destination backup directory
FTPHOST="${FTPHOSTBASE}/${FTPHOSTDIR}"     # ftp.domain.com/backup
FTPPORT='21'                               # remote ftp port
FTPMODE='passive'                          # not used right now
FTPUSER='ftpuser'                          # ftp username
FTPPASS='ftppassword'                 # ftp password
FTPOPTS="-u "${FTPUSER},"${FTPPASS}"" -p ${FTPPORT}"  # DO NOT EDIT
    Code:
    /root/tools/dbbackup.sh

------------------------------------------------------------------------------
Version Check:
------------------------------------------------------------------------------
!!! there maybe a newer version of /root/tools/dbbackup.sh available !!!
https://community.centminmod.com/posts/18935/

Always ensure Current Version is higher or equal to Latest Version
------------------------------------------------------------------------------
Current Version: 0.1.5
Lastest Version: 0.1.4
------------------------------------------------------------------------------


[backup: db1]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-300915-175122.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3244 KB cswaits: 22]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-300915-175122.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 912 KB cswaits: 5]
    505 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-300915-175122.sql.gz

[backup: db2]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-300915-175122.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 40% maxmem: 3248 KB cswaits: 44]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-300915-175122.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 6]
    505 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-300915-175122.sql.gz

[backup: db3]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-300915-175122.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 3296 KB cswaits: 2]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-300915-175122.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1164 KB cswaits: 6]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-300915-175122.sql.gz

[backup: db5]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-300915-175122.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 64% maxmem: 3296 KB cswaits: 69]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-300915-175122.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 160% maxmem: 2532 KB cswaits: 49]
    38K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-300915-175122.sql.gz

[backup: db6]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-300915-175122.sql
    [real:0.05s user:0.05s sys:0.00s cpu: 89% maxmem: 3300 KB cswaits: 60]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-300915-175122.sql
    [real:0.02s user:0.06s sys:0.00s cpu: 260% maxmem: 3228 KB cswaits: 76]
    155K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-300915-175122.sql.gz

[backup: db4]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-300915-175122.sql
    [real:0.07s user:0.05s sys:0.00s cpu: 92% maxmem: 3304 KB cswaits: 70]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-300915-175122.sql
    [real:0.02s user:0.09s sys:0.00s cpu: 320% maxmem: 3264 KB cswaits: 156]
    253K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-300915-175122.sql.gz

------------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
-rw-r--r-- 1 root nginx  38K Sep 30 17:51 db5-backup-300915-175122.sql.gz
-rw-r--r-- 1 root nginx  13K Sep 30 17:51 db3-backup-300915-175122.sql.gz
-rw-r--r-- 1 root nginx  505 Sep 30 17:51 db2-backup-300915-175122.sql.gz
-rw-r--r-- 1 root nginx  505 Sep 30 17:51 db1-backup-300915-175122.sql.gz
-rw-r--r-- 1 root nginx 155K Sep 30 17:51 db6-backup-300915-175122.sql.gz
-rw-r--r-- 1 root nginx 253K Sep 30 17:51 db4-backup-300915-175122.sql.gz

------------------------------------------------------------------------------
FTP transfer via lftp

transfer /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-300915-175122.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-300915-175122.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-300915-175122.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-300915-175122.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-300915-175122.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-300915-175122.sql.gz

[Databases FTP transferred to ftp.domain.com/backup
cd ok, cwd=/backup
drwxr-s---    2 501      nginx        4.0K Sep 30 17:51 ./
drwxr-s---    6 501      nginx        4.0K Sep 30 16:02 ../
-rw-r-----    1 501      nginx         505 Sep 30 17:50 db1-backup-300915-175015.sql.gz
-rw-r-----    1 501      nginx         504 Sep 30 17:50 db1-backup-300915-175055.sql.gz
-rw-r-----    1 501      nginx         505 Sep 30 17:51 db1-backup-300915-175122.sql.gz
-rw-r-----    1 501      nginx         505 Sep 30 17:50 db2-backup-300915-175015.sql.gz
-rw-r-----    1 501      nginx         505 Sep 30 17:50 db2-backup-300915-175055.sql.gz
-rw-r-----    1 501      nginx         505 Sep 30 17:51 db2-backup-300915-175122.sql.gz
-rw-r-----    1 501      nginx         13K Sep 30 17:50 db3-backup-300915-175015.sql.gz
-rw-r-----    1 501      nginx         13K Sep 30 17:50 db3-backup-300915-175055.sql.gz
-rw-r-----    1 501      nginx         13K Sep 30 17:51 db3-backup-300915-175122.sql.gz
-rw-r-----    1 501      nginx        253K Sep 30 17:50 db4-backup-300915-175015.sql.gz
-rw-r-----    1 501      nginx        253K Sep 30 17:50 db4-backup-300915-175055.sql.gz
-rw-r-----    1 501      nginx        253K Sep 30 17:51 db4-backup-300915-175122.sql.gz
-rw-r-----    1 501      nginx         38K Sep 30 17:50 db5-backup-300915-175015.sql.gz
-rw-r-----    1 501      nginx         38K Sep 30 17:50 db5-backup-300915-175055.sql.gz
-rw-r-----    1 501      nginx         38K Sep 30 17:51 db5-backup-300915-175122.sql.gz
-rw-r-----    1 501      nginx        155K Sep 30 17:50 db6-backup-300915-175015.sql.gz
-rw-r-----    1 501      nginx        155K Sep 30 17:50 db6-backup-300915-175055.sql.gz
-rw-r-----    1 501      nginx        155K Sep 30 17:51 db6-backup-300915-175122.sql.gz
     
  15. Oct 1, 2015 #15
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.1.7 - Add Remote MySQL Database Support



    Add remote MySQL database server support with a new set of dbbackup.sh variables to play with.

    Example run with AWSUPLOAD='y' enabled for S3 uploads + FTP='y' enabled for FTP transfers + REMOTEMYSQL='y' for remote mysql server

    Code:
    ###########################################################################
# Remote MySQL server settings
# ensure CSF Firewall on both ends have whitelisted both
# source and destination IP addresses if both servers run
# centminmod LEMP stack
REMOTEMYSQL='n'                                      # 'y' to enable
REMOTEMYSQL_HOST='YOURREMOTE_MYSQLSERVER_IPADDRESS'  # remote mysql server ip
REMOTEMYSQL_USER='YOURREMOTE_MYSQL_USERNAME'         # remote mysql username
REMOTEMYSQL_PASS='YOURREMOTE_MYSQL_PASSWORD'         # remote mysql password
REMOTEMYSQL_PORT='3306'                              # remote mysql port 3306 default
# DO NOT EDIT REMOTEMYSQL_OPTS
    Code:
    /root/tools/dbbackup.sh

------------------------------------------------------------------------------
Version Check:
------------------------------------------------------------------------------
!!! there maybe a newer version of /root/tools/dbbackup.sh available !!!
https://community.centminmod.com/posts/18935/

Always ensure Current Version is higher or equal to Latest Version
------------------------------------------------------------------------------
Current Version: 0.1.7
Lastest Version: 0.1.6
------------------------------------------------------------------------------


[backup: db1 ** Remote MySQL Server Backup: 111.222.333.444 ** ]
    mysqldump --single-transaction db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-300915-185734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3272 KB cswaits: 23]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-300915-185734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 6]
    509 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-300915-185734.sql.gz

[backup: db2 ** Remote MySQL Server Backup: 111.222.333.444 ** ]
    mysqldump --single-transaction db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-300915-185734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 3272 KB cswaits: 23]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-300915-185734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 908 KB cswaits: 6]
    509 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-300915-185734.sql.gz

[backup: db3 ** Remote MySQL Server Backup: 111.222.333.444 ** ]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-300915-185734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3324 KB cswaits: 36]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-300915-185734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1164 KB cswaits: 6]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-300915-185734.sql.gz

[backup: db5 ** Remote MySQL Server Backup: 111.222.333.444 ** ]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-300915-185734.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 66% maxmem: 3320 KB cswaits: 36]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-300915-185734.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 112% maxmem: 2548 KB cswaits: 10]
    38K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-300915-185734.sql.gz

[backup: db6 ** Remote MySQL Server Backup: 111.222.333.444 ** ]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-300915-185734.sql
    [real:0.06s user:0.04s sys:0.00s cpu: 91% maxmem: 3320 KB cswaits: 26]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-300915-185734.sql
    [real:0.02s user:0.05s sys:0.00s cpu: 257% maxmem: 3232 KB cswaits: 174]
    155K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-300915-185734.sql.gz

[backup: db4 ** Remote MySQL Server Backup: 111.222.333.444 ** ]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-300915-185734.sql
    [real:0.07s user:0.06s sys:0.00s cpu: 93% maxmem: 3324 KB cswaits: 36]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-300915-185734.sql
    [real:0.02s user:0.07s sys:0.00s cpu: 316% maxmem: 3272 KB cswaits: 187]
    253K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-300915-185734.sql.gz

------------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
-rw-r--r-- 1 root nginx 155K Sep 30 18:57 db6-backup-300915-185734.sql.gz
-rw-r--r-- 1 root nginx  38K Sep 30 18:57 db5-backup-300915-185734.sql.gz
-rw-r--r-- 1 root nginx 253K Sep 30 18:57 db4-backup-300915-185734.sql.gz
-rw-r--r-- 1 root nginx  13K Sep 30 18:57 db3-backup-300915-185734.sql.gz
-rw-r--r-- 1 root nginx  509 Sep 30 18:57 db2-backup-300915-185734.sql.gz
-rw-r--r-- 1 root nginx  509 Sep 30 18:57 db1-backup-300915-185734.sql.gz

------------------------------------------------------------------------------
Upload to Amazon S3 via s3cmd

[s3cmd upload (Storage Class: STANDARD): db1]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-300915-185734.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-300915-185734.sql.gz'  [1 of 1]
509 of 509   100% in    0s  1063.62 B/s  done
    [real:0.59s user:0.09s sys:0.02s cpu: 18% maxmem: 17336 KB cswaits: 22]

[s3cmd upload (Storage Class: STANDARD): db2]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-300915-185734.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-300915-185734.sql.gz'  [1 of 1]
509 of 509   100% in    0s  1181.61 B/s  done
    [real:0.54s user:0.10s sys:0.00s cpu: 20% maxmem: 17332 KB cswaits: 20]

[s3cmd upload (Storage Class: STANDARD): db3]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-300915-185734.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-300915-185734.sql.gz'  [1 of 1]
12442 of 12442   100% in    0s    24.78 kB/s  done
    [real:0.60s user:0.09s sys:0.01s cpu: 18% maxmem: 17436 KB cswaits: 18]

[s3cmd upload (Storage Class: STANDARD): db5]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-300915-185734.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-300915-185734.sql.gz'  [1 of 1]
38073 of 38073   100% in    0s    64.04 kB/s  done
    [real:0.69s user:0.10s sys:0.01s cpu: 17% maxmem: 17464 KB cswaits: 25]

[s3cmd upload (Storage Class: STANDARD): db6]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-300915-185734.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-300915-185734.sql.gz'  [1 of 1]
158199 of 158199   100% in    0s   173.74 kB/s  done
    [real:1.00s user:0.09s sys:0.02s cpu: 11% maxmem: 17516 KB cswaits: 25]

[s3cmd upload (Storage Class: STANDARD): db4]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-300915-185734.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-300915-185734.sql.gz'  [1 of 1]
258351 of 258351   100% in    0s   257.24 kB/s  done
    [real:1.09s user:0.10s sys:0.01s cpu: 10% maxmem: 17704 KB cswaits: 37]

[Databases Uploaded to s3://YOUR_S3BUCKETNAME/dbbackups/ bucket]
2015-09-30 18:57       509   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-300915-185734.sql.gz
2015-09-30 18:57       509   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-300915-185734.sql.gz
2015-09-30 18:57     12442   s3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-300915-185734.sql.gz
2015-09-30 18:57     38073   s3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-300915-185734.sql.gz
2015-09-30 18:57    158199   s3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-300915-185734.sql.gz
2015-09-30 18:57    258351   s3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-300915-185734.sql.gz


------------------------------------------------------------------------------
FTP transfer via lftp

transfer /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-300915-185734.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-300915-185734.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-300915-185734.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-300915-185734.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-300915-185734.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-300915-185734.sql.gz

[Databases FTP transferred to ftp.domain.com/backup
cd ok, cwd=/backup
-rw-r-----    1 501      nginx         13K Sep 30 18:57 db3-backup-300915-185734.sql.gz
-rw-r-----    1 501      nginx         38K Sep 30 18:57 db5-backup-300915-185734.sql.gz
-rw-r-----    1 501      nginx         509 Sep 30 18:57 db1-backup-300915-185734.sql.gz
-rw-r-----    1 501      nginx         509 Sep 30 18:57 db2-backup-300915-185734.sql.gz
-rw-r-----    1 501      nginx        155K Sep 30 18:57 db6-backup-300915-185734.sql.gz
-rw-r-----    1 501      nginx        253K Sep 30 18:57 db4-backup-300915-185734.sql.gz
     
  16. Oct 2, 2015 #16
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.3.5 - Example Retention Threshold = 2 days



    Example of setting Retention Thredhold to older than 2 days.

    Code:
    /root/tools/dbbackup.sh                                                            
[backup: db1  ]
    mysqldump db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-011015-213402.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3304 KB cswaits: 52]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-011015-213402.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1120 KB cswaits: 7]
    9.8K /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-011015-213402.sql.gz

[backup: db2  ]
    mysqldump db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-011015-213402.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3300 KB cswaits: 58]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-011015-213402.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1140 KB cswaits: 7]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-011015-213402.sql.gz

[backup: db3  ]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-011015-213402.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3296 KB cswaits: 57]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-011015-213402.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1172 KB cswaits: 7]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-011015-213402.sql.gz

[backup: db5  ]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-011015-213402.sql
    [real:0.01s user:0.01s sys:0.00s cpu: 64% maxmem: 3308 KB cswaits: 51]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-011015-213402.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 200% maxmem: 2540 KB cswaits: 47]
    38K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-011015-213402.sql.gz

[backup: db6  ]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-011015-213402.sql
    [real:0.06s user:0.05s sys:0.00s cpu: 90% maxmem: 3304 KB cswaits: 30]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-011015-213402.sql
    [real:0.02s user:0.07s sys:0.00s cpu: 316% maxmem: 3236 KB cswaits: 165]
    155K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-011015-213402.sql.gz

[backup: db4  ]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-011015-213402.sql
    [real:0.08s user:0.07s sys:0.00s cpu: 91% maxmem: 3308 KB cswaits: 54]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-011015-213402.sql
    [real:0.02s user:0.08s sys:0.00s cpu: 329% maxmem: 3232 KB cswaits: 136]
    253K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-011015-213402.sql.gz

[backup: db7  ]
    mysqldump --single-transaction db7 > /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-011015-213402.sql
    [real:1.80s user:1.10s sys:0.17s cpu: 71% maxmem: 3320 KB cswaits: 84]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-011015-213402.sql
    [real:0.38s user:1.36s sys:0.07s cpu: 376% maxmem: 3584 KB cswaits: 3041]
    3.7M /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-011015-213402.sql.gz

------------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
-rw-r--r-- 1 root nginx 155K Oct  1 21:34 db6-backup-011015-213402.sql.gz
-rw-r--r-- 1 root nginx  38K Oct  1 21:34 db5-backup-011015-213402.sql.gz
-rw-r--r-- 1 root nginx 253K Oct  1 21:34 db4-backup-011015-213402.sql.gz
-rw-r--r-- 1 root nginx  13K Oct  1 21:34 db3-backup-011015-213402.sql.gz
-rw-r--r-- 1 root nginx  13K Oct  1 21:34 db2-backup-011015-213402.sql.gz
-rw-r--r-- 1 root nginx 9.8K Oct  1 21:34 db1-backup-011015-213402.sql.gz
-rw-r--r-- 1 root nginx 3.7M Oct  1 21:34 db7-backup-011015-213402.sql.gz

    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-022449.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-022449.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-022739.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-022739.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-022917.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-022917.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-031501.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-031501.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-035420.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-035420.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-043413.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-043413.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-043913.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-043913.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-044135.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-044135.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-044519.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-044519.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-053249.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-053249.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-061501.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-061501.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-064222.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-064222.sql.gz
    Deleting older than  days backup: /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-064240.sql.gz
    rm -rf /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-280915-064240.sql.gz
     
  17. Oct 2, 2015 #17
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.3.6 - Email & Pushover Mobile Notifications



    0.3.6 add Email and Pushover.net mobile device notifications and DELOLD_VERBOSE variable enabled by default, this variable controls whether you want to list out every old backup file during deletion routine. If you do not want to set DEL_OLD_VERBOSE='n'

    Code:
    DELOLD='y'
DELOLD_VERBOSE='y'
DEL_THRESHOLD='31'
###########################################################################
# pushover API
PUSHALERT='n'
papiurl=https://api.pushover.net/1/messages.json
ptoken='yourapitoken'
puser='yourusertoken'
pdevice='yourdeviceid'
ppriority=0
ptimestamp=$(date +%s)
# pmessage="test timestamp"
###########################################################################
# EMAIL SECTION
EMAIL_SEND='n'
EMAIL='youremailaddress'
    Pushover mobile notification

    dbbackup-pushover-00.png

    dbbackup-pushover-01.png

    Email notification using Zoho Mail Android App

    dbbackup-email-00.png
    dbbackup-email-01.png
     
    Last edited: Oct 2, 2015
  18. Oct 2, 2015 #18
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.3.7 - more verbose existing process check



    If you try running dbbackup.sh when existing process is still running, you get a more verbose message listing any existing processes

    Code:
    /root/tools/dbbackup.sh

------------------------------------------------
existing /root/tools/dbbackup.sh process detected
are you sure you do not have
another /root/tools/dbbackup.sh process still running?
------------------------------------------------

-rw-rw-r-- 1 root root 0 Oct  2 06:32 /tmp/dbbackup-run

------------------------------------------------
ps aufx | grep "dbbackup.sh"
------------------------------------------------
root     26158  0.0  0.1   9332  1508 pts/0    S+   06:32   0:00      |   \_ /bin/bash /root/tools/dbbackup.sh
root     26183  0.0  0.1   9336  1136 pts/0    S+   06:32   0:00      |       \_ /bin/bash /root/tools/dbbackup.sh
root     26271  0.0  0.1   9332  1512 pts/1    S+   06:32   0:00          \_ /bin/bash /root/tools/dbbackup.sh
root     26296  0.0  0.0   9336  1004 pts/1    S+   06:32   0:00              \_ /bin/bash /root/tools/dbbackup.sh
root     26301  0.0  0.0   6444   704 pts/1    S+   06:32   0:00              |   \_ grep dbbackup.sh

------------------------------------------------
try again later
------------------------------------------------
     
  19. Oct 3, 2015 #19
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.4.0 - add mysql schema only backups



    Add mysqldump database schema no data backups for table structure backups as well

    Code:
    /root/tools/dbbackup.sh 

[backup data + schema: db1  ]
    mysqldump db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3300 KB cswaits: 70]

[backup schema only: db1  ]
    mysqldump -d db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 28% maxmem: 3292 KB cswaits: 35]

[compression routine: db1  ]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1120 KB cswaits: 6]
    9.8K /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-031015-090937.sql.gz
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 916 KB cswaits: 6]
    808 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-031015-090937.sql.gz

[backup data + schema: db2  ]
    mysqldump db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3300 KB cswaits: 48]

[backup schema only: db2  ]
    mysqldump -d db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 28% maxmem: 3292 KB cswaits: 30]

[compression routine: db2  ]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1140 KB cswaits: 7]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-031015-090937.sql.gz
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 916 KB cswaits: 6]
    826 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-031015-090937.sql.gz

[backup data + schema: db3  ]
    mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3296 KB cswaits: 3]

[backup schema only: db3  ]
    mysqldump -d db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3288 KB cswaits: 68]

[compression routine: db3  ]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1168 KB cswaits: 6]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-031015-090937.sql.gz
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 920 KB cswaits: 6]
    777 /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-031015-090937.sql.gz

[backup data + schema: db5  ]
    mysqldump --single-transaction db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-031015-090937.sql
    [real:0.01s user:0.00s sys:0.00s cpu: 64% maxmem: 3304 KB cswaits: 43]

[backup schema only: db5  ]
    mysqldump --single-transaction -d db5 > /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 28% maxmem: 3292 KB cswaits: 35]

[compression routine: db5  ]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 114% maxmem: 2680 KB cswaits: 103]
    38K /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-031015-090937.sql.gz
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 916 KB cswaits: 6]
    802 /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-schema-031015-090937.sql.gz

[backup data + schema: db6  ]
    mysqldump --single-transaction db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-031015-090937.sql
    [real:0.06s user:0.05s sys:0.00s cpu: 91% maxmem: 3304 KB cswaits: 41]

[backup schema only: db6  ]
    mysqldump --single-transaction -d db6 > /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 28% maxmem: 3292 KB cswaits: 3]

[compression routine: db6  ]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-031015-090937.sql
    [real:0.02s user:0.05s sys:0.00s cpu: 275% maxmem: 3240 KB cswaits: 102]
    155K /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-031015-090937.sql.gz
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 920 KB cswaits: 6]
    833 /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-schema-031015-090937.sql.gz

[backup data + schema: db4  ]
    mysqldump --single-transaction db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-031015-090937.sql
    [real:0.07s user:0.06s sys:0.00s cpu: 89% maxmem: 3308 KB cswaits: 70]

[backup schema only: db4  ]
    mysqldump --single-transaction -d db4 > /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 28% maxmem: 3296 KB cswaits: 50]

[compression routine: db4  ]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-031015-090937.sql
    [real:0.02s user:0.07s sys:0.00s cpu: 272% maxmem: 3276 KB cswaits: 150]
    253K /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-031015-090937.sql.gz
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 920 KB cswaits: 6]
    917 /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-schema-031015-090937.sql.gz

[backup data + schema: db7  ]
    mysqldump --single-transaction db7 > /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-031015-090937.sql
    [real:1.29s user:1.05s sys:0.13s cpu: 91% maxmem: 3320 KB cswaits: 83]

[backup schema only: db7  ]
    mysqldump --single-transaction -d db7 > /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 28% maxmem: 3300 KB cswaits: 2]

[compression routine: db7  ]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-031015-090937.sql
    [real:0.39s user:1.45s sys:0.06s cpu: 379% maxmem: 3580 KB cswaits: 2761]
    3.7M /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-031015-090937.sql.gz
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-schema-031015-090937.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 932 KB cswaits: 6]
    1.1K /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-schema-031015-090937.sql.gz

------------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
-rw-r--r-- 1 root nginx  802 Oct  3 09:09 db5-backup-schema-031015-090937.sql.gz
-rw-r--r-- 1 root nginx  38K Oct  3 09:09 db5-backup-031015-090937.sql.gz
-rw-r--r-- 1 root nginx  777 Oct  3 09:09 db3-backup-schema-031015-090937.sql.gz
-rw-r--r-- 1 root nginx  13K Oct  3 09:09 db3-backup-031015-090937.sql.gz
-rw-r--r-- 1 root nginx  826 Oct  3 09:09 db2-backup-schema-031015-090937.sql.gz
-rw-r--r-- 1 root nginx  13K Oct  3 09:09 db2-backup-031015-090937.sql.gz
-rw-r--r-- 1 root nginx  808 Oct  3 09:09 db1-backup-schema-031015-090937.sql.gz
-rw-r--r-- 1 root nginx 9.8K Oct  3 09:09 db1-backup-031015-090937.sql.gz
-rw-r--r-- 1 root nginx  833 Oct  3 09:09 db6-backup-schema-031015-090937.sql.gz
-rw-r--r-- 1 root nginx 155K Oct  3 09:09 db6-backup-031015-090937.sql.gz
-rw-r--r-- 1 root nginx  917 Oct  3 09:09 db4-backup-schema-031015-090937.sql.gz
-rw-r--r-- 1 root nginx 253K Oct  3 09:09 db4-backup-031015-090937.sql.gz
-rw-r--r-- 1 root nginx 1.1K Oct  3 09:09 db7-backup-schema-031015-090937.sql.gz
-rw-r--r-- 1 root nginx 3.7M Oct  3 09:09 db7-backup-031015-090937.sql.gz

------------------------------------------------------------------------------
Upload to Amazon S3 via s3cmd

[s3cmd upload (Storage Class: STANDARD): db1]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-031015-090937.sql.gz'  [1 of 1]
 10021 of 10021   100% in    0s    17.72 kB/s  done
    [real:0.66s user:0.09s sys:0.01s cpu: 17% maxmem: 17424 KB cswaits: 17]

[s3cmd upload (Storage Class: STANDARD): db1 schema]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-schema-031015-090937.sql.gz'  [1 of 1]
 808 of 808   100% in    0s  1563.11 B/s  done
    [real:0.62s user:0.10s sys:0.01s cpu: 17% maxmem: 17348 KB cswaits: 22]

[s3cmd upload (Storage Class: STANDARD): db2]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-031015-090937.sql.gz'  [1 of 1]
 13030 of 13030   100% in    0s    25.96 kB/s  done
    [real:0.60s user:0.10s sys:0.01s cpu: 19% maxmem: 17452 KB cswaits: 15]

[s3cmd upload (Storage Class: STANDARD): db2 schema]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-schema-031015-090937.sql.gz'  [1 of 1]
 826 of 826   100% in    0s  1366.86 B/s  done
    [real:0.71s user:0.10s sys:0.00s cpu: 15% maxmem: 17348 KB cswaits: 21]

[s3cmd upload (Storage Class: STANDARD): db3]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-031015-090937.sql.gz'  [1 of 1]
 12438 of 12438   100% in    0s    25.39 kB/s  done
    [real:0.59s user:0.10s sys:0.01s cpu: 19% maxmem: 17448 KB cswaits: 22]

[s3cmd upload (Storage Class: STANDARD): db3 schema]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-schema-031015-090937.sql.gz'  [1 of 1]
 777 of 777   100% in    0s  1767.93 B/s  done
    [real:0.55s user:0.10s sys:0.01s cpu: 20% maxmem: 17336 KB cswaits: 21]

[s3cmd upload (Storage Class: STANDARD): db5]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-031015-090937.sql.gz'  [1 of 1]
 38069 of 38069   100% in    0s    63.07 kB/s  done
    [real:0.70s user:0.09s sys:0.01s cpu: 16% maxmem: 17472 KB cswaits: 23]

[s3cmd upload (Storage Class: STANDARD): db5 schema]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db5-backup-schema-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-schema-031015-090937.sql.gz'  [1 of 1]
 802 of 802   100% in    0s  1779.42 B/s  done
    [real:0.56s user:0.10s sys:0.01s cpu: 20% maxmem: 17340 KB cswaits: 22]

[s3cmd upload (Storage Class: STANDARD): db6]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-031015-090937.sql.gz'  [1 of 1]
 158193 of 158193   100% in    0s   187.71 kB/s  done
    [real:0.93s user:0.09s sys:0.02s cpu: 12% maxmem: 17532 KB cswaits: 35]

[s3cmd upload (Storage Class: STANDARD): db6 schema]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db6-backup-schema-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-schema-031015-090937.sql.gz'  [1 of 1]
 833 of 833   100% in    0s  1681.85 B/s  done
    [real:0.61s user:0.09s sys:0.02s cpu: 19% maxmem: 17348 KB cswaits: 21]

[s3cmd upload (Storage Class: STANDARD): db4]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-031015-090937.sql.gz'  [1 of 1]
 258344 of 258344   100% in    0s   280.65 kB/s  done
    [real:1.01s user:0.10s sys:0.01s cpu: 12% maxmem: 17708 KB cswaits: 38]

[s3cmd upload (Storage Class: STANDARD): db4 schema]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db4-backup-schema-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-schema-031015-090937.sql.gz'  [1 of 1]
 917 of 917   100% in    0s  1923.67 B/s  done
    [real:0.58s user:0.09s sys:0.02s cpu: 19% maxmem: 17336 KB cswaits: 21]

[s3cmd upload (Storage Class: STANDARD): db7]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db7-backup-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db7-backup-031015-090937.sql.gz'  [1 of 1]
 3814998 of 3814998   100% in    1s  1941.27 kB/s  done
    [real:2.03s user:0.15s sys:0.04s cpu: 9% maxmem: 17716 KB cswaits: 149]

[s3cmd upload (Storage Class: STANDARD): db7 schema]
INFO: Compiling list of local files...
INFO: Running stat() and reading/calculating MD5 values on 1 files, this may take some time...
INFO: Summary: 1 local files to upload
'/home/nginx/domains/newdomain.com/backup/mysql/db7-backup-schema-031015-090937.sql.gz' -> 's3://YOUR_S3BUCKETNAME/dbbackups/db7-backup-schema-031015-090937.sql.gz'  [1 of 1]
 1117 of 1117   100% in    0s     2.36 kB/s  done
    [real:0.57s user:0.08s sys:0.02s cpu: 19% maxmem: 17348 KB cswaits: 21]

[Databases Uploaded to s3://YOUR_S3BUCKETNAME/dbbackups/ bucket]
2015-10-03 09:09       777   s3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-schema-031015-090937.sql.gz
2015-10-03 09:09       802   s3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-schema-031015-090937.sql.gz
2015-10-03 09:09       808   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-schema-031015-090937.sql.gz
2015-10-03 09:09       826   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-schema-031015-090937.sql.gz
2015-10-03 09:09       833   s3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-schema-031015-090937.sql.gz
2015-10-03 09:09       917   s3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-schema-031015-090937.sql.gz
2015-10-03 09:09      1117   s3://YOUR_S3BUCKETNAME/dbbackups/db7-backup-schema-031015-090937.sql.gz
2015-10-03 09:09     10021   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-031015-090937.sql.gz
2015-10-03 09:09     12438   s3://YOUR_S3BUCKETNAME/dbbackups/db3-backup-031015-090937.sql.gz
2015-10-03 09:09     13030   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-031015-090937.sql.gz
2015-10-03 09:09     38069   s3://YOUR_S3BUCKETNAME/dbbackups/db5-backup-031015-090937.sql.gz
2015-10-03 09:09    158193   s3://YOUR_S3BUCKETNAME/dbbackups/db6-backup-031015-090937.sql.gz
2015-10-03 09:09    258344   s3://YOUR_S3BUCKETNAME/dbbackups/db4-backup-031015-090937.sql.gz
2015-10-03 09:09   3814998   s3://YOUR_S3BUCKETNAME/dbbackups/db7-backup-031015-090937.sql.gz


------------------------------------------------------------------------------
FTP transfer via lftp

transfer /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db5-backup-schema-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db6-backup-schema-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db4-backup-schema-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-031015-090937.sql.gz
transfer /home/nginx/domains/newdomain.com/backup/mysql/db7-backup-schema-031015-090937.sql.gz

[Databases FTP transferred to ftp.domain.com/backup
cd ok, cwd=/backup
-rw-r-----    1 501      nginx         777 Oct  3 09:09 db3-backup-schema-031015-090937.sql.gz
-rw-r-----    1 501      nginx         802 Oct  3 09:09 db5-backup-schema-031015-090937.sql.gz
-rw-r-----    1 501      nginx         808 Oct  3 09:09 db1-backup-schema-031015-090937.sql.gz
-rw-r-----    1 501      nginx         826 Oct  3 09:09 db2-backup-schema-031015-090937.sql.gz
-rw-r-----    1 501      nginx         833 Oct  3 09:09 db6-backup-schema-031015-090937.sql.gz
-rw-r-----    1 501      nginx         917 Oct  3 09:09 db4-backup-schema-031015-090937.sql.gz
-rw-r-----    1 501      nginx        1117 Oct  3 09:09 db7-backup-schema-031015-090937.sql.gz
-rw-r-----    1 501      nginx       10021 Oct  3 09:09 db1-backup-031015-090937.sql.gz
-rw-r-----    1 501      nginx       12438 Oct  3 09:09 db3-backup-031015-090937.sql.gz
-rw-r-----    1 501      nginx       13030 Oct  3 09:09 db2-backup-031015-090937.sql.gz
-rw-r-----    1 501      nginx       38069 Oct  3 09:09 db5-backup-031015-090937.sql.gz
-rw-r-----    1 501      nginx      158193 Oct  3 09:09 db6-backup-031015-090937.sql.gz
-rw-r-----    1 501      nginx      258344 Oct  3 09:09 db4-backup-031015-090937.sql.gz
-rw-r-----    1 501      nginx     3814998 Oct  3 09:09 db7-backup-031015-090937.sql.gz
     
  20. Oct 3, 2015 #20
    eva2000

    eva2000 Administrator Staff Member

    54,052
    12,176
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,734
    Local Time:
    10:00 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+

    0.4.2 - add DBLIST variable support



    Instead of setting mysql databases to be backed up in .dblist file, you can set them within dbbackup.sh itself via space separated list of mysql databases in DBLIST variable.

    for example

    Code:
    DBLIST='db1 db2'
    Code:
    [backup data + schema: db1  ]
    mysqldump db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-031015-095105.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3300 KB cswaits: 60]

[backup schema only: db1  ]
    mysqldump -d db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-031015-095105.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 28% maxmem: 3296 KB cswaits: 36]

[compression routine: db1  ]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-031015-095105.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1120 KB cswaits: 6]
    9.8K /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-031015-095105.sql.gz
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-031015-095105.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 916 KB cswaits: 6]
    808 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-031015-095105.sql.gz

[backup data + schema: db2  ]
    mysqldump db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-031015-095105.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3300 KB cswaits: 2]

[backup schema only: db2  ]
    mysqldump -d db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-031015-095105.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3296 KB cswaits: 3]

[compression routine: db2  ]
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-031015-095105.sql
    [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1140 KB cswaits: 7]
    13K /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-031015-095105.sql.gz
    pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-031015-095105.sql
    [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 916 KB cswaits: 6]
    826 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-031015-095105.sql.gz

------------------------------------------------------------------------------
[Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
-rw-r--r-- 1 root nginx  826 Oct  3 09:51 db2-backup-schema-031015-095105.sql.gz
-rw-r--r-- 1 root nginx  13K Oct  3 09:51 db2-backup-031015-095105.sql.gz
-rw-r--r-- 1 root nginx  808 Oct  3 09:51 db1-backup-schema-031015-095105.sql.gz
-rw-r--r-- 1 root nginx 9.8K Oct  3 09:51 db1-backup-031015-095105.sql.gz
     
    Last edited: Oct 3, 2015
Previous Thread Next Thread
Loading...
Page 1 of 2
Thread Status:
Not open for further replies.

.