If you plan to use this new feature to express your needs while given a chance, why not to start with replication-related bugs in latest and greatest MySQL 5.6.12? Here is my "Top 10" list (starting with recently reported):
Second, I would say that I am quite surprised at how much we have done in this release. The previous release (2.0.7) was in February, which is just a few months ago, and yet it looks like ages when I see the list of improvements, new features and bug fixes in the Release Notes. I did not realized it until I ran my last batch of checks to test the upgrade from the previous release, which I hadn’t run for quite a long[Read more...]
The main benefit of using GTIDs is to have much easier failover than with file-based replication. We will see how to change the replication topology when using GTID-based replication. That will show where GTIDs shine and where improvements are expected.
This is the second post of a series of articles focused on MySQL 5.6 GTIDs. You can find part one here.
Our goal will be to go from setup #1 to setup #2 on the picture below, following various scenarios:
For these tests, all servers are running[Read more...]
I recently wrote about non-deterministic queries in the replication stream. That’s resolved by using either MIXED or ROW based replication rather than STATEMENT based.
Another thing that’s not fully handled by STATEMENT based replication is temporary tables. Imagine the following:
If for any reason a slave server shuts down and restarts after the temp table creation, replication will break because the temporary table will no longer exist on the restarted slave server. It’s obvious when you think about it, but[Read more...]
As you probably know, Tungsten Replicator can replicate data from MySQL to MongoDB. The installation is relatively simple and, once done, replication works very well. There was a bug in the installation procedure recently, and as I was testing that the breakage has been fixed, I wanted to share the experience of getting started with this replication.
For this exercise, we will use a MySQL sandbox running MySQL 5.5.31.
We download the binaries from dev.mysql.com and install a sandbox, making sure that it is configured as master, and that it is used row-based-replication.
$ mkdir -p $HOME/opt/mysql[Read more...]
$ cd ~/downloads
You might find a warning like the below in your error log:
130522 17:54:18 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
Statement: INSERT INTO tbl2 SELECT * FROM tbl1 WHERE col IN (417,523)
What do MariaDB and MySQL mean with this warning? The server can’t guarantee that this exact query, with STATEMENT based replication, will always yield identical results on the slave.
Does that mean that you have to use ROW based (or MIXED) replication? Possibly, but not[Read more...]
DrupalCamp Conference is hold in Göteborg, Sweden on May 25, 2013.
MySQL is part of this show and I would like to invite you to our session on "Scalability and Availability with MySQL Replication" given by Sven Sandberg on Saturday-May 25, 2013 @ 13:45-14:25!
So, if you are close to Göteborg or attending this event, do not forget to come to listen Sven's talk!
Global Transactions Identifiers are one of the new features regarding replication in MySQL 5.6. They open up a lot of opportunities to make the life of DBAs much easier when having to maintain servers under a specific replication topology. However you should keep in mind some limitations of the current implementation. This post is the first one of a series of articles focused on the implications of enabling GTIDs on a production setup.
The manual describes very nicely how to switch to GTID-based replication, I won’t repeat[Read more...]
These days I’ve answer some questions about replication lag, and I realized that most of people does not correctly understand how this process works internally on MySQL, and why does delays happen:
See the bellow image, it represents asynchronous replication on MySQL, I highly recommend[Read more...]
Bearing in mind the number on the image, let’s see how the process works:
Replication is a hot topic in MySQL 5.6, and for good reason: There are many excellent features that make it a strong well-supported feature, from the new Global Transaction Identifiers (GTIDs), to simplified replication configuration and automated failover using MySQL Utilities (now available in alpha as a separate download).Circular Replication
The simplest topology consists of a master server that accepts changes, and slaves that replicate those changes from the master. A common requirement is for a network to have multiple servers that[Read more...]
Got a packet bigger than ‘slave_max_allowed_packet’ bytes and binlog_format=STATEMENT|MIXED
Since version 5.1.64 MySQL introduces a new variable named slave_max_allowed_packet, which was introduced to allow large updates using row-based replication do not cause replication to fail when exceeded max_allowed_packet.
The problem is if you have you replication using binlog_format=STATEMENT or binlog_format=MIXED it ignores this option and use as[Read more...]
One of the first things we do when preparing a client’s infrastructure for Galera deployment is see whether their schema is suitable.
Naturally, checking a schema in the server is more effective than going through other sources and possibly missing bits. On the downside, the only viable way to get this info out of MariaDB[Read more...]
To get a taste of the power of Tungsten Replicator, we will show how to switch roles. This is a controlled operation (as opposed to fail-over), where we can decide when to switch and which nodes are involved.
In our topology, host1 is the master, and we have three slaves. We can either ask for a switch and let the script select the first available slave, or tell the script which slave should be promoted. The script will show us the steps needed to perform the operation.
IMPORTANT! Please note that this operation is not risk free. Tungsten replicator is a simple replication system, not a complete management tool like Continuent Tungsten. WIth the replicator, you must make sure that the applications have stopped writing to the master before starting the switch, and then you[Read more...]
Tungsten Replicator is an open source tool that does high performance replication across database servers. It was designed to replace MySQL replication, although it also supports replication from and to Oracle and other systems. In this article, we will only cover MySQL replication, both simple and multi-master.
To follow the material in this article, you will need a recent build of Tungsten Replicator. You can get the latest ones from http://bit.ly/tr20_builds. In this article, we are using build 2.0.8-167.
Before starting any installation, you should make sure that you have satisfied all the prerequisites. Don't[Read more...]
Read the original article at Why does MySQL replication fail?
When considering active-active multi-master, you must consider it’s foundation technology. Although MySQL replication is straightforward to setup, it can fail in a myriad of ways. Most of those are known and well understood. We can solve them only if we use the technology in the standard way. Click through to the end for multi-master solutions [...]
For more articles like these go to Sean Hull's Scalable StartupsRelated posts:
MySQL 5.6 includes a host of enhancements to replication (http://www.mysql.com/products/enterprise/replication.html), enabling DevOps teams to reliably scale-out their MySQL infrastructure across commodity hardware, on-premise or in the cloud.
One of the most significant enhancements is the introduction of Global Transaction Identifiers (GTIDs) where the primary development motivation was:
- enabling seamless failover or switchover from a replication master to slave
- promoting that slave to the new master
- without manual intervention and with minimal service disruption.
You can download the new MySQL Replication High Availability Guide (http://www.mysql.com/why-mysql/white-papers/mysql-replication-high-availability/) to[Read more...]
MySQL lets database administrators define access rights on many levels – from the ability to run global commands down to access to individual columns. Some rights can be applied to many different objects, such as for example SELECT or UPDATE, which can be granted globally or restricted only to certain databases or tables, while others are only meant for one specific purpose. An example of the latter could be FILE privilege, which permits user to interact with the file system from inside a database instance. It only makes sense as the global right and not anywhere else.
As any other activity that produces changes, GRANT statements are replicated to MySQL slaves. Regardless of the binary log format setting, such events are always logged in STATEMENT format. It is likely because the command needs to handle more than just updating the[Read more...]
The new MySQL 5.6 GA release delivers a host of new capabilities to support developers releasing new services faster, with more agility, performance and security .
One of the areas with the most far-reaching set of enhancements is MySQL replication (http://www.mysql.com/products/enterprise/replication.html) used by the largest web, mobile and social properties to horizontally scale highly-available MySQL databases across distributed clusters of low cost, commodity servers.
A new on-demand MySQL 5.6 replication webinar (http://www.mysql.com/news-and-events/web-seminars/mysql-5-6-replication-enabling-next-generation-of-web-mobile-social-and-cloud-services/) takes you on a guided tour through all of those enhancements, including:
- 5x higher master and slave[Read more...]