As detailed in T301949, `tools-db-1` is a new VM with Ceph storage that will become the new primary ToolsDB database.
This task is to set up `tools-db-1` as a MariaDB replica that will replicate from the current primary `clouddb1001`. I already did that once (T301949#8554308) but after the outage in T329535 the replica is no longer in sync and needs to be recreated from scratch.
The required steps are detailed below, based on the experience of my previous attempt.
**Step 2 will set MariaDB in read-only mode, and step 3 will stop it completely, so remember to write a message in IRC (`#wikimedia-cloud`) and send an email to [cloud-announce@lists.wikimedia.org](mailto:cloud-announce@lists.wikimedia.org) to inform people of the brief ToolsDB outage (expected to last about 2 minutes in total).**
1. In the Horizon web interface (project: `clouddb-services`), create a new 4TB Cinder volume (type: `high-iops`) and attach it to `clouddb1001`. Then format it and mount it.
```
mkfs.ext4 /dev/vdb1
mkdir /mnt/tools-db
mount /dev/vdb1 /mnt/tools-db
```
2. Flush data to disk and take note of the current GTID.
```
FLUSH TABLES WITH READ LOCK;
SELECT @@global.gtid_binlog_pos; #store the result somewhere safe, it will be needed in step 9
```
3. Stop MariaDB on clouddb1001 to ensure the snapshot we are going to take in the next step is "clean". I tried taking a snapshot without stopping MariaDB and that meant I could not load it cleanly on a new instance running a newer version of MariaDB (see T301949#8565151).
```
systemctl stop mariadb
```
4. Create an LVM snapshot of the current locally-attached volume `/dev/vd/data` in `clouddb1001`. The maximum size that can be allocated in the current volume group (as shown by `vgdisplay`) is 13G. This operation will take less than a second.
```
lvcreate -L13G -s -n data_lvm_snapshot /dev/vd/data
```
5. Restart mariadb on clouddb1001, and enable write mode (this instance of mariadb is configured to start in read-only mode for extra safety).
```
systemctl start mariadb
SET GLOBAL read_only = 0;
```
6. Copy the snapshot to the attached Cinder volume, excluding the `linkwatcher` database because it's the biggest database and we plan to migrate it separately in T328691. I will use `tar` because it's faster than `rsync` (see T301949#8558606) and has an easy `--exclude` option that `cp` does not have. This operation will take a few hours and should complete just in time before the space allocated for the LVM snapshot fills up. If the space fills up, there is no dramatic consequence, but the snapshot will be deleted automatically by LVM and the copy will fail if it is still in progress.
```
mkdir /mnt/data_lvm_snapshot
mount /dev/vd/data_lvm_snapshot /mnt/data_lvm_snapshot -onouuid,ro
cd /mnt/data_lvm_snapshot/labsdb
# make sure to run this in a screen/tmux session as it will take a few hours
tar cf - --exclude='./backup1' --exclude='./data/s51230__linkwatcher' . | (cd /mnt/tools-db && tar xvf -)
```
7. Unmount the snapshot and delete the snapshot (or simply let LVM delete it automatically when the allocated space fills up).
```
umount /mnt/data_lvm_backup
lvremove /dev/vd/data_lvm_snapshot
```
8. From the Horizon web interface, unattach the data volume from `clouddb1001`, then from a shell in a `cloudcontrol*` host, transfer the volume to the `tools` project.
```
export OS_PROJECT_ID=clouddb-services
openstack volume list
openstack volume transfer request create {volume-id}
export OS_PROJECT_ID=tools
openstack volume transfer request accept --auth-key {auth-key} {transfer-id}
```
9. From the Horizon web interface, create a Cinder volume snapshot (for extra safety in case something goes wrong in the following steps) and
attach the volume to `tools-db-1`. Then from a shell in `tools-db-1` make sure that MariaDB is not running, mount the volume, and start MariaDB.
```
systemctl stop mariadb
mount /dev/sdb1 /srv/labsdb
systemctl start mariadb
```
10. Check with `journalctl -u mariadb -f` that MariaDB successfully upgrades the data to the new MariaDB version. It will take some time as it involves rebuilding tables. When it completes, start the replication, excluding two databases: linkwatcher (that will be migrated separately in T328691) and mw (that will be migrated separately in T328693).
```
SET GLOBAL replicate_wild_ignore_table="s51230__linkwatcher.%,s54518__mw.%";
SET GLOBAL gtid_slave_pos = "{the value from step 2}"
SET GLOBAL innodb_flush_log_at_trx_commit=2;
CHANGE MASTER TO
MASTER_HOST='clouddb1001.clouddb-services.eqiad1.wikimedia.cloud',
MASTER_USER='repl',
MASTER_PASSWORD='{the password for the repl user},
MASTER_USE_GTID=slave_pos;
START SLAVE;
```
11. Using `SHOW SLAVE STATUS\G` or the [ToolsDB Grafana dashboard](https://grafana-labs.wikimedia.org/d/000000273/tools-mariadb?orgId=1), check that the replication lag (`Seconds_Behind_Master`) decreases to zero.