Adjust sql parameters

[perso/Immae/Config/Nix.git] / modules / private / databases / mariadb_replication.nix

{ pkgs, config, lib, ... }:
let
  cfg = config.myServices.databasesReplication.mariadb;
in
{
  options.myServices.databasesReplication.mariadb = {
    enable = lib.mkEnableOption "Enable mariadb replication";
    base = lib.mkOption {
      type = lib.types.path;
      description = ''
        Base path to put the replications
        '';
    };
    hosts = lib.mkOption {
      default = {};
      description = ''
        Hosts to backup
        '';
      type = lib.types.attrsOf (lib.types.submodule {
        options = {
          package = lib.mkOption {
            type = lib.types.package;
            default = pkgs.mariadb;
            description = ''
              Mariadb package for this host
            '';
          };
          serverId = lib.mkOption {
            type = lib.types.int;
            description = ''
              Server id to use for replication cluster (must be unique among the cluster!)
            '';
          };
          host = lib.mkOption {
            type = lib.types.str;
            description = ''
              Host to connect to
              '';
          };
          port = lib.mkOption {
            type = lib.types.str;
            description = ''
              Port to connect to
              '';
          };
          user = lib.mkOption {
            type = lib.types.str;
            description = ''
              User to connect as
              '';
          };
          password = lib.mkOption {
            type = lib.types.str;
            description = ''
              Password to use
              '';
          };
          dumpUser = lib.mkOption {
            type = lib.types.str;
            description = ''
              User who can do a dump
              '';
          };
          dumpPassword = lib.mkOption {
            type = lib.types.str;
            description = ''
              Password for the dump user
              '';
          };
        };
      });
    };
  };

  config = lib.mkIf cfg.enable {
    users.users.mysql = {
      description = "MySQL server user";
      group = "mysql";
      uid = config.ids.uids.mysql;
      extraGroups = [ "keys" ];
    };
    users.groups.mysql.gid = config.ids.gids.mysql;

    secrets.keys = lib.flatten (lib.mapAttrsToList (name: hcfg: [
      {
        dest = "mysql_replication/${name}/slave_init_commands";
        user = "mysql";
        group = "mysql";
        permissions = "0400";
        text = ''
          CHANGE MASTER TO master_host="${hcfg.host}", master_port=${hcfg.port}, master_user="${hcfg.user}", master_password="${hcfg.password}", master_ssl=1, master_use_gtid=slave_pos;
          START SLAVE;
          '';
      }
      {
        dest = "mysql_replication/${name}/mysqldump_remote";
        permissions = "0400";
        user = "root";
        group = "root";
        text = ''
          [mysqldump]
          user = ${hcfg.user}
          password = ${hcfg.password}
        '';
      }
      {
        dest = "mysql_replication/${name}/mysqldump";
        permissions = "0400";
        user = "root";
        group = "root";
        text = ''
          [mysqldump]
          user = ${hcfg.dumpUser}
          password = ${hcfg.dumpPassword}
        '';
      }
      {
        dest = "mysql_replication/${name}/client";
        permissions = "0400";
        user = "mysql";
        group = "mysql";
        text = ''
          [client]
          user = ${hcfg.dumpUser}
          password = ${hcfg.dumpPassword}
        '';
      }
    ]) cfg.hosts);

    services.cron = {
      enable = true;
      systemCronJobs = lib.flatten (lib.mapAttrsToList (name: hcfg:
        let
          dataDir = "${cfg.base}/${name}/mysql";
          backupDir = "${cfg.base}/${name}/mysql_backup";
          backup_script = pkgs.writeScript "backup_mysql_${name}" ''
              #!${pkgs.stdenv.shell}

              set -euo pipefail

              filename=${backupDir}/$(${pkgs.coreutils}/bin/date -Iminutes).sql
              ${hcfg.package}/bin/mysqldump \
                --defaults-file=${config.secrets.location}/mysql_replication/${name}/mysqldump \
                -S /run/mysqld_${name}/mysqld.sock \
                --gtid \
                --master-data \
                --flush-privileges \
                --ignore-database=netdata \
                --all-databases > $filename
              ${pkgs.gzip}/bin/gzip $filename
            '';
          u = pkgs.callPackage ./utils.nix {};
          cleanup_script = pkgs.writeScript "cleanup_mysql_${name}" (u.exponentialDumps "sql.gz" backupDir);
        in [
          "0 22,4,10,16 * * * root ${backup_script}"
          "0 3 * * * root ${cleanup_script}"
        ]) cfg.hosts);
    };

    system.activationScripts = lib.attrsets.mapAttrs' (name: hcfg:
      lib.attrsets.nameValuePair "mysql_replication_${name}" {
        deps = [ "users" "groups" ];
        text = ''
          install -m 0700 -o mysql -g mysql -d ${cfg.base}/${name}/mysql
          install -m 0700 -o mysql -g mysql -d ${cfg.base}/${name}/mysql_backup
          '';
      }) cfg.hosts;

    environment.etc = lib.attrsets.mapAttrs' (name: hcfg:
      lib.attrsets.nameValuePair "mysql/${name}_my.cnf" {
        text = ''
          [mysqld]
          skip-networking
          socket = /run/mysqld_${name}/mysqld.sock
          datadir = ${cfg.base}/${name}/mysql/
          log-bin = mariadb-bin
          server-id = ${builtins.toString hcfg.serverId}
          '';
      }
    ) cfg.hosts;

    systemd.services = lib.attrsets.mapAttrs' (name: hcfg:
      let
        dataDir = "${cfg.base}/${name}/mysql";
      in
      lib.attrsets.nameValuePair "mysql_backup_${name}" {
        description = "Mysql replication for ${name}";
        wantedBy = [ "multi-user.target" ];
        after = [ "network.target" ];
        restartTriggers = [ config.environment.etc."mysql/${name}_my.cnf".source ];
        unitConfig.RequiresMountsFor = dataDir;

        preStart = ''
          if ! test -e ${dataDir}/mysql; then
            ${hcfg.package}/bin/mysqldump \
              --defaults-file=${config.secrets.location}/mysql_replication/${name}/mysqldump_remote \
              -h ${hcfg.host} \
              -P ${hcfg.port} \
              --ssl \
              --gtid \
              --flush-privileges \
              --master-data \
              --all-databases > ${dataDir}/initial.sql

            ${hcfg.package}/bin/mysql_install_db \
              --defaults-file=/etc/mysql/${name}_my.cnf \
              --user=mysql \
              --datadir=${dataDir} \
              --basedir=${hcfg.package}
          fi
          '';

        serviceConfig = {
          User = "mysql";
          Group = "mysql";
          RuntimeDirectory = "mysqld_${name}";
          RuntimeDirectoryMode = "0755";
          SupplementaryGroups = "keys";
          PermissionsStartOnly = true;
          Type = "notify";

          ExecStart = "${hcfg.package}/bin/mysqld --defaults-file=/etc/mysql/${name}_my.cnf --user=mysql --datadir=${dataDir} --basedir=${hcfg.package}";
          ExecStartPost =
            let
              sql_before = pkgs.writeText "mysql-initial-before" ''
                DROP DATABASE test;
                '';
              setupScript = pkgs.writeScript "mysql-setup" ''
                #!${pkgs.runtimeShell} -e

                if test -e ${dataDir}/initial.sql; then
                  cat \
                    ${sql_before} \
                    ${dataDir}/initial.sql \
                    ${config.secrets.location}/mysql_replication/${name}/slave_init_commands \
                    | ${hcfg.package}/bin/mysql \
                    --defaults-file=/etc/mysql/${name}_my.cnf \
                    -S /run/mysqld_${name}/mysqld.sock \
                    --user=root
                  rm -f ${dataDir}/initial.sql
                fi
                '';
            in
              "+${setupScript}";
          # initial dump can take a long time
          TimeoutStartSec="infinity";
          TimeoutStopSec = 120;
        };
    }) cfg.hosts;
  };
}