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 = literalExpression ''
164 [
165 { name = "foodatabase"; schema = ./foodatabase.sql; }
166 { name = "bardatabase"; }
167 ]
168 '';
169 };
170
171 initialScript = mkOption {
172 type = types.nullOr types.path;
173 default = null;
174 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.";
175 };
176
177 ensureDatabases = mkOption {
178 type = types.listOf types.str;
179 default = [];
180 description = lib.mdDoc ''
181 Ensures that the specified databases exist.
182 This option will never delete existing databases, especially not when the value of this
183 option is changed. This means that databases created once through this option or
184 otherwise have to be removed manually.
185 '';
186 example = [
187 "nextcloud"
188 "matomo"
189 ];
190 };
191
192 ensureUsers = mkOption {
193 type = types.listOf (types.submodule {
194 options = {
195 name = mkOption {
196 type = types.str;
197 description = lib.mdDoc ''
198 Name of the user to ensure.
199 '';
200 };
201 ensurePermissions = mkOption {
202 type = types.attrsOf types.str;
203 default = {};
204 description = lib.mdDoc ''
205 Permissions to ensure for the user, specified as attribute set.
206 The attribute names specify the database and tables to grant the permissions for,
207 separated by a dot. You may use wildcards here.
208 The attribute values specfiy the permissions to grant.
209 You may specify one or multiple comma-separated SQL privileges here.
210
211 For more information on how to specify the target
212 and on which privileges exist, see the
213 [GRANT syntax](https://mariadb.com/kb/en/library/grant/).
214 The attributes are used as `GRANT ''${attrName} ON ''${attrValue}`.
215 '';
216 example = literalExpression ''
217 {
218 "database.*" = "ALL PRIVILEGES";
219 "*.*" = "SELECT, LOCK TABLES";
220 }
221 '';
222 };
223 };
224 });
225 default = [];
226 description = lib.mdDoc ''
227 Ensures that the specified users exist and have at least the ensured permissions.
228 The MySQL users will be identified using Unix socket authentication. This authenticates the Unix user with the
229 same name only, and that without the need for a password.
230 This option will never delete existing users or remove permissions, especially not when the value of this
231 option is changed. This means that users created and permissions assigned once through this option or
232 otherwise have to be removed manually.
233 '';
234 example = literalExpression ''
235 [
236 {
237 name = "nextcloud";
238 ensurePermissions = {
239 "nextcloud.*" = "ALL PRIVILEGES";
240 };
241 }
242 {
243 name = "backup";
244 ensurePermissions = {
245 "*.*" = "SELECT, LOCK TABLES";
246 };
247 }
248 ]
249 '';
250 };
251
252 replication = {
253 role = mkOption {
254 type = types.enum [ "master" "slave" "none" ];
255 default = "none";
256 description = lib.mdDoc "Role of the MySQL server instance.";
257 };
258
259 serverId = mkOption {
260 type = types.int;
261 default = 1;
262 description = lib.mdDoc "Id of the MySQL server instance. This number must be unique for each instance.";
263 };
264
265 masterHost = mkOption {
266 type = types.str;
267 description = lib.mdDoc "Hostname of the MySQL master server.";
268 };
269
270 slaveHost = mkOption {
271 type = types.str;
272 description = lib.mdDoc "Hostname of the MySQL slave server.";
273 };
274
275 masterUser = mkOption {
276 type = types.str;
277 description = lib.mdDoc "Username of the MySQL replication user.";
278 };
279
280 masterPassword = mkOption {
281 type = types.str;
282 description = lib.mdDoc "Password of the MySQL replication user.";
283 };
284
285 masterPort = mkOption {
286 type = types.port;
287 default = 3306;
288 description = lib.mdDoc "Port number on which the MySQL master server runs.";
289 };
290 };
291 };
292
293 };
294
295
296 ###### implementation
297
298 config = mkIf cfg.enable {
299
300 services.mysql.dataDir =
301 mkDefault (if versionAtLeast config.system.stateVersion "17.09" then "/var/lib/mysql"
302 else "/var/mysql");
303
304 services.mysql.settings.mysqld = mkMerge [
305 {
306 datadir = cfg.dataDir;
307 port = mkDefault 3306;
308 }
309 (mkIf (cfg.replication.role == "master" || cfg.replication.role == "slave") {
310 log-bin = "mysql-bin-${toString cfg.replication.serverId}";
311 log-bin-index = "mysql-bin-${toString cfg.replication.serverId}.index";
312 relay-log = "mysql-relay-bin";
313 server-id = cfg.replication.serverId;
314 binlog-ignore-db = [ "information_schema" "performance_schema" "mysql" ];
315 })
316 (mkIf (!isMariaDB) {
317 plugin-load-add = "auth_socket.so";
318 })
319 ];
320
321 users.users = optionalAttrs (cfg.user == "mysql") {
322 mysql = {
323 description = "MySQL server user";
324 group = cfg.group;
325 uid = config.ids.uids.mysql;
326 };
327 };
328
329 users.groups = optionalAttrs (cfg.group == "mysql") {
330 mysql.gid = config.ids.gids.mysql;
331 };
332
333 environment.systemPackages = [ cfg.package ];
334
335 environment.etc."my.cnf".source = cfg.configFile;
336
337 systemd.services.mysql = {
338 description = "MySQL Server";
339
340 after = [ "network.target" ];
341 wantedBy = [ "multi-user.target" ];
342 restartTriggers = [ cfg.configFile ];
343
344 unitConfig.RequiresMountsFor = cfg.dataDir;
345
346 path = [
347 # Needed for the mysql_install_db command in the preStart script
348 # which calls the hostname command.
349 pkgs.nettools
350 ];
351
352 preStart = if isMariaDB then ''
353 if ! test -e ${cfg.dataDir}/mysql; then
354 ${cfg.package}/bin/mysql_install_db --defaults-file=/etc/my.cnf ${mysqldOptions}
355 touch ${cfg.dataDir}/mysql_init
356 fi
357 '' else ''
358 if ! test -e ${cfg.dataDir}/mysql; then
359 ${cfg.package}/bin/mysqld --defaults-file=/etc/my.cnf ${mysqldOptions} --initialize-insecure
360 touch ${cfg.dataDir}/mysql_init
361 fi
362 '';
363
364 script = ''
365 # https://mariadb.com/kb/en/getting-started-with-mariadb-galera-cluster/#systemd-and-galera-recovery
366 if test -n "''${_WSREP_START_POSITION}"; then
367 if test -e "${cfg.package}/bin/galera_recovery"; then
368 VAR=$(cd ${cfg.package}/bin/..; ${cfg.package}/bin/galera_recovery); [[ $? -eq 0 ]] && export _WSREP_START_POSITION=$VAR || exit 1
369 fi
370 fi
371
372 # The last two environment variables are used for starting Galera clusters
373 exec ${cfg.package}/bin/mysqld --defaults-file=/etc/my.cnf ${mysqldOptions} $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
374 '';
375
376 postStart = let
377 # The super user account to use on *first* run of MySQL server
378 superUser = if isMariaDB then cfg.user else "root";
379 in ''
380 ${optionalString (!isMariaDB) ''
381 # Wait until the MySQL server is available for use
382 count=0
383 while [ ! -e /run/mysqld/mysqld.sock ]
384 do
385 if [ $count -eq 30 ]
386 then
387 echo "Tried 30 times, giving up..."
388 exit 1
389 fi
390
391 echo "MySQL daemon not yet started. Waiting for 1 second..."
392 count=$((count++))
393 sleep 1
394 done
395 ''}
396
397 if [ -f ${cfg.dataDir}/mysql_init ]
398 then
399 # While MariaDB comes with a 'mysql' super user account since 10.4.x, MySQL does not
400 # Since we don't want to run this service as 'root' we need to ensure the account exists on first run
401 ( echo "CREATE USER IF NOT EXISTS '${cfg.user}'@'localhost' IDENTIFIED WITH ${if isMariaDB then "unix_socket" else "auth_socket"};"
402 echo "GRANT ALL PRIVILEGES ON *.* TO '${cfg.user}'@'localhost' WITH GRANT OPTION;"
403 ) | ${cfg.package}/bin/mysql -u ${superUser} -N
404
405 ${concatMapStrings (database: ''
406 # Create initial databases
407 if ! test -e "${cfg.dataDir}/${database.name}"; then
408 echo "Creating initial database: ${database.name}"
409 ( echo 'create database `${database.name}`;'
410
411 ${optionalString (database.schema != null) ''
412 echo 'use `${database.name}`;'
413
414 # TODO: this silently falls through if database.schema does not exist,
415 # we should catch this somehow and exit, but can't do it here because we're in a subshell.
416 if [ -f "${database.schema}" ]
417 then
418 cat ${database.schema}
419 elif [ -d "${database.schema}" ]
420 then
421 cat ${database.schema}/mysql-databases/*.sql
422 fi
423 ''}
424 ) | ${cfg.package}/bin/mysql -u ${superUser} -N
425 fi
426 '') cfg.initialDatabases}
427
428 ${optionalString (cfg.replication.role == "master")
429 ''
430 # Set up the replication master
431
432 ( echo "use mysql;"
433 echo "CREATE USER '${cfg.replication.masterUser}'@'${cfg.replication.slaveHost}' IDENTIFIED WITH mysql_native_password;"
434 echo "SET PASSWORD FOR '${cfg.replication.masterUser}'@'${cfg.replication.slaveHost}' = PASSWORD('${cfg.replication.masterPassword}');"
435 echo "GRANT REPLICATION SLAVE ON *.* TO '${cfg.replication.masterUser}'@'${cfg.replication.slaveHost}';"
436 ) | ${cfg.package}/bin/mysql -u ${superUser} -N
437 ''}
438
439 ${optionalString (cfg.replication.role == "slave")
440 ''
441 # Set up the replication slave
442
443 ( echo "stop slave;"
444 echo "change master to master_host='${cfg.replication.masterHost}', master_user='${cfg.replication.masterUser}', master_password='${cfg.replication.masterPassword}';"
445 echo "start slave;"
446 ) | ${cfg.package}/bin/mysql -u ${superUser} -N
447 ''}
448
449 ${optionalString (cfg.initialScript != null)
450 ''
451 # Execute initial script
452 # using toString to avoid copying the file to nix store if given as path instead of string,
453 # as it might contain credentials
454 cat ${toString cfg.initialScript} | ${cfg.package}/bin/mysql -u ${superUser} -N
455 ''}
456
457 rm ${cfg.dataDir}/mysql_init
458 fi
459
460 ${optionalString (cfg.ensureDatabases != []) ''
461 (
462 ${concatMapStrings (database: ''
463 echo "CREATE DATABASE IF NOT EXISTS \`${database}\`;"
464 '') cfg.ensureDatabases}
465 ) | ${cfg.package}/bin/mysql -N
466 ''}
467
468 ${concatMapStrings (user:
469 ''
470 ( echo "CREATE USER IF NOT EXISTS '${user.name}'@'localhost' IDENTIFIED WITH ${if isMariaDB then "unix_socket" else "auth_socket"};"
471 ${concatStringsSep "\n" (mapAttrsToList (database: permission: ''
472 echo "GRANT ${permission} ON ${database} TO '${user.name}'@'localhost';"
473 '') user.ensurePermissions)}
474 ) | ${cfg.package}/bin/mysql -N
475 '') cfg.ensureUsers}
476 '';
477
478 serviceConfig = mkMerge [
479 {
480 Type = if isMariaDB then "notify" else "simple";
481 Restart = "on-abort";
482 RestartSec = "5s";
483
484 # User and group
485 User = cfg.user;
486 Group = cfg.group;
487 # Runtime directory and mode
488 RuntimeDirectory = "mysqld";
489 RuntimeDirectoryMode = "0755";
490 # Access write directories
491 ReadWritePaths = [ cfg.dataDir ];
492 # Capabilities
493 CapabilityBoundingSet = "";
494 # Security
495 NoNewPrivileges = true;
496 # Sandboxing
497 ProtectSystem = "strict";
498 ProtectHome = true;
499 PrivateTmp = true;
500 PrivateDevices = true;
501 ProtectHostname = true;
502 ProtectKernelTunables = true;
503 ProtectKernelModules = true;
504 ProtectControlGroups = true;
505 RestrictAddressFamilies = [ "AF_UNIX" "AF_INET" "AF_INET6" ];
506 LockPersonality = true;
507 MemoryDenyWriteExecute = true;
508 RestrictRealtime = true;
509 RestrictSUIDSGID = true;
510 PrivateMounts = true;
511 # System Call Filtering
512 SystemCallArchitectures = "native";
513 }
514 (mkIf (cfg.dataDir == "/var/lib/mysql") {
515 StateDirectory = "mysql";
516 StateDirectoryMode = "0700";
517 })
518 ];
519 };
520 };
521}