MySQL

This component sets up a managed instance of the MySQL database server.

We use the Percona Distribution for MySQL which provides useful improvements over the standard Oracle MySQL/MariaDB implementations.

Supported versions

There’s a role for each supported version, currently:

  • mysql57: Percona 5.7.x (End-of-life)

  • percona80: Percona 8.0.x (LTS release)

  • percona83: Percona 8.3.x (End-of-life)

  • percona84: Percona 8.4.x

We recommend the use of percona84, as this is the most up to date LTS release. You might also use percona80 if you are unable to use 8.4. All other versions don’t receive security updates and are only included to allow the upgrade of existing machines to this platform version.

Configuration

MySQL works out-of-the box without configuration.

The root user is authenticated by socket auth with the mysql and root system users.

Custom config files in /etc/local/mysql are included in the main mysql configuration file on the next system build. Add a local.cnf (or any other *.cnf) file there, and run sudo fc-manage switch to activate the new configuration.

Note

Changes to *.cnf files in this directory will restart MySQL to activate the new configuration.

Interaction

Service users can use sudo -iu mysql to access the MySQL root account to perform administrative commands and log files in /var/log/mysql. To connect to the local MySQL server, run mysql as mysql user:

sudo -u mysql mysql

The MySQL server can be accessed from other machines in the same resource group on the default port 3306.

Slow Log

SQL statements that take longer than 100 milliseconds to run, are logged to /var/log/mysql/mysql.slow. The log file is rotated when file size is greater than 2GB or at least weekly.

The default of 100 milliseconds for slow queries can be changed with a global option: SET GLOBAL long_query_time=1.5; where the value is the time in seconds.

Backup

For backup tasks the xtrabackup command is provided, along with sudo permission for executing xtrabackup from the service user as root.

Monitoring

The default monitoring setup checks that the MySQL server process is running and that it responds to connection attempts to the standard MySQL port.

Populating with Initial Data

For populating the database with data or executing other custom SQL commands at first startup, the NixOS option services.percona.initialScript can be set to a file containing such SQL commands.

Caution

This is mainly useful for Development Host deployments, as the script will only be executed at first startup and is ignored afterwards.

Enabling a Percona role first and only setting an initial script later won’t have any effect anymore.

Migrate user password hash algorithm

Before Percona (and generally MySQL) 8.4, mysql_native_password was the default authentication and password hash algorithm. This algorithm will be removed in our platform with NixOS 25.11. With 8.4 caching_sha2_password is the new default algorithm to use.

To get a list of users needing to be migrated, run the following SQL statement:

SELECT user,host,plugin FROM mysql.user WHERE plugin='mysql_native_password';

These users need to be manually migrated to the new algorithm with the following SQL statement (insert username, host, password):

ALTER USER 'username'@'host' IDENTIFIED WITH 'caching_sha2_password' by 'password';

This migration needs to be done before using Percona 9.0. Please check your MySQL client library support for caching_sha2_password before migrating the user.