Replication : Be careful while executing DCL with replicate-ignore-db=mysql

Replication : Be careful while executing DCL with replicate-ignore-db=mysql.

Normally in mysql replication Data Control Language (DCL) statements won’t replicate into slave if we are configuring replication with replicate-ignore-db=mysql. But if we are executing DCL statements from different database schema (other than mysql schema) in master, the event will replicate to slave.

slave configuration

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-test-01
                  Master_User: repl
                  Master_Port: 3306
		--
		--
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
		--
		--
        Seconds_Behind_Master: 0
		--
		--
1 row in set (0.00 sec)

mysql>

eg :

1, Create one user on master from diff database schema.

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
mysql> grant usage on *.* to ram_test@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user where user='ram_test' and host='%';
+----------+------+
| user     | host |
+----------+------+
| ram_test | %    |
+----------+------+
1 row in set (0.00 sec)

mysql>

On slave also user creation DCL event will execute even after  giving replicate-ignore-db=mysql parameter. If you look at the binlog event of user creation you can see ‘use `test`’ statement attached with that event. Since mysql is taking default database schema for binary logging this event will execute in slave without any issue.

mysql> show binlog events in 'binary-log.000817' from  7275 limit 1;
+-------------------+------+------------+-----------+-------------+------------------------------------------------+
| Log_name          | Pos  | Event_type | Server_id | End_log_pos | Info                                           |
+-------------------+------+------------+-----------+-------------+------------------------------------------------+
| binary-log.000817 | 7275 | Query      |  1000	    |        7386 | use `test`; grant usage on *.* to ram_test@'%' |
+-------------------+------+------------+-----------+-------------+------------------------------------------------+
1 row in set (0.00 sec)

mysql>

On slave


mysql>  select user,host from mysql.user where user='ram_test' and host='%';
+----------+------+
| user     | host |
+----------+------+
| ram_test | %    |
+----------+------+
1 row in set (0.00 sec)

mysql> select @@hostname;
+-------------------------+
| @@hostname              |
+-------------------------+
| mysql-test-02		  |
+-------------------------+
1 row in set (0.00 sec)

mysql>

2, There is a chance of replication error with this configuration.
In this example I am dropping one user on master from diff database schema.

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> drop user ram_test1@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@hostname;
+-------------------------+
| @@hostname              |
+-------------------------+
| mysql-test-01		  |
+-------------------------+
1 row in set (0.00 sec)

mysql>

Here you can see replication failed with ‘Operation DROP USER failed’. This is because user ‘ram_test1′@’%’ was not present in slave instance.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-test-01
                  Master_User: repl
                  Master_Port: 3306
			--
			--
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
			--
			--
                   Last_Errno: 1396
                   Last_Error: Error 'Operation DROP USER failed for 'ram_test1'@'%'' on query. Default database: 'test'. Query: 'drop user ram_test1@'%''
			--
			--

mysql>

Make sure you are in mysql schema while executing DCL with replicate-ignore-db=mysql configuration :-)

Leave a Reply