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. **EDIT: Reusing the same steps from T301949#8554308 did not work this time. I have updated the strategy, and the steps below, to use a libvirt snapshot instead of an LVM snapshot. See the comments below for more details.**
**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`. Create a partition and an ext4 filesystem.
```
fdisk /dev/vdb # use "g" to create the partition table, then "n" to create a single partition
mkfs.ext4 /dev/vdb1
```
2. Flush data to disk and take note of the current GTID. If the "FLUSH TABLES" command hangs for more than a few seconds, open a separate mysql session, run `SHOW PROCESSLIST;` and manually kill (using `KILL {id}`) any query where command is not `Sleep` and state is not `Waiting...`.
```
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. From the hypervisor `cloudvirt1019`, create a libvirt snapshot of the virtual disk attached to `clouddb1001` (instance name `i-000045d4`) and mount it to `/mnt`.
```
virsh domblklist i-000045d4 #take note of the virtual disk filename
virsh snapshot-create-as --disk-only --domain i-00038d77 #this
mkdir /mnt-snapshot
guestmount -a {disk_filename} -i --ro /mnt-snapshot
```
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
systemctl start prometheus-mysqld-exporter #for some reason this must be restarted manually
SET GLOBAL read_only = 0;
```
6. Detach the destination Cinder volume (`tools-db-1`) from `clouddb1001` and attach it directly to the hypervisor using `rbd map`, then mount it to `/mnt-cinder`.
```
rbd map eqiad1-cinder/volume-9583ba88-b836-4233-8e51-6213d403f9c7
mkdir /mnt-cinder
mount /dev/rbd0p1 /mnt-cinder/
```
7. Copy the snapshot to the attached Cinder volume.
```
# make sure to run this in a screen/tmux session as it will take a few hours
rsync -a --info=progress2 /mnt-snapshot/srv/labsdb/ /mnt-cinder/
```
8. Unmount both the snapshot and the Cinder volume.
```
guestunmount /mnt-snapshot
umount /mnt-cinder
rbd unmap /dev/rbd0
```
9. 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}
```
10. From the Horizon web interface (project: `tools`), 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
chown -R mysql:mysql /srv/labsdb/
systemctl start mariadb
```
11. Run `mysql_upgrade` to upgrade the data format from MariaDB 10.1 to 10.4
12. Check with `journalctl -u mariadb -f` that there is no error. Then 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;
```
13. 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.