MySQL 5.7 Selective Replication: Parameters Not Recommended for Production

MySQL Selective Replication

It is strongly advised against using the parameters mentioned in this article for selective replication in MySQL environments. This document serves only as a testing reference and is not recommended for production use.

Test Environment: MySQL 5.7.26, Note: MySQL 5.6 behavior may differ!

Note: All conclusions below refer to the slave server. For example, if a database is excluded from binlog processing, it means that operations on tables within that database will not be executed on the slave.

1 binlog-do-db

1.1 binlog_format = ROW

Master configuration in my.cnf:

binlog-do-db=a;

Testing procedure:

1.1.1 DDL Statements

Master DDL operations:

（1） Create database b, then create database a

On Master:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database a;
Query OK, 1 row affected (0.00 sec)
mysql> create database b;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a                  |
| b                  |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> show master status\G

*************************** 1. row ***************************

             File: binlog.000004
         Position: 462
     Binlog_Do_DB: a
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql>
# mysqlbinlog --base64-output=decode-rows -v -v  binlog.000004  >> ~/db_01.txt
# at 372
#200224 14:18:12 server id 1611343306  end_log_pos 462 CRC32 0x738926e7     Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1582525092/*!*/;
create database a
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

On Slave:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a                  |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.161.134
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 462
               Relay_Log_File: relaylog.000006
                Relay_Log_Pos: 669
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Conclusion: When creating databases on the master with binlog-do-db configured, only databases specified in the parameter are recorded in the binary log, while others are omitted.

（2） Create table a in database a, and table b in database b

On Master:

mysql> use a;
Database changed
mysql> create table a (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table b.b (id int);
Query OK, 0 rows affected (0.01 sec)

mysql>

Binary log on Master:
use `a`/*!*/;
SET TIMESTAMP=1582525459/*!*/;
create table a (id int)
/*!*/;
# at 623
#200224 14:24:29 server id 1611343306  end_log_pos 688 CRC32 0x0ca2b45e     Anonymous_GTID  last_committed=3    sequence_number=4   rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 688
#200224 14:24:29 server id 1611343306  end_log_pos 786 CRC32 0x15eeef53     Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1582525469/*!*/;
create table b.b (id int)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

On Slave:

mysql> desc a.a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.161.134
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 786
               Relay_Log_File: relaylog.000006
                Relay_Log_Pos: 830
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
      Last_Errno: 1049
       Last_Error: Error 'Unknown database 'b'' on query. Default database: 'a'. Query: 'create table b.b (id int)'

Since operations in database a were logged to binlog, but the slave lacks database b, replication fails.

Conclusion: DDL operations performed in database a are logged to binlog.

(3) Perform DDL in database b targeting table in database aCreate database b on slave to resolve previous error, ensuring both databases exist on slave.

mysql> use b;
Database changed
mysql> alter table a.a add column name varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc a.a;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Check the master's binary log; no record appears for this operation.

Conclusion: DDL operations executed in an unconfigured database do not appear in binlog, thus are not replicated.

(4) Perform DDL on table in database b from database a

Master:
mysql> use a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> alter table b.b add column name varchar(10); 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>

Master binlog:
# at 851
#200224 15:31:20 server id 1611343306  end_log_pos 967 CRC32 0x13c14a93     Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1582532628/*!*/;
alter table b.b add column name varchar(10)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
<br></br>
Slave:
mysql> desc b.b;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>

Replication proceeds normally!

Key findings:

(1) Using binlog-do-db specifies which databases to log DDL changes. Any DDL executed in those databases gets recorded.

(2) DDL operations in non-specified databases are not logged to binlog even if they target databases listed in binlog-do-db.

1.1.2 DML Statements

Master DML operations:

(1) Perform DML in database a on table in database a Ensure slave replicates changes from database a.

Master:
mysql> use a;
Database changed
mysql> drop table a;
Query OK, 0 rows affected (0.01 sec)
mysql> create table a (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into a values(1);
Query OK, 1 row affected (0.01 sec)

Slave:
mysql> select * from a.a;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Conclusion: Operations on tables in the configured database are logged.

(2) Perform DML in database a on table in database b

Master:
mysql> desc b.b;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> use a;
Database changed
mysql> insert into b.b values(1,'b');
Query OK, 1 row affected (0.01 sec)

mysql> select * from b.b;
+------+------+
| id   | name |
+------+------+
|    1 | b    |
+------+------+
1 row in set (0.00 sec)
mysql><br></br><br></br>
Slave:
mysql> select * from b.b;
Empty set (0.00 sec)
mysql>

Conclusion: DML operations in a configured database targeting other databases are not logged.

(3) Perform DML in database b on table in database a

Master:
mysql> desc a.a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> use b;
Database changed
mysql> select * from a.a;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into a.a values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a.a;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql><br></br>
Master binlog:
# at 1622
#200224 16:23:48 server id 1611343306  end_log_pos 1696 CRC32 0x99c73a44    Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1582532628/*!*/;
BEGIN
/*!*/;
# at 1696
#200224 16:23:48 server id 1611343306  end_log_pos 1737 CRC32 0xfef48115    Table_map: `a`.`a` mapped to number 113
# at 1737
#200224 16:23:48 server id 1611343306  end_log_pos 1777 CRC32 0x5e85445b    Write_rows: table id 113 flags: STMT_END_F
### INSERT INTO `a`.`a`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
# at 1777
#200224 16:23:48 server id 1611343306  end_log_pos 1808 CRC32 0x602e2c5c    Xid = 78
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
<br></br>
Slave:
mysql> select * from a.a;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

**Conclusion: Even though there's no explicit use of database b in binlog, operations on a configured database are still logged.

2 binlog-ignore-db

Master configuration in my.cnf:

binlog-ignore-db=a;

2.1 binlog_format = ROW

2.1.1 DDL Statements

(1) Create table in database a from database b

Master:
mysql> use b;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table a.a2 (id int);
Query OK, 0 rows affected (0.02 sec)

Slave:
mysql> show tables;
+-------------+
| Tables_in_a |
+-------------+
| a           |
| a2          |
+-------------+

Conclusion: DDL operations outside ignored databases are logged.

(2) Create table in database b from database a

Master:
mysql> use a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table b.b2 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql>
<br></br>
Slave:
mysql> use b;
Database changed
mysql> show tables;
+-------------+
| Tables_in_b |
+-------------+
| b           |
+-------------+
1 row in set (0.00 sec)

Conclusion: DDL operations inside ignored databases are not logged.

2.1.2 DML Statements

(1) Perform DML in database b on table in database a

Master:
mysql> use b;
Database changed
mysql> insert into a.a2 values(10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from a.a2;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)
mysql><br></br>
Slave:
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.161.134
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000005
          Read_Master_Log_Pos: 482
               Relay_Log_File: relaylog.000009
                Relay_Log_Pos: 689
        Relay_Master_Log_File: binlog.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
mysql> select * from a.a2;
Empty set (0.00 sec)

Conclusion: DML operations in non-ignored databases on ignored databases are not logged.

(2) Perform DML in database a on table in database b

Master:

mysql> use a;
Database changed
mysql> select * from b.b;
+------+------+
| id   | name |
-----+------+
|    1 | b    |
+------+------+
1 row in set (0.00 sec)
mysql> insert into b.b values(2,'b1');
Query OK, 1 row affected (0.01 sec)
mysql> select * from b.b;
+------+------+
| id   | name |
+------+------+
|    1 | b    |
|    2 | b1   |
+------+------+
2 rows in set (0.00 sec)

Slave:
mysql> select * from b.b;
+------+------+
| id   | name |
+------+------+
|    2 | b1   |
+------+------+
1 row in set (0.00 sec)

Conclusion: DML operations in ignored databases on non-ignored databases are logged and executed.

3 replicate-do-db

This setting applies to slaves. Configure on slave:

replicate-do-db=a;

3.1 binlog_format = ROW

3.1.1 DDL Statements

(1) Perform DDL in database a on table in database b

Master:
mysql> use a;
Database changed
mysql> create table b.b6 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql>


Slave:
mysql> use b;
Database changed
mysql> show tables;
+-------------+
| Tables_in_b |
+-------------+        
| b6          |
+-------------+
5 rows in set (0.00 sec)

Conclusion: All binlog entries are applied to relay logs, and DDL operations in configured databases are executed.

(2) Perform DDL in database b on table in database a

Master:
mysql> use b;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table a.a6 (id int);   
Query OK, 0 rows affected (0.01 sec)

Slave:
mysql> use a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------+
| Tables_in_a |
+------------+
| a           |
| a1          |
| a2          |
| a4          |
+-------------+

Conclusion: All binlog entries are applied to relay logs, but DDL operations outside configured databases are not executed.

3.1.2 DML Statements

(1) Perform DML in database a on table in database b

Master:
mysql> use a;
Database changed
mysql> insert into b.b6 values(1);
Query OK, 1 row affected (0.01 sec)

Slave:
mysql> select * from b.b6;
Empty set (0.00 sec)

Conclusion: All binlog entries are applied to relay logs, but DML operations outside configured databases are not executed.

(2) Perform DML in database b on table in database a

Master:
mysql> select * from a.a;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
mysql> use b;
Database changed
mysql> insert into a.a values(3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from a.a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
<br></br>
Slave:
mysql>
mysql> select * from a.a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

Conclusion: All binlog entries are applied to relay logs, DML operations in configured databases are executed, and those outside are not.

4 replicate-ignore-db

Configure on slave:

replicate-ignore-db=a;

4.1 binlog_format = ROW

4.1.1 DDL Statements

(1) Perform DDL in database a on table in database b

Master:
mysql> use a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table b.b7 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> alter table b.b add column (age int);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Slave:
mysql> use b;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------+
| Tables_in_b |
+-------------+
| b           |
| b1          |
| b2          |
| b3          |
| b6          |
+-------------+
5 rows in set (0.00 sec)

mysql> desc b;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Conclusion: All binlog entries are aplied to relay logs, but DDL operations in ignored databases are not executed.

(2) Perform DDL in database b on table in database a

Master:
mysql> use b;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table a.a7 (id int);           
Query OK, 0 rows affected (0.02 sec)
mysql> alter table a.a add column (age int);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Slave:
mysql> use a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| age   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show tables;
+-------------+
| Tables_in_a |
+-------------+
| a           |
| a1          |
| a2          |
| a4          |
| a7          |
+-------------+
5 rows in set (0.00 sec)
mysql>

Conclusion: All binlog entries are applied to relay logs, but DDL operations in non-ignored databases are executed.

4.1.2 DML Statements

(1) Perform DML in database a on table in database b

Master:
mysql> use a;
Database changed
mysql> insert into b.b (id) values(21);
Query OK, 1 row affected (0.00 sec)<br></br>

Slave:
mysql> select * from b.b;
+------+------+
| id   | name |
+------+------+
|    1 | b    |
|    2 | b1   |
|   21 | NULL |
+------+------+
3 rows in set (0.00 sec)

Conclusion: All binlog entries are applied to relay logs, DML operations in ignored databases targeting non-ignored tables are executed.

(2) Perform DML in database b on table in database a

Master:
mysql> use b;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into a.a (id) values(22);
Query OK, 1 row affected (0.01 sec)<br></br>

Slave:
mysql> select * from a.a;
+------+------+
| id   | age  |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | NULL |
|    1 | NULL |
|    4 | NULL |
|  100 | NULL |
|  400 | NULL |
|  600 | NULL |
+------+------+
8 rows in set (0.00 sec)

Conclusion: All binlog entries are applied to relay logs, DML operations in non-ignored databases targeting non-ignored tables are executed, while operations in ignored databases are not.

5 Summary

Avoid using these parameters in production environments! Avoid using these parameters in production environments! Avoid using these parameters in production environments!

Tags: MySQL Replication Binlog selective-replication configuration

Posted on Wed, 22 Jul 2026 17:02:08 +0000 by flyersman

Freaks City

MySQL 5.7 Selective Replication: Parameters Not Recommended for Production

MySQL Selective Replication

1 binlog-do-db

1.1 binlog_format = ROW

1.1.1 DDL Statements

1.1.2 DML Statements

2 binlog-ignore-db

2.1 binlog_format = ROW

2.1.1 DDL Statements

2.1.2 DML Statements

3 replicate-do-db

3.1 binlog_format = ROW

3.1.1 DDL Statements

3.1.2 DML Statements

4 replicate-ignore-db

4.1 binlog_format = ROW

4.1.1 DDL Statements

4.1.2 DML Statements

5 Summary

Hot Tags