Operations grimoire/MySQL - Revision history

Dereckson: /* Howto */

2025-10-12T10:14:31Z

Howto

@@ Line 64: / Line 64: @@
 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 ==

Dereckson: /* Create a new database or user */

2025-10-12T09:49:56Z

Create a new database or user

← Older revision		Revision as of 09:49, 12 October 2025
Line 12:		Line 12:
	=== Create a new database or user ===		=== Create a new database or user ===

	It's a simple ~~two~~ steps process:		It's a simple three steps process:

	# Edit the relevant pillar file, for example pillar/dbserver/cluster-B.sls ~~in rOPS~~		# Generate a password and store it in Vault
			# Edit the relevant pillar file, for example {{Ops file\|pillar/dbserver/cluster-B.sls}}
	# Deploy the change: <code>salt db-B-001 state.apply roles/dbserver-mysql/content</code>		# Deploy the change: <code>salt db-B-001 state.apply roles/dbserver-mysql/content</code>

			To generate the password and store it in one operation you can use:
			$ vault kv put ops/secrets/dbserver/cluster-B/users/foo username=foo password="$(pwgen 21)"

	You generally need:		You generally need:

Dereckson: /* Docker */

2025-10-04T22:14:26Z

Docker

← Older revision		Revision as of 22:14, 4 October 2025
Line 69:		Line 69:
	== Backup ==		== Backup ==
	=== Docker ===		=== Docker ===
	Databases on acquisitariat can be dumped with mysqldump:		Databases on acquisitariat can currently be dumped with mysqldump:

	$ mysqldump acquisitariat --all-databases		$ mysqldump acquisitariat --all-databases

Dereckson: /* Docker */ ChatGPT doc for a backup script according my specifications to ignore Phabricator huge tables

2025-10-04T22:14:06Z

Docker: ChatGPT doc for a backup script according my specifications to ignore Phabricator huge tables

← Older revision		Revision as of 22:14, 4 October 2025
Line 72:		Line 72:

	$ mysqldump acquisitariat --all-databases		$ 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:

			<syntaxhighlight lang="yaml">
			ignore_tables:
			- %_file.storageblob
			</syntaxhighlight>

			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 ====
			<syntaxhighlight lang="yaml">
			aes_key: /etc/keys/mysql-backup.key

			ignore_tables:
			- %_file.storageblob

			containers:
			acquisitariat: /srv/acquisitariat/mysql
			phpbb_db: /srv/phpbb_db/mysql
			</syntaxhighlight>

			==== Usage ====
			Run without arguments to back up all configured containers:

			<syntaxhighlight lang="bash">
			$ mysql_backup
			</syntaxhighlight>

			==== 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 ===		=== Regular clusters ===

Dereckson: Notes about replication log (with the help of GPT) (let's try if GPT can be a good DBA series)

2025-07-27T16:12:36Z

Notes about replication log (with the help of GPT) (let's try if GPT can be a good DBA series)

← Older revision		Revision as of 16:12, 27 July 2025
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.

			== 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 [https://devcentral.nasqueron.org/D3645 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 ==		== Backup ==
Line 101:		Line 107:
	\|-		\|-
	\|}		\|}

			== 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.

	[[Category:Operations grimoire]]		[[Category:Operations grimoire]]
	[[Category:MySQL]]		[[Category:MySQL]]

Dereckson at 01:26, 27 October 2024

2024-10-27T01:26:08Z

← Older revision		Revision as of 01:26, 27 October 2024
Line 101:		Line 101:
	\|-		\|-
	\|}		\|}

			[[Category:Operations grimoire]]
			[[Category:MySQL]]

Dereckson: Backup

2024-10-27T01:25:46Z

Backup

@@ Line 60: / Line 60: @@
 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.
 == Clusters ==

Dereckson at 19:44, 3 August 2024

2024-08-03T19:44:51Z

← Older revision		Revision as of 19:44, 3 August 2024
Line 57:		Line 57:
	printf "ALTER USER 'root'@'%' IDENTIFIED BY '%s';\n" $(cat .token) > /tmp/rotate-password.sql		printf "ALTER USER 'root'@'%' IDENTIFIED BY '%s';\n" $(cat .token) > /tmp/rotate-password.sql
	mysqld --init-file=/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.

	== Clusters ==		== Clusters ==

Dereckson: /* Howto */

2024-08-03T19:42:17Z

Howto

@@ Line 45: / Line 45: @@
      .tcl sqlrehash
      Tcl error: Vault returned HTTP/1.1 503 Service Unavailable, 200 OK was expected.
 == Clusters ==

Dereckson at 19:46, 5 January 2024

2024-01-05T19:46:31Z

← Older revision		Revision as of 19:46, 5 January 2024
Line 26:		Line 26:

	Servers don't have a public ICANN IP, so you can only connect from other Nasqueron servers.		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:

			{\| class="wikitable"
			\|+ 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 ==		== Clusters ==