Deleting files

[szepeviktor]

Good morning!

I'd like to backup websites once a week, and keep several weeks on Glacier.
But I do not have enough storage on my vps, so I would delete old backups right away from the vps.
Is there a way/command to keep files on Glacier for a month then delete them later?

[vsespb]

Hello. There is option to delete files from Glacier, which are missing on local file system - see --delete-removed
Unfortunately option for delayed deletion is not yet implemented - see #40

Anyway seems you need to implement some backup rotation (i.e. create new backup each week),
so you can

1. create

/data/backup/week01
/data/backup/week02
/data/backup/week03

etc

2. backup always parent directory - whole /data/backup.

3. delete older weekXX folders

4. delete very old folders from Glacier: issue mtglacier sync --delete-removed with --filter option which will filter only the week
   that you want to delete.

and seems steps 1-4 should be automated with scripting.

p.s.
when playing with --delete-removed you can find that --dry-run is helpful for testing.

[szepeviktor]

Oh! Thank you. You are very kind.

[szepeviktor]

May I post this rolling-backup script here?

[vsespb]

Sure! That would be helpful for other users!

[szepeviktor]

@vsespb Is there a better filter rule to delete only week1 then this?

sync --delete-removed --dir /var/bckroot/ \
--filter '-week2/*' --filter '-week3/*' --filter '-week4/*'

[szepeviktor]

or --include 'week1/*' --exclude '*'

[vsespb]

Hello.

I think that would work:

sync --delete-removed --dir /var/bckroot/ --filter '+/week1/ -'

relevant rules found in docs:

1) If the pattern starts with a '/' then it is anchored to a particular spot in the hierarchy of files, otherwise it is matched against the final component of the filename.

2) If the pattern ends with a '/' then it will only match a directory and all files/subdirectories inside this directory.

9) if PATTERN is empty, it matches anything.

also, same effect:

sync --delete-removed --dir /var/bckroot/ --include '/week1/' --exclude '**'

or

sync --delete-removed --dir /var/bckroot/ --include '/week1/**' --exclude '**'

relevant rule:

6. Wildcard '**' matches anything, including slashes.

so you were close ;)

[szepeviktor]

This is the alpha version of the WordPress rolling backup script

#!/bin/bash

##
## This is a WordPress rolling backup script
##  see: https://github.com/szepeviktor/wplib
##

    #FIXME backup only 'pluginexcept' plugins

    #FIXME  to handle remote servers:
    #host TEXT,         -- host server

    #FIXME get WP 'includes' & 'content' dir from Wordpress


#  glacier─┬─szerver.vault─┬─week00─┬─n*[{sites-FULLDATE.tar}]
#          │               │        └─n*[{sqlite-FULLDATE.db}]
#          │               ├─week01─┬─n*[{sites-FULLDATE.tar}]
#          │               │        └─n*[{sqlite-FULLDATE.db}]
#          │               └─week02─┬─n*[{sites-FULLDATE.tar}]
#          │                        └─n*[{sqlite-FULLDATE.db}]
#          ├─szerver3.vault─┬─week00─┬─n*[{sites-FULLDATE.tar}]
#          │                │        └─n*[{sqlite-FULLDATE.db}]
#          │                ├─week01─┬─n*[{sites-FULLDATE.tar}]
#          │                │        └─n*[{sqlite-FULLDATE.db}]
#          │                └─week02─┬─n*[{sites-FULLDATE.tar}]
#          │                         └─n*[{sqlite-FULLDATE.db}]
#          └─szerver4.vault─┬─week00─┬─n*[{sites-FULLDATE.tar}]
#                           │        └─n*[{sqlite-FULLDATE.db}]
#                           ├─week01─┬─n*[{sites-FULLDATE.tar}]
#                           │        └─n*[{sqlite-FULLDATE.db}]
#                           └─week02─┬─n*[{sites-FULLDATE.tar}]
#                                    └─n*[{sqlite-FULLDATE.db}]

## mysql config from ~/.my.cnf

## source wplib
. /usr/local/share/wplib/wp-libwp.sh

[ -r ~/.config/wplib/wplibrc ] && . ~/.config/wplib/wplibrc

WP_EXCLUDES="--exclude='wp-admin' --exclude='wp-includes' --exclude='wp-content/cache'"


## params: ERROR
error_log() {
    echo "[do-glacier] ERROR ${*}" | tee -a "$GLOG_FILE" >&2
}

## check commands, mtglacier config, vault, wplib config
check_configs() {
    ## commands
    which mtglacier sqlite3 > /dev/null || exit 99

    ## wplib config
    [ -r "$GCONFIG" ] || exit 92
    [ -r "$GSERVER_JOURNAL" ] || exit 93
    [ -z "$GSERVER_VAULT" ] && exit 94
    [ -d "$GBACKUP_DIR" ] || exit 95
    [ -z "$GLOG_FILE" ] || GLOG_FILE="/var/log/mtglacier.log"
    [ -z "$GFULLDATE" ] || GFULLDATE="%Y%m%d%H%M"
    [ -z "$GKEEPDAYS" ] || GKEEPDAYS="62"
    [ -r "$SITELIST_DB" ] || exit 96
    local TABLES="$(echo ".tables" | sqlite3 "$SITELIST_DB")"
    [ -z "$TABLES" ] && exit 97

    ## mtglacier config
    local KEYS="$(grep -c "^key=\|^secret=\|^region=" "$GCONFIG")"
    [ "$KEYS" = 3 ] || exit 98

    ## GSERVER_VAULT
    # trial-and-error? https://github.com/vsespb/mt-aws-glacier/issues/76
}

## execute sqlite3 with args
squery() {
    sqlite3 -separator '*' "$SITELIST_DB" "$*"
}

## execute sqlite without args
squery_pipe() {
    sqlite3 -separator '*' "$SITELIST_DB"
}

## create new database
newdb() {
    squery_pipe <<SQL3
CREATE TABLE sites (
    name TEXT,         -- name of the WP site
    path TEXT,         -- WP root*
    dbname TEXT,       -- database name*
    dbprefix TEXT,     -- db prefix*
    url TEXT,          -- siteurl*
    locale TEXT,       -- site's locale*'
    user TEXT,         -- WP admin user* (has_cap)
    plugins TEXT,      -- comma separated list of all plugins*
    pluginexcept TEXT, -- comma separated list of plugins to hold and to backup* (exists)

    owner TEXT,        -- linux user*
    cache INT,         -- type of opcode cache (0-disabled 1-apc, 2-opache)
    ramcache INT,      -- size of ramdisk cache in MB (0-disable)* (is_mounted)
    vault TEXT         -- glacier vault name (""-no backup)
                   -- (*could be checked)
);
CREATE TABLE weeks (
    dirname TEXT,      -- name of the week's dir' ("YYYYWW")
    timestamp INT      -- UNIX timestamp
);
SQL3
}

## set db fields from WP install parameters
fillup_db() {
    ## all WP sites from home and /var/www
    for YAML in /var/www/*/server/wp-cli.yml /home/*/public*/server/wp-cli.yml; do
        local SITEROOT="${YAML%/*}"
        pushd "$SITEROOT"
        if ! detect_wp; then
            echo "no WP here: '$(pwd)'"
            continue
        fi
        echo "YAML:        ${YAML}"
        echo "root:        ${SITEROOT}"
        local SITEURL="$(do_wp__ option get siteurl 2> /dev/null)"
        local SITENAME="${SITEURL#*//}"
        echo
        read -e -p "site name?   " -i "${SITENAME//[^a-z]}" SITENAME
        if [ -z "$SITENAME" ] || ! [ -z "${SITENAME//[a-z0-9]}" ]; then
            echo "invalid name: '${SITENAME}'"
            continue
        fi
        popd > /dev/null
        squery "INSERT INTO sites VALUES(
            '${SITENAME}',  -- name
            '${SITEROOT}',  -- path
            '$(do_wp__ eval 'echo DB_NAME;' 2> /dev/null)',  -- dbname
            '$(do_wp__ eval 'echo $GLOBALS["table_prefix"];' 2> /dev/null)',  -- dbprefix
            '${SITEURL}',  -- url
            '$(do_wp__ eval 'global $locale; echo $locale;' 2> /dev/null)',  -- locale
            '$(do_wp__ user list --field=user_login \
                --role=administrator --orderby=ID --number=1 2> /dev/null)',  -- user
            '$(do_wp__ plugin list --field=name | tr '\n' ',')',  -- plugins
            '',  -- pluginexcept
            '${WPOWNER}',  -- owner
            '2',  -- opcode cache type
            '0',  -- ramcache size
            '${GSERVER_VAULT}'  -- vault
        );"
    done
}

## create site's week dir
set_week_dir() {
    # get date "YYYYWW"
    local WEEK="$(date --utc "+%G%V")"
    # set WEEKDIR
    WEEKDIR="${GBACKUP_DIR}/${WEEK}"
    # exists?, if yes: insert into the `weeks` table
    mkdir -p "$WEEKDIR" 2> /dev/null \
        && squery "INSERT INTO weeks VALUES('${WEEK}', $(date --utc "+%s"));"

}

## params: $@
do_glacier() {
    #echo DBG: mtglacier --config "$GCONFIG" --vault "$GSERVER_VAULT" --journal "$GSERVER_JOURNAL" "$@"
    #mtglacier --dry-run --config "$GCONFIG" --vault "$GSERVER_VAULT" --journal "$GSERVER_JOURNAL" "$@"
    mtglacier --config "$GCONFIG" --vault "$GSERVER_VAULT" --journal "$GSERVER_JOURNAL" "$@" >> "$GLOG_FILE"
}

## sync
upload_backup() {
    do_glacier sync --dir "$GBACKUP_DIR" --new
}

## upload sqlite db
upload_sqlite() {
    do_glacier upload-file \
        --filename "$SITELIST_DB" \
        --set-rel-filename "$(basename "$SITELIST_DB")-$(date --utc "+${GFULLDATE}")" \
        || error_log "sqlite upload failure"
}

## delete one week from glacier
purge_old() {
    local OLDWEEKS

    for OLDWEEK in $(find "$GBACKUP_DIR" -mindepth 1 -maxdepth 1 -type d -mtime "+${GKEEPDAYS}" -printf "%f\n"); do
        OLDWEEKS+="--include '/${OLDWEEK}/**' "
        echo "[do-glacier] INFO scheduled to purge: ${OLDWEEK}"
    done
    [ -z "$OLDWEEKS" ] && return 0

    do_glacier sync --delete-removed \
        --dir "$GBACKUP_DIR" \
        $OLDWEEKS \
        --exclude "'**'" || error_log "purge failure: ${OLDWEEKS}"
}

## measure archive
get_week_backup_size() {
    du -sh "$WEEKDIR" >> "$GLOG_FILE"
}

## perform all backups
do_backup() {
    set_week_dir

    ## get sites from sqlite
    local NAMES="$(squery "SELECT name FROM sites WHERE vault!=''")"
    #check error & empty
    #move local-s here
    for NAME in ${NAMES}; do
        local ROW="$(squery "SELECT dbname,dbprefix,path FROM sites WHERE name='${NAME}'")"
        #check error & empty

        local DBNAME="$(cut -d'*' -f1 <<< "$ROW")"
        local DBPREFIX="$(cut -d'*' -f2 <<< "$ROW")"
        local WPROOT="$(cut -d'*' -f3 <<< "$ROW")"
        local ARCHIVETIME="$(date --utc "+${GFULLDATE}")"
        local SITEDB_ARCHIVE="${WEEKDIR}/${NAME}-db-${ARCHIVETIME}.tar.xz"
        local SITEFILES_ARCHIVE="${WEEKDIR}/${NAME}-files-${ARCHIVETIME}.tar.xz"

        # archive exist?
        if [ -f "$SITEDB_ARCHIVE" ] || [ -f "$SITEFILES_ARCHIVE" ]; then
            error_log "this site is already archived: ${NAME}"
            continue
        fi

        # list db tables
        local DBTABLES="$(echo "USE \`${DBNAME}\`; SHOW TABLES LIKE '${DBPREFIX}%';" | mysql -N)"
        # one .sql file per table
        for DBTABLE in ${DBTABLES}; do
            local DBTABLE_BACKUP="${WEEKDIR}/${NAME}-table_${DBTABLE}.sql"
            ionice -c3 nice mysqldump --events "$DBNAME" "$DBTABLE" > "$DBTABLE_BACKUP"
            [ -s "$DBTABLE_BACKUP" ] || error_log "table backup failure: ${NAME}, ${DBNAME}/${DBTABLE}"
        done

        # compress .sql files
        pushd "$WEEKDIR" > /dev/null
        ionice -c3 nice tar --create --xz --file "$SITEDB_ARCHIVE" "${NAME}-table_"*.sql \
            && rm "${WEEKDIR}/${NAME}-table_"*.sql \
            || error_log "database compression failure: ${NAME}"
        popd > /dev/null

        # archive WPROOT
        pushd "$WPROOT" > /dev/null
        ionice -c3 nice tar $WP_EXCLUDES --create --xz --file "$SITEFILES_ARCHIVE" * \
            || error_log "file archive failure: ${NAME}"
        popd > /dev/null

        # upload to glacier
        if upload_backup; then
            rm "$SITEDB_ARCHIVE" "$SITEFILES_ARCHIVE" || error_log "cleanup failure: ${NAME}"
        else
            error_log "backup upload failure: ${NAME}"
        fi
    done
}


check_configs

fillup_db;exit

do_backup
upload_sqlite
get_week_backup_size
purge_old

logger -t "do-glacier" "upload complete, see ${GLOG_FILE}"

[szepeviktor]

Thank you for helping!!

[vsespb]

ok, cool. I can add a link into see also https://github.com/vsespb/mt-aws-glacier#see-also after you release it as non-alfa and publish on github, if you want.