path: root/systems/backup-2/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.int;
            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 {
    myServices.chatonsProperties.hostings.mysql-replication = {
      file.datetime = "2022-08-27T15:00:00";
      hosting = {
        name = "Mysql replication";
        description = "Replication of mysql database";
        website = "db-1.immae.eu";
        status.level = "OK";
        status.description = "OK";
        registration.load = "OPEN";
        install.type = "PACKAGE";
      };
      software = {
        name = "MariaDB";
        website = "https://mariadb.org/";
        license.url = "https://github.com/MariaDB/server/blob/10.11/COPYING";
        license.name = "GNU General Public License v2.0";
        version = pkgs.mariadb.version;
        source.url = "https://github.com/MariaDB/server";
      };
    };
    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.listToAttrs (lib.flatten (lib.mapAttrsToList (name: hcfg: [
      (lib.nameValuePair "mysql_replication/${name}/slave_init_commands" {
        user = "mysql";
        group = "mysql";
        permissions = "0400";
        text = ''
          CHANGE MASTER TO master_host="${hcfg.host}", master_port=${builtins.toString hcfg.port}, master_user="${hcfg.user}", master_password="${hcfg.password}", master_ssl=1, master_use_gtid=slave_pos;
          START SLAVE;
          '';
      })
      (lib.nameValuePair "mysql_replication/${name}/mysqldump_remote" {
        permissions = "0400";
        user = "root";
        group = "root";
        text = ''
          [mysqldump]
          user = ${hcfg.user}
          password = ${hcfg.password}
        '';
      })
      (lib.nameValuePair "mysql_replication/${name}/mysqldump" {
        permissions = "0400";
        user = "root";
        group = "root";
        text = ''
          [mysqldump]
          user = ${hcfg.dumpUser}
          password = ${hcfg.dumpPassword}
        '';
      })
      (lib.nameValuePair "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.fullPaths."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;

    environment.systemPackages = lib.mapAttrsToList (name: hcfg:
      pkgs.writeScriptBin "mysql_backup_${name}" ''
        #!${pkgs.stdenv.shell}

        exec ${hcfg.package}/bin/mysql -S /run/mysqld_${name}/mysqld.sock "$@"
      ''
    ) 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
            if ! test -e ${dataDir}/initial.sql; then
              ${hcfg.package}/bin/mysqldump \
                --defaults-file=${config.secrets.fullPaths."mysql_replication/${name}/mysqldump_remote"} \
                -h ${hcfg.host} \
                -P ${builtins.toString hcfg.port} \
                --ssl \
                --gtid \
                --flush-privileges \
                --master-data \
                --all-databases > ${dataDir}/initial.sql
            fi

            ${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;
                INSTALL SONAME 'auth_pam';
                '';
              setupScript = pkgs.writeScript "mysql-setup" ''
                #!${pkgs.runtimeShell} -e

                if test -e ${dataDir}/initial.sql; then
                  cat \
                    ${sql_before} \
                    ${dataDir}/initial.sql \
                    ${config.secrets.fullPaths."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;
  };
}