[Maria-developers] WL40: Notes/questions
With WL36 and WL40 we have: mysqlbinlog options: --database=db --rewrite-db=db_from->db_to --do-table=db.tbl --ignore-table=db.tbl --wild-do-table=pattern.pattern --wild-ignore-table=pattern.pattern replication options: --replicate-rewrite-db=db_from->db_to --replicate-do-db=db --replicate-ignore-db=db --replicate-do-table=tbl --replicate-ignore-table=tbl --replicate-wild-do-table=db.tbl --replicate-wild-ignore-table=db.tbl 1. In mysqlbinlog we do not have --do_db and --ignore_db options. Does it mean that instead it is supposed to use: --replicate-wild-do-table=db.% --replicate-wild-ignore-table=db.% respectively? Compared with other options, --database option looks like a "foreign body" : - contrary to other options, it allows to specify only one database (with multiple --database's only the last one is used); - having with --database an analog of do_db, we have no similar analog of ignore_db. 2. In replication two functions are used for filtering databases: - db_ok(const char* db) which matches db only with do-db and ignore-db rules; - db_ok_with_wild_table(const char* db) which matches db only with wild-do-table=db.% and wild-ignore-table=db.% rules. This function is applied only to CREATE DB, DROP DB, and ALTER DATABASE statements. In mysqlbinlog, should we follow the same scheme, namely: - db_ok() for matching db with --database option only; - db_ok_with_wild_table() for statements listed above? 3. According to replication filtering rules, --replicate-rewrite_db is always done _before_ other --replicate-* rules are tested; see explanation for --replicate-rewrite-db in RefMan (16.1.3.3. Replication Slave Options and Variables), or the following piece of code in og_event.cc: int Table_map_log_event::do_apply_event(Relay_log_info const* rli) { RPL_TABLE_LIST* table_list; ... strmov(table_list->db, rpl_filter->get_rewrite_db(m_dbnam, &dummy_len)); ... if (...!rpl_filter->db_ok(table_list->db) ...) ... } And what about --database + rewrite-db for mysqlbinlog? If we mean to output only database xxx with renaming it to yyy, should we use (1) mysqlbinlog --database=xxx --rewrite-db=xxx->yyy or (2) mysqlbinlog --database=yyy --rewrite-db=xxx->yyy In current WL36 design it is supposed that (1) should be used (surely, this can easily be redesigned). But this becomes confused with using of --wild-do-table + replication filtering rules, for which we should use: (3) --wild-do-table=yyy.% --rewrite-db=xxx->yyy
Alexi1952 <Alexi1952@yandex.ru> writes: Hi Alexi! Sorry for the delay in answering, things have been quite busy...
With WL36 and WL40 we have: mysqlbinlog options: --database=db --rewrite-db=db_from->db_to --do-table=db.tbl --ignore-table=db.tbl --wild-do-table=pattern.pattern --wild-ignore-table=pattern.pattern
replication options: --replicate-rewrite-db=db_from->db_to --replicate-do-db=db --replicate-ignore-db=db --replicate-do-table=tbl --replicate-ignore-table=tbl --replicate-wild-do-table=db.tbl --replicate-wild-ignore-table=db.tbl
1. In mysqlbinlog we do not have --do_db and --ignore_db options. Does it mean that instead it is supposed to use:
--replicate-wild-do-table=db.% --replicate-wild-ignore-table=db.%
respectively?
I think the --database option of mysqlbinlog is supposed to be similar to mysqldump of a particular database, rather than similar to replication.
Compared with other options, --database option looks like a "foreign body" :
- contrary to other options, it allows to specify only one database (with multiple --database's only the last one is used);
- having with --database an analog of do_db, we have no similar analog of ignore_db.
2. In replication two functions are used for filtering databases:
- db_ok(const char* db) which matches db only with do-db and ignore-db rules;
- db_ok_with_wild_table(const char* db) which matches db only with wild-do-table=db.% and wild-ignore-table=db.% rules. This function is applied only to CREATE DB, DROP DB, and ALTER DATABASE statements.
In mysqlbinlog, should we follow the same scheme, namely:
- db_ok() for matching db with --database option only; - db_ok_with_wild_table() for statements listed above?
It is a bit of a complex issue, but your suggestion sounds reasonable.
3. According to replication filtering rules, --replicate-rewrite_db is always done _before_ other --replicate-* rules are tested; see explanation for --replicate-rewrite-db in RefMan (16.1.3.3. Replication Slave Options and Variables), or the following piece of code in og_event.cc:
int Table_map_log_event::do_apply_event(Relay_log_info const* rli) { RPL_TABLE_LIST* table_list; ... strmov(table_list->db, rpl_filter->get_rewrite_db(m_dbnam, &dummy_len)); ... if (...!rpl_filter->db_ok(table_list->db) ...) ... }
And what about --database + rewrite-db for mysqlbinlog? If we mean to output only database xxx with renaming it to yyy, should we use
(1) mysqlbinlog --database=xxx --rewrite-db=xxx->yyy or (2) mysqlbinlog --database=yyy --rewrite-db=xxx->yyy
In current WL36 design it is supposed that (1) should be used (surely, this can easily be redesigned). But this becomes confused with using of --wild-do-table + replication filtering rules, for which we should use:
(3) --wild-do-table=yyy.% --rewrite-db=xxx->yyy
Personally, I think it makes more sense to apply the filter before the rewrite in mysqlbinlog. Even though this is different from how replication works. (2) and (3) appear quite confusing (to the user). I think the difference is that with replication, the --replicate-[wild-]{do,ignore}-table options concern how the binlog is _applied_. So it makes sense (maybe) to rewrite the events first, and then apply them (since the other way around is impossible). But with mysqlbinlog we are not applying events, only filtering. So I think it makes more sense for filtering rules to apply to the name before rewriting. We can't change replication to do filtering before rewriting, which leaves it inconsistent with mysqlbinlog according to this. But instead we could use different names for the options, as I think the -do- implies actually applying the events rather than filtering. So maybe --include-table --exclude-table --wild-include-table --wild-exclude-table and these (and --database) are done before rewrite. And the --wild-* forms should be sufficient for more complex database filtering (so no need for options similar to --replicate-{do,ignore}-db). At least that's my immediate opinion. Good analysis BTW! - Kristian.
Hi Kristian, Alexi The current replication filters are borked. I wouldn't put work into it, but rather leave them (they are used) and add something that works. Current - the binlog options prevent things from getting logged locally, which is bad for point-in-time recovery. - the replicate options work on the slave end, so they still get transmitted which is a) slow and b) potential security issue. What should happen is that a slave has a profile on the master with a certain filter. So, create a set of named filters, and attach a filter to a slave. If filters are enabled (any defined), refuse any slave that does not have a filter profile, to prevent security problems. Filters could be in a mysql.replfilters table and read into a mem structure, makes it easy to manage. Cheers, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget. Follow our blog at http://openquery.com/blog/ OurDelta: enhanced builds for MySQL @ http://ourdelta.org
Arjen Lentz <arjen@openquery.com> writes:
Current - the binlog options prevent things from getting logged locally, which is bad for point-in-time recovery. - the replicate options work on the slave end, so they still get transmitted which is a) slow and b) potential security issue.
Yes. So we filter either when generating the event or when applying the event. Whereas the better place to filter would be when sending to slave. Of course, filtering when generating and/or applying is significantly easier, especially for statement-based replication. Since it is at those points we have a parsed statement available. At send-to-slave time we only have the currently selected database, and parsing each statement before sending to each slave may not be the right solution. Filtering on current database (or on whatever for row-based) might be more feasible, though there would still be the additional overhead of decoding each event before sending to each slave. But it is good to keep in mind that the general problem has wider scope, thanks for your comment! (In this worklog we are looking at mysqlbinlog, not replication, and this discussion helps clarify why we may want different options in mysqlbinlog from in current replication). - Kristian.
Hi Kristian On 02/10/2009, at 5:22 PM, Kristian Nielsen wrote:
Arjen Lentz <arjen@openquery.com> writes:
Current - the binlog options prevent things from getting logged locally, which is bad for point-in-time recovery. - the replicate options work on the slave end, so they still get transmitted which is a) slow and b) potential security issue.
Yes. So we filter either when generating the event or when applying the event. Whereas the better place to filter would be when sending to slave.
Of course, filtering when generating and/or applying is significantly easier, especially for statement-based replication. Since it is at those points we have a parsed statement available. At send-to-slave time we only have the currently selected database, and parsing each statement before sending to each slave may not be the right solution.
Filtering on current database (or on whatever for row-based) might be more feasible, though there would still be the additional overhead of decoding each event before sending to each slave.
Kristian, your comment, while sounding entirely sensible, is beyond hilariously funny. Because currently, the db filtering works on the default db. So if you say ignore-db bar, and your default db is foo, and then you do INSERT INTO bar.t1. it'll happily insert. There's also an intrinsic potential race condition with either method, when dealing with multi-table update and delete: what do you do if one db is allowed and the other is not?
But it is good to keep in mind that the general problem has wider scope, thanks for your comment!
Ye spending time on the existing stuff just seems like a waste to me. I'd rather see something more sensible, even if it's still on basis of current db. That'd already be more valuable.
(In this worklog we are looking at mysqlbinlog, not replication, and this discussion helps clarify why we may want different options in mysqlbinlog from in current replication).
Yea I got that. Still... Cheers, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget. Follow our blog at http://openquery.com/blog/ OurDelta: enhanced builds for MySQL @ http://ourdelta.org
Hi!
"Arjen" == Arjen Lentz <arjen@openquery.com> writes:
<cut>
Filtering on current database (or on whatever for row-based) might be more feasible, though there would still be the additional overhead of decoding each event before sending to each slave.
Arjen> Kristian, your comment, while sounding entirely sensible, is beyond Arjen> hilariously funny. Arjen> Because currently, the db filtering works on the default db. Arjen> So if you say ignore-db bar, and your default db is foo, and then you Arjen> do INSERT INTO bar.t1. it'll happily insert. That was something that was done by design and consensus, after talking with developers and user of MySQL. This solved tricky issues when you had statements with more than one database involved. The solution has it's pitfalls, as you showed, but it was then to be best solution we could come up with. But enough about history... Arjen> There's also an intrinsic potential race condition with either method, Arjen> when dealing with multi-table update and delete: Arjen> what do you do if one db is allowed and the other is not? And INSERT .... SELECT ...
But it is good to keep in mind that the general problem has wider scope, thanks for your comment!
Arjen> Ye spending time on the existing stuff just seems like a waste to me. Arjen> I'd rather see something more sensible, even if it's still on basis of Arjen> current db. That'd already be more valuable. We have to work with the existing stuff, as the intention is to be a drop-in replacement of MySQL. There are a lot of users that are depending on the current model and we can't just 'fix' things without thinking through things... So the right way to go forward is with small steps and add do it with new options that doesn't break old behavior. Regards, Monty
participants (4)
-
Alexi1952 -
Arjen Lentz -
Kristian Nielsen -
Michael Widenius