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