MySQL Preacher

Obtaining ansi SQL from RBR.

Recently I needed to replicate between MySQL and another database technology. You might say, why on earth would you want to do something like that, but believe me there are reasons and definitely not (to go away from MySQL to some other DB technology like Oracle or SQL server). Unsurprisingly there are quite a few different tools to do it from any platform towards MySQL but very few which do it the other way round, just to name a couple: Golden Gate and DSCallards.

Whilst not going into their tools (you can find more information on their websites), HIT from DSCallards needs its software to run on Windows and Golden Gate is an expensive beast which was too much for what I needed, thus I decided to have a look at doing the job myself. Although it might look an overkill to do so, it took me a few hours to find a solution and implement it and a couple more to test it and here is a simple description.

MySQL replication can be SBR (statement based), RBR (row based) or a mixture of both. Now despite the fact that the mixture provides the best performance, it would be the most complicated in order to achieve a home made solution, and SBR in my opinion would have also been a bit of a headache to make sure queries didn’t contain non ansi sql through the use of functions like now(), sysdate() and many others. I therefore decided that RBR would be the option of choice.

Although I wonder how many of you ever read an RBR binlog using `mysqlbinlog mysql-bin.000004`, it would be something like:

#090526 14:09:13 server id 1  end_log_pos 1420  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343353/*!*/;
BEGIN
/*!*/;
# at 1420
# at 1464
#090526 14:09:13 server id 1  end_log_pos 1464  Table_map: `test`.`t2` mapped to number 21
#090526 14:09:13 server id 1  end_log_pos 1498  Write_rows: table id 21 flags: STMT_END_F

BINLOG ‘
+ekbShMBAAAALAAAALgFAAAAABUAAAAAAAAABHRlc3QAAnQyAAID/gL+AQM=
+ekbShcBAAAAIgAAANoFAAAQABUAAAAAAAEAAv/+AQAAAA==
‘/*!*/;
# at 1498
#090526 14:09:13 server id 1  end_log_pos 1567  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343353/*!*/;
COMMIT
/*!*/;
# at 1567
#090526 14:09:38 server id 1  end_log_pos 1635  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343378/*!*/;
BEGIN
/*!*/;
# at 1635
# at 1679
#090526 14:09:38 server id 1  end_log_pos 1679  Table_map: `test`.`t2` mapped to number 21
#090526 14:09:38 server id 1  end_log_pos 1733  Update_rows: table id 21 flags: STMT_END_F

BINLOG ‘
EuobShMBAAAALAAAAI8GAAAAABUAAAAAAAAABHRlc3QAAnQyAAID/gL+AQM=
EuobShgBAAAANgAAAMUGAAAQABUAAAAAAAEAAv///AQAAAABZPwCAAAAAWT+AQAAAP4CAAAA
‘/*!*/;
# at 1733
#090526 14:09:38 server id 1  end_log_pos 1802  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343378/*!*/;
COMMIT

Now that isn’t the most readable text you ever seen right? As Giuseppe said “This is more difficult to read than ancient Etruscan. If you are a DBA, you curse and look for help.” But the replication guys at mysql created a nice -v for us to add to mysqlbinlog thus issuing `mysqlbinlog -v mysql-bin.000004` would result in the following:

#090526 14:09:13 server id 1  end_log_pos 1420  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343353/*!*/;
BEGIN
/*!*/;
# at 1420
# at 1464
#090526 14:09:13 server id 1  end_log_pos 1464  Table_map: `test`.`t2` mapped to number 21
#090526 14:09:13 server id 1  end_log_pos 1498  Write_rows: table id 21 flags: STMT_END_F

BINLOG ‘
+ekbShMBAAAALAAAALgFAAAAABUAAAAAAAAABHRlc3QAAnQyAAID/gL+AQM=
+ekbShcBAAAAIgAAANoFAAAQABUAAAAAAAEAAv/+AQAAAA==
‘/*!*/;
### INSERT INTO test.t2
### SET
###   @1=1
###   @2=NULL
# at 1498
#090526 14:09:13 server id 1  end_log_pos 1567  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343353/*!*/;
COMMIT
/*!*/;
# at 1567
#090526 14:09:38 server id 1  end_log_pos 1635  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343378/*!*/;
BEGIN
/*!*/;
# at 1635
# at 1679
#090526 14:09:38 server id 1  end_log_pos 1679  Table_map: `test`.`t2` mapped to number 21
#090526 14:09:38 server id 1  end_log_pos 1733  Update_rows: table id 21 flags: STMT_END_F

BINLOG ‘
EuobShMBAAAALAAAAI8GAAAAABUAAAAAAAAABHRlc3QAAnQyAAID/gL+AQM=
EuobShgBAAAANgAAAMUGAAAQABUAAAAAAAEAAv///AQAAAABZPwCAAAAAWT+AQAAAP4CAAAA
‘/*!*/;
### UPDATE test.t2
### WHERE
###   @1=4
###   @2=’d’
### SET
###   @1=2
###   @2=’d’
### UPDATE test.t2
### WHERE
###   @1=1
###   @2=NULL
### SET
###   @1=2
###   @2=NULL
# at 1733
#090526 14:09:38 server id 1  end_log_pos 1802  Query   thread_id=192   exec_time=0     error_code=0
SET TIMESTAMP=1243343378/*!*/;
COMMIT

The exact same output with some decently readable output. The problem at this point is that the output is not really something any other technology would undestand (not even feeding it to MySQL would work! ….

At this point I needed to do some compromises in order to reduce the complexity of this job, i.e. I will not be creating and altering tables / databases or indexes during runtime (this I can do without as I can do the same things on the slave manually when I need to do anything like that on the master) and the replication won’t be in real time i.e. the slave will be fed the sql periodically through a script. The last compromise wasn’t actually a compromise but a decision based on speed of coding as I’m more proficient in bash than perl and as such I decided to go with bash as a proof of concept that this thing can be done. This would never happen in reality as it would be much slower as compared to coding the same thing in perl or C / C++ (choice is up to you).

Now parsing the binary logs is not rocket science is it? As you can see there are three hashes ‘###’ in front of the readable query so a simple grep is fine. A few subsitutions and text processing and you’ll end up with:

darrencassar@mysqlpreacher $ /home/dcassar/sandbox/5.1.30/bin/mysqlbinlog  | sed ’s/^ *//g’ | tr ‘\015\012′ ‘\020 ‘ | sed ’s/ INSERT/;\nINSERT/g’ | sed ’s/ DELETE/;\nDELETE/g’ | sed ’s/ UPDATE/;\nUPDATE/g’ | sed ‘${/^$/!s/$/;\
> /;}’
INSERT INTO test.t1 SET @1=1 @2=’a';
DELETE FROM test.t1 WHERE @1=1 @2=’a';
INSERT INTO test.t2 SET @1=2 @2=’d';
UPDATE test.t2 WHERE @1=2 @2=’d’ SET @1=4 @2=’d';
INSERT INTO test.t2 SET @1=1 @2=NULL;
UPDATE test.t2 WHERE @1=4 @2=’d’ SET @1=2 @2=’d';
UPDATE test.t2 WHERE @1=1 @2=NULL SET @1=2 @2=NULL ;
darrencassar@mysqlpreacher $

That is more readable but still not correctly formatted for our ANSI SQL slave.

`INSERT INTO test.t1 SET @1=1 @2=’a';` would need to be replaced with `INSERT INTO test.t1 (cola, colb) values(1,’a');` or `INSERT INTO test.t1 values(1,’a');`,
`DELETE FROM test.t1 WHERE @1=1 @2=’a';` would need to be replaced with `DELETE FROM test.t1 WHERE cola=1 AND colb=’a';` and
`UPDATE test.t2 WHERE @1=2 @2=’d’ SET @1=4 @2=’d';` would have to become `UPDATE test.t2 SET cola=4 , colb=’d’ WHERE cola=2 AND colb=’d';`

The above means we need to replace @1 and @2 with the appropriate column names done using a lookup table in my case by placing a files in data/dbname each bearing names of the different tables and listing each column in order:
I.E. for a database named test and table named table1 having columns cola and colb I used the database folder named test in the mysql data folder and placed a file named table1, the contents of which were:

cola
colb

The following piece of code does the job of replacing those dreadful @1, @2 etc with proper table names:

for (( i=1; i<=`wc -l $dbname/$tbname | cut -d ” ” -f 1`; i++ ))
do
columnname=`awk ‘NR==a’ a=$i $dbname/$tbname`
LINE_TEMP=`echo $LINE_TEMP | sed ’s/@’$i’=/AND ‘$columnname’=/g’ | sed ’s/SET AND/SET/g’ | sed ’s/WHERE AND/WHERE/g’`
done

If you were thinking how I extracted dbname and tbname, here it is:

dbname=`echo $LINE | cut -d ” ” -f$col | cut -d “.” -f 1`
tbname=`echo $LINE | cut -d ” ” -f$col | cut -d “.” -f 2`

where $LINE is each line extracted using the first command parsing mysqlbinary output and the variable col is 3 for delete and 2 for update which reflects the positioning of the database name and table name inside the extracted lines.

A rather ugly way of re-ordering the update command yet functional is:

echo $LINE_TEMP | cut -d “.” -f2 | sed ’s/’$tbname’ //’ | sed ’s/;//’ > templinefile
cat templinefile | sed ’s/ SET /\nSET /g’ > templinefile2
LINE_TEMP=”UPDATE $dbname.$tbname `tail -1 templinefile2  | sed ’s/ is NULL/=NULL/g’ | sed ’s/AND/,/g’` `head -1 templinefile2`;”

The last thing to remember is taking care of `=NULL` and replace it by `is NULL`.

LINE_TEMP=`echo $LINE_TEMP | sed ’s/=NULL/ is NULL/g’`

As I said this was a fast proof of concept rather than a full fledged optimized script doing the job! The total length of code is 37 lines (excluding comments but with correct and nead formatting).

Enjoy
Darren

Advanced, Intermediate, MySQL bash, MySQL, mysqlbinlog, parsing, replication, row based replication, statement based replication

This is a simple mysql circular replication implementation on a single machine (just a proof of concept) which can easily be done on a broader scale. Just be aware of the cons of circular replication which mainly gets down to: once a node freaks out or stops for one reason or the other, it’s a bitch and you need to take care of IMMEDIATELY.

Download Sandbox from https://launchpad.net/mysql-sandbox/+download
Download MySQL from http://dev.mysql.com/downloads

Copy the downloaded software onto the your *nix box onto any folder of your preference called $BASEDIR

run:

cd /$BASEDIR

gunzip mysql_sandbox_X.X.XX.tar.gz
tar -xf mysql_sandbox_X.X.XX.tar

ln -s mysql_sandbox_X.X.XX sandbox

time /$BASEDIR/sandbox/make_replication_sandbox –circular=4 –topology=circular /$BASEDIR/mysql-5.1.30-linux-x86_64-glibc23.tar.gz


user@hostname $ time /$BASEDIR/sandbox/make_replication_sandbox --circular=4 --topology=circular /$BASEDIR/mysql-5.1.30-linux-x86_64-glibc23.tar.gz
installing node 1
installing node 2
installing node 3
installing node 4
# server: 1:
# server: 2:
# server: 3:
# server: 4:
# server: 1:
# server: 2:
# server: 3:
# server: 4:
Circular replication activated
group directory installed on /$BASEDIR/sandboxes/rcsandbox_5_1_30

real 1m47.913s
user 0m6.364s
sys 0m2.445s

cd /$BASEDIR/sandboxes/rcsandbox_5_1_30

user@hostname $ ./n1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node1 [localhost] {msandbox} ((none)) > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.01 sec)

node1 [localhost] {msandbox} ((none)) > show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 17001 |
+---------------+-------+
1 row in set (0.00 sec)

node1 [localhost] {msandbox} ((none)) > exit
Bye
user@hostname $ ./n2
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node2 [localhost] {msandbox} ((none)) > show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 17002 |
+---------------+-------+
1 row in set (0.00 sec)

node2 [localhost] {msandbox} ((none)) > exit
Bye
user@hostname $ ./n3
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node3 [localhost] {msandbox} ((none)) > show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 17003 |
+---------------+-------+
1 row in set (0.00 sec)

node3 [localhost] {msandbox} ((none)) > exit
Bye
user@hostname $ ./n4
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node4 [localhost] {msandbox} ((none)) > show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 17004 |
+---------------+-------+
1 row in set (0.00 sec)

node4 [localhost] {msandbox} ((none)) > exit
Bye

user@hostname $ cd ..
user@hostname $ ./n1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node1 [localhost] {msandbox} ((none)) > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)

node1 [localhost] {msandbox} ((none)) > create database dba;
Query OK, 1 row affected (0.04 sec)

node1 [localhost] {msandbox} ((none)) > USE dba;
Database changed
node1 [localhost] {msandbox} (dba) > CREATE TABLE `instance` (
-> `dbid` tinyint unsigned NOT NULL,
-> `hostname` varchar(255) NOT NULL,
-> `port` smallint unsigned NOT NULL,
-> PRIMARY KEY (`dbid`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

node1 [localhost] {msandbox} (dba) > CREATE TABLE `backups` (
-> `instance` tinyint unsigned NOT NULL,
-> `dbname` varchar(255) NOT NULL,
-> `date_time` datetime NOT NULL,
-> `dumpsize` varchar(16) NOT NULL,
-> `checksum` varchar(20) NOT NULL,
-> FOREIGN KEY (instance) REFERENCES instance(dbid) ON UPDATE CASCADE ON DELETE RESTRICT
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

node1 [localhost] {msandbox} (dba) > CREATE TABLE `table_size` (
-> `instance` tinyint unsigned NOT NULL,
-> `dbname` varchar(255) NOT NULL,
-> `tbname` varchar(255) NOT NULL,
-> `size_of_data` decimal(18,0) NOT NULL,
-> `size_of_index` decimal(18,0) NOT NULL,
-> `total` decimal(18,0) NOT NULL,
-> `date_time` datetime NOT NULL,
-> FOREIGN KEY (instance) REFERENCES instance(dbid) ON UPDATE CASCADE ON DELETE RESTRICT
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

node1 [localhost] {msandbox} (dba) > CREATE TABLE `memory` (
-> `instance` tinyint unsigned NOT NULL,
-> `date_time` datetime NOT NULL,
-> `size` decimal(18,0) NOT NULL,
-> `rss` decimal(18,0) NOT NULL,
-> FOREIGN KEY (instance) REFERENCES instance(dbid) ON UPDATE CASCADE ON DELETE RESTRICT
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

node1 [localhost] {msandbox} (dba) > exit
Bye
user@hostname $ ./n4
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node4 [localhost] {msandbox} ((none)) > use dba;
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
node4 [localhost] {msandbox} (dba) > CREATE TABLE `connectivity` (
-> `instance` tinyint unsigned NOT NULL,
-> `date_time` datetime NOT NULL,
-> `established` smallint unsigned NOT NULL,
-> `listen` smallint unsigned NOT NULL,
-> `close` smallint unsigned NOT NULL,
-> `time_wait` smallint unsigned NOT NULL,
-> FOREIGN KEY (instance) REFERENCES instance(dbid) ON UPDATE CASCADE ON DELETE RESTRICT
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

node4 [localhost] {msandbox} (dba) > show tables;
+---------------+
| Tables_in_dba |
+---------------+
| backups |
| connectivity |
| instance |
| memory |
| table_size |
+---------------+
5 rows in set (0.00 sec)

node4 [localhost] {msandbox} (dba) > select * from memory;
Empty set (0.00 sec)

node4 [localhost] {msandbox} (dba) > show create table memory;
+--------+---------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------+
| memory | CREATE TABLE `memory` (
`instance` tinyint(3) unsigned NOT NULL,
`date_time` datetime NOT NULL,
`size` decimal(18,0) NOT NULL,
`rss` decimal(18,0) NOT NULL,
KEY `instance` (`instance`),
CONSTRAINT `memory_ibfk_1` FOREIGN KEY (`instance`) REFERENCES `instance` (`dbid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------+
1 row in set (0.00 sec)

node4 [localhost] {msandbox} (dba) > insert into instance (dbid,hostname,port) values ('1','machine1','17001');
Query OK, 1 row affected (0.00 sec)

node4 [localhost] {msandbox} (dba) > select * from instance;
+------+----------+-------+
| dbid | hostname | port |
+------+----------+-------+
| 1 | machine1 | 17001 |
+------+----------+-------+
1 row in set (0.00 sec)

node4 [localhost] {msandbox} (dba) > insert into memory (instance,date_time,size,rss) values('1',now(),'42252431','2543234');
Query OK, 1 row affected (0.00 sec)

node4 [localhost] {msandbox} (dba) > select * from memory;
+----------+---------------------+----------+---------+
| instance | date_time | size | rss |
+----------+---------------------+----------+---------+
| 1 | 2009-01-28 16:27:00 | 42252431 | 2543234 |
+----------+---------------------+----------+---------+
1 row in set (0.00 sec)

node4 [localhost] {msandbox} (dba) > exit
Bye
user@hostname $ ./n2
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

node2 [localhost] {msandbox} ((none)) > use dba;
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
node2 [localhost] {msandbox} (dba) > select * from memory;
+----------+---------------------+----------+---------+
| instance | date_time | size | rss |
+----------+---------------------+----------+---------+
| 1 | 2009-01-28 16:27:00 | 42252431 | 2543234 |
+----------+---------------------+----------+---------+
1 row in set (0.00 sec)

node2 [localhost] {msandbox} (dba) > show tables;
+---------------+
| Tables_in_dba |
+---------------+
| backups |
| connectivity |
| instance |
| memory |
| table_size |
+---------------+
5 rows in set (0.00 sec)

node2 [localhost] {msandbox} (dba) > exit
Bye
user@hostname $ ./check_slaves
node # 1
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
node # 2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
node # 3
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
node # 4
Slave_IO_Running: Yes
Slave_SQL_Running: Yes


Databases, Intermediate, Linux, Mac OS, MySQL, Solaris circular replication, replication, sandbox