Add mysql replication

5 files changed, 317 insertions, 11 deletions

diff --git a/modules/private/databases/default.nix b/modules/private/databases/default.nix
index d1d6a2b..7371410 100644
--- a/modules/private/databases/default.nix
+++ b/modules/private/databases/default.nix
@@ -21,6 +21,10 @@ in
         inherit (myconfig.env.ldap) host base;
         inherit (myconfig.env.databases.mysql.pam) dn filter password;
       };
+      replicationLdapConfig = {
+        inherit (myconfig.env.ldap) host base;
+        inherit (myconfig.env.ldap.eldiron) dn password;
+      };
       credentials.root = myconfig.env.databases.mysql.systemUsers.root;
     };
 
diff --git a/modules/private/databases/mariadb.nix b/modules/private/databases/mariadb.nix
index 4293f02..632306c 100644
--- a/modules/private/databases/mariadb.nix
+++ b/modules/private/databases/mariadb.nix
@@ -34,6 +34,17 @@ in {
           };
         };
       };
+      replicationLdapConfig = lib.mkOption {
+        description = "LDAP configuration to allow replication";
+        type = lib.types.submodule {
+          options = {
+            host = lib.mkOption { type = lib.types.str; };
+            base = lib.mkOption { type = lib.types.str; };
+            dn = lib.mkOption { type = lib.types.str; };
+            password = lib.mkOption { type = lib.types.str; };
+          };
+        };
+      };
       dataDir = lib.mkOption {
         type = lib.types.path;
         default = "/var/lib/mysql";
@@ -72,6 +83,13 @@ in {
     # User identified by LDAP:
     # CREATE USER foo@% IDENTIFIED VIA pam USING 'mysql' REQUIRE SSL;
     # CREATE USER foo@localhost IDENTIFIED VIA pam USING 'mysql';
+
+    # To create a user (host) for replication:
+    # CREATE USER 'host'@'%' IDENTIFIED VIA pam USING 'mysql_replication' REQUIRE SSL;
+    # GRANT REPLICATION SLAVE, REPLICATION CLIENT, RELOAD, LOCK TABLES, SELECT, SHOW VIEW ON *.* TO 'host'@'%';
+    # (the lock/select grant permits to let the replication host handle
+    # the initial fetch of the database)
+    # % should be valid for both localhost (for cron dumps) and the origin host.
     services.mysql = {
       enable = true;
       package = cfg.package;
@@ -80,6 +98,10 @@ in {
         ssl_ca = ${pkgs.cacert}/etc/ssl/certs/ca-bundle.crt
         ssl_key = ${config.security.acme.directory}/mysql/key.pem
         ssl_cert = ${config.security.acme.directory}/mysql/fullchain.pem
+
+        # for replication
+        log-bin=mariadb-bin
+        server-id=1
         '';
     };
 
@@ -120,17 +142,22 @@ in {
           ssl start_tls
         '';
       }
+      {
+        dest = "mysql/pam_replication";
+        permissions = "0400";
+        user = "mysql";
+        group = "mysql";
+        text =  with cfg.replicationLdapConfig; ''
+          host ${host}
+          base ${base}
+          binddn ${dn}
+          bindpw ${password}
+          pam_login_attribute cn
+          ssl start_tls
+        '';
+      }
     ];
 
-    services.cron = {
-      enable = true;
-      systemCronJobs = [
-        ''
-          30 1,13 * * * root ${cfg.package}/bin/mysqldump --defaults-file=${config.secrets.location}/mysql/mysqldump --all-databases > ${cfg.dataDir}/backup.sql
-        ''
-      ];
-    };
-
     security.pam.services = let
       pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
     in [
@@ -142,8 +169,14 @@ in {
           account required ${pam_ldap} config=${config.secrets.location}/mysql/pam
           '';
       }
+      {
+        name = "mysql_replication";
+        text = ''
+          auth    required ${pam_ldap} config=${config.secrets.location}/mysql/pam_replication
+          account required ${pam_ldap} config=${config.secrets.location}/mysql/pam_replication
+          '';
+      }
     ];
 
   };
 }
-
diff --git a/modules/private/databases/mariadb_replication.nix b/modules/private/databases/mariadb_replication.nix
new file mode 100644
index 0000000..23648bb
--- /dev/null
+++ b/modules/private/databases/mariadb_replication.nix
@@ -0,0 +1,237 @@
+{ 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}
+        '';
+      }
+    ]) 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
+
+              ${hcfg.package}/bin/mysqldump \
+                --defaults-file=${config.secrets.location}/mysql_replication/${name}/mysqldump \
+                -S /run/mysqld_${name}/mysqld.sock \
+                --gtid \
+                --master-data \
+                --flush-privileges \
+                --all-databases > ${backupDir}/$(${pkgs.coreutils}/bin/date -Iseconds).sql
+            '';
+          u = pkgs.callPackage ./utils.nix {};
+          cleanup_script = pkgs.writeScript "cleanup_mysql_${name}" (u.exponentialDumps 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]
+          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;
+  };
+}
+
diff --git a/modules/private/databases/postgresql_replication.nix b/modules/private/databases/postgresql_replication.nix
index 145fcac..cc32c2b 100644
--- a/modules/private/databases/postgresql_replication.nix
+++ b/modules/private/databases/postgresql_replication.nix
@@ -115,9 +115,11 @@ in
 
               ${hcfg.package}/bin/pg_dumpall -h ${dataDir} -f ${backupDir}/$(${pkgs.coreutils}/bin/date -Iseconds).sql
             '';
+          u = pkgs.callPackage ./utils.nix {};
+          cleanup_script = pkgs.writeScript "cleanup_postgresql_${name}" (u.keepLastNDumps backupDir 12);
         in [
           "0 22,4,10,16 * * * postgres ${backup_script}"
-          "0 3 * * * postgres ${pkgs.coreutils}/bin/rm -f $(${pkgs.coreutils}/bin/ls -1 ${backupDir}/*.sql | ${pkgs.coreutils}/bin/sort -r | ${pkgs.gnused}/bin/sed -e '1,12d')"
+          "0 3 * * * postgres ${cleanup_script}"
         ]) cfg.hosts);
     };
 
diff --git a/modules/private/databases/utils.nix b/modules/private/databases/utils.nix
new file mode 100644
index 0000000..c296f49
--- /dev/null
+++ b/modules/private/databases/utils.nix
@@ -0,0 +1,30 @@
+{ pkgs }:
+{
+  keepLastNDumps = backupDir: n: ''
+    #!${pkgs.stdenv.shell}
+
+    cd ${backupDir}
+    ${pkgs.coreutils}/bin/rm -f \
+      $(${pkgs.coreutils}/bin/ls -1 *.sql \
+        | ${pkgs.coreutils}/bin/sort -r \
+        | ${pkgs.gnused}/bin/sed -e '1,${builtins.toString n}d')
+    '';
+  exponentialDumps = backupDir: let
+    log2rotateSrc = builtins.fetchGit {
+      url = "https://github.com/avian2/pylog2rotate";
+      ref = "master";
+      rev = "061f0564757289d3bea553b16f8fd5c4a0319c5e";
+    };
+    log2rotate = pkgs.writeScript "log2rotate" ''
+      #!${pkgs.python3}/bin/python
+
+      ${builtins.readFile "${log2rotateSrc}/log2rotate.py"}
+    '';
+  in ''
+    #!${pkgs.stdenv.shell}
+
+    cd ${backupDir}
+    ${pkgs.coreutils}/bin/rm -f $(ls -1 *.sql | grep -v 'T22:' | sort -r | sed -e '1,12d')
+    ${pkgs.coreutils}/bin/rm -f $(ls -1 *T22*.sql | ${log2rotate} --skip 7 --fuzz 7 --delete --format='%Y-%m-%dT%H:%M:%S+00:00.sql')
+    '';
+}