Subversion Repositories ALCASAR

#!/bin/bash

#########################

## ALCASAR replication ##

##       connect       ##

#########################

# The script is designed to connect instance to a remote ALCASAR.

# Constants

readonly ALCASAR_PWD="/root/ALCASAR-passwords.txt"

readonly LOCALHOST="127.0.0.1"

readonly DB_PORT=3306

readonly ALCASAR_CONF=/usr/local/etc/alcasar.conf

# Dynamically generated constants

DB_ROOT_PWD="$(grep db_root "$ALCASAR_PWD" | cut -d '=' -f 2-)"

readonly DB_ROOT_PWD;

# Variables

remote_name=""

remote_addr=""

remote_ssh_port=""

remote_ssh_user=""

remote_db_user=""

remote_db_pwd=""

remote_role=""

bind_port=""

# Revert modifications already made while adding remote

# $1: previous error code

abort() {

	error_code="$1"

	# Revert FW

	tmp_disable_outbound_connection

	# Delete SSH tunnel service file

	service_file="replication-$remote_name.service"

	service_path="/etc/systemd/system/$service_file"

	[ -f "$service_file" ] && rm "$service_file"

	return "$error_code"

}

# Add remote as primary

add_remote_as_primary() {

	echo "Adding '$remote_name' as primary..."

	exec_query "CHANGE MASTER '$remote_name' TO MASTER_HOST='$LOCALHOST', MASTER_PORT=$bind_port, MASTER_USER='$remote_db_user', MASTER_PASSWORD='$remote_db_pwd', MASTER_USE_GTID=replica_pos"

}

# Verify hostname and IP are not already used by other primary servers

check_availability() {

	attributes="$(/usr/local/bin/alcasar-replication-list.sh --all)"

	# Check for remote name availability

	echo "$attributes" | grep -q "$remote_name"

	if [ "$?" -eq 0 ]

	then

		echo "error: name '$remote_name' already used" >&2

		return 15

	fi

	# Check for remote IP availability

	echo "$attributes" | grep -q "$remote_addr"

	if [ "$?" -eq 0 ] && [ -n "$remote_addr" ]

	then

		echo "error: address '$remote_addr' already used" >&2

		return 16

	fi

	# Check for binding port availability

	echo "$attributes" | grep -q "$bind_port"

	if [ "$?" -eq 0 ] && [ -n "$bind_port" ]

	then

		echo "error: binding port '$bind_port' already used" >&2

		return 17

	fi

}

# Check script args

# $@: script args

check_args() {

	# Parse args

	args="$(getopt --longoptions "to-primary,to-secondary,name:,address:,port:,user:,db-user:,db-password:,bind-port:,help" --options "n:,a:,p:,u:,h" -- "$@")"

	# Reset script args list

	eval set -- "$args"

	# Print help

	if [ "$#" -eq 1 ]

	then

		usage

		return 1

	fi

	# Loop over all args

	while true

	do

		case "$1" in

			--to-primary)

				echo "Remote role: primary"

				remote_role="primary"

				;;

			--to-secondary)

				echo "Remote role: secondary"

				remote_role="secondary"

				;;

			--name | -n)

				echo "Remote name: $2"

				remote_name="$2"

				shift

				;;

			--address | -a)

				echo "Remote address: $2"

				remote_addr="$2"

				shift

				;;

			--port | -p)

				echo "Remote SSH port: $2"

				remote_ssh_port="$2"

				shift

				;;

			--user | -u)

				echo "Remote user: $2"

				remote_ssh_user="$2"

				shift

				;;

			--db-user)

				echo "Remote database user: $2"

				remote_db_user="$2"

				shift

				;;

			--db-password)

				echo "Remote database user password: $2"

				remote_db_pwd="$2"

				shift

				;;

			--bind-port)

				echo "Local binding port: $2"

				bind_port="$2"

				shift

				;;

			--help | -h)

				usage

				return 2

				;;

			--)

				# End of args

				break

				;;

			*)

				echo "error: unknown $1" >&2

				return 3

				break

				;;

		esac

		shift

	done

	# All fields must be filled

	case "$remote_role" in

		primary)

			# Needed args to be passed

			if [ -z "$remote_name"     ] ||

			   [ -z "$remote_addr"     ] ||

			   [ -z "$remote_ssh_port" ] ||

			   [ -z "$remote_ssh_user" ] ||

			   [ -z "$remote_db_user"  ] ||

			   [ -z "$remote_db_pwd"   ]

			then

				echo "error: some args are missing" >&2

				return 4

			fi

			;;

		secondary)

			# Needed args to be passed

			if [ -z "$remote_name"     ] ||

			   [ -z "$bind_port"       ] ||

			   [ -z "$remote_db_user"  ] ||

			   [ -z "$remote_db_pwd"   ]

			then

				echo "error: some args are missing" >&2

				return 5

			fi

			;;

		*)

			echo "error: remote role is missing" >&2

			return 6

			;;

	esac

}

# Test connection to remote system and remote database

# before making SSH tunnel.

check_primary_credentials() {

	# Test SSH credentials

	if ! /usr/bin/ssh -o StrictHostKeyChecking=no -o PasswordAuthentication=no -p "$remote_ssh_port" "$remote_ssh_user"@"$remote_addr" exit

	then

		echo "error: cannot SSH with '$remote_ssh_user' to $remote_addr:$remote_ssh_port" >&2

		echo "hint: have you deployed root pubkey on the remote?"

		return 7

	fi

	echo "Successfully connected with '$remote_ssh_user' to $remote_addr:$remote_ssh_port"

	# Test database credentials

	if ! /usr/bin/ssh -q -p "$remote_ssh_port" "$remote_ssh_user"@"$remote_addr" -- /usr/bin/mariadb --user="$remote_db_user" --password="$remote_db_pwd" --execute="QUIT"

	then

		echo "error: cannot connect with '$remote_db_user' to remote database" >&2

		return 8

	fi

	echo "Successfully connected with '$remote_db_user' to remote database"

}

# Test connection to remote database through SSH tunnel

check_secondary_credentials() {

	if ! /usr/bin/mariadb --host="$LOCALHOST" --port="$bind_port" --user="$remote_db_user" --password="$remote_db_pwd" --execute="QUIT"

	then

		echo "error: cannot connect with '$remote_db_user' to remote database" >&2

		return 9

	fi

}

# Make a SSH tunnel to remote host

create_ssh_tunnel() {

	# Find a common binding port

	find_common_free_port || return 11

	service_file="replication-$remote_name.service"

	service_path="/etc/systemd/system/$service_file"

	# Write down SSH tunnel service file

	echo "[Unit]

Description=Setup a secure bidirectional tunnel with $remote_name

After=network.target

[Service]

ExecStart=/usr/bin/ssh -NT -4 -o ServerAliveInterval=60 -o ExitOnForwardFailure=yes -p $remote_ssh_port -L $bind_port:localhost:$DB_PORT -R $bind_port:localhost:$DB_PORT $remote_ssh_user@$remote_addr

RestartSec=5

Restart=always

[Install]

WantedBy=multi-user.target

" > "$service_path"

	# Start and enable SSH tunnel

	echo "Enabling $remote_name service..."

	/usr/bin/systemctl enable "$service_file"

	echo "Starting $remote_name service..."

	/usr/bin/systemctl start "$service_file"

}

# Execute SQL query

# $1: query

# $2: user (default: root)

# $3: password (default: root pwd)

# $4: host (default: localhost)

# $5: port (default: 3306)

exec_query() {

	# Check args

	if [ $# -lt 1 ]

	then

		echo "usage: $0 \"SQL query\" <DB user> <DB password> <SQL server address> <SQL server port>"

		return 12

	fi

	# Execute the query

	/usr/bin/mariadb --host="${4:-localhost}" --port="${5:-$DB_PORT}" --user="${2:-root}" --password="${3:-$DB_ROOT_PWD}" --execute="$1"

}

find_common_free_port() {

	remote_busy_ports_file=/tmp/remote_busy_ports

	local_busy_ports_file=/tmp/local_busy_ports

	common_busy_ports_file=/tmp/common_busy_ports

	ports_list_file=/tmp/ports_list

	free_ports_file=/tmp/free_ports

	# Get remote busy ports

	/usr/bin/ssh -q -p "$remote_ssh_port" "$remote_ssh_user"@"$remote_addr" -- /usr/sbin/ss --listening --numeric --ipv4 | tail -n +2 | cut -d ':' -f 2 | cut -d ' ' -f 1 | sort -u > "$remote_busy_ports_file"

	if [ "$?" -ne 0 ]

	then

		echo "error: cannot SSH with '$remote_ssh_user' to $remote_addr:$remote_ssh_port" >&2

		return 13

	fi

	# Get local busy ports

	/usr/sbin/ss --listening --numeric --ipv4 | tail -n +2 | cut -d ':' -f 2 | cut -d ' ' -f 1 | sort -u > "$local_busy_ports_file"

	# List ports range from system

	read lower_port upper_port < /proc/sys/net/ipv4/ip_local_port_range

	# Write ports in a file

	echo -n > "$ports_list_file"

	for port in $(seq "$lower_port" "$upper_port")

	do

		echo "$port" >> "$ports_list_file"

	done

	# Merge busy ports

	/usr/bin/cat "$remote_busy_ports_file" "$local_busy_ports_file" > "$common_busy_ports_file"

	# Sorts ports

	/usr/bin/sort -u -o "$common_busy_ports_file" "$common_busy_ports_file"

	/usr/bin/sort -o "$ports_list_file" "$ports_list_file"

	# Substract available ports in common

	/usr/bin/comm --check-order -3 "$ports_list_file" "$common_busy_ports_file" | cut -f 1 | sed "/^$/d" > "$free_ports_file"

	# Verify at least one free port have been found

	if [ ! -s "$free_ports_file" ]

	then

		echo "error: no common port found for binding" >&2

		return 14

	fi

	# Pick the first common port

	bind_port="$(head -n 1 "$free_ports_file")"

	echo "Both machines binded on port $bind_port->$DB_PORT"

	echo "Please take note about the binding port for primary's connection setup."

	# Remove tmp files

	rm "$remote_busy_ports_file"

	rm "$local_busy_ports_file"

	rm "$common_busy_ports_file"

	rm "$ports_list_file"

	rm "$free_ports_file"

}

# Allow outbound connection for testing connection

tmp_allow_outbound_connection() {

	/usr/sbin/iptables -A OUTPUT -d "$remote_addr" -p tcp --dport "$remote_ssh_port" -j ACCEPT

}

# Disable outbound connection which that was used to test test connection

tmp_disable_outbound_connection() {

	/usr/sbin/iptables -D OUTPUT -d "$remote_addr" -p tcp --dport "$remote_ssh_port" -j ACCEPT

}

# Print help message

usage() {

	echo "usage: $0 ROLE OPTIONS"

	echo

	echo "ROLE"

	echo "	--to-primary"

	echo "		remote server is a primary"

	echo "	--to-secondary"

	echo "		remote server is a secondary"

	echo

	echo "OPTIONS"

	echo "	--name=NAME, -n NAME"

	echo "		friendly name given to the remote"

	echo "	--address=ADDRESS, -a ADDRESS"

	echo "		remote IP address"

	echo "	--port=PORT, -p PORT"

	echo "		remote SSH port"

	echo "	--user=USER, -u USER"

	echo "		remote SSH user"

	echo "	--db-user=USER"

	echo "		remote database replication user"

	echo "	--db-password=PASSWORD"

	echo "		remote database replication user password"

	echo "	--bind-port=PORT"

	echo "		used from primary: local port binded to remote database. It has been displayed during secondary connection to primary"

	echo "	--help, -h"

	echo "		print this help message"

	echo

	echo "ROLE OPTIONS"

	echo "	--to-primary: needs name, address, port, user, db-user, db-password"

	echo "	--to-secondary: needs name, bind-port, db-user, db-password"

}

# Main

check_args "$@" || exit

check_availability || exit

case "$remote_role" in

	primary)

		tmp_allow_outbound_connection || abort "$?" || exit

		check_primary_credentials || abort "$?" || exit

		create_ssh_tunnel || abort "$?" || exit

		;;

	secondary)

		check_secondary_credentials || exit

		;;

esac

# Set remote as master

add_remote_as_primary || abort "$?" || exit

# Set Netfilter

echo -n "Allowing outbound connection to remote SSH "

# Get remote IP and port from its name

port="$(grep "ExecStart" "$service_path" | cut -d ' ' -f 9)"

ip="$(grep "ExecStart" "$service_path" | cut -d ' ' -f 14 | cut -d '@' -f2)"

echo "($ip:$port)"

/usr/bin/sed -i -E "/^REPLICATION_TO=/s/=(.*)/=\1$ip:$port,/" /usr/local/etc/alcasar.conf

/usr/local/bin/alcasar-iptables.sh

# Start replication

# /usr/local/bin/alcasar-replication-start.sh --name="$remote_name"