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

# Dynamically generated constants

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

REPL_DB_USER_PWD="$(grep db_replication_pwd "$ALCASAR_PWD" | cut -d '=' -f 2-)"

readonly DB_ROOT_PWD;

readonly REPL_DB_USER=db_replication

readonly REPL_DB_USER_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"

	echo "Abort script with $error_code error code"

	# Revert FW

	tmp_disable_outbound_connection

	# Remove REPLICA

	del_remote_as_primary

	# Delete SSH tunnel service file

	service_file="replication-$remote_name.service"

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

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

	sed -i "/^REPLICATION_TO=/s/$ip:$port,//" /usr/local/etc/alcasar.conf

	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"

}

# Delete remote as primary

del_remote_as_primary() {

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

	exec_query "RESET REPLICA '$remote_name' ALL"

}

# 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 creating 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 primary ($remote_addr:$remote_ssh_port)"

	# Retrieve remote db_replication pwd

	if ! /usr/bin/scp -q -P "$remote_ssh_port" "$remote_ssh_user"@"$remote_addr":/home/replication/local-db_replication-pwd.txt /home/replication/primary-db_replication-pwd.txt

	then

		echo "error: cannot retrieve remote primary db_user pwd" >&2

		return 7

	fi

	remote_db_pwd=$(cat /home/replication/primary-db_replication-pwd.txt)

	rm /home/replication/primary-db_replication-pwd.txt

	echo "Successfully retrieve remote primary db_user pass"

	# 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

	echo "Successfully connected with '$remote_db_user' to remote secondary database on port $bind_port"

}

# Add a systemd unit to create SSH tunnel to remote primary

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"

}

# create, retrieve & import remote primary database

retrieve_primary_database() {

	# creation of a fresh dump

	if ! /usr/bin/ssh -q -p "$remote_ssh_port" "$remote_ssh_user"@"$remote_addr" -- "sudo /usr/local/bin/alcasar-mariadb.sh -d &&  cp -f /var/Save/base/\$(ls -t /var/Save/base/ | head -n 1) /home/replication/alcasar-users-database-primary.sql.gz"

	then

		echo "error: cannot create a fresh primary database dump" >&2

		return 18

	fi

	echo "Primary database dump created"

	if ! scp -q -P "$remote_ssh_port" "$remote_ssh_user"@"$remote_addr":alcasar-users-database-primary.sql.gz /tmp/

	then

		echo "error: cannot retrieve localy the fresh primary database dump" >&2

		return 19

	fi

	echo "Primary database dump locally copied"

	alcasar-mariadb.sh --import /tmp/alcasar-users-database-primary.sql.gz

	rm -f /tmp/alcasar-users-database-primary.sql.gz

	/usr/bin/ssh -q -p "$remote_ssh_port" "$remote_ssh_user"@"$remote_addr" -- "rm -f /home/replication/alcasar-users-database-primary.sql.gz"

}

add_symmetric_replication() {

#	hostname="$(/usr/local/bin/alcasar-replication-ssh-keys-management.sh --show-pubkey | cut -d' ' -f3 | cut -d'@' -f2 |cut -d'.' -f1)"

	active_db_port="$(/usr/local/bin/alcasar-replication-list.sh --all |grep Master_Port|cut -d" " -f2)"

	echo "adding data for symetrical replication (--bind-port=$active_db_port)"

	if ! /usr/bin/scp -q -P "$remote_ssh_port" /home/replication/local-db_replication-pwd.txt "$remote_ssh_user"@"$remote_addr":/home/replication/"$active_db_port"-db_replication-pwd.txt

	then

		echo "error: cannot copy local db_replication-pwd to primary" >&2

		return 7

	fi

#	if ! /usr/bin/ssh -p "$remote_ssh_port" "$remote_ssh_user"@"$remote_addr" -- "sudo /usr/local/bin/alcasar-replication-add.sh --to-secondary --name=$hostname --bind-port=$active_db_port --db-user=db_replication --db-password=`cat /home/replication/"$hostname"-"$active_db_port"-db_replication-pwd.txt`"

#	then

#		echo "error: cannot add symmetric replication" >&2

#		return 7

#	fi

#	echo "Successfully add symmetric replication"

}

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

		retrieve_primary_database || abort "$?" || exit

		add_remote_as_primary || abort "$?" || exit

		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

		add_symmetric_replication || abort "$?" || exit

		;;

	secondary)

		check_secondary_credentials || exit

		add_remote_as_primary || abort "$?" || exit # In a federation, primary/secondary is define by SSH role (sshd-server=primary; ssh-client=secondary)

		;;

esac

# Set Netfilter