diff options
author | root <root@vps-18a7a2b7.vps.ovh.ca> | 2024-01-18 12:54:11 -0500 |
---|---|---|
committer | root <root@vps-18a7a2b7.vps.ovh.ca> | 2024-01-18 12:54:11 -0500 |
commit | e8ae75e07955d8940fa0a40115c2fb3566b94bc4 (patch) | |
tree | c8ef5bbc35cafd13ad2e0b6819c0554383c358f0 | |
parent | dfd1c30c59953145257ed4da956383d22fb58958 (diff) |
fixes, idempotence and output improvements
-rwxr-xr-x | wip/mariadb-push-replica.sh | 63 |
1 files changed, 53 insertions, 10 deletions
diff --git a/wip/mariadb-push-replica.sh b/wip/mariadb-push-replica.sh index 5e87358..c4df851 100755 --- a/wip/mariadb-push-replica.sh +++ b/wip/mariadb-push-replica.sh | |||
@@ -64,7 +64,7 @@ show_hostnames() | |||
64 | check_db() | 64 | check_db() |
65 | { | 65 | { |
66 | show_hostnames | 66 | show_hostnames |
67 | mariadb -t <<END | sed 's/^/ /' | 67 | mariadb --skip-reconnect -t <<END |
68 | select @@hostname, @@server_id, @@gtid_slave_pos, @@sql_log_bin; | 68 | select @@hostname, @@server_id, @@gtid_slave_pos, @@sql_log_bin; |
69 | END | 69 | END |
70 | } | 70 | } |
@@ -77,14 +77,15 @@ create_backup() | |||
77 | [ -e "$binlog_info_file" ] && return | 77 | [ -e "$binlog_info_file" ] && return |
78 | mkdir -p "$mariabackup_target_dir" | 78 | mkdir -p "$mariabackup_target_dir" |
79 | marialog=$(mktemp) | 79 | marialog=$(mktemp) |
80 | e=0 | ||
80 | (set -- \ | 81 | (set -- \ |
81 | -u root \ | 82 | -u root \ |
82 | --target-dir="$mariabackup_target_dir" | 83 | --target-dir="$mariabackup_target_dir" |
83 | exec >"$marialog" 2>&1 | 84 | exec >"$marialog" 2>&1 |
84 | set -x | 85 | set -x |
85 | mariabackup --backup --rsync "$@" | 86 | mariabackup --backup --rsync "$@" |
86 | mariabackup --prepare "$@") | 87 | mariabackup --prepare "$@") || e=$? |
87 | if [ -e "$binlog_info_file" ] | 88 | if [ "$e" = 0 -a -e "$binlog_info_file" ] |
88 | then | 89 | then |
89 | tail -v -n2 "$marialog" >&2 | 90 | tail -v -n2 "$marialog" >&2 |
90 | rm "$marialog" | 91 | rm "$marialog" |
@@ -107,24 +108,62 @@ send_backup() | |||
107 | rsync -zaR -- /./"${mariabackup_target_dir#/}" "$1":/ | 108 | rsync -zaR -- /./"${mariabackup_target_dir#/}" "$1":/ |
108 | } | 109 | } |
109 | 110 | ||
111 | restore_from_backup() | ||
112 | { | ||
113 | livedb=/var/lib/mysql | ||
114 | mariabackup_target_dir=/var/mariadb/backup | ||
115 | set -e | ||
116 | if [ -e "$livedb" ] | ||
117 | then | ||
118 | if [ "$(systemctl is-active mariadb)" = active ] | ||
119 | then | ||
120 | systemctl stop mariadb | ||
121 | fi | ||
122 | livedb_backup=$livedb~$(date -Ins) | ||
123 | mv -v -T -- "$livedb" "$livedb_backup" | ||
124 | mkdir "$livedb" | ||
125 | chown --reference="$livedb_backup" "$livedb" | ||
126 | chmod --reference="$livedb_backup" "$livedb" | ||
127 | fi | ||
128 | marialog=$(mktemp) | ||
129 | exec >"$marialog" 2>&1 | ||
130 | set -- \ | ||
131 | -u root \ | ||
132 | --target-dir="$mariabackup_target_dir" | ||
133 | if mariabackup --move-back "$@" | ||
134 | then | ||
135 | tail -v -n2 "$marialog" >&2 | ||
136 | # Can't believe mariabackup | ||
137 | # is so primitive as to | ||
138 | # recommend this chown in its | ||
139 | # documentation | ||
140 | chown -R mysql:mysql /var/lib/mysql | ||
141 | systemctl start mariadb | ||
142 | else | ||
143 | cat "$marialog" >&2 | ||
144 | fi | ||
145 | |||
146 | } | ||
147 | |||
110 | enable_replication() | 148 | enable_replication() |
111 | { | 149 | { |
112 | replica_host=$1 | 150 | primary_host=$1 |
113 | replication_user=$2 | 151 | replication_user=$2 |
114 | replication_password=$3 | 152 | replication_password=$3 |
115 | mariabackup_target_dir=/var/mariadb/backup | 153 | mariabackup_target_dir=/var/mariadb/backup |
116 | binlog_info_file=$mariabackup_target_dir/xtrabackup_binlog_info | 154 | binlog_info_file=$mariabackup_target_dir/xtrabackup_binlog_info |
117 | [ -e "$binlog_info_file" ] || return 0 | 155 | set -e |
118 | read master_log_file master_log_pos gtid_slave_pos < $binlog_info_file | 156 | read master_log_file master_log_pos gtid_slave_pos < $binlog_info_file |
119 | tee /dev/stderr <<END | mariadb -t | 157 | tee /dev/stderr <<END | mariadb --skip-reconnect -t |
120 | STOP SLAVE; | 158 | STOP SLAVE; |
121 | SET GLOBAL gtid_slave_pos = "$gtid_slave_pos"; | 159 | SET GLOBAL gtid_slave_pos = "$gtid_slave_pos"; |
122 | CHANGE MASTER TO | 160 | CHANGE MASTER TO |
123 | MASTER_HOST='$replica_host', | 161 | MASTER_HOST='$primary_host', |
124 | MASTER_USER='$replication_user', | 162 | MASTER_USER='$replication_user', |
125 | MASTER_PASSWORD='$replication_password', | 163 | MASTER_PASSWORD='$replication_password', |
126 | MASTER_USE_GTID=slave_pos; | 164 | MASTER_USE_GTID=slave_pos; |
127 | START SLAVE; | 165 | START SLAVE; |
166 | SHOW SLAVE STATUS\G | ||
128 | END | 167 | END |
129 | set -x | 168 | set -x |
130 | rm -r /var/mariadb/backup | 169 | rm -r /var/mariadb/backup |
@@ -132,7 +171,7 @@ END | |||
132 | 171 | ||
133 | create_replication_user() | 172 | create_replication_user() |
134 | { | 173 | { |
135 | mariadb -t <<END | 174 | mariadb --skip-reconnect -t <<END |
136 | CREATE OR REPLACE USER '$2'@'$1' IDENTIFIED BY '$3'; | 175 | CREATE OR REPLACE USER '$2'@'$1' IDENTIFIED BY '$3'; |
137 | GRANT REPLICATION SLAVE ON *.* TO '$2'@'$1'; | 176 | GRANT REPLICATION SLAVE ON *.* TO '$2'@'$1'; |
138 | END | 177 | END |
@@ -142,7 +181,7 @@ showvars() | |||
142 | { | 181 | { |
143 | for h in "$@" | 182 | for h in "$@" |
144 | do | 183 | do |
145 | run_$h mariadb -t <<END | 184 | run_$h mariadb --skip-reconnect -t <<END |
146 | select VARIABLE_NAME,SESSION_VALUE,GLOBAL_VALUE | 185 | select VARIABLE_NAME,SESSION_VALUE,GLOBAL_VALUE |
147 | from INFORMATION_SCHEMA.SYSTEM_VARIABLES | 186 | from INFORMATION_SCHEMA.SYSTEM_VARIABLES |
148 | where VARIABLE_NAME LIKE '%SLAVE%' \G | 187 | where VARIABLE_NAME LIKE '%SLAVE%' \G |
@@ -150,6 +189,7 @@ END | |||
150 | done | 189 | done |
151 | } | 190 | } |
152 | 191 | ||
192 | set -e | ||
153 | run_primary check_db | 193 | run_primary check_db |
154 | run_replica check_db | 194 | run_replica check_db |
155 | : showvars replica | 195 | : showvars replica |
@@ -161,8 +201,11 @@ run_primary create_replication_user \ | |||
161 | "$replication_password" | 201 | "$replication_password" |
162 | run_primary create_backup | 202 | run_primary create_backup |
163 | run_primary send_backup "$replica_host" | 203 | run_primary send_backup "$replica_host" |
204 | run_replica restore_from_backup | ||
164 | run_replica enable_replication \ | 205 | run_replica enable_replication \ |
165 | "$replica_host" \ | 206 | "$primary_host" \ |
166 | "$replication_user" \ | 207 | "$replication_user" \ |
167 | "$replication_password" | 208 | "$replication_password" |
209 | run_replica check_db | ||
168 | run_primary delete_backup | 210 | run_primary delete_backup |
211 | run_primary check_db | ||