summary refs log tree commit diff
path: root/nixos/modules/services/databases/mysql.nix
diff options
context:
space:
mode:
Diffstat (limited to 'nixos/modules/services/databases/mysql.nix')
-rw-r--r--nixos/modules/services/databases/mysql.nix521
1 files changed, 521 insertions, 0 deletions
diff --git a/nixos/modules/services/databases/mysql.nix b/nixos/modules/services/databases/mysql.nix
new file mode 100644
index 00000000000..625b31d081c
--- /dev/null
+++ b/nixos/modules/services/databases/mysql.nix
@@ -0,0 +1,521 @@
+{ config, lib, pkgs, ... }:
+
+with lib;
+
+let
+
+  cfg = config.services.mysql;
+
+  isMariaDB = lib.getName cfg.package == lib.getName pkgs.mariadb;
+
+  mysqldOptions =
+    "--user=${cfg.user} --datadir=${cfg.dataDir} --basedir=${cfg.package}";
+
+  format = pkgs.formats.ini { listsAsDuplicateKeys = true; };
+  configFile = format.generate "my.cnf" cfg.settings;
+
+in
+
+{
+  imports = [
+    (mkRemovedOptionModule [ "services" "mysql" "pidDir" ] "Don't wait for pidfiles, describe dependencies through systemd.")
+    (mkRemovedOptionModule [ "services" "mysql" "rootPassword" ] "Use socket authentication or set the password outside of the nix store.")
+    (mkRemovedOptionModule [ "services" "mysql" "extraOptions" ] "Use services.mysql.settings.mysqld instead.")
+    (mkRemovedOptionModule [ "services" "mysql" "bind" ] "Use services.mysql.settings.mysqld.bind-address instead.")
+    (mkRemovedOptionModule [ "services" "mysql" "port" ] "Use services.mysql.settings.mysqld.port instead.")
+  ];
+
+  ###### interface
+
+  options = {
+
+    services.mysql = {
+
+      enable = mkEnableOption "MySQL server";
+
+      package = mkOption {
+        type = types.package;
+        example = literalExpression "pkgs.mariadb";
+        description = "
+          Which MySQL derivation to use. MariaDB packages are supported too.
+        ";
+      };
+
+      user = mkOption {
+        type = types.str;
+        default = "mysql";
+        description = ''
+          User account under which MySQL runs.
+
+          <note><para>
+          If left as the default value this user will automatically be created
+          on system activation, otherwise you are responsible for
+          ensuring the user exists before the MySQL service starts.
+          </para></note>
+        '';
+      };
+
+      group = mkOption {
+        type = types.str;
+        default = "mysql";
+        description = ''
+          Group account under which MySQL runs.
+
+          <note><para>
+          If left as the default value this group will automatically be created
+          on system activation, otherwise you are responsible for
+          ensuring the user exists before the MySQL service starts.
+          </para></note>
+        '';
+      };
+
+      dataDir = mkOption {
+        type = types.path;
+        example = "/var/lib/mysql";
+        description = ''
+          The data directory for MySQL.
+
+          <note><para>
+          If left as the default value of <literal>/var/lib/mysql</literal> this directory will automatically be created before the MySQL
+          server starts, otherwise you are responsible for ensuring the directory exists with appropriate ownership and permissions.
+          </para></note>
+        '';
+      };
+
+      configFile = mkOption {
+        type = types.path;
+        default = configFile;
+        defaultText = ''
+          A configuration file automatically generated by NixOS.
+        '';
+        description = ''
+          Override the configuration file used by MySQL. By default,
+          NixOS generates one automatically from <option>services.mysql.settings</option>.
+        '';
+        example = literalExpression ''
+          pkgs.writeText "my.cnf" '''
+            [mysqld]
+            datadir = /var/lib/mysql
+            bind-address = 127.0.0.1
+            port = 3336
+
+            !includedir /etc/mysql/conf.d/
+          ''';
+        '';
+      };
+
+      settings = mkOption {
+        type = format.type;
+        default = {};
+        description = ''
+          MySQL configuration. Refer to
+          <link xlink:href="https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html"/>,
+          <link xlink:href="https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html"/>,
+          and <link xlink:href="https://mariadb.com/kb/en/server-system-variables/"/>
+          for details on supported values.
+
+          <note>
+            <para>
+              MySQL configuration options such as <literal>--quick</literal> should be treated as
+              boolean options and provided values such as <literal>true</literal>, <literal>false</literal>,
+              <literal>1</literal>, or <literal>0</literal>. See the provided example below.
+            </para>
+          </note>
+        '';
+        example = literalExpression ''
+          {
+            mysqld = {
+              key_buffer_size = "6G";
+              table_cache = 1600;
+              log-error = "/var/log/mysql_err.log";
+              plugin-load-add = [ "server_audit" "ed25519=auth_ed25519" ];
+            };
+            mysqldump = {
+              quick = true;
+              max_allowed_packet = "16M";
+            };
+          }
+        '';
+      };
+
+      initialDatabases = mkOption {
+        type = types.listOf (types.submodule {
+          options = {
+            name = mkOption {
+              type = types.str;
+              description = ''
+                The name of the database to create.
+              '';
+            };
+            schema = mkOption {
+              type = types.nullOr types.path;
+              default = null;
+              description = ''
+                The initial schema of the database; if null (the default),
+                an empty database is created.
+              '';
+            };
+          };
+        });
+        default = [];
+        description = ''
+          List of database names and their initial schemas that should be used to create databases on the first startup
+          of MySQL. The schema attribute is optional: If not specified, an empty database is created.
+        '';
+        example = [
+          { name = "foodatabase"; schema = literalExpression "./foodatabase.sql"; }
+          { name = "bardatabase"; }
+        ];
+      };
+
+      initialScript = mkOption {
+        type = types.nullOr types.path;
+        default = null;
+        description = "A file containing SQL statements to be executed on the first startup. Can be used for granting certain permissions on the database.";
+      };
+
+      ensureDatabases = mkOption {
+        type = types.listOf types.str;
+        default = [];
+        description = ''
+          Ensures that the specified databases exist.
+          This option will never delete existing databases, especially not when the value of this
+          option is changed. This means that databases created once through this option or
+          otherwise have to be removed manually.
+        '';
+        example = [
+          "nextcloud"
+          "matomo"
+        ];
+      };
+
+      ensureUsers = mkOption {
+        type = types.listOf (types.submodule {
+          options = {
+            name = mkOption {
+              type = types.str;
+              description = ''
+                Name of the user to ensure.
+              '';
+            };
+            ensurePermissions = mkOption {
+              type = types.attrsOf types.str;
+              default = {};
+              description = ''
+                Permissions to ensure for the user, specified as attribute set.
+                The attribute names specify the database and tables to grant the permissions for,
+                separated by a dot. You may use wildcards here.
+                The attribute values specfiy the permissions to grant.
+                You may specify one or multiple comma-separated SQL privileges here.
+
+                For more information on how to specify the target
+                and on which privileges exist, see the
+                <link xlink:href="https://mariadb.com/kb/en/library/grant/">GRANT syntax</link>.
+                The attributes are used as <code>GRANT ''${attrName} ON ''${attrValue}</code>.
+              '';
+              example = literalExpression ''
+                {
+                  "database.*" = "ALL PRIVILEGES";
+                  "*.*" = "SELECT, LOCK TABLES";
+                }
+              '';
+            };
+          };
+        });
+        default = [];
+        description = ''
+          Ensures that the specified users exist and have at least the ensured permissions.
+          The MySQL users will be identified using Unix socket authentication. This authenticates the Unix user with the
+          same name only, and that without the need for a password.
+          This option will never delete existing users or remove permissions, especially not when the value of this
+          option is changed. This means that users created and permissions assigned once through this option or
+          otherwise have to be removed manually.
+        '';
+        example = literalExpression ''
+          [
+            {
+              name = "nextcloud";
+              ensurePermissions = {
+                "nextcloud.*" = "ALL PRIVILEGES";
+              };
+            }
+            {
+              name = "backup";
+              ensurePermissions = {
+                "*.*" = "SELECT, LOCK TABLES";
+              };
+            }
+          ]
+        '';
+      };
+
+      replication = {
+        role = mkOption {
+          type = types.enum [ "master" "slave" "none" ];
+          default = "none";
+          description = "Role of the MySQL server instance.";
+        };
+
+        serverId = mkOption {
+          type = types.int;
+          default = 1;
+          description = "Id of the MySQL server instance. This number must be unique for each instance.";
+        };
+
+        masterHost = mkOption {
+          type = types.str;
+          description = "Hostname of the MySQL master server.";
+        };
+
+        slaveHost = mkOption {
+          type = types.str;
+          description = "Hostname of the MySQL slave server.";
+        };
+
+        masterUser = mkOption {
+          type = types.str;
+          description = "Username of the MySQL replication user.";
+        };
+
+        masterPassword = mkOption {
+          type = types.str;
+          description = "Password of the MySQL replication user.";
+        };
+
+        masterPort = mkOption {
+          type = types.port;
+          default = 3306;
+          description = "Port number on which the MySQL master server runs.";
+        };
+      };
+    };
+
+  };
+
+
+  ###### implementation
+
+  config = mkIf cfg.enable {
+
+    services.mysql.dataDir =
+      mkDefault (if versionAtLeast config.system.stateVersion "17.09" then "/var/lib/mysql"
+                 else "/var/mysql");
+
+    services.mysql.settings.mysqld = mkMerge [
+      {
+        datadir = cfg.dataDir;
+        port = mkDefault 3306;
+      }
+      (mkIf (cfg.replication.role == "master" || cfg.replication.role == "slave") {
+        log-bin = "mysql-bin-${toString cfg.replication.serverId}";
+        log-bin-index = "mysql-bin-${toString cfg.replication.serverId}.index";
+        relay-log = "mysql-relay-bin";
+        server-id = cfg.replication.serverId;
+        binlog-ignore-db = [ "information_schema" "performance_schema" "mysql" ];
+      })
+      (mkIf (!isMariaDB) {
+        plugin-load-add = "auth_socket.so";
+      })
+    ];
+
+    users.users = optionalAttrs (cfg.user == "mysql") {
+      mysql = {
+        description = "MySQL server user";
+        group = cfg.group;
+        uid = config.ids.uids.mysql;
+      };
+    };
+
+    users.groups = optionalAttrs (cfg.group == "mysql") {
+      mysql.gid = config.ids.gids.mysql;
+    };
+
+    environment.systemPackages = [ cfg.package ];
+
+    environment.etc."my.cnf".source = cfg.configFile;
+
+    systemd.services.mysql = {
+      description = "MySQL Server";
+
+      after = [ "network.target" ];
+      wantedBy = [ "multi-user.target" ];
+      restartTriggers = [ cfg.configFile ];
+
+      unitConfig.RequiresMountsFor = cfg.dataDir;
+
+      path = [
+        # Needed for the mysql_install_db command in the preStart script
+        # which calls the hostname command.
+        pkgs.nettools
+      ];
+
+      preStart = if isMariaDB then ''
+        if ! test -e ${cfg.dataDir}/mysql; then
+          ${cfg.package}/bin/mysql_install_db --defaults-file=/etc/my.cnf ${mysqldOptions}
+          touch ${cfg.dataDir}/mysql_init
+        fi
+      '' else ''
+        if ! test -e ${cfg.dataDir}/mysql; then
+          ${cfg.package}/bin/mysqld --defaults-file=/etc/my.cnf ${mysqldOptions} --initialize-insecure
+          touch ${cfg.dataDir}/mysql_init
+        fi
+      '';
+
+      script = ''
+        # https://mariadb.com/kb/en/getting-started-with-mariadb-galera-cluster/#systemd-and-galera-recovery
+        if test -n "''${_WSREP_START_POSITION}"; then
+          if test -e "${cfg.package}/bin/galera_recovery"; then
+            VAR=$(cd ${cfg.package}/bin/..; ${cfg.package}/bin/galera_recovery); [[ $? -eq 0 ]] && export _WSREP_START_POSITION=$VAR || exit 1
+          fi
+        fi
+
+        # The last two environment variables are used for starting Galera clusters
+        exec ${cfg.package}/bin/mysqld --defaults-file=/etc/my.cnf ${mysqldOptions} $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
+      '';
+
+      postStart = let
+        # The super user account to use on *first* run of MySQL server
+        superUser = if isMariaDB then cfg.user else "root";
+      in ''
+        ${optionalString (!isMariaDB) ''
+          # Wait until the MySQL server is available for use
+          count=0
+          while [ ! -e /run/mysqld/mysqld.sock ]
+          do
+              if [ $count -eq 30 ]
+              then
+                  echo "Tried 30 times, giving up..."
+                  exit 1
+              fi
+
+              echo "MySQL daemon not yet started. Waiting for 1 second..."
+              count=$((count++))
+              sleep 1
+          done
+        ''}
+
+        if [ -f ${cfg.dataDir}/mysql_init ]
+        then
+            # While MariaDB comes with a 'mysql' super user account since 10.4.x, MySQL does not
+            # Since we don't want to run this service as 'root' we need to ensure the account exists on first run
+            ( echo "CREATE USER IF NOT EXISTS '${cfg.user}'@'localhost' IDENTIFIED WITH ${if isMariaDB then "unix_socket" else "auth_socket"};"
+              echo "GRANT ALL PRIVILEGES ON *.* TO '${cfg.user}'@'localhost' WITH GRANT OPTION;"
+            ) | ${cfg.package}/bin/mysql -u ${superUser} -N
+
+            ${concatMapStrings (database: ''
+              # Create initial databases
+              if ! test -e "${cfg.dataDir}/${database.name}"; then
+                  echo "Creating initial database: ${database.name}"
+                  ( echo 'create database `${database.name}`;'
+
+                    ${optionalString (database.schema != null) ''
+                    echo 'use `${database.name}`;'
+
+                    # TODO: this silently falls through if database.schema does not exist,
+                    # we should catch this somehow and exit, but can't do it here because we're in a subshell.
+                    if [ -f "${database.schema}" ]
+                    then
+                        cat ${database.schema}
+                    elif [ -d "${database.schema}" ]
+                    then
+                        cat ${database.schema}/mysql-databases/*.sql
+                    fi
+                    ''}
+                  ) | ${cfg.package}/bin/mysql -u ${superUser} -N
+              fi
+            '') cfg.initialDatabases}
+
+            ${optionalString (cfg.replication.role == "master")
+              ''
+                # Set up the replication master
+
+                ( echo "use mysql;"
+                  echo "CREATE USER '${cfg.replication.masterUser}'@'${cfg.replication.slaveHost}' IDENTIFIED WITH mysql_native_password;"
+                  echo "SET PASSWORD FOR '${cfg.replication.masterUser}'@'${cfg.replication.slaveHost}' = PASSWORD('${cfg.replication.masterPassword}');"
+                  echo "GRANT REPLICATION SLAVE ON *.* TO '${cfg.replication.masterUser}'@'${cfg.replication.slaveHost}';"
+                ) | ${cfg.package}/bin/mysql -u ${superUser} -N
+              ''}
+
+            ${optionalString (cfg.replication.role == "slave")
+              ''
+                # Set up the replication slave
+
+                ( echo "stop slave;"
+                  echo "change master to master_host='${cfg.replication.masterHost}', master_user='${cfg.replication.masterUser}', master_password='${cfg.replication.masterPassword}';"
+                  echo "start slave;"
+                ) | ${cfg.package}/bin/mysql -u ${superUser} -N
+              ''}
+
+            ${optionalString (cfg.initialScript != null)
+              ''
+                # Execute initial script
+                # using toString to avoid copying the file to nix store if given as path instead of string,
+                # as it might contain credentials
+                cat ${toString cfg.initialScript} | ${cfg.package}/bin/mysql -u ${superUser} -N
+              ''}
+
+            rm ${cfg.dataDir}/mysql_init
+        fi
+
+        ${optionalString (cfg.ensureDatabases != []) ''
+          (
+          ${concatMapStrings (database: ''
+            echo "CREATE DATABASE IF NOT EXISTS \`${database}\`;"
+          '') cfg.ensureDatabases}
+          ) | ${cfg.package}/bin/mysql -N
+        ''}
+
+        ${concatMapStrings (user:
+          ''
+            ( echo "CREATE USER IF NOT EXISTS '${user.name}'@'localhost' IDENTIFIED WITH ${if isMariaDB then "unix_socket" else "auth_socket"};"
+              ${concatStringsSep "\n" (mapAttrsToList (database: permission: ''
+                echo "GRANT ${permission} ON ${database} TO '${user.name}'@'localhost';"
+              '') user.ensurePermissions)}
+            ) | ${cfg.package}/bin/mysql -N
+          '') cfg.ensureUsers}
+      '';
+
+      serviceConfig = mkMerge [
+        {
+          Type = if isMariaDB then "notify" else "simple";
+          Restart = "on-abort";
+          RestartSec = "5s";
+
+          # User and group
+          User = cfg.user;
+          Group = cfg.group;
+          # Runtime directory and mode
+          RuntimeDirectory = "mysqld";
+          RuntimeDirectoryMode = "0755";
+          # Access write directories
+          ReadWritePaths = [ cfg.dataDir ];
+          # Capabilities
+          CapabilityBoundingSet = "";
+          # Security
+          NoNewPrivileges = true;
+          # Sandboxing
+          ProtectSystem = "strict";
+          ProtectHome = true;
+          PrivateTmp = true;
+          PrivateDevices = true;
+          ProtectHostname = true;
+          ProtectKernelTunables = true;
+          ProtectKernelModules = true;
+          ProtectControlGroups = true;
+          RestrictAddressFamilies = [ "AF_UNIX" "AF_INET" "AF_INET6" ];
+          LockPersonality = true;
+          MemoryDenyWriteExecute = true;
+          RestrictRealtime = true;
+          RestrictSUIDSGID = true;
+          PrivateMounts = true;
+          # System Call Filtering
+          SystemCallArchitectures = "native";
+        }
+        (mkIf (cfg.dataDir == "/var/lib/mysql") {
+          StateDirectory = "mysql";
+          StateDirectoryMode = "0700";
+        })
+      ];
+    };
+  };
+}