From 9f6a78629aad1d22dc8b928860fd05eb40f07352 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Isma=C3=ABl=20Bouya?= Date: Thu, 5 Dec 2019 00:51:45 +0100 Subject: [PATCH] Add mysql replication --- modules/private/databases/default.nix | 4 + modules/private/databases/mariadb.nix | 53 +++- .../private/databases/mariadb_replication.nix | 237 ++++++++++++++++++ .../databases/postgresql_replication.nix | 4 +- modules/private/databases/utils.nix | 30 +++ modules/private/default.nix | 1 + modules/private/monitoring/default.nix | 7 + .../private/monitoring/objects_backup-2.nix | 5 + modules/private/system/backup-2.nix | 16 ++ 9 files changed, 346 insertions(+), 11 deletions(-) create mode 100644 modules/private/databases/mariadb_replication.nix create mode 100644 modules/private/databases/utils.nix 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') + ''; +} diff --git a/modules/private/default.nix b/modules/private/default.nix index b4b8c4f..51052ed 100644 --- a/modules/private/default.nix +++ b/modules/private/default.nix @@ -11,6 +11,7 @@ set = { postgresql = ./databases/postgresql.nix; redis = ./databases/redis.nix; postgresqlReplication = ./databases/postgresql_replication.nix; + mariadbReplication = ./databases/mariadb_replication.nix; websites = ./websites; atenInte = ./websites/aten/integration.nix; diff --git a/modules/private/monitoring/default.nix b/modules/private/monitoring/default.nix index 0259cbf..baeebc9 100644 --- a/modules/private/monitoring/default.nix +++ b/modules/private/monitoring/default.nix @@ -72,6 +72,13 @@ in users = [ "naemon" ]; runAs = "postgres"; } + { + commands = [ + { command = "${myplugins}/check_last_file_date /backup2/*"; options = [ "NOPASSWD" ]; } + ]; + users = [ "naemon" ]; + runAs = "mysql"; + } { commands = [ { command = "${myplugins}/check_last_file_date /backup2/*"; options = [ "NOPASSWD" ]; } diff --git a/modules/private/monitoring/objects_backup-2.nix b/modules/private/monitoring/objects_backup-2.nix index b8ecb81..fad6763 100644 --- a/modules/private/monitoring/objects_backup-2.nix +++ b/modules/private/monitoring/objects_backup-2.nix @@ -26,5 +26,10 @@ use = "local-service"; check_command = ["check_last_file_date" "/backup2/eldiron/postgresql_backup" "7" "postgres"]; } + { + service_description = "Last mysql dump in /backup2/eldiron/mysql_backup is not too old"; + use = "local-service"; + check_command = ["check_last_file_date" "/backup2/eldiron/mysql_backup" "7" "mysql"]; + } ]; } diff --git a/modules/private/system/backup-2.nix b/modules/private/system/backup-2.nix index 3120a57..0e94a1a 100644 --- a/modules/private/system/backup-2.nix +++ b/modules/private/system/backup-2.nix @@ -61,6 +61,22 @@ }; }; }; + mariadb = { + enable = true; + base = "/backup2"; + hosts = { + eldiron = { + serverId = 2; + # mysql resolves "backup-2" host and checks the ip, but uses /etc/hosts which only contains ip4 + host = myconfig.env.servers.eldiron.ips.main.ip4; + port = "3306"; + user = "backup-2"; + password = myconfig.env.ldap.backup-2.password; + dumpUser = "root"; + dumpPassword = myconfig.env.databases.mysql.systemUsers.root; + }; + }; + }; }; # This value determines the NixOS release with which your system is -- 2.41.0