The latest from the MySQL community

In the MySQL world, EXPLAIN is a keyword used to gain information about query execution. This blog post will demonstrate how to utilize MySQL EXPLAIN to remedy problematic queries.

On the Technical Solutions team here at PlanetScale, we frequently talk with users who seek advice regarding query performance. Although creating an EXPLAIN plan is relatively simple, the output isn’t exactly intuitive. It’s essential to understand its features and how to leverage it best to achieve performance goals.

EXPLAIN vs. EXPLAIN ANALYZE

When you prepend the EXPLAIN keyword to the beginning of a query, it explains how the database executes that query and the estimated costs. By leveraging this internal MySQL tool, you can observe the following: …

The End-of-Life (EOL) date for MySQL 5.7 is scheduled for October 2023, which means that after that date, MySQL 5.7 will no longer receive updates, bug fixes, or security patches. This does not mean that MySQL 5.7 will stop working after the EOL date, but it does mean that any issues or vulnerabilities discovered after that date will not be addressed, This can leave your database at risk of security breaches or performance issues. AWS RDS support for MySQL will also hold good only till October 2023.

To avoid these potential issues, it is recommended to upgrade to a newer version of MySQL before the EOL date for MySQL 5.7. Upgrading to a newer version such as MySQL 8 …

You might want to use binlog compression with MySQL/Percona Server for MySQL, but it can come with drawbacks. This article discusses the need for using binlog compression and the potential issues it may cause.

Binlog compression is a technique used to reduce the size of binary log files, which can become quite large over time. This can be especially important in situations where disk space is limited. However, it’s important to be aware that using binlog compression can also cause issues with replication.

Consider the following scenario: you have restored a backup that was taken from a replica node using Percona XtraBackup. Once the restoration is complete, you want to set up replication using the information from the xtrabackup_slave_info …

Although MySQL 8 was released back in 2018, a significant share of MySQL servers out there are still running MySQL 5.x. MySQL 5 had a lengthy run from its release in 2005, and thus many organizations still have databases that were built on 5.x. But Oracle has been phasing out MySQL 5.7 support for various platforms over the past few years and end of life for MySQL 5.7 is slated for October 2023.

If you’re still running a database on MySQL 5.7, it’s time to seriously consider upgrading. You'll get several new features that give you performance improvements and security enhancements, so it is important that you do this soon — especially with the imminent end-of-life of MySQL 5.7, which means there will be no further security updates. Fortunately, this process is usually pretty straightforward, but there are several changes you may have to make. This article will cover many of the things that you should look out for when …

In this blog, I’ll discuss the use case for replication. We want to improve our ability to replicate your data and limit replication to row-based events securely, wherein we do not have control over the source(s).

The replica doesn’t have checking capabilities when processing replicated transactions as of MySQL 8.0.18. It does this to carry out all instructions from its upstream. The replica must impose data access limitations on the replicated stream because changes may get past the security barrier separating the source and replica in some configurations. In that situation, implementing the upstream changes in a more constrained security context is beneficial to organizations needing privilege-controlled aggregate data from multiple separate databases.

In MySQL 8.0.18, a new feature PRIVILEGE_CHECKS_USER is introduced in replication channels. When a PRIVILEGE CHECKS USER account is used, a replication channel is more protected …

If you are a mobile app, frontend, backend, or game developer, you use data types such as string, numeric, or DateTime. You also know that since the advent of non-relational databases (NoSQL) such as MongoDB, which, by not being tied to a traditional SQL schema, do reading and writing on databases much faster. But MySQL showed that storing the JSON (JavaScript Object Notation) data type could also improve the speed of reading and writing relational databases.

This post will explore the JSON Data type in Percona Server for MySQL.

One of the key features of Percona Server is support for JSON data type, which allows for the storage of JSON documents within MySQL. It allows for more flexible and efficient storage of semi-structured data (​​which is more …

Codership is pleased to announce a new Generally Available (GA) release of the multi-master Galera Cluster for MySQL 5.7, consisting of MySQL-wsrep 5.7.40 (release notes, download) and MySQL-wsrep 8.0.31 (release notes, download) with Galera replication library 4.14 (release notes, download) implementing wsrep API version 26. …

ProxySQL is an open-source MySQL proxy server, meaning it serves as an intermediary between a MySQL server and the applications that access its databases. ProxySQL can improve performance by distributing traffic among a pool of multiple database servers.

Consider 2 slaves are routed under Proxysql , In any one of the slave, if the replication is broken, we could still see the traffic routing to the broken replication slave. We can make Proxy to not send traffic to broken replication slave, by setting appropriate value to the variable mysql-monitor_slave_lag_when_null

…

This post was originally published in October 2018 and was updated in March 2023.

pt-query-digest is one of the most commonly used tools for query auditing in MySQL. By default, pt-query-digest reports the top ten queries consuming the most amount of time inside MySQL. A query that takes more time than the set threshold for completion is considered slow, but it’s not always true that tuning such queries makes them faster. Sometimes, when resources on the server are busy, it will impact every other operation on the server, and so will impact queries too. In such cases, you will see the proportion of slow queries going up. That can also include queries that work fine in general.

This article explains a small trick to identify such spots using pt-query-digest and the slow query log. pt-query-digest …

Hello friends, at Percona Managed Services, we usually do different types of things every day, including routine tasks, monitoring, and, very frequently, answering questions that are not always easy to answer.

A few days ago, a client asked us the following question: “Hey Percona, I have a question and a problem simultaneously: I want to delete rows from a table from a specific date back because the data is not necessary. I tried to run the DELETE command, which gave me this error: Cannot delete or update a parent row: a foreign key constraint fails. Could you please help me?”

At first glance, the error message was obviously clear: the table from which rows were to be deleted had a child table, which prevented the execution of the DELETE directly.

“Don’t worry, we’ll take a look at the issue, and …