Blame | Last modification | View Log
#!/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-)"
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() {
# All fields must be filled
if [ -z "$remote_name" ] ||
[ -z "$remote_addr" ] ||
[ -z "$remote_ssh_port" ] ||
[ -z "$remote_ssh_user" ]
then
echo "error: some args are missing" >&2
return 10
fi
# 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 was used testing 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
# Start replication
/usr/local/bin/alcasar-replication-start.sh --name="$remote_name"