Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 28

Displaying posts with tag: gtid (reset)

Q&A: Putting MySQL Fabric to use
+1 Vote Up -0Vote Down

Martin Arrieta and I gave an online presentation last week on “Putting MySQL Fabric To Use.” If you missed it, you can find a recording and the slides here, and the vagrant environment we used plus a transcript of the commands we ran here (be sure to check out the ‘sharding’ branch, as that’s what we used during the webinar).

Thank you all for attending and asking interesting questions. We were unable to answer all of them in the scheduled time, so here are our replies

  [Read more...]
MySQL Slave Scaling and more
+3 Vote Up -0Vote Down

Jean-François talks about binlog servers. Take a look here: http://blog.booking.com/mysql_slave_scaling_and_more.html

MariaDB 10.0.11 Overview and Highlights
+5 Vote Up -0Vote Down

MariaDB 10.0.11 was recently released, and is available for download here:


This is the second GA release of MariaDB 10.0, and 12th overall release of MariaDB 10.0.

This is primarily a bug-fix release.

Here are the main items of note:

  • Updated TokuDB engine to version 7.1.6
  • Updated Spider storage engine to version 3.2 (now Gamma)
  • Updated XtraDB storage engine to version 5.6.17-65.0
  • Updated InnoDB storage engine to version 5.6.17
  • Updated
  •   [Read more...]
    Errant transactions: Major hurdle for GTID-based failover in MySQL 5.6
    +1 Vote Up -0Vote Down

    I have previously written about the new replication protocol that comes with GTIDs in MySQL 5.6. Because of this new replication protocol, you can inadvertently create errant transactions that may turn any failover to a nightmare. Let’s see the problems and the potential solutions.

    In short

    • Errant transactions may cause all kinds of data corruption/replication errors when failing over.
    • Detection of errant transactions can be done with the GTID_SUBSET() and
      [Read more...]
    GTIDs in MySQL 5.6: New replication protocol; new ways to break replication
    +1 Vote Up -0Vote Down

    One of the MySQL 5.6 features many people are interested in is Global Transactions IDs (GTIDs). This is for a good reason: Reconnecting a slave to a new master has always been a challenge while it is so trivial when GTIDs are enabled. However, using GTIDs is not only about replacing good old binlog file/position with unique identifiers, it is also using a new replication protocol. And if you are not aware of it, it can bite.

    Replication protocols: old vs new

    The old protocol is pretty straightforward: the slave connects to a given binary log file at a specific offset, and the master sends all the transactions from there.

    The new protocol is slightly different: the slave first sends the range of GTIDs it has

      [Read more...]
    MySQL 5.6 + GTID & MariaDB 10 replication
    +1 Vote Up -0Vote Down

    While at the keynote of Tomas Ulin at Percona Live MySQL Conference & Expo Santa Clara 2014, he asked the audience what they were running, and most of the audience was on MySQL 5.5 while about 15% of the audience was on MySQL 5.6. This number is steadily increasing I’m sure, so one thing that becomes important is that people will probably start turning on Global Transaction Identifiers (GTIDs). 

    As you may already know, MariaDB 10 has a different implementation of Global Transaction ID. To me, this poses a problem in a mixed use environment (or even a migration scenario). Which is why

      [Read more...]
    Cloning a slave using Mysql Enterprise Backup on a GTID enabled server
    Employee +0 Vote Up -0Vote Down

    MySQL 5.6 introduced a new feature called GTID (Global Transaction IDentifier) support in Replication. For every transaction that is committed on to the server, a GTID of the format :

    server_uuid:transaction_id is written into the master's binary log.

    This offers the following advantages:

    • Very helpful to set up a slave and create a replication setup.

    • User need not worry about fetching the master's binlog filename and position in the “CHANGE MASTER TO” command which is used to synchronise the slave with the master.

    • Applying GTIDs on slaves ensures consistency – since GTIDs are unique, it cannot be applied more than once on the server.

    For a gtid enabled server, the following properties

      [Read more...]
    MariaDB 10.0.10 Overview and Highlights
    +2 Vote Up -0Vote Down

    MariaDB 10.0.10 was recently released, and is available for download here:


    This is the first GA ("Generally Availability", aka "recommended for production systems") release of MariaDB 10.0, and 11th overall release of MariaDB 10.0.

    Since this is the initial 10.0 GA release, this is primarily a bug-fix and polishing release.

    Here are the main items of note:

    read more

    MySQL 5.6 GTIDs: Evaluation and Online Migration
    +0 Vote Up -0Vote Down

    A colleague and I have been looking at GTID on MySQL recently and you may be interested in the blog post that results from that. You can see it here. http://blog.booking.com/mysql-5.6-gtids-evaluation-and-online-migration.html.


    How to Set Up Asynchronous Replication from Galera Cluster to Standalone MySQL server with GTID
    +1 Vote Up -0Vote Down
    March 3, 2014 By Severalnines

    Hybrid replication, i.e. combining Galera and asynchronous MySQL replication in the same setup, became much easier with MySQL 5.6 and GTID. Although it was fairly straightforward to replicate from a standalone MySQL server to a Galera Cluster, doing it the other way round (Galera → standalone MySQL) was a bit more challenging. At least until MySQL 5.6 and GTID. 

    There are a few good reasons to attach an asynchronous slave to a Galera Cluster. For one, long-running reporting/OLAP type queries on a Galera node might slow down an entire cluster, if the reporting load is so intensive that the node has to

      [Read more...]
    MySQL 5.6 GA one year – What is next?
    +3 Vote Up -0Vote Down

    MySQL 5.6 has been GA for just over a year now. See MySQL 5.6.10 Release Notes.  Congratulations on your birthday! That is quite a long time. I was using it earlier in production because it worked and could do things that 5.5 could not do, but earlier versions were to use at your own risk, and indeed if prodded incorrectly would fall on the floor. That is fair enough because they were work in progress, yet if you poked them the right way they did a very good job.  Those dev versions have been long since upgraded which is good so they do not need quite as much care and attention.

    So from where I see 5.6 it works very well. One big change that has made a large difference but which I think a lot of people may not really understand or use is the

      [Read more...]
    Fun with Bugs #19 - waiting for MySQL 5.6.13 and some real fun?
    +0 Vote Up -0Vote Down
    I feel like MySQL 5.6.12 was released ages ago, while in reality it was on June 3, less than 2 months ago. No wonder I feel so, after writing several posts about bugs fixed and not fixed in it... Anyway, we still have to wait for MySQL 5.6.13 for a week or even two probably and in the meantime I decided to write new post for this series based on good old idea of making a digest of my recent bugs-related posts at Facebook. I know, it's boring and annoying (same as waiting for the release of 5.6.13).

    Let's start with Bug #69846 - "ICP does not work on UNIQUE indexes". Based on my quick tests presented there I'd say that ICP (

      [Read more...]
    Changing an async slave of a PXC cluster to a new Master
    +1 Vote Up -0Vote Down
    Async and PXC

    A common question I get about Percona XtraDB Cluster is if you can mix it with asynchronous replication, and the answer is yes!  You can pick any node in your cluster and it can either be either a slave or a master just like any other regular MySQL standalone server (Just be sure to use log-slave-updates in both cases on the node in question!).  Consider this architecture:

    However, there are some caveats to be aware of.  If you slave from a cluster node, there is no built in mechanism to fail that slave over automatically to another master node in your cluster.  You cannot assume that the binary log positions are the same on all nodes in

      [Read more...]
    Replication in MySQL 5.6: GTIDs benefits and limitations – Part 2
    +2 Vote Up -0Vote Down

    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...]
    Replication in MySQL 5.6: GTIDs benefits and limitations – Part 1
    +2 Vote Up -0Vote Down

    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...]
    Introducing MySQL Utilities release-1.2.1
    Employee +4 Vote Up -0Vote Down
    The MySQL Utilities Team is pleased to announce our latest release, version 1.2.1. This release contains many quality improvements and enhancements to the HA and Replication utilities. The following lists some of the most significant improvements.
    • Improved transaction gathering algorithm for failover
      • Skips slaves that are already caught up
      • Ensures all transactions in the relay logs on the slaves are executed first
    • External scripts in mysqlfailover and mysqlrpladmin now receive the old and new master information
    • Improved demote master handling for switchover
    • Improved connection error handling
    • Quoting of tables and database names has been improved
    • Login-path feature now reads port and socket
    The utilities team continues to focus on improving usability, making features easier to use,
      [Read more...]
    Flexible Fail-over Policies Using MySQL and Global Transaction Identifiers
    Employee +1 Vote Up -0Vote Down
    << Previous Post: Advanced use of Global Transaction Identifiers

    As we saw in my previous posts, Global Transaction Identifiers in MySQL 5.6 allow you to change the replication topology arbitrarily. You don't even need to specify the positions in the replication stream – when you turn on auto-positioning, the master automatically sends only those transactions that are missing on the slave.

    When you do a fail-over, you want to make sure that the new master is up-to-date. For instance, consider the following scenario:

      [Read more...]
    Deep Dive into GTIDs and MySQL 5.6 - What, Why and How
    Employee_Team +6 Vote Up -0Vote Down

    Global Transaction Identifiers (GTIDs) are one of the key replication enhancements in MySQL 5.6. GTIDs make it simple to track and compare replication across a master - slave topology. This enables:

    - Much simpler recovery from failures of the master,

    - Introduces great flexibility in the provisioning and on-going management of multi-tier or ring (circular) replication topologies.

    A new on-demand MySQL 5.6 GTID webinar (http://www.mysql.com/news-and-events/web-seminars/mysql-replication-simplifying-scaling-and-ha-with-gtids/) delivered by the replication engineering team is now available, providing deep insight into the design and implementation of GTIDs, and how they enable users to simplify MySQL scaling and HA. The webinar covers:

    - Concepts: What is

      [Read more...]
    Easily testing MySQL 5.6 GTID in a sandbox
    +6 Vote Up -0Vote Down

    MySQL 5.6 seems to be ready for GA. I have no inside information about it, but from some clues collected in various places I feel that the release should not be far away. Thus, it's time for some serious testing, and for that purpose I have worked at updating MySQL Sandbox with some urgent features.

    I have just released MySQL Sandbox 3.0.28, with more support for MySQL 5.6. Notably in this release, there is suppression of MySQL 5.6 annoying verbosity, additional suppression of more annoying warnings ( actually a bug) when using empty passwords

      [Read more...]
    Round Robin Replication using GTID
    Employee +2 Vote Up -0Vote Down
    In a previous post I showed how to implement multi-source round-robin replication in pure SQL using the tables that are needed for crash-safe replication. I also outlined a revised version of this approach in the Replication Tips & Tricks presentation I gave at MySQL Connect. This was, however, before the GTID (Global Transaction ID) implementation was done. Now that they are introduced, multi-source replication is even easier since you no longer have to keep track of the positions.

    Figure 1. Tables for storing information about masters
    CREATE TABLE my_masters (
        host CHAR(50) NOT NULL,

      [Read more...]
    MySQL 5.6, GTID and performance_schema
    +1 Vote Up -0Vote Down

    Not much to add really to the bug I’ve filed here: bug#67159.

    Again this GTID stuff looks good, but seems to prevent changes in the configuration of performance_schema, which I think is not appropriate, especially as P_S now has lots of extra goodies and after 5.6 will surely have even more.

    MySQL-5.6, GTID and binlogs on slaves
    +2 Vote Up -0Vote Down

    Not much to add really to the bug I’ve filed here: bug#67099.

    I personally can think of some very nasty consequences of applying this on the slaves I manage, and the reason I’m posting the bug is that while I guess this is too late to fix in 5.6 as it’s effectively a new feature, I’m sure many sites may bump into this and be somewhat disappointed if they want to use the new GTID feature and have several slaves.  Hence, if the fix/feature has to go into MySQL 5.7 then I hope it goes in sooner rather than later. We will see.

    Updated: 2013-09-19

    I probably should have updated this earlier but it does seem that Oracle have taken these comments on board. See: WL6559.  It looks like they

      [Read more...]
    More GTID troubles in MySQL 5.6
    +1 Vote Up -0Vote Down

    A colleague, Kris, commented recently on a recent find I made on 5.6.7-rc1 which while not the end of the world does seem to severely limit the use of the new GTID features, or if nothing else make it a pain to use.

    Today I found another related issue, when trying to check the syntax of one of the MySQL commands. That’s now reported as bug#67073.

    All of these are sort of edge cases but do make 5.6 look a little rough round the edges, when the rest of the product is actually really good and a great improvement from The MySQL 5.5 GA version that everyone uses.  That’s really a shame.

    I did report the Original problem as SR 3-6270525721 : MySQL 5.6.7-rc1, grants, replication and

      [Read more...]
    MySQL 5.6 Replication: FAQ
    Employee_Team +2 Vote Up -0Vote Down
    0 0 1 1207 6881 Homework 57 16 8072 14.0 Normal 0 false false false EN-GB JA X-NONE

    On Wednesday May 16th, we ran a webinar to provide an overview of all of the new replication features and enhancements that are previewed in the MySQL 5.6 Development Release – including Global Transaction IDs, auto-failover

      [Read more...]
    Meet the MySQL Experts Podcast: MySQL Replication Global Transaction Identifiers & HA Utilities
    Employee_Team +3 Vote Up -0Vote Down
    0 0 1 223 1273 Homework 10 2 1494 14.0 Normal 0 false false false EN-US JA X-NONE

    In the latest episode of our “Meet The MySQL Experts” podcast, Luis Soares, Engineering Manager of MySQL

      [Read more...]
    Initial Reactions to MySQL 5.6
    +3 Vote Up -0Vote Down

    New versions of MySQL are always interesting to try out. Often they have features which I may have asked for myself so it’s satisfying to see them eventually appear on a system I use. Often other new features make life easier for the DBA. Finally we hope overall performance will improve and managing the server(s) will be come easier.

    So I had a system which needs to make heavy writes, and performance was a problem, even when writing to SSDs. Checkpointing seemed to be the big issue and the ib_logfile size in MySQL 5.5 is limited to 4 GB. That seems a lot, but once MySQL starts to fill these files (and this happens at ~70% of the total I believe),  checkpointing kicks in heavily, and slows things down.  So the main reason for trying out MySQL 5.6 was to see how things performed with larger ib_logfiles. (Yes, MariaDB 5.5 can do this too.)

    Things improved a lot for my

      [Read more...]
    New MySQL 5.6 Replication Utilities – mysqlfailover and mysqlrpladmin
    +3 Vote Up -0Vote Down

    With all of the new news coming out right now, it can be easy to miss or overlook some of the new features.

    While there’s been a lot of talk about MySQL 5.6 Replication, I specifically wanted to mention the new ‘mysqlfailover’ and ‘mysqlrpladmin’ utilities.

    These are two new MySQL replication utilities (results of the new Global Transaction Identifiers (GTIDs) in MySQL 5.6).

    Let me quote the MySQL 5.6 Replication article for both of these utilities:


    “Provides continuous monitoring of the replication topology, enabling failover to a slave in the event of an outage on the master.

    The default behavior is to

      [Read more...]
    MySQL Utilities and Global Transaction Identifiers
    Employee +7 Vote Up -0Vote Down
    The new MySQL 5.6 Development Milestone Release (DMR) includes many new enhancements. One of the most impressive is the use of Global Transaction Identifiers (GTIDs) for replication. With GTIDs enabled, administrators no longer need to keep track of binary log files and positions. In a nutshell, GTIDs simplify the setup and maintenance of replication.

    MySQL Utilities has taken this a step further by providing two new utilities that automate two of the most complex replication administration tasks - switchover and failover. Switchover is changing the role of an active, healthy master to one of its slaves whereas failover is the act of promoting a candidate slave to become the new master. Clearly, switchover is an elective operation and failover is performed when there are issues with the master.

    The GTID utilities are included in release-1.0.5 of

      [Read more...]
    Showing entries 1 to 28

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.