Page MenuHomeMiraheze

Revise MariaDB backup strategy
Open, NormalPublic

Description

(todo: fill in with more information)

Our current backup strategy is directly copying /srv/mariadb to Bacula. However, that is guaranteed to provide corruption, since no table locks are acquired during the backup. On top of that, retrieving specific databases or tables is a pain.

See https://wikitech.wikimedia.org/wiki/MariaDB/Backups, and https://wikitech.wikimedia.org/wiki/MariaDB/Backups#Snapshoting_and_disaster_recovery in particular. While we can't compare our wiki sizes to Wikimedia's, their backups are generated using commonly used tools: mariabackup (which we're already using for creating replicas) and mydumper (alternative for mysqldump).

Event Timeline

Southparkfan triaged this task as Normal priority.Jul 6 2020, 17:30
Southparkfan created this task.

Contacted Owen for a data processing agreement for the free infra offers.

John added a subscriber: John.

Moving over to next goal period, @Southparkfan any updates/re-prioritisations?

Budget approved by Board. I have purchased a 4GB MKVM (dbbackup1.miraheze.org) @ RamNode.

mariabackup is running on db12 and db13 and is dumping all databases directly (over ssh) to dbbackup1.

Overview of plan:

  1. Set up replication for db12 (c3) and db13 (c4)
  2. Dump db11 (c2) to dbbackup1
  3. Set up replication for db11 (c2)
  4. Explore the possibility of using wmfbackups, otherwise write our own script that stops replication for a given cluster, uses mydumper to backup all databases to sql.gz files and starts replication

MariaDB instances became corrupted due to my own fault. Recloning.

dbbackup1 is replicating all clusters now.

Testing backups:

mysql -S /var/run/mysqld/mysqld.c4.sock -e 'stop slave'

cd /srv/backups

mydumper -x '^(?!(mysql\.|test\.))' -G -E -R -v 3 -t 1 -c -S /var/run/mysqld/mysqld.c4.sock -L "/srv/backups/logs/$(date +"%Y%m%d%H%M%S")-c4.log"

mysql -S /var/run/mysqld/mysqld.c4.sock -e 'start slave'