Operations grimoire/MySQL

MySQL and MariaDB are available as a standalone role and can also be enabled on devserver. Currently, all deployed instances through the dbserver-mysql role are MariaDB ones.

It's mainly used by the websites hosted in the Alkane PaaS and by IRC eggdrops

Applications on the Docker PaaS can have their own container or use Acquisitariat, a container for Phabricator instances, and other dev & community services. Those containers use MySQL.

Howto

Open a console

Use peer authentication from the root user: sudo mysql

Create a new database or user

It's a simple two steps process:

1. Edit the relevant pillar file, for example pillar/dbserver/cluster-B.sls in rOPS
2. Deploy the change: salt db-B-001 state.apply roles/dbserver-mysql/content

You generally need:

• an user block in dbserver_mysql.users
  • block title is the username
  • a password needs to be set in Vault, key is under ops/secrets/
  • if it can be restricted to a single host or a subpart of the private network, you can set Host:, if not it will be %.
• a database block

Servers don't have a public ICANN IP, so you can only connect from other Nasqueron servers.

What to do after an update of MySQL servers?

The following services need their connection to explicitly be restarted:

Services to tweak

Cluster	Service	Procedure
B	ViperServ > Wearg	Connect to partyline, run .tcl sqlrehash
B	ViperServ > Daeghrefn	Connect to partyline, run .tcl sqlrehash

Note: ViperServ eggdrops use Vault for credentials, so if you've got a 503, ensure Vault is unsealed:

   .tcl sqlrehash
   Tcl error: Vault returned HTTP/1.1 503 Service Unavailable, 200 OK was expected.

Rotate root password for a Docker container

It's not clear how to force restarting the MySQL container with a --init-file process.

Shutdown the container, and do the operation in a new container works:

   docker run -it --rm -v /srv/acquisitariat/mysql:/var/lib/mysql nasqueron/mysql bash
   cat > .token
   <write here the new password from Vault>
   printf "ALTER USER 'root'@'%' IDENTIFIED BY '%s';\n" $(cat .token) > /tmp/rotate-password.sql
   mysqld --init-file=/tmp/rotate-password.sql

While the acquisitariat container uses 'root'@'%', other containers could use 'root'@'localhost'. If not you'll get an error message the init script failed to execute the ALTER statement.

Configuration notes

Binary log rotation

To prevent unbounded disk usage by the MariaDB binary logs, a retention policy has been set to expire logs after 14 days per D3645.

This is configured via the `binlog_expire_logs_seconds` directive, which automatically purges logs older than two weeks. This change helps maintain disk availability without manual cleanup, while retaining enough history for most diagnostic or future replication use cases.

Backup

Docker

Databases on acquisitariat can currently be dumped with mysqldump:

   $ mysqldump acquisitariat --all-databases

An alternative plan is to prepare a script to automate MySQL backups for Docker containers running the ``nasqueron/mysql`` image.

It performs backups concurrently at database or table level depending on database size.

• **Small databases (≤ 5 MB):** backed up as one SQL file.
• **Large databases:** each table is dumped individually.

Table Ignore Rules

Tables can be ignored with patterns defined in the YAML configuration.

For example:

ignore_tables:
  - %_file.storageblob

This means that in any database whose name ends with ``_file``, the table ``storageblob`` will be excluded.

``%`` is used as a wildcard (same as MySQL LIKE).

Encryption

After the backup is complete, the resulting tar archive is encrypted using AES-256-CBC via OpenSSL. The encryption key path is defined by ``aes_key`` in the configuration file.

Plan in the future is to use encryption as a service instead, once OpenBao new deployment is stabilized.

Container autodetection

If no container list is defined in YAML, the script automatically detects all containers based on the ``nasqueron/mysql`` image and identifies the mounted volume corresponding to ``/var/lib/mysql`` via ``docker inspect``.

Example configuration

aes_key: /etc/keys/mysql-backup.key

ignore_tables:
  - %_file.storageblob

containers:
  acquisitariat: /srv/acquisitariat/mysql
  phpbb_db: /srv/phpbb_db/mysql

Usage

Run without arguments to back up all configured containers:

$ mysql_backup

Output

Each container backup is:

 * dumped under a temporary directory
 * put in a tar container, and gzipped (.tar or .tar.gz)
 * encrypted to AES (``.aes`` file)
 * printed with its final encrypted path (not sure we'll keep that)

Regular clusters

Automation is needed here to dump /var/backups/db, see T2076

On db-B-001, /var/backups/db-treasure-chest contains database inherited from old servers.

Clusters

Letters can be discontinuous: for example, A is a PostgreSQL cluster.

Cluster B

B is the general cluster, for Nasqueron services. It currently has one server, db-B-001.

Databases

Database	Managed by	Description
Nasqueron	Nasqueron IRC SIG	Database for our eggdrops
wikis	Nasqueron Ops	MediaWiki farm
arsmagica
inidal_wiki
nasqueron_wiki
utopia	Dereckson	MediaWiki + archives
wolfplexdb	Wolfplex	MediaWiki + other content

Troubleshoot

Binary log uses all the disk space

Issue when logged as root a `RESET MASTER;` sql command to restart the binary log from scratch.