blob: 68ddaa9e1a360eafe1c039a0801ae83624087aa4 (
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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
# GN2 Time Machines
GN1 time machines are pretty straightforward. With GN2 the complexity has increased a lot because of interacting services and a larger dependency graph.
Here I track what it takes today to install an instance of GN2 that is 'frozen' in time.
- [X] Install Mariadb and recover production DB (est. 3-4 hrs)
## Tags
* assigned: pjotrp
* status: in progress
* priority: medium
* type: system administration
* keywords: systems, production
## Tasks
General time line:
* [X] Install machine software and physical (4 hours)
* [X] Sync backups on a daily basis and add monitoring (2 hours)
* [X] Set up Mariadb and sync from backup (4 hours)
* [ ] GN2 production environment
* [ ] GN3 aliases server (Racket)
* [ ] GN3 Genenetwork3 service (Python)
* [ ] GN3 auth proxy (Racket)
* [ ] set up https and letsencrypt
* [ ] setup logrotate for production log files
* [ ] Check performance and install monitors
## INFO
### Setting up Guix
We tend to install software in a guix profile. E.g.
```
guix pull -p ~/opt/guix-pull
. /home/wrk/opt/guix-pull/etc/profile
guix package -i mariadb -p /usr/local/guix-profiles/mariadb
```
### Mariadb (est. 1-2 hours)
Set up a global Mariadb
```
guix package -i mariadb -p /usr/local/guix-profiles/mariadb
```
Usually I use the Debian version to set up defaults
```
apt-get install mariadb
cd /etc/systemd/system
cp /lib/systemd/system/mariadb.service .
systemctl disable mariadb
```
Add to systemd
```diff
+Type=simple
+CapabilityBoundingSet=CAP_IPC_LOCK CAP_DAC_OVERRIDE CAP_AUDIT_WRITE
+PrivateDevices=false
+ProtectHome=false
+ExecStart=/usr/local/guix-profiles/mariadb/bin/mariadbd --pid-file=/var/run/mysqld/mariadb.pid $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
+PIDFile=/usr/local/mysql/data/mysqld.pid
+# ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
-ExecStartPost=/etc/mysql/debian-start
+RestartSec=15s
+TimeoutStartSec=infinity
+TimeoutStopSec=infinity
```
comment out the galera ExecStart too.
```
systemctl enable mariadb-guix.service
```
Make sure all symlinks point to our configuration file.
Before starting systemd you may want to make sure the database is running.
```
/usr/local/guix-profiles/mariadb/bin/mariadbd --pid-file=/var/run/mysqld/mariadb.pid --verbose (--help)
```
as root you should be able to login with
```
mysql -e 'show databases'
```
### Mariadb database from backup
We have daily incremental backups on P2, Tux02 and Epysode. First restore the files with
```
. ~/.borg-pass
cd /export2/tux01-restore
borg extract --progress /export2/backup/tux01/borg-tux01::borg-backup-mariadb-20220815-03:13-Mon
```
Extracting 430Gb takes about 90 minutes.
Now make sure mariadb is stopped. Copy the database to fast storage. Set permissions correctly:
```
chown mysql.mysql -R /var/lib/mysql
```
Check them and symlink the DB dir:
```
root@epysode:/export/tux01-mirror#
cp -vau /export2/tux01-restore/home/backup/tux01_mariadb_new .
systemctl stop mariadb
ln -s /export/tux01-mirror/tux01_mariadb_new/latest /var/lib/mysql
systemctl start mariadb
/usr/local/guix-profiles/guix-profiles/mariadb/bin/mysql_upgrade -u webqtlout -pwebqtlout
/export/backup/scripts/tux02/system_check.sh
```
In the process I discover that ibdata1 file has grown to 100GB. Not a problem yet, but we should purge that on production at some point
=> https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/
(obviously we don't want to use mysqldump right now, but I'll need to do some future work).
|
