Performing regular backups of your database cluster is imperative for high availability and disaster recovery. If for any reason you lost your entire cluster and had to do a full restore from backup, you would need a reliable and up-to-date backup to start from.

Best Practices for Backups

Some recommendations to consider for a good scheduled backup regime:

• You should be able to completely recover from a catastrophic failure from at least two previous full backups. Just in case the most recent full backup is damaged, lost, or corrupt,
• Your backup should contain at least one full backup within a chosen cycle, normally weekly,
• Store backups away from the current data location, preferably off site,
• Use a mixture of mysqldump and Xtrabackup for extra safety, and not rely on one method,
• Test restore your backups on a regular basis, e.g. every two months.

The MySQL Development team is happy to announce our 8.0.1 development milestone release (DMR), now available for download at dev.mysql.com. (8.0.1 adds features to 8.0.0). The source code is available at GitHub. You can find the full list of changes and bug fixes in the 8.0.1 Release Notes.…

MySQL Server 5.7.18, 5.6.36, and 5.5.55, new versions of the popular Open Source Database Management System, have been released. These releases are recommended for use on production systems. For an overview of what’s new, please see http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html For information on installing the release on new servers, please see the MySQL installation documentation at […]

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 …

In MySQL 5.7, one of the most popular new features is the introduction of JSON:

1. The JSON data type
2. A set of 20 functions to search, manipulate and create JSON documents
3. Virtual Columns for JSON indexing

In MySQL 8.0 we plan to extend this functionality (for example: with the addition of JSON aggregate functions), but we are also considering changing the behaviour of one of the existing functions.…

In this article, I will show you how to configure a MySQL database cluster with three nodes in a multi-master replication. Multi-master replication allows writing of records in each node, so if a node will fail, we can work on the other as if nothing happened. This tutorial describes how to install and configure three Percona XtraDB Cluster nodes on Debian 8 servers, we will be using the packages from the Percona repositories.

Unlike standard MySQL server and MySQL Cluster, the way to start a MySQL/MariaDB Galera Cluster is a bit different. Galera requires you to start a node in a cluster as a reference point, before the remaining nodes are able to join and form the cluster. This process is known as cluster bootstrap. Bootstrapping is an initial step to introduce a database node as primary component, before others see it as a reference point to sync up data.

How does it work?

When Galera starts with the bootstrap command on a node, that particular node will reach Primary state (check the value of wsrep_cluster_status). The remaining nodes will just require a normal start command and they will automatically look for existing Primary Component (PC) in the cluster and join to form a cluster. Data synchronization then happens through either incremental state transfer (IST) or snapshot state transfer (SST) between the joiner and the donor.

So …

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 queries in systems with parallel processing can return rows in a non-deterministic order (and how to fix it).

Short story:

Do not rely on the order of your rows if your query does not use

ORDER BY

. Even with

ORDER BY

, rows with the same values can be sorted differently. To fix this issue, always add

ORDER BY ... ID

 when you have

LIMIT N

.

Long story:

While playing with MariaDB ColumnStore and Yandex ClickHouse, I came across a very simple case. In MariaDB ColumnStore and …