In this blog post we are going to take a closer look at ProxySQL rules. How do they work, and how big is the performance impact of having many rules?

I would like to say thank you to Renè, who was willing to answer all my questions during my tests.

Overview

ProxySQL is heavily based on the query rules. We can set up ProxySQL without rules based only on the host groups, but if we want read/write splitting or sharding (or anything else) we need rules.

ProxySQL knows the SQL protocol and language, so we can easily create rules based on username, schema name and even on the query itself. We can write regular expressions that match the query digest. Let me show you an example:

insert into mysql_query_rules …

If you met one of the (few) MySQL consultants around the globe and asked him/her to review your queries and/or schemas, I am sure that he/she would tell you something regarding the importance of good primary key(s) design. Especially in the case of InnoDB, I’m sure they started to explain to you about index merges and page splits. These two notions are closely related to performance, and you should take this relationship into consideration when designing any index (not just PKs).

That may sound like mumbo jumbo to you, and you may be right. This is not easy stuff, especially when talking about internals. This is not something you deal with on a regular basis, and often you don’t want to deal with it at all.

But sometimes it’s a necessity. If so, this article is for you.

In this article, I want to shed some light in explaining some of the most unclear, behind the scenes operations in InnoDB: page index creation, …

If you met one of the (few) MySQL consultants around the globe and asked him/her to review your queries and/or schemas, I am sure that he/she would tell you something regarding the importance of good primary key(s) design. Especially in the case of InnoDB, I’m sure they started to explain to you about index merges and page splits. These two notions are closely related to performance, and you should take this relationship into consideration when designing any index (not just PKs).

That may sound like mumbo jumbo to you, and you may be right. This is not easy stuff, especially when talking about internals. This is not something you deal with on a regular basis, and often you don’t want to deal with it at all.

But sometimes it’s a necessity. If so, this article is for you.

In this article, I want to shed some light in explaining some of the most unclear, behind the scenes operations in InnoDB: page index …

Error messageError 1045 (28000): Access denied for user ‘user’@’host’ (using password: YES)Debugging Summary

• Check for typo error: username or password. 
• Check the host name and compare it with mysql.user table host name. 
• Check user exists or not. 
• Check whether host contains IP address or host name.

There is a great chance that, you might have encountered this issue multiple times in your work. This issue occurred to me most of times due to the incorrectly entering user name or password. Though this is one of the reasons, there are other many chances you might get this issue. Sometimes, it looks very similar, but when you dig deeper, you will realize multiple factors contributing to this error. This post will explain in detail, most of the common reasons and work around to fix this issue. 

Possible reasons:

• Case 1: …

In this blog, we’ll look at how to resolve MySQL error code 1215: “Cannot add foreign key constraint”.

Our Support customers often come to us with things like “My database deployment fails with error 1215”, “Am trying to create a foreign key and can’t get it working” or “Why am I unable to create a constraint?” To be honest, the error message doesn’t help much. You just get the following line:

ERROR 1215 (HY000): Cannot add foreign key constraint

But MySQL never tells you exactly WHY it failed. There’s actually a multitude of reasons this can happen. This blog post is a compendium of the most common reasons why you can get ERROR 1215, how to diagnose your case to find which one is affecting you and potential solutions for adding the foreign key.

Overview

In this post I will explain how to transfer a partitioned table using transportable tablespaces on MySQL 5.6. Note that the operation is not officially supported until MySQL 5.7, as the discard tablespace operation will fail if it is run against a partitioned table on MySQL 5.6.

The workaround is transferring each individual partition as if it was a standalone table. Continue reading to learn more about the detailed procedure.

Steps

1. Create the same table structure on the destination server:

[root@mysqlsandbox ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35-80.0 Percona Server (GPL), Release 80.0, Revision f113994f31

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. …

In honor of the upcoming MariaDB M17 conference in New York City on April 11-12, we have enhanced Percona Monitoring and Management (PMM) Metrics Monitor with a new MariaDB Dashboard and multiple new graphs!

The Percona Monitoring and Management MariaDB Dashboard builds on the efforts of the MariaDB development team to instrument the Aria Storage Engine Status Variables related to Aria Pagecache and Aria Transaction Log activity, the tracking of Index Condition Pushdown (ICP), InnoDB Online DDL when using ALTER TABLE ... ALGORITHM=INPLACE, InnoDB Deadlocks …

Introduction

Recently I have been involved in an effort to convert MySQL databases from a utf8 character set to utf8mb4. As a part of this effort, my team evaluated which collations would be best for facilitating a broad range of multi-lingual support.

There have been many recent posts in the MySQL community about better unicode collation support in MySQL 8 such as from the MySQL Server Team’s blog at Oracle, who have also done a good job of showing us how newer collations based on UTF8 9.0.0 will properly group and sort characters according to their case and inheritance. As the title of the latter post suggests, the “devil is” indeed “in the details”.

There is also the matter of the …

Following are the few best practices and basic commands for MySQL Administration.

MySQL Access and credential security

shell> mysql -u testuser -pMyP@ss0rd
mysql: [Warning] Using a password on the command line interface can be insecure.

By looking at OS cmd’s history using history cmd other os users can see/get MySQL user password easily. It always good to not use a password on the command line interface. Another option for securing password while automating MySQL scripts is a use of mysql_config_editor. For more info on this check out my blog post about credential security.

Consider of having following implementation for Strong access policy.

• use of  validate_password plugin for a strong password policy.
• Limit …

In this blog post, we’ll look at the performance of SST data transfer using encryption.

In my previous post, we reviewed SST data transfer in an unsecured environment. Now let’s take a closer look at a setup with encrypted network connections between the donor and joiner nodes.

The base setup is the same as the previous time:

• Database server: Percona XtraDB Cluster 5.7 on donor node
• Database: sysbench database – 100 tables 4M rows each (total ~122GB)
• Network: donor/joiner hosts are connected with dedicated 10Gbit LAN
• Hardware: donor/joiner hosts – boxes with 28 Cores+HT/RAM 256GB/Samsung SSD 850/Ubuntu 16.04

The setup details for the encryption aspects in our testing:

• Cryptography libraries: openssl-1.0.2, …