GTID Replication (Skip Transaction using empty transaction) To skip SQL thread's error in GTID replication setup, Insert empty transaction. PROBLEM :  db-test (none)> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxxxx
                  Master_User: rep_user
                  Master_Port: 3306
        …

MySQL 8.0.16 has been released last Thursday. In it, you can find some new replication features. Here is a quick summary. Follow-up blog posts will provide details about these features.

• Large Messages Fragmentation Layer for Group Replication. Tiago Vale’s work, introduces message fragmentation to the Group Communication Framework.

… Tweet Google Plus Share

So over the years, we all learn more and more about what we like and use often in MySQL. 

Currently, I step in and out of a robust about of different systems. I love it being able to see how different companies use MySQL.  I also see several aspect and settings that often get missed. So here are a few things I think should always be set and they not impact your MySQL database. 

At a high level:

• >Move the Slow log to a table 
• Set report_host_name 
• Set master & slaves to use tables
• Turn off log_queries_not_using_indexes until needed 
• Side note -- USE  ALGORITHM=INPLACE
• Side note -- USE mysql_config_editor
• Side note -- USE  mysql_upgrade  --upgrade-system-tables

Move the Slow log to a …

Just about a month ago, Pavel Ivanov released Ripple under the Apache-2.0 license. Ripple is a MySQL binlog server: software which receives binary logs from MySQL or MariaDB servers and delivers them to another MySQL or MariaDB server. Practically ,this is an intermediary master which does not store any data, except the binary logs themselves, and does not apply events. This solution allows saving of a lot of resources on the server, which acts only as a middle-man between the master and its actual slave(s).

The intermediary server, keeping binary logs only and not doing any other job, is a prevalent use case which allows us to remove IO (binlog read) and network (binlog retrieval via network) load from the actual …

Recently, we had an edge case where a MySQL slave went out-of-sync but it couldn’t be rebuilt from scratch. The slave was acting as a master server to some applications and it had data was being written to it. It was a design error, and this is not recommended, but it happened. So how do you synchronize the data in this circumstance? This blog post describes the steps taken to recover from this situation. The tools used to recover the slave were pt-slave-restart, pt-table-checksum, pt-table-sync and mysqldiff.

Scenario

To illustrate this …

On previous posts about Group Replication consistency we:

1. introduced consistency levels;
2. explained how to configure the primary failover consistency;
3. presented how to configure transaction consistency levels to achieve the consistency required by your applications.

In blog 3. we presented the consistency levels: EVENTUAL, BEFORE, AFTER and BEFORE_AND_AFTER; their scopes: SESSION, GLOBAL; and their context: whether they only impact the ongoing transaction or all concurrent transactions.…

Tweet Google …

This was not on the radar. I have never been proficient in Galera clusters and related technologies, and thus I hadn’t given much thought to Percona Xtradb Cluster (PXC), until Alkin approached me at FOSDEM, and proposed to extend dbdeployer features to support PXC. He mentioned that many support engineers at Percona use dbdeployer) on a daily basis and that the addition of PXC would be welcome.

I could not follow up …

• Replication used to replicate data from the Master node to a slave node[Replica].
• By default Replication is asynchronous.
• It uses binary logs for reading data from the Master node and relay log on slave[Replica].
• Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Replication Methods:

• File-based Replication:

The traditional method is based on replicating events from the master’s binary log, and requires the log files and positions in them to be synchronized between master and slave.

• GTID Replication:

The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within …

Some months ago, Shlomi Noach published a series about Service Discovery.  In his posts, Shlomi describes many ways for an application to find the master.  He also gives detail on how these solutions cope with failing-over to a slave, including their integration with Orchestrator.

This is a great series, and I recommend its reading for everybody implementing master failover, with or without

Recently we had encountered a strange issue with replication and temp directory(tmpdir) change while working for one major client.

All the servers under this were running with Percona flavor of MySQL versioned 5.6.38 hosted on a Debian 8(Jessie)

The MySQL architecture setup  is as follows one master with 5 direct slaves under it

Through this blog, we will take you through the issue we had faced and how we fixed ultimately.

Client Request:

There was a simple request from our client to add a column and index to a 16GB production table since the table had foreign keys, to avoid complications we decided to go with online DDL instead of pt-online-schema.

When we started to alter, it got failed due to insufficient space in “tmpdir”.MySQL by default would be using “/tmp” for temp table creating, sorting and other temp operation, Since we had only 5.7G left on the “/” …