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="db_replication"

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

# 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 database 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

report_host=$safe_hostname

log-basename=$safe_hostname

binlog-format=mixed

expire_logs_days=$EXPIRE_BINLOG_DAYS

master_retry_count=0

log_slave_updates=0

binlog-ignore-db=information_schema

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

binlog-ignore-db=gammu

replicate-ignore-table = radius.radacct

replicate-ignore-table = radius.totacct

replicate-ignore-table = radius.mtotacct

" > "$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

echo "initializing binary logging"

exec_query "RESET MASTER" || exit

# 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"

echo $REPL_DB_PWD > /home/$REPL_USER/db_replication_user_pass.txt

chown $REPL_USER:$REPL_USER /home/$REPL_USER/db_replication_user_pass.txt; chmod 400 /home/$REPL_USER/db_replication_user_pass.txt

# 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."