MariaDB/Upgrading a section

Rolling restarts are used to perform security updates or upgrade minor or major MariaDB versions.

See the Kernel and MariaDB version dashboard: https://zarcillo.wikimedia.org/ui/hosts

Monitor kernel update progress with: https://grafana.wikimedia.org/d/fcnrmzq/mariadb-kernel-versions

Monitor MariaDB update progress with: https://grafana.wikimedia.org/d/bd9fc9e2-6bb5-463d-a783-87c59d23b6f1/mariadb-versions

Using the dbtools scripts to update/reboot multiple hosts

The dbtools scripts repository contains rolling_restart.py and similar scripts

Also to update security tasks like T426633 you can use security_reboots_updater.py from this MR. See its --help for usage and provide a Phabricator API token at ~/.phabricator_token.

Tool summary

Name	Target	Multi-host	Zarcillo lock	Reboot	OS upgrade	MariaDB upgrade	Phab on failure
`rolling_restart.py`	s, es6/es7, x	🟢	🟢	🟢	❓	🔴	🟢
`rolling_restart_es.py`	es1–es5	🟢	🟢	🟢	❓	🔴	🔴
`rolling_restart_pc_ms.py`	pc, ms	🟢	🔴	🟢	❓	🟢	🔴
`sre.mysql.upgrade`	minor ver.	🔴	🔴	🟢	🔴	🟢	🔴
`sre.mysql.major-upgrade`	major ver.	🔴	🔴	🟢	🔴	🟢	🔴
manual	m sections	🔴	🔴	🟢	🔴	🔴	🔴

Phab: opens a task on Phabricator on failure.

Updating the core (s), external storage read-write (es6, es7) and extension storage (x1, x3) sections

Use rolling_restart.py. The script performs a rolling restart of database hosts while also updating the OS. It updates MariaDB as part of the OS update according to Puppet's configuration.

The script handles, in order:

Silencing of alerts
Depooling
Updating OS including MariaDB (if a new version is present and configured in puppet)
Rebooting (switches to a new kernel if found)
Checking replication lag (except for RO `es*` sections)
Repooling
Enabling alerts

If a host fails to come back after reboot the script opens a Phabricator task tagged DBA and stops.

Usage:

Check out its repository and auto_schema in a dedicated directory on a cumin host
Review rolling_restart.py's help
Run in screen/tmux/byobu as:

# See the help command
sudo PYTHONPATH=../auto_schema python3 rolling_restart.py -h
sudo PYTHONPATH=../auto_schema python3 rolling_restart.py --run --task-id <task> <versions>

# Example:
sudo PYTHONPATH=../auto_schema python3 rolling_restart.py --task-id T426633 --dc codfw --run 12:6.1.172,13:6.12.88

Updating the external storage read only (es1-5) sections

For the read-write sections see above.

For the read only sections:

# See:
./rolling_restart_es.py -h

# Example:
sudo ./rolling_restart_es.py -t T419961 -r'Security updates' 6.1.164 check

Updating the MainStash (ms) and Parser cache (pc) sections

# See:
./rolling_restart_pc_ms.py -h

# Example:
sudo ./rolling_restart_pc_ms.py -t T419961 -r'Security updates' 12:6.1.164 13:6.12.74 reboot ms

Updating the Misc (m) sections

There is no script to run the updates. Coordinate with Jaime as the misc section is used as backup source.

See MariaDB/dbproxy for details.

Updating Misc (m) sections

Misc replicas

Eqiad replica db1217 (m1, m2, m3 and m5): can be done anytime but coordinate with Jaime
Codfw replica db2160: can be done anytime

for i in m1 m2 m3 m5; do echo $i; systemctl stop mariadb@$i ; done
umount /srv && swapoff -a && sync && reboot
for i in m1 m2 m3 m5; do echo $i; systemctl start mariadb@$i ; done
for i in m1 m2 m3 m5; do echo $i;mysql -S /run/mysqld/mysqld.$i.sock -e "start replica"; done
for i in m1 m2 m3 m5; do echo $i;mysql -S /run/mysqld/mysqld.$i.sock -e "show replica status \G"; done

Misc masters

Codfw master can be done anytime without switchover

Identify the dbproxy hosts fronting the misc instances to reboot by looking on puppet
- grep <master_hostname> hieradata/hosts/dbproxy2*.yaml
Create an icinga silence for such dbproxy hosts
- sudo cookbook sre.hosts.downtime --hours 24 -r "Reboots $task" 'dbproxyREPLACEME*'
Also downtime the host to be rebooted and its replicas
- sudo cookbook sre.hosts.downtime --hours 24 -r "Reboots $task" 'REPLACEME*'
Monitor dbproxy dashboard https://grafana.wikimedia.org/d/fc48lf4/dbproxy
Restart dbproxy's haproxy service after master reboot

Eqiad master requires a custom process

Updating Data Platform (dbstore*) hosts

Ask Ben Tullis

Order of upgrades

Upgrade clouddb* hosts.
Upgrade Sanitarium hosts in both DCs (see below)
Upgrade Sanitarium primaries in both DCs and ensure sanitarium host hangs from the 10.4 one in the active DC
Upgrade the candidate master on the standby DC
Upgrade the backup source in the standby DC (coordinate with Jaime)
Upgrade the master in the standby DC
Upgrade the candidate master in the primary DC
Upgrade the backup source in the primary DC (coordinate with Jaime)
Switchover the primary host in the primary DC to a Buster+10.4 host
Upgrade the old primary and make it a candidate primary

Upgrading sanitarium masters

To upgrade db1154 and db1155 first set a downtime for all their replicas then:

export task=T0000
sudo cookbook sre.hosts.downtime --hours 24 -r "Reboots $task" 'an-redacted*'
sudo cookbook sre.hosts.downtime --hours 24 -r "Reboots $task" 'clouddb*'
sudo cookbook sre.hosts.downtime --hours 24 -r "Reboots $task" 'db115[45]*'

ssh db1154
sudo -1
for i in s1 s3 s5 s8 x3; do echo $i; systemctl stop mariadb@$i ; done
umount /srv && swapoff -a && sync && reboot
for i in s1 s3 s5 s8 x3; do
 echo $i; systemctl start mariadb@$i ;
 mysql -S /run/mysqld/mysqld.$i.sock -e "start replica";
 mysql -S /run/mysqld/mysqld.$i.sock -e "show replica status \G";
done

ssh db1155
sudo -i
for i in s2 s4 s6 s7; do echo $i; systemctl stop mariadb@$i ; done
umount /srv && swapoff -a && sync && reboot
for i in s2 s4 s6 s7; do
 echo $i; systemctl start mariadb@$i ;
 mysql -S /run/mysqld/mysqld.$i.sock -e "start replica";
 mysql -S /run/mysqld/mysqld.$i.sock -e "show replica status \G";
done

Upgrading MariaDB minor version on a single host

Use the sre.mysql.upgrade cookbook.

Upgrading MariaDB major version on a single host

Use the sre.mysql.major-upgrade cookbook.

Reimage and upgrade MariaDB major version on a single host

If required to reimage to a different OS:

sudo cookbook sre.mysql.major-upgrade -t TASK --reimage $OS (trixie,bookworm etc) --repool $HOST wmf-mariadb1011

Where wmf-mariadb1011 is the version to be upgraded to (should match what we have on the repo for that OS).

Upgrade procedure (legacy)

Patch the dhcp file: [example]
Run puppet on install1003 and install2003
Depool the host (if needed) using software/dbtools/depool-and-wait
Silence the host in Icinga (e.g. on a cumin host, cookbook sre.hosts.downtime xxxx.wmnet -D1 -t TXXXXXX -r "reimage for upgrade - TXXXXXX")
Stop MySQL on the host
Run umount /srv; swapoff -a
Run reimage: sudo -E sudo cookbook sre.hosts.reimage xxxx.wmnet -p TXXXXXX
Wait until the host is up
Run systemctl set-environment MYSQLD_OPTS=”--skip-slave-start”
Run chown -R mysql. /srv/*; systemctl start mariadb ; mysql_upgrade
Run systemctl restart prometheus-mysqld-exporter.service
Dropped the host from Tendril and re-add it, otherwise they won’t get updated on tendril metrics
Check all the tables before starting replication (this can take up to 24h depending on the section)
- In a screen run: mysqlcheck --all-databases
- If any corruption is discovered, fix it with the following: journalctl -xe -u mariadb | grep table | grep Flagged | awk -F "table" '{print $2}' | awk -F " " '{print $1}' | tr -d "\`" | uniq >> /root/to_fix ; for i in `cat /root/to_fix`; do echo $i; mysql -e "set session sql_log_bin=0; alter table $i engine=InnoDB, force"; done
Start the replica
Wait until the host is up
Repool the host.

Accepts a Cumin query to match one or more hosts and:

Depools the host using the sre.mysql.depool cookbook.
Downtimes it
Stops replication
Upgrades packages including MariaDB using apt-get dist-upgrade
Reboots it
Starts MariaDB and runs mysql_upgrade then restarts replication
Optionally repools it if --repool was passed
Logs in Phabricator

Previous manual steps (obsoleted)

You should log that a maintenance is about to happen:
```
!log Upgrade db1111 T123456
```
The package for the mariadb server must be upgraded, usually:
```
sudo apt upgrade 'wmf-mariadb*'
```
where wmf-mariadb* is the package version you want to upgrade to, e.g. wmf-mariadb104, for WMF's version of MariaDB 10.4 WMFf package is built thinking to avoid side effects- so it won't automatically try to stop, restart or alter in any way a running instance- so it is possible to run it an any time, even if a previous version is currently executing. But unless there is a reason for it (e.g. minimizing upgrade downtime) it should probably ran after all current instances are shutdown.

Start mysql in a safe way- not starting replication automatically and removing any old buffer pool dump:

sudo systemctl set-environment MYSQLD_OPTS="--skip-slave-start"
<for each datadir> sudo mv ib_buffer_pool ib_buffer_pool.bak

mysql_upgrade must be ran on every instance after startup, and before replication starts, for single instance hosts:

systemctl start mariadb
systemctl status mariadb  # check it started correctly (it is ok to have some errors on first start up due to ongoing upgrade, due to old table formats)
mysql_upgrade

For multiple instance hosts, for each instance:

sudo systemctl start mariadb@<section>
sudo systemctl status mariadb@<section>  # check it started correctly (it is ok to have some errors on first start up due to ongoing upgrade, due to old table formats)
sudo mysql_upgrade -S /run/mysqld/mysqld.<section>.sock

Where section is the list of instances to upgrade on that host (e.g. s1 and s2, x1, s5 and s4, etc.)

After upgrade, if the mysql database changed, it is important to perform a reboot. This is normally skippable for minor upgrades, but guarantees it started with the right formatting:
```
sudo systemctl restart mariadb # or sudo systemctl restart mariadb@<section> (for each section upgraded)
```

The rest of steps to get the server into production state would be the same as on a regular reboot/restart: MariaDB/Rebooting_a_host (restart replication, repool, reenable monitoring, safety checks)

This page is a part of the SRE Data Persistence technical documentation
(go here for a list of all our pages)