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