Working day to day with Percona Remote DBA customers, we have been facing an issue from time to time when MySQL replication lag is flapping between 0 and XXXXX constantly – i.e. Seconds_Behind_Master is 0 for a few secs, then it’s like 6287 or 25341, again 0 and so on. I would like to note the 5 different scenarios and symptoms of this – some are obvious and some are not really.
Symptoms: MySQL error log on a slave shows the slave thread is connecting/disconnecting from a master constantly.
Solution: check whether all nodes in the replication have unique
I was rooting through past blog entries and I stumbled accross a draft post on setting up multi-master (update anywhere) asynchronous replication for MySQL Cluster. The post never quite got finished and published and while the material is now 4 years old it may still be helpfull to some and so I’m posting it now. Note that a lot has happened with MySQL Cluster in the last 4 years and in this area, the most notable change has been the Enhanced conflict resolution with MySQL Cluster active-active replication feature introduced in MySQL Cluster 7.2 and if you’re only dealing with a pair of Clusters, that’s your best option as it removed the need for you[Read more...]
What happened? In a surfeit of overconfidence, we released Tungsten 2.1.1, with faith on the test suite and its result. The faith was justified, as the test suite was able to catch any known problem and regression. The overconfidence was unjustified, because, due to a series of unfortunate events, some sections of the test suite were accidentally disabled, and the regression that was lurking in the dark was not caught.
Therefore, instead of having a quiet post-release week-end, the whole team[Read more...]
This session is an opportunity for you to meet the MySQL engineering team and discuss the latest tools and best practices for sharding MySQL across distributed[Read more...]
A tip for all those cloud users that like cloning database servers (as reported in my book Effective MySQL – Replication Techniques in Depth).
Starting with MySQL 5.6, MySQL instances have a UUID. Cloning servers to quickly create slaves will result in the following error message.
mysql> SHOW SLAVE STATUS\G ... Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. ...
The solution is simple. Clear our the file based configuration file (located in the MySQL datadir) and restart the MySQL instance.
$ rm -f /mysql/data/auto.cnf $ service mysql restart
UPDATE 2013-08-30: Tungsten 2.1.2 was released.
UPDATE 2013-08-23: We have found a few problems that happen when replicating with RBR and temporal columns. We will have to publish an updated bugfix release quite soon.Tungsten Replicator 2.1.1 is out. Key features in this release are:
Recently i updated to MySQL 5.6 and we were really excited about the very good overall performance. But beside a major bug concerning wrong results when running a SELECT that includes a HAVING based on a function (see http://bugs.mysql.com/bug.php?id=69638) we also noticed that from time to time the replication breaks with the following error:
Last_SQL_Errno: 1590 Last_SQL_Error: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log
After some investigation it seemed like this happens if one modifies some user privileges, so we stumbled upon http://bugs.mysql.com/bug.php?id=68892.
Essentially the bug report says that if you use the wrong syntax for GRANT-statements the[Read more...]
Catching up on our reading today, we saw a blog post from Chris Stevens, the multi-faceted VP of Engineering at Traxo, technology consultant, and Full Stack developer. In his post, he notes some of the impressive new database technologies he’s seeing lately in tech blogs and on the conference circuit.
Chris says that when it makes sense he advises his clients to look at non-relational datatstores, but for a number of reasons he works with MySQL wherever possible. Many of his clients looking to run globally distributed applications often need to distribute the database across more than one data center (geo-distribution) for fault tolerance and localized performance.
“Globally distributed applications often require localized performance,” he said in his post. “We can do[Read more...]
Dr Charles Bell and I recently presented a webinar on MySQL Utilities; there was a heavy focus on what you can acheive with them and how you should use them. In case you couldn’t attend or want to listen to some of the details again, the replay from that webinar is available here.
MySQL Utilities provide a collection of command-line utilities that are used for maintaining and administering MySQL databases, including:
Tungsten Replicator is a powerful replication engine that, in addition to providing the same features as MySQL Replication, can also create several topologies, such as
The real weakness of these topologies is that they don’t come together easily. Installation requires several commands, and running them unassisted is a daunting task. Some time ago, we introduced a set of scripts (the Tungsten Cookbook) that allow you to[Read more...]
Gist is a simple way to share snippets and pastes with others. All gists are git repositories, so they are automatically versioned, forkable and usable as a git repository.
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!