Get the most out of your Centmin Mod LEMP stack
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.

Thread Status:
Not open for further replies.
  1. eva2000

    eva2000 Administrator Staff Member

    36,054
    7,910
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,192
    Local Time:
    8:56 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x

    0.4.5 - Add Multiple Remote FTP Servers Support



    Add multiple remote ftp backup options - adds 2 more ftp locations you can transfer backups to. So you can transfer MySQL database backups to 3 separate remote ftp locations.

    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='ftppass'                          # ftp password
    FTPOPTS="-u "${FTPUSER},"${FTPPASS}"" -p ${FTPPORT}"  # DO NOT EDIT
    
    # 2nd ftp location
    FTP_B='n'                                    # whether to enable ftp remote transfers
    FTPHOSTBASE_B='ftp.domain2.com'              # destination ftp hostname
    FTPHOSTDIR_B='backup'                        # set destination backup directory
    FTPHOST_B="${FTPHOSTBASE_B}/${FTPHOSTDIR_B}" # ftp.domain.com/backup
    FTPPORT_B='21'                               # remote ftp port
    FTPMODE_B='passive'                          # not used right now
    FTPUSER_B='ftpuser'                          # ftp username
    FTPPASS_B='ftppass'                          # ftp password
    FTPOPTS_B="-u "${FTPUSER_B},"${FTPPASS_B}"" -p ${FTPPORT_B}"  # DO NOT EDIT
    
    # 3rd ftp location
    FTP_C='n'                                    # whether to enable ftp remote transfers
    FTPHOSTBASE_C='ftp.domain3.com'              # destination ftp hostname
    FTPHOSTDIR_C='backup'                        # set destination backup directory
    FTPHOST_C="${FTPHOSTBASE_C}/${FTPHOSTDIR_C}" # ftp.domain.com/backup
    FTPPORT_C='21'                               # remote ftp port
    FTPMODE_C='passive'                          # not used right now
    FTPUSER_C='ftpuser'                          # ftp username
    FTPPASS_C='ftppass'                          # ftp password
    FTPOPTS_C="-u "${FTPUSER_C},"${FTPPASS_C}"" -p ${FTPPORT_C}"  # DO NOT EDIT
    ###########################################################################
    Code:
    [backup data + schema: db1  ]
        mysqldump db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-051015-031838.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3300 KB cswaits: 68]
    
    [backup schema only: db1  ]
        mysqldump -d db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-051015-031838.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3296 KB cswaits: 36]
    
    [compression routine: db1  ]
        pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-051015-031838.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-051015-031838.sql.gz
        pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-051015-031838.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-051015-031838.sql.gz
    
    [backup data + schema: db2  ]
        mysqldump db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-051015-031838.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 37% maxmem: 3304 KB cswaits: 57]
    
    [backup schema only: db2  ]
        mysqldump -d db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-051015-031838.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 28% maxmem: 3296 KB cswaits: 55]
    
    [compression routine: db2  ]
        pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-051015-031838.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1144 KB cswaits: 7]
        13K /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-051015-031838.sql.gz
        pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-051015-031838.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-051015-031838.sql.gz
    
    [backup data + schema: db3  ]
        mysqldump db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-051015-031838.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 33% maxmem: 3296 KB cswaits: 17]
    
    [backup schema only: db3  ]
        mysqldump -d db3 > /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-051015-031838.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 28% maxmem: 3292 KB cswaits: 22]
    
    [compression routine: db3  ]
        pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-051015-031838.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1172 KB cswaits: 6]
        13K /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-051015-031838.sql.gz
        pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-051015-031838.sql
        [real:0.00s user:0.00s sys:0.00s cpu: ?% maxmem: 916 KB cswaits: 6]
        778 /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-051015-031838.sql.gz
    
    ------------------------------------------------------------------------------
    [Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
    -rw-r--r-- 1 root nginx  778 Oct  5 03:18 db3-backup-schema-051015-031838.sql.gz
    -rw-r--r-- 1 root nginx  13K Oct  5 03:18 db3-backup-051015-031838.sql.gz
    -rw-r--r-- 1 root nginx  826 Oct  5 03:18 db2-backup-schema-051015-031838.sql.gz
    -rw-r--r-- 1 root nginx  13K Oct  5 03:18 db2-backup-051015-031838.sql.gz
    -rw-r--r-- 1 root nginx  808 Oct  5 03:18 db1-backup-schema-051015-031838.sql.gz
    -rw-r--r-- 1 root nginx 9.8K Oct  5 03:18 db1-backup-051015-031838.sql.gz
    
    
    ------------------------------------------------------------------------------
    FTP transfer via lftp to ftp.domain.com/backup
    
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-051015-031838.sql.gz
    
    [Databases FTP transferred to ftp.domain.com/backup
    cd ok, cwd=/backup
    -rw-r-----    1 501      nginx         778 Oct  5 03:18 db3-backup-schema-051015-031838.sql.gz
    -rw-r-----    1 501      nginx         808 Oct  5 03:18 db1-backup-schema-051015-031838.sql.gz
    -rw-r-----    1 501      nginx         826 Oct  5 03:18 db2-backup-schema-051015-031838.sql.gz
    -rw-r-----    1 501      nginx       10020 Oct  5 03:18 db1-backup-051015-031838.sql.gz
    -rw-r-----    1 501      nginx       12438 Oct  5 03:18 db3-backup-051015-031838.sql.gz
    -rw-r-----    1 501      nginx       13030 Oct  5 03:18 db2-backup-051015-031838.sql.gz
    
    ------------------------------------------------------------------------------
    FTP transfer via lftp to ftp.domain2.com/backup
    
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-051015-031838.sql.gz
    
    [Databases FTP transferred to ftp.domain2.com/backup
    cd ok, cwd=/backup
    -rw-r-----    1 501      nginx         778 Oct  5 03:18 db3-backup-schema-051015-031838.sql.gz
    -rw-r-----    1 501      nginx         808 Oct  5 03:18 db1-backup-schema-051015-031838.sql.gz
    -rw-r-----    1 501      nginx         826 Oct  5 03:18 db2-backup-schema-051015-031838.sql.gz
    -rw-r-----    1 501      nginx       10020 Oct  5 03:18 db1-backup-051015-031838.sql.gz
    -rw-r-----    1 501      nginx       12438 Oct  5 03:18 db3-backup-051015-031838.sql.gz
    -rw-r-----    1 501      nginx       13030 Oct  5 03:18 db2-backup-051015-031838.sql.gz
    
    ------------------------------------------------------------------------------
    FTP transfer via lftp to ftp.domain3.com/backup
    
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-051015-031838.sql.gz
    transfer /home/nginx/domains/newdomain.com/backup/mysql/db3-backup-schema-051015-031838.sql.gz
    
    [Databases FTP transferred to ftp.domain3.com/backup
    cd ok, cwd=/backup
    -rw-r-----    1 501      nginx         778 Oct  5 03:18 db3-backup-schema-051015-031838.sql.gz
    -rw-r-----    1 501      nginx         808 Oct  5 03:18 db1-backup-schema-051015-031838.sql.gz
    -rw-r-----    1 501      nginx         826 Oct  5 03:18 db2-backup-schema-051015-031838.sql.gz
    -rw-r-----    1 501      nginx       10020 Oct  5 03:18 db1-backup-051015-031838.sql.gz
    -rw-r-----    1 501      nginx       12438 Oct  5 03:18 db3-backup-051015-031838.sql.gz
    -rw-r-----    1 501      nginx       13030 Oct  5 03:18 db2-backup-051015-031838.sql.gz
    If you setup the remote ftp servers on Centmin Mod stack remote servers like I did for ftp.domain.com, ftp.domain2.com and ftp.domain3.com

    Code:
     ls -lah /home/nginx/domains/ftp.domain2.com/backup/
    total 232K
    drwxr-s--- 2 nginx nginx 4.0K Oct  5 03:18 .
    drwxr-s--- 6 nginx nginx 4.0K Oct  5 03:01 ..
    -rw-r----- 1 nginx nginx 9.8K Oct  5 03:16 db1-backup-051015-031634.sql.gz
    -rw-r----- 1 nginx nginx 9.8K Oct  5 03:16 db1-backup-051015-031655.sql.gz
    -rw-r----- 1 nginx nginx 9.8K Oct  5 03:18 db1-backup-051015-031825.sql.gz
    -rw-r----- 1 nginx nginx 9.8K Oct  5 03:18 db1-backup-051015-031838.sql.gz
    -rw-r----- 1 nginx nginx  808 Oct  5 03:16 db1-backup-schema-051015-031634.sql.gz
    -rw-r----- 1 nginx nginx  808 Oct  5 03:16 db1-backup-schema-051015-031655.sql.gz
    -rw-r----- 1 nginx nginx  808 Oct  5 03:18 db1-backup-schema-051015-031825.sql.gz
    -rw-r----- 1 nginx nginx  808 Oct  5 03:18 db1-backup-schema-051015-031838.sql.gz
    -rw-r----- 1 nginx nginx  13K Oct  5 03:16 db2-backup-051015-031634.sql.gz
    -rw-r----- 1 nginx nginx  13K Oct  5 03:16 db2-backup-051015-031655.sql.gz
    -rw-r----- 1 nginx nginx  13K Oct  5 03:18 db2-backup-051015-031825.sql.gz
    -rw-r----- 1 nginx nginx  13K Oct  5 03:18 db2-backup-051015-031838.sql.gz
    -rw-r----- 1 nginx nginx  827 Oct  5 03:16 db2-backup-schema-051015-031634.sql.gz
    -rw-r----- 1 nginx nginx  827 Oct  5 03:16 db2-backup-schema-051015-031655.sql.gz
    -rw-r----- 1 nginx nginx  827 Oct  5 03:18 db2-backup-schema-051015-031825.sql.gz
    -rw-r----- 1 nginx nginx  826 Oct  5 03:18 db2-backup-schema-051015-031838.sql.gz
    -rw-r----- 1 nginx nginx  13K Oct  5 03:16 db3-backup-051015-031634.sql.gz
    -rw-r----- 1 nginx nginx  13K Oct  5 03:16 db3-backup-051015-031655.sql.gz
    -rw-r----- 1 nginx nginx  13K Oct  5 03:18 db3-backup-051015-031825.sql.gz
    -rw-r----- 1 nginx nginx  13K Oct  5 03:18 db3-backup-051015-031838.sql.gz
    -rw-r----- 1 nginx nginx  778 Oct  5 03:16 db3-backup-schema-051015-031634.sql.gz
    -rw-r----- 1 nginx nginx  778 Oct  5 03:16 db3-backup-schema-051015-031655.sql.gz
    -rw-r----- 1 nginx nginx  778 Oct  5 03:18 db3-backup-schema-051015-031825.sql.gz
    -rw-r----- 1 nginx nginx  778 Oct  5 03:18 db3-backup-schema-051015-031838.sql.gz
    
    Code:
    ls -lah /home/nginx/domains/ftp.domain3.com/backup/
    total 64K
    drwxr-s--- 2 nginx nginx 4.0K Oct  5 03:18 .
    drwxr-s--- 6 nginx nginx 4.0K Oct  5 03:03 ..
    -rw-r----- 1 nginx nginx 9.8K Oct  5 03:18 db1-backup-051015-031838.sql.gz
    -rw-r----- 1 nginx nginx  808 Oct  5 03:18 db1-backup-schema-051015-031838.sql.gz
    -rw-r----- 1 nginx nginx  13K Oct  5 03:18 db2-backup-051015-031838.sql.gz
    -rw-r----- 1 nginx nginx  826 Oct  5 03:18 db2-backup-schema-051015-031838.sql.gz
    -rw-r----- 1 nginx nginx  13K Oct  5 03:18 db3-backup-051015-031838.sql.gz
    -rw-r----- 1 nginx nginx  778 Oct  5 03:18 db3-backup-schema-051015-031838.sql.gz
     
    Last edited: Oct 5, 2015
    • Useful Useful x 1
  2. eva2000

    eva2000 Administrator Staff Member

    36,054
    7,910
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,192
    Local Time:
    8:56 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x

    0.4.6 add /etc/my.cnf backup routine



    Code:
    /root/tools/dbbackup.sh                               
    backup /etc/my.cnf
    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/my.cnf-backup-221015-010127' -> 's3://YOUR_S3BUCKETNAME/dbbackups/my.cnf-backup-221015-010127'  [1 of 1]
    4513 of 4513   100% in    1s     2.56 kB/s  done
    Code:
    ------------------------------------------------------------------------------
    [Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
    -rw-r--r-- 1 root nginx 4.5K Oct 22 01:01 my.cnf-backup-221015-010127
    -rw-r--r-- 1 root nginx  511 Oct 22 01:01 db2-backup-schema-221015-010127.sql.gz
    -rw-r--r-- 1 root nginx  504 Oct 22 01:01 db2-backup-221015-010127.sql.gz
    -rw-r--r-- 1 root nginx  507 Oct 22 01:01 db1-backup-schema-221015-010127.sql.gz
    -rw-r--r-- 1 root nginx  500 Oct 22 01:01 db1-backup-221015-010127.sql.gz
    ------------------------------------------------------------------------------
    Code:
    [Databases Uploaded to s3://YOUR_S3BUCKETNAME/dbbackups/ bucket]
    2015-10-22 01:01      4513   s3://YOUR_S3BUCKETNAME/dbbackups/my.cnf-backup-221015-010127
    2015-10-22 01:01       500   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-221015-010127.sql.gz
    2015-10-22 01:01       504   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-221015-010127.sql.gz
    2015-10-22 01:01       507   s3://YOUR_S3BUCKETNAME/dbbackups/db1-backup-schema-221015-010127.sql.gz
    2015-10-22 01:01       511   s3://YOUR_S3BUCKETNAME/dbbackups/db2-backup-schema-221015-010127.sql.gz
    Code:
    [Databases FTP transferred to ftp.domain.com/backup
    cd ok, cwd=/backup
    -rw-r-----    1 1000     nginx        4513 Oct 22 01:06 my.cnf-backup-221015-010438
    -rw-r-----    1 1000     nginx         500 Oct 22 01:06 db1-backup-221015-010438.sql.gz
    -rw-r-----    1 1000     nginx         500 Oct 22 01:06 db2-backup-221015-010438.sql.gz
    -rw-r-----    1 1000     nginx         507 Oct 22 01:06 db1-backup-schema-221015-010438.sql.gz
    -rw-r-----    1 1000     nginx         507 Oct 22 01:06 db2-backup-schema-221015-010438.sql.gz
     
    • Like Like x 2
  3. eva2000

    eva2000 Administrator Staff Member

    36,054
    7,910
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,192
    Local Time:
    8:56 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x

    0.5.5 - add lftp debug mode & integrate ftp passive switch variables



    new variables which can be set in dbbackup.sh or persistent db-config.ini itself
    • FTPDEBUG='n', FTPDEBUG_B='n', FTPDEBUG_C='n'. When set to ='y' they will enable lftp debug mode
    • FTPMODE='passive', FTPMODE_B='passive', FTPMODE_C='passive'. 2 options passive or active, defaults to passive mode
     
  4. eva2000

    eva2000 Administrator Staff Member

    36,054
    7,910
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,192
    Local Time:
    8:56 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x

    0.5.9 - Add pxz compress method for CentOS 7



    CentOS 7 now has yum support for pxz compression via EPEL yum repo so add it as available option in COMPRESS_METHOD variable used to determine what method of compression used for database backups.
    Code (Text):
    COMPRESS_METHOD='pigz'      # pigz, pbzip2, lbzip2 or pxz default is pigz
    
     
  5. eva2000

    eva2000 Administrator Staff Member

    36,054
    7,910
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,192
    Local Time:
    8:56 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x

    0.6.0 s3cmd 2.0.1 update



    Updated dbbackup.sh to support s3cmd 2.0.1 version for new setups of dbbackup.sh. For existing dbbackup.sh users you can update using below command within directory that dbbackup.sh resides.
    Code (Text):
    ./dbbackup.sh s3cmd-update
    

    use s3cmd version flag to check updated version
    Code (Text):
    s3cmd --version
    s3cmd version 2.0.1
    
     
    • Like Like x 1
  6. eva2000

    eva2000 Administrator Staff Member

    36,054
    7,910
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,192
    Local Time:
    8:56 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x

    0.6.1



    April 4th, 2018 - 0.6.1 - fix for backing up database names with hyphens in them which isn't a valid identifier for mysql. MariaDB MySQL will auto map and convert hyphens into @002d characters. So db-1 database name is created as db@002d1 in mysql data directory /var/lib/mysql/[email protected] and maps to that if you use mysql commands natively it sees db-1 as [email protected]. But outside of mysql, which dbbackup.sh script works on to check size of databases and whether it's 100% innodb or innodb/myisam mixed tables, the dbbackup script was not able to see db-1 at /var/lib/mysql/db-1 as MariaDB creates the data structure as/var/lib/mysql/[email protected] and thus dbbackup.sh skipped backing up hyphenated database names. The fix to dbbackup.sh adds some scripting logic for /var/lib/mysql database checks for @002d characters.

    Example for dbbackup.sh run with database names db-1 and mydb for vhost name = domain.com where /home/nginx/domains/domain.com/.dblist contains list of database names to backup
    Code (Text):
    cat /home/nginx/domains/domain.com/.dblist
    db-1
    mydb
    

    Code (Text):
    ./dbbackup.sh                 
    backup /etc/my.cnf
    
    [backup data + schema: db-1  ]
        mysqldump --single-transaction db-1 > /home/nginx/domains/domain.com/backup/mysql/db-1-backup-040418-084513.sql
        [real:0.05s user:0.00s sys:0.02s cpu: 66% maxmem: 3672 KB cswaits: 27]
    
    [backup schema only: db-1  ]
        mysqldump --single-transaction -d db-1 > /home/nginx/domains/domain.com/backup/mysql/db-1-backup-schema-040418-084513.sql
        [real:0.07s user:0.01s sys:0.02s cpu: 57% maxmem: 3672 KB cswaits: 28]
    
    [compression routine: db-1  ]
        pigz -4R /home/nginx/domains/domain.com/backup/mysql/db-1-backup-040418-084513.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 90% maxmem: 988 KB cswaits: 7]
        504 /home/nginx/domains/domain.com/backup/mysql/db-1-backup-040418-084513.sql.gz
        pigz -4R /home/nginx/domains/domain.com/backup/mysql/db-1-backup-schema-040418-084513.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 80% maxmem: 988 KB cswaits: 5]
        511 /home/nginx/domains/domain.com/backup/mysql/db-1-backup-schema-040418-084513.sql.gz
    
    [backup data + schema: mydb  ]
        mysqldump --single-transaction mydb > /home/nginx/domains/domain.com/backup/mysql/mydb-backup-040418-084513.sql
        [real:0.05s user:0.01s sys:0.02s cpu: 63% maxmem: 3672 KB cswaits: 27]
    
    [backup schema only: mydb  ]
        mysqldump --single-transaction -d mydb > /home/nginx/domains/domain.com/backup/mysql/mydb-backup-schema-040418-084513.sql
        [real:0.07s user:0.02s sys:0.02s cpu: 68% maxmem: 3672 KB cswaits: 25]
    
    [compression routine: mydb  ]
        pigz -4R /home/nginx/domains/domain.com/backup/mysql/mydb-backup-040418-084513.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 77% maxmem: 988 KB cswaits: 8]
        505 /home/nginx/domains/domain.com/backup/mysql/mydb-backup-040418-084513.sql.gz
        pigz -4R /home/nginx/domains/domain.com/backup/mysql/mydb-backup-schema-040418-084513.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 80% maxmem: 988 KB cswaits: 5]
        512 /home/nginx/domains/domain.com/backup/mysql/mydb-backup-schema-040418-084513.sql.gz
    
    ------------------------------------------------------------------------------
    [Databases backed up locally at: /home/nginx/domains/domain.com/backup/mysql]
    -rw-r--r-- 1 root nginx 5.1K Apr  4 08:45 my.cnf-backup-040418-084513
    -rw-r--r-- 1 root nginx  512 Apr  4 08:45 mydb-backup-schema-040418-084513.sql.gz
    -rw-r--r-- 1 root nginx  505 Apr  4 08:45 mydb-backup-040418-084513.sql.gz
    -rw-r--r-- 1 root nginx  511 Apr  4 08:45 db-1-backup-schema-040418-084513.sql.gz
    -rw-r--r-- 1 root nginx  504 Apr  4 08:45 db-1-backup-040418-084513.sql.gz
    


    mysql data directory saved db-1 database directory as /var/lib/mysql/[email protected]
    Code (Text):
    ls -lhrt /var/lib/mysql | grep @
    drwx------  2 mysql mysql   19 Apr  4 06:44 [email protected]
    
     
    • Informative Informative x 1
  7. eva2000

    eva2000 Administrator Staff Member

    36,054
    7,910
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,192
    Local Time:
    8:56 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x

    dbbackup.sh - 0.6.2



    Updated dbbackup.sh to use --default-character-set flag with mysqldump set to detected mysql database's character set instead of server default character set as some databases have a default character set which differs from server defaults.

    Example run for updated dbbackup.sh 0.6.2 below where database name = testdb uses utf8mb4 database set by web application/php script at database creation/setup time which differs from MariaDB MySQL server default character set = utf8
    Code (Text):
    /root/tools/dbbackup.sh
    backup /etc/my.cnf
    
    [backup data + schema: testdb  ]
        mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob testdb > /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-230818-022524.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 69% maxmem: 3684 KB cswaits: 23]
    
    [backup schema only: testdb  ]
        mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d testdb > /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-230818-022524.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 61% maxmem: 3684 KB cswaits: 23]
    
    [compression routine: testdb  ]
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-230818-022524.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1008 KB cswaits: 9]
        514 /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-230818-022524.sql.gz
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-230818-022524.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1008 KB cswaits: 6]
        521 /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-230818-022524.sql.gz
    
    ------------------------------------------------------------------------------
    [Databases backed up locally at: /home/nginx/domains/testdb.com/backup/mysql]
    -rw-r--r-- 1 root nginx  521 Aug 23 02:25 testdb-backup-schema-230818-022524.sql.gz
    -rw-r--r-- 1 root nginx  514 Aug 23 02:25 testdb-backup-230818-022524.sql.gz
    -rw-r--r-- 1 root nginx 5.2K Aug 23 02:25 my.cnf-backup-230818-022524
     
    • Winner Winner x 2
    • Like Like x 1
  8. eva2000

    eva2000 Administrator Staff Member

    36,054
    7,910
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,192
    Local Time:
    8:56 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x

    dbbackup.sh - 0.6.3



    Updated dbbackup.sh to add 2 optional variables to force a specific default character set via --default-character-set for mysqldump backup commands. Be careful when forcing the default character set as incorrectly set, it can corrupt your database data. If your database has tables with differing default character set/collations, you can also run into issues as well. So need to test your mysqldump backed up sql files via test dummy database restores and inspect the data they contain. For more details on MySQL character set/collations read MySQL :: MySQL 5.6 Reference Manual :: 10 Character Sets, Collations, Unicode

    Code (Text):
    # mysqldump backups use server default character set i.e. utf8 but you want to
    # backup specific databases listed on .dblist or DBLIST using a character set
    # other than server default, you would set FORCE_DB_CHARSET='y' below and specify
    # the default character set you want i.e. FORCE_DB_CHARSETNAME='utf8mb4' would set
    # --default-character-set=utf8mb4 for mysqldump
    FORCE_DB_CHARSET='n'
    FORCE_DB_CHARSETNAME='utf8mb4'
    

    • FORCE_DB_CHARSET='n' is disabled by default, but to enable set FORCE_DB_CHARSET='y' then when you run dbbackup.sh it will run mysqldump with the defined character set from variable FORCE_DB_CHARSETNAME.
    • So when set to FORCE_DB_CHARSETNAME='utf8mb4', mysqldump will use --default-character-set=utf8mb4 when FORCE_DB_CHARSET='y' is set
    Example with these 2 variables set
    Code (Text):
    FORCE_DB_CHARSET='y'
    FORCE_DB_CHARSETNAME='utf8mb4'
    

    Trying to backup sbt and testdb database, even though sbt database is utf8 default, running dbbackup.sh will now force utf8mb4
    Code (Text):
    mysql -e "SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;"
    +--------------------+---------+--------------------+
    | database           | charset | collation          |
    +--------------------+---------+--------------------+
    | information_schema | utf8    | utf8_general_ci    |
    | mysql              | utf8    | utf8_general_ci    |
    | performance_schema | utf8    | utf8_general_ci    |
    | sbt                | utf8    | utf8_general_ci    |
    | sbt_uft8mb4        | utf8mb4 | utf8mb4_general_ci |
    | testdb             | utf8mb4 | utf8mb4_general_ci |
    +--------------------+---------+--------------------+
    

    and resulting sbt database uses --default-character-set=utf8mb4
    Code (Text):
    /root/tools/dbbackup.sh
    backup /etc/my.cnf
    
    [backup data + schema: testdb  ]
        mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob testdb > /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-240818-062155.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 68% maxmem: 3684 KB cswaits: 23]
    
    [backup schema only: testdb  ]
        mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d testdb > /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-240818-062155.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 66% maxmem: 3684 KB cswaits: 23]
    
    [compression routine: testdb  ]
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-240818-062155.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1008 KB cswaits: 5]
        514 /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-240818-062155.sql.gz
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-240818-062155.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1008 KB cswaits: 6]
        521 /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-240818-062155.sql.gz
    
    [backup data + schema: sbt  ]
        mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob sbt > /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-240818-062155.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 61% maxmem: 3728 KB cswaits: 38]
    
    [backup schema only: sbt  ]
        mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d sbt > /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-240818-062155.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 56% maxmem: 3720 KB cswaits: 36]
    
    [compression routine: sbt  ]
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-240818-062155.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1140 KB cswaits: 5]
        5.2K /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-240818-062155.sql.gz
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-240818-062155.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1008 KB cswaits: 5]
        751 /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-240818-062155.sql.gz
    
    ------------------------------------------------------------------------------
    [Databases backed up locally at: /home/nginx/domains/testdb.com/backup/mysql]
    -rw-r--r-- 1 root nginx  521 Aug 24 06:21 testdb-backup-schema-240818-062155.sql.gz
    -rw-r--r-- 1 root nginx  514 Aug 24 06:21 testdb-backup-240818-062155.sql.gz
    -rw-r--r-- 1 root nginx  751 Aug 24 06:21 sbt-backup-schema-240818-062155.sql.gz
    -rw-r--r-- 1 root nginx 5.2K Aug 24 06:21 sbt-backup-240818-062155.sql.gz
    -rw-r--r-- 1 root nginx 5.2K Aug 24 06:21 my.cnf-backup-240818-062155
    
     
    • Winner Winner x 1
  9. eva2000

    eva2000 Administrator Staff Member

    36,054
    7,910
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,192
    Local Time:
    8:56 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x

    dbbackup.sh 0.6.4 updated database character set & collation detection



    Updated dbbackup.sh 0.6.4 MySQL database backup routine to set smarter --default-character-set default character sets for each database you backup to prevent potential corruption of database table data when the table data collation and character set is not the same as the MySQL database server.
    • If empty database default character set, fallback to server default character set found in variable character_set_server.
    • Otherwise, set mysqldump to use character set of tables if utf8mb4 or utf8 detected. So if mixed latin1, utf8 and utf8mb4 table collations, then --default-character-set=utf8mb4 will be used. If mixed latin1, utf8 table collations, --default-character-set=utf8 will be used.
    • Otherwise, fall back to database's default character set which in most cases for databases created on same server will be same as server default character set. If different, database's default character set takes priority.
    Example of backing up 3 databases named, testdb, sbt, sbt_uft8mb4
    Code (Text):
    mysql -e "SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;"
    +--------------------+---------+--------------------+
    | database           | charset | collation          |
    +--------------------+---------+--------------------+
    | information_schema | utf8    | utf8_general_ci    |
    | mysql              | utf8    | utf8_general_ci    |
    | performance_schema | utf8    | utf8_general_ci    |
    | sbt                | utf8    | utf8_general_ci    |
    | sbt_uft8mb4        | utf8mb4 | utf8mb4_general_ci |
    | testdb             | utf8mb4 | utf8mb4_general_ci |
    +--------------------+---------+--------------------+
    

    Code (Text):
    /root/tools/dbbackup.sh
    backup /etc/my.cnf
    
    [backup data + schema: testdb  ]
        mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob testdb > /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-270818-135734.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 66% maxmem: 3684 KB cswaits: 23]
    
    [backup schema only: testdb  ]
        mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d testdb > /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-270818-135734.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 68% maxmem: 3684 KB cswaits: 24]
    
    [compression routine: testdb  ]
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-270818-135734.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1004 KB cswaits: 5]
        515 /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-270818-135734.sql.gz
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-270818-135734.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 0% maxmem: 1004 KB cswaits: 5]
        522 /home/nginx/domains/testdb.com/backup/mysql/testdb-backup-schema-270818-135734.sql.gz
    
    [backup data + schema: sbt  ]
        mysqldump --single-transaction --default-character-set=utf8 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob sbt > /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-270818-135734.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 58% maxmem: 3724 KB cswaits: 37]
    
    [backup schema only: sbt  ]
        mysqldump --single-transaction --default-character-set=utf8 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d sbt > /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-270818-135734.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 64% maxmem: 3716 KB cswaits: 37]
    
    [compression routine: sbt  ]
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-270818-135734.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 50% maxmem: 1140 KB cswaits: 5]
        5.2K /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-270818-135734.sql.gz
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-270818-135734.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1008 KB cswaits: 6]
        748 /home/nginx/domains/testdb.com/backup/mysql/sbt-backup-schema-270818-135734.sql.gz
    
    [backup data + schema: sbt_uft8mb4  ]
        mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob sbt_uft8mb4 > /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-270818-135734.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 60% maxmem: 3728 KB cswaits: 36]
    
    [backup schema only: sbt_uft8mb4  ]
        mysqldump --single-transaction --default-character-set=utf8mb4 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d sbt_uft8mb4 > /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-schema-270818-135734.sql
        [real:0.01s user:0.00s sys:0.00s cpu: 57% maxmem: 3720 KB cswaits: 36]
    
    [compression routine: sbt_uft8mb4  ]
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-270818-135734.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1140 KB cswaits: 5]
        5.2K /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-270818-135734.sql.gz
        pigz -4R /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-schema-270818-135734.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 1004 KB cswaits: 5]
        768 /home/nginx/domains/testdb.com/backup/mysql/sbt_uft8mb4-backup-schema-270818-135734.sql.gz
    
    ------------------------------------------------------------------------------
    [Databases backed up locally at: /home/nginx/domains/testdb.com/backup/mysql]
    -rw-r--r-- 1 root nginx  522 Aug 27 13:57 testdb-backup-schema-270818-135734.sql.gz
    -rw-r--r-- 1 root nginx  515 Aug 27 13:57 testdb-backup-270818-135734.sql.gz
    -rw-r--r-- 1 root nginx  768 Aug 27 13:57 sbt_uft8mb4-backup-schema-270818-135734.sql.gz
    -rw-r--r-- 1 root nginx 5.2K Aug 27 13:57 sbt_uft8mb4-backup-270818-135734.sql.gz
    -rw-r--r-- 1 root nginx  748 Aug 27 13:57 sbt-backup-schema-270818-135734.sql.gz
    -rw-r--r-- 1 root nginx 5.2K Aug 27 13:57 sbt-backup-270818-135734.sql.gz
    -rw-r--r-- 1 root nginx 5.2K Aug 27 13:57 my.cnf-backup-270818-135734
    
     
  10. eva2000

    eva2000 Administrator Staff Member

    36,054
    7,910
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,192
    Local Time:
    8:56 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x

    dbbackup.sh 0.6.5 s3cmd update



    Update s3cmd 2.0.2 can be updated via dbbackup.sh s3cmd-update option
    Code (Text):
    ./dbbackup.sh s3cmd-update
    

    Code (Text):
    s3cmd --version
    s3cmd version 2.0.2
    

    example run backing up mysql databases db1 and db2 to local disk and to AWS S3 bucket with dbbackup.sh options set via config at /root/tools/db-config.ini and using REDUCED_REDUNDANCY storage class instead of STANDARD
    Code (Text):
    AWSUPLOAD='y'
    #STORAGECLASS='STANDARD'
    STORAGECLASS='REDUCED_REDUNDANCY'
    

    Code (Text):
    /root/tools/dbbackup.sh
    
    backup /etc/my.cnf
    INFO: No cache file found, creating it.
    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/my.cnf-backup-130918-142226' -> 's3://S3BUCKETNAME/dbbackups/my.cnf-backup-130918-142226'  [1 of 1]
     4515 of 4515   100% in    1s     3.08 kB/s  done
    
    [backup data + schema: db1  ]
        mysqldump --single-transaction --default-character-set=latin1 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-130918-142226.sql
        [real:0.04s user:0.01s sys:0.01s cpu: 60% maxmem: 3672 KB cswaits: 27]
    
    [backup schema only: db1  ]
        mysqldump --single-transaction --default-character-set=latin1 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d db1 > /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-130918-142226.sql
        [real:0.05s user:0.01s sys:0.02s cpu: 68% maxmem: 3676 KB cswaits: 26]
    
    [compression routine: db1  ]
        pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-130918-142226.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 80% maxmem: 996 KB cswaits: 5]
        502 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-130918-142226.sql.gz
        pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-130918-142226.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 100% maxmem: 996 KB cswaits: 5]
        509 /home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-130918-142226.sql.gz
    
    [backup data + schema: db2  ]
        mysqldump --single-transaction --default-character-set=latin1 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-130918-142226.sql
        [real:0.03s user:0.00s sys:0.01s cpu: 61% maxmem: 3672 KB cswaits: 26]
    
    [backup schema only: db2  ]
        mysqldump --single-transaction --default-character-set=latin1 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -d db2 > /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-130918-142226.sql
        [real:0.03s user:0.00s sys:0.01s cpu: 60% maxmem: 3672 KB cswaits: 27]
    
    [compression routine: db2  ]
        pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-130918-142226.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 66% maxmem: 996 KB cswaits: 5]
        502 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-130918-142226.sql.gz
        pigz -4R /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-130918-142226.sql
        [real:0.00s user:0.00s sys:0.00s cpu: 66% maxmem: 996 KB cswaits: 5]
        509 /home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-130918-142226.sql.gz
    
    ------------------------------------------------------------------------------
    [Databases backed up locally at: /home/nginx/domains/newdomain.com/backup/mysql]
    -rw-r--r-- 1 root nginx 4.5K Sep 13 14:22 my.cnf-backup-130918-142226
    -rw-r--r-- 1 root nginx  509 Sep 13 14:22 db2-backup-schema-130918-142226.sql.gz
    -rw-r--r-- 1 root nginx  502 Sep 13 14:22 db2-backup-130918-142226.sql.gz
    -rw-r--r-- 1 root nginx  509 Sep 13 14:22 db1-backup-schema-130918-142226.sql.gz
    -rw-r--r-- 1 root nginx  502 Sep 13 14:22 db1-backup-130918-142226.sql.gz
    

    Code (Text):
    ------------------------------------------------------------------------------
    Upload to Amazon S3 via s3cmd
    
    [s3cmd upload (Storage Class: REDUCED_REDUNDANCY): 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-130918-142226.sql.gz' -> 's3://S3BUCKETNAME/dbbackups/db1-backup-130918-142226.sql.gz'  [1 of 1]
     502 of 502   100% in    1s   357.40 B/s  done
        [real:1.82s user:0.40s sys:0.09s cpu: 26% maxmem: 20352 KB cswaits: 12]
    
    [s3cmd upload (Storage Class: REDUCED_REDUNDANCY): 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
    upload: '/home/nginx/domains/newdomain.com/backup/mysql/db1-backup-schema-130918-142226.sql.gz' -> 's3://S3BUCKETNAME/dbbackups/db1-backup-schema-130918-142226.sql.gz'  [1 of 1]
     509 of 509   100% in    1s   345.52 B/s  done
        [real:2.00s user:0.52s sys:0.09s cpu: 30% maxmem: 20340 KB cswaits: 12]
    
    [s3cmd upload (Storage Class: REDUCED_REDUNDANCY): 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-130918-142226.sql.gz' -> 's3://S3BUCKETNAME/dbbackups/db2-backup-130918-142226.sql.gz'  [1 of 1]
     502 of 502   100% in    1s   348.87 B/s  done
        [real:1.85s user:0.38s sys:0.10s cpu: 26% maxmem: 20352 KB cswaits: 14]
    
    [s3cmd upload (Storage Class: REDUCED_REDUNDANCY): 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
    upload: '/home/nginx/domains/newdomain.com/backup/mysql/db2-backup-schema-130918-142226.sql.gz' -> 's3://S3BUCKETNAME/dbbackups/db2-backup-schema-130918-142226.sql.gz'  [1 of 1]
     509 of 509   100% in    1s   353.57 B/s  done
        [real:1.88s user:0.44s sys:0.10s cpu: 29% maxmem: 20340 KB cswaits: 12]
    
    [Databases Uploaded to s3://S3BUCKETNAME/dbbackups/ bucket]
    2018-09-13 14:22      4515   s3://S3BUCKETNAME/dbbackups/my.cnf-backup-130918-142226
    2018-09-13 14:22       502   s3://S3BUCKETNAME/dbbackups/db1-backup-130918-142226.sql.gz
    2018-09-13 14:22       502   s3://S3BUCKETNAME/dbbackups/db2-backup-130918-142226.sql.gz
    2018-09-13 14:22       509   s3://S3BUCKETNAME/dbbackups/db1-backup-schema-130918-142226.sql.gz
    2018-09-13 14:22       509   s3://S3BUCKETNAME/dbbackups/db2-backup-schema-130918-142226.sql.gz
    

    Verifying that files are uploaded to Amazon S3 bucket as reduced redundnacy storage class

    dbbackup-0.6.5-reduced-redundancy-s3-backup-01.png dbbackup-0.6.5-reduced-redundancy-s3-backup-02.png
     
..
Thread Status:
Not open for further replies.