MySQL Replication New Features

I had lunch and great conversation with Harrison Fisk of MySQL over lunch.

Then I chatted with Jan (lighttpd) again for about half an hour about Ruby, PHP, Rails and of course Apache and Lighttpd. After chatting with Jan I chatted some more with Jeremy Cole of Yahoo!.

Mike gave me a good tip to meet everyone I have been wanting to meet, but unfortunately once again, I didn't check the email until the lunch session was finished.

Right now I am sitting in the MySQL replication new features session.

The new features in MySQL 5.0 include auto-increment variables for bi-directional replication (multi-master).

Starting with MySQL 5, we can have replication of variables such as FOREIGN_KEY_CHECKS, UNIQUE_KEY_CHECKS, SQL_AUTO_IS_NULL and SQL_MODE

Also, character set and time zone replication is now possible.

In addition, replication of stored procedures can now be done.

Auto-increment variables for bi-directional replication (multi-master)
We want two servers to own the information (multi-master replication) for different types of queries on both servers.

Q: Is there a programmatic interface to access the bin log?
A: not really

Q: WHat are the advantages of row based replication (RBR)?
A: We can replicate non-deterministric statements, for example, UDFs, LOAD_FILE(), UUID(), USER(), FOUND_ROWS()
It also makes it possible to replicate between MySQL clusters.

Statement based technology
- it is proven technology
- sometimes (not always) produces smaller log files

There are four new binlog events

1. Table map event: that is mapping of number to table definition so we can find which number matches which table definition.
2. Binwrite event (after image) so "this row shall exist in slave database"
3. Binupdate event (before image, after image) so "this row shall be changed in slave database"
4. Bindelete event (before image) so "this row shal not exist in the slavbe database"

Some optimization tips:
only primary key in BI which will work only if table have PK
Only changed column values in the AI. Works only if table have PK.

Also note that log is idempotent if PK exists and there are only RBR events in log. One more thing to note is that slave can execute both SBR and RBR events.

Tags: mysqluc