MySQL 5.6 Replication Webinar

Update – the recording of this webinar is now available here.

This Wednesday (27th March) Mat Keep and I will be presenting a free, live webinar on MySQL 5.6 Replication. You need to register here ahead of the webinar – worth doing even if you can’t attend as you’ll then be sent a link to the replay when it’s available. We’ll also have some of the key MySQL replication developers on-line to answer your questions and so it’s also a great chance to get some free consultancy

Details….

Join this session to learn how the new replication features in MySQL 5.6 enable developers and DBAs to build and scale next generation services using the world’s most popular open source database. MySQL 5.6 delivers new replication capabilities which we will discuss and demonstrate in the webinar:

• High performance with Binary Log Group Commit, 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

The session will wrap up with resources to get started with MySQL 5.6.

WHEN:
Wed, Mar 27: 07:00 Pacific time (America)
Wed, Mar 27: 08:00 Mountain time (America)
Wed, Mar 27: 09:00 Central time (America)
Wed, Mar 27: 10:00 Eastern time (America)
Wed, Mar 27: 14:00 UTC
Wed, Mar 27: 14:00 Western European time
Wed, Mar 27: 15:00 Central European time
Wed, Mar 27: 16:00 Eastern European time
Wed, Mar 27: 19:30 India, Sri Lanka
Wed, Mar 27: 22:00 Singapore/Malaysia/Philippines time
Wed, Mar 27: 22:00 China time
Wed, Mar 27: 23:00 日本
Thu, Mar 28: 01:00 NSW, ACT, Victoria, Tasmania (Australia)

The presentation will be approximately 60 minutes long.

MySQL 5.6 GA – Replication Enhancements

Multi-Threaded Slave

• Global Transaction Identifiers (GTIDs) – a unique identifier that is used accross your replication topology to identify a transaction. Makes setting up and managing your cluster (including the promotion of a new master) far simpler and more reliable.
• Multi-threaded slaves (MTS) – Increases the performance of replication on the slave; different threads will handle applying events to different databases.
• Binary Log Group Commit – Improves replication performance on the master.
• Optimized Row Based Replication – reduces the amount of data that needs to be replicated; reducing network usage and potentially speeding up replication.
• Crash-Safe Replication – makes replication transactional. The data and replication positioning information are both updated as part of the same transaction.
• Replication Event Checksums – checks to ensure that the data being replicated hasn’t become corrupted, avoids writing corrupted data to the slave.
• Time-Delayed Replication – configure one or more of your slaves to only apply replicated events after a configured time. This can be useful to protect against DBA mistakes.
• Informational Logs – includes the original statement in the binary log when using row-based-replication to aid in debugging.

To coincide with the GA announcement, a new white paper has been released – MySQL 5.6 Replication An Introduction – that provides a lot more details on these new features. We’ve also released a second white paper – MySQL 5.6 Replication, Enhancing Scalability and Availability – A Tutorial that steps you through using MySQL Replication, with a particular focus on the new features.

If you’re already familiar with using MySQL Replication, here are a few pointers on what to do differently for the new MySQL 5.6 features but you should refer to the full tutorial to see these changes in context.

First of all, here are some extra configuration parameters to include…

[mysqld]
gtid-mode=on				# GTID
enforce-gtid-consistency=true		# GTID
master-info-repository=TABLE            # Chrash-safe replication
relay-log-info-repository=TABLE         # Chrash-safe replication
slave-parallel-workers=2                # MTS
binlog-checksum=CRC32                   # Checksums
master-verify-checksum=1                # Checksums
slave-sql-verify-checksum=1             # Checksums
binlog-rows-query-log_events=1          # Informational logs

When initiating (or restarting) replication on the slave, it is no longer necessary to include the binary log positioning information as the master and slave will automatically negotiate what outstanding events need to be replicated based on any GTIDs previously received by the slave…

slave> CHANGE MASTER TO MASTER_HOST='black', 
                        MASTER_USER='repl_user', 
                        MASTER_PASSWORD='billy', 
                        MASTER_AUTO_POSITION=1;

Please try out these new features and let us know what you think.

Replication and auto-failover made easy with MySQL Utilities

Utilities in MySQL Workbench

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 use of these utilities you should use the InnoDB storage engine together with the Global Transaction ID functionality from the latest MySQL 5.6 DMR.

Do you really need/want auto-failover?

For many people, the instinctive reaction is to deploy a fully automated system that detects when the master database fails and then fails over (promotes a slave to be the new master) without human intervention. For many applications this may be the correct approach.

There are inherent risks to this though – What if the failover implementation has a flaw and fails (after all, we probably don’t test this out in the production system very often)? What if the slave isn’t able to cope with the workload and makes things worse? Is it just a transitory glitch and would the best approach have been just to wait it out?

Following a recent, high profile outage there has been a great deal of debate on the topic between those that recommend auto-failover and those that believe it should only ever be entrusted to a knowledgeable (of the application and the database architecture) and well informed (of the state of the database nodes, application load etc.) human. Of course, if the triggering of the failover is to be left to a human then you want that person to have access to the information they need and an extremely simple procedure (ideally a single command) to execute the failover. Probably the truth is that it all depends on your specific circumstances.

The MySQL replication utilities aim to support you whichever camp you belong to:

• In the fully automated mode, the utilities will continually monitor the state of the master and in the event of its failure identify the best slave to promote – by default it will select the one that is most up-to-date and then apply any changes that are available on other slaves but not on this one before promoting it to be the new master. The user can override this behaviour (for example by limiting which of the slaves are eligible for promotion). The user is also able to bind in their own programs to be run before and after the failover (for example, to inform the application).
• In the monitoring mode, the utility still continually checks the availability of the master, and informs the user if it should fail. The user then executes a single command to fail over to their preferred slave.

Step 1. Make sure MySQL Servers are configured correctly

For some of the utilities, it’s important that you’re using Global Transaction IDs; binary logging needs to be enabled; may as well use the new crash-safe slave functionality… It’s beyond the scope of this post to go through all of those and so instead I’ll just give example configuration files for the 5 MySQL Servers that will be used:

my1.cnf

[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
datadir=/home/billy/mysql/data1
server-id=1
log-bin=util11-bin.log
report-host=utils1
report-port=3306
socket=/home/billy/mysql/sock1
port=3306

my2.cnf

[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
datadir=/home/billy/mysql/data2
server-id=2
log-bin=util12-bin.log
report-host=utils1
report-port=3307
socket=/home/billy/mysql/sock2
port=3307

my3.cnf

[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
datadir=/home/billy/mysql/data3
server-id=3
log-bin=util2-bin.log
report-host=utils2
report-port=3306
socket=/home/billy/mysql/sock3
port=3306

my4.cnf

[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
master-info-repository=TABLE
relay-log-info-repository=TABLE
master-info-file=/home/billy/mysql/master4.info
datadir=/home/billy/mysql/data4
server-id=4
log-bin=util4-bin.log
report-host=utils2
report-port=3307
socket=/home/billy/mysql/sock4
port=3307

my5.cnf

[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
disable-gtid-unsafe-statements=true # Use enforce-gtid-consistency from 5.6.9+
datadir=/home/billy/mysql/data5
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
#master-info-file=/home/billy/mysql/master5.info
server-id=5
log-bin=util5-bin.log
report-host=utils2
report-port=3308
socket=/home/billy/mysql/sock5
port=3308

The utilities are actually going to be run from a remote host and so it will be necessary for that host to access each of the MySQL Servers and so a user has to be granted remote access (note that the utilities will automatically create the replication user):

[billy@utils1 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "grant all on *.* to root@'%' with grant option;"
[billy@utils1 ~]$ mysql -h 127.0.0.1 -P3307 -u root -e "grant all on *.* to root@'%' with grant option;"
[billy@utils2 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "grant all on *.* to root@'%' with grant option;"
[billy@utils2 ~]$ mysql -h 127.0.0.1 -P3307 -u root -e "grant all on *.* to root@'%' with grant option;"
[billy@utils2 ~]$ mysql -h 127.0.0.1 -P3308 -u root -e "grant all on *.* to root@'%' with grant option;"

OK – that’s the most painful part of the whole process out of the way!

Set up replication

While there are extra options (such as specifying what username/password to use for the replication user or providing a password for the root user) I’m going to keep things simple and use the defaults as much as possible. The following commands are run from the MySQL Utilities terminal – just click on the pocket-knife icon in MySQL Workbench.

mysqlreplicate --master=root@utils1:3306 --slave=root@utils1:3307
# master on utils1: ... connected.
# slave on utils1: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

mysqlreplicate --master=root@utils1:3306 --slave=root@utils2:3306
# master on utils1: ... connected.
# slave on utils2: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

mysqlreplicate --master=root@utils1:3306 --slave=root@utils2:3307
# master on utils1: ... connected.
# slave on utils2: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

mysqlreplicate --master=root@utils1:3306 --slave=root@utils2:3308
# master on utils1: ... connected.
# slave on utils2: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

That’s it, replication has now been set up from one master to four slaves.

You can now check that the replication topology matches what you intended:

mysqlrplshow --master=root@utils1;
# master on utils1: ... connected.
# Finding slaves for master: utils1:3306

# Replication Topology Graph
utils1:3306 (MASTER)
   |
   +--- utils1:3307 - (SLAVE)
   |
   +--- utils2:3306 - (SLAVE)
   |
   +--- utils2:3307 - (SLAVE)
   |
   +--- utils2:3308 - (SLAVE)

Additionally, you can also check that any of the replication relationships is correctly configure:

mysqlrplcheck --master=root@utils1 --slave=root@utils2:3307
# master on utils1: ... connected.
# slave on utils2: ... connected.
Test Description                                                     Status
---------------------------------------------------------------------------
Checking for binary logging on master                                [pass]
Are there binlog exceptions?                                         [pass]
Replication user exists?                                             [pass]
Checking server_id values                                            [pass]
Is slave connected to master?                                        [pass]
Check master information file                                        [pass]
Checking InnoDB compatibility                                        [pass]
Checking storage engines compatibility                               [pass]
Checking lower_case_table_names settings                             [pass]
Checking slave delay (seconds behind master)                         [pass]
# ...done.

Including the -s option would have included the output that you’d expect to see from SHOW SLAVE STATUS\G on the slave.

Automated monitoring and failover

The previous section showed how you can save some serious time (and opportunity for user-error) when setting up MySQL replication. We now look at using the utilities to automatically monitor the state of the master and then automatically promote a new master from the pool of slaves. For simplicity I'll stick with default values wherever possible but note that there are a number of extra options available to you such as:

• Constraining which slaves are eligible for promotion to master; the default is to take the most up-to-date slave
• Binding in your own scripts to be run before or after the failover (e.g. inform your application to switch master?)
• Have the utility monitor the state of the servers but don't automatically initiate failover

Here is how to set it up:

mysqlfailover --master=root@utils1:3306 --discover-slaves-login=root --rediscover

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Aug 15 13:19:30 2012

Master Information
------------------
Binary Log File    Position  Binlog_Do_DB  Binlog_Ignore_DB
util11-bin.000001  2586

Replication Health Status
+---------+-------+---------+--------+------------+---------+
| host    | port  | role    | state  | gtid_mode  | health  |
+---------+-------+---------+--------+------------+---------+
| utils1  | 3306  | MASTER  | UP     | ON         | OK      |
| utils1  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3306  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3308  | SLAVE   | UP     | ON         | OK      |
+---------+-------+---------+--------+------------+---------+


Q-quit R-refresh H-health G-GTID Lists U-UUIDs

mysqlfailover will then continue to run, refreshing the state - just waiting for something to go wrong.

Rather than waiting, I kill the master MySQL Server:

mysqladmin -h utils1 -P3306 -u root shutdown

Checking with the still-running mysqlfailover we can see that it has promoted utils1:3307.

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Aug 15 13:21:13 2012

Master Information
------------------
Binary Log File    Position  Binlog_Do_DB  Binlog_Ignore_DB
util12-bin.000001  7131

Replication Health Status
+---------+-------+---------+--------+------------+---------+
| host    | port  | role    | state  | gtid_mode  | health  |
+---------+-------+---------+--------+------------+---------+
| utils1  | 3307  | MASTER  | UP     | ON         | OK      |
| utils2  | 3306  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3308  | SLAVE   | UP     | ON         | OK      |
+---------+-------+---------+--------+------------+---------+


Q-quit R-refresh H-health G-GTID Lists U-UUIDs

Add the recovered MySQL Server back into the topology

After restarting the failed MySQL Server, it can be added back into the mix as a slave to the new master:

mysqlreplicate --master=root@utils1:3307 --slave=root@utils1:3306
# master on utils1: ... connected.
# slave on utils1: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

The output from mysqlfailover (still running) confirms the addition:

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Aug 15 13:24:38 2012

Master Information
------------------
Binary Log File    Position  Binlog_Do_DB  Binlog_Ignore_DB
util12-bin.000001  7131

Replication Health Status
+---------+-------+---------+--------+------------+---------+
| host    | port  | role    | state  | gtid_mode  | health  |
+---------+-------+---------+--------+------------+---------+
| utils1  | 3307  | MASTER  | UP     | ON         | OK      |
| utils1  | 3306  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3306  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3308  | SLAVE   | UP     | ON         | OK      |
+---------+-------+---------+--------+------------+---------+


Q-quit R-refresh H-health G-GTID Lists U-UUIDs

If it were important that the recovered MySQL Server be restored as the master then it is simple to manually trigger the promotion (after quitting out of mysqlfailover):

mysqlrpladmin --master=root@utils1:3307 --new-master=root@utils1:3306 --demote-master 
  --discover-slaves-login=root switchover

# Discovering slaves for master at utils1:3307
# Checking privileges.
# Performing switchover from master at utils1:3307 to slave at utils1:3306.
# Checking candidate slave prerequisites.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+---------+-------+---------+--------+------------+---------+
| host    | port  | role    | state  | gtid_mode  | health  |
+---------+-------+---------+--------+------------+---------+
| utils1  | 3306  | MASTER  | UP     | ON         | OK      |
| utils1  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3306  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3307  | SLAVE   | UP     | ON         | OK      |
| utils2  | 3308  | SLAVE   | UP     | ON         | OK      |
+---------+-------+---------+--------+------------+---------+
# ...done.

As always, we'd really appreciate people trying this out and giving us feedback!

MySQL 5.6 Replication – webinar replay

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 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

Upcoming conferences to learn more about MySQL Cluster & Replication

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 registering now to get the early bird pricing and save $500 (end 13th July). There are lots of great sessions lined up both from the MySQL experts within Oracle and users and community members.

Upcoming webinar: MySQL 5.6 Replication – For Next Generation of Web and Cloud Services

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

The session will wrap up with resources to get started with MySQL 5.6 and an opportunity to ask questions.

The webinar will last 45-60 minutes and will start on Wednesday, May 16, 2012 at 09:00 Pacific time (America); start times in other time zones:

• Wed, May 16: 06:00 Hawaii time
• Wed, May 16: 10:00 Mountain time (America)
• Wed, May 16: 11:00 Central time (America)
• Wed, May 16: 12:00 Eastern time (America)
• Wed, May 16: 16:00 UTC
• Wed, May 16: 17:00 Western European time
• Wed, May 16: 18:00 Central European time
• Wed, May 16: 19:00 Eastern European time

As always, it’s worth registering even if you can’t make the live webcast as you’ll  be emailed a link to the replay as soon as it’s available.

MySQL 5.6 Replication Enhancements – webinar replay

Global Transaction IDs - simplifying replication management

The replay has now been released for the MySQL 5.6 replication enhancements replay where you can get the latest information on all of the great new content that has been included in the MySQL 5.6 Development Releases as well as some features that are still being developed. You can view the replay here.

Some of the topics discussed are:

• Enhanced data integrity: Global Transactions Identifiers, Crash-Safe Slaves and Replication Event Checksums;
• High performance: Multi-Threaded Slaves, Binlog Group Commit and Optimized Row-Based Replication;
• Improved flexibility: Time Delayed Replication, Multi-Source Replication, Binlog API and Informational Log Events

What’s new in MySQL 5.6 Replication – free webinar

Global Transaction IDs - simplifying replication management

There will be a webinar this Wednesday where you can get the latest information on all of the great new content that has been included in the MySQL 5.6 Development Releases as well as some features that are still being developed. As always, the webinar is free but you need to register here in advance. Even if you can’t attend the live event it’s worth registering so that you get sent the replay.

Some of the topics we’ll be discussing are:

• Enhanced data integrity: Global Transactions Identifiers, Crash-Safe Slaves and Replication Event Checksums;
• High performance: Multi-Threaded Slaves, Binlog Group Commit and Optimized Row-Based Replication;
• Improved flexibility: Time Delayed Replication, Multi-Source Replication, Binlog API and Informational Log Events

MySQL HA Solutions – webinar replay

If you were unable to attend the live webinar (or you want to go back and listen to it again) then it’s now available to view on-line here.

Databases are the center of today’s web and enterprise applications, storing and protecting an organization’s most valuable assets and supporting business-critical applications. Just minutes of downtime can result in significant lost revenue and dissatisfied customers. Ensuring database highly availability is therefore a top priority for any organization. Tune into this webcast to learn more.

The session discusses:

1. Causes, effect and impact of downtime
2. Methodologies to map applications to HA solution
3. Overview of MySQL HA solutions
4. Operational best practices to ensure business continuity

Enhanced conflict resolution with MySQL Cluster active-active replication

Detecting conflicts

Part of the latest MySQL Cluster Development Milestone Release (MySQL Cluster 7.2.1 – select the “Development Release” tab at http://dev.mysql.com/downloads/cluster/#downloads) is a couple of enhancements to the conflict detection and resolution mechanism for active-active (multi-master) replication. While MySQL Cluster has had conflict detection for years it has now been made much more complete and a lot easier to use:

• No changes needed to the application schema
• Entire conflicting transaction is rolled back together with any dependent transactions

The focus of this post will be to step through how to use this feature – while it will also attempt to explain how it works at a high level, you should refer to the following posts for the design details and philosophy: Eventual consistency with MySQL & Eventual Consistency – detecting conflicts.

What is a conflict?

MySQL Cluster allows bi-directional replication between two (or more) clusters. Replication within each cluster is synchronous but between clusters it is asynchronous which means the following scenario is possible:

In this example a value (column for a row in a table) is set to 11 on site A and the change is queued for replication to site B. In the mean time, an application sets the value to 20 on site B and that change is queued for replication to site A. Once both sites have received and applied the replicated change from the other cluster site A contains the value 20 while site B contains 11 – in other words the databases are now inconsistent.

How MySQL Cluster 7.2 implements eventual consistency

There are two phases to establishing consistency between both clusters after an inconsistency has been introduced:

1. Detect that a conflict has happened
2. Resolve the inconsistency

Detecting the conflict

The following animation illustrates how MySQL Cluster 7.2 detects that an inconsistency has been introduced by the asynchronous, active-active replication:

Detecting conflicts

While we typically consider the 2 clusters in an active-active replication configuration to be peers, in this case we designate one to be the primary and the other the secondary. Reads and writes can still be sent to either cluster but it is the responsibility of the primary to identify that a conflict has arisen and then remove the inconsistency.

A logical clock is used to identify (in relative terms) when a change is made on the primary – for those who know something of the MySQL Cluster internals, we use the index of the Global Checkpoint that the update is contained in. For all tables that have this feature turned on, an extra, hidden column is automatically added on the primary – this represents the value of the logical clock when the change was made.

Once the change has been applied on the primary, there is a “window of conflict” for the effected row(s) during which if a different change is made to the same row(s) on the secondary then there will be an inconsistency. Once the slave on the secondary has applied the change from the primary, it will send a replication event back to the slave on the primary, containing the primary’s clock value associated with the changes that have just been applied on the secondary. (Remember that the clock is actually the Global Checkpoint Index and so this feature is sometimes referred to as Reflected GCI). Once the slave on the primary has received this event, it knows that all changes tagged with a clock value no later than the reflected GCI are now safe – the window of conflict has closed.

If an application modifies this same row on the secondary before the replication event from the primary was applied then it will send an associated replication event to the slave on the primary before it reflects the new GCI. The slave on the primary will process this replication event and compare the clock value recorded with the effected rows with the latest reflected GCI; as the clock value for the conflicting row is higher the primary recognises that a conflict has occured and will launch the algorithm to resolve the inconsistency.

Resolving the inconsistency

In earlier releases of MySQL Cluster (or if choosing to use the original algorithm in MySQL Cluster 7.2) you had a choice of simply flagging the primary key of the conflicting rows or backing out one of the changes to the conflicting rows. Using the new NDB$EPOCH_TRANS function, the primary will overwrite the data in the secondary for the effected row(s) and any other rows that were updated in the same transaction (even if they are in tables for which conflict detection has not been enabled).

In fact the algorithm goes a step further and if there were subsequent transactions on the secondary that wrote to the conflicting rows then all of the changes from those dependent transactions on the secondary will be backed-out as well.

Worked example

In this section, we step through how to setup the active-active replication, with the new conflict detection/resolution feature enabled and then test it out by manually introducing some conflicting transations.

Set-up MySQL Clusters and basic active-acative replication

Hosts used for replication

To keep things simple, just two hosts are used; “black” will contain all nodes for the primary cluster and “blue” will contain all nodes for the secondary. As an extra simplification a single MySQL Server in each cluster will act as both the master and the slave.

This post will quickly show the configuration files and steps to get the 2 clusters up and running but for a better understanding of these steps you can refer to Deploying MySQL Cluster over multiple hosts.

config.ini (black):

[ndb_mgmd]
hostname=localhost
datadir=/home/billy/my_cluster/data
nodeid=1

[ndbd default]
noofreplicas=2
datadir=/home/billy/my_cluster/data

[ndbd]
hostname=localhost
nodeid=3

[ndbd]
hostname=localhost
nodeid=4

[mysqld]
nodeid=50

config.ini (blue):

[ndb_mgmd]
hostname=localhost
datadir=/home/billy/my_cluster/data
nodeid=1

[ndbd default]
noofreplicas=2
datadir=/home/billy/my_cluster/data

[ndbd]
hostname=localhost
nodeid=3

[ndbd]
hostname=localhost
nodeid=4

[mysqld]
nodeid=50

my.cnf for primary cluster (black):

[mysqld]
ndbcluster
datadir=/home/billy/my_cluster/data
server-id=8
log-bin=black-bin.log 
ndb-log-transaction-id=1
binlog-format=ROW
ndb-log-update-as-write=0

my.cnf for secondary cluster (blue):

[mysqld]
ndbcluster
datadir=/home/billy/my_cluster/data
server-id=9
log-bin=blue-bin.log
ndb-log-transaction-id=1
binlog-format=ROW
ndb-log-update-as-write=0
ndb-log-apply-status=1

Note that the options set in the my.cnf file are very important – if any of these are missing then things will not work as expected.

Start up primary cluster (black):

billy@black:~/my_cluster$ ndb_mgmd --initial
   -f conf/config.ini --configdir=/home/billy/my_cluster/conf/
billy@black:~/my_cluster$ ndbd --initial
billy@black:~/my_cluster$ ndbd --initial
billy@black:~/my_cluster$ ndb_mgm -e show # wait for ndbds to finish starting
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3    @127.0.0.1  (mysql-5.5.15 ndb-7.2.1, Nodegroup: 0, Master)
id=4    @127.0.0.1  (mysql-5.5.15 ndb-7.2.1, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @127.0.0.1  (mysql-5.5.15 ndb-7.2.1)

[mysqld(API)]   3 node(s)
id=50 (not connected, accepting connect from any host)

billy@black:~/my_cluster$ mysqld --defaults-file=conf/my.cnf &

Start up secondary cluster (blue):

billy@blue:~/my_cluster$ ndb_mgmd --initial
   -f conf/config.ini --configdir=/home/billy/my_cluster/conf/
billy@blue:~/my_cluster$ ndbd --initial
billy@blue:~/my_cluster$ ndbd --initial
billy@blue:~/my_cluster$ ndb_mgm -e show # wait for ndbds to finish starting
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3    @127.0.0.1  (mysql-5.5.15 ndb-7.2.1, Nodegroup: 0, Master)
id=4    @127.0.0.1  (mysql-5.5.15 ndb-7.2.1, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @127.0.0.1  (mysql-5.5.15 ndb-7.2.1)

[mysqld(API)]   3 node(s)
id=50 (not connected, accepting connect from any host)

billy@blue:~/my_cluster$ mysqld --defaults-file=conf/my.cnf &

Both clusters are now running and replication can be activated for both sites:

billy@black:~/my_cluster$ mysql -u root --prompt="black-mysql> "
black-mysql> CREATE USER repl_user@192.168.1.16;
black-mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.1.16
                 IDENTIFIED BY 'billy';

billy@blue:~/my_cluster$ mysql -u root --prompt="blue-mysql> "
blue-mysql> CREATE USER repl_user@192.168.1.20;
blue-mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.1.20
                 IDENTIFIED BY 'billy';
blue-mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.20',
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='billy',
    -> MASTER_LOG_FILE='',
    -> MASTER_LOG_POS=4;
blue-mysql> START SLAVE;

black-mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.16',
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='billy',
    -> MASTER_LOG_FILE='',
    -> MASTER_LOG_POS=4;
black-mysql> START SLAVE;

Set up enhanced conflict detection & resolution

The first step is to identify the tables that need conflict detection enabling. Each of those tables then has to have an entry in the mysql.ndb_replication table where they’re tagged as using the new NDB$EPOCH_TRANS() function – you could also choose to use NDB$EPOCH(), in which case only the changes to conflicting rows will be backed-out rather than the full transactions. A few things to note:

• This must be done before creating the application tables themselves
• Should only be done on the primary
• By default the table doesn’t exist and so the very first step is to create it

black-mysql> CREATE TABLE mysql.ndb_replication  (
    ->     db VARBINARY(63),
    ->     table_name VARBINARY(63),
    ->     server_id INT UNSIGNED,
    ->     binlog_type INT UNSIGNED,
    ->     conflict_fn VARBINARY(128),
    ->     PRIMARY KEY USING HASH (db, table_name, server_id)
    -> )   ENGINE=NDB
    -> PARTITION BY KEY(db,table_name);
black-mysql> INSERT INTO mysql.ndb_replication VALUES ('clusterdb', 'simple1', 8, 0,
'NDB$EPOCH_TRANS()');
black-mysql> INSERT INTO mysql.ndb_replication VALUES ('clusterdb', 'simple2', 8, 0,
'NDB$EPOCH_TRANS()');
black-mysql> INSERT INTO mysql.ndb_replication VALUES ('clusterdb', 'simple3', 8, 0,
'NDB$EPOCH_TRANS()');

For each of these tables you should also create an exceptions table which will record any conflicts that have resulted in changes being rolled back; the format of these tables is rigidly defined and so take care to copy the types exactly; again this only needs doing on the primary:

black-mysql> CREATE DATABASE clusterdb;USE clusterdb;
black-mysql> CREATE TABLE simple1$EX (server_id INT UNSIGNED,
               master_server_id INT UNSIGNED, master_epoch BIGINT UNSIGNED,
               count INT UNSIGNED, id INT NOT NULL, PRIMARY KEY(server_id,
               master_server_id, master_epoch, count)) ENGINE=NDB;
black-mysql> CREATE TABLE simple2$EX (server_id INT UNSIGNED,
               master_server_id INT UNSIGNED, master_epoch BIGINT UNSIGNED,
               count INT UNSIGNED, id INT NOT NULL, PRIMARY KEY(server_id,
               master_server_id, master_epoch, count)) ENGINE=NDB;
black-mysql> CREATE TABLE simple3$EX (server_id INT UNSIGNED,
               master_server_id INT UNSIGNED, master_epoch BIGINT UNSIGNED,
               count INT UNSIGNED, id INT NOT NULL, PRIMARY KEY(server_id,
               master_server_id, master_epoch, count)) ENGINE=NDB;

Finally, the application tables themselves can be created (this only needs doing on the primary as they’ll be replicated to the secondary):

black-mysql> CREATE TABLE simple1 (id INT NOT NULL PRIMARY KEY, value INT) ENGINE=ndb;
black-mysql> CREATE TABLE simple2 (id INT NOT NULL PRIMARY KEY, value INT) ENGINE=ndb;
black-mysql> CREATE TABLE simple3 (id INT NOT NULL PRIMARY KEY, value INT) ENGINE=ndb;

Everything is now set up and the new configuration can be tested to ensure that conflicts are detected and the correct updates are rolled back.

Testing enhanced active-active replication and conflict detection

The first step is to add some data to our new tables (note that at this point replication is running and so they only need to be created on the primary) and then update 1 row to make sure that it is replicated to the secondary:

black-mysql> INSERT INTO simple1 VALUES (1,10);
black-mysql> INSERT INTO simple2 VALUES (1,10);
black-mysql> INSERT INTO simple3 VALUES (1,10);
black-mysql> UPDATE simple1 SET value=12 WHERE id=1;

blue-mysql> USE clusterdb;
blue-mysql> SELECT * FROM simple1;
+----+-------+
| id | value |
+----+-------+
|  1 |    12 |
+----+-------+

It is important that the NDB$EPOCH_TRANS() function rolls back any transactions on the secondary that involve a conflict (as well as subsequent, dependent transactions that modify the same rows); to do this manually the simplest approach is to stop the slave IO thread on the secondary thread in order to increase the size of the window of conflict (which is otherwise very short). Once the slave IO thread has been stopped a change is made to table simple1 on the primary and then the secondary makes a (conflicting) change to the same row as well as making a change to table simple2 in the same transaction. A second transaction on the primary will change a row in simple3 – as it doesn’t touch any rows that have been involved in a conflict then that change should stand.

blue-mysql> STOP SLAVE IO_THREAD;

black-mysql> UPDATE simple1 SET value=13 WHERE id=1;

blue-mysql> BEGIN; # conflicting transaction
blue-mysql> UPDATE simple1 SET value=20 WHERE id=1;
blue-mysql> UPDATE simple2 SET value=20 WHERE id=1;
blue-mysql> COMMIT;
blue-mysql> UPDATE simple3 SET value=20 WHERE id=1; # non conflicting
blue-mysql> SELECT * FROM simple1;
+----+-------+
| id | value |
+----+-------+
|  1 |    20 |
+----+-------+
blue-mysql> SELECT * FROM simple2;
+----+-------+
| id | value |
+----+-------+
|  1 |    20 |
+----+-------+
blue-mysql> SELECT * FROM simple3;
+----+-------+
| id | value |
+----+-------+
|  1 |    20 |
+----+-------+

If you now check the exception tables then you can see that the primary (black) has received the changes from the secondary (blue) and because the first transaction updated the same row in simple1 during its window of conflict it has recorded that the change needs to be rolled back – this will happen as soon as the replication thread is restarted on the secondary:

black-mysql> SELECT * FROM simple1$EX;
+-----------+------------------+---------------+-------+----+
| server_id | master_server_id | master_epoch  | count | id |
+-----------+------------------+---------------+-------+----+
|         8 |                9 | 1494648619009 |     3 |  1 |
+-----------+------------------+---------------+-------+----+

black-mysql> SELECT * FROM simple2$EX;
+-----------+------------------+---------------+-------+----+
| server_id | master_server_id | master_epoch  | count | id |
+-----------+------------------+---------------+-------+----+
|         8 |                9 | 1494648619009 |     1 |  1 |
+-----------+------------------+---------------+-------+----+

black-mysql> SELECT * FROM simple3$EX;
Empty set (0.05 sec)

blue-mysql> START SLAVE IO_THREAD;
blue-mysql> SELECT * FROM simple1;
+----+-------+
| id | value |
+----+-------+
|  1 |    13 |
+----+-------+

blue-mysql> SELECT * FROM simple2;
+----+-------+
| id | value |
+----+-------+
|  1 |    10 |
+----+-------+

blue-mysql> SELECT * FROM simple3;
+----+-------+
| id | value |
+----+-------+
|  1 |    20 |
+----+-------+

These are the results we expect – simple1 has the value set by the primary with the subsequent change on the secondary rolled back; simple2 was not updated by the primary but the change on the secondary was rolled back as it was made in the same transaction as the conflicting update to simple1. The change on the secondary to simple3 has survived as it was made outside of any conflicting transaction and the change was not dependent on any conflicting changes. Finally just confirm that the data is identical on the primary:

black-mysql> SELECT * FROM simple1;
+----+-------+
| id | value |
+----+-------+
|  1 |    13 |
+----+-------+

black-mysql> SELECT * FROM simple2;
+----+-------+
| id | value |
+----+-------+
|  1 |    10 |
+----+-------+

black-mysql> SELECT * FROM simple3;
+----+-------+
| id | value |
+----+-------+
|  1 |    20 |
+----+-------+

Statistics are provided on the primary that record that 1 conflict has been detected, effecting 1 transaction and that it resulted in 2 row changes being rolled back:

black-mysql> SHOW STATUS LIKE 'ndb_conflict%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Ndb_conflict_fn_max                      | 0     |
| Ndb_conflict_fn_old                      | 0     |
| Ndb_conflict_fn_max_del_win              | 0     |
| Ndb_conflict_fn_epoch                    | 0     |
| Ndb_conflict_fn_epoch_trans              | 1     |
| Ndb_conflict_trans_row_conflict_count    | 1     |
| Ndb_conflict_trans_row_reject_count      | 2     |
| Ndb_conflict_trans_reject_count          | 1     |
| Ndb_conflict_trans_detect_iter_count     | 1     |
| Ndb_conflict_trans_conflict_commit_count | 1     |
+------------------------------------------+-------+

We’re anxious to get feedback on this feature and so please go ahead and download MySQL Cluster 7.2.1 and let us know how you get on through the comments for this post.

London 18th October: Oracle Technology Network MySQL Developer Day

I will be presenting on MySQL Cluster and MySQL Replication at the Oracle Technical Network MySQL Developer day in London on Tuesday, 18 October 2011 (8:30 AM – 4:00 PM). It’s free but you need to register here while there are still places (attendance has been extremely high at other locations).

The MySQL Developer Day is a one-stop shop for you to learn all the essential MySQL skills. In this free, one-day seminar, we will cover everything you need to know to successfully design, develop, and manage your MySQL databases. You’ll also learn the guidelines and best practices in performance tuning and scalability.

Attend this event and gain the knowledge to:

• Develop your new applications cost-effectively using MySQL
• Improve performance of your existing MySQL databases
• Manage your MySQL environment more efficiently

8:30 a.m. – 9:30 a.m. Registration
9:30 a.m. – 10:30 a.m. Keynote: MySQL Essentials
10:30 a.m. – 11:30 p.m. Session: MySQL Replication and Scalabilit
11:30 a.m. – 11:45 a.m. Break
11:45 a.m. – 12:45 p.m. Session: MySQL Cluster
12:45 p.m. – 1:30 p.m. Lunch
1:30 p.m. – 2:30 p.m. Session: MySQL Administration and Management Tools
2:30 p.m. – 3:30 p.m. Session: MySQL Performance Tuning
3:30 p.m. – 4:00 p.m. Close

If you’re going to be in the area, please register and come along – would be good to meet as many of you as possible.

Delayed Replication in MySQL 5.6 Development Release

The new Development Release for MySQL 5.6 contains a great feature that our users have been asking for for a while (work log 344 first raised in 2010!) – delayed replication.

Stop mistake being propagated

The concept (and as you’ll see the execution) is extremely simple. If a user makes a mistake on the master – such as dropping some critical tables – then we want to give them the opportunity to recover the situation by using the data held on one of the slaves. The problem is that the slave is busily trying to keep up with the master and in all likelihood will have dropped these tables before the user has time to pull the plug on the replication stream. What this feature does is give the DBA the option to introduce a configurable delay into the replication process so that they have time to cut it off before the mistake is propagated.

This blog explains how this works, how to set that up and then how to bring the slave up to date (to the point in time just before the mistake was made on the master).

MySQL Replication Implementation

To understand how this is implemented, it helps to have a little bit of background on how MySQL replication is implemented. When a change is made on the master, it is applied to the master’s local disk copy and then written to the binary log. The change is then asynchronously (but normally immediately) copied from the master’s binary log to the relay log on the slave; from there an SQL thread on the slave will read the change from the relay log and apply it to the slave’s copy of the data.

This feature works by allowing the user to configure a delay between when the change is applied on the master and when that change is taken from the relay log and applied to the slave. Note that if the master fails during this delay period then the change is not lost as it is has already been safely recorded in the slave’s relay log.

Immediate & Delayed Replication

As the delay is implemented on the slave, you are free to use ‘real-time’ replication to one slave (to allow the fastest possible failover if the master fails) and delayed replication to a second slave to guard against user error. This is the setup that this post steps through.

For simplicity, all three MySQL Servers will be run on a single host but each uses a different port number as shown in the diagram. “slave” will apply changes as quickly as it can while “slave2″ will introduce a delay when applying changes from its relay log.

Setting up the first slave is very standard:

master> CREATE USER repl_user@localhost;
master> GRANT REPLICATION SLAVE ON *.* TO 
  repl_user@localhost IDENTIFIED BY 'pw';

slave> CHANGE MASTER TO
 -> MASTER_HOST = 'localhost',
 -> MASTER_PORT = 3306,
 -> MASTER_USER = 'repl_user',
 -> MASTER_PASSWORD = 'pw';

slave> start slave;

slave2> CHANGE MASTER TO
 -> MASTER_HOST = 'localhost',
 -> MASTER_PORT = 3306,
 -> MASTER_USER = 'repl_user',
 -> MASTER_PASSWORD = 'pw',
 -> MASTER_DELAY = 20;

slave2> START SLAVE;

master> CREATE DATABASE clusterdb;USE clusterdb;
master> CREATE TABLE towns (Town VARCHAR(20));

master> INSERT INTO towns VALUES ("Maidenhead"),("Bray");

slave> SELECT * FROM towns;
 +------------+
 | Town       |
 +------------+
 | Maidenhead |
 | Bray       |
 +------------+

slave2> SELECT * FROM towns;
 Empty set (0.00 sec)

slave2> SELECT * FROM towns;
 +------------+
 | Town       |
 +------------+
 | Maidenhead |
 | Bray       |
 +------------+

master> INSERT INTO towns VALUES ("Cookham"),("Marlow");
master> DROP TABLE towns;

slave> SELECT * FROM tables;
     ERROR 1146 (42S02): Table 'clusterdb.tables' doesn't exist

slave2> STOP SLAVE;
slave2> SELECT * FROM towns;
 +------------+
 | Town       |
 +------------+
 | Maidenhead |
 | Bray       |
 +------------+

Bring the Slave up to the Desired Point in Time

This is a good start, while slave has dropped the table, it still exists on slave2. Unfortunately, slave2 is missing the additions to the table that were made just before the mistake was made. The next step is to bring slave 2 almost up to date – stopping just before the table was dropped. To do this we need to find the position within the master’s binary log just before the table was dropped – this can be done using the SHOW BINLOG EVENTS command on the master. Once we have that position (file-name + position) we can tell slave 2 to catch up just to that point using START SLAVE UNTIL . Once that has been done, I check that the extra 2 inserts have been applied to slave2:

master> SHOW BINLOG EVENTS\G

....

*************************** 10. row ***************************
 Log_name: ws2-bin.000001
 Pos: 842
 Event_type: Query
 Server_id: 1
 End_log_pos: 957
 Info: use `clusterdb`; INSERT INTO towns VALUES ("Cookham"),("Marlow")
 *************************** 11. row ***************************
 Log_name: ws2-bin.000001
 Pos: 957
 Event_type: Xid
 Server_id: 1
 End_log_pos: 984
 Info: COMMIT /* xid=32 */
 *************************** 12. row ***************************
 Log_name: ws2-bin.000001
 Pos: 984
 Event_type: Query
 Server_id: 1
 End_log_pos: 1096
 Info: use `clusterdb`; DROP TABLE `towns` /* generated by server */

slave2> START SLAVE UNTIL
 -> MASTER_LOG_FILE='ws2-bin.000001',
 -> MASTER_LOG_POS=984;

slave2> SELECT * FROM towns;
 +------------+
 | Town       |
 +------------+
 | Maidenhead |
 | Bray       |
 | Cookham    |
 | Marlow     |
 +------------+

Success! Now slave2 contains exactly the data we need. After this it’s up to you what to do next; typically this could involve promoting slave2 to be the new master.

If you want to try this out for yourselves then you can download the MySQL 5.6 Milestone Development Release from dev.mysql.com (select the Development Maintenance Release sub-tab to get MySQL 5.6).

Feature Preview – Multi-Threaded Replication Slaves

This week, MySQL released a feature-preview – the ability to have multiple threads on the slave so that it is better able to keep pace with the updates being applied on the master. To simplify potential sequencing issues, all updates for a particular database will be handled by the same thread – in other words you need to make sure that your application uses multiple databases in order to see the benefits.
Luís Soares (from the development team) has written a great Blog going into the details.

Almost here – MySQL Cluster at Collaborate 11

A quick reminder that MySQL is well represented at the Oracle Collaborate conference which starts in Orlando on Sunday.

For those not familiar with Collaborate, it’s the big community conference for Oracle users – this year it’s in Orlando from April 10th through 14th (I’ve just re-checked the weather forecast, 31 Celsius vs. -18 at the last conference I presented at – OOW Beijing in December – what a difference 4 months and 8,000 miles make!).

I’ll be presenting on MySQL Cluster in a session called “Building Highly Available Scalable Real-Time Services with MySQL Cluster” where I’ll focus on:

• Basics of MySQL Cluster – what it does, who uses it and why
• Accessing your data – SQL and NoSQL access methods
• Latest features
• What’s coming in the future.

My session starts at 8:00 am on Tuesday 12th April (sorry for the early start) and is in room 306A.

For people interested in MySQL Cluster, another session you should try to attend is “MySQL Cluster for the Enterprise” presented by Craig Russell at 2:15 pm on Wednesday 13th April.

Other MySQL HA topics from the Oracle team:

• Boosting MySQL replication performance through the multi-threaded slave.
• MYSQL 5.5 REPLICATION
• INTRODUCTION TO MYSQL REPLICATION (Tutorial)
• SHARDING TECHNIQUES FOR MYSQL

To get an overall picture of what is happening to MySQL in Oracle, you should attend Tomas Ulin’s (VP of MySQL Engineering) “The State of MySQL” session at 9:15 am on Monday 11th April.

You can see a full list of sessions in the MySQL track here.

And last but not least, come and visit us at the MySQL booths in the Oracle Demo Grounds (Booth #657) to chat with us and/or get a demo. Here are the opening times:

• Monday 6:00pm – 8:00pm (Welcome Reception)
• Tuesday 10:15am – 4:00pm & 5:30pm-7:00pm (Reception)
• Wednesday 10:15am – 4:00pm

I’ll be at the demo booth as much as possible but definitely for the 6:00pm – 8:00pm slot on Monday and from 10:15am – 1pm on Wednesday – hope to see some of you there.

Register for the event at http://collaborate11.ioug.org/Home/Registration/tabid/82/Default.aspx

Want to attend a MySQL 5.5 seminar?

Oracle University has added a new course to its training catalog “Introduction to MySQL 5.5″ which is a day-long seminar. The seminar goes into some detail on many aspects of using MySQL and of course pays particular attention to the new features in MySQL 5.5. I’ve reviewed the material and can assure you that there is plenty of it!

Of most relevance to this blog is the overview of MySQL Cluster (which isn’t a big focus of the seminar as Cluster is currently using MySQL 5.1) and MySQL replication – the highest profile 5.5 feature being asynchronous replication which can make sure that zero updates are lost even if the master fails catastrophically.

At the time of writing, neither the on-line and instructor-led sessions have been scheduled and so you should register an interest here. When OU have sufficient registrants they’ll schedule the sessions (note that unlike webinars, white papers etc. there is a charge for this training).

Here’s the official description:

Introduction to MySQL 5.5

MySQL (including Cluster) at the 2010 UK Oracle User Group Conference

The UK Oracle User Group Conference runs from 29th November through 1st December 2010 – the MySQL content is on Wednesday 1st at the ICC in Birmingham. This is a great chance for MySQL users to find out more on the latest developments and question some of the experts (both from inside and outside Oracle) and for Oracle users who don’t have experience with MySQL to discover what it’s all about.

The day starts with a keynote from Rich Mason (VP responsible for MySQL sales) at 8:45; I’ll be part of a MySQL panel at 9:50 and then Mat Keep and I will be presenting on MySQL Cluster at 14:15. In the Cluster session we’ll will introduce MySQL Cluster and cover the most important features of MySQL Cluster 7.1: ndbinfo; MySQL Cluster Connector/Java and other features that push the limits of MySQL Cluster into new workloads and communities.

ndbinfo presents real-time usage statistics from the MySQL Cluster data nodes as a series of SQL tables, enabling developers and administrators to monitor database performance and optimize their applications.

Designed for Java developers, the MySQL Cluster Connector for Java implements an easy-to-use and high performance native Java interface and OpenJPA plug-in that maps Java classes to tables stored in the MySQL Cluster database.

Finally, we’ll present some real-world case studies and explain some of the new capabilities that are currently in development.

Another session that’s likely to be suited to people interested in MySQL Cluster is a more general presentation on delivering High Availability with MySQL – presented by Mario Beck.

As you’d expect there are many more MySQL sessions to choose from – check the full schedule.

To register for just the MySQL sessions use the MySQL registration page, the full conference registration page is here.

Hope to see as many of you there as possible!

New white paper – MySQL Replication – Enhancing Scalability and Availability with MySQL 5.5

MySQL Replication from 1 Master to Multiple Slaves

A new white paper has been published that covers MySQL Replication – background information, how it works, how to use it and what’s new in MySQL 5.5. Simply register for the white paper at mysql.com and you’ll be sent your free copy.

The paper starts by covering the fundamental concepts behind replication such as the difference between synchronous and asynchronous replication and the idea behind semisynchronous replication.

It goes on to describe the common use-cases for replication – scaling out, high availability, geographic redundancy and offloading backups or analytics.

Various replication topologies are discussed from simple master-slave to multi-master rings.

As the title of the paper suggests, the paper covers the significant replication changes introduced in MySQL 5.5:

• Semisynchronous replication: Improved resilience by having master wait for slave to persist events.
• Slave fsync tuning & Automatic relay log recovery: Option to dictate when relay logs are written to disk rather than relying on default operating system behavior; set sync_relay_log=1 to ensure that no more than 1 statement or transaction is missing from the relay log after a crash. The slave can now recover from corrupted relay logs by requesting corrupt entries to be resent from the master. Three new options are introduced (sync-master-info, sync-relay-log and sync-relay-log-info)
• Replication Heartbeat: Automatically checks the status of the connection between the master and the slave(s), allowing a more precise failure detection mechanism. Can detect loss of connection within milliseconds (configurable). Avoid unnecessary relay log rotation when the master is idle.
• Per server replication filtering: When a server is removed from a replication ring, a surviving server can be selected to remove its outstanding replication messages once they’ve been applied by all servers.
• Precise Slave Type Conversions: Allows different types to be used on the master and slave, with automatic type promotion and demotion when using row-based replication (already possible with statement-based replication)
• Individual Log Flushing: Selectively flush server logs when using ‘FLUSH LOGS’ for greater control
• Safe logging of mixed transactions: Replicate transactions containing both InnoDB and MyISAM changes

To be better able to use replication, it helps to understand the basics about how MySQL Replication has been implemented – for example the roles of the binary and relay logs and so this is covered.

Perhaps the most useful sections are those that actually walk through using MySQL Replication – specifically these activities:

• Configuring, running and testing replication
• Migrating from traditional asynchronous replication to semisynchronous replication
• Administering & monitoring replication (including MySQL Enterprise Monitor)
• Failover and recovery

Replication is a little different when using MySQL Cluster; most significantly because you can have multiple MySQL Servers and other application nodes modifying the same database but also because the domains where MySQL Cluster has been used have required extreme High Availability and so there are Cluster-unique features such as active-active replication with conflict detection and resolution. This paper isn’t focussed on MySQL Cluster but a summary of these differences is included.

Get MySQL Replication up and running in 5 minutes

MySQL Replication is incredibly simple to get up and running and this (short) post steps you through it.

Simple Master -> Slave Replication

MySQL allows you to build up complex replication hierarchies, such as multi-master, chains of read slaves, backup databases at a remote site or any combination of these. This post focuses on a simple single master to single slave topology – the more complex solutions are built from this basic building block.

This post also makes the assumption that the 2 MySQL Servers have been installed but that there is no existing data in the master that needs to be copied to the slave – it’s not complex to add that extra requirement and it will be covered in a future post.

Server “black” (192.168.0.31) is to be our master and “blue” (192.168.0.34) the slave.

Step 1: Edit the configuration files & start the MySQL Servers

The first step in setting up replication involves editing the “my.cnf” file on the servers that will serve as the master and slave. A default is provided with the MySQL installation but in case there is already a production MySQL database running on these servers, we provide local configuration files “master.cnf” and “slave.cnf” that will be used when starting up the MySQL servers.

At a minimum we’ll want to add two options to the [mysqld] section of the master.cnf file:

• log-bin: in this example we choose black-bin.log
• server-id: in this example we choose 1. The server cannot act as a replication master unless binary logging is enabled. The server_id variable must be a positive integer value between 1 to 2^32

master.cnf:

[mysqld]
server-id=1
log-bin=black-bin.log
datadir=/home/billy/mysql/master/data
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Note: For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should also specify the innodb_flush_log_at_trx_commit=1, sync_binlog=1 options.

Next, you’ll need to add the server-id option to the [mysqld] section of the slave’s slave.cnf file. The server-id value, like the master_id value, must be a positive integer between 1 to 2^32, It is also necessary that the ID of the slave be different from the ID of the master. If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves.

slave.cnf:

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
datadir=/home/billy/mysql/slave/data

Now, start the MySQL servers using the service manager or directly from the command line if not being run as a service:

[billy@black ~]$ mysqld --defaults-file=/home/billy/mysql/master/master.cnf &

[billy@blue ~]$ mysqld --defaults-file=/home/billy/mysql/slave/slave.cnf&

Step 2: Create Replication User

Create an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege:

[billy@black ~]$ mysql -u root --prompt='master> '
master> CREATE USER repl_user@192.168.0.34;
master> GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.0.34 IDENTIFIED BY 'billy';

Step 3: Initialize Replication

We are now ready to initialize replication on the slave; issue a CHANGE MASTER command:

slave> CHANGE MASTER TO MASTER_HOST='192.168.0.31',
 -> MASTER_USER='repl_user',
 -> MASTER_PASSWORD='billy',
 -> MASTER_LOG_FILE='',
 -> MASTER_LOG_POS=4;

Where:

• MASTER_HOST: the IP or hostname of the master server, in this example blue or 192.168.0.31
• MASTER_USER: this is the user we granted the REPLICATION SLAVE privilege to in Step 2, in this example, “repl_user”
• MASTER_PASSWORD: this is the password we assigned to ”rep_user” in Step 2
• MASTER_LOG_FILE: is an empty string (wouldn’t be empty if there were existing writes to be picked up from the master)
• MASTER_LOG_POS: is 4 (would likely be different if there were existing writes to be picked up from the master)

Finally, start replication on the slave:

slave> start slave;

Step 4: Basic Checks

Now we are ready to perform a basic check to ensure that replication is indeed working. In this example we insert a row of data into the “simples” table on the master server and then verify that these new rows materialize on the slave server:

master> create database clusterdb;
master> create table clusterdb.simples (id int not null primary key) engine=ndb;
master> insert into clusterdb.simples values (999),(1),(2),(3);
slave> select * from clusterdb.simples;
 +-----+
 | id  |
 +-----+
 |   1 |
 |   2 |
 |   3 |
 | 999 |
 +-----+

Software preview MySQL Scriptable Replication

Fig. 1 MySQL per-row replication filtering

A MySQL Software preview is available which allows you to write Lua scripts to control replication on a statement-by-statement basis. Note that this is prototype functionality and is not supported but feedback on its usefulness would be gratefully received.The final version would allow much greater functionality but this preview allows you to implement filters on either the master or slave to examine the statements being replicated and decide whether to continue processing each one or not.

After reading this article, you may be interested in trying this out for yourself and want to create your own script(s). You can get more information on the functionality and download the special version of MySQL from http://forge.mysql.com/wiki/ReplicationFeatures/ScriptableReplication

To understand how this feature works, you first need to understand the very basics about how MySQL replication works. Changes that are made to the ‘Master’ MySQL Server are written to a binary log. Any slave MySQL Servers that subscribe to this master are sent the data from the master’s binary log; the slave(s) then copy this data to their own relay log(s). The slave(s) will then work through all of the updates in their relay logs and apply them to their local database(s). The implementation is a little more complex when using MySQL Cluster as the master’s updates may come through multiple MySQL Servers or directly from an application through the NDB API but all of the changes will still make it into the binary log.

MySQL Replication supports both statement and row based replication (as well as mixed) but this software preview is restricted to statement based replication. As MySQL Cluster must use row based replication this preview cannot be used with Cluster but the final implementation should work with all storage engines.

As show in Fig. 1 there are 4 points where you can choose to filter statements being replicated:

1. Before the update is written to the binary log
2. After the update has been read from the binary log
3. Before the update is written to the relay log
4. After the update has been read from the relay log

The final 2 interest me most as it allows us to have multiple slaves which apply different filters – this article includes a worked example of how that could be exploited.

Fig. 2 Details for each filtering point

The filters are written as Lua scripts. The names of the script file, module name and function names vary depending on which of these filtering points is to be used. Fig. 2 shows these differences. In all cases, the scripts are stored in the following folder: “<mysql-base-directory>/ext/replication”.

This article creates 2 different scripts – one for each of 2 slave servers. In both cases the filter script is executed after an update is read from the relay log. One slave will discard any statement of the form “INSERT INTO <table-name> SET sub_id = 401, …” by searching for the sub string “sub_id = X” where X is even while the second slave will discard any where X is odd. Any statement that doesn’t include this pattern will be allowed through.

Fig. 3 Implementation of odd/even sharded replication

If a script returns TRUE then the statement is discarded, if it returns FALSE then the replication process continues. Fig. 3 shows the architecture and pseudo code for the odd/even replication sharding.

The actual code for the two slaves is included here:

slave-odd: <mysql-base-directory>/ext/replication/relay_log.lua

function after_read(event)
  local m = event.query
  if m then
    id = string.match(m, "sub_id = (%d+)")
    if id then
      if id %2 == 0 then
        return true
      else
        return false
      end
    else
      id = string.match(m, "sub_id=(%d+)")
        if id then
          if id %2 == 0 then
            return true
          else
            return false
          end
       else
         return false
       end
    end
  else
    return false
  end
end

slave-even: <mysql-base-directory>/ext/replication/relay_log.lua

function after_read(event)
  local m = event.query
  if m then
    id = string.match(m, "sub_id = (%d+)")
    if id then
      if id %2 == 1 then
        return true
      else
        return false
      end
    else
      id = string.match(m, "sub_id=(%d+)")
        if id then
          if id %2 == 1 then
            return true
          else
            return false
          end
       else
         return false
       end
    end
  else
    return false
  end
end

Replication can then be set-up as normal as described in Setting up MySQL Asynchronous Replication for High Availability with the exception that we use 2 slaves rather than 1.

Once replication has been started on both of the slaves, the database and tables should be created; note that for some reason, the creation of the tables isn’t replicated to the slaves when using this preview load and so the tables actually need to be created 3 times:

mysql-master> CREATE DATABASE clusterdb; mysql-master> USE clusterdb; mysql-master> CREATE TABLE sys1 (code INT NOT NULL PRIMARY KEY, country VARCHAR (30)) engine=innodb; mysql-master> CREATE TABLE subs1 (sub_id INT NOT NULL PRIMARY KEY, code INT) engine=innodb;

mysql-slave-odd> USE clusterdb; mysql-slave-odd> CREATE TABLE sys1 (code INT NOT NULL PRIMARY KEY, country VARCHAR (30)) engine=innodb; mysql-slave-odd> create table subs1 (sub_id INT NOT NULL PRIMARY KEY, code INT) engine=innodb;

mysql-slave-even> USE clusterdb; mysql-slave-even> CREATE TABLE sys1 (code INT NOT NULL PRIMARY KEY, country VARCHAR (30)) engine=innodb; mysql-slave-even> CREATE TABLE subs1 (sub_id INT NOT NULL PRIMARY KEY, code INT) engine=innodb;

The data can then be added to the master and then the 2 slaves can be checked to validate that it behaved as expected:

mysql-master> INSERT INTO sys1 SET area_code=33, country="France";
mysql-master> INSERT INTO sys1 SET area_code=44, country="UK";
mysql-master> INSERT INTO subs1 SET sub_id=401, code=44;
mysql-master> INSERT INTO subs1 SET sub_id=402, code=33;
mysql-master> INSERT INTO subs1 SET sub_id=976, code=33;
mysql-master> INSERT INTO subs1 SET sub_id=981, code=44;

mysql-slave-odd> SELECT * FROM sys1;
+------+---------+
| code | country |
+------+---------+
|  33  | France  |
|  44  | UK      |
+------+---------+

mysql-slave-odd> SELECT * FROM subs1;
+--------+------+
| sub_id | code |
+--------+------+
|   401  | 44   |
|   981  | 44   |
+--------+------+

Fig. 4 Results of partitioned replication

mysql-slave-even> SELECT * FROM sys1;
+------+---------+
| code | country |
+------+---------+
|  33  | France  |
|  44  | UK      |
+------+---------+
mysql-slave-even> SELECT * FROM subs1;
+--------+------+
| sub_id | code |
+--------+------+
|   402  | 33   |
|   976  | 33   |
+--------+------+

Fig. 4 illustrates this splitting of data between the 2 slaves – all rows from the system table are stored in both databases (as well as in the master) while the data in the subscriber table (and it would work for multiple subscriber tables too) are partitioned between the 2 databases – odd values in one, even in the other. Obviously, this could be extended to more slaves by changing the checks in the scripts.

As an illustration of how this example could be useful, all administrative data could be provisioned into and maintained by the master – both system and subscriber data. Each slave could then serve a subset of the subscribers, providing read-access to the administrative data andread/write access for the more volatile subscriber data (which is mastered on the ‘slave’). In this way, there can be a central point to manage the administrative data while being able to scale out to multiple, databases to provide maximum capacity and performance to the applications. For example, in a telco environment, you may filter rows by comparing a subscriber’s phone number to a set of area codes so that the local subscribers are accessed from the local database – minimising latency.

From a data integrity perspective, this approach is safe if (and only if) the partitioning rules ensures that all related rows are on the same slave (in our example, all rows from all tables for a particular subscriber will be on the same slave – so as long as we don’t need transactional consistency between different subscribers then this should be safe).

Fig. 5 Partitioned replication for MySQL Cluster

As mentioned previously this software preview doesn’t work with MySQL Cluster but looking forward to when it does, the example could be extended by having each of the slave servers be part of the same Cluster. In this case, the partitioned data will be consolidated back into a single database (for this scenario, you would likely configure just one server to act as the slave for the system data). On the face of it, this would be a futile exercise but in cases where the performance bottlenecks on the throughput of a single slave server, this might be a way to horizontally scale the replication performance for applications which make massive numbers of database writes.

MySQL Cluster: Geographic Replication Deep-Dive webinar

Multi-Master Replication for HA with MySQL Cluster

MySQL Cluster has been deployed into some of the most demanding web, telecoms and enterprise /
government workloads, supporting 99.999% availability with real time performance and linear write scalability.

You can register on-line here.

Tune into this webinar where you can hear from the MySQL Cluster product management team provide a detailed “deep dive” into one of MySQL Cluster’s key capabilities – Geographic Replication.

In this session, you will learn how using Geographic Replication enables your applications to:

• Achieve higher levels of availability within a data center or across a WAN
• Locate data closer to users, providing lower latency access
• Replicate to other MySQL storage engines for complex data analysis and reporting of real time data
• Gow to get started with Geographic Replication

Tuesday, November 24, 2009: 10:00 Central European time

• Tue, Nov 24:  09:00 Western European time
• Tue, Nov 24:  11:00 Eastern European time

The presentation will be approximately 1 hour long, including on-line Q&A.