maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #01461
Updated (by Guest): Add a mysqlbinlog option to change the used database (36)
-----------------------------------------------------------------------
WORKLOG TASK
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
TASK...........: Add a mysqlbinlog option to change the used database
CREATION DATE..: Fri, 07 Aug 2009, 14:57
SUPERVISOR.....: Monty
IMPLEMENTOR....:
COPIES TO......:
CATEGORY.......: Server-Sprint
TASK ID........: 36 (http://askmonty.org/worklog/?tid=36)
VERSION........: Server-9.x
STATUS.........: Complete
PRIORITY.......: 60
WORKED HOURS...: 49
ESTIMATE.......: 0 (hours remain)
ORIG. ESTIMATE.: 0
PROGRESS NOTES:
-=-=(Guest - Sat, 07 Nov 2009, 22:43)=-=-
Category updated.
--- /tmp/wklog.36.old.9112 2009-11-07 22:43:50.000000000 +0200
+++ /tmp/wklog.36.new.9112 2009-11-07 22:43:50.000000000 +0200
@@ -1 +1 @@
-Server-RawIdeaBin
+Server-Sprint
-=-=(Guest - Sat, 07 Nov 2009, 22:43)=-=-
Status updated.
--- /tmp/wklog.36.old.9112 2009-11-07 22:43:50.000000000 +0200
+++ /tmp/wklog.36.new.9112 2009-11-07 22:43:50.000000000 +0200
@@ -1 +1 @@
-Un-Assigned
+Complete
-=-=(Bothorsen - Tue, 03 Nov 2009, 13:49)=-=-
More cleanup work done by Alexi, Bo and Sergey.
Worked 4 hours and estimate 0 hours remain (original estimate increased by 4 hours).
-=-=(Bothorsen - Tue, 03 Nov 2009, 13:49)=-=-
Sergey and Bo has been working on getting the patch ready, and Alexi has fixed some issues with the
patch.
Worked 15 hours and estimate 0 hours remain (original estimate increased by 15 hours).
-=-=(Bothorsen - Tue, 03 Nov 2009, 13:47)=-=-
Alexi has implemented a patch for this item.
Worked 30 hours and estimate 0 hours remain (original estimate increased by 30 hours).
-=-=(Guest - Tue, 15 Sep 2009, 18:04)=-=-
Low Level Design modified.
--- /tmp/wklog.36.old.19322 2009-09-15 18:04:49.000000000 +0300
+++ /tmp/wklog.36.new.19322 2009-09-15 18:04:49.000000000 +0300
@@ -191,7 +191,7 @@
- In process_event() function add switch case for Load_log_event and
add print_use_stmt() invocations where needed (according to the
- events lis above), e.g.:
+ events list above), e.g.:
Exit_status process_event(
PRINT_EVENT_INFO *print_event_info,
-=-=(Guest - Tue, 15 Sep 2009, 15:53)=-=-
Low Level Design modified.
--- /tmp/wklog.36.old.13421 2009-09-15 15:53:31.000000000 +0300
+++ /tmp/wklog.36.new.13421 2009-09-15 15:53:31.000000000 +0300
@@ -150,10 +150,17 @@
following events (see process_event() function):
- Query_log_event
-- Execute_load_query_log_event
-- Create_file_log_event
-
-TODO. Needed to check this list requires carefully !!!
+- Load_log_event
+- Execute_load_query_log_event [ :public Query_log_event ]
+- Create_file_log_event [ :public Load_log_event ]
+
+TODO. Needed to check this list carefully (not sure for Create_file_log_event)
+ Notes.
+ - In replication, only Query_log_event and Load_log_event uses
+ rpl_filter->get_rewrite_db();
+ - In mysqlbinlog (process_event), Execute_load_query_log_event
+ and Create_file_log_event are processed in separate switch
+ cases. And Load_log_event is processed in the default switch case.
Conditions for emiting use-statement:
- LOG_EVENT_SUPPRESS_USE_F is OFF for the event
@@ -182,8 +189,9 @@
*/
}
-- In process_event() function add print_use_stmt() invocations where
- needed (according to the events lis above), e.g.:
+- In process_event() function add switch case for Load_log_event and
+ add print_use_stmt() invocations where needed (according to the
+ events lis above), e.g.:
Exit_status process_event(
PRINT_EVENT_INFO *print_event_info,
@@ -207,6 +215,11 @@
}
break;
...
+ case LOAD_EVENT:
+ print_use_stmt((Load_log_event*)ev, print_event_info);
+ break;
+ default:
+ ...
}
...
}
-=-=(Guest - Tue, 15 Sep 2009, 12:12)=-=-
Low Level Design modified.
--- /tmp/wklog.36.old.3961 2009-09-15 12:12:26.000000000 +0300
+++ /tmp/wklog.36.new.3961 2009-09-15 12:12:26.000000000 +0300
@@ -144,6 +144,8 @@
3. Supporting rewrite-db for SBR events
---------------------------------------
+Limited to emiting USE <db_to> instead of USE <db_from>.
+
USE statements can be emited by mysqlbinlog as a result of processing the
following events (see process_event() function):
-=-=(Guest - Tue, 15 Sep 2009, 12:08)=-=-
Low Level Design modified.
--- /tmp/wklog.36.old.3794 2009-09-15 12:08:54.000000000 +0300
+++ /tmp/wklog.36.new.3794 2009-09-15 12:08:54.000000000 +0300
@@ -1 +1,229 @@
+Content
+-------
+1. Adding rewrite-db option
+2. Supporting rewrite-db option for RBR events
+3. Supporting rewrite-db option for SBR events
+ (Limited to affecting only USE statements)
+4. Current status
+
+1. Adding rewrite-db option
+---------------------------
+
+1.1. Syntax:
+ --rewrite-db='db_from->db_to'
+
+1.2. Add 'OPT_REWRITE_DB' to 'options_client' (in client_priv.h).
+
+1.3. In mysqlbinlog.cc:
+
+- Add { "rewrite-db", OPT_REWRITE_DB, ...} record to my_long_options:
+- Add Rpl_filter object to mysqlbinlog.cc
+
+ Rpl_filter* binlog_filter;
+
+- Add corresponding switch case to get_one_option():
+
+ case OPT_REWRITE_DB:
+ <extract db-from and db-to strings>
+ binlog_filter->add_db_rewrite(db_from, db_to);
+ break;
+ .
+Note. To make Rpl_filter usable in a MYSQL_CLIENT context, few small
+additional changes are required:
+
+- In sql_list.cc/h, Sql_alloc::new(size_t) and Sql_alloc::new[](size_t)
+ uses sql_alloc() which is THD dependent. These are to be modified
+ as follows:
+
+ #ifdef MYSQL_CLIENT
+ extern MEM_ROOT sql_list_client_mem_root; // defined in sql_list.cc
+ #endif
+
+ class Sql_alloc
+ { ...
+ static void *operator new(size_t size) throw ()
+ {
+ #ifndef MYSQL_CLIENT
+ return sql_alloc(size);
+ #else
+ return alloc_root(&sql_list_client_mem_root, size);
+ #endif
+ }
+ static void *operator new[](size_t size) throw ()
+ {
+ #ifndef MYSQL_CLIENT
+ return sql_alloc(size);
+ #else
+ return alloc_root(&sql_list_client_mem_root, size);
+ #endif
+ }
+ ...
+ }
+
+- In rpl_filter.cc:
+
+ Rpl_filter::Rpl_filter() :
+ ...
+ {
+ #ifdef MYSQL_CLIENT
+ init_alloc_root(&sql_list_client_mem_root, ...);
+ #endif
+ ...
+ }
+
+ Rpl_filter::~Rpl_filter()
+ { ...
+ #ifdef MYSQL_CLIENT
+ free_root(&sql_list_client_mem_root, ...);
+ #endif
+ }
+
+2. Supporting rewrite-db for RBR events
+---------------------------------------
+
+In binlog, each row operation event is preceded by Table map event(s) which maps
+table id(s) to database and table names. So, it's enough to support rewriting
+database name in a Table map.
+
+2.1. Add rewrite_db() member to Table_map_log_event:
+
+ int Table_map_log_event::rewrite_db(
+ const char* new_db,
+ size_t new_db_len,
+ const Format_description_log_event* desc)
+ {
+ /* 1. In temp_buf member (possibly reallocating it) rewrite
+ event length, db length, and db parts
+ 2. Change m_dblen and m_dbnam members
+ */
+ }
+
+Comment. This function assumes that temp_buf member contains Table map
+binlog representaion (temp_buf is used for creating corresponding
+BINLOG statement).
+
+2.2. In mysqlbinlog modify corresponding switch case in the
+process_event() function:
+
+ Exit_status process_event(
+ PRINT_EVENT_INFO *print_event_info,
+ Log_event *ev, ...)
+ {
+ ...
+ switch (ev_type) {
+ ...
+ case TABLE_MAP_EVENT:
+ {
+ Table_map_log_event *map= ((Table_map_log_event *)ev);
+ if (shall_skip_database(map->get_db_name()))
+ { ...
+ }
+ // WL36
+ size_t new_len= 0;
+ const char* new_db= binlog_filter->get_rewrite_db(
+ map->get_db_name(), &new_len);
+ if (new_len && map->rewrite_db(new_db, new_len,
+ glob_description_event))
+ { error("Could not rewrite database name");
+ goto err;
+ }
+ }
+ case WRITE_ROWS_EVENT:
+ case DELETE_ROWS_EVENT:
+ case UPDATE_ROWS_EVENT:
+ ...
+ }
+ ...
+ }
+
+Comment. Rpl_filter::get_rewrite_db(db_from, &len): if filter contains
+a (db_from, db_to) pair, this function returns pointer to db_to and
+sets len = db_to length; otherwise, it returns db_from and does not
+change len value.
+
+3. Supporting rewrite-db for SBR events
+---------------------------------------
+
+USE statements can be emited by mysqlbinlog as a result of processing the
+following events (see process_event() function):
+
+- Query_log_event
+- Execute_load_query_log_event
+- Create_file_log_event
+
+TODO. Needed to check this list requires carefully !!!
+
+Conditions for emiting use-statement:
+- LOG_EVENT_SUPPRESS_USE_F is OFF for the event
+ (e.g. it is ON for 'create database' statement)
+- event's db name differs from db_name in PRINT_EVENT_INFO
+ (PRINT_EVENT_INFO keeps db name of the last issued USE statement;
+ initially, this db name is empty).
+
+3.1. In mysqlbinlog.cc
+
+- Add the following function:
+
+ void print_use_stmt(Log_event* event, PRINT_EVENT_INFO* pinfo)
+ {
+ if (event->flags & LOG_EVENT_SUPPRESS_USE_F)
+ return;
+ /*
+ - For events listed above get db_from = event->db;
+ - If db_from is the same as pinfo->db then return;
+ - If there is rewrite-db rule db_from->db_to,
+ set db = db_to. Else set db = db_from;
+ - Print "use <db>" to mysqlbinlog output
+ - Set pinfo->db = db_from
+ (this suppresses emiting use-statements by corresponding
+ log_event's print-function)
+ */
+ }
+
+- In process_event() function add print_use_stmt() invocations where
+ needed (according to the events lis above), e.g.:
+
+ Exit_status process_event(
+ PRINT_EVENT_INFO *print_event_info,
+ Log_event *ev, ...)
+ {
+ ...
+ switch (ev_type) {
+ case QUERY_EVENT:
+ if (shall_skip_database(((Query_log_event*)ev)->db))
+ goto end;
+ if (opt_base64_output_mode == BASE64_OUTPUT_ALWAYS)
+ {
+ // Possibly in case of rewite-db rule for ev->db
+ // a warning should be emited here (see note below)
+ ... write_event_header_and_base64(ev, ...) ...
+ }
+ else
+ {
+ print_use_stmt((Query_log_event*)ev, print_event_info);
+ ev->print(result_file, print_event_info);
+ }
+ break;
+ ...
+ }
+ ...
+ }
+
+Note. write_event_header_and_base64() does not print use-statement. It
+produces BINLOG statement using ev->temp_buf content (i.e. the binary
+log representation of the event). We don't rewrite temp_buf here with
+db_to name (as we do it for Table map event) - this implies the
+limitation 3 mentioned above.
+Question: Is supporting of rewite_db + --base64-output really needed
+currently?
+
+4. Current status
+-----------------
+
+The outlined design (implemented for mysql-5.1.37) is tested for
+simple test-cases.
+
+TODO. 1. Check list of events which can emit use-statement.
+ 2. Supporting of rewite_db + --base64-output ?
+
-=-=(Guest - Mon, 14 Sep 2009, 11:51)=-=-
Low Level Design modified.
--- /tmp/wklog.36.old.9711 2009-09-14 11:51:43.000000000 +0300
+++ /tmp/wklog.36.new.9711 2009-09-14 11:51:43.000000000 +0300
@@ -1 +1 @@
-Pay no attention: just check for having access
+
------------------------------------------------------------
-=-=(View All Progress Notes, 18 total)=-=-
http://askmonty.org/worklog/index.pl?tid=36&nolimit=1
DESCRIPTION:
Sometimes there is a need to take a binary log and apply it to a database with
a different name than the original name of the database on binlog producer.
If one is using statement-based replication, he can achieve this by grepping
out "USE dbname" statements out of the output of mysqlbinlog(*). With
row-based replication this is no longer possible, as database name is encoded
within the the BINLOG '....' statement.
This task is about adding an option to mysqlbinlog that would allow to change
the names of used databases in both RBR and SBR events.
(*) this implies that all statements refer to tables in the current database,
doesn't catch updates made inside stored functions and so forth, but still
works for a practially-important subset of cases.
HIGH-LEVEL SPECIFICATION:
Context
-------
(See http://askmonty.org/wiki/index.php/Scratch/ReplicationOptions for global
overview)
At the moment, the server has a replication slave option
--replicate-rewrite-db="from->to"
the option affects
- Table_map_log_event (all RBR events)
- Load_log_event (LOAD DATA)
- Query_log_event (SBR-based updates, with the usual assumption that the
statement refers to tables in current database, so that changing the current
database will make the statement to work on a table in a different database).
See also MySQL BUG#42941. Note this bug is fixed in MySQL 5.1.37, which is not
merged into MariaDB at the time of writing, but planned to be merged before
release.
What we could do
----------------
Option1: make mysqlbinlog accept --replicate-rewrite-db option
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Make mysqlbinlog accept --replicate-rewrite-db options and process them to the
same extent as replication slave would process --replicate-rewrite-db option.
Option2: Add database-agnostic RBR events and --strip-db option
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Right now RBR events require a databasename. It is not possible to have RBR
event stream that won't mention which database the events are for. When I
tried to use debugger and specify empty database name, attempt to apply the
binlog resulted in this error:
090809 17:38:44 [ERROR] Slave SQL: Error 'Table '.tablename' doesn't exist' on
opening tables,
We could do as follows:
- Make the server interpret empty database name in RBR event (i.e. in a
Table_map_log_event) as "use current database". Binlog slave thread
probably should not allow such events as it doesn't have a natural current
database.
- Add a mysqlbinlog --strip-db option that would
= not produce any "USE dbname" statements
= change databasename for all RBR events to be empty
That way, mysqlbinlog output will be database-agnostic and apply to the
current database.
(this will have the usual limitations that we assume that all statements in
the binlog refer to the current database).
Option3: Enhance database rewrite
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If there is a need to support database change for statements that use
dbname.tablename notation and are replicated as statements (i.e. are DDL
statements and/or DML statements that are binlogged as statements),
then that could be supported as follows:
- Make the server's parser recognize special form of comments
/* !database-alias(oldname,newname) */
and save the mapping somewhere
- Put the hooks in table open and name resolution code to use the saved
mapping.
Once we've done the above, it will be easy to perform a complete,
no-compromise or restrictions database name change in binary log.
It will be possible to do the rewrites either on the slave (
--replicate-rewrite-db will work for all kinds of statements), or in
mysqlbinlog (adding a comment is easy and doesn't require mysqlbinlog to
parse the statement).
LOW-LEVEL DESIGN:
Content
-------
1. Adding rewrite-db option
2. Supporting rewrite-db option for RBR events
3. Supporting rewrite-db option for SBR events
(Limited to affecting only USE statements)
4. Current status
1. Adding rewrite-db option
---------------------------
1.1. Syntax:
--rewrite-db='db_from->db_to'
1.2. Add 'OPT_REWRITE_DB' to 'options_client' (in client_priv.h).
1.3. In mysqlbinlog.cc:
- Add { "rewrite-db", OPT_REWRITE_DB, ...} record to my_long_options:
- Add Rpl_filter object to mysqlbinlog.cc
Rpl_filter* binlog_filter;
- Add corresponding switch case to get_one_option():
case OPT_REWRITE_DB:
<extract db-from and db-to strings>
binlog_filter->add_db_rewrite(db_from, db_to);
break;
.
Note. To make Rpl_filter usable in a MYSQL_CLIENT context, few small
additional changes are required:
- In sql_list.cc/h, Sql_alloc::new(size_t) and Sql_alloc::new[](size_t)
uses sql_alloc() which is THD dependent. These are to be modified
as follows:
#ifdef MYSQL_CLIENT
extern MEM_ROOT sql_list_client_mem_root; // defined in sql_list.cc
#endif
class Sql_alloc
{ ...
static void *operator new(size_t size) throw ()
{
#ifndef MYSQL_CLIENT
return sql_alloc(size);
#else
return alloc_root(&sql_list_client_mem_root, size);
#endif
}
static void *operator new[](size_t size) throw ()
{
#ifndef MYSQL_CLIENT
return sql_alloc(size);
#else
return alloc_root(&sql_list_client_mem_root, size);
#endif
}
...
}
- In rpl_filter.cc:
Rpl_filter::Rpl_filter() :
...
{
#ifdef MYSQL_CLIENT
init_alloc_root(&sql_list_client_mem_root, ...);
#endif
...
}
Rpl_filter::~Rpl_filter()
{ ...
#ifdef MYSQL_CLIENT
free_root(&sql_list_client_mem_root, ...);
#endif
}
2. Supporting rewrite-db for RBR events
---------------------------------------
In binlog, each row operation event is preceded by Table map event(s) which maps
table id(s) to database and table names. So, it's enough to support rewriting
database name in a Table map.
2.1. Add rewrite_db() member to Table_map_log_event:
int Table_map_log_event::rewrite_db(
const char* new_db,
size_t new_db_len,
const Format_description_log_event* desc)
{
/* 1. In temp_buf member (possibly reallocating it) rewrite
event length, db length, and db parts
2. Change m_dblen and m_dbnam members
*/
}
Comment. This function assumes that temp_buf member contains Table map
binlog representaion (temp_buf is used for creating corresponding
BINLOG statement).
2.2. In mysqlbinlog modify corresponding switch case in the
process_event() function:
Exit_status process_event(
PRINT_EVENT_INFO *print_event_info,
Log_event *ev, ...)
{
...
switch (ev_type) {
...
case TABLE_MAP_EVENT:
{
Table_map_log_event *map= ((Table_map_log_event *)ev);
if (shall_skip_database(map->get_db_name()))
{ ...
}
// WL36
size_t new_len= 0;
const char* new_db= binlog_filter->get_rewrite_db(
map->get_db_name(), &new_len);
if (new_len && map->rewrite_db(new_db, new_len,
glob_description_event))
{ error("Could not rewrite database name");
goto err;
}
}
case WRITE_ROWS_EVENT:
case DELETE_ROWS_EVENT:
case UPDATE_ROWS_EVENT:
...
}
...
}
Comment. Rpl_filter::get_rewrite_db(db_from, &len): if filter contains
a (db_from, db_to) pair, this function returns pointer to db_to and
sets len = db_to length; otherwise, it returns db_from and does not
change len value.
3. Supporting rewrite-db for SBR events
---------------------------------------
Limited to emiting USE <db_to> instead of USE <db_from>.
USE statements can be emited by mysqlbinlog as a result of processing the
following events (see process_event() function):
- Query_log_event
- Load_log_event
- Execute_load_query_log_event [ :public Query_log_event ]
- Create_file_log_event [ :public Load_log_event ]
TODO. Needed to check this list carefully (not sure for Create_file_log_event)
Notes.
- In replication, only Query_log_event and Load_log_event uses
rpl_filter->get_rewrite_db();
- In mysqlbinlog (process_event), Execute_load_query_log_event
and Create_file_log_event are processed in separate switch
cases. And Load_log_event is processed in the default switch case.
Conditions for emiting use-statement:
- LOG_EVENT_SUPPRESS_USE_F is OFF for the event
(e.g. it is ON for 'create database' statement)
- event's db name differs from db_name in PRINT_EVENT_INFO
(PRINT_EVENT_INFO keeps db name of the last issued USE statement;
initially, this db name is empty).
3.1. In mysqlbinlog.cc
- Add the following function:
void print_use_stmt(Log_event* event, PRINT_EVENT_INFO* pinfo)
{
if (event->flags & LOG_EVENT_SUPPRESS_USE_F)
return;
/*
- For events listed above get db_from = event->db;
- If db_from is the same as pinfo->db then return;
- If there is rewrite-db rule db_from->db_to,
set db = db_to. Else set db = db_from;
- Print "use <db>" to mysqlbinlog output
- Set pinfo->db = db_from
(this suppresses emiting use-statements by corresponding
log_event's print-function)
*/
}
- In process_event() function add switch case for Load_log_event and
add print_use_stmt() invocations where needed (according to the
events list above), e.g.:
Exit_status process_event(
PRINT_EVENT_INFO *print_event_info,
Log_event *ev, ...)
{
...
switch (ev_type) {
case QUERY_EVENT:
if (shall_skip_database(((Query_log_event*)ev)->db))
goto end;
if (opt_base64_output_mode == BASE64_OUTPUT_ALWAYS)
{
// Possibly in case of rewite-db rule for ev->db
// a warning should be emited here (see note below)
... write_event_header_and_base64(ev, ...) ...
}
else
{
print_use_stmt((Query_log_event*)ev, print_event_info);
ev->print(result_file, print_event_info);
}
break;
...
case LOAD_EVENT:
print_use_stmt((Load_log_event*)ev, print_event_info);
break;
default:
...
}
...
}
Note. write_event_header_and_base64() does not print use-statement. It
produces BINLOG statement using ev->temp_buf content (i.e. the binary
log representation of the event). We don't rewrite temp_buf here with
db_to name (as we do it for Table map event) - this implies the
limitation 3 mentioned above.
Question: Is supporting of rewite_db + --base64-output really needed
currently?
4. Current status
-----------------
The outlined design (implemented for mysql-5.1.37) is tested for
simple test-cases.
TODO. 1. Check list of events which can emit use-statement.
2. Supporting of rewite_db + --base64-output ?
ESTIMATED WORK TIME
ESTIMATED COMPLETION DATE
-----------------------------------------------------------------------
WorkLog (v3.5.9)
