Bug: Cluster: Ignoring databases does not work

Hi there

First of all: First of all I forgot to put the corresponding databases on the ignore list, maybe this has the consequence that I have no other choice than to "apt-get purge mariadb" and to clean up the rest by hand. Nevertheless, it would be very interesting to know exactly where the problem lies!

The starting situation is as follows:

DB-Server 1: At our site. It contains the following databases:

• information_schema (MariaDB System DB)
• mysql (MariaDB System DB)
• performance_schema (MariaDB System DB)
• pf_server (Self-created)
• phpmyadmin (from phpmyadmin halt)
• r_17 (Self created)
• r_21 (Self created)
• r_22 (Self created)
• r_23 (Self-created)
• r_636 (Self-created)
• r_preset (Self created)

DB Server 2: In the data center. This contains these databases:

• apsc (from Plesk Obsidian 18.0.x)
• horde (from Plesk Obsidian 18.0x)
• information_schema (MariaDB System DB)
• mysql (Mariadb System DB)
• performance_schema (MariaDB System DB)
• pf_server (Self-created)
• phpmyadmin_xxxxxxxxxxxxxx (phpadmin-DB, which also created Plesk)
• psa (by Plesk Obsidian 18.0.x)
• r_1 (self created)
• r_preset (Self created)

Server data:

DB Server 1: MariaDB 10.1.26, Debian Stretch updated with apt-get upgrade
DB Server 2: MariaDB 10.1.43, Ubuntu LTS 18.x, Plesk Obsidian 18.0x

Contents of the file /etc/mysql/mariadb.conf.d/50-server.cnf from server 1:

[CODE]#

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

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

1. this is only for the mysqld standalone daemon
   [mysqld]

#

1. * 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

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

#

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

#

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

#

1. * Logging and Replication
   #
2. Both location gets rotated by the cronjob.
3. Be aware that this log type is a performance killer.
4. As of 5.1 you can enable the log at runtime!
   #general_log_file = /var/log/mysql/mysql.log
   #general_log = 1
   #
5. Error log - should be very few entries.
   #
   log_error = /var/log/mysql/error.log
   #
6. 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
   #
7. The following can be used as easy to replay backup logs or for replication.
8. note: if you are setting up a replication slave, see README.Debian about
9. 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

#

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

#

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

#

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

#

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

1. this is only for embedded server
   [embedded]

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

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

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[/CODE]

Contents of the file /etc/mysql/mariadb.conf.d/50-server.cnf from server 2:

[CODE]#

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

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

1. this is only for the mysqld standalone daemon
   [mysqld]

#

1. * 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

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

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

   J.M., 2019-12-10

#

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

#

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

#

1. * Logging and Replication
   #
2. Both location gets rotated by the cronjob.
3. Be aware that this log type is a performance killer.
4. As of 5.1 you can enable the log at runtime!
   #general_log_file = /var/log/mysql/mysql.log
   #general_log = 1
   #
5. Error log - should be very few entries.
   #
   log_error = /var/log/mysql/error.log
   #
6. 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
   #
7. The following can be used as easy to replay backup logs or for replication.
8. note: if you are setting up a replication slave, see README.Debian about
9. 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

#

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

#

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

#

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

#

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

1. this is only for embedded server
   [embedded]

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

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

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[/CODE]

After the adjustments I have of course restarted the services!

I created the Master-Master-Cluster as follows (The first time I forgot to set the `binlog-ignore-db`- and `replication-ignore-db`-entries above):

replication user created on server 1:

[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;[/CODE]

replication user created on server 2:

[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;[/CODE]

I do the `UPDATE mysql.user ...` for compatibility reasons, I think I already had problems (older MySQL-/MariaDB versions??) when I didn't do this...

Then I restarted the services again...

Then I logged on to the other server with `mysql -u replication -p -h REMOTE_IP` to test if the remote login works. It has.

Then

• A "normal" local login to server 1 via `mysql -u root -p`.
• Then entered the SQL command "SHOW MASTER STATUS", which gave me the following parameters:
• `master_log_file`
• `master_log_pos`
• The databases placed on the ignore list (later, the first time I forgot this as I said), so not what the slave should not receive, but what the master should not, for replication purposes, log -> so the DBs behind the MariaDB config parameter `binlog-ignore-db`.

• Then I opened a console window of server 2 (with Plesk) and entered `mysql -u root -p` and executed the following SQL statements:

• 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;

...and then restarted the MariaDB service.

The same for server 2:

• A "normal" local login to server 2 via `mysql -u root -p`.
• Then entered the SQL command "SHOW MASTER STATUS", which gave me the following parameters:
• `master_log_file`
• `master_log_pos`
• The databases placed on the ignore list (later, the first time I forgot this as I said), so not what the slave should not receive, but what the master should not, for replication purposes, log -> so the DBs behind the MariaDB config parameter `binlog-ignore-db`.

• Then I focused the console window of Server 1 again and executed the following SQL statements (analogous to the previous procedure):

• 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;

(As well as restart the MariaDB service afterwards.)

BUT: Well, first I completely forgot to blacklist any databases with `binlog-ignore-db` or `replicate-ignore-db`... but I did that later and tried with these configurations:

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
[/CODE]

OR

[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
[/CODE]

OR

[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
[/CODE]

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
[/CODE]

OR

[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
[/CODE]

OR

[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
[/CODE]

-> SO IN EACH CASE

• do not record master data, so there is nothing to request from the slave
• Record master data, but put it on the ignore list for the slave
• Both at once, no record at the master, no fetch at the slave. (Somehow superfluous, but you never know)

AND: As you can see, I put ALL databases on the ignore list (for troubleshooting purposes) so that I can test it with a completely new test.

Before I built the Master-Master-Cluster with a new config, I entered a `RESET SLAVE ALL` after `STOP SLAVE`. Only `RESET SLAVE` had the consequence that `SHOW SLAVE STATUS` did not return '0 rows', but still something. (WTF??)

I reset the master(-index) via `RESET MASTER`, then the index data was back to 'mysql-000001.bin' and the log position back to '313'...

I got the whole thing from here, by the way:
Configure Master-Master MySQL Database Replication

The problem with the whole thing is that even though I have both on both sides (record (`binlog-ignore-db`) and receive (`replicate-ignore-db`)) both for ALL databases excluded for ALL databases, so I can mount new completely new, fresh, test DB and not radio the others in!

But still REIN GAR does NOT work, you can always see in the logs error entries of databases of the other server, which are actually excluded!!!

And what is written here with `SET GLOBAL SQL_SLAVE_SKIP_COUNTER = x;`, that doesn't really work either:
Repair Slave MySQL Replication - Table Doesn't Exist

But perhaps someone here knows a solution...

My current alternatives would be:

• Set up completely new MariaDB instances on port 3307
• Purge existing MariaDB instances (apt-get purge ...), and then manually remove the corpses under /etc/mysql and /usr/mysql
• Even programming something, that would have been faster from the beginning anyway!

I am grateful for any input!

regards, jan