summaryrefslogtreecommitdiff
path: root/replicate-db
blob: af4fc360f8e512182d23f982798e5fab5feea024 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
#!/bin/bash

set -e

if [ -d '/var/lib/mysql' ]; then
  read -p 'mariadb seems to be installed already - I will remove it first.' -r s
  if [ -n "${s}" ]; then
    echo 'Aborted.'
    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

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 '
  /^\s*\[mysqld]\s*$/ a group_concat_max_len = 4096 \
server-id = '"$RANDOM"' \
skip-networking
' '/etc/my.cnf.d/server.cnf'

sudo systemctl start mysqld
sudo systemctl enable mysqld

printf '\n\n\n%s\n%s\n\n\n\n\n' "${root_pw}" "${root_pw}" | \
  sudo mysql_secure_installation

# set up stunnel

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

ssh master@buildmaster.archlinux32.org '
  cd /data/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" \
    '127.0.0.1' \
    33061 \
    'replikat' \
    "${replikat_pw}"
  xzcat "${tmp_file}" | pv
  xzgrep -- '^-- CHANGE MASTER TO ' "${tmp_file}" | \
    sed 's/^-- //'
  printf 'START SLAVE;\n'
  printf 'SHOW SLAVE STATUS;\n'
} | \
  mysql -u root -p"${root_pw}"

printf '\n\nAll set up successfully.\n'