mariadb: change location configuration file to /etc/my.cnf #51314

Izorkin · 2018-12-01T12:24:56Z

Motivation for this change

Change location configuration file to /etc/my.cnf
Required for normal works mysql utilities.

Things done

infinisil · 2018-12-02T17:15:55Z

Required for normal works mysql utilities.

Can you expand on that? Why is this needed? Preferably also add this in the commit message.

Izorkin · 2018-12-02T19:30:47Z

It is more convenient to view/edit my.cnf in default and static location.
mysql, mysql_install_db, mariabackup are looking by default for my.cnf in /etc. After this change, one does not need to always specify --defaults-extra-file, to point the utilities to non-default location of my.cnf.

nixos/modules/services/databases/mysql.nix

Izorkin · 2018-12-07T20:38:34Z

Thanks!

andir · 2019-05-17T11:13:57Z

By the looks of it this seems to have broken the mysqlReplication test:
https://hydra.nixos.org/build/85547230

Izorkin · 2019-05-17T12:30:28Z

@andir this correction method will do?

diff --git a/nixos/modules/services/databases/mysql.nix b/nixos/modules/services/databases/mysql.nix
index 89291d4438f..a98bacf3a75 100644
--- a/nixos/modules/services/databases/mysql.nix
+++ b/nixos/modules/services/databases/mysql.nix
@@ -289,6 +289,7 @@ in
       ${optionalString (cfg.bind != null) "bind-address = ${cfg.bind}" }
       ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "log-bin=mysql-bin"}
       ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "server-id = ${toString cfg.replication.serverId}"}
+      ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "slave-skip-errors = 1062"}
       ${optionalString (cfg.ensureUsers != [])
       ''
         plugin-load-add = auth_socket.so

flokli · 2019-05-17T12:37:28Z

@Izorkin I once tried that too, and this gets the vm test to succeed, but as @grahamc mentioned, this is a terrible hack - we can't just ignore all duplicate keys errors on slave replication.

Izorkin · 2019-05-17T21:34:49Z

@flokli @andir please check this variant

diff --git a/nixos/modules/services/databases/mysql.nix b/nixos/modules/services/databases/mysql.nix
index 89291d4438f..de313d0c454 100644
--- a/nixos/modules/services/databases/mysql.nix
+++ b/nixos/modules/services/databases/mysql.nix
@@ -318,7 +318,7 @@ in
             if ! test -e ${cfg.dataDir}/mysql; then
                 mkdir -m 0700 -p ${cfg.dataDir}
                 chown -R ${cfg.user} ${cfg.dataDir}
-                ${mysql}/bin/mysql_install_db --defaults-file=/etc/my.cnf ${installOptions}
+                ${mysql}/bin/mysql_install_db ${installOptions}
                 touch /tmp/mysql_init
             fi

How to leave the parameter mysql_install_db --defaults-file=/etc/my.cnf in non replication mode?

flokli · 2019-05-17T22:10:20Z

Interresting - this seems to work indeed!

@Izorkin, can you explain why we should not pass /etc/my.cnf to mysql_install_db?

Izorkin · 2019-05-18T08:42:47Z

@flokli need create new db without log-bin=mysql-bin
example

diff --git a/nixos/modules/services/databases/mysql.nix b/nixos/modules/services/databases/mysql.nix
index 89291d4438f..a200cab4ba1 100644
--- a/nixos/modules/services/databases/mysql.nix
+++ b/nixos/modules/services/databases/mysql.nix
@@ -281,6 +281,13 @@ in

     environment.systemPackages = [mysql];

+    environment.etc."my-test.cnf".text =
+    ''
+      [mysqld]
+      port = 3306
+      datadir = /var/lib/mysql
+    '';
+
     environment.etc."my.cnf".text =
     ''
       [mysqld]
@@ -288,6 +295,8 @@ in
       datadir = ${cfg.dataDir}
       ${optionalString (cfg.bind != null) "bind-address = ${cfg.bind}" }
       ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "log-bin=mysql-bin"}
+      ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "log-bin-index=mysql-bin.index"}
+      ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "relay-log=mysql-relay-bin"}
       ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "server-id = ${toString cfg.replication.serverId}"}
       ${optionalString (cfg.ensureUsers != [])
       ''
@@ -318,7 +327,7 @@ in
             if ! test -e ${cfg.dataDir}/mysql; then
                 mkdir -m 0700 -p ${cfg.dataDir}
                 chown -R ${cfg.user} ${cfg.dataDir}
-                ${mysql}/bin/mysql_install_db --defaults-file=/etc/my.cnf ${installOptions}
+                ${mysql}/bin/mysql_install_db --defaults-file=/etc/my-test.cnf ${installOptions}
                 touch /tmp/mysql_init
             fi

I do not know how to fix it

And need to add replication
--log-basename = master/slave1/slave2
--relay-log=mysqld-relay-bin
https://mariadb.com/kb/en/library/setting-up-replication/

Izorkin · 2019-05-18T09:26:41Z

Normally this variant?

diff --git a/nixos/modules/services/databases/mysql.nix b/nixos/modules/services/databases/mysql.nix
index 89291d4438f..a63b8956518 100644
--- a/nixos/modules/services/databases/mysql.nix
+++ b/nixos/modules/services/databases/mysql.nix
@@ -287,7 +287,9 @@ in
       port = ${toString cfg.port}
       datadir = ${cfg.dataDir}
       ${optionalString (cfg.bind != null) "bind-address = ${cfg.bind}" }
-      ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "log-bin=mysql-bin"}
+      ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "log-bin=mysql-bin-${toString cfg.replication.serverId}"}
+      ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "log-bin-index=mysql-bin-${toString cfg.replication.serverId}.index"}
+      ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "relay-log=mysql-relay-bin"}
       ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "server-id = ${toString cfg.replication.serverId}"}
       ${optionalString (cfg.ensureUsers != [])
       ''
@@ -319,6 +321,7 @@ in
                 mkdir -m 0700 -p ${cfg.dataDir}
                 chown -R ${cfg.user} ${cfg.dataDir}
                 ${mysql}/bin/mysql_install_db --defaults-file=/etc/my.cnf ${installOptions}
+                ${optionalString (cfg.replication.role == "master" || cfg.replication.role == "slave") "rm ${cfg.dataDir}/mysql-bin-${toString cfg.replication.serverId}.*"}
                 touch /tmp/mysql_init
             fi

flokli · 2019-05-18T10:07:13Z

I also saw the log messages recommending to setup a relay-log, so this might be a good idea.

Not so sure about the rm ${cfg.dataDir}/mysql-bin-${toString cfg.replication.serverId}.* part - why would we want to remove the binary log from servers or slaves during initial setup?

Izorkin · 2019-05-18T10:15:10Z

mysql-bin log causes replication conflict, since perhaps they are identical (sorry, bad english)

flokli · 2019-05-18T10:39:51Z

No worries, thanks for your help! But this should be then a bug upstream, shouldn't it? How come it was introduced by this PR?

Izorkin · 2019-05-18T10:42:39Z

Not running check mysqlReplication (
Create new PR with latest variant?

flokli · 2019-05-18T13:19:10Z

Let's continue discussion in #61671. Thanks!

Izorkin requested a review from infinisil as a code owner December 1, 2018 12:24

GrahamcOfBorg added 6.topic: nixos 8.has: module (update) 10.rebuild-darwin: 0 10.rebuild-linux: 0 labels Dec 1, 2018

Izorkin force-pushed the mariadb-my.cnf branch from 441d218 to 8a6c3f3 Compare December 1, 2018 13:25

Mic92 approved these changes Dec 2, 2018

View reviewed changes

mariadb: change location configuration file to /etc/my.cnf

953be3e

Izorkin force-pushed the mariadb-my.cnf branch from 8a6c3f3 to 953be3e Compare December 2, 2018 19:31

Izorkin changed the title ~~mariadb: change location my.cnf~~ mariadb: change location configuration file to /etc/my.cnf Dec 2, 2018

Mic92 reviewed Dec 2, 2018

View reviewed changes

nixos/modules/services/databases/mysql.nix Show resolved Hide resolved

grahamc merged commit ca3f089 into NixOS:master Dec 7, 2018

Izorkin deleted the mariadb-my.cnf branch December 7, 2018 20:38

andir mentioned this pull request May 17, 2019

nixosTests.mysqlReplication is broken #61523

Closed

flokli mentioned this pull request May 18, 2019

nixos/mysql: fix mysqlReplication test #61671

Merged

10 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

mariadb: change location configuration file to /etc/my.cnf #51314

mariadb: change location configuration file to /etc/my.cnf #51314

Izorkin commented Dec 1, 2018 •

edited

infinisil commented Dec 2, 2018

Izorkin commented Dec 2, 2018

Izorkin commented Dec 7, 2018

andir commented May 17, 2019

Izorkin commented May 17, 2019

flokli commented May 17, 2019

Izorkin commented May 17, 2019

flokli commented May 17, 2019

Izorkin commented May 18, 2019

Izorkin commented May 18, 2019

flokli commented May 18, 2019

Izorkin commented May 18, 2019

flokli commented May 18, 2019 via email

Izorkin commented May 18, 2019 •

edited

flokli commented May 18, 2019

mariadb: change location configuration file to /etc/my.cnf #51314

mariadb: change location configuration file to /etc/my.cnf #51314

Conversation

Izorkin commented Dec 1, 2018 • edited

Motivation for this change

Things done

infinisil commented Dec 2, 2018

Izorkin commented Dec 2, 2018

Izorkin commented Dec 7, 2018

andir commented May 17, 2019

Izorkin commented May 17, 2019

flokli commented May 17, 2019

Izorkin commented May 17, 2019

flokli commented May 17, 2019

Izorkin commented May 18, 2019

Izorkin commented May 18, 2019

flokli commented May 18, 2019

Izorkin commented May 18, 2019

flokli commented May 18, 2019 via email

Izorkin commented May 18, 2019 • edited

flokli commented May 18, 2019

Izorkin commented Dec 1, 2018 •

edited

Izorkin commented May 18, 2019 •

edited