diff options
Diffstat (limited to 'nixos/tests/mysql')
| -rw-r--r-- | nixos/tests/mysql/mariadb-galera-mariabackup.nix | 223 | ||||
| -rw-r--r-- | nixos/tests/mysql/mariadb-galera-rsync.nix | 216 | ||||
| -rw-r--r-- | nixos/tests/mysql/mysql-autobackup.nix | 38 | ||||
| -rw-r--r-- | nixos/tests/mysql/mysql-backup.nix | 56 | ||||
| -rw-r--r-- | nixos/tests/mysql/mysql-replication.nix | 89 | ||||
| -rw-r--r-- | nixos/tests/mysql/mysql.nix | 143 | ||||
| -rw-r--r-- | nixos/tests/mysql/testdb.sql | 11 |
7 files changed, 776 insertions, 0 deletions
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/mysql/mysql-autobackup.nix b/nixos/tests/mysql/mysql-autobackup.nix new file mode 100644 index 000000000000..65576e52a537 --- /dev/null +++ b/nixos/tests/mysql/mysql-autobackup.nix @@ -0,0 +1,38 @@ +import ./../make-test-python.nix ({ pkgs, lib, ... }: + +{ + name = "automysqlbackup"; + meta.maintainers = [ lib.maintainers.aanderse ]; + + machine = + { pkgs, ... }: + { + services.mysql.enable = true; + services.mysql.package = pkgs.mysql; + services.mysql.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" + ) + ''; +}) diff --git a/nixos/tests/mysql/mysql-backup.nix b/nixos/tests/mysql/mysql-backup.nix new file mode 100644 index 000000000000..c4c1079a8a64 --- /dev/null +++ b/nixos/tests/mysql/mysql-backup.nix @@ -0,0 +1,56 @@ +# Test whether mysqlBackup option works +import ./../make-test-python.nix ({ pkgs, ... } : { + name = "mysql-backup"; + meta = with pkgs.stdenv.lib.maintainers; { + maintainers = [ rvl ]; + }; + + nodes = { + master = { pkgs, ... }: { + services.mysql = { + enable = true; + initialDatabases = [ { name = "testdb"; schema = ./testdb.sql; } ]; + package = pkgs.mysql; + }; + + 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") + master.wait_for_unit("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" + ) + ''; +}) diff --git a/nixos/tests/mysql/mysql-replication.nix b/nixos/tests/mysql/mysql-replication.nix new file mode 100644 index 000000000000..81038dccd947 --- /dev/null +++ b/nixos/tests/mysql/mysql-replication.nix @@ -0,0 +1,89 @@ +import ./../make-test-python.nix ({ pkgs, ...} : + +let + replicateUser = "replicate"; + replicatePassword = "secret"; +in + +{ + name = "mysql-replication"; + meta = with pkgs.stdenv.lib.maintainers; { + maintainers = [ eelco shlevy ]; + }; + + nodes = { + master = + { pkgs, ... }: + + { + services.mysql.enable = true; + services.mysql.package = pkgs.mysql; + services.mysql.replication.role = "master"; + services.mysql.replication.slaveHost = "%"; + services.mysql.replication.masterUser = replicateUser; + services.mysql.replication.masterPassword = replicatePassword; + services.mysql.initialDatabases = [ { name = "testdb"; schema = ./testdb.sql; } ]; + networking.firewall.allowedTCPPorts = [ 3306 ]; + }; + + slave1 = + { pkgs, nodes, ... }: + + { + services.mysql.enable = true; + services.mysql.package = pkgs.mysql; + services.mysql.replication.role = "slave"; + services.mysql.replication.serverId = 2; + services.mysql.replication.masterHost = nodes.master.config.networking.hostName; + services.mysql.replication.masterUser = replicateUser; + services.mysql.replication.masterPassword = replicatePassword; + }; + + slave2 = + { pkgs, nodes, ... }: + + { + services.mysql.enable = true; + services.mysql.package = pkgs.mysql; + services.mysql.replication.role = "slave"; + services.mysql.replication.serverId = 3; + services.mysql.replication.masterHost = nodes.master.config.networking.hostName; + services.mysql.replication.masterUser = replicateUser; + services.mysql.replication.masterPassword = replicatePassword; + }; + }; + + testScript = '' + master.start() + master.wait_for_unit("mysql") + master.wait_for_open_port(3306) + # 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" + ) + + slave1.start() + slave2.start() + slave1.wait_for_unit("mysql") + slave1.wait_for_open_port(3306) + slave2.wait_for_unit("mysql") + slave2.wait_for_open_port(3306) + + # wait for replications to finish + slave1.wait_until_succeeds( + "mysql -u root -D testdb -N -B -e 'select count(id) from tests' | grep -q 5" + ) + slave2.wait_until_succeeds( + "mysql -u root -D testdb -N -B -e 'select count(id) from tests' | grep -q 5" + ) + + slave2.succeed("systemctl stop mysql") + master.succeed("echo 'insert into testdb.tests values (123, 456);' | mysql -u root -N") + slave2.succeed("systemctl start mysql") + slave2.wait_for_unit("mysql") + slave2.wait_for_open_port(3306) + slave2.wait_until_succeeds( + "echo 'select * from testdb.tests where Id = 123;' | mysql -u root -N | grep 456" + ) + ''; +}) diff --git a/nixos/tests/mysql/mysql.nix b/nixos/tests/mysql/mysql.nix new file mode 100644 index 000000000000..d236ce946328 --- /dev/null +++ b/nixos/tests/mysql/mysql.nix @@ -0,0 +1,143 @@ +import ./../make-test-python.nix ({ pkgs, ...} : { + name = "mysql"; + meta = with pkgs.stdenv.lib.maintainers; { + maintainers = [ eelco shlevy ]; + }; + + nodes = { + mysql = + { pkgs, ... }: + + { + services.mysql.enable = true; + services.mysql.initialDatabases = [ + { name = "testdb"; schema = ./testdb.sql; } + { name = "empty_testdb"; } + ]; + # note that using pkgs.writeText here is generally not a good idea, + # as it will store the password in world-readable /nix/store ;) + services.mysql.initialScript = pkgs.writeText "mysql-init.sql" '' + CREATE USER 'passworduser'@'localhost' IDENTIFIED BY 'password123'; + ''; + services.mysql.package = pkgs.mysql57; + }; + + mysql80 = + { pkgs, ... }: + + { + # prevent oom: + # Kernel panic - not syncing: Out of memory: compulsory panic_on_oom is enabled + virtualisation.memorySize = 1024; + + services.mysql.enable = true; + services.mysql.initialDatabases = [ + { name = "testdb"; schema = ./testdb.sql; } + { name = "empty_testdb"; } + ]; + # note that using pkgs.writeText here is generally not a good idea, + # as it will store the password in world-readable /nix/store ;) + services.mysql.initialScript = pkgs.writeText "mysql-init.sql" '' + CREATE USER 'passworduser'@'localhost' IDENTIFIED BY 'password123'; + ''; + services.mysql.package = pkgs.mysql80; + }; + + mariadb = + { pkgs, ... }: + + { + users.users.testuser = { }; + users.users.testuser2 = { }; + services.mysql.enable = true; + services.mysql.initialScript = pkgs.writeText "mariadb-init.sql" '' + ALTER USER root@localhost IDENTIFIED WITH unix_socket; + DELETE FROM mysql.user WHERE password = ''' AND plugin = '''; + DELETE FROM mysql.user WHERE user = '''; + FLUSH PRIVILEGES; + ''; + services.mysql.ensureDatabases = [ "testdb" "testdb2" ]; + services.mysql.ensureUsers = [{ + name = "testuser"; + ensurePermissions = { + "testdb.*" = "ALL PRIVILEGES"; + }; + } { + name = "testuser2"; + ensurePermissions = { + "testdb2.*" = "ALL PRIVILEGES"; + }; + }]; + services.mysql.settings = { + mysqld = { + plugin-load-add = [ "ha_tokudb.so" "ha_rocksdb.so" ]; + }; + }; + services.mysql.package = pkgs.mariadb; + }; + + }; + + testScript = '' + start_all() + + mysql.wait_for_unit("mysql") + mysql.succeed("echo 'use empty_testdb;' | mysql -u root") + mysql.succeed("echo 'use testdb; select * from tests;' | mysql -u root -N | grep 4") + # ';' acts as no-op, just check whether login succeeds with the user created from the initialScript + mysql.succeed("echo ';' | mysql -u passworduser --password=password123") + + mysql80.wait_for_unit("mysql") + mysql80.succeed("echo 'use empty_testdb;' | mysql -u root") + mysql80.succeed("echo 'use testdb; select * from tests;' | mysql -u root -N | grep 4") + # ';' acts as no-op, just check whether login succeeds with the user created from the initialScript + mysql80.succeed("echo ';' | mysql -u passworduser --password=password123") + + mariadb.wait_for_unit("mysql") + mariadb.succeed( + "echo 'use testdb; create table tests (test_id INT, PRIMARY KEY (test_id));' | sudo -u testuser mysql -u testuser" + ) + mariadb.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 + mariadb.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 + mariadb.fail( + "echo 'use testdb; insert into tests values (23);' | sudo -u testuser2 mysql -u testuser" + ) + mariadb.succeed( + "echo 'use testdb; select test_id from tests;' | sudo -u testuser mysql -u testuser -N | grep 42" + ) + + # Check if TokuDB plugin works + mariadb.succeed( + "echo 'use testdb; create table tokudb (test_id INT, PRIMARY KEY (test_id)) ENGINE = TokuDB;' | sudo -u testuser mysql -u testuser" + ) + mariadb.succeed( + "echo 'use testdb; insert into tokudb values (25);' | sudo -u testuser mysql -u testuser" + ) + mariadb.succeed( + "echo 'use testdb; select test_id from tokudb;' | sudo -u testuser mysql -u testuser -N | grep 25" + ) + mariadb.succeed( + "echo 'use testdb; drop table tokudb;' | sudo -u testuser mysql -u testuser" + ) + + # Check if RocksDB plugin works + mariadb.succeed( + "echo 'use testdb; create table rocksdb (test_id INT, PRIMARY KEY (test_id)) ENGINE = RocksDB;' | sudo -u testuser mysql -u testuser" + ) + mariadb.succeed( + "echo 'use testdb; insert into rocksdb values (28);' | sudo -u testuser mysql -u testuser" + ) + mariadb.succeed( + "echo 'use testdb; select test_id from rocksdb;' | sudo -u testuser mysql -u testuser -N | grep 28" + ) + mariadb.succeed( + "echo 'use testdb; drop table rocksdb;' | sudo -u testuser mysql -u testuser" + ) + ''; +}) diff --git a/nixos/tests/mysql/testdb.sql b/nixos/tests/mysql/testdb.sql new file mode 100644 index 000000000000..3c68c49ae82c --- /dev/null +++ b/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'); |