Upgrading to MySQL 5.6, benefits to switching to GTID replication?

Posted on

Question :

We have the most basic of MySQL replication setups, a single Master doing statement-based replication to a single Slave, a 90/10 mix of innodb/myisam tables. Both run Percona MySQL 5.5 at the moment but we are upgrading to 5.6 in a couple weeks and have scheduled downtime.

Will this kind of setup see much benefit by switching to GTID replication? Do I need to switch to row-based replication as well?

Trying to decide if it is worth the extra downtime for converting to and testing GTID right now.

Answer :

I would say that you benefit from future proofing your environment, because inevitably you will want to add more slaves.

I also don’t like having to remember specific coordinates of what statements have applied/haven’t when doing a restore. Having the server know the current set of applied statements is very useful to prevent duplicate processing/rogue corruption. In 5.7 mysqlbinlog even has a --idempotent mode.

Operationally GTIDs are much easier to manage, and are the future 🙂

I recommend row-based replication as well – I think it’s a great option:

The only biggest benifit of switching to Mysql 5.6.5< is due to the feature that supports automatic failover utility which is default in Mysql 5.9.1 in a Mysql replication topology, this requires GTID level replication. Unless or until you don’t really need an automatic failover within Mysql itself, we can work with the legacy features of Mysql 5.5 which is recognized most.

But also I would like to highlight some cons of using GTID:
A nontransactional storage engine (Myisam) mixed with updates to tables that use a transactional storage engine (Innodb) within the same transaction can result in multiple GTIDs being assigned to the same transaction. Thish might break sync between Master/Slave replication.

In a nut shell:
If your environment is stable in performance, High-availability and Security in your existing version it is better to keep untouched. If there is a necessity in any of the above then go for it. Management audit says a lot to keep latest version according to standards but they don’t see technology wise what is standard? For this we need not spend time and effort to upgrade and sustain the same setup when it is feasible and effecient in current versions.

Leave a Reply

Your email address will not be published. Required fields are marked *