Send patches - preferably formatted by git format-patch - to patches at archlinux32 dot org.
summaryrefslogtreecommitdiff
path: root/replicate-db
diff options
context:
space:
mode:
authorErich Eckner <git@eckner.net>2018-06-22 11:16:28 +0200
committerErich Eckner <git@eckner.net>2018-06-22 11:16:28 +0200
commit49f44a3fce2aec73da710a9e1ef3148c83100652 (patch)
tree8f76d1bdf5d274fa1cf7ead2fbbdca2ac0f2f168 /replicate-db
parent13e5347fd5b337769dcbd8224758038a88963349 (diff)
replicate-db: use stunnel
Diffstat (limited to 'replicate-db')
-rwxr-xr-xreplicate-db88
1 files changed, 55 insertions, 33 deletions
diff --git a/replicate-db b/replicate-db
index e7699b8..9154d64 100755
--- a/replicate-db
+++ b/replicate-db
@@ -9,11 +9,31 @@ if [ -d '/var/lib/mysql' ]; then
exit
fi
sudo systemctl stop mysqld || true
+ sudo systemctl disable mysqld || true
sudo pacman -Rs mariadb || true
sudo rm -rf --one-file-system '/var/lib/mysql' '/etc/mysql'
fi
-sudo pacman -S --noconfirm mariadb
+if [ -d '/etc/stunnel' ]; then
+ read -p 'stunnel seems to be installed already - I will remove it first.' -r s
+ if [ -n "${s}" ]; then
+ echo 'Aborted.'
+ exit
+ fi
+ sudo systemctl stop stunnel || true
+ sudo systemctl disable stunnel || true
+ sudo pacman -Rs stunnel || true
+ sudo rm -rf --one-file-system '/etc/stunnel'
+fi
+
+read -p 'enter new root-pw: ' -s -r root_pw
+printf '\n'
+read -p 'enter replikat-pw: ' -s -r replikat_pw
+printf '\n'
+
+sudo pacman -S --noconfirm mariadb stunnel
+
+# set up mariadb
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
sudo sed -i '
/myisam_sort_buffer_size/ a group_concat_max_len = 4096
@@ -22,50 +42,50 @@ sudo sed -i '
b
}
s/^server-id/#\0/
+ /^#skip-networking$/ s/^#//
' '/etc/mysql/my.cnf'
+
sudo systemctl start mysqld
+sudo systemctl enable mysqld
-read -p 'enter our new root-pw: ' -s -r our_root_pw
-printf '\n'
-read -p 'enter remote root-pw: ' -s -r remote_root_pw
-printf '\n'
+printf '\n\n%s\n%s\n\n\n\n\n' "${root_pw}" "${root_pw}" | \
+ sudo mysql_secure_installation
-replikat_pw=$(
- head -c12 /dev/urandom | \
- base64 -w0
-)
+# set up stunnel
-printf '\n\n%s\n%s\n\n\n\n\n' "${our_root_pw}" "${our_root_pw}" | \
- sudo mysql_secure_installation
+sudo tee /etc/stunnel/stunnel.conf > /dev/null <<EOF
+[buildmaster-mysql]
+client = yes
+accept = 127.0.0.1:33061
+connect = buildmaster.archlinux32.org:3307
+verifyChain = yes
+CApath = /etc/ssl/certs
+checkHost = buildmaster.archlinux32.org
+EOF
+
+sudo systemctl start stunnel
+sudo systemctl enable stunnel
tmp_file=$(mktemp)
trap 'rm "${tmp_file}"' EXIT
-printf '%s\n' "${remote_root_pw}" | \
- ssh buildmaster '
- echo "logged in" >&2;
- mysql buildmaster -u root -p -e "'"$(
- printf "DROP USER IF EXISTS 'replikat'@'%s';\n" \
- "$(hostname)"
- printf "CREATE USER 'replikat'@'%s' IDENTIFIED BY '%s';\n" \
- "$(hostname)" \
- "${replikat_pw}"
- printf "GRANT REPLICATION SLAVE ON *.* TO 'replikat'@'%s';\n" \
- "$(hostname)"
- printf 'FLUSH PRIVILEGES;\n'
- printf 'QUIT\n'
- )"'" >&2;
- echo "created user" >&2;
- cd /var/backup;
- ls -t | grep -m1 '"'"'^database-.*\.xz$'"'"' | xargs pv
- echo "copied dump" >&2;
- ' > "${tmp_file}"
+ssh buildmaster '
+ cd /var/backup;
+ ls -t | grep -m1 '"'"'^database-.*\.xz$'"'"' | xargs pv
+' > "${tmp_file}"
{
printf 'SHOW SLAVE STATUS;\n'
+ printf "CREATE USER 'webserver'@'localhost' IDENTIFIED BY 'empty';\n" \
+ printf "GRANT USAGE ON *.* TO 'webserver'@'localhost' IDENTIFIED BY 'empty';\n"
+ printf "GRANT REPLICATION CLIENT ON *.* TO 'webserver'@'localhost';\n"
+ printf 'GRANT %s ON buildmaster.* TO '"'"'webserver'"'"'@'"'"'localhost'"'"';\n' \
+ 'CREATE TEMPORARY TABLES' \
+ 'SELECT' \
+ 'SHOW VIEW'
printf "CHANGE MASTER TO MASTER_HOST='%s', MASTER_PORT=%s, MASTER_USER='%s', MASTER_PASSWORD='%s';\n" \
- 'buildmaster.archlinux32.org' \
- 3306 \
+ '127.0.0.1' \
+ 33061 \
'replikat' \
"${replikat_pw}"
xzcat "${tmp_file}" | pv
@@ -74,4 +94,6 @@ printf '%s\n' "${remote_root_pw}" | \
printf 'START SLAVE;\n'
printf 'SHOW SLAVE STATUS;\n'
} | \
- mysql -u root -p"${our_root_pw}"
+ mysql -u root -p"${root_pw}"
+
+printf '\n\nAll set up successfully.\n'