Description:
Adding `replicate_ignore_db=` with an empty value on the replica's my.cnf file causes CREATE USER/GRANT commands to not replicate to that particular replica.
mysql> show binlog events in 'mysqld-bin.000001';
+-------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------+
| mysqld-bin.000001 | 4 | Format_desc | 173315299 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| mysqld-bin.000001 | 123 | Previous_gtids | 173315299 | 154 | |
| mysqld-bin.000001 | 154 | Gtid | 173315299 | 219 | SET @@SESSION.GTID_NEXT= '0483a4a9-78b9-11eb-b34e-00163e01b794:1' |
| mysqld-bin.000001 | 219 | Query | 173315299 | 328 | DROP VIEW IF EXISTS mysql.nonexisting_23498985 |
| mysqld-bin.000001 | 328 | Gtid | 173315299 | 393 | SET @@SESSION.GTID_NEXT= '0483a4a9-78b9-11eb-b34e-00163e01b794:2' |
| mysqld-bin.000001 | 393 | Query | 173315299 | 502 | DROP VIEW IF EXISTS mysql.nonexisting_23498985 |
| mysqld-bin.000001 | 502 | Gtid | 173315299 | 567 | SET @@SESSION.GTID_NEXT= '0483a4a9-78b9-11eb-b34e-00163e01b794:3' |
| mysqld-bin.000001 | 567 | Query | 173315299 | 755 | CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| mysqld-bin.000001 | 755 | Gtid | 173315299 | 820 | SET @@SESSION.GTID_NEXT= '0483a4a9-78b9-11eb-b34e-00163e01b794:4' |
| mysqld-bin.000001 | 820 | Query | 173315299 | 958 | GRANT SELECT ON `test`.* TO 'test'@'localhost' |
| mysqld-bin.000001 | 958 | Gtid | 173315299 | 1023 | SET @@SESSION.GTID_NEXT= '0483a4a9-78b9-11eb-b34e-00163e01b794:5' |
| mysqld-bin.000001 | 1023 | Query | 173315299 | 1117 | CREATE DATABASE test |
+-------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------+
[root@jerichorivera-node2 ~]# mysql -uroot -e "show binlog events in 'mysqld-bin.000003'"
+-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysqld-bin.000003 | 4 | Format_desc | 173315196 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| mysqld-bin.000003 | 123 | Previous_gtids | 173315196 | 194 | 0483a4a9-78b9-11eb-b34e-00163e01b794:1-2 |
| mysqld-bin.000003 | 194 | Gtid | 173315299 | 259 | SET @@SESSION.GTID_NEXT= '0483a4a9-78b9-11eb-b34e-00163e01b794:3' |
| mysqld-bin.000003 | 259 | Query | 173315299 | 327 | BEGIN |
| mysqld-bin.000003 | 327 | Query | 173315299 | 396 | COMMIT |
| mysqld-bin.000003 | 396 | Gtid | 173315299 | 461 | SET @@SESSION.GTID_NEXT= '0483a4a9-78b9-11eb-b34e-00163e01b794:4' |
| mysqld-bin.000003 | 461 | Query | 173315299 | 529 | BEGIN |
| mysqld-bin.000003 | 529 | Query | 173315299 | 598 | COMMIT |
| mysqld-bin.000003 | 598 | Gtid | 173315299 | 663 | SET @@SESSION.GTID_NEXT= '0483a4a9-78b9-11eb-b34e-00163e01b794:5' |
| mysqld-bin.000003 | 663 | Query | 173315299 | 757 | CREATE DATABASE test |
+-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
replica server's my.cnf:
++++++++++++++++++++++++++++++++++
[root@jerichorivera-node2 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld]
innodb_buffer_pool_size=512M
server_id=173315196
report_host=10.84.148.124
log-bin=mysqld-bin
enforce_gtid_consistency=ON
gtid_mode=ON
# required for 5.6
log-slave-updates
replicate_ignore_db=
++++++++++++++++++++++++++++++++++
[root@jerichorivera-node2 ~]# mysql -uroot -e "select @@global.binlog_format"
+------------------------+
| @@global.binlog_format |
+------------------------+
| ROW |
+------------------------+
How to repeat:
Setup source-replica replication, two replicas to compare one where replication works.
Add a line under [mysqld] in one of the replica's my.cnf:
[mysqld]
replicate_ignore_db=
On the master execute DCL like:
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'user';
GRANT SELECT ON test.* TO 'user1'@'localhost';
Then execute a DDL or DML to see that it gets replicated:
CREATE DATABASE test;
Suggested fix:
DCL statements should continue replicating.
Add a warning on mysql startup on empty variables that require a value.
