1

Running mysqlbinlog to load up binary logs from one server to another.

Consistently get message:

WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case.

Yah -- OK? So??

Well maybe this is a stupid question, but how am I supposed to distinguish the "GTID" of the database I want from the "GTID" of the database I don't want? In other words, how do I specify transactions to a particular database while shutting off this annoying warning?

Tried adding "--include-gtids" parameter, but I think it wants a list of GTIDs. I don't have a list of GTIDs. I have a database. Why is that complicated?

UncaAlby
  • 5,146
  • 1
  • 16
  • 19

1 Answers1

2

It's complicated because --database doesn't mean what you probably think it means.

It does NOT mean only include changes to the named database.

It means:

This option causes mysqlbinlog to output entries from the binary log (local log only) that occur while db_name is been selected as the default database by USE.

For example:

USE db1;
INSERT INTO db2.mytable ...

This will NOT be included if you use --database db2, because db2 wasn't the default database when this transaction was written to the binary log.

Another example:

USE db3;
UPDATE db1.mytable JOIN db2.myothertable
SET db1.col1 = ...,
    db2.col2 = ...;

Should the changes to db1.mytable resulting from this be included if you use --database db2?

Trick question: the changes to neither table will be included, because the current default database was db3.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Hi, thanks for your response. So now I know why it's complicated. We're using the CakePHP framework, so as far as I know, internally there are both "USE DB;" and "UPDATE DB.TABLE" statements on a fairly consistent basis. No other database is in use except when testing. And, despite the consistent issuing of this warning, the process _does_ appear to be replicating the specified database. – UncaAlby Feb 27 '20 at 20:43
  • Yes, but MySQL is designed to work with any application, not just yours. :-) – Bill Karwin Feb 27 '20 at 21:06
  • MySQL is certainly a great database, no argument, but I'd still like to know how to eliminate this annoying warning while still limiting updates to a specified database. I currently have the stderr redirected to a file (otherwise it's emailed to me), but that seems like an unnecessary kludge. Further, even tho it's going to work 99.9% of the time, there's still that unlikely scenario where somebody may manually log in and update a table while neglecting the "USE DB;" command first. I need to capture those updates also, which, as you described the process, they won't. – UncaAlby Feb 27 '20 at 21:20
  • I don't know of any solution to do what you describe. There's no option in `mysqlbinlog` to filter changes made to tables in a specific database, only for changes made while a specific _default_ database was current. – Bill Karwin Feb 27 '20 at 22:46