path: root/mariadb/mariadb-push-databases-to-empty-replica

#!/bin/bash
set -e
source rpc.bash

datadir=/etc/hosting-tools
default_replica_host_file=$datadir/default-replica-host
replication_password_file=$datadir/replication-password

if [ -r "$default_replica_host_file" ]
then
	read default_replica_host < "$default_replica_host_file"
fi

gen_password()
{
	generated_password_length=32
	generated_password=$(tr -cd a-zA-Z0-9 < /dev/urandom |
		head -c "$generated_password_length")
	[ "${#generated_password}" -eq "$generated_password_length" ]
	printf '%s\n' "$generated_password"
}

if ! [ -e "$replication_password_file" ]
then
	gen_password > "$replication_password_file"
fi

if [ -r "$replication_password_file" ]
then
	read replication_password < "$replication_password_file" 
	[ "${#replication_password}" -ge 30 ]
fi

primary_host=$(hostname --fqdn)
if [ $# = 0 ]
then
	replica_host=$default_replica_host
else
	replica_host=${1:-$default_replica_host}
	shift
fi
replication_user=replication

without_tty_input()
{
	if [ -t 0 ]
	then
		"$@" < /dev/null
	else
		"$@"
	fi
}

run_()
{
	case "$1" in
		primary_host | replica_host ) ;;
		* ) return 58 ;;
	esac
	(set -x
	: ${1%_host} : $2 $3 ${4:+ ...})
	BASH_RPC_REMOTE_DEST=${!1} \
		without_tty_input \
		remote_run_function "${@:2}"
}

run_primary()
{
	run_ primary_host "$@"
}

run_replica()
{
	run_ replica_host "$@"
}

show_hostnames()
{
	printf \
		"==> %s %s <==\n    %s\n" \
		"$(hostname -A)" \
		"$(hostname -I)" \
		"$(uptime)"
}

stop_mariadb_server_and_remove_database_files()
{
	livedb=/var/lib/mysql
	set -e
	set -o pipefail
	[ -e "$livedb" ] || return 0
	if [ "$(systemctl is-active mariadb)" = active ]
	then
		systemctl stop mariadb
	fi
	livedb_backup=$livedb~$(date -Ins)
	mv -v -T -- "$livedb" "$livedb_backup"
	mkdir "$livedb"
	chown --reference="$livedb_backup" "$livedb"
	chmod --reference="$livedb_backup" "$livedb"
}

silent_unless_error()
{
	set -- "$(mktemp)" "$@"
	if
		"${@:2}" >"$1" 2>&1
	then
		local r=0
	else
		local r=$?
		cat "$1" >&2
	fi
	rm "$1"
	return $r
}

mostly_silent_unless_error()
{
	echo "+ $*" >&2
	set -- "$(mktemp)" "$@"
	if
		"${@:2}" >"$1" 2>&1
	then
		tail -n3 "$1" >&2
		local r=0
	else
		local r=$?
		cat "$1" >&2
	fi
	rm "$1"
	return $r
}

mariadb_install_replication_credentials()
{
	set -e
	mariadb -v --skip-reconnect -t <<.
stop slave
;
change master
to
	master_host     = '$1'
,	master_user     = '$2'
,	master_password = '$3'
,	master_ssl = 1
,	master_ssl_verify_server_cert = 1
;
.
}

create_and_authorize_replication_user()
{
	mariadb --skip-reconnect -t <<.
create or replace
user
	'$2'@'$1'
identified
by
	'$3'
;
grant
	replication replica
on
	*.*
to
	'$2'@'$1'
;
select
	@@hostname
	as
		'primary host'
,	concat (user, '@', host)
	as
		login
,	repl_slave_priv
	as
		'replica privilege'
from
	mysql.user
where
	repl_slave_priv = 'Y'
;
.
}

showvars()
{
	mariadb --skip-reconnect -t <<.
select
	variable_name
,	session_value
,	global_value
from
	information_schema.system_variables
where
	variable_name like '%slave%state%'
\G
.
}

check_input()
{
	[ "$primary_host" ]
	[ "$replica_host" ]
	[ "$replication_user" ]
	[ "$replication_password" ]
	dns_check_servers
}

dns_check_servers()
{
	primary_ipv4=$(dig +short -ta "$primary_host")
	replica_ipv4=$(dig +short -ta "$replica_host")
	echo "primary: $primary_host $primary_ipv4"
	echo "replica: $replica_host $replica_ipv4"
}

truncated_machineid_decimal_string_int32()
{
	systemd-id128 machine-id |
	sha256sum |
	( read -n8 &&
	printf '%u\n' 0x"$REPLY" )
}

run_both()
{
	set -e
	r=0
	run_primary "$@" || r=$?
	run_replica "$@"
	return $r
}

set_server_id()
{
	set -e
	chosen_id=$(truncated_machineid_decimal_string_int32)
	[ "$chosen_id" -gt 1 ]
	mariadb -v --skip-reconnect -t <<.
set global
	server_id = $chosen_id
;
.
}

mariadb_enable_semi_sync()
{
	set -e
	[ "$1" = off ] || set -- on
	mariadb -v --skip-reconnect -t <<.
stop
	slave io_thread
;
set global
	rpl_semi_sync_master_enabled = $1
;
set global
	rpl_semi_sync_slave_enabled = $1
;
.
	mariadb_show_vars_like 'rpl_%'
}

mariadb_show_vars_like()
{
	set -e
	mariadb -v --skip-reconnect -t <<.
select
	@@hostname
,	@@server_id
;
use
	information_schema
;
select
	variable_name
,	variable_value
from
	global_variables
where
	variable_name like '$1'
;
.
}

mariadb_list_databases()
{
	show_all_databases >&2
	mariadb --skip-reconnect -B -s <<.
select
	schema_name
from
	information_schema.schemata
;
.
}

printlines()
{
	printf '%s\n' "$@"
}

printarray()
{
	declare -n _PRINTARRAY_VARNAME="$1"
	printf '%s\n' "${_PRINTARRAY_VARNAME[@]}"
}

# Call run_replica from here to avoid
# piping the database back to caller
# unnecessarily.  Better would be
# a direct connect from mariadb client
# to remote mariadb server; but that
# requires transmitting credentials.
# Credential-transporter.  Transporter-transporter.
# Well, a transporter protein is more like a provenance tag,
# and the transporter code is the receptor to the transporter
# which is the provenance checker.  But anyway we have that
# with ssh, except we don't: we are assuming the primary
# has the ssh root of the replica!  Insanity!  Now this only works
# because the code runs on the primary; unless we forward the
# ssh auth with the ssh agent; but that only makes the security
# flaw temporary not solved; in fact, the replica should receive
# the transmission on some limited authorization channel; which
# _could_ be ssh; in fact, the dump could transparently be either
# live or else cached on the server side; it could be the
# rsync.net backup even; but ... it needs to include
# the btrfs snap similarly ... .
send_mariadb_dump()
{
	(
		set -x
		mariadb-dump "${@:2}"
	) |
	BASH_RPC_REMOTE_DEST=$1 \
		remote_run_function \
		receive_mariadb_dump
}

receive_mariadb_dump()
{
	pv -f |
	tee /var/cache/mariadb-dump.sql |
	mariadb --skip-reconnect
}

save_array()
{
	declare -n _to_save="$1"
	case "$2 $3" in
		'from zfile' )
			mapfile -d '' -t _to_save < "$4"
			;;
		'from lines' )
			mapfile -t _to_save < "$4"
			;;
		* )
			false
			;;
	esac
}

mariadb_scan_databases()
{
	set -- "$(mktemp)"
	declare -g -a primary_dbs
	save_array primary_dbs from lines <(
		run_primary mariadb_list_databases 2>"$1" |
		sort -u
	)

	declare -g -a replica_dbs
	save_array replica_dbs from lines <(
		run_replica mariadb_list_databases 2>>"$1" |
		sort -u
	)

	declare -g -a primary_dbs_not_on_replica
	save_array primary_dbs_not_on_replica from zfile <(
		subtract_arrays primary_dbs replica_dbs
	)
	if [ ${#primary_dbs_not_on_replica[@]} -gt 0 ]
	then
		cat "$1" >&2
	fi
	rm -- "$1"
}

showmissing()
{
	if [ ${#primary_dbs_not_on_replica[@]} -gt 0 ]
	then
		printf "Missing on ${replica_host}: %s\n" \
			"${primary_dbs_not_on_replica[@]}" >&2
	fi
}

choose_mariadbdump_target_databases()
{
	declare -n _target_db_array="$1"
	shift
	if [ $# = 0 ]
	then
		showmissing

		if [ "$SEND_ALL_MARIADB_DATABASES" ]
		then
			_target_db_array=("${primary_dbs_not_on_replica[@]}")
		else
			_target_db_array=()
		fi
	else
		save_array _target_db_array from lines \
			<(intersection_lines \
				<(printarray primary_dbs_not_on_replica) \
				<(printlines "$@" | sort -u))
	fi
}

intersection_lines()
{
	comm -12 -- "$1" "$2"
}

subtract_arrays()
{
	declare -n _a="$1" _b="$2"
	comm -z -23 -- \
		<(printf '%s\0' "${_a[@]}") \
		<(printf '%s\0' "${_b[@]}")
}

main()
{
	set -e
	check_input

	: run_both set_server_id
	: run_both mariadb_enable_semi_sync off
	run_primary create_and_authorize_replication_user \
		"$replica_host" \
		"$replication_user" \
		"$replication_password"

	mariadb_scan_databases
	choose_mariadbdump_target_databases databases "$@"
	if [ ${#databases[@]} -gt 0 ]
	then
		run_replica \
			mariadb_install_replication_credentials \
			"$primary_host" \
			"$replication_user" \
			"$replication_password"
		run_primary \
			send_mariadb_dump \
			"$replica_host" \
			--master-data \
			--apply-slave-statements \
			--gtid \
			--single-transaction \
			--databases "${databases[@]}"
		run_replica \
			show_all_databases
	fi
	if gtid=$(run_primary mariadb_get_primary_gtid) &&
	[ "$gtid" ]
	then
		run_replica \
			mariadb_wait_on_gtid "$gtid"
	fi
}

mariadb_get_primary_gtid()
{
	mariadb --skip-reconnect -B -s <<.
select
	@@gtid_binlog_pos;
.
}

mariadb_wait_on_gtid()
{
	local gtid="$1"
	mariadb --skip-reconnect -t <<.
select
	@@hostname
,	@@gtid_slave_pos as 'replica gtid'
,	'$gtid' as 'primary gtid'
;
stop
	slave io_thread
;
start
	slave io_thread
;
.
	echo "trying primary_gtid_wait()... " >&2
	mariadb -t --skip-reconnect <<.
select
	master_gtid_wait('$gtid')
	as \`primary_gtid_wait('$gtid')\`
;
.
}

cleanup_after_test()
{
	run_primary delete_backup || true
}

show_all_databases()
{
mariadb --skip-reconnect -t "$@" <<.
select
	@@hostname
,	@@server_id
,	count(schema_name) as 'databases'
,	user() as 'login'
,	@@gtid_slave_pos as 'replica gtid'
,	@@gtid_binlog_pos as 'primary gtid'
from
	information_schema.schemata
\G
select
	schema_name as 'database name'
from
	information_schema.schemata
;
.
}

if false
then
	cleanup_after_test
fi
SEND_ALL_MARIADB_DATABASES=y
main "$@"
exit $?