systems/backup-2/databases/mariadb_replication.nix

   1 { pkgs, config, lib, ... }:
   2 let
   3   cfg = config.myServices.databasesReplication.mariadb;
   4 in
   5 {
   6   options.myServices.databasesReplication.mariadb = {
   7     enable = lib.mkEnableOption "Enable mariadb replication";
   8     base = lib.mkOption {
   9       type = lib.types.path;
  10       description = ''
  11         Base path to put the replications
  12         '';
  13     };
  14     hosts = lib.mkOption {
  15       default = {};
  16       description = ''
  17         Hosts to backup
  18         '';
  19       type = lib.types.attrsOf (lib.types.submodule {
  20         options = {
  21           package = lib.mkOption {
  22             type = lib.types.package;
  23             default = pkgs.mariadb;
  24             description = ''
  25               Mariadb package for this host
  26             '';
  27           };
  28           serverId = lib.mkOption {
  29             type = lib.types.int;
  30             description = ''
  31               Server id to use for replication cluster (must be unique among the cluster!)
  32             '';
  33           };
  34           host = lib.mkOption {
  35             type = lib.types.str;
  36             description = ''
  37               Host to connect to
  38               '';
  39           };
  40           port = lib.mkOption {
  41             type = lib.types.int;
  42             description = ''
  43               Port to connect to
  44               '';
  45           };
  46           user = lib.mkOption {
  47             type = lib.types.str;
  48             description = ''
  49               User to connect as
  50               '';
  51           };
  52           password = lib.mkOption {
  53             type = lib.types.str;
  54             description = ''
  55               Password to use
  56               '';
  57           };
  58           dumpUser = lib.mkOption {
  59             type = lib.types.str;
  60             description = ''
  61               User who can do a dump
  62               '';
  63           };
  64           dumpPassword = lib.mkOption {
  65             type = lib.types.str;
  66             description = ''
  67               Password for the dump user
  68               '';
  69           };
  70         };
  71       });
  72     };
  73   };
  74
  75   config = lib.mkIf cfg.enable {
  76     myServices.chatonsProperties.hostings.mysql-replication = {
  77       file.datetime = "2022-08-27T15:00:00";
  78       hosting = {
  79         name = "Mysql replication";
  80         description = "Replication of mysql database";
  81         website = "db-1.immae.eu";
  82         status.level = "OK";
  83         status.description = "OK";
  84         registration.load = "OPEN";
  85         install.type = "PACKAGE";
  86       };
  87       software = {
  88         name = "MariaDB";
  89         website = "https://mariadb.org/";
  90         license.url = "https://github.com/MariaDB/server/blob/10.11/COPYING";
  91         license.name = "GNU General Public License v2.0";
  92         version = pkgs.mariadb.version;
  93         source.url = "https://github.com/MariaDB/server";
  94       };
  95     };
  96     users.users.mysql = {
  97       description = "MySQL server user";
  98       group = "mysql";
  99       uid = config.ids.uids.mysql;
 100       extraGroups = [ "keys" ];
 101     };
 102     users.groups.mysql.gid = config.ids.gids.mysql;
 103
 104     secrets.keys = lib.listToAttrs (lib.flatten (lib.mapAttrsToList (name: hcfg: [
 105       (lib.nameValuePair "mysql_replication/${name}/slave_init_commands" {
 106         user = "mysql";
 107         group = "mysql";
 108         permissions = "0400";
 109         text = ''
 110           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;
 111           START SLAVE;
 112           '';
 113       })
 114       (lib.nameValuePair "mysql_replication/${name}/mysqldump_remote" {
 115         permissions = "0400";
 116         user = "root";
 117         group = "root";
 118         text = ''
 119           [mysqldump]
 120           user = ${hcfg.user}
 121           password = ${hcfg.password}
 122         '';
 123       })
 124       (lib.nameValuePair "mysql_replication/${name}/mysqldump" {
 125         permissions = "0400";
 126         user = "root";
 127         group = "root";
 128         text = ''
 129           [mysqldump]
 130           user = ${hcfg.dumpUser}
 131           password = ${hcfg.dumpPassword}
 132         '';
 133       })
 134       (lib.nameValuePair "mysql_replication/${name}/client" {
 135         permissions = "0400";
 136         user = "mysql";
 137         group = "mysql";
 138         text = ''
 139           [client]
 140           user = ${hcfg.dumpUser}
 141           password = ${hcfg.dumpPassword}
 142         '';
 143       })
 144     ]) cfg.hosts));
 145
 146     services.cron = {
 147       enable = true;
 148       systemCronJobs = lib.flatten (lib.mapAttrsToList (name: hcfg:
 149         let
 150           dataDir = "${cfg.base}/${name}/mysql";
 151           backupDir = "${cfg.base}/${name}/mysql_backup";
 152           backup_script = pkgs.writeScript "backup_mysql_${name}" ''
 153               #!${pkgs.stdenv.shell}
 154
 155               set -euo pipefail
 156
 157               filename=${backupDir}/$(${pkgs.coreutils}/bin/date -Iminutes).sql
 158               ${hcfg.package}/bin/mysqldump \
 159                 --defaults-file=${config.secrets.fullPaths."mysql_replication/${name}/mysqldump"} \
 160                 -S /run/mysqld_${name}/mysqld.sock \
 161                 --gtid \
 162                 --master-data \
 163                 --flush-privileges \
 164                 --ignore-database=netdata \
 165                 --all-databases > $filename
 166               ${pkgs.gzip}/bin/gzip $filename
 167             '';
 168           u = pkgs.callPackage ./utils.nix {};
 169           cleanup_script = pkgs.writeScript "cleanup_mysql_${name}" (u.exponentialDumps "sql.gz" backupDir);
 170         in [
 171           "0 22,4,10,16 * * * root ${backup_script}"
 172           "0 3 * * * root ${cleanup_script}"
 173         ]) cfg.hosts);
 174     };
 175
 176     system.activationScripts = lib.attrsets.mapAttrs' (name: hcfg:
 177       lib.attrsets.nameValuePair "mysql_replication_${name}" {
 178         deps = [ "users" "groups" ];
 179         text = ''
 180           install -m 0700 -o mysql -g mysql -d ${cfg.base}/${name}/mysql
 181           install -m 0700 -o mysql -g mysql -d ${cfg.base}/${name}/mysql_backup
 182           '';
 183       }) cfg.hosts;
 184
 185     environment.etc = lib.attrsets.mapAttrs' (name: hcfg:
 186       lib.attrsets.nameValuePair "mysql/${name}_my.cnf" {
 187         text = ''
 188           [mysqld]
 189           skip-networking
 190           socket = /run/mysqld_${name}/mysqld.sock
 191           datadir = ${cfg.base}/${name}/mysql/
 192           log-bin = mariadb-bin
 193           server-id = ${builtins.toString hcfg.serverId}
 194           '';
 195       }
 196     ) cfg.hosts;
 197
 198     environment.systemPackages = lib.mapAttrsToList (name: hcfg:
 199       pkgs.writeScriptBin "mysql_backup_${name}" ''
 200         #!${pkgs.stdenv.shell}
 201
 202         exec ${hcfg.package}/bin/mysql -S /run/mysqld_${name}/mysqld.sock "$@"
 203       ''
 204     ) cfg.hosts;
 205
 206     systemd.services = lib.attrsets.mapAttrs' (name: hcfg:
 207       let
 208         dataDir = "${cfg.base}/${name}/mysql";
 209       in
 210       lib.attrsets.nameValuePair "mysql_backup_${name}" {
 211         description = "Mysql replication for ${name}";
 212         wantedBy = [ "multi-user.target" ];
 213         after = [ "network.target" ];
 214         restartTriggers = [ config.environment.etc."mysql/${name}_my.cnf".source ];
 215         unitConfig.RequiresMountsFor = dataDir;
 216
 217         preStart = ''
 218           if ! test -e ${dataDir}/mysql; then
 219             if ! test -e ${dataDir}/initial.sql; then
 220               ${hcfg.package}/bin/mysqldump \
 221                 --defaults-file=${config.secrets.fullPaths."mysql_replication/${name}/mysqldump_remote"} \
 222                 -h ${hcfg.host} \
 223                 -P ${builtins.toString hcfg.port} \
 224                 --ssl \
 225                 --gtid \
 226                 --flush-privileges \
 227                 --master-data \
 228                 --all-databases > ${dataDir}/initial.sql
 229             fi
 230
 231             ${hcfg.package}/bin/mysql_install_db \
 232               --defaults-file=/etc/mysql/${name}_my.cnf \
 233               --user=mysql \
 234               --datadir=${dataDir} \
 235               --basedir=${hcfg.package}
 236           fi
 237           '';
 238
 239         serviceConfig = {
 240           User = "mysql";
 241           Group = "mysql";
 242           RuntimeDirectory = "mysqld_${name}";
 243           RuntimeDirectoryMode = "0755";
 244           SupplementaryGroups = "keys";
 245           PermissionsStartOnly = true;
 246           Type = "notify";
 247
 248           ExecStart = "${hcfg.package}/bin/mysqld --defaults-file=/etc/mysql/${name}_my.cnf --user=mysql --datadir=${dataDir} --basedir=${hcfg.package}";
 249           ExecStartPost =
 250             let
 251               sql_before = pkgs.writeText "mysql-initial-before" ''
 252                 DROP DATABASE test;
 253                 INSTALL SONAME 'auth_pam';
 254                 '';
 255               setupScript = pkgs.writeScript "mysql-setup" ''
 256                 #!${pkgs.runtimeShell} -e
 257
 258                 if test -e ${dataDir}/initial.sql; then
 259                   cat \
 260                     ${sql_before} \
 261                     ${dataDir}/initial.sql \
 262                     ${config.secrets.fullPaths."mysql_replication/${name}/slave_init_commands"} \
 263                     | ${hcfg.package}/bin/mysql \
 264                     --defaults-file=/etc/mysql/${name}_my.cnf \
 265                     -S /run/mysqld_${name}/mysqld.sock \
 266                     --user=root
 267                   rm -f ${dataDir}/initial.sql
 268                 fi
 269                 '';
 270             in
 271               "+${setupScript}";
 272           # initial dump can take a long time
 273           TimeoutStartSec="infinity";
 274           TimeoutStopSec = 120;
 275         };
 276     }) cfg.hosts;
 277   };
 278 }
 279