pyrox.dev
1{ config, lib, pkgs, ... }:
2
3with lib;
4
5let
6
7 cfg = config.services.mysql;
8
9 isMariaDB = lib.getName cfg.package == lib.getName pkgs.mariadb;
10
11 mysqldOptions =
12 "--user=${cfg.user} --datadir=${cfg.dataDir} --basedir=${cfg.package}";
13
14 format = pkgs.formats.ini { listsAsDuplicateKeys = true; };
15 configFile = format.generate "my.cnf" cfg.settings;
16
17in
18
19{
20 imports = [
21 (mkRemovedOptionModule [ "services" "mysql" "pidDir" ] "Don't wait for pidfiles, describe dependencies through systemd.")
22 (mkRemovedOptionModule [ "services" "mysql" "rootPassword" ] "Use socket authentication or set the password outside of the nix store.")
23 (mkRemovedOptionModule [ "services" "mysql" "extraOptions" ] "Use services.mysql.settings.mysqld instead.")
24 (mkRemovedOptionModule [ "services" "mysql" "bind" ] "Use services.mysql.settings.mysqld.bind-address instead.")
25 (mkRemovedOptionModule [ "services" "mysql" "port" ] "Use services.mysql.settings.mysqld.port instead.")
26 ];
27
28 ###### interface
29
30 options = {
31
32 services.mysql = {
33
34 enable = mkEnableOption (lib.mdDoc "MySQL server");
35
36 package = mkOption {
37 type = types.package;
38 example = literalExpression "pkgs.mariadb";
39 description = lib.mdDoc ''
40 Which MySQL derivation to use. MariaDB packages are supported too.
41 '';
42 };
43
44 user = mkOption {
45 type = types.str;
46 default = "mysql";
47 description = lib.mdDoc ''
48 User account under which MySQL runs.
49
50 ::: {.note}
51 If left as the default value this user will automatically be created
52 on system activation, otherwise you are responsible for
53 ensuring the user exists before the MySQL service starts.
54 :::
55 '';
56 };
57
58 group = mkOption {
59 type = types.str;
60 default = "mysql";
61 description = lib.mdDoc ''
62 Group account under which MySQL runs.
63
64 ::: {.note}
65 If left as the default value this group will automatically be created
66 on system activation, otherwise you are responsible for
67 ensuring the user exists before the MySQL service starts.
68 :::
69 '';
70 };
71
72 dataDir = mkOption {
73 type = types.path;
74 example = "/var/lib/mysql";
75 description = lib.mdDoc ''
76 The data directory for MySQL.
77
78 ::: {.note}
79 If left as the default value of `/var/lib/mysql` this directory will automatically be created before the MySQL
80 server starts, otherwise you are responsible for ensuring the directory exists with appropriate ownership and permissions.
81 :::
82 '';
83 };
84
85 configFile = mkOption {
86 type = types.path;
87 default = configFile;
88 defaultText = ''
89 A configuration file automatically generated by NixOS.
90 '';
91 description = lib.mdDoc ''
92 Override the configuration file used by MySQL. By default,
93 NixOS generates one automatically from {option}`services.mysql.settings`.
94 '';
95 example = literalExpression ''
96 pkgs.writeText "my.cnf" '''
97 [mysqld]
98 datadir = /var/lib/mysql
99 bind-address = 127.0.0.1
100 port = 3336
101
102 !includedir /etc/mysql/conf.d/
103 ''';
104 '';
105 };
106
107 settings = mkOption {
108 type = format.type;
109 default = {};
110 description = lib.mdDoc ''
111 MySQL configuration. Refer to
112 <https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html>,
113 <https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html>,
114 and <https://mariadb.com/kb/en/server-system-variables/>
115 for details on supported values.
116
117 ::: {.note}
118 MySQL configuration options such as `--quick` should be treated as
119 boolean options and provided values such as `true`, `false`,
120 `1`, or `0`. See the provided example below.
121 :::
122 '';
123 example = literalExpression ''
124 {
125 mysqld = {
126 key_buffer_size = "6G";
127 table_cache = 1600;
128 log-error = "/var/log/mysql_err.log";
129 plugin-load-add = [ "server_audit" "ed25519=auth_ed25519" ];
130 };
131 mysqldump = {
132 quick = true;
133 max_allowed_packet = "16M";
134 };
135 }
136 '';
137 };
138
139 initialDatabases = mkOption {
140 type = types.listOf (types.submodule {
141 options = {
142 name = mkOption {
143 type = types.str;
144 description = lib.mdDoc ''
145 The name of the database to create.
146 '';
147 };
148 schema = mkOption {
149 type = types.nullOr types.path;
150 default = null;
151 description = lib.mdDoc ''
152 The initial schema of the database; if null (the default),
153 an empty database is created.
154 '';
155 };
156 };
157 });
158 default = [];
159 description = lib.mdDoc ''
160 List of database names and their initial schemas that should be used to create databases on the first startup
161 of MySQL. The schema attribute is optional: If not specified, an empty database is created.
162 '';
163 example = [
164 { name = "foodatabase"; schema = literalExpression "./foodatabase.sql"; }
165 { name = "bardatabase"; }
166 ];
167 };
168
169 initialScript = mkOption {
170 type = types.nullOr types.path;
171 default = null;
172 description = lib.mdDoc "A file containing SQL statements to be executed on the first startup. Can be used for granting certain permissions on the database.";
173 };
174
175 ensureDatabases = mkOption {
176 type = types.listOf types.str;
177 default = [];
178 description = lib.mdDoc ''
179 Ensures that the specified databases exist.
180 This option will never delete existing databases, especially not when the value of this
181 option is changed. This means that databases created once through this option or
182 otherwise have to be removed manually.
183 '';
184 example = [
185 "nextcloud"
186 "matomo"
187 ];
188 };
189
190 ensureUsers = mkOption {
191 type = types.listOf (types.submodule {
192 options = {
193 name = mkOption {
194 type = types.str;
195 description = lib.mdDoc ''
196 Name of the user to ensure.
197 '';
198 };
199 ensurePermissions = mkOption {
200 type = types.attrsOf types.str;
201 default = {};
202 description = lib.mdDoc ''
203 Permissions to ensure for the user, specified as attribute set.
204 The attribute names specify the database and tables to grant the permissions for,
205 separated by a dot. You may use wildcards here.
206 The attribute values specfiy the permissions to grant.
207 You may specify one or multiple comma-separated SQL privileges here.
208
209 For more information on how to specify the target
210 and on which privileges exist, see the
211 [GRANT syntax](https://mariadb.com/kb/en/library/grant/).
212 The attributes are used as `GRANT ''${attrName} ON ''${attrValue}`.
213 '';
214 example = literalExpression ''
215 {
216 "database.*" = "ALL PRIVILEGES";
217 "*.*" = "SELECT, LOCK TABLES";
218 }
219 '';
220 };
221 };
222 });
223 default = [];
224 description = lib.mdDoc ''
225 Ensures that the specified users exist and have at least the ensured permissions.
226 The MySQL users will be identified using Unix socket authentication. This authenticates the Unix user with the
227 same name only, and that without the need for a password.
228 This option will never delete existing users or remove permissions, especially not when the value of this
229 option is changed. This means that users created and permissions assigned once through this option or
230 otherwise have to be removed manually.
231 '';
232 example = literalExpression ''
233 [
234 {
235 name = "nextcloud";
236 ensurePermissions = {
237 "nextcloud.*" = "ALL PRIVILEGES";
238 };
239 }
240 {
241 name = "backup";
242 ensurePermissions = {
243 "*.*" = "SELECT, LOCK TABLES";
244 };
245 }
246 ]
247 '';
248 };
249
250 replication = {
251 role = mkOption {
252 type = types.enum [ "master" "slave" "none" ];
253 default = "none";
254 description = lib.mdDoc "Role of the MySQL server instance.";
255 };
256
257 serverId = mkOption {
258 type = types.int;
259 default = 1;
260 description = lib.mdDoc "Id of the MySQL server instance. This number must be unique for each instance.";
261 };
262
263 masterHost = mkOption {
264 type = types.str;
265 description = lib.mdDoc "Hostname of the MySQL master server.";
266 };
267
268 slaveHost = mkOption {
269 type = types.str;
270 description = lib.mdDoc "Hostname of the MySQL slave server.";
271 };
272
273 masterUser = mkOption {
274 type = types.str;
275 description = lib.mdDoc "Username of the MySQL replication user.";
276 };
277
278 masterPassword = mkOption {
279 type = types.str;
280 description = lib.mdDoc "Password of the MySQL replication user.";
281 };
282
283 masterPort = mkOption {
284 type = types.port;
285 default = 3306;
286 description = lib.mdDoc "Port number on which the MySQL master server runs.";
287 };
288 };
289 };
290
291 };
292
293
294 ###### implementation
295
296 config = mkIf cfg.enable {
297
298 services.mysql.dataDir =
299 mkDefault (if versionAtLeast config.system.stateVersion "17.09" then "/var/lib/mysql"
300 else "/var/mysql");
301
302 services.mysql.settings.mysqld = mkMerge [
303 {
304 datadir = cfg.dataDir;
305 port = mkDefault 3306;
306 }
307 (mkIf (cfg.replication.role == "master" || cfg.replication.role == "slave") {
308 log-bin = "mysql-bin-${toString cfg.replication.serverId}";
309 log-bin-index = "mysql-bin-${toString cfg.replication.serverId}.index";
310 relay-log = "mysql-relay-bin";
311 server-id = cfg.replication.serverId;
312 binlog-ignore-db = [ "information_schema" "performance_schema" "mysql" ];
313 })
314 (mkIf (!isMariaDB) {
315 plugin-load-add = "auth_socket.so";
316 })
317 ];
318
319 users.users = optionalAttrs (cfg.user == "mysql") {
320 mysql = {
321 description = "MySQL server user";
322 group = cfg.group;
323 uid = config.ids.uids.mysql;
324 };
325 };
326
327 users.groups = optionalAttrs (cfg.group == "mysql") {
328 mysql.gid = config.ids.gids.mysql;
329 };
330
331 environment.systemPackages = [ cfg.package ];
332
333 environment.etc."my.cnf".source = cfg.configFile;
334
335 systemd.services.mysql = {
336 description = "MySQL Server";
337
338 after = [ "network.target" ];
339 wantedBy = [ "multi-user.target" ];
340 restartTriggers = [ cfg.configFile ];
341
342 unitConfig.RequiresMountsFor = cfg.dataDir;
343
344 path = [
345 # Needed for the mysql_install_db command in the preStart script
346 # which calls the hostname command.
347 pkgs.nettools
348 ];
349
350 preStart = if isMariaDB then ''
351 if ! test -e ${cfg.dataDir}/mysql; then
352 ${cfg.package}/bin/mysql_install_db --defaults-file=/etc/my.cnf ${mysqldOptions}
353 touch ${cfg.dataDir}/mysql_init
354 fi
355 '' else ''
356 if ! test -e ${cfg.dataDir}/mysql; then
357 ${cfg.package}/bin/mysqld --defaults-file=/etc/my.cnf ${mysqldOptions} --initialize-insecure
358 touch ${cfg.dataDir}/mysql_init
359 fi
360 '';
361
362 script = ''
363 # https://mariadb.com/kb/en/getting-started-with-mariadb-galera-cluster/#systemd-and-galera-recovery
364 if test -n "''${_WSREP_START_POSITION}"; then
365 if test -e "${cfg.package}/bin/galera_recovery"; then
366 VAR=$(cd ${cfg.package}/bin/..; ${cfg.package}/bin/galera_recovery); [[ $? -eq 0 ]] && export _WSREP_START_POSITION=$VAR || exit 1
367 fi
368 fi
369
370 # The last two environment variables are used for starting Galera clusters
371 exec ${cfg.package}/bin/mysqld --defaults-file=/etc/my.cnf ${mysqldOptions} $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
372 '';
373
374 postStart = let
375 # The super user account to use on *first* run of MySQL server
376 superUser = if isMariaDB then cfg.user else "root";
377 in ''
378 ${optionalString (!isMariaDB) ''
379 # Wait until the MySQL server is available for use
380 count=0
381 while [ ! -e /run/mysqld/mysqld.sock ]
382 do
383 if [ $count -eq 30 ]
384 then
385 echo "Tried 30 times, giving up..."
386 exit 1
387 fi
388
389 echo "MySQL daemon not yet started. Waiting for 1 second..."
390 count=$((count++))
391 sleep 1
392 done
393 ''}
394
395 if [ -f ${cfg.dataDir}/mysql_init ]
396 then
397 # While MariaDB comes with a 'mysql' super user account since 10.4.x, MySQL does not
398 # Since we don't want to run this service as 'root' we need to ensure the account exists on first run
399 ( echo "CREATE USER IF NOT EXISTS '${cfg.user}'@'localhost' IDENTIFIED WITH ${if isMariaDB then "unix_socket" else "auth_socket"};"
400 echo "GRANT ALL PRIVILEGES ON *.* TO '${cfg.user}'@'localhost' WITH GRANT OPTION;"
401 ) | ${cfg.package}/bin/mysql -u ${superUser} -N
402
403 ${concatMapStrings (database: ''
404 # Create initial databases
405 if ! test -e "${cfg.dataDir}/${database.name}"; then
406 echo "Creating initial database: ${database.name}"
407 ( echo 'create database `${database.name}`;'
408
409 ${optionalString (database.schema != null) ''
410 echo 'use `${database.name}`;'
411
412 # TODO: this silently falls through if database.schema does not exist,
413 # we should catch this somehow and exit, but can't do it here because we're in a subshell.
414 if [ -f "${database.schema}" ]
415 then
416 cat ${database.schema}
417 elif [ -d "${database.schema}" ]
418 then
419 cat ${database.schema}/mysql-databases/*.sql
420 fi
421 ''}
422 ) | ${cfg.package}/bin/mysql -u ${superUser} -N
423 fi
424 '') cfg.initialDatabases}
425
426 ${optionalString (cfg.replication.role == "master")
427 ''
428 # Set up the replication master
429
430 ( echo "use mysql;"
431 echo "CREATE USER '${cfg.replication.masterUser}'@'${cfg.replication.slaveHost}' IDENTIFIED WITH mysql_native_password;"
432 echo "SET PASSWORD FOR '${cfg.replication.masterUser}'@'${cfg.replication.slaveHost}' = PASSWORD('${cfg.replication.masterPassword}');"
433 echo "GRANT REPLICATION SLAVE ON *.* TO '${cfg.replication.masterUser}'@'${cfg.replication.slaveHost}';"
434 ) | ${cfg.package}/bin/mysql -u ${superUser} -N
435 ''}
436
437 ${optionalString (cfg.replication.role == "slave")
438 ''
439 # Set up the replication slave
440
441 ( echo "stop slave;"
442 echo "change master to master_host='${cfg.replication.masterHost}', master_user='${cfg.replication.masterUser}', master_password='${cfg.replication.masterPassword}';"
443 echo "start slave;"
444 ) | ${cfg.package}/bin/mysql -u ${superUser} -N
445 ''}
446
447 ${optionalString (cfg.initialScript != null)
448 ''
449 # Execute initial script
450 # using toString to avoid copying the file to nix store if given as path instead of string,
451 # as it might contain credentials
452 cat ${toString cfg.initialScript} | ${cfg.package}/bin/mysql -u ${superUser} -N
453 ''}
454
455 rm ${cfg.dataDir}/mysql_init
456 fi
457
458 ${optionalString (cfg.ensureDatabases != []) ''
459 (
460 ${concatMapStrings (database: ''
461 echo "CREATE DATABASE IF NOT EXISTS \`${database}\`;"
462 '') cfg.ensureDatabases}
463 ) | ${cfg.package}/bin/mysql -N
464 ''}
465
466 ${concatMapStrings (user:
467 ''
468 ( echo "CREATE USER IF NOT EXISTS '${user.name}'@'localhost' IDENTIFIED WITH ${if isMariaDB then "unix_socket" else "auth_socket"};"
469 ${concatStringsSep "\n" (mapAttrsToList (database: permission: ''
470 echo "GRANT ${permission} ON ${database} TO '${user.name}'@'localhost';"
471 '') user.ensurePermissions)}
472 ) | ${cfg.package}/bin/mysql -N
473 '') cfg.ensureUsers}
474 '';
475
476 serviceConfig = mkMerge [
477 {
478 Type = if isMariaDB then "notify" else "simple";
479 Restart = "on-abort";
480 RestartSec = "5s";
481
482 # User and group
483 User = cfg.user;
484 Group = cfg.group;
485 # Runtime directory and mode
486 RuntimeDirectory = "mysqld";
487 RuntimeDirectoryMode = "0755";
488 # Access write directories
489 ReadWritePaths = [ cfg.dataDir ];
490 # Capabilities
491 CapabilityBoundingSet = "";
492 # Security
493 NoNewPrivileges = true;
494 # Sandboxing
495 ProtectSystem = "strict";
496 ProtectHome = true;
497 PrivateTmp = true;
498 PrivateDevices = true;
499 ProtectHostname = true;
500 ProtectKernelTunables = true;
501 ProtectKernelModules = true;
502 ProtectControlGroups = true;
503 RestrictAddressFamilies = [ "AF_UNIX" "AF_INET" "AF_INET6" ];
504 LockPersonality = true;
505 MemoryDenyWriteExecute = true;
506 RestrictRealtime = true;
507 RestrictSUIDSGID = true;
508 PrivateMounts = true;
509 # System Call Filtering
510 SystemCallArchitectures = "native";
511 }
512 (mkIf (cfg.dataDir == "/var/lib/mysql") {
513 StateDirectory = "mysql";
514 StateDirectoryMode = "0700";
515 })
516 ];
517 };
518 };
519}