[MDEV-10375] after set replicate_wild_do_table, binlog file could not update ? Created: 2016-07-14  Updated: 2016-07-19  Resolved: 2016-07-16

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.1.14
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: sysdljr Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 6.7, MariaDB 10.1.14


 Description   

Hi,
Today, we setup new master/slave (both MariaDB 10.1.14), add log_slave_updates=1, log_bin , relay_log parameters in slave's my.cnf file.
in mariadb client , execute below command, when master update, slave replication normal , and log_bin and relay_log file could update normal too.
stop slave; set global replicate_wild_do_table='test01.%,mysql.%' ; start slave;

IF add replicate_wild_do_table='test01.%,mysql.%' in slave's my.cnf file and restart salve, replication normal, relay_log file could update, but log_bin file could not update.

we test four machines, all have the problem .
Please help confirm ,thanks .

 Comments   
Comment by Elena Stepanova [ 2016-07-15 ]

Please attach cnf files from master and slave and an example of a statement that gets replicated but does not show up in the binlog:

  • do FLUSH BINARY LOGS both on master and slave;
  • run the statement in question on master;
  • run SHOW MASTER STATUS on master;
  • wait till it's replicated on the slave;
  • run SHOW ALL SLAVES STATUS\G on the slave;
  • attach last binlogs from master and slave (they should be tiny).

Thanks.

Comment by sysdljr [ 2016-07-16 ]

@Elena Stepanova , thank you quick reply.

I use two test method for reference :
1. add parameter replicate_wild_do_table='test01.%,mysql.%' in slave's my.cnf and restart mariadb server, slave's binlog file could not update.
FLUSH BINARY LOGS both on master and slave
execute statement on master:

MariaDB [test01]> create table test0716(id int primary key ,c1 int);
 
Query OK, 0 rows affected (0.37 sec)
 
 
 
MariaDB [test01]> show master status;
 
+------------------+----------+--------------+------------------+
 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 
+------------------+----------+--------------+------------------+
 
| mysql-bin.000002 |      538 |              |                  |
 
 
 
on slave: SHOW ALL SLAVES STATUS \G
 
               Slave_IO_State: Waiting for master to send event
 
                  Master_Host: 172.28.14.118
 
                  Master_User: rep_user
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin.000002
 
          Read_Master_Log_Pos: 538
 
               Relay_Log_File: relay-bin.000002
 
                Relay_Log_Pos: 713
 
        Relay_Master_Log_File: mysql-bin.000002
 
             Slave_IO_Running: Yes
 
            Slave_SQL_Running: Yes
 
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: test01.%,mysql.%
 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
 
                   Last_Error: 
                 Skip_Counter: 0
 
          Exec_Master_Log_Pos: 538
 
              Relay_Log_Space: 1009
 
              Until_Condition: None
 
               Until_Log_File: 
                Until_Log_Pos: 0
 
           Master_SSL_Allowed: No
 
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
 
Master_SSL_Verify_Server_Cert: No
 
                Last_IO_Errno: 0
 
                Last_IO_Error: 
               Last_SQL_Errno: 0
 
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 118
 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: Slave_Pos
 
                  Gtid_IO_Pos: 0-118-4
 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative

master and slave's binlog in attach file: master_01_mysql-bin.000002, slave_01_mysql-bin.000003

-------------------------------------------------------
2. command line set replicate_wild_do_table on slave, slave's binlog file could update normal
stop slave; set global replicate_Wild_do_table="test01.%,mysql.%"; start slave;

on master:

MariaDB [test01]> create table test201607(id int primary key ,c1 int);      
Query OK, 0 rows affected (0.46 sec)
 
 
 
MariaDB [test01]> show master status;
 
+------------------+----------+--------------+------------------+
 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 
+------------------+----------+--------------+------------------+
 
| mysql-bin.000002 |      700 |              |                  |
 
+------------------+----------+--------------+------------------+

on slave:

SHOW ALL SLAVES STATUS \G			
               Slave_IO_State: Waiting for master to send event
 
                  Master_Host: 172.28.14.118
 
                  Master_User: rep_user
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin.000002
 
          Read_Master_Log_Pos: 700
 
               Relay_Log_File: relay-bin.000002
 
                Relay_Log_Pos: 875
 
        Relay_Master_Log_File: mysql-bin.000002
 
             Slave_IO_Running: Yes
 
            Slave_SQL_Running: Yes
 
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: test01.%,mysql.%
 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
 
                   Last_Error: 
                 Skip_Counter: 0
 
          Exec_Master_Log_Pos: 700
 
              Relay_Log_Space: 1171
 
              Until_Condition: None
 
               Until_Log_File: 
                Until_Log_Pos: 0
 
           Master_SSL_Allowed: No
 
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
 
Master_SSL_Verify_Server_Cert: No
 
                Last_IO_Errno: 0
 
                Last_IO_Error: 
               Last_SQL_Errno: 0
 
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 118
 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: Slave_Pos
 
                  Gtid_IO_Pos: 0-118-5
 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative

master and slave binlog in attach file: master_02_mysql-bin.000002, salve_02_mysql-bin.000003

master and slave's my.cnf in attach file: master_my.cnf ,slave_my.cnf

Comment by Elena Stepanova [ 2016-07-16 ]

sysdljr,

You are misusing the option. Please check documentation:
https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#replicate_wild_do_table

The directive does not accept a comma-delimited list, and needs to be used multiple times to specify multiple wildcard patterns (on both the commandline and in configuration files)

So, in your case it should be 

replicate_wild_do_table='test01.%'
 
replicate_wild_do_table='mysql.%'

Comment by sysdljr [ 2016-07-17 ]

Thank you.
Sorry about that, I have not see complete documentation, only set the parameter reference replicate_do_db format.

Generated at Thu Feb 08 07:41:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.