Merge pull request #84136 from Izorkin/mariadb-galera-test

nixos/tests: add check mariadb galera cluster

9 files changed, 459 insertions, 9 deletions

diff --git a/nixos/tests/all-tests.nix b/nixos/tests/all-tests.nix
index 851f20ca4b43..ecdd30a6c50a 100644
--- a/nixos/tests/all-tests.nix
+++ b/nixos/tests/all-tests.nix
@@ -24,7 +24,6 @@ in
   _3proxy = handleTest ./3proxy.nix {};
   acme = handleTest ./acme.nix {};
   atd = handleTest ./atd.nix {};
-  automysqlbackup = handleTest ./automysqlbackup.nix {};
   avahi = handleTest ./avahi.nix {};
   babeld = handleTest ./babeld.nix {};
   bcachefs = handleTestOn ["x86_64-linux"] ./bcachefs.nix {}; # linux-4.18.2018.10.12 is unsupported on aarch64
@@ -176,6 +175,8 @@ in
   magnetico = handleTest ./magnetico.nix {};
   magic-wormhole-mailbox-server = handleTest ./magic-wormhole-mailbox-server.nix {};
   mailcatcher = handleTest ./mailcatcher.nix {};
+  mariadb-galera-mariabackup = handleTest ./mysql/mariadb-galera-mariabackup.nix {};
+  mariadb-galera-rsync = handleTest ./mysql/mariadb-galera-rsync.nix {};
   mathics = handleTest ./mathics.nix {};
   matomo = handleTest ./matomo.nix {};
   matrix-synapse = handleTest ./matrix-synapse.nix {};
@@ -197,9 +198,10 @@ in
   munin = handleTest ./munin.nix {};
   mutableUsers = handleTest ./mutable-users.nix {};
   mxisd = handleTest ./mxisd.nix {};
-  mysql = handleTest ./mysql.nix {};
-  mysqlBackup = handleTest ./mysql-backup.nix {};
-  mysqlReplication = handleTest ./mysql-replication.nix {};
+  mysql = handleTest ./mysql/mysql.nix {};
+  mysql-autobackup = handleTest ./mysql/mysql-autobackup.nix {};
+  mysql-backup = handleTest ./mysql/mysql-backup.nix {};
+  mysql-replication = handleTest ./mysql/mysql-replication.nix {};
   nagios = handleTest ./nagios.nix {};
   nat.firewall = handleTest ./nat.nix { withFirewall = true; };
   nat.firewall-conntrack = handleTest ./nat.nix { withFirewall = true; withConntrackHelpers = true; };
diff --git a/nixos/tests/mysql/mariadb-galera-mariabackup.nix b/nixos/tests/mysql/mariadb-galera-mariabackup.nix
new file mode 100644
index 000000000000..73abf6c555f9
--- /dev/null
+++ b/nixos/tests/mysql/mariadb-galera-mariabackup.nix
@@ -0,0 +1,223 @@
+import ./../make-test-python.nix ({ pkgs, ...} :
+
+let
+  mysqlenv-common      = pkgs.buildEnv { name = "mysql-path-env-common";      pathsToLink = [ "/bin" ]; paths = with pkgs; [ bash gawk gnutar inetutils which ]; };
+  mysqlenv-mariabackup = pkgs.buildEnv { name = "mysql-path-env-mariabackup"; pathsToLink = [ "/bin" ]; paths = with pkgs; [ gzip iproute netcat procps pv socat ]; };
+
+in {
+  name = "mariadb-galera-mariabackup";
+  meta = with pkgs.stdenv.lib.maintainers; {
+    maintainers = [ izorkin ];
+  };
+
+  # The test creates a Galera cluster with 3 nodes and is checking if mariabackup-based SST works. The cluster is tested by creating a DB and an empty table on one node,
+  # and checking the table's presence on the other node.
+
+  nodes = {
+    galera_01 =
+      { pkgs, ... }:
+      {
+      networking = {
+        interfaces.eth1 = {
+          ipv4.addresses = [
+            { address = "192.168.1.1"; prefixLength = 24; }
+          ];
+        };
+        extraHosts = ''
+          192.168.1.1 galera_01
+          192.168.1.2 galera_02
+          192.168.1.3 galera_03
+        '';
+        firewall.allowedTCPPorts = [ 3306 4444 4567 4568 ];
+        firewall.allowedUDPPorts = [ 4567 ];
+      };
+      users.users.testuser = { };
+      systemd.services.mysql = with pkgs; {
+        path = [ mysqlenv-common mysqlenv-mariabackup ];
+      };
+      services.mysql = {
+        enable = true;
+        package = pkgs.mariadb;
+        ensureDatabases = [ "testdb" ];
+        ensureUsers = [{
+          name = "testuser";
+          ensurePermissions = {
+            "testdb.*" = "ALL PRIVILEGES";
+          };
+        }];
+        initialScript = pkgs.writeText "mariadb-init.sql" ''
+          GRANT ALL PRIVILEGES ON *.* TO 'check_repl'@'localhost' IDENTIFIED BY 'check_pass' WITH GRANT OPTION;
+          FLUSH PRIVILEGES;
+        '';
+        settings = {
+          mysqld = {
+            bind_address = "0.0.0.0";
+          };
+          galera = {
+            wsrep_on = "ON";
+            wsrep_debug = "OFF";
+            wsrep_retry_autocommit = "3";
+            wsrep_provider = "${pkgs.mariadb-galera_25}/lib/galera/libgalera_smm.so";
+            wsrep_cluster_address = "gcomm://";
+            wsrep_cluster_name = "galera";
+            wsrep_node_address = "192.168.1.1";
+            wsrep_node_name = "galera_01";
+            wsrep_sst_method = "mariabackup";
+            wsrep_sst_auth = "check_repl:check_pass";
+            binlog_format = "ROW";
+            enforce_storage_engine = "InnoDB";
+            innodb_autoinc_lock_mode = "2";
+          };
+        };
+      };
+    };
+
+    galera_02 =
+      { pkgs, ... }:
+      {
+      networking = {
+        interfaces.eth1 = {
+          ipv4.addresses = [
+            { address = "192.168.1.2"; prefixLength = 24; }
+          ];
+        };
+        extraHosts = ''
+          192.168.1.1 galera_01
+          192.168.1.2 galera_02
+          192.168.1.3 galera_03
+        '';
+        firewall.allowedTCPPorts = [ 3306 4444 4567 4568 ];
+        firewall.allowedUDPPorts = [ 4567 ];
+      };
+      users.users.testuser = { };
+      systemd.services.mysql = with pkgs; {
+        path = [ mysqlenv-common mysqlenv-mariabackup ];
+      };
+      services.mysql = {
+        enable = true;
+        package = pkgs.mariadb;
+        settings = {
+          mysqld = {
+            bind_address = "0.0.0.0";
+          };
+          galera = {
+            wsrep_on = "ON";
+            wsrep_debug = "OFF";
+            wsrep_retry_autocommit = "3";
+            wsrep_provider = "${pkgs.mariadb-galera_25}/lib/galera/libgalera_smm.so";
+            wsrep_cluster_address = "gcomm://galera_01,galera_02,galera_03";
+            wsrep_cluster_name = "galera";
+            wsrep_node_address = "192.168.1.2";
+            wsrep_node_name = "galera_02";
+            wsrep_sst_method = "mariabackup";
+            wsrep_sst_auth = "check_repl:check_pass";
+            binlog_format = "ROW";
+            enforce_storage_engine = "InnoDB";
+            innodb_autoinc_lock_mode = "2";
+          };
+        };
+      };
+    };
+
+    galera_03 =
+      { pkgs, ... }:
+      {
+      networking = {
+        interfaces.eth1 = {
+          ipv4.addresses = [
+            { address = "192.168.1.3"; prefixLength = 24; }
+          ];
+        };
+        extraHosts = ''
+          192.168.1.1 galera_01
+          192.168.1.2 galera_02
+          192.168.1.3 galera_03
+        '';
+        firewall.allowedTCPPorts = [ 3306 4444 4567 4568 ];
+        firewall.allowedUDPPorts = [ 4567 ];
+      };
+      users.users.testuser = { };
+      systemd.services.mysql = with pkgs; {
+        path = [ mysqlenv-common mysqlenv-mariabackup ];
+      };
+      services.mysql = {
+        enable = true;
+        package = pkgs.mariadb;
+        settings = {
+          mysqld = {
+            bind_address = "0.0.0.0";
+          };
+          galera = {
+            wsrep_on = "ON";
+            wsrep_debug = "OFF";
+            wsrep_retry_autocommit = "3";
+            wsrep_provider = "${pkgs.mariadb-galera_25}/lib/galera/libgalera_smm.so";
+            wsrep_cluster_address = "gcomm://galera_01,galera_02,galera_03";
+            wsrep_cluster_name = "galera";
+            wsrep_node_address = "192.168.1.3";
+            wsrep_node_name = "galera_03";
+            wsrep_sst_method = "mariabackup";
+            wsrep_sst_auth = "check_repl:check_pass";
+            binlog_format = "ROW";
+            enforce_storage_engine = "InnoDB";
+            innodb_autoinc_lock_mode = "2";
+          };
+        };
+      };
+    };
+  };
+
+  testScript = ''
+    galera_01.start()
+    galera_01.wait_for_unit("mysql")
+    galera_01.wait_for_open_port(3306)
+    galera_01.succeed(
+        "sudo -u testuser mysql -u testuser -e 'use testdb; create table db1 (test_id INT, PRIMARY KEY (test_id)) ENGINE = InnoDB;'"
+    )
+    galera_01.succeed(
+        "sudo -u testuser mysql -u testuser -e 'use testdb; insert into db1 values (37);'"
+    )
+    galera_02.start()
+    galera_02.wait_for_unit("mysql")
+    galera_02.wait_for_open_port(3306)
+    galera_03.start()
+    galera_03.wait_for_unit("mysql")
+    galera_03.wait_for_open_port(3306)
+    galera_02.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; select test_id from db1;' -N | grep 37"
+    )
+    galera_02.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; create table db2 (test_id INT, PRIMARY KEY (test_id)) ENGINE = InnoDB;'"
+    )
+    galera_02.succeed("systemctl stop mysql")
+    galera_01.succeed(
+        "sudo -u testuser mysql -u testuser -e 'use testdb; insert into db2 values (38);'"
+    )
+    galera_03.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; create table db3 (test_id INT, PRIMARY KEY (test_id)) ENGINE = InnoDB;'"
+    )
+    galera_01.succeed(
+        "sudo -u testuser mysql -u testuser -e 'use testdb; insert into db3 values (39);'"
+    )
+    galera_02.succeed("systemctl start mysql")
+    galera_02.wait_for_open_port(3306)
+    galera_02.succeed(
+        "sudo -u testuser mysql -u root -e 'show status' -N | grep 'wsrep_cluster_size.*3'"
+    )
+    galera_03.succeed(
+        "sudo -u testuser mysql -u root -e 'show status' -N | grep 'wsrep_local_state_comment.*Synced'"
+    )
+    galera_01.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; select test_id from db3;' -N | grep 39"
+    )
+    galera_02.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; select test_id from db2;' -N | grep 38"
+    )
+    galera_03.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; select test_id from db1;' -N | grep 37"
+    )
+    galera_01.succeed("sudo -u testuser mysql -u testuser -e 'use testdb; drop table db3;'")
+    galera_02.succeed("sudo -u testuser mysql -u root -e 'use testdb; drop table db2;'")
+    galera_03.succeed("sudo -u testuser mysql -u root -e 'use testdb; drop table db1;'")
+  '';
+})
diff --git a/nixos/tests/mysql/mariadb-galera-rsync.nix b/nixos/tests/mysql/mariadb-galera-rsync.nix
new file mode 100644
index 000000000000..cacae4569b57
--- /dev/null
+++ b/nixos/tests/mysql/mariadb-galera-rsync.nix
@@ -0,0 +1,216 @@
+import ./../make-test-python.nix ({ pkgs, ...} :
+
+let
+  mysqlenv-common      = pkgs.buildEnv { name = "mysql-path-env-common";      pathsToLink = [ "/bin" ]; paths = with pkgs; [ bash gawk gnutar inetutils which ]; };
+  mysqlenv-rsync       = pkgs.buildEnv { name = "mysql-path-env-rsync";       pathsToLink = [ "/bin" ]; paths = with pkgs; [ lsof procps rsync stunnel ]; };
+
+in {
+  name = "mariadb-galera-rsync";
+  meta = with pkgs.stdenv.lib.maintainers; {
+    maintainers = [ izorkin ];
+  };
+
+  # The test creates a Galera cluster with 3 nodes and is checking if rsync-based SST works. The cluster is tested by creating a DB and an empty table on one node,
+  # and checking the table's presence on the other node.
+
+  nodes = {
+    galera_04 =
+      { pkgs, ... }:
+      {
+      networking = {
+        interfaces.eth1 = {
+          ipv4.addresses = [
+            { address = "192.168.2.1"; prefixLength = 24; }
+          ];
+        };
+        extraHosts = ''
+          192.168.2.1 galera_04
+          192.168.2.2 galera_05
+          192.168.2.3 galera_06
+        '';
+        firewall.allowedTCPPorts = [ 3306 4444 4567 4568 ];
+        firewall.allowedUDPPorts = [ 4567 ];
+      };
+      users.users.testuser = { };
+      systemd.services.mysql = with pkgs; {
+        path = [ mysqlenv-common mysqlenv-rsync ];
+      };
+      services.mysql = {
+        enable = true;
+        package = pkgs.mariadb;
+        ensureDatabases = [ "testdb" ];
+        ensureUsers = [{
+          name = "testuser";
+          ensurePermissions = {
+            "testdb.*" = "ALL PRIVILEGES";
+          };
+        }];
+        settings = {
+          mysqld = {
+            bind_address = "0.0.0.0";
+          };
+          galera = {
+            wsrep_on = "ON";
+            wsrep_debug = "OFF";
+            wsrep_retry_autocommit = "3";
+            wsrep_provider = "${pkgs.mariadb-galera_25}/lib/galera/libgalera_smm.so";
+            wsrep_cluster_address = "gcomm://";
+            wsrep_cluster_name = "galera-rsync";
+            wsrep_node_address = "192.168.2.1";
+            wsrep_node_name = "galera_04";
+            wsrep_sst_method = "rsync";
+            binlog_format = "ROW";
+            enforce_storage_engine = "InnoDB";
+            innodb_autoinc_lock_mode = "2";
+          };
+        };
+      };
+    };
+
+    galera_05 =
+      { pkgs, ... }:
+      {
+      networking = {
+        interfaces.eth1 = {
+          ipv4.addresses = [
+            { address = "192.168.2.2"; prefixLength = 24; }
+          ];
+        };
+        extraHosts = ''
+          192.168.2.1 galera_04
+          192.168.2.2 galera_05
+          192.168.2.3 galera_06
+        '';
+        firewall.allowedTCPPorts = [ 3306 4444 4567 4568 ];
+        firewall.allowedUDPPorts = [ 4567 ];
+      };
+      users.users.testuser = { };
+      systemd.services.mysql = with pkgs; {
+        path = [ mysqlenv-common mysqlenv-rsync ];
+      };
+      services.mysql = {
+        enable = true;
+        package = pkgs.mariadb;
+        settings = {
+          mysqld = {
+            bind_address = "0.0.0.0";
+          };
+          galera = {
+            wsrep_on = "ON";
+            wsrep_debug = "OFF";
+            wsrep_retry_autocommit = "3";
+            wsrep_provider = "${pkgs.mariadb-galera_25}/lib/galera/libgalera_smm.so";
+            wsrep_cluster_address = "gcomm://galera_04,galera_05,galera_06";
+            wsrep_cluster_name = "galera-rsync";
+            wsrep_node_address = "192.168.2.2";
+            wsrep_node_name = "galera_05";
+            wsrep_sst_method = "rsync";
+            binlog_format = "ROW";
+            enforce_storage_engine = "InnoDB";
+            innodb_autoinc_lock_mode = "2";
+          };
+        };
+      };
+    };
+
+    galera_06 =
+      { pkgs, ... }:
+      {
+      networking = {
+        interfaces.eth1 = {
+          ipv4.addresses = [
+            { address = "192.168.2.3"; prefixLength = 24; }
+          ];
+        };
+        extraHosts = ''
+          192.168.2.1 galera_04
+          192.168.2.2 galera_05
+          192.168.2.3 galera_06
+        '';
+        firewall.allowedTCPPorts = [ 3306 4444 4567 4568 ];
+        firewall.allowedUDPPorts = [ 4567 ];
+      };
+      users.users.testuser = { };
+      systemd.services.mysql = with pkgs; {
+        path = [ mysqlenv-common mysqlenv-rsync ];
+      };
+      services.mysql = {
+        enable = true;
+        package = pkgs.mariadb;
+        settings = {
+          mysqld = {
+            bind_address = "0.0.0.0";
+          };
+          galera = {
+            wsrep_on = "ON";
+            wsrep_debug = "OFF";
+            wsrep_retry_autocommit = "3";
+            wsrep_provider = "${pkgs.mariadb-galera_25}/lib/galera/libgalera_smm.so";
+            wsrep_cluster_address = "gcomm://galera_04,galera_05,galera_06";
+            wsrep_cluster_name = "galera-rsync";
+            wsrep_node_address = "192.168.2.3";
+            wsrep_node_name = "galera_06";
+            wsrep_sst_method = "rsync";
+            binlog_format = "ROW";
+            enforce_storage_engine = "InnoDB";
+            innodb_autoinc_lock_mode = "2";
+          };
+        };
+      };
+    };
+  };
+
+  testScript = ''
+    galera_04.start()
+    galera_04.wait_for_unit("mysql")
+    galera_04.wait_for_open_port(3306)
+    galera_04.succeed(
+        "sudo -u testuser mysql -u testuser -e 'use testdb; create table db1 (test_id INT, PRIMARY KEY (test_id)) ENGINE = InnoDB;'"
+    )
+    galera_04.succeed(
+        "sudo -u testuser mysql -u testuser -e 'use testdb; insert into db1 values (41);'"
+    )
+    galera_05.start()
+    galera_05.wait_for_unit("mysql")
+    galera_05.wait_for_open_port(3306)
+    galera_06.start()
+    galera_06.wait_for_unit("mysql")
+    galera_06.wait_for_open_port(3306)
+    galera_05.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; select test_id from db1;' -N | grep 41"
+    )
+    galera_05.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; create table db2 (test_id INT, PRIMARY KEY (test_id)) ENGINE = InnoDB;'"
+    )
+    galera_05.succeed("systemctl stop mysql")
+    galera_04.succeed(
+        "sudo -u testuser mysql -u testuser -e 'use testdb; insert into db2 values (42);'"
+    )
+    galera_06.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; create table db3 (test_id INT, PRIMARY KEY (test_id)) ENGINE = InnoDB;'"
+    )
+    galera_04.succeed(
+        "sudo -u testuser mysql -u testuser -e 'use testdb; insert into db3 values (43);'"
+    )
+    galera_05.succeed("systemctl start mysql")
+    galera_05.wait_for_open_port(3306)
+    galera_05.succeed(
+        "sudo -u testuser mysql -u root -e 'show status' -N | grep 'wsrep_cluster_size.*3'"
+    )
+    galera_06.succeed(
+        "sudo -u testuser mysql -u root -e 'show status' -N | grep 'wsrep_local_state_comment.*Synced'"
+    )
+    galera_04.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; select test_id from db3;' -N | grep 43"
+    )
+    galera_05.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; select test_id from db2;' -N | grep 42"
+    )
+    galera_06.succeed(
+        "sudo -u testuser mysql -u root -e 'use testdb; select test_id from db1;' -N | grep 41"
+    )
+    galera_04.succeed("sudo -u testuser mysql -u testuser -e 'use testdb; drop table db3;'")
+    galera_05.succeed("sudo -u testuser mysql -u root -e 'use testdb; drop table db2;'")
+    galera_06.succeed("sudo -u testuser mysql -u root -e 'use testdb; drop table db1;'")
+  '';
+})
diff --git a/nixos/tests/automysqlbackup.nix b/nixos/tests/mysql/mysql-autobackup.nix
index 224b93862fbd..65576e52a537 100644
--- a/nixos/tests/automysqlbackup.nix
+++ b/nixos/tests/mysql/mysql-autobackup.nix
@@ -1,4 +1,4 @@
-import ./make-test-python.nix ({ pkgs, lib, ... }:
+import ./../make-test-python.nix ({ pkgs, lib, ... }:
 
 {
   name = "automysqlbackup";
diff --git a/nixos/tests/mysql-backup.nix b/nixos/tests/mysql/mysql-backup.nix
index a0595e4d5539..c4c1079a8a64 100644
--- a/nixos/tests/mysql-backup.nix
+++ b/nixos/tests/mysql/mysql-backup.nix
@@ -1,5 +1,5 @@
 # Test whether mysqlBackup option works
-import ./make-test-python.nix ({ pkgs, ... } : {
+import ./../make-test-python.nix ({ pkgs, ... } : {
   name = "mysql-backup";
   meta = with pkgs.stdenv.lib.maintainers; {
     maintainers = [ rvl ];
diff --git a/nixos/tests/mysql-replication.nix b/nixos/tests/mysql/mysql-replication.nix
index a2654f041add..81038dccd947 100644
--- a/nixos/tests/mysql-replication.nix
+++ b/nixos/tests/mysql/mysql-replication.nix
@@ -1,4 +1,4 @@
-import ./make-test-python.nix ({ pkgs, ...} :
+import ./../make-test-python.nix ({ pkgs, ...} :
 
 let
   replicateUser = "replicate";
diff --git a/nixos/tests/mysql.nix b/nixos/tests/mysql/mysql.nix
index 11c1dabf9360..d236ce946328 100644
--- a/nixos/tests/mysql.nix
+++ b/nixos/tests/mysql/mysql.nix
@@ -1,4 +1,4 @@
-import ./make-test-python.nix ({ pkgs, ...} : {
+import ./../make-test-python.nix ({ pkgs, ...} : {
   name = "mysql";
   meta = with pkgs.stdenv.lib.maintainers; {
     maintainers = [ eelco shlevy ];
diff --git a/nixos/tests/testdb.sql b/nixos/tests/mysql/testdb.sql
index 3c68c49ae82c..3c68c49ae82c 100644
--- a/nixos/tests/testdb.sql
+++ b/nixos/tests/mysql/testdb.sql
diff --git a/pkgs/servers/sql/mariadb/default.nix b/pkgs/servers/sql/mariadb/default.nix
index ea11faf1efcf..9b5118eacc36 100644
--- a/pkgs/servers/sql/mariadb/default.nix
+++ b/pkgs/servers/sql/mariadb/default.nix
@@ -1,4 +1,4 @@
-{ stdenv, fetchurl, fetchFromGitHub, cmake, pkgconfig, makeWrapper, ncurses
+{ stdenv, fetchurl, fetchFromGitHub, cmake, pkgconfig, makeWrapper, ncurses, nixosTests
 , libiconv, openssl, pcre, boost, judy, bison, libxml2, libkrb5, linux-pam, curl
 , libaio, libevent, jemalloc450, jemalloc, cracklib, systemd, perl
 , bzip2, lz4, lzo, snappy, xz, zlib, zstd
@@ -101,6 +101,15 @@ common = rec { # attributes common to both builds
 
   passthru.mysqlVersion = "5.7";
 
+  passthru.tests = {
+    mariadb-galera-mariabackup = nixosTests.mariadb-galera-mariabackup;
+    mariadb-galera-rsync = nixosTests.mariadb-galera-rsync;
+    mysql = nixosTests.mysql;
+    mysql-autobackup = nixosTests.mysql-autobackup;
+    mysql-backup = nixosTests.mysql-backup;
+    mysql-replication = nixosTests.mysql-replication;
+  };
+
   meta = {
 
     description = "An enhanced, drop-in replacement for MySQL";