Moved everything into ./modules

6 files changed, 1301 insertions, 0 deletions

diff --git a/modules/apps/mariadb/default.nix b/modules/apps/mariadb/default.nix
new file mode 100644
index 0000000..cdf5d92
--- /dev/null
+++ b/modules/apps/mariadb/default.nix
@@ -0,0 +1,442 @@
+{ config, pkgs, lib, ... }:
+let
+  inherit (builtins)
+    attrNames filter isBool isInt isList isPath isString length replaceStrings
+    toString ;
+
+  inherit (lib)
+    mkOption mkEnableOption mkIf types toUpper unique
+    optionalString hasPrefix concatStringsSep splitString flatten
+    concatMapStrings concatMapStringsSep concatStrings mapAttrsToList filterAttrs;
+
+  inherit (types)
+    attrsOf either int lines listOf package str submodule ;
+
+  cfg = config.nixsap.apps.mariadb;
+
+  getDirs = l: map dirOf (filter (p: p != null && hasPrefix "/" p) l);
+  mydirs = [ cfg.mysqld.datadir ] ++ getDirs [ cfg.mysqld.log_bin cfg.mysqld.relay_log ];
+  explicit = filterAttrs (n: v: n != "_module" && v != null);
+  hasMasters = (explicit cfg.replicate) != {};
+  concatNonEmpty = sep: list: concatStringsSep sep (filter (s: s != "") list);
+
+  # XXX /run/mysqld/mysqld.sock is the default socket
+  rundir   = "/run/mysqld";
+  initFile = pkgs.writeText "init" ''
+    CREATE USER IF NOT EXISTS '${cfg.user}'@'localhost' IDENTIFIED VIA unix_socket;
+    GRANT ALL ON *.* TO '${cfg.user}'@'localhost' WITH GRANT OPTION;
+  '';
+
+  mkIgnoreTablesList = quotes: { databases, ignore-tables, ... }:
+    let
+      q = optionalString quotes "`";
+      hasDot = t: 2 == length (splitString "." t);
+      all-tbl = filter (t: ! hasDot t) ignore-tables;
+      db-tbl = (filter hasDot ignore-tables) ++
+                flatten (map (t: map (d: "${q}${d}${q}.${q}${t}${q}") databases) all-tbl);
+    in unique db-tbl;
+
+  mkEntry = name: value:
+    let
+      showList = l: concatMapStringsSep "," (toString) (unique l);
+      optimizer_switch = a:
+        showList (mapAttrsToList (n: v:
+            "${n}=${if v then "on" else "off"}"
+          ) (explicit a));
+    in if hasPrefix "skip" name then (optionalString value name)
+       else if name == "optimizer_switch" then "${name} = ${optimizer_switch value}"
+       else if isBool value then "${name} = ${if value then "ON" else "OFF"}"
+       else if isInt value then "${name} = ${toString value}"
+       else if isList value then "${name} = ${showList value}"
+       else if isString value then "${name} = ${value}"
+       else abort "Unrecognized option ${name}";
+
+  show = n: v:
+         if isBool v then (if v then "1" else "0")
+    else if isInt v then toString v
+    else if isString v then "'${v}'"
+    else if isPath v then "'${v}'"
+    else abort "Unrecognized option ${n}";
+
+  mkReplOpt = ch: args@{databases, ignore-databases, ...}:
+    let wild_do_table = concatMapStringsSep "\n" (d:
+                    "${ch}.replicate_wild_do_table = ${d}.%"
+                   ) databases;
+        ignore_table = concatMapStringsSep "\n" (t:
+                    "${ch}.replicate_ignore_table = ${t}"
+                   ) (mkIgnoreTablesList false args);
+        ignore_db = concatMapStringsSep "\n" (d:
+                    "${ch}.replicate_ignore_db = ${d}"
+                   ) ignore-databases;
+    in ''
+      ${ignore_db}
+      ${ignore_table}
+      ${wild_do_table}
+    '';
+
+  mkDynamicReplOpt = ch: args@{databases, ignore-databases, ...}:
+    ''
+      SET default_master_connection = "${ch}";
+      SET GLOBAL replicate_ignore_db = "${concatStringsSep "," ignore-databases}";
+      SET GLOBAL replicate_wild_do_table = "${concatMapStringsSep "," (d: "${d}.%") databases}";
+      SET GLOBAL replicate_ignore_table = "${concatMapStringsSep "," (t: "${t}") (mkIgnoreTablesList false args)}";
+    '';
+
+  replCnf = pkgs.writeText "mysqld-repl.cnf" ''
+      [mysqld]
+      ${concatNonEmpty "\n" (mapAttrsToList mkReplOpt (explicit cfg.replicate))}
+    '';
+
+  mysqldCnf =
+    if hasMasters && (cfg.mysqld.server_id == null || cfg.mysqld.server_id < 1)
+    then throw "Misconfigured slave: server_id was not set to a positive integer"
+    else pkgs.writeText "mysqld.cnf" ''
+      [mysqld]
+      basedir = ${cfg.package}
+      init_file = ${initFile}
+      pid_file = ${rundir}/mysqld.pid
+      plugin_load = unix_socket=auth_socket.so
+      plugin_load_add = server_audit=server_audit.so
+      ${concatNonEmpty "\n" (mapAttrsToList mkEntry (explicit cfg.mysqld))}
+      ${optionalString hasMasters "!include ${replCnf}"}
+    '';
+
+  await = pkgs.writeBashScript "await" ''
+    count=0
+    while ! mysql -e ';' 2>/dev/null; do
+      if ! (( count % 60 )); then
+        mysql -e ';'
+      fi
+      sleep 5s
+      (( ++count ))
+    done
+  '';
+
+  conf = pkgs.writeBashScriptBin "mariadb-conf"
+    ''
+      set -euo pipefail
+      trap "" SIGHUP
+      ${await}
+      ${optionalString (cfg.configure' != "") ''
+        tmp=$(mktemp)
+        trap 'rm -f "$tmp"' EXIT
+        mysql -N mysql < ${pkgs.writeText "mariadb-make-conf2.sql" cfg.configure'} > "$tmp"
+        mysql -v mysql < "$tmp"
+      ''}
+      mysql -v mysql < ${pkgs.writeText "mariadb-conf.sql" cfg.configure}
+    '';
+
+  maintenance = pkgs.writeBashScriptBin "mariadb-maint" ''
+    set -euo pipefail
+    trap "" SIGHUP
+    ${await}
+    ${optionalString hasMasters "mysql -e 'STOP ALL SLAVES SQL_THREAD'"}
+    mysql_upgrade --user=${cfg.user}
+    mysql_tzinfo_to_sql "$TZDIR" | mysql mysql
+    mysql mysql < ${./procedures.sql}
+    cat <<'__SQL__' | mysql
+    DROP DATABASE IF EXISTS test;
+    DELETE FROM mysql.db WHERE Db='test' OR Db='test%';
+    DELETE FROM mysql.user WHERE User='${cfg.user}' AND Host NOT IN ('localhost');
+    DELETE FROM mysql.user WHERE User=${"''"};
+    DELETE FROM mysql.user WHERE User='root';
+    DELETE FROM mysql.proxies_priv WHERE User='root';
+    FLUSH PRIVILEGES;
+    ${concatMapStrings (db: ''
+    CREATE DATABASE IF NOT EXISTS `${db}`;
+    '') cfg.databases}
+    __SQL__
+    ${optionalString hasMasters "mysql -e 'START ALL SLAVES'"}
+  '';
+
+  changeMaster =
+    let
+      do = ch: opts:
+        let
+          masterOptions = filterAttrs (n: _: n != "password-file") (explicit opts.master);
+          masterOptionName = n: ''MASTER_${toUpper (replaceStrings ["-"] ["_"] n)}'';
+          changeMaster = "CHANGE MASTER '${ch}' TO " + (concatStringsSep ", " (mapAttrsToList (n: v:
+              "${masterOptionName n}=${show n v}") masterOptions)) + ";";
+        in pkgs.writeBashScript "change-master-${ch}" ''
+          cat <<'__SQL__'
+          ${changeMaster}
+          ${mkDynamicReplOpt ch opts}
+          __SQL__
+          ${optionalString (opts.master.password-file != null) ''
+            pwd=$(cat '${opts.master.password-file}')
+            echo "CHANGE MASTER '${ch}' TO MASTER_PASSWORD='$pwd';"''}
+        '';
+
+    in pkgs.writeBashScript "changeMaster" (
+      concatStringsSep "\n" (mapAttrsToList (ch: opts: ''
+        [ "$1" = ${ch} ] && exec ${do ch opts}
+      '') (explicit cfg.replicate))
+    );
+
+  importDump =
+    let
+      do = ch: opts:
+        let
+          cnf = "${rundir}/master-${ch}.cnf";
+          mysqldumpOptions = filterAttrs (n: _: n != "password-file" && n != "path")
+            (explicit opts.mysqldump);
+          binary = if opts.mysqldump.path != null then opts.mysqldump.path else "mysqldump";
+          mysqldump = concatStringsSep " " (
+              [ binary "--defaults-file=${cnf}" "--skip-comments" "--force" ]
+              ++ mapAttrsToList (n: v: "--${n}=${show n v}") mysqldumpOptions);
+          databases = concatStringsSep " " ([ "--databases" ] ++ opts.databases);
+          ignore-tables = concatMapStringsSep " " (t: "--ignore-table=${t}") (mkIgnoreTablesList false opts);
+        in pkgs.writeBashScript "import-${ch}" ''
+          set -euo pipefail
+          touch '${cnf}'
+          trap "rm -f '${cnf}'" EXIT
+          trap "exit 255" TERM INT
+          chmod 0600 '${cnf}'
+          ${optionalString (opts.mysqldump.password-file != null) ''
+            printf '[client]\npassword=' > '${cnf}'
+            cat '${opts.mysqldump.password-file}' >> '${cnf}'
+          ''}
+          echo 'SET default_master_connection="${ch}";'
+          ${optionalString (!cfg.mysqld.log_slave_updates) "echo 'SET sql_log_bin=0;'"}
+          ${mysqldump} --master-data=0 --no-data ${databases}
+          ${mysqldump} --master-data=1 ${ignore-tables} ${databases}
+        '';
+    in pkgs.writeBashScript "importDump" (
+      concatStringsSep "\n" (mapAttrsToList (ch: opts: ''
+        [ "$1" = ${ch} ] && exec ${do ch opts}
+      '') (explicit cfg.replicate))
+    );
+
+  watchdog = pkgs.writeBashScript "slave-watchdog"
+    (import ./slave-watchdog.nix {inherit importDump changeMaster;});
+
+  slaves =
+    let
+      channels = attrNames (explicit cfg.replicate);
+      truncate = ch: concatMapStringsSep "\n"
+        (t: "TRUNCATE TABLE ${t};") (mkIgnoreTablesList true cfg.replicate.${ch});
+      truncateIgnored = pkgs.writeText "truncate.sql"
+        (concatMapStringsSep "\n" truncate channels);
+      old = "${rundir}/channels";
+      new = pkgs.writeText "channels.new" (concatMapStringsSep "\n"
+        (ch: "${ch}:${cfg.replicate.${ch}.master.host}") channels);
+    in pkgs.writeBashScriptBin "mariadb-slaves" ''
+      set -euo pipefail
+      rm -f ${rundir}/*.lock
+      ${await}
+      touch ${old}
+      chmod 0600 ${old}
+      trap 'rm -f ${old}' EXIT
+      mysql -e 'SHOW ALL SLAVES STATUS\G' \
+        | awk '/Connection_name:/ {printf $2 ":"}; /Master_Host:/ {print $2}' \
+        | sort > ${old}
+      obsolete=$(comm -23 ${old} ${new} | cut -d: -f1)
+      for ch in $obsolete; do
+        echo "Deleting obsolete slave $ch"
+        mysql -e "CALL mysql.resetSlave('$ch')"
+      done
+      ${optionalString hasMasters ''
+        mysql -f < ${truncateIgnored} || echo '(errors ignored)' >&2
+        export PARALLEL_SHELL=${pkgs.bash}/bin/bash
+        export HOME='${rundir}'
+        {
+          while true; do
+            printf "${concatStringsSep "\\n" channels}\n"
+            sleep 10m
+          done
+        } | parallel \
+            --halt-on-error 0 \
+            --jobs '${toString cfg.slaveWatchdogs}' \
+            --line-buffer \
+            --no-notice \
+            --tagstr '* {}:' \
+            'flock -E 0 -n ${rundir}/master-{}.lock ${watchdog} {}'
+        ''
+      }
+    '';
+
+  all-keys = unique (filter (f: f != null && hasPrefix "/run/keys/" f ) (flatten (
+      mapAttrsToList (ch: {master, mysqldump, ...}:
+        [ master.password-file
+          master.ssl-key
+          mysqldump.password-file
+          mysqldump.ssl-key
+        ]) (explicit cfg.replicate)
+    ) ++ [
+      cfg.mysqld.ssl_key
+    ]));
+
+in {
+
+  imports = [ ./roles.nix ];
+
+  options.nixsap = {
+    apps.mariadb = {
+      enable = mkEnableOption "MySQL";
+
+      user = mkOption {
+        description = "User to run as";
+        default = "mariadb";
+        type = str;
+      };
+
+      package = mkOption {
+        description = "MariaDB Package (10.1.x)";
+        type = package;
+        default = pkgs.mariadb;
+      };
+
+      replicate = mkOption {
+        type = attrsOf (submodule (import ./replicate.nix));
+        default = {};
+        description = "Replication channels";
+      };
+
+      slaveWatchdogs = mkOption {
+        type = either str int;
+        default = "80%";
+        description = ''
+          Number of parallel slave monitoring and recovery processes.
+          In the format of GNU Parallel, e. g. "100%", -1. +3, 7, etc.
+        '';
+      };
+
+      mysqld = mkOption {
+        type = submodule (import ./mysqld.nix);
+        default = {};
+        description = "mysqld options";
+      };
+
+      databases = mkOption {
+        description = "Databases to create if not exist";
+        type = listOf str;
+        default = [];
+      };
+
+      configure = mkOption {
+        type = lines;
+        default = "";
+        description = ''
+          Any SQL statements to execute, typically GRANT / REVOKE etc.
+          This is executed in contect of the `mysql` database.
+        '';
+        example = ''
+          CREATE USER IF NOT EXISTS 'icinga'@'%' IDENTIFIED BY PASSWORD '*AC8C3BDA823EECFF90A8381D554232C7620345B3';
+          GRANT USAGE ON *.* TO 'icinga'@'%' REQUIRE SSL;
+          REVOKE ALL, GRANT OPTION FROM 'icinga'@'%';
+          GRANT PROCESS, REPLICATION CLIENT, SHOW DATABASES ON *.* TO 'icinga'@'%';
+          GRANT SELECT ON mysql.* TO 'icinga'@'%';
+        '';
+      };
+
+      configure' = mkOption {
+        type = lines;
+        default = "";
+        internal = true;
+        description = ''
+          SQL statements that generate other SQL statements to be executed.
+          Those generated statements will be executed before `configure`.
+        '';
+        example = ''
+          SELECT CONCAT('GRANT SELECT ON `', table_schema, '`.`', table_name, '` TO \'_oms_package_vn\';')
+          FROM information_schema.tables WHERE
+          table_schema LIKE '%oms_live_vn' AND
+          table_name LIKE 'oms_package%';
+        '';
+      };
+    };
+  };
+
+  config = mkIf cfg.enable {
+    environment.systemPackages = [ cfg.package ];
+    nixsap.system.users.daemons = [ cfg.user ];
+    nixsap.deployment.keyrings.${cfg.user} = all-keys;
+
+    nixsap.apps.mariadb.configure = concatMapStringsSep "\n"
+      (n: ''
+        CREATE USER IF NOT EXISTS '${n}'@'localhost' IDENTIFIED VIA unix_socket;
+        REVOKE ALL, GRANT OPTION FROM '${n}'@'localhost';
+        GRANT SELECT, EXECUTE ON mysql.* TO '${n}'@'localhost';
+        GRANT PROCESS, REPLICATION CLIENT, SHOW DATABASES, SHOW VIEW ON *.* TO '${n}'@'localhost';
+        '') config.nixsap.system.users.sysops;
+
+    systemd.services.mariadb-slaves = {
+      description = "MariaDB slaves watchdog";
+      requires = [ "mariadb.service" ];
+      after = [ "mariadb.service" "mariadb-maintenance.service" ];
+      wantedBy = [ "multi-user.target" ];
+      path = with pkgs; [ gnused gawk cfg.package utillinux parallel ];
+      serviceConfig = {
+        ExecStart = "${slaves}/bin/mariadb-slaves";
+        User = cfg.user;
+      } // (if hasMasters
+        then {
+          Restart = "always";
+        }
+        else {
+          Type = "oneshot";
+        });
+    };
+
+    systemd.services.mariadb-maintenance = {
+      description = "MariaDB maintenance";
+      after = [ "mariadb.service" ];
+      wantedBy = [ "multi-user.target" ];
+      path = [ cfg.package ];
+      serviceConfig = {
+        ExecStart = "${maintenance}/bin/mariadb-maint";
+        User = cfg.user;
+        Type = "oneshot";
+        RemainAfterExit = true;
+      };
+    };
+
+    systemd.services.mariadb-conf = {
+      description = "MariaDB configuration";
+      after = [ "mariadb.service" "mariadb-maintenance.service" ];
+      wantedBy = [ "multi-user.target" ];
+      path = [ cfg.package ];
+      serviceConfig = {
+        ExecStart = "${conf}/bin/mariadb-conf";
+        User = cfg.user;
+        Type = "oneshot";
+        RemainAfterExit = true;
+      };
+    };
+
+    systemd.services.mariadb = {
+      description = "MariaDB server";
+      wantedBy = [ "multi-user.target" ];
+      wants = [ "keys.target" ];
+      after = [ "keys.target" "network.target" "local-fs.target" ];
+      path = [ pkgs.inetutils ];
+      environment = {
+        UMASK = "0640";
+        UMASK_DIR = " 0750";
+      };
+      preStart = ''
+        mkdir -p '${rundir}'
+        chmod 0700 '${rundir}'
+        mkdir -p ${concatMapStringsSep " " (d: "'${d}'") mydirs}
+        if [ ! -f '${cfg.mysqld.datadir}/mysql/user.MYI' ]; then
+          rm -rf '${cfg.mysqld.datadir}/mysql'
+          ${cfg.package}/bin/mysql_install_db --defaults-file=${mysqldCnf}
+        fi
+        chown -Rc '${cfg.user}':$(id -g -n '${cfg.user}') '${rundir}' ${concatMapStringsSep " " (d: "'${d}'") mydirs}
+        chmod -Rc u=rwX,g=rX,o= ${concatMapStringsSep " " (d: "'${d}'") mydirs}
+        chmod 0755 '${rundir}'
+      '';
+
+      serviceConfig = {
+        ExecStart = "${cfg.package}/bin/mysqld --defaults-file=${mysqldCnf}";
+        PermissionsStartOnly = true;
+        User = cfg.user;
+        Restart = "always";
+        TimeoutSec = 0; # XXX it can take hours to shutdown, and much more to start if you kill shutdown :-D
+        LimitNOFILE = "infinity";
+        LimitMEMLOCK = "infinity";
+        OOMScoreAdjust = -1000;
+      };
+    };
+  };
+}
diff --git a/modules/apps/mariadb/mysqld.nix b/modules/apps/mariadb/mysqld.nix
new file mode 100644
index 0000000..d66d96d
--- /dev/null
+++ b/modules/apps/mariadb/mysqld.nix
@@ -0,0 +1,285 @@
+{ lib, ... }:
+with lib;
+with lib.types;
+
+let
+  engines = [
+    "Archive"
+    "Aria"
+    "Blackhole"
+    "CSV"
+    "Example"
+    "InnoDB"
+    "Memory"
+    "MyISAM"
+  ];
+
+  syslog-facilities = [
+    "LOG_USER"
+    "LOG_MAIL"
+    "LOG_DAEMON"
+    "LOG_AUTH"
+    "LOG_SYSLOG"
+    "LOG_LPR"
+    "LOG_NEWS"
+    "LOG_UUCP"
+    "LOG_CRON"
+    "LOG_AUTHPRIV"
+    "LOG_FTP"
+    "LOG_LOCAL0"
+    "LOG_LOCAL1"
+    "LOG_LOCAL2"
+    "LOG_LOCAL3"
+    "LOG_LOCAL4"
+    "LOG_LOCAL5"
+    "LOG_LOCAL6"
+    "LOG_LOCAL7"
+  ];
+
+  syslog-priorities = [
+    "LOG_EMERG"
+    "LOG_ALERT"
+    "LOG_CRIT"
+    "LOG_ERR"
+    "LOG_WARNING"
+    "LOG_NOTICE"
+    "LOG_INFO"
+    "LOG_DEBUG"
+  ];
+
+  sql-modes = [
+    "ALLOW_INVALID_DATES"
+    "ANSI"
+    "ANSI_QUOTES"
+    "DB2"
+    "ERROR_FOR_DIVISION_BY_ZERO"
+    "HIGH_NOT_PRECEDENCE"
+    "IGNORE_BAD_TABLE_OPTIONS"
+    "IGNORE_SPACE"
+    "MAXDB"
+    "MSSQL"
+    "MYSQL323"
+    "MYSQL40"
+    "NO_AUTO_CREATE_USER"
+    "NO_AUTO_VALUE_ON_ZERO"
+    "NO_BACKSLASH_ESCAPES"
+    "NO_DIR_IN_CREATE"
+    "NO_ENGINE_SUBSTITUTION"
+    "NO_FIELD_OPTIONS"
+    "NO_KEY_OPTIONS"
+    "NO_TABLE_OPTIONS"
+    "NO_UNSIGNED_SUBTRACTION"
+    "NO_ZERO_DATE"
+    "NO_ZERO_IN_DATE"
+    "ONLY_FULL_GROUP_BY"
+    "ORACLE"
+    "PAD_CHAR_TO_FULL_LENGTH"
+    "PIPES_AS_CONCAT"
+    "POSTGRESQL"
+    "REAL_AS_FLOAT"
+    "STRICT_ALL_TABLES"
+    "STRICT_TRANS_TABLES"
+    "TRADITIONAL"
+  ];
+
+  flush-methods = [
+    "ALL_O_DIRECT"
+    "O_DIRECT"
+    "O_DSYNC"
+    "fdatasync"
+  ];
+
+  default = v: type: mkOption { type = type; default = v; };
+  mandatory = type: mkOption { inherit type; };
+  optional = type: mkOption { type = nullOr type; default = null; };
+  set = opts: mkOption { type = nullOr (submodule opts); default = null; };
+
+  oneOrMore = l: let en = enum' l; in either en (uniq (listOf en));
+
+  # XXX https://github.com/NixOS/nixpkgs/issues/9826
+  enum' = values:
+    let show = v: let t = builtins.typeOf v;
+            in if t == "string" then ''"${v}"''
+          else if t == "int" then builtins.toString v
+          else ''<${t}>'';
+    in mkOptionType {
+      name = "one of ${concatStringsSep ", " (map show values)}";
+      check = flip elem values;
+      merge = mergeOneOption;
+    };
+
+  isFloat = x: builtins.match "^[0-9]+(\\.[0-9]+)?$" (builtins.toString x) != null;
+
+  float = mkOptionType {
+    name = "positive float";
+    check = isFloat;
+  };
+
+  # https://mariadb.com/kb/en/mariadb/optimizer-switch/
+  optimizer = {
+    options = {
+      derived_merge                 = optional bool;
+      derived_with_keys             = optional bool;
+      exists_to_in                  = optional bool;
+      extended_keys                 = optional bool;
+      firstmatch                    = optional bool;
+      in_to_exists                  = optional bool;
+      index_merge                   = optional bool;
+      index_merge_intersection      = optional bool;
+      index_merge_sort_intersection = optional bool;
+      index_merge_sort_union        = optional bool;
+      index_merge_union             = optional bool;
+      join_cache_bka                = optional bool;
+      join_cache_hashed             = optional bool;
+      join_incremental              = optional bool;
+      loosescan                     = optional bool;
+      materialization               = optional bool;
+      mrr                           = optional bool;
+      mrr_cost_based                = optional bool;
+      mrr_sort_keys                 = optional bool;
+      optimize_join_buffer_size     = optional bool;
+      outer_join_with_cache         = optional bool;
+      partial_match_rowid_merge     = optional bool;
+      partial_match_table_scan      = optional bool;
+      semijoin                      = optional bool;
+      semijoin_with_cache           = optional bool;
+      subquery_cache                = optional bool;
+      table_elimination             = optional bool;
+    };
+  };
+
+in {
+  options = {
+    binlog_checksum                         = optional (enum ["NONE" "CRC32"]);
+    binlog_commit_wait_count                = optional int;
+    binlog_commit_wait_usec                 = optional int;
+    binlog_direct_non_transactional_updates = optional bool;
+    binlog_format                           = optional (enum ["ROW" "MIXED" "STATEMENT"]);
+    binlog_optimize_thread_scheduling       = optional bool;
+    binlog_row_image                        = optional (enum ["FULL" "NOBLOB" "MINIMAL"]);
+    binlog_stmt_cache_size                  = optional int;
+    character_set_server                    = optional str;
+    collation_server                        = optional str;
+    connect_timeout                         = optional int;
+    datadir                                 = mandatory path;
+    default_storage_engine                  = optional (enum engines);
+    default_time_zone                       = optional str;
+    encrypt_binlog                          = optional bool;
+    event_scheduler                         = optional (either bool (enum ["DISABLED"]));
+    expire_logs_days                        = optional int;
+    general_log                             = optional bool;
+    group_concat_max_len                    = optional int;
+    ignore_db_dirs                          = optional (uniq (listOf str));
+    init_connect                            = optional str;
+    init_slave                              = optional str;
+    innodb_autoinc_lock_mode                = optional (enum' [ 0 1 2 ]);
+    innodb_buffer_pool_dump_at_shutdown     = optional bool;
+    innodb_buffer_pool_instances            = optional int;
+    innodb_buffer_pool_load_at_startup      = optional bool;
+    innodb_buffer_pool_size                 = optional int;
+    innodb_doublewrite                      = optional bool;
+    innodb_file_format                      = optional (enum ["antelope" "barracuda"]);
+    innodb_file_per_table                   = optional bool;
+    innodb_flush_log_at_trx_commit          = optional (enum' [0 1 2]);
+    innodb_flush_method                     = optional (enum flush-methods);
+    innodb_io_capacity                      = optional int;
+    innodb_io_capacity_max                  = optional int;
+    innodb_lock_wait_timeout                = optional int;
+    innodb_log_file_size                    = optional int;
+    innodb_open_files                       = optional int;
+    innodb_read_io_threads                  = optional int;
+    innodb_rollback_on_timeout              = optional bool;
+    innodb_thread_concurrency               = optional int;
+    innodb_write_io_threads                 = optional int;
+    interactive_timeout                     = optional int;
+    join_buffer_size                        = optional int;
+    local_infile                            = optional bool;
+    log_bin                                 = optional path;
+    log_bin_index                           = optional str;
+    log_output                              = optional (oneOrMore ["TABLE" "FILE"]);
+    log_queries_not_using_indexes           = optional bool;
+    log_slave_updates                       = default false bool;
+    log_slow_rate_limit                     = optional int;
+    log_slow_verbosity                      = optional (enum' ["query_plan" "innodb" "explain"]);
+    log_warnings                            = optional (enum' [ 0 1 2 3 ]);
+    long_query_time                         = optional float;
+    max_allowed_packet                      = optional int;
+    max_binlog_cache_size                   = optional int;
+    max_binlog_size                         = optional int;
+    max_binlog_stmt_cache_size              = optional int;
+    max_connect_errors                      = optional int;
+    max_connections                         = optional int;
+    max_heap_table_size                     = optional int;
+    max_relay_log_size                      = optional int;
+    max_user_connections                    = optional int;
+    net_read_timeout                        = optional int;
+    net_write_timeout                       = optional int;
+    optimizer_switch                        = set optimizer;
+    port                                    = default 3306 int;
+    query_alloc_block_size                  = optional int;
+    query_cache_limit                       = optional int;
+    query_cache_min_res_unit                = optional int;
+    query_cache_size                        = optional int;
+    query_cache_strip_comments              = optional bool;
+    query_cache_type                        = optional (enum' [ 0 1 "DEMAND"]);
+    query_cache_wlock_invalidate            = optional bool;
+    query_prealloc_size                     = optional int;
+    relay_log                               = optional path;
+    relay_log_index                         = optional str;
+    relay_log_purge                         = optional bool;
+    relay_log_recovery                      = optional bool;
+    relay_log_space_limit                   = optional int;
+    server_audit_events                     = optional (uniq (listOf (enum ["CONNECT" "QUERY" "TABLE" "QUERY_DDL" "QUERY_DML"])));
+    server_audit_excl_users                 = optional (listOf str);
+    server_audit_file_path                  = optional path;
+    server_audit_file_rotate_size           = optional int;
+    server_audit_file_rotations             = optional int;
+    server_audit_incl_users                 = optional (listOf str);
+    server_audit_logging                    = optional bool;
+    server_audit_output_type                = optional (enum ["SYSLOG" "FILE"]);
+    server_audit_query_log_limit            = optional int;
+    server_audit_syslog_facility            = optional (enum syslog-facilities);
+    server_audit_syslog_ident               = optional str;
+    server_audit_syslog_info                = optional str;
+    server_audit_syslog_priority            = optional (enum syslog-priorities);
+    server_id                               = optional int;
+    skip_log_bin                            = optional bool;
+    skip_name_resolve                       = optional bool;
+    skip_networking                         = optional bool;
+    slave_compressed_protocol               = optional bool;
+    slave_ddl_exec_mode                     = optional (enum ["IDEMPOTENT" "STRICT"]);
+    slave_domain_parallel_threads           = optional int;
+    slave_exec_mode                         = optional (enum ["IDEMPOTENT" "STRICT"]);
+    slave_load_tmpdir                       = optional path;
+    slave_max_allowed_packet                = optional int;
+    slave_net_timeout                       = optional int;
+    slave_parallel_max_queued               = optional int;
+    slave_parallel_mode                     = optional (enum ["conservative" "optimisitic" "none" "aggressive" "minimal"]);
+    slave_parallel_threads                  = optional int;
+    slave_skip_errors                       = optional (uniq (listOf int));
+    slave_sql_verify_checksum               = optional bool;
+    slave_transaction_retries               = optional int;
+    slow_query_log                          = optional bool;
+    slow_query_log_file                     = optional path;
+    sort_buffer_size                        = optional int;
+    sql_mode                                = optional (uniq (listOf (enum sql-modes)));
+    ssl_ca                                  = optional path;
+    ssl_capath                              = optional path;
+    ssl_cert                                = optional path;
+    ssl_cipher                              = optional str;
+    ssl_crl                                 = optional path;
+    ssl_crlpath                             = optional path;
+    ssl_key                                 = optional path;
+    table_definition_cache                  = optional int;
+    table_open_cache                        = optional int;
+    thread_cache_size                       = optional int;
+    tmp_table_size                          = optional int;
+    tmpdir                                  = optional path;
+    wait_timeout                            = optional int;
+  };
+  config = {
+    ignore_db_dirs = [ "lost+found" ];
+  };
+
+}
+
diff --git a/modules/apps/mariadb/procedures.sql b/modules/apps/mariadb/procedures.sql
new file mode 100644
index 0000000..3aabe80
--- /dev/null
+++ b/modules/apps/mariadb/procedures.sql
@@ -0,0 +1,134 @@
+-- These procedures belong to the mysql DB, e. g.
+-- CALL mysql.resetSlave('foo');
+-- Keep it simple: each procedure should be self-contained.
+
+DELIMITER $$
+
+DROP PROCEDURE IF EXISTS stopSlave $$
+CREATE PROCEDURE stopSlave (IN ch VARCHAR(64))
+  COMMENT 'Stops slave channel (both I/O and SQL threads)'
+BEGIN
+  -- Ignore ERROR 1617 (HY000): There is no master connection 'foo'
+  DECLARE EXIT HANDLER FOR 1617
+    BEGIN
+      SELECT 'No such master connection'
+        AS warning;
+    END;
+
+  SET default_master_connection = ch;
+  STOP SLAVE;
+END $$
+
+DROP PROCEDURE IF EXISTS startSlave $$
+CREATE PROCEDURE startSlave (IN ch VARCHAR(64))
+  COMMENT 'Starts slave channel (both I/O and SQL threads)'
+BEGIN
+  DECLARE EXIT HANDLER FOR 1617
+    BEGIN
+      SELECT 'No such master connection'
+        AS warning;
+    END;
+
+  SET default_master_connection = ch;
+  START SLAVE;
+END $$
+
+DROP PROCEDURE IF EXISTS kickSlave $$
+CREATE PROCEDURE kickSlave (IN ch VARCHAR(64))
+  COMMENT 'Skips the next event from the master'
+BEGIN
+  DECLARE EXIT HANDLER FOR 1617
+    BEGIN
+      SELECT 'No such master connection'
+        AS warning;
+    END;
+
+  SET default_master_connection = ch;
+  STOP SLAVE;
+  SET GLOBAL sql_slave_skip_counter = 1;
+  START SLAVE;
+END $$
+
+DROP PROCEDURE IF EXISTS pauseSlave $$
+CREATE PROCEDURE pauseSlave (IN ch VARCHAR(64))
+  COMMENT 'Stops SQL thread of the slave channel'
+BEGIN
+  DECLARE EXIT HANDLER FOR 1617
+    BEGIN
+      SELECT 'No such master connection'
+        AS warning;
+    END;
+
+  SET default_master_connection = ch;
+  STOP SLAVE SQL_THREAD;
+END $$
+
+DROP PROCEDURE IF EXISTS resetSlave $$
+CREATE PROCEDURE resetSlave (IN ch VARCHAR(64))
+  COMMENT 'Stops and deletes slave channel'
+BEGIN
+  DECLARE EXIT HANDLER FOR 1617
+    BEGIN
+      SELECT 'No such master connection'
+        AS warning;
+    END;
+
+  SET default_master_connection = ch;
+  STOP SLAVE;
+  RESET SLAVE ALL;
+END $$
+
+DROP PROCEDURE IF EXISTS stopAllSlaves $$
+CREATE PROCEDURE stopAllSlaves ()
+  COMMENT 'Stops all slaves'
+BEGIN
+  STOP ALL SLAVES;
+END $$
+
+DROP PROCEDURE IF EXISTS pauseAllSlaves $$
+CREATE PROCEDURE pauseAllSlaves ()
+  COMMENT 'Stops SQL thread of all slaves'
+BEGIN
+  STOP ALL SLAVES SQL_THREAD;
+END $$
+
+DROP PROCEDURE IF EXISTS startAllSlaves $$
+CREATE PROCEDURE startAllSlaves ()
+  COMMENT 'Starts all slaves'
+BEGIN
+  START ALL SLAVES;
+END $$
+
+DROP PROCEDURE IF EXISTS enableGeneralLog $$
+CREATE PROCEDURE enableGeneralLog ()
+BEGIN
+  SET GLOBAL general_log = ON;
+END $$
+
+DROP PROCEDURE IF EXISTS disableGeneralLog $$
+CREATE PROCEDURE disableGeneralLog ()
+BEGIN
+  SET GLOBAL general_log = OFF;
+END $$
+
+DROP PROCEDURE IF EXISTS truncateGeneralLog $$
+CREATE PROCEDURE truncateGeneralLog ()
+BEGIN
+  TRUNCATE mysql.general_log;
+END $$
+
+DROP PROCEDURE IF EXISTS truncateSlowLog $$
+CREATE PROCEDURE truncateSlowLog ()
+BEGIN
+  TRUNCATE mysql.slow_log;
+END $$
+
+DROP PROCEDURE IF EXISTS showEvents $$
+CREATE PROCEDURE showEvents ()
+  COMMENT 'Shows all events for the mysql schema'
+BEGIN
+  SHOW EVENTS IN mysql;
+END $$
+
+DELIMITER ;
+
diff --git a/modules/apps/mariadb/replicate.nix b/modules/apps/mariadb/replicate.nix
new file mode 100644
index 0000000..9f51dbf
--- /dev/null
+++ b/modules/apps/mariadb/replicate.nix
@@ -0,0 +1,87 @@
+{ config, lib, ... }:
+with lib;
+with lib.types;
+let
+  mandatory = type: mkOption { inherit type; };
+  optional = type: mkOption { type = nullOr type; default = null; };
+
+  common = foldl (a: b: a//b) {} [
+    { host                   = mandatory str; }
+    { password-file          = optional path; }
+    { port                   = optional int; }
+    { ssl                    = optional bool; }
+    { ssl-ca                 = optional path; }
+    { ssl-cert               = optional path; }
+    { ssl-key                = optional path; }
+    { ssl-verify-server-cert = optional bool; }
+    { user                   = mandatory str; }
+  ];
+
+  master.options = foldl (a: b: a//b) {} [
+    { connect-retry    = optional int; }
+    { heartbeat-period = optional int; }
+    common
+  ];
+
+  mysqldump.options = foldl (a: b: a//b) {} [
+    { compress           = optional bool; }
+    { lock-tables        = optional bool; }
+    { path               = optional path; }
+    { single-transaction = optional bool; }
+    common
+  ];
+
+in {
+  options = {
+    databases = mkOption {
+      type = listOf str;
+      description = ''
+        List of databases to dump and replicate.  This will be written as
+        `foo.replicate_wild_do_table = db.%`.
+        '';
+      example = [ "oms_live_sg" "bob_live_sg" ];
+    };
+
+    ignore-tables = mkOption {
+      type = listOf str;
+      description = ''
+        List of tables to ignore.  This will be written as
+        `foo.replicate_ignore_table = db.table`.  If database prefix is
+        omitted, expressions for all databases will be generated.
+        '';
+      example = [ "schema_updates" "bob_live_sg.locks" ];
+      default = [];
+    };
+
+    ignore-databases = mkOption {
+      type = listOf str;
+      description = ''
+        List of databases to ignore. You do not need this in most cases.
+        See http://dev.mysql.com/doc/refman/en/replication-rules.html.
+        This will be written as `foo.replicate_ignore_db = mysql`.  This is
+        useful when you want procedures in other databases, like `mysql`,
+        not to be replicated.
+        '';
+      default = [ "mysql" "test" "tmp" ];
+    };
+
+    master = mkOption { type = submodule (master); };
+    mysqldump = mkOption { type = submodule (mysqldump); };
+  };
+
+  config = {
+    mysqldump = {
+      compress           = mkDefault true;
+      host               = mkDefault config.master.host;
+      password-file      = mkDefault config.master.password-file;
+      port               = mkDefault config.master.port;
+      single-transaction = mkDefault true;
+      ssl                = mkDefault config.master.ssl;
+      ssl-ca             = mkDefault config.master.ssl-ca;
+      ssl-cert           = mkDefault config.master.ssl-cert;
+      ssl-key            = mkDefault config.master.ssl-key;
+      user               = mkDefault config.master.user;
+    };
+  };
+}
+
diff --git a/modules/apps/mariadb/roles.nix b/modules/apps/mariadb/roles.nix
new file mode 100644
index 0000000..2971242
--- /dev/null
+++ b/modules/apps/mariadb/roles.nix
@@ -0,0 +1,250 @@
+{ config, lib, pkgs, ... }:
+let
+  inherit (builtins)
+    elemAt filter isAttrs isList length trace ;
+  inherit (lib)
+    attrNames concatMapStrings concatMapStringsSep concatStrings
+    concatStringsSep filterAttrs flatten mapAttrsToList mkIf mkOption
+    optionalString replaceStrings splitString types ;
+  inherit (types)
+    attrsOf either listOf str submodule ;
+
+  explicit = filterAttrs (n: v: n != "_module" && v != null);
+
+  inherit (config.nixsap.apps.mariadb) roles;
+  basicRoles = filterAttrs (_: v: isAttrs v) roles;
+  topRoles = filterAttrs (_: v: isList v) roles;
+  allRoles = attrNames roles;
+  sqlList = concatMapStringsSep ", " (i: "'${i}'");
+
+  concatMapAttrs = f: attrs: concatStrings (mapAttrsToList f attrs);
+
+  schemaName = object: elemAt (splitString "." object) 0;
+  isSchema = object:
+    let p = splitString "." object;
+        n = length p;
+    in (n == 1)
+    || (n == 2 && (elemAt p 1) == "%")
+    || ((elemAt p 1) == "%" && (elemAt p 2) == "%");
+
+  tableName = object: elemAt (splitString "." object) 1;
+  isTable = object:
+    let p = splitString "." object;
+        n = length p;
+    in (n == 2 && (elemAt p 1) != "%")
+    || (n > 2 && (elemAt p 2) == "%");
+
+  columnName = object: elemAt (splitString "." object) 2;
+  isColumn = object:
+    let p = splitString "." object;
+        n = length p;
+    in (n > 2 && (elemAt p 2) != "%");
+
+  grant = role: privileges:
+    {
+      schemas = concatMapAttrs (priv: objects:
+          concatMapStrings (o:
+            let
+              db = schemaName o;
+              p = "${replaceStrings [" "] ["_"] priv}_priv";
+            in ''
+              SELECT 'GRANT ${priv} ON `${db}`.* TO \'${role}\';'
+              FROM information_schema.schemata -- Not really used, but for syntax and locks
+              WHERE NOT EXISTS (
+                SELECT 1 FROM db
+                WHERE db.host = ${"''"} -- role, not user
+                AND db.user = '${role}'
+                AND '${db}' LIKE db.db
+                AND db.${p} = 'Y'
+              ) LIMIT 1;
+            '') (filter isSchema (flatten [objects]))
+        ) (explicit privileges);
+
+      tables = concatMapAttrs (priv: objects:
+          concatMapStrings (o: ''
+            SELECT CONCAT('GRANT ${priv} ON `', t.table_schema, '`.`', t.table_name, '` TO \'${role}\';')
+            FROM information_schema.tables t
+            WHERE t.table_schema LIKE '${schemaName o}'
+            AND t.table_name LIKE '${tableName o}'
+            AND NOT EXISTS (
+              SELECT 1 FROM mysql.tables_priv
+              WHERE tables_priv.host = ${"''"} -- role, not user
+              AND tables_priv.user = '${role}'
+              AND tables_priv.db = t.table_schema
+              AND tables_priv.table_name = t.table_name
+              AND FIND_IN_SET('${priv}', tables_priv.table_priv) > 0
+            );
+            '') (filter isTable (flatten [objects]))
+        ) (explicit privileges);
+
+      columns = concatMapAttrs (priv: objects:
+          let colObjs = filter isColumn (flatten [objects]);
+          in optionalString ([] != colObjs) (''
+            SELECT CONCAT ('GRANT ${priv}(',
+                GROUP_CONCAT(DISTINCT c.column_name SEPARATOR ','),
+              ') ON `', c.table_schema, '`.`', c.table_name, '` TO \'${role}\';')
+            FROM information_schema.columns c WHERE (
+          '' + concatMapStringsSep " OR " (o:
+              ''
+                ( c.table_schema LIKE '${schemaName o}' AND
+                  c.table_name LIKE '${tableName o}' AND
+                  c.column_name LIKE '${columnName o}')
+              '') colObjs
+          +
+          ''
+          ) AND NOT EXISTS (
+              SELECT 1 FROM columns_priv
+              WHERE columns_priv.host = ${"''"} -- role, not user
+              AND columns_priv.user = '${role}'
+              AND columns_priv.db = c.table_schema
+              AND columns_priv.table_name = c.table_name
+              AND columns_priv.column_name = c.column_name
+              AND FIND_IN_SET('${priv}', columns_priv.column_priv) > 0
+          ) GROUP BY CONCAT(c.table_schema, c.table_name);
+          '')
+        ) (explicit privileges);
+    };
+
+  refreshRolesSQL =
+    let
+      sql = concatMapAttrs (role: privileges: ''
+        ${(grant role privileges).schemas}
+        ${(grant role privileges).tables}
+        ${(grant role privileges).columns}
+      '') basicRoles;
+    in pkgs.writeText "refresh-roles.sql" sql;
+
+  refreshRoles = pkgs.writeBashScriptBin "refreshRoles" ''
+    set -euo pipefail
+
+    doze() {
+      difference=$(($(date -d "08:00" +%s) - $(date +%s)))
+      if [ $difference -lt 0 ]; then
+          sleep $((86400 + difference))
+      else
+          sleep $difference
+      fi
+    }
+
+    while true; do
+      while ! mysql -e ';'; do
+        sleep 5s
+      done
+      tmp=$(mktemp)
+      trap 'rm -f "$tmp"' EXIT
+      mysql -N mysql < ${refreshRolesSQL} >> "$tmp"
+      mysql -v mysql < "$tmp"
+      doze
+    done
+  '';
+
+  addRoles = ''
+    ${concatMapStrings (r: "CREATE ROLE IF NOT EXISTS '${r}';\n") (attrNames roles)}
+
+    ${concatStrings
+       (mapAttrsToList (role: subroles: ''
+        ${concatMapStringsSep "\n" (r: "GRANT '${r}' TO '${role}';") subroles}
+        '') topRoles)
+    }
+  '';
+
+  revokeRoles = ''
+    ${concatMapAttrs (role: subroles: ''
+      SELECT CONCAT('REVOKE \''', role, '\' FROM \''', user, '\';') FROM roles_mapping
+        WHERE user = '${role}'
+        AND role NOT IN (${sqlList subroles});
+    '') topRoles
+    }
+
+    SELECT CONCAT('DROP ROLE \''', user, '\';') FROM user WHERE is_role='Y'
+      ${optionalString (allRoles != []) "AND user NOT IN (${sqlList allRoles})"}
+    ;
+  '';
+
+  roleType =
+    let
+      objects = mkOption {
+        type = either str (listOf str);
+        default = [];
+        example = [
+          "%bleep.%.created\_at"
+          "%bob\_live\_sg.brand\_type"
+          "%bob\_live\_sg.catalog%"
+          "%bob\_live\_sg.supplier.status"
+          "bar.%"
+          "beep"
+          "foo.%.%"
+        ];
+      };
+      basicRole = submodule {
+        options.nixsap = {
+          "ALL"       = objects;
+          "ALTER"     = objects;
+          "CREATE"    = objects;
+          "DELETE"    = objects;
+          "DROP"      = objects;
+          "INDEX"     = objects;
+          "INSERT"    = objects;
+          "SELECT"    = objects;
+          "SHOW VIEW" = objects;
+          "UPDATE"    = objects;
+        };
+      };
+      topRole = listOf str;
+    in either basicRole topRole;
+
+in {
+  options.nixsap.apps.mariadb = {
+    roles = mkOption {
+      type = attrsOf roleType;
+      default = {};
+      description = ''
+        Defines MariaDB roles. A role can be a "basic" one or a "top"
+        one. The basic roles are granted of regular privileges like SELECT
+        or UPDATE, while the top roles are granted of other roles. For basic
+        roles MySQL wildcards ("%" and "_") can be used to specify objects
+        to be granted on, including databases, tables and columns names. A
+        script runs periodically to find all matching objects and grants on
+        them. Objects are denoted as "schema[.table[.column]]".
+      '';
+      example = {
+        top_role = [ "basic_role" ];
+        basic_role = {
+          SELECT = [
+            "%bob\_live\_sg.brand\_type"
+            "%bob\_live\_sg.catalog%"
+            "%bob\_live\_sg.supplier.created\_at"
+            "%bob\_live\_sg.supplier.id\_supplier"
+            "%bob\_live\_sg.supplier.name%"
+            "%bob\_live\_sg.supplier.status"
+            "%bob\_live\_sg.supplier.type"
+            "%bob\_live\_sg.supplier.updated\_at"
+          ];
+        };
+        monitoring = {
+          SELECT = [
+            "%.%.created_at"
+          ];
+        };
+      };
+    };
+  };
+
+  config = {
+    nixsap.apps.mariadb.configure = optionalString (roles != {}) addRoles;
+    nixsap.apps.mariadb.configure' = revokeRoles;
+
+    systemd.services.mariadb-roles = mkIf (basicRoles != {}) {
+      description = "refresh MariaDB basic roles";
+      after = [ "mariadb.service" "mariadb-maintenance.service" ];
+      wantedBy = [ "multi-user.target" ];
+      path = [ pkgs.mariadb ];
+      serviceConfig = {
+        ExecStart = "${refreshRoles}/bin/refreshRoles";
+        User = config.nixsap.apps.mariadb.user;
+        Restart = "always";
+      };
+    };
+  };
+}
+
diff --git a/modules/apps/mariadb/slave-watchdog.nix b/modules/apps/mariadb/slave-watchdog.nix
new file mode 100644
index 0000000..8d1147e
--- /dev/null
+++ b/modules/apps/mariadb/slave-watchdog.nix
@@ -0,0 +1,103 @@
+{ changeMaster, importDump }: ''
+set -euo pipefail
+
+ch="$1"
+status=$(mktemp)
+trap 'rm -f "$status"' EXIT
+
+slave_status () {
+  if ! mysql -e ';'; then
+    echo unknown; return
+  fi
+
+  if mysql -e "SHOW SLAVE '$1' STATUS\\G" | sed 's,^ *,,' > "$status"; then
+    if grep -oE '\bMaster_Server_Id:\s*[1-9][0-9]*' "$status" >&2; then
+      io_errno=$(awk '/Last_IO_Errno:/ {print $2}' "$status")
+      sql_errno=$(awk '/Last_SQL_Errno:/ {print $2}' "$status")
+      case "$io_errno:$sql_errno" in
+        0:0)
+          echo ok
+          return
+          ;;
+        0:*)
+          awk '/Last_SQL_Error:/ {print $0}' "$status" >&2
+          echo "sql_error:$sql_errno"
+          return
+          ;;
+        *:*)
+          awk '/Last_IO_Error:/ {print $0}' "$status" >&2
+          echo "io_error:$io_errno"
+          return
+          ;;
+      esac
+    fi
+  fi
+  echo none
+}
+
+sql_errors=0
+none_count=0
+while true; do
+  st=$(slave_status "$ch")
+
+  case "$st" in
+    ok|unknown)
+      echo "status: $st" >&2
+      exit
+      ;;
+    none)
+      # XXX existing slave might not be initialized yet after mariadb restarts
+      (( ++none_count ))
+      echo "status: $st (count: $none_count)" >&2
+      if [ "$none_count" -lt 10 ]; then
+        sleep 1m
+        continue
+      fi
+      mysql -v -N -e "CALL mysql.resetSlave('$ch')" >&2
+      ${changeMaster} "$ch" | mysql
+      if ${importDump} "$ch" | mysql; then
+        mysql -v -N -e "CALL mysql.startSlave('$ch')" >&2
+        exit
+      else
+        echo 'Import failed. Starting over' >&2
+        mysql -v -N -e "CALL mysql.resetSlave('$ch')" >&2
+        exit 1
+      fi
+      ;;
+    io_error:*)
+      echo "status: $st" >&2
+      mysql -v -N -e "CALL mysql.stopSlave('$ch')" >&2
+      ${changeMaster} "$ch" | mysql
+      mysql -v -N -e "CALL mysql.startSlave('$ch')" >&2
+      exit 1
+      ;;
+    sql_error:1205) # Lock wait timeout exceeded
+      echo "status: $st" >&2
+      mysql -v -N -e "CALL mysql.startSlave('$ch')" >&2
+      exit 1
+      ;;
+    sql_error:*)
+      (( ++sql_errors ))
+      echo "status: $st (count: $sql_errors)" >&2
+      if [ "$sql_errors" -le 1 ]; then
+        mysql -v -N -e "CALL mysql.pauseSlave('$ch')" >&2
+        sleep 1s
+        mysql -v -N -e "CALL mysql.startSlave('$ch')" >&2
+      elif [ "$sql_errors" -le 2 ]; then
+        mysql -v -N -e "CALL mysql.stopSlave('$ch')" >&2
+        # this *unlikely* *may* change replication option (ignore tables, etc.)
+        ${changeMaster} "$ch" | mysql
+        mysql -v -N -e "CALL mysql.startSlave('$ch')" >&2
+      else
+        echo '!!! Resetting slave !!!' >&2
+        mysql -v -N -e "CALL mysql.resetSlave('$ch')" >&2
+        exit 1
+      fi
+      sleep 2m
+      ;;
+    *) echo "BUG: $st" >&2; exit 255;;
+  esac
+  sleep 1s
+done
+''
+