A database reflection daemon for MySql

db_reflect

#!/bin/bash

# Database Reflector
# Written by Nathan Fiscaletti
# Version 0.1.4
# Released 1512531374

# You can add more reflections here
# Use format s_user@s_host:s_pass:s_db>d_user@d_host:d_pass:d_db
#     Example: "root@127.0.0.1:pass:db_a>user@8.8.8.8:pass:db_b"
#     Will mirror 127.0.0.1.db_a to 8.8.8.8.db_b
DB_REFLECTIONS=(
    "root@192.168.1.218:password:test_db_1>root@192.168.1.218:password:test_db_2"
)

# Configuration
INTERVAL=120

# Initialize the daemon
function main() {
    log "[INIT] Starting Database Reflector"
    log "[INIT] ---------------------------"

    # Main function call
    reflect
}

# Starts a thread that will run once ever $INTERVAL seconds.
# On each iterration, it will run a database reflection
# for each of the reflection entries defined in $DB_REFLECTIONS.
function reflect() {
    while true; do
        for i in $(seq 0 $(expr ${#DB_REFLECTIONS[@]} - 1)); do 

            # Parse the reflection definition
            reflection="${DB_REFLECTIONS[$i]}"

            # Create to property arrays based on this reflection
            # db_s = source database
            # db_d = destination database
            # 0 = host, 1 = user, 2 = pass, 3 = db
            db_s=($(db_from_rd $(rd_from_delim_arr ${reflection} \> 0)))
            db_d=($(db_from_rd $(rd_from_delim_arr ${reflection} \> 1)))

            # Generate the optfiles for database connections.
            # This will eliminate the need to store credentials
            # in the command line history.
            tmp=($(create_tmp_opt_files ${db_s[2]} ${db_d[2]}))

            log "[REFL] ${db_s[0]}.${db_s[3]} -> ${db_d[0]}.${db_d[3]}"

            mysqldump \
            --defaults-extra-file=${tmp[0]} \
            ${db_s[3]} \
            -h ${db_s[0]} \
            -u ${db_s[1]} \
            | \
            mysql \
            --defaults-extra-file=${tmp[1]} \
            -h ${db_d[0]} \
            -u ${db_d[1]} \
            ${db_d[3]}

            # Clean up the optfiles for the database connections.
            rm_tmp_opt_files $tmp
        done

        log "[WAIT] Sleeping for ${INTERVAL}s"
        sleep $INTERVAL
    done;
}

# Creates temporary files using
# the supplied credentials.
# 1 = s_db_pass, 2 = d_db_pass
function create_tmp_opt_files() {
    DB_FROM_OPTFILE="$(mktemp)"
    chmod 0600 "$DB_FROM_OPTFILE"
    cat >"$DB_FROM_OPTFILE" <<EOF
[client]
password="${1}"
EOF
    chmod 0400 "$DB_FROM_OPTFILE"

    DB_TO_OPTFILE="$(mktemp)"
    chmod 0600 "$DB_TO_OPTFILE"
    cat >"$DB_TO_OPTFILE" <<EOF
[client]
password="${2}"
EOF
    chmod 0400 "$DB_TO_OPTFILE"

    echo "$DB_FROM_OPTFILE $DB_TO_OPTFILE"
}

# Removes temporary files
# 1 = opt files array
function rm_tmp_opt_files() {
    rm -f $1
    rm -f $2
}

# Splits a variable at a delim and
# returns the value at the 
# specified index.
# 1 = src, 2 = delim, 3 = index
function rd_from_delim_arr() {
    rd=(${1//$2/ })
    echo ${rd[$3]}
}

# Returns an internal array containing
# the host, user, pass and db from
# a reflection definition.
# Note: result must be encapsulated
#       in array markers ()
# 1 = rd
function db_from_rd()
{
    user=$( \
        rd_from_delim_arr $1 @ 0
    )
    
    host=$( \
        rd_from_delim_arr \
        $(rd_from_delim_arr $1 @ 1) : 0
    )

    pass=$( \
        rd_from_delim_arr \
        $(rd_from_delim_arr $1 @ 1) : 1
    )

    db=$( \
        rd_from_delim_arr \
        $(rd_from_delim_arr $1 @ 1) : 2
    )

    echo "$host $user $pass $db"
}

# Logs a message
# 1 = message
function log()
{
    echo "[`date '+%Y-%m-%d %H:%M:%S'`] $1"
}

# Initialize the main function
# after all function definitions.
main