Heterogenous replication involves moving data from one database platform to another. This is a complicated endevour because datatypes, date & time formats, and a whole lot more tend to differ across platforms. In fact it’s so complex many enterprises simply employ a commercial solution to take away the drudgery. Join 31,000 others and follow Sean … Continue reading Replicate MySQL to Amazon Redshift with Tungsten: The good, the bad & the ugly →

One of the cool new features in 5.7 Release Candidate is Multi Source Replication, as I previously looked into in 5.7.5 DMR.

I’ve had more and more people like the idea of this, so here’s a quick set-up as to what’s needed and how it could work.

1. Prepare master environments.
2. Prepare 40 masters for replication.
3. Create slave.
4. Insert data into the Masters.

* I originally tried running 50 mysql’s but I just ran out of resources on my old pc, so it’s at 40.

* The real key behind this scenario is that each of the masters has a unique centre_code that is implicitly inserted via the app & users at that site. i.e. no other site will want to modify any data that was entered from that site, e.g. call centre reclaims dept for specific areas / regions, CNC …

MySQL replication isn’t perfect and sometimes our data gets out of sync, either by a failure in replication or human intervention. We are all familiar with Percona Toolkit’s pt-table-checksum and pt-table-sync to help us check and fix data inconsistencies – but imagine the following scenario where we mix regular replication with the Tungsten Replicator:

We have regular replication going from master (db1) to 4 slaves (db2, db3, db4 and db5), but also we find that db3 is also master of db4 and db5 using Tungsten replication for 1 database called test. This setup is currently working this way because it was deployed some time ago when multi-source replication was not possible using regular MySQL replication. This is now a working feature in …

The link to the slides of my talks can be found at the end of this post but first, let me share some thoughts about PLMCE.

Talking with people, I was surprised to be criticized of presenting only the good sides of my solution without giving credit to the good side of the alternative solutions.  More than surprised, I was also a little shocked as I want to be perceived as objective as possible.  Let me try to fix that:

• I am not a GTID and log-slaves detractor, I am a simplicity lover.
• I actually like GTIDs and I have some use-cases for them; one of  them is described in the MariaDB Knowledge Base (search for last_gtid in that page).
• But I think that using GTIDs in the slave protocol is complex and that a …

Version: 5.5.5-10.0.10-MariaDB-log MariaDB Server

When I use mariadb as multi-source slave DB I met a strange problem . Creating index on a vary large partition table , Mariadb do — copy to tmp table on the slave side. It takes a long time and still not finish over 9 hours.

Primary : MySQL 5.6.16 —

add index on a partition table:

CREATE TABLE `track_raw_wap211_log` (
 `table_id` varchar(100) DEFAULT NULL,
 `page_id` varchar(100) DEFAULT NULL,
 `banner_id` varchar(100) DEFAULT NULL,
 `button_id` varchar(100) DEFAULT NULL,
 `test_id` varchar(100) DEFAULT NULL,
 `classfication` varchar(100) DEFAULT NULL,
 `request_refer` varchar(100) DEFAULT NULL,
 `request_url` text,
 `title` varchar(100) DEFAULT NULL,
 `user_id` varchar(100) DEFAULT NULL,
 `language` varchar(100) DEFAULT NULL,
 `event` varchar(100) DEFAULT NULL,
 `event_desc` varchar(100) DEFAULT NULL,
 `event_type` varchar(100) DEFAULT NULL,
 `log_version` varchar(100) DEFAULT …

Updated 2015-04-25: add the link to the slides of my PLMCE talk and a link to a bug number.

MaxScale 1.1.0 is out and includes the new Binlog Server module.  This is the first post in s series of three.  The two others are about Operations and High Availability.  The links to the 2 other posts are at the end of this page.

In this post, I present how to install and configure MaxScale as a Binlog

Note: DO NOT use this procedure in production, this is a proof of concept (POC).  MaxScale 1.1.0 does not yet fully support that procedure and things could go wrong in some situations (see at the end of the post for the details).

In my talk at PLMCE 2015, I presented an architecture to promote a slave as a new master without touching any other slave and I claimed that I tested it.  This HOWTO

In the Install and Configure HOWTO, we learned how to install and configure a MaxScale Binlog Server.  In this HOWTO, I will present the common operations that you might need to perform when using this software.  Those operations include:

Purging Binary Logs, Chaining Binlog Servers, Saving Binary Log Files in the Non-Default Directory, Downloading Binary Logs other than First, Listing Connected

Introduction

One of the primary objectives of MySQL replication is providing an easy failover process i.e. switching to a redundant system if the primary MySQL server fails. In MySQL this translates to switching to the most appropriate slave server in the eventuality of a failure of the master server.

The promotion of a candidate slave to become a new master is based on a lot of factors, undoubtedly the most important factor being that the chosen slave should be most up to date with the primary server (the old master) before we lost it! This blog explains how to use new features in MySQL 5.7.4 and later to make failover easier.

To find the most up to date slave server, a failover script looks at the set of transactions received by the slave and compares it with every other slave to find the one that has received the biggest set of transactions. There could be more sophisticated ways of doing this, for instance you …

Dealing with the failure of a MySQL master is not simple.  The most common solution is to promote a slave as the new master but in an environment where you have many slaves, the asynchronous implementation of replication gets in your way.  The problem is that each slave might be in a different state:

some could be very close to the dead master, some could be missing the latest transactions, and