We saw yesterday that the new MySQL Shell was out and how we could create a MySQL InnoDB Cluster manually using the Shell.

Today, I would like to show you how easy it is to create recipes to automate all the process. I have created a Puppet module that can be used as Proof-of-concept (You might need more features to use it in production, feel free to fork it).

The module can be found on this github repo.

When using Puppet, I really like to put all configuration in hiera.

Environment

We have 3 GNU/Linux servers: mysql1, mysql2 and mysql3.

We won’t install anything related to MySQL manually, everything will be handled by Puppet.

Nodes definition …

 Earlier this week,  MySQL Shell 1.08 has been released. This is the first Release Candidate of this major piece of MySQL InnoDB Cluster.

Some commands have been changed and some new ones were added.

For example the following useful commands were added:

• dba.checkInstanceConfiguration()
• cluster.checkInstanceState()
• dba.rebootClusterFromCompleteOutage()

So let’s have a look on how to use the new MySQL Shell to create a MySQL InnoDB Cluster.

Action Plan

We have 3 blank Linux servers: mysql1, mysql2 and mysql3 all running rpm based Linux version 7 (Oracle Linux 7, CentOS 7, …).

We will install the required MySQL yum repositories and install the needed packages …

Running out of disk space is something which, of course, should never happen as we all setup monitoring and alerting and only run well behaved applications. But when it does happen we want things to fail gracefully.

So what happens when mysqld runs out of disk space?
The answer is: It depends

1. It might start to wait until disk space becomes available.
2. It might crash intentionally after a 'long semaphore wait'
3. It might return an error to the client (e.g. 'table full')
4. It might skip writing to the binlog (see binlog_error_action )

What actually happens might depend on the filesystem and OS.

Fixing the disk space issue can be done by adding more space or cleaning up some space. The later can often be …

Percona announces the GA release of Percona Server for MySQL 5.7.17-11 on February 3, 2017. Download the latest version from the Percona web site or the Percona Software Repositories.

Based on MySQL 5.7.17, including all the bug fixes in it, Percona Server for MySQL 5.7.17-11 is the current GA release in the Percona Server for MySQL 5.7 series. Percona’s provides completely open-source and free software. Find release details in the …

In my previous post, we saw the usage of MySQL Group Replication (MGR) in single-primary mode. We know that Oracle does not recommends using MGR in multi-primary mode, but there is so much in the documentation and in presentations about MGR behavior in multi-primary, that I feel I should really give it a try, and especially compare this technology with the already existing multiple master solution introduced in 5.7: multi-source replication.

Installation

To this extent, I will set up two clusters using MySQL-Sandbox. The instructions for MGR in …

Overview

MySQL Group Replication was released as GA with MySQL 5.7.17. It is essentially a plugin that, when enabled, allows users to set replication with this new way.

There has been some confusion about the stability and usability of this release. Until recently, MySQL Group Replication (MGR) was only available in the Labs, which traditionally denotes a preview or an use-at-your-own-risk feature. Several months ago we saw the release of Group Replication as a Docker image, which allowed users to deploy a peer-to-peer cluster (every node is a master.) However, about one month after such release, word came from Oracle discouraging this setup, and inviting users to use Group Replicator in …

In this blog post we’ll look at how to move a MySQL partition from one table to another, for MySQL versions before 5.7.

Up to version 5.7, MySQL had a limitation that made it impossible to directly exchange partitions between partitioned tables. Now and then, we get questions about how to import an .ibd for use as a partition in a table, as well as how to exchange partitions with another partitioned table. Below is step-by-step instructions on how to move a partition from one table to another.

In this example, one of our customers had two tables with the following structures:

CREATE TABLE live_tbl (
some_id bigint(20) NOT NULL DEFAULT '0',
summary_date date NOT NULL,
PRIMARY KEY (some_id,summary_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE …

Today’s blog post is related again to MySQL Group Replication.

By default MySQL Group Replication runs in Single-Primary mode. And it’s the best option and the option you should use.

But sometimes it might happen that in very specific cases you would like to run you MGR Cluster in Multi-Primary mode: writing simultaneously on all the nodes member of the Group.

It’s of course feasible but you need to make some extra verification as not all workload are compatible with this behavior of the cluster.

Requirements

The requirements are the same as those for using MGR in Single-Primary mode:

• InnoDB Storage Engine
• Primary Keys
• IPv4 Network
• Binary Log Active
• Slave Updates Logged
• Binary …

In a previous article, Ovais demonstrated how a DDL can render a table blocked from new queries. In another article, Valerii introduced performance_schema.metadata_locks, which is available in MySQL 5.7 and exposes metadata lock details. Given this information, here’s a quick way to troubleshoot metadata locks by creating a stored procedure that can:

• Find out which thread(s) have the metadata lock
• Determine which thread has been waiting for it the longest
• Find other threads waiting for the metadata lock

Setting up …

Today in our series of articles related to MySQL Group Replication’s limitations, let’s have a quick look at Savepoints.

The manual is clear about this: Transaction savepoints are not supported.

The first thing to check then is if the application that will use our MySQL Group Replication Cluster is currently using savepoints.

We have two ways to find this, the first is using STATUS variables:

mysql> show global status like '%save%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Com_release_savepoint      | 2     |
| Com_rollback_to_savepoint  | 0     |
| Com_savepoint              | 4     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
+----------------------------+-------+

…