MySQL has introduced the PERFORMANCE_SCHEMA (P_S) in MySQL 5.5 and made it really usable in MySQL 5.6 and added some enhancements in MySQL 5.7 and 8.0.

Unfortunately the PERFORMANCE_SCHEMA was not really intuitive for the broader audience. Thus Mark Leith created the sys Schema for an easier access for the normal DBA and DevOps and Daniel Fischer has enhanced it further. Fortunately the sys Schema up to version 1.5.1 is available on GitHub. So we can adapt and use it for MariaDB as well. The version of the sys Schema in MySQL 8.0 is 1.6.0 and seems not to be on GitHub yet. But you can extract it from the MySQL 8.0 directory structure: …

Overview

I find far too often that MySQL error and slow query logs are unaccounted for.  Setting up log rotation helps make the logs manageable in the event that they start to fill up and can help make your troubleshooting of issues more efficient.

Setup

All steps in the examples below are run as the root user. The first step is to setup a user that will perform the log rotation.  It is recommended to only give enough access to the MySQL user for the task that it is performing.

Create Log Rotate MySQL User

mysql > CREATE USER 'log_rotate'@'localhost' IDENTIFIED BY '<ENTER PASSWORD HERE>';
mysql > GRANT RELOAD,SUPER ON *.* to 'log_rotate'@'localhost';
mysql > FLUSH PRIVILEGES;</pre>

The next step is to setup the MySQL authentication config as root.  Here are two methods to set this up.  The first method will be the more secure method of …

In the following we show how InnoDB cluster can be deployed in a container context. In the official documentation (Introducing InnoDB Cluster), InnoDB is described as: MySQL InnoDB cluster provides a complete high availability solution for MySQL. MySQL Shell includes AdminAPI which enables you to easily configure and administer a group of at least three […]

In this blog post, we’ll look at how Percona XtraDB Cluster (PXC) executes FLUSH and LOCK handling.

Introduction

Percona XtraDB Cluster is a multi-master solution that allows parallel execution of the transactions on multiple nodes at the same point in time. Given this semantics, it is important to understand how Percona XtraDB Cluster executes statements regarding FLUSH and LOCK handling (that operate at node level).

The section below enlist different flavors of these statements and their PXC semantics

FLUSH TABLE WITH READ LOCK

• FTWRL is normally used for backup purposes.
• Execution of this command establishes a global level …

In the Pythian blog today, John Schulz writes The Case Against Auto Increment In MySQL, but his blog contains some misunderstandings about MySQL, and makes some bad conclusions.

The Concerns are Based on Bad Assumptions

In his blog, Schulz describes several concerns about using auto-increment primary keys.

Primary Key Access

"...when access is made by a secondary index, first the secondary

Introduction

In my travels to visit many customers over the last few years, I often see my customers creating many or all of their MySQL InnoDB tables using auto-increment primary keys. Many Object Relational Mappers do this by default on behalf of the user. Once the tables are all created with auto increment primary keys, then the database designer/developer goes about assigning alternate keys that they will actually use to access the data. Most of the time the auto-increment key is simply there to ensure that there is a unique key on the table and it’s often not used as an access path. This is a common design pattern, but is it the best way to create tables using MySQL? I am witting this blog to present the case that it’s a pretty bad idea most of the time.

Why it’s a bad Idea

There are four reasons why, which I will explain in some depth later in the post but for now, they are:

• Auto increment keys …

In this blog post, We will be explaining about the timeouts in SST on systemd implementation which we faced recently in Percona XtraDB Cluster  during our Consulting with a client. State Snapshot Transfers (SST) refers to complete data sync from one of the nodes from the cluster to the joining node.

SST will happen for one or more reasons listed below.

1. Initial sync to join a node to cluster.
2. Node is out of cluster and lost its ability to join back due to data corruption or inconsistencies and also when the node went far behind the node, …

In this blog post, we’ll see how to use different mount points on PMM Docker deployments (Percona Monitoring and Management). This is useful if you want to use other mount points for the different directories, or even if you want to use a custom path that is not bound to Docker’s volumes directory (which is /var/lib/docker/volumes/ by default) within the same mount point.

There are two ways in which you can achieve this:

• using symlinks after the pmm-data container is created
• modifying the docker create command to use …

Recently I blogged about the new default authentication plugin in MySQL 8.0 and I got some comments complaining that this new authentication plugin is breaking half of applications.

So first of all, if you are using an old connector or a connector (like the one for Go) not yet supporting caching_sha2_passwordas authentication plugin, you are still able to use the old one. If you have created a new user for your application not supporting the new authentication method, you just have to run the …

Please join Percona’s Ramesh Sivaraman (QA Engineer) and Krunal Bauskar (Software Engineer, Percona XtraDB Cluster Lead) as they present Percona XtraDB Cluster 5.7 with ProxySQL for Your MySQL High Availability and Clustering Needs on Thursday, March 22, 2018 at 8:30 am PDT (UTC-7) / 11:30 am EDT (UTC-4).

Percona has developed Percona XtraDB Cluster (based on Galera Cluster) and integrated it with ProxySQL to address MySQL high availability and …