diff options
Diffstat (limited to 'nixpkgs/nixos/tests/mysql')
| -rw-r--r-- | nixpkgs/nixos/tests/mysql/common.nix | 10 | ||||
| -rw-r--r-- | nixpkgs/nixos/tests/mysql/mariadb-galera.nix | 250 | ||||
| -rw-r--r-- | nixpkgs/nixos/tests/mysql/mysql-autobackup.nix | 53 | ||||
| -rw-r--r-- | nixpkgs/nixos/tests/mysql/mysql-backup.nix | 68 | ||||
| -rw-r--r-- | nixpkgs/nixos/tests/mysql/mysql-replication.nix | 101 | ||||
| -rw-r--r-- | nixpkgs/nixos/tests/mysql/mysql.nix | 151 | ||||
| -rw-r--r-- | nixpkgs/nixos/tests/mysql/testdb.sql | 11 |
7 files changed, 644 insertions, 0 deletions
diff --git a/nixpkgs/nixos/tests/mysql/common.nix b/nixpkgs/nixos/tests/mysql/common.nix new file mode 100644 index 000000000000..1cf52347f4c7 --- /dev/null +++ b/nixpkgs/nixos/tests/mysql/common.nix @@ -0,0 +1,10 @@ +{ lib, pkgs }: { + mariadbPackages = lib.filterAttrs (n: _: lib.hasPrefix "mariadb" n) (import ../../../pkgs/servers/sql/mariadb pkgs); + mysqlPackages = { + inherit (pkgs) mysql80; + }; + perconaPackages = { + inherit (pkgs) percona-server_8_0; + }; + mkTestName = pkg: "mariadb_${builtins.replaceStrings ["."] [""] (lib.versions.majorMinor pkg.version)}"; +} diff --git a/nixpkgs/nixos/tests/mysql/mariadb-galera.nix b/nixpkgs/nixos/tests/mysql/mariadb-galera.nix new file mode 100644 index 000000000000..7455abbce5fb --- /dev/null +++ b/nixpkgs/nixos/tests/mysql/mariadb-galera.nix @@ -0,0 +1,250 @@ +{ + system ? builtins.currentSystem, + config ? {}, + pkgs ? import ../../.. { inherit system config; }, + lib ? pkgs.lib +}: + +let + inherit (import ./common.nix { inherit pkgs lib; }) mkTestName mariadbPackages; + + makeTest = import ./../make-test-python.nix; + + # Common user configuration + makeGaleraTest = { + mariadbPackage, + name ? mkTestName mariadbPackage, + galeraPackage ? pkgs.mariadb-galera + }: makeTest { + name = "${name}-galera-mariabackup"; + meta = { + maintainers = with lib.maintainers; [ izorkin ] ++ lib.teams.helsinki-systems.members; + }; + + # 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 = let + mkGaleraNode = { + id, + method + }: let + address = "192.168.1.${toString id}"; + isFirstClusterNode = id == 1 || id == 4; + in { + users = { + users.testuser = { + isSystemUser = true; + group = "testusers"; + }; + groups.testusers = { }; + }; + + networking = { + interfaces.eth1 = { + ipv4.addresses = [ + { inherit address; prefixLength = 24; } + ]; + }; + extraHosts = lib.concatMapStringsSep "\n" (i: "192.168.1.${toString i} galera_0${toString i}") (lib.range 1 6); + firewall.allowedTCPPorts = [ 3306 4444 4567 4568 ]; + firewall.allowedUDPPorts = [ 4567 ]; + }; + systemd.services.mysql = with pkgs; { + path = with pkgs; [ + bash + gawk + gnutar + gzip + inetutils + iproute2 + netcat + procps + pv + rsync + socat + stunnel + which + ]; + }; + services.mysql = { + enable = true; + package = mariadbPackage; + ensureDatabases = lib.mkIf isFirstClusterNode [ "testdb" ]; + ensureUsers = lib.mkIf isFirstClusterNode [{ + name = "testuser"; + ensurePermissions = { + "testdb.*" = "ALL PRIVILEGES"; + }; + }]; + initialScript = lib.mkIf isFirstClusterNode (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 = "NONE"; + wsrep_retry_autocommit = "3"; + wsrep_provider = "${galeraPackage}/lib/galera/libgalera_smm.so"; + wsrep_cluster_address = "gcomm://" + + lib.optionalString (id == 2 || id == 3) "galera_01,galera_02,galera_03" + + lib.optionalString (id == 5 || id == 6) "galera_04,galera_05,galera_06"; + wsrep_cluster_name = "galera"; + wsrep_node_address = address; + wsrep_node_name = "galera_0${toString id}"; + wsrep_sst_method = method; + wsrep_sst_auth = "check_repl:check_pass"; + binlog_format = "ROW"; + enforce_storage_engine = "InnoDB"; + innodb_autoinc_lock_mode = "2"; + }; + }; + }; + }; + in { + galera_01 = mkGaleraNode { + id = 1; + method = "mariabackup"; + }; + + galera_02 = mkGaleraNode { + id = 2; + method = "mariabackup"; + }; + + galera_03 = mkGaleraNode { + id = 3; + method = "mariabackup"; + }; + + galera_04 = mkGaleraNode { + id = 4; + method = "rsync"; + }; + + galera_05 = mkGaleraNode { + id = 5; + method = "rsync"; + }; + + galera_06 = mkGaleraNode { + id = 6; + method = "rsync"; + }; + + }; + + 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 testuser -e 'use testdb; select test_id from db1;' -N | grep 37" + ) + galera_02.succeed( + "sudo -u testuser mysql -u testuser -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 testuser -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 testuser -e 'show status' -N | grep 'wsrep_cluster_size.*3'" + ) + galera_03.succeed( + "sudo -u testuser mysql -u testuser -e 'show status' -N | grep 'wsrep_local_state_comment.*Synced'" + ) + galera_01.succeed( + "sudo -u testuser mysql -u testuser -e 'use testdb; select test_id from db3;' -N | grep 39" + ) + galera_02.succeed( + "sudo -u testuser mysql -u testuser -e 'use testdb; select test_id from db2;' -N | grep 38" + ) + galera_03.succeed( + "sudo -u testuser mysql -u testuser -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 testuser -e 'use testdb; drop table db2;'") + galera_03.succeed("sudo -u testuser mysql -u testuser -e 'use testdb; drop table db1;'") + galera_01.crash() + galera_02.crash() + galera_03.crash() + + 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 testuser -e 'use testdb; select test_id from db1;' -N | grep 41" + ) + galera_05.succeed( + "sudo -u testuser mysql -u testuser -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 testuser -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 testuser -e 'show status' -N | grep 'wsrep_cluster_size.*3'" + ) + galera_06.succeed( + "sudo -u testuser mysql -u testuser -e 'show status' -N | grep 'wsrep_local_state_comment.*Synced'" + ) + galera_04.succeed( + "sudo -u testuser mysql -u testuser -e 'use testdb; select test_id from db3;' -N | grep 43" + ) + galera_05.succeed( + "sudo -u testuser mysql -u testuser -e 'use testdb; select test_id from db2;' -N | grep 42" + ) + galera_06.succeed( + "sudo -u testuser mysql -u testuser -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 testuser -e 'use testdb; drop table db2;'") + galera_06.succeed("sudo -u testuser mysql -u testuser -e 'use testdb; drop table db1;'") + ''; + }; +in + lib.mapAttrs (_: mariadbPackage: makeGaleraTest { inherit mariadbPackage; }) mariadbPackages diff --git a/nixpkgs/nixos/tests/mysql/mysql-autobackup.nix b/nixpkgs/nixos/tests/mysql/mysql-autobackup.nix new file mode 100644 index 000000000000..b49466db0a9c --- /dev/null +++ b/nixpkgs/nixos/tests/mysql/mysql-autobackup.nix @@ -0,0 +1,53 @@ +{ + system ? builtins.currentSystem, + config ? {}, + pkgs ? import ../../.. { inherit system config; }, + lib ? pkgs.lib +}: + +let + inherit (import ./common.nix { inherit pkgs lib; }) mkTestName mariadbPackages; + + makeTest = import ./../make-test-python.nix; + + makeAutobackupTest = { + package, + name ? mkTestName package, + }: makeTest { + name = "${name}-automysqlbackup"; + meta.maintainers = [ lib.maintainers.aanderse ]; + + nodes.machine = { + services.mysql = { + inherit package; + enable = true; + initialDatabases = [ { name = "testdb"; schema = ./testdb.sql; } ]; + }; + + services.automysqlbackup.enable = true; + }; + + testScript = '' + start_all() + + # Need to have mysql started so that it can be populated with data. + machine.wait_for_unit("mysql.service") + + with subtest("Wait for testdb to be fully populated (5 rows)."): + machine.wait_until_succeeds( + "mysql -u root -D testdb -N -B -e 'select count(id) from tests' | grep -q 5" + ) + + with subtest("Do a backup and wait for it to start"): + machine.start_job("automysqlbackup.service") + machine.wait_for_job("automysqlbackup.service") + + with subtest("wait for backup file and check that data appears in backup"): + machine.wait_for_file("/var/backup/mysql/daily/testdb") + machine.succeed( + "${pkgs.gzip}/bin/zcat /var/backup/mysql/daily/testdb/daily_testdb_*.sql.gz | grep hello" + ) + ''; + }; +in + lib.mapAttrs (_: package: makeAutobackupTest { inherit package; }) mariadbPackages diff --git a/nixpkgs/nixos/tests/mysql/mysql-backup.nix b/nixpkgs/nixos/tests/mysql/mysql-backup.nix new file mode 100644 index 000000000000..451f5c04ce46 --- /dev/null +++ b/nixpkgs/nixos/tests/mysql/mysql-backup.nix @@ -0,0 +1,68 @@ +{ + system ? builtins.currentSystem, + config ? {}, + pkgs ? import ../../.. { inherit system config; }, + lib ? pkgs.lib +}: + +let + inherit (import ./common.nix { inherit pkgs lib; }) mkTestName mariadbPackages; + + makeTest = import ./../make-test-python.nix; + + makeBackupTest = { + package, + name ? mkTestName package + }: makeTest { + name = "${name}-backup"; + + nodes = { + master = { pkgs, ... }: { + services.mysql = { + inherit package; + enable = true; + initialDatabases = [ { name = "testdb"; schema = ./testdb.sql; } ]; + }; + + services.mysqlBackup = { + enable = true; + databases = [ "doesnotexist" "testdb" ]; + }; + }; + }; + + testScript = '' + start_all() + + # Delete backup file that may be left over from a previous test run. + # This is not needed on Hydra but useful for repeated local test runs. + master.execute("rm -f /var/backup/mysql/testdb.gz") + + # Need to have mysql started so that it can be populated with data. + master.wait_for_unit("mysql.service") + + # Wait for testdb to be fully populated (5 rows). + master.wait_until_succeeds( + "mysql -u root -D testdb -N -B -e 'select count(id) from tests' | grep -q 5" + ) + + # Do a backup and wait for it to start + master.start_job("mysql-backup.service") + + # wait for backup to fail, because of database 'doesnotexist' + master.wait_until_fails("systemctl is-active -q mysql-backup.service") + + # wait for backup file and check that data appears in backup + master.wait_for_file("/var/backup/mysql/testdb.gz") + master.succeed( + "${pkgs.gzip}/bin/zcat /var/backup/mysql/testdb.gz | grep hello" + ) + + # Check that a failed backup is logged + master.succeed( + "journalctl -u mysql-backup.service | grep 'fail.*doesnotexist' > /dev/null" + ) + ''; + }; +in + lib.mapAttrs (_: package: makeBackupTest { inherit package; }) mariadbPackages diff --git a/nixpkgs/nixos/tests/mysql/mysql-replication.nix b/nixpkgs/nixos/tests/mysql/mysql-replication.nix new file mode 100644 index 000000000000..83da1e7b6cb8 --- /dev/null +++ b/nixpkgs/nixos/tests/mysql/mysql-replication.nix @@ -0,0 +1,101 @@ +{ + system ? builtins.currentSystem, + config ? {}, + pkgs ? import ../../.. { inherit system config; }, + lib ? pkgs.lib +}: + +let + inherit (import ./common.nix { inherit pkgs lib; }) mkTestName mariadbPackages; + + replicateUser = "replicate"; + replicatePassword = "secret"; + + makeTest = import ./../make-test-python.nix; + + makeReplicationTest = { + package, + name ? mkTestName package, + }: makeTest { + name = "${name}-replication"; + meta = { + maintainers = lib.teams.helsinki-systems.members; + }; + + nodes = { + primary = { + services.mysql = { + inherit package; + enable = true; + replication.role = "master"; + replication.slaveHost = "%"; + replication.masterUser = replicateUser; + replication.masterPassword = replicatePassword; + initialDatabases = [ { name = "testdb"; schema = ./testdb.sql; } ]; + }; + networking.firewall.allowedTCPPorts = [ 3306 ]; + }; + + secondary1 = { nodes, ... }: { + services.mysql = { + inherit package; + enable = true; + replication.role = "slave"; + replication.serverId = 2; + replication.masterHost = nodes.primary.networking.hostName; + replication.masterUser = replicateUser; + replication.masterPassword = replicatePassword; + }; + }; + + secondary2 = { nodes, ... }: { + services.mysql = { + inherit package; + enable = true; + replication.role = "slave"; + replication.serverId = 3; + replication.masterHost = nodes.primary.networking.hostName; + replication.masterUser = replicateUser; + replication.masterPassword = replicatePassword; + }; + }; + }; + + testScript = '' + primary.start() + primary.wait_for_unit("mysql") + primary.wait_for_open_port(3306) + # Wait for testdb to be fully populated (5 rows). + primary.wait_until_succeeds( + "sudo -u mysql mysql -u mysql -D testdb -N -B -e 'select count(id) from tests' | grep -q 5" + ) + + secondary1.start() + secondary2.start() + secondary1.wait_for_unit("mysql") + secondary1.wait_for_open_port(3306) + secondary2.wait_for_unit("mysql") + secondary2.wait_for_open_port(3306) + + # wait for replications to finish + secondary1.wait_until_succeeds( + "sudo -u mysql mysql -u mysql -D testdb -N -B -e 'select count(id) from tests' | grep -q 5" + ) + secondary2.wait_until_succeeds( + "sudo -u mysql mysql -u mysql -D testdb -N -B -e 'select count(id) from tests' | grep -q 5" + ) + + secondary2.succeed("systemctl stop mysql") + primary.succeed( + "echo 'insert into testdb.tests values (123, 456);' | sudo -u mysql mysql -u mysql -N" + ) + secondary2.succeed("systemctl start mysql") + secondary2.wait_for_unit("mysql") + secondary2.wait_for_open_port(3306) + secondary2.wait_until_succeeds( + "echo 'select * from testdb.tests where Id = 123;' | sudo -u mysql mysql -u mysql -N | grep 456" + ) + ''; + }; +in + lib.mapAttrs (_: package: makeReplicationTest { inherit package; }) mariadbPackages diff --git a/nixpkgs/nixos/tests/mysql/mysql.nix b/nixpkgs/nixos/tests/mysql/mysql.nix new file mode 100644 index 000000000000..0a61f9d38fe2 --- /dev/null +++ b/nixpkgs/nixos/tests/mysql/mysql.nix @@ -0,0 +1,151 @@ +{ + system ? builtins.currentSystem, + config ? {}, + pkgs ? import ../../.. { inherit system config; }, + lib ? pkgs.lib +}: + +let + inherit (import ./common.nix { inherit pkgs lib; }) mkTestName mariadbPackages mysqlPackages perconaPackages; + + makeTest = import ./../make-test-python.nix; + # Setup common users + makeMySQLTest = { + package, + name ? mkTestName package, + useSocketAuth ? true, + hasMroonga ? true, + hasRocksDB ? pkgs.stdenv.hostPlatform.is64bit + }: makeTest { + inherit name; + meta = { + maintainers = lib.teams.helsinki-systems.members; + }; + + nodes = { + ${name} = + { pkgs, ... }: { + + users = { + groups.testusers = { }; + + users.testuser = { + isSystemUser = true; + group = "testusers"; + }; + + users.testuser2 = { + isSystemUser = true; + group = "testusers"; + }; + }; + + services.mysql = { + enable = true; + initialDatabases = [ + { name = "testdb3"; schema = ./testdb.sql; } + ]; + # note that using pkgs.writeText here is generally not a good idea, + # as it will store the password in world-readable /nix/store ;) + initialScript = pkgs.writeText "mysql-init.sql" (if (!useSocketAuth) then '' + CREATE USER 'testuser3'@'localhost' IDENTIFIED BY 'secure'; + GRANT ALL PRIVILEGES ON testdb3.* TO 'testuser3'@'localhost'; + '' else '' + ALTER USER root@localhost IDENTIFIED WITH unix_socket; + DELETE FROM mysql.user WHERE password = ''' AND plugin = '''; + DELETE FROM mysql.user WHERE user = '''; + FLUSH PRIVILEGES; + ''); + + ensureDatabases = [ "testdb" "testdb2" ]; + ensureUsers = [{ + name = "testuser"; + ensurePermissions = { + "testdb.*" = "ALL PRIVILEGES"; + }; + } { + name = "testuser2"; + ensurePermissions = { + "testdb2.*" = "ALL PRIVILEGES"; + }; + }]; + package = package; + settings = { + mysqld = { + plugin-load-add = lib.optional hasMroonga "ha_mroonga.so" + ++ lib.optional hasRocksDB "ha_rocksdb.so"; + }; + }; + }; + }; + }; + + testScript = '' + start_all() + + machine = ${name} + machine.wait_for_unit("mysql") + machine.succeed( + "echo 'use testdb; create table tests (test_id INT, PRIMARY KEY (test_id));' | sudo -u testuser mysql -u testuser" + ) + machine.succeed( + "echo 'use testdb; insert into tests values (42);' | sudo -u testuser mysql -u testuser" + ) + # Ensure testuser2 is not able to insert into testdb as mysql testuser2 + machine.fail( + "echo 'use testdb; insert into tests values (23);' | sudo -u testuser2 mysql -u testuser2" + ) + # Ensure testuser2 is not able to authenticate as mysql testuser + machine.fail( + "echo 'use testdb; insert into tests values (23);' | sudo -u testuser2 mysql -u testuser" + ) + machine.succeed( + "echo 'use testdb; select test_id from tests;' | sudo -u testuser mysql -u testuser -N | grep 42" + ) + + ${lib.optionalString hasMroonga '' + # Check if Mroonga plugin works + machine.succeed( + "echo 'use testdb; create table mroongadb (test_id INT, PRIMARY KEY (test_id)) ENGINE = Mroonga;' | sudo -u testuser mysql -u testuser" + ) + machine.succeed( + "echo 'use testdb; insert into mroongadb values (25);' | sudo -u testuser mysql -u testuser" + ) + machine.succeed( + "echo 'use testdb; select test_id from mroongadb;' | sudo -u testuser mysql -u testuser -N | grep 25" + ) + machine.succeed( + "echo 'use testdb; drop table mroongadb;' | sudo -u testuser mysql -u testuser" + ) + ''} + + ${lib.optionalString hasRocksDB '' + # Check if RocksDB plugin works + machine.succeed( + "echo 'use testdb; create table rocksdb (test_id INT, PRIMARY KEY (test_id)) ENGINE = RocksDB;' | sudo -u testuser mysql -u testuser" + ) + machine.succeed( + "echo 'use testdb; insert into rocksdb values (28);' | sudo -u testuser mysql -u testuser" + ) + machine.succeed( + "echo 'use testdb; select test_id from rocksdb;' | sudo -u testuser mysql -u testuser -N | grep 28" + ) + machine.succeed( + "echo 'use testdb; drop table rocksdb;' | sudo -u testuser mysql -u testuser" + ) + ''} + ''; + }; +in + lib.mapAttrs (_: package: makeMySQLTest { + inherit package; + hasRocksDB = false; hasMroonga = false; useSocketAuth = false; + }) mysqlPackages + // (lib.mapAttrs (_: package: makeMySQLTest { + inherit package; + }) mariadbPackages) + // (lib.mapAttrs (_: package: makeMySQLTest { + inherit package; + name = "percona_8_0"; + hasMroonga = false; useSocketAuth = false; + }) perconaPackages) diff --git a/nixpkgs/nixos/tests/mysql/testdb.sql b/nixpkgs/nixos/tests/mysql/testdb.sql new file mode 100644 index 000000000000..3c68c49ae82c --- /dev/null +++ b/nixpkgs/nixos/tests/mysql/testdb.sql @@ -0,0 +1,11 @@ +create table tests +( Id INTEGER NOT NULL, + Name VARCHAR(255) NOT NULL, + primary key(Id) +); + +insert into tests values (1, 'a'); +insert into tests values (2, 'b'); +insert into tests values (3, 'c'); +insert into tests values (4, 'd'); +insert into tests values (5, 'hello'); |