MariaDB Master-Master Cluster: Datenbank-ignore-Einträge werden ignoriert!!

jmar83

SQL-Guru
Beiträge
146
Hallo zusammen

Vorab: Zuerst habe ich vergessen, die entsprechenden Datenbanken auf die Ignorierliste zu setzen, evtl. hat das nun zu Folge dass mir nix anderes übrig bleibt als "apt-get purge mariadb" sowie die Übrigbleibsel noch von Hand aufzuräumen. Trotzdem wäre es aber hochinteressant zu wissen, wo genau das Problem liegt!

Ausgangssituation ist folgende:

DB-Server 1: Bei uns vor Ort. Dieser beinhaltet folgende Datenbanken:

- information_schema (MariaDB System DB)
- mysql (MariaDB System DB)
- performance_schema (MariaDB System DB)
- pf_server (Selbst erstellt)
- phpmyadmin (von phpmyadmin halt)
- r_17 (Selbst erstellt)
- r_21 (Selbst erstellt)
- r_22 (Selbst erstellt)
- r_23 (Selbst erstellt)
- r_636 (Selbst erstellt)
- r_preset (Selbst erstellt)


DB-Server 2: Im Rechenzentrum. Dieser beinhaltet diese Datenbanken:

- apsc (von Plesk Obsidian 18.0.x)
- horde (von Plesk Obsidian 18.0x)
- information_schema (MariaDB System DB)
- mysql (Mariadb System DB)
- performance_schema (MariaDB System DB)
- pf_server (Selbst erstellt)
- phpmyadmin_xxxxxxxxxxxx (phpadmin-DB, welche ebenfalls Plesk erstellt hat)
- psa (von Plesk Obsidian 18.0.x)
- r_1 (Selbst erstellt)
- r_preset (Selbst erstellt)


Server Daten:

DB-Server 1: MariaDB 10.1.26, Debian Stretch auf den neusten Stand gebracht mit apt-get upgrade
DB-Server 2: MariaDB 10.1.43, Ubuntu LTS 18.x, Plesk Obsidian 18.0x


Inhalt der Datei /etc/mysql/mariadb.conf.d/50-server.cnf von Server 1:

Code:
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
skip-external-locking

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address        = 127.0.0.1

#
# * Fine Tuning
#
key_buffer_size        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

#
# * Query Cache Configuration
#
query_cache_limit    = 1M
query_cache_size        = 16M

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_rate_limit    = 1000
#log_slow_verbosity    = query_plan
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id        = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db        = include_database_name
#binlog_ignore_db    = exclude_database_name

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
# ssl-cipher=TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
# ssl=on

#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * Unix socket authentication plugin is built-in since 10.0.22-6
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]

# J.M., 2019-12-10 {
# Master-Master Cluster
  bind-address = 0.0.0.0
  server_id = 1
  log_bin = /var/log/mysql/mysql-bin.log
  log_bin_index = /var/log/mysql/mysql-bin.log.index
  relay_log = /var/log/mysql/mysql-relay-bin
  relay_log_index = /var/log/mysql/mysql-relay-bin.index
  expire_logs_days = 10
  max_binlog_size = 100M
  log_slave_updates = 1
  auto-increment-increment = 2
  auto-increment-offset = 1
  #skip-host-cache
  #skip-name-resolve
  #binlog-ignore-db    = information_schema, mysql, performance_schema, phpmyadmin, r_preset, pf_server, r_17, r_21, r_22, r_23, r_636
  #replicate-ignore-db = information_schema, mysql, performance_schema

  replicate-ignore-db = information_schema, mysql, performance_schema, apsc, horde, phpmyadmin_Lje7YSVLKyOz, psa, pf_server, r_preset, r_1

# } J.M., 2019-12-10


Teil 2 unten:
 
Werbung:
Inhalt der Datei /etc/mysql/mariadb.conf.d/50-server.cnf von Server 2:

Code:
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
skip-external-locking

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.

# J.M., 2019-12-10 {
# Commented out
#bind-address        = 127.0.0.1
# } J.M., 2019-12-10


#
# * Fine Tuning
#
key_buffer_size        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

#
# * Query Cache Configuration
#
query_cache_limit    = 1M
query_cache_size        = 16M

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_rate_limit    = 1000
#log_slow_verbosity    = query_plan
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id        = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db        = include_database_name
#binlog_ignore_db    = exclude_database_name

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
# ssl-cipher=TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
# ssl=on

#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * Unix socket authentication plugin is built-in since 10.0.22-6
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]

# J.M., 2019-12-10 {
# Master-Master Cluster
  bind-address = 0.0.0.0
  server_id = 2
  log_bin = /var/log/mysql/mysql-bin.log
  log_bin_index = /var/log/mysql/mysql-bin.log.index
  relay_log = /var/log/mysql/mysql-relay-bin
  relay_log_index = /var/log/mysql/mysql-relay-bin.index
  expire_logs_days = 10
  max_binlog_size = 100M
  log_slave_updates = 1
  auto-increment-increment = 2
  auto-increment-offset = 2
  #skip-host-cache
  #skip-name-resolve
  #binlog-ignore-db    = information_schema, mysql, performance_schema, apsc, horde, phpmyadmin_Lje7YSVLKyOz, psa, pf_server, r_preset, r_1
  #replicate-ignore-db = information_schema, mysql, performance_schema

  replicate-ignore-db = information_schema, mysql, performance_schema, phpmyadmin, r_preset, pf_server, r_17, r_21, r_22, r_23, r_636

# } J.M., 2019-12-10


Nach der Anpassungen habe ich selbstverständlich die Dienste neu gestartet!


Teil 3 unten...
 
Erstellt habe ich den Master-Master-Cluster folgendermassen (Beim ersten Mal habe ich aber die `binlog-ignore-db`- sowie `replication-ignore-db`-Einträgen oben vergessen zu setzen):

Replication-User auf Server 1 erstellt:

Code:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_server1'@'%' IDENTIFIED BY 'xxx';
UPDATE mysql.user SET authentication_string = PASSWORD('xxx') WHERE User = 'replication_server1';
FLUSH PRIVILEGES;


Replication-User auf Server 2 erstellt:

Code:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_server2'@'%' IDENTIFIED BY 'xxx';
UPDATE mysql.user SET authentication_string = PASSWORD('xxx') WHERE User = 'replication_server2';
FLUSH PRIVILEGES;

(Das `UPDATE mysql.user ...` mache ich jeweils aus Kompatbilitätsgründen, ich glaube ich hatte schon Probleme (ältere MySQL-/MariaDB-Versionen??) als ich das nicht gemacht habe...


Anschliessend habe ich wieder die Dienste neu gestartet...


Dann habe ich mich mit `mysql -u replication -p -h REMOTE_IP` auf den jeweils anderen Server eingeloggt, um zu testen ob das remote-Login funktioniert. Das hat es.

Anschliessend

- Ein "ganz normales", lokales Login auf Server 1 über `mysql -u root -p`
- Dann den SQL-Befehl "SHOW MASTER STATUS" eingegeben, dieser gab mir folgende Parameter aus:
- `master_log_file`
- `master_log_pos`
- Dei auf die Ignorierliste gesetzten Datenbanken (später, beim ersten Mal habe ich das wie gesagt vergessen), also nicht das was der Slave nicht empfangen soll, sondern das was der Master nicht, zwecks Replikation, loggen soll -> also die DBs hinter dem MariaDB-Konfig-Parameter `binlog-ignore-db`

- Dann habe ich ein Konsolenfenster von Server 2 (mit Plesk) geöffnet und dort `mysql -u root -p` eingegeben und anschliessend folgende SQL-Statements ausgeführt:

- STOP SLAVE;
- CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='replication_server1', master_password='xxx', master_log_file='mysql-bin.000001', master_log_pos=313;
- START SLAVE;
- FLUSH PRIVILEGES;

...sowie anschliessend den MariaDB-Dienst neu gestartet.



Das gleiche bei Server 2:

- Ein "ganz normales", lokales Login auf Server 2 über `mysql -u root -p`
- Dann den SQL-Befehl "SHOW MASTER STATUS" eingegeben, dieser gab mir folgende Parameter aus:
- `master_log_file`
- `master_log_pos`
- Dei auf die Ignorierliste gesetzten Datenbanken (später, beim ersten Mal habe ich das wie gesagt vergessen), also nicht das was der Slave nicht empfangen soll, sondern das was der Master nicht, zwecks Replikation, loggen soll -> also die DBs hinter dem MariaDB-Konfig-Parameter `binlog-ignore-db`

- Dann habe ich wieder das Konsolenfenster von Server 1 fokussiert und dort wieder (analog dem vorherigen Vorgang) folgende SQL-Statements ausgeführt:

- STOP SLAVE;
- CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='replication_server2', master_password='xxx', master_log_file='mysql-bin.000001', master_log_pos=313;
- START SLAVE;
- FLUSH PRIVILEGES;

(Sowie anschliessend wieder den MariaDB-Dienst neu gestartet.)


ABER: Nun war es ja so, dass ich zuerst es komplett vergessen habe, irgendwelche Datenbanken per `binlog-ignore-db` oder `replicate-ignore-db` zu "blacklisten"... das habe ich dann aber später nachgeholt und mit diese Konfigurationen versucht:

Server 1:

Code:
binlog-ignore-db = information_schema, mysql, performance_schema, phpmyadmin, r_preset, pf_server, r_17, r_21, r_22, r_23, r_636

replicate-ignore-db = information_schema, mysql, performance_schema, apsc, horde, phpmyadmin_Lje7YSVLKyOz, psa, pf_server, r_preset, r_1

ODER

Code:
#binlog-ignore-db = information_schema, mysql, performance_schema, phpmyadmin, r_preset, pf_server, r_17, r_21, r_22, r_23, r_636

replicate-ignore-db = information_schema, mysql, performance_schema, apsc, horde, phpmyadmin_Lje7YSVLKyOz, psa, pf_server, r_preset, r_1

ODER

Code:
binlog-ignore-db = information_schema, mysql, performance_schema, phpmyadmin, r_preset, pf_server, r_17, r_21, r_22, r_23, r_636

#replicate-ignore-db = information_schema, mysql, performance_schema, apsc, horde, phpmyadmin_Lje7YSVLKyOz, psa, pf_server, r_preset, r_1




Server 2:

Code:
binlog-ignore-db = information_schema, mysql, performance_schema, apsc, horde, phpmyadmin_Lje7YSVLKyOz, psa, pf_server, r_preset, r_1

replicate-ignore-db = information_schema, mysql, performance_schema, phpmyadmin, r_preset, pf_server, r_17, r_21, r_22, r_23, r_636

ODER

Code:
#binlog-ignore-db = information_schema, mysql, performance_schema, apsc, horde, phpmyadmin_Lje7YSVLKyOz, psa, pf_server, r_preset, r_1

replicate-ignore-db = information_schema, mysql, performance_schema, phpmyadmin, r_preset, pf_server, r_17, r_21, r_22, r_23, r_636

ODER

Code:
binlog-ignore-db = information_schema, mysql, performance_schema, apsc, horde, phpmyadmin_Lje7YSVLKyOz, psa, pf_server, r_preset, r_1

#replicate-ignore-db = information_schema, mysql, performance_schema, phpmyadmin, r_preset, pf_server, r_17, r_21, r_22, r_23, r_636


-> ALSO JEWEILS
- Master-Daten nicht aufzeichen, somit gibt's nix anzufordern vom Slave
- Master-Daten aufzeichen, aber beim Slave auf die Ignorierliste setzen
- Beides auf einmal, beim Master nix aufzeichen, beim Slave nichts holen. (Irgendwie überflüssig, aber man weiss ja nie)

UND: Wie man sieht, habe ich (zwecks Fehlerdiagnose) ALLE Datenbanken auf die Ignorierliste gesetzt, damit ich es dann mit einer komplett neuen Testen kann.


Bevor ich den Master-Master-Cluster mit einer jeweils neuen Konfig aufgebaut habe, habe ich nach `STOP SLAVE` noch ein `RESET SLAVE ALL` eingegeben. Nur `RESET SLAVE` hatte zur folge, dass `SHOW SLAVE STATUS` nicht '0 rows', sondern immer noch was zurückgegeben hat. (WTF??)

Den Master(-Index) habe ich über `RESET MASTER` zurückgesetzt, danach war die Index-Daten wieder auf 'mysql-000001.bin' und die log Position wieder auf '313'...

Das Ganze hab ich übrigens von hier:
Configure Master-Master MySQL Database Replication


Das Problem am an der ganzen Geschichte ist, dass, obwohl ich auf beiden Seiten beides (aufzeichen (`binlog-ignore-db`) sowie empfangen (`replicate-ignore-db`)) für ALLE Datenbanken ausgeschlossen habe, damit ich neue komplett neue, frische, Test-DB montieren kann und die anderen nicht reinfunken!!

Aber trotzdem funktioniert damit REIN GAR NICHTS, ständig sieht man in den Logs Fehler-Einträge von Datenbanken des jeweiligen anderen Servers, welche eigentlich ausgeschlossen sind!!!

Und was hier steht mit `SET GLOBAL SQL_SLAVE_SKIP_COUNTER = x;`, das klappt leider auch nicht wirklich:
Repair Slave MySQL Replication – Table Doesn’t Exist


Einfach nur zum kotzen, das Ganze... tut mir leid, aber anders kann man den ganzen Kram wohl nicht ausdrücken!!!!

Aber evtl. weiss jemand hier eine Lösung...

Meine aktuellen Alternativen wären:

- Komplett neue MariaDB-Instanzen auf Port 3307 aufsetzen
- Vorhandene MariaDB-Instanzen purgen (apt-get purge ...), und dann noch die Leichen unter /etc/mysql und /usr/mysql manuell wegräumen
- Selbst was programmieren, das wäre wohl von Anfang an eh schneller gewesen - so lange wie ich schon an diesem be******enen Problem hocke!!


Bin für jegliche Inputs dankbar!
 
Wieder mal ne Antwort welche typisch ist für den blauen Elefant - den kenne ich doch schon seit Jahren nicht anders!! :)

Leider bin ich nur Angestellter. Oder hast du mir vielleicht einen MariaDB- oder MySQL-freien Job auf Lager? ;-)
 
Spass beiseite - bin Schweizer und sonst mit meinem Job (abgesehen von solchen Murksereien) eigentlich ganz zufrieden... es geht halt nicht immer alles "gerade aus" in der IT-Welt, ist halt (leider) so.
 
Scheinbar lag es daran:


Code:
binlog_ignore_db = information_schema
  binlog_ignore_db = mysql
  binlog_ignore_db = performance_schema
  binlog_ignore_db = phpmyadmin
  binlog_ignore_db = r_preset
  binlog_ignore_db = pf_server
  binlog_ignore_db = r_17
  binlog_ignore_db = r_21
  binlog_ignore_db = r_22
  binlog_ignore_db = r_23
  binlog_ignore_db = r_636

  replicate_ignore-db = information_schema
  replicate_ignore-db = mysql
  replicate_ignore-db = performance_schema
  replicate_ignore-db = apsc
  replicate_ignore-db = horde
  replicate_ignore-db = phpmyadmin_Lje7YSVLKyOz
  replicate_ignore-db = psa
  replicate_ignore-db = pf_server
  replicate_ignore-db = r_preset
  replicate_ignore-db = r_1

vs.

Code:
binlog-ignore-db    = information_schema, mysql, performance_schema, phpmyadmin, r_preset, pf_server, r_17, r_21, r_22, r_23, r_636
replicate-ignore-db = information_schema, mysql, performance_schema, apsc, horde, phpmyadmin_Lje7YSVLKyOz, psa, pf_server, r_preset, r_1


Zwar mein Fehler, ich habe intuitiv gehandelt und gedacht dass man mit Kommas trennen kann - was ja eigentlich auch das einzig logische und richtige wäre (keine Ahnung, was sich die Entwickler dabei überlegt haben!!)

Mit einem Komma müsste man nicht 100x `parameter_name = ...` erwähnen, und es wird wohl auch selten bis nie vorkommen dass jemand Kommas im DB-Namen verwendet und es deswegen einen Konflikt mit Komma-separierten Werten geben könnte! (Falls ein MySQL-/MariaDB-Name mit Kommas überhaupt geht, aber sowas überflüssiges zu testen - na ja, dazu hab ich jetzt definitiv keine Lust mehr!!!)
 
ob der Variablenname mit Unter- oder Bindestrich hinterlegt wird, scheint aber in diesem Fall wohl keine Rolle zu spielen.

Nach meinen Beobachtungen verwendet MySQL den Bindestrich, während der MariaDB-Standard mit Unterstrichen arbeitet. MariaDB scheint aber in dieser Hinsicht kompatibel zu sein zum Vorgänger MySQL...

Nachtrag:

"Falls ein MySQL-/MariaDB-Name mit Kommas überhaupt geht, aber sowas überflüssiges zu testen - na ja, dazu hab ich jetzt definitiv keine Lust mehr!!!"

Ja, das geht, gerade nach der Diskussion mit dem Arbeitskollegen bewiesen...
 
Werbung:
Zurück
Oben