There are several possibilities to end up with inconsistent MySQL replication, This could be accidental or intentional. In this blog I would like to discuss on how to identify the inconsistent slaves with master and fix them. I used here pt-table-checksum (to find the difference between master and slave) and pt-table-sync (to sync. between MySQL master and slave) from Percona Toolkit, The detailed documentation of Percona toolkit is available here for your reference. I expect / recommend you to be careful (as I mentioned above, sometimes records are inserted / deleted on MySQL slave intentionally) before using pt-table-checksum to sync. slave with master because rollbacking this task is even more expensive. The objective of this blog is to show you how to find differences between master and …[Read more]
2 Older Entries »
For second day in a row I am trying to concentrate on writing a
second issue for my new series of blog posts, but I can not
complete. It seems half of it is going to be devoted to three
tools from Percona Toolkit that I had to remember and explain
this week: pt-table-checksum, pt-table-sync and pt-slave-restart. So, I decided to write a
separate post on this topic.
The context was simple: in the middle of (the weekend) night customer comes with broken replication issue, without a fresh backup from master or with few gigabytes …
In the first post, I showed you how to use the
tools in a typical MySQL replication setup. In this post, we’ll discuss MySQL replication for more advanced topologies. I will show you how to use these tools in a chained …[Read more]
This blog post explores the syncing options for an inconsistent MySQL slave of Galera cluster node using pt-table-sync
Checksum is a standard practice among DBAs to verify the data consistency across replicated nodes. In this post we’re going to review the syncing options for an inconsistent MySQL slave of Galera cluster node.
Here we’re assuming a setup of regular replication to a MySQL
instance from one of the Galera cluster nodes.
In the usual MySQL replication setup, standard practice involves the usage of the pt-table-checksum tool to identify the discrepancies and usage of pt-table-sync to bring them in sync. The checksum tool, pt-table-checksum, can run across Galera cluster node to verify the data consistency and confirm if the MySQL slave is consistent with a chosen primary node.
What happens if this Galera cluster’s regular MySQL slave sees
data inconsistency on it? Will pt-table-sync work there? The
answer to this depends…
pt-table-sync when used with –sync-to-master …
MySQL replication is a process that allows you to easily maintain multiple copies of MySQL data by having them copied automatically from a master to a slave database.
It’s essential to make sure the slave servers have the same set of data as the master to ensure data is consistent within the replication stream. MySQL slave server data can drift from the master for many reasons – e.g. replication errors, accidental direct updates on slave, etc.
Here at Percona Support we highly recommend that our customers periodically run the pt-table-checksum tool to verify data consistency within replication streams. Specifically, after fixing replication errors on slave servers to ensure that the slave has identical data as its master. As you don’t want to put …[Read more]
Data inconsistencies in replication environments are a pretty common. There are lots of posts that explain how to fix those using pt-table-checksum and pt-table-sync. Usually we only care about the data but from time to time we receive this question in support:
How can I check the table definition consistency between servers?
Replication also allow us to have different table definition between master and slaves. For example, there are some cases that you need some indexes on slaves for querying purposes but are not really needed on the master. There are some other cases where those differences are just a mistake that needs to be fixed.
MySQL Replication is very simple to set up. In this post I’ll discuss its importance and five handy tools for monitoring and managing MySQL replication.
What is MySQL Replication? It’s the process of copying the (real-time events) data from one master instance to another slave instance and maintaining the redundant consistent data in a different machine. This enables a distributed database system that shares the same level of information.
In MySQL the replication works based on the three threads as shown below.
1) I/O thread on the slave server: To start on receiving replication events, an I/O thread starts on the slave server and connects to the master server.
2) Master connection handler thread: As a connection handier, master starts a thread whenever a replication slave connects to a master. The master server sends the events from its binary log file to the slave I/O thread, notifying slave …[Read more]
On a recent consulting engagement, the PSCE team were charged with what can be considered a fairly common task of synchronising tables between master and slave in MySQL Replication. On this occasion the schema contained both foreign key constraints and triggers, this post describes how we avoided the potential problems related to such an operation.
The process to synchronise tables in MySQL is to first identify the differences between tables and then execute queries which bring those tables into a consistent state. The first part of the process can be handled by the pt-table-checksum tool, which steps through the table analysing sets of rows (chunks) and recording a checksum value. Then taking advantage of replication, the same process occurs on each of the slaves and the checksums can then be compared. Once the entire table has been processed, a second tool pt-table-sync can be used …[Read more]
Percona Toolkit’s pt-table-checksum is a great tool to find data inconsistencies between a MySQL master and its replicas. However it is sometimes not enough to know that there are inconsistencies and let pt-table-sync fix the issue: you may want to know which exact rows are different to identify the statements that created the inconsistency. This post shows one way to achieve that goal.
Let’s assume you have 2 servers running MySQL 5.5: db1 the master and db2 the replica. You want to upgrade to MySQL 5.6 using an in-place upgrade and to play safe, you will upgrade db2 (the slave) first. If all goes well you will promote it and upgrade db1.
A good thing to do after upgrading db2 is to check for potential data …[Read more]
2 Older Entries »