Description:
I am trying to create a simple replication topology with one master and one slave. The master has 9 schema out of which three are being replicated to the slave.
root@master>show databases;
+--------------------+
| Database |
+--------------------+
| avinash |
| holika |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sample |
| sys |
| test |
+--------------------+
9 rows in set (0.00 sec)
All the configuration is default on master, server_id=1 and binlog_format=row. Just a test setup. The slave has following replicate-wild-do-table options and server_id=2, binlog_format=row, rest of the configuration is default.
replicate-wild-do-table=sakila.%
replicate-wild-do-table=test.%
replicate-wild-do-table=sample.%
root@slave>show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.1.11
Source_User: replicant
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000003
Read_Source_Log_Pos: 346
Relay_Log_File: mysql2-relay-bin.000041
Relay_Log_Pos: 555
Relay_Source_Log_File: binlog.000003
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: sakila.%,test.%,sample.%
The slave halts with My-001949 Unknown Database error if I create a stored procedure on master on the schema which is not replicated to the slave.
On master I create a stored procedure
cat avinash_createsp.sql
DELIMITER //
CREATE PROCEDURE avinash.GetAll()
BEGIN
SELECT * FROM avinash.t1;
END //
DELIMITER ;
root@master:[(none)]>use avinash;
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
root@master:[avinash]>source avinash_createsp.sql;
Query OK, 0 rows affected (0.09 sec)
root@master:[avinash]>
On the slave
*************************** 1. row ***************************
Replica_IO_State: Connecting to source
Source_Host: 192.168.1.11
Source_User: replicant
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000003
Read_Source_Log_Pos: 832
Relay_Log_File: mysql2-relay-bin.000041
Relay_Log_Pos: 753
Relay_Source_Log_File: binlog.000003
Replica_IO_Running: Connecting
Replica_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: sakila.%,test.%,sample.%
Replicate_Wild_Ignore_Table:
Last_Errno: 1049
Last_Error: Error 'Unknown database 'avinash'' on query. Default database: 'avinash'. Query: 'CREATE DEFINER=`root`@`localhost` PROCEDURE `avinash`.`GetAll`()
BEGIN
SELECT * FROM avinash.t1;
The error log on slave reports
2022-01-19T05:34:00.583906Z 31 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Error 'Unknown database 'avinash'' on query. Default database: 'avinash'. Query: 'CREATE DEFINER=`root`@`localhost` PROCEDURE `avinash`.`GetAll`() BEGIN SELECT * FROM avinash.t1; END', Error_code: MY-001049
2022-01-19T05:34:00.584021Z 31 [Warning] [MY-010584] [Repl] Slave: Unknown database 'avinash' Error_code: MY-001049
I have gone through the replication chapter and https://dev.mysql.com/doc/refman/8.0/en/replication-rules.html
As per my understanding the statement CREATE PROCEDURE replicates as per Statement Based Replication format. I use USE AVINASH; statement before I execute the CREATE PROCEDURE statement. Thus, the statement should not be replicated on the slave.
I have gone through the following bug. But the bug is for version 5.5. Also the solution there is to use replicate-do-db options for the replicated schema on slave. I can't use that option because it doesn't allow cross database updates which is a requirement. https://bugs.mysql.com/bug.php?id=72682 Also it mentions to ignore information_schema and performance_schema on master binlogs.
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
This link mentions that the information_schema and performance_schema are not replicated. Excluding the standard MySQL databases from replication
How to repeat:
To repeat the bug
1. Create a master server using MySQL Community 8.0.25 version on Linux (Ubuntu, RedHat, Oracle Linux)
2. Use binlog_format=row.
3. Create some databases
4. Create a slave for the master using MySQL Community 8.0.25 version on Linux (Ubuntu, RedHat, Oracle Linux)
5. use binlog_format=row
6. use replicate-wild-do-table=schemaA.% on slave
7. Start the slave
8. On master
use schemaB;
create some procedure;
9. On slave it is unknown database error 1049.
Suggested fix:
I fixed it using skip-slave-error=1049, but I don't like this option but have no choice.
You could also use replicate-do-db option instead of replicate-wild-do-table, but it doesn't allow cross database DDL.
Description: I am trying to create a simple replication topology with one master and one slave. The master has 9 schema out of which three are being replicated to the slave. root@master>show databases; +--------------------+ | Database | +--------------------+ | avinash | | holika | | information_schema | | mysql | | performance_schema | | sakila | | sample | | sys | | test | +--------------------+ 9 rows in set (0.00 sec) All the configuration is default on master, server_id=1 and binlog_format=row. Just a test setup. The slave has following replicate-wild-do-table options and server_id=2, binlog_format=row, rest of the configuration is default. replicate-wild-do-table=sakila.% replicate-wild-do-table=test.% replicate-wild-do-table=sample.% root@slave>show replica status\G *************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: 192.168.1.11 Source_User: replicant Source_Port: 3306 Connect_Retry: 60 Source_Log_File: binlog.000003 Read_Source_Log_Pos: 346 Relay_Log_File: mysql2-relay-bin.000041 Relay_Log_Pos: 555 Relay_Source_Log_File: binlog.000003 Replica_IO_Running: Yes Replica_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: sakila.%,test.%,sample.% The slave halts with My-001949 Unknown Database error if I create a stored procedure on master on the schema which is not replicated to the slave. On master I create a stored procedure cat avinash_createsp.sql DELIMITER // CREATE PROCEDURE avinash.GetAll() BEGIN SELECT * FROM avinash.t1; END // DELIMITER ; root@master:[(none)]>use avinash; 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 root@master:[avinash]>source avinash_createsp.sql; Query OK, 0 rows affected (0.09 sec) root@master:[avinash]> On the slave *************************** 1. row *************************** Replica_IO_State: Connecting to source Source_Host: 192.168.1.11 Source_User: replicant Source_Port: 3306 Connect_Retry: 60 Source_Log_File: binlog.000003 Read_Source_Log_Pos: 832 Relay_Log_File: mysql2-relay-bin.000041 Relay_Log_Pos: 753 Relay_Source_Log_File: binlog.000003 Replica_IO_Running: Connecting Replica_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: sakila.%,test.%,sample.% Replicate_Wild_Ignore_Table: Last_Errno: 1049 Last_Error: Error 'Unknown database 'avinash'' on query. Default database: 'avinash'. Query: 'CREATE DEFINER=`root`@`localhost` PROCEDURE `avinash`.`GetAll`() BEGIN SELECT * FROM avinash.t1; The error log on slave reports 2022-01-19T05:34:00.583906Z 31 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Error 'Unknown database 'avinash'' on query. Default database: 'avinash'. Query: 'CREATE DEFINER=`root`@`localhost` PROCEDURE `avinash`.`GetAll`() BEGIN SELECT * FROM avinash.t1; END', Error_code: MY-001049 2022-01-19T05:34:00.584021Z 31 [Warning] [MY-010584] [Repl] Slave: Unknown database 'avinash' Error_code: MY-001049 I have gone through the replication chapter and https://dev.mysql.com/doc/refman/8.0/en/replication-rules.html As per my understanding the statement CREATE PROCEDURE replicates as per Statement Based Replication format. I use USE AVINASH; statement before I execute the CREATE PROCEDURE statement. Thus, the statement should not be replicated on the slave. I have gone through the following bug. But the bug is for version 5.5. Also the solution there is to use replicate-do-db options for the replicated schema on slave. I can't use that option because it doesn't allow cross database updates which is a requirement. https://bugs.mysql.com/bug.php?id=72682 Also it mentions to ignore information_schema and performance_schema on master binlogs. binlog-ignore-db = information_schema binlog-ignore-db = performance_schema This link mentions that the information_schema and performance_schema are not replicated. Excluding the standard MySQL databases from replication How to repeat: To repeat the bug 1. Create a master server using MySQL Community 8.0.25 version on Linux (Ubuntu, RedHat, Oracle Linux) 2. Use binlog_format=row. 3. Create some databases 4. Create a slave for the master using MySQL Community 8.0.25 version on Linux (Ubuntu, RedHat, Oracle Linux) 5. use binlog_format=row 6. use replicate-wild-do-table=schemaA.% on slave 7. Start the slave 8. On master use schemaB; create some procedure; 9. On slave it is unknown database error 1049. Suggested fix: I fixed it using skip-slave-error=1049, but I don't like this option but have no choice. You could also use replicate-do-db option instead of replicate-wild-do-table, but it doesn't allow cross database DDL.