MySQL Replication

Apr 23, 2007

This afternoon I attended the MySQL Replication: The Complete Tutorial at the MySQL Conference. This was a two part tutorial of which I missed the first tutorial because I was at the MySQL Scaling and High Availability Architectures tutorial in the morning.

The second part jumped right in row-based replication. Row-based replication writes the actual changed row to the binary log whereas regular statement replication records the actual SQL statement to the log.

MySQL’s binary log is designed to handle both statement and row-based replication. MySQL’s replication can also replicate tables, stored procedures, stored functions, triggers, and events. MySQL will automatically add a “definer” to the statement so that privileges are applied correctly on the slave server.

There was some discussion about the technical side of replication and cluster replication. They touched on backup tools in which I learned that there are no free tools for performing a hot-backup of an InnoDB table. You can get around this by replicating the table to a slave server. The slave’s table could then be taken offline for a full backup or the table could be a MyISAM in which the database could be backed up while online. They recommended to a tool for calculating a table checksum to make sure your replicated databases are all synced.

Other interesting links include Heartbeat for Linux, DRBD (distributed replicated block device), and stunnel (secure tunnel).

The future of replication includes better conflict resolution for multi-master scenarios where the same row is being modified by both servers before they have had a chance to sync. The plan is to fire a special trigger when a conflict occurs so you can define what happens.

MySQL can support replication of blob data, but it can be extremely slow if the blob is of a significant size. As I understand it, the blob data is sent over the wire regardless of whether the data was modified or not. It is recommended to create a second table to hold the blob and create a one-to-one relation between the two tables.

All in all, this was an informative tutorial, but I’m left wondering if I would have been better off attending the Wikipedia internals discussion.


No Comments

No comments yet.

RSS feed for comments on this post. TrackBack URL

Sorry, the comment form is closed at this time.