diff options
author | Igor Pashev <pashev.igor@gmail.com> | 2016-09-23 12:41:01 +0300 |
---|---|---|
committer | Igor Pashev <pashev.igor@gmail.com> | 2016-09-23 12:41:49 +0300 |
commit | af337a12e6f084556400fa93c71304ad63f1efa6 (patch) | |
tree | ad5125cbfb2e812f4a507b182b875526b2a2d0e9 /apps/mariadb | |
download | nixsap-af337a12e6f084556400fa93c71304ad63f1efa6.tar.gz |
Initial commit
Diffstat (limited to 'apps/mariadb')
-rw-r--r-- | apps/mariadb/default.nix | 442 | ||||
-rw-r--r-- | apps/mariadb/mysqld.nix | 285 | ||||
-rw-r--r-- | apps/mariadb/procedures.sql | 134 | ||||
-rw-r--r-- | apps/mariadb/replicate.nix | 87 | ||||
-rw-r--r-- | apps/mariadb/roles.nix | 250 | ||||
-rw-r--r-- | apps/mariadb/slave-watchdog.nix | 103 |
6 files changed, 1301 insertions, 0 deletions
diff --git a/apps/mariadb/default.nix b/apps/mariadb/default.nix new file mode 100644 index 0000000..cdf5d92 --- /dev/null +++ b/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/apps/mariadb/mysqld.nix b/apps/mariadb/mysqld.nix new file mode 100644 index 0000000..d66d96d --- /dev/null +++ b/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/apps/mariadb/procedures.sql b/apps/mariadb/procedures.sql new file mode 100644 index 0000000..3aabe80 --- /dev/null +++ b/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/apps/mariadb/replicate.nix b/apps/mariadb/replicate.nix new file mode 100644 index 0000000..9f51dbf --- /dev/null +++ b/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/apps/mariadb/roles.nix b/apps/mariadb/roles.nix new file mode 100644 index 0000000..2971242 --- /dev/null +++ b/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/apps/mariadb/slave-watchdog.nix b/apps/mariadb/slave-watchdog.nix new file mode 100644 index 0000000..8d1147e --- /dev/null +++ b/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 +'' + |