If you aren’t familiar with MySQL replication, “Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous by default – slaves need not to connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.” (From: http://dev.mysql.com/doc/refman/5.5/en/replication.html).

I use MySQL replication on my home office server. I don’t really have much data to store, but it is nice to have several replicated slaves for backup purposes and also for testing new replication features of MySQL. I …

In the majority of MySQL replication scenarios, you want your slave databases to be a mirror of your master databases. You usually don’t want your slave to be behind your master by more than a few seconds – and your main goal is for your slave to always be in sync with your master. Would you ever want your slave to deliberately be a few seconds, minutes or even hours behind your master? There have been several suggestions from MySQL users over the years regarding this functionality as “feature request” (even though most of the requests were submitted as MySQL “bugs”, which was the easiest way to submit such a request).

The first request (that I could find) was by Jason Garrett, back in August of 2006, and was logged as “bug 21639″. Jason wanted MySQL to “provide a parameter/setting which allows an administrator to specify how many seconds a replication …

Overview
MySQL replication does not always scale up well. A common reason for
that is the single-threaded nature of the slave server applier.
Indeed the performance race between the master and the slave
has been unfair. Contrary to the master, which executes transactions
concurrently by multiple threads, the standard MySQL slave is limited
to install changes sequentially by the only thread.
However, if logical partitioning of data per database
takes place, the 5.6 server's Multi-Threaded Slave (MTS) framework may
be found helpful. It allows to install changes done to different
databases in parallel.
For instance the simplest use case would be when the master server has
just two databases and transactions against the master server update
only one of them at a time. Those transactions when replicated will
be executed by two separate slave worker …

If you’re a user of MySQL Workbench then you may have noticed a pocket knife icon appear in the top right hand corner – click on that and a terminal opens which gives you access to the MySQL utilities. In this post I’m focussing on the replication utilities but you can also refer to the full MySQL Utilities documentation.

What I’ll step through is how to uses these utilities to:

• Set up replication from a single master to multiple slaves
• Automatically detect the failure of the master and promote one of the slaves to be the new master
• Introduce the old master back into the topology as a new slave and then promote it to be the master again

Tutorial Video

Before going through the steps in detail here’s a demonstration of the replication utilities in action…

To get full …

The problemSometime back a member of the MySQL support team informed us that the slaves are very sluggish, when he tried to run a delete query on the master. "What may be the problem?" we wondered. This is in RBR and its pretty usual that we have slower slaves when using RBR as compared to SBR. We requested for some more details and found that it is something else. It's a problem when a lot of rows are getting modified in a table without PRIMARY KEY.

"UPDATE t1 set a = a+1;" and other queries such as this are generally found to make slaves sluggish if the table does not have proper index. Is it a problem with the fact that a lot of data is being transferred over the network? After all the total amount of data that will be transferred over the network will be more than twice (before and after image + some header) the sizeof data that is modified. Could this be …

MySQL 5.6 Replication - Global Transaction IDs

On Wednesday (16th May 2012), Mat Keep and I presented on the new replication features that are previewed as part of the latest MySQL 5.6 Development Release.

The replay for that webinar (together with the chart deck) is now available from here.

In addition, there were a huge number of great questions raised and we had a couple of  key engineers answering them on-line – view the Q&A transcript here.

A reminder of the topics covered in the webinar…

MySQL 5.6 delivers new replication capabilities which we will discuss in the …

There are a couple of conferences coming up where you can expect to learn about the latest developments in MySQL Cluster and MySQL Replication (as well as what else is happening in MySQL 5.6).

The first is the Oracle MySQL Innovation Day which is being held in Oracle HQ at Redwood Shores. This is an all-day event on 5th June – unfortunately I won’t be able to attend this one but there will be lots of great Cluster and replication sessions. If you can’t make it out to California then there will be a live Webcast. You can register here to attend in person or join the webcast.

The second is MySQL Connect – this runs the weekend before Oracle OpenWorld in San Francisco; it’s not  until 29th September but it’s worth …

MySQL 5.6 Replication - Global Transaction IDs

On Wednesday (16th May 2012), Mat Keep and I will be presenting the new replication features that are previewed as part of the latest MySQL 5.6 Development Release. If you’d like to attend then register here.

MySQL 5.6 delivers new replication capabilities which we will discuss in the webinar:

• High performance with Multi-Threaded Slaves and Optimized Row Based Replication
• High availability with Global Transaction Identifiers, Failover Utilities and Crash Safe Slaves & Binlog
• Data integrity with Replication Event Checksums
• Dev/Ops agility with new Replication Utilities, Time Delayed Replication and more
…

Read the original article at Bulletproofing MySQL replication with checksums

Your MySQL replications running well? You might not even know if they aren’t. One of the scariest things about MySQL replication is that it can drift out of sync with the master “silently”. No errors, no warnings.

1. What and Why?

MySQL’s replication solution evolved as a statement based technology. Instead of sending actual block changes, MySQL just has to log committed transactions, and reapply those on the slave side. This affords a wonderful array of topologies and different uses, but has it’s drawbacks. The biggest occur when data does not get updated or changed in the …

Read the original article at 5 Ways to fortify MySQL replication

MySQL replication technology is powerful and flexible. But it doesn’t do everything perfectly all the time. You may experience trouble with the slaves falling behind the master, or want to scale horizontally by building new slaves automatically. Or you might need to build a slave without blocking the master database.

All of these goals can be achieved using some powerful tools. Here’s a quick guide to those tools and how to use them.

1. 1. Build new Replicas without Downtime

Something we’re sure you need to do quite often is to build new slaves. You can snapshot from another slave by bringing …