Subversion Repositories ALCASAR

#!/bin/bash

#########################
## ALCASAR replication ##
##       install       ##
#########################
# The script is designed to setup replication between ALCASAR instances.

# Constants
readonly PASSWD_FILE="/root/ALCASAR-passwords.txt"
readonly REPL_USER="replication"
readonly REPL_DB_USER="$REPL_USER"
readonly REPL_PWD_LENGTH=16
readonly REPL_DB_PWD_LENGTH=16
readonly DB_CONF=/etc/my.cnf.d/server.cnf
readonly DB_REPL_CONF=/etc/my.cnf.d/replication.cnf
readonly EXPIRE_BINLOG_DAYS=31
readonly ALCASAR_CONF=/usr/local/etc/alcasar.conf

# Dynamically generated constants
DB_ROOT_PWD="$(grep db_root "$PASSWD_FILE" | cut -d '=' -f 2-)"
readonly DB_ROOT_PWD
REPL_PWD="$(tr -dc "a-zA-Z0-9" < /dev/random | head -c "$REPL_PWD_LENGTH")"
readonly REPL_PWD
REPL_DB_PWD="$(tr -dc "a-zA-Z0-9" < /dev/random | head -c "$REPL_DB_PWD_LENGTH")"
readonly REPL_DB_PWD
# SRV_ID must be < 2^32, see server_id variable of MariaDB
SRV_ID="$(cat /dev/urandom | tr -dc "0-9" | head -c 9)"
readonly SRV_ID


# Execute SQL queries on local server
exec_query() {
        if [ $# -ne 1 ]
        then
                echo "A SQL query must be given." >&2
                return 1
        fi

        # Execute the query
        /usr/bin/mariadb --user=root --password="$DB_ROOT_PWD" --execute="$1"
}

if grep -q "REPLICATION=on" "$ALCASAR_CONF"
then
        echo "error: replication is already installed" >&2
        exit 2
fi

# Save generated credentials for system user
echo "# Replication account for remote access" >> "$PASSWD_FILE"
echo "replication=$REPL_USER" >> "$PASSWD_FILE"
echo "replication_pwd=$REPL_PWD" >> "$PASSWD_FILE"
echo "Replication user: $REPL_USER"
echo "Replication password: $REPL_PWD"

# Save generated credentials for database user
echo "# Database replication account" >> "$PASSWD_FILE"
echo "db_replication=$REPL_DB_USER" >> "$PASSWD_FILE"
echo "db_replication_pwd=$REPL_DB_PWD" >> "$PASSWD_FILE"
echo "Database replication user: $REPL_DB_USER"
echo "Database replication password: $REPL_DB_PWD"

# Create local replication user
echo "Creating '$REPL_DB_USER' user on database..."
exec_query "CREATE USER '$REPL_DB_USER'@'%' IDENTIFIED BY '$REPL_DB_PWD'" || exit
echo "Granting '$REPL_DB_USER' user for replication..."
exec_query "GRANT REPLICATION REPLICA ON *.* TO '$REPL_DB_USER'@'%'" || exit
exec_query "FLUSH PRIVILEGES" || exit

# Remove forbidden characters for MariaDB
safe_hostname=$(echo "$HOSTNAME" | tr "[./]" '-')

# Enable binary logging
echo "Enabling binary logging..."
echo -n "[mariadb]
log-bin
server_id=$SRV_ID
report_host=$safe_hostname
log-basename=$safe_hostname
binlog-format=mixed
expire_logs_days=$EXPIRE_BINLOG_DAYS
master_retry_count=0
log_slave_updates=1
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
" > "$DB_REPL_CONF"

# Listen on localhost
sed -i "s?^.*skip-networking.*?#skip-networking?" "$DB_CONF"
sed -i "s?^#bind-address.*?bind-address=127.0.0.1?" "$DB_CONF"

# Apply binary logging
echo "Restarting MariaDB..."
/usr/bin/systemctl restart mariadb.service

# User for SSH tunneling
echo "Creating replication user..."
/usr/sbin/useradd -s /bin/sh -m "$REPL_USER"
echo -e "$REPL_PWD\n$REPL_PWD" | passwd "$REPL_USER"
mkdir "/home/$REPL_USER/.ssh"
touch "/home/$REPL_USER/.ssh/authorized_keys"
chown replication:replication -R "/home/$REPL_USER/.ssh"

# Generate user SSH key
if ! ls ~/.ssh/id_* &> /dev/null
then
        echo "Generating SSH key..."
        mkdir ~/.ssh
        /usr/bin/ssh-keygen -t rsa -b 4096 -N "" -f ~/.ssh/id_rsa
fi

echo "Setting replication state to 'on'..."
sed -i "/^REPLICATION=/s/off/on/" "$ALCASAR_CONF"

echo "Database replication succesfully installed."