Operations grimoire/MySQL: Difference between revisions
No edit summary |
No edit summary |
||
(One intermediate revision by the same user not shown) | |||
Line 60: | Line 60: | ||
While the acquisitariat container uses 'root'@'%', other containers could use 'root'@'localhost'. | 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. | If not you'll get an error message the init script failed to execute the ALTER statement. | ||
== Backup == | |||
=== Docker === | |||
Databases on acquisitariat can be dumped with mysqldump: | |||
$ mysqldump acquisitariat --all-databases | |||
=== Regular clusters === | |||
Automation is needed here to dump /var/backups/db, see [https://devcentral.nasqueron.org/T2076 T2076] | |||
On db-B-001, /var/backups/db-treasure-chest contains database inherited from old servers. | |||
== Clusters == | == Clusters == | ||
Line 90: | Line 101: | ||
|- | |- | ||
|} | |} | ||
[[Category:Operations grimoire]] | |||
[[Category:MySQL]] |
Latest revision as of 01:26, 27 October 2024
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:
- Edit the relevant pillar file, for example pillar/dbserver/cluster-B.sls in rOPS
- 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:
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.
Backup
Docker
Databases on acquisitariat can be dumped with mysqldump:
$ mysqldump acquisitariat --all-databases
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.
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 |