Operations grimoire/MySQL

From Nasqueron Agora

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.

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