[Maria-discuss] Don't replicate procedures in the mysql database
Hi all, I'm using multi-source replication with "white list" options only: foo.replicate_wild_do_table = foo.% foo.replicate_ignore_table = foo.beep foo.replicate_ignore_table = foo.tmp The masters are writing row-based binary logs as is (no filtering at masters). This works almost as expected: only the `foo` database gets updates, the mentioned tables are ignored. The `mysql` database does not get updates, even when I run GRANT / REVOKE on the masters quite often. But when I create a procedure on the master it get propagated to the slave: When I execute `CREATE PROCEDURE mysql.bar` on the master, this procedure appears on the slave. How could I avoid that? Did I miss some options? Is this a bug?
On 28/08/16 21:06, Игорь Пашев wrote:
Hi all,
I'm using multi-source replication with "white list" options only:
foo.replicate_wild_do_table = foo.% foo.replicate_ignore_table = foo.beep foo.replicate_ignore_table = foo.tmp
The masters are writing row-based binary logs as is (no filtering at masters).
This works almost as expected: only the `foo` database gets updates, the mentioned tables are ignored. The `mysql` database does not get updates, even when I run GRANT / REVOKE on the masters quite often.
But when I create a procedure on the master it get propagated to the slave:
A question for you is why is it important not to have it on the slave? If the slave is a failover it should have the rpocedure.
When I execute `CREATE PROCEDURE mysql.bar` on the master, this procedure appears on the slave.
Which is what replication is meant to do. Its probably also best not to populate the mysql database with your stored procedures.
Is this a bug?
No.
How could I avoid that?
set session sql_log_bin=0; create procedure...
2016-08-29 1:25 GMT+03:00 Daniel Black <daniel.black@au1.ibm.com>:
A question for you is why is it important not to have it on the slave?
Because the slave already has this procedure with a slightly different implementation, and different owner (definer).
If the slave is a failover it should have the procedure.
If I wanted a failover server I would not be fiddling with replication options and channels at all.
When I execute `CREATE PROCEDURE mysql.bar` on the master, this procedure appears on the slave.
Which is what replication is meant to do.
This is not consistent with creating and modifying mysql users. That's why I'm asking.
Its probably also best not to populate the mysql database with your stored procedures.
It's totally fine for maintenance procedures. See for example http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.SQLRef....
set session sql_log_bin=0; create procedure...
This is not scalable and sometime impossible: for the SUPER privilege, at Amazon RDS, it could break other slaves.
A question for you is why is it important not to have it on the slave?
Because the slave already has this procedure with a slightly different implementation, and different owner (definer).
As a workaround to not stop/break the replication you could run the slave with slave-skip-errors = 1304 ( https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-v... ) I'm just not sure if it is the right one for duplicate procedure. Should be according to https://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html Error: 1304 SQLSTATE: 42000 (ER_SP_ALREADY_EXISTS) Message: %s %s already exists rr
Per the MySQL documentation, it is noted that replicate-*-table options do only apply to tables, and replicate-*-db options are suggested to filter stored procedures, functions and events. I imagine that applies similarly to MariaDB. The various *-db filters can be problematic[1], but perhaps will work well enough for your case. If I understand your problem, I think maybe replicate-ignore-db=mysql might work. I believe if you want to apply that filter to only selective connections w/ Multi-source replication, you have to specify it in the my.cnf[2] and restart the database. [1] https://www.percona.com/blog/2009/05/14/why-mysqls-binlog-do-db-option-is-da... [2] https://mariadb.com/kb/en/mariadb/multi-source-replication/#fixedreplicate-f... On Mon, Aug 29, 2016 at 8:44 AM, Игорь Пашев <pashev.igor@gmail.com> wrote:
2016-08-29 1:25 GMT+03:00 Daniel Black <daniel.black@au1.ibm.com>:
A question for you is why is it important not to have it on the slave?
Because the slave already has this procedure with a slightly different implementation, and different owner (definer).
If the slave is a failover it should have the procedure.
If I wanted a failover server I would not be fiddling with replication options and channels at all.
When I execute `CREATE PROCEDURE mysql.bar` on the master, this procedure appears on the slave.
Which is what replication is meant to do.
This is not consistent with creating and modifying mysql users. That's why I'm asking.
Its probably also best not to populate the mysql database with your stored procedures.
It's totally fine for maintenance procedures. See for example http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.SQLRef....
set session sql_log_bin=0; create procedure...
This is not scalable and sometime impossible: for the SUPER privilege, at Amazon RDS, it could break other slaves.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Thanks to everyone Here is the solution: On the slave: Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: foo.% Replicate_Wild_Ignore_Table: On the master: USE mysql; CREATE PROCEDURE whatever() BEGIN END;
participants (4)
-
Andrew Garner -
Daniel Black -
Reinis Rozitis -
Игорь Пашев