The latest from the MySQL community

Whenever you install your favorite MySQL server on a freshly created Ubuntu instance, you start by updating the configuration for MySQL, such as configuring buffer pool, changing the default datadir director, and disabling one of the most outstanding features – query cache. It’s a nice thing to do, but first things first. Let’s review the best practices we usually follow in Managed Services before using your MySQL server in production and stage env, even for home play purposes.

Memory

Our usual recommendation is to use specific memory parameters, which we suggest to ensure optimal performance.

• To prevent out-of-memory (OOM) episodes, the OOM Score has to be set to -800.
• vm.swappiness = 1
• Disable Transparent Huge Pages
• Install and enable jemalloc. Let’s briefly go through each setting to understand why adjustments are needed. Afterward, we will see how to configure these …

As MySQL database administrators, we are well aware of the significance of implementing a primary key within a table. Throughout our careers, most of us have encountered situations where the absence of a primary key has led to operational challenges. Primary keys play an indispensable role in sound database design by uniquely identifying individual rows and significantly enhancing data retrieval, manipulation, and overall system performance.

From the MySQL documentation:

The PRIMARY KEY clause is a critical factor affecting the performance of MySQL queries and the space usage for tables and indexes. The primary key uniquely identifies a row in a table. Every row in the table should have a primary key value, and no two rows can have the same primary key value.

It is common for tables to be inadvertently created without a primary key, often leading to regrettable consequences that we only recognize when issues …

ProxySQL is a high-performance SQL proxy, which runs as a daemon watched by a monitoring process. The process monitors the daemon and restarts it in case of a crash to minimize downtime.

The daemon accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.

The proxy is designed to run continuously without needing to be restarted. Most configurations can be done at runtime using queries similar to SQL statements in the ProxySQL admin interface. These include runtime parameters, server grouping, and traffic-related settings.

Here, we will consider ProxySQL configured for async replication. Even when a replica is broken/stopped, ProxySQL still routes connections to replicas. It can be overcome by setting the appropriate value for mysql-monitor_slave_lag_when_null

…

Some fun is coming

While I was writing the internal guidelines on how to migrate from MariaDB to Percona Server, I had to export the users accounts in a portable way. This given MariaDB uses  some non standard syntax brings me to first test some external tools such as Fred https://github.com/lefred/mysqlshell-plugins/wiki/user#getusersgrants and our PT-SHOW-GRANTS tool. 

Useless to say this had open a can worms, given first I had to fix/convert the specifics for MariaDB (not in the scope of this blog), then while testing I discover another nasty issue, that currently prevent us to easily export the new …

If you are deploying MySQL on containers, one of the first tasks is to find the right image.

There’s a certain amount of confusion, especially when we’re trying to help someone who’s having problems with their deployment.

For example, when people say I’m using the official docker image… what does that really mean?

Docker Hub, provides their official image (https://hub.docker.com/_/mysql), but this is not the official MySQL image that we, the MySQL Team at Oracle, support.

Before the mess with Docker Hub ([1], [2], [3]), the real official images for MySQL …

MySQL is an outstanding open source transactional database used by most web-based applications and is very good at handling OLTP workloads. However, modern business is very much dependent on analytical data. ClickHouse is a columnar database that handles analytical workloads quickly. I recommend you read our previous blog, Using ClickHouse as an Analytic Extension for MySQL, from Vadim to know more about this.

In this blog post, I will show you how to replicate MySQL data in real-time to ClickHouse. I am going to use the MaterializedMySQL engine for this purpose. The blog post contains the following topics.

• MaterializedMySQL Engine – Overview
• Prerequisites for the Replication
• Replication setup
• Replication testing
• Understanding ReplacingMergeTree
• What …

Percona University Online has released a new free course, “MySQL 5.7 End of Life Options – Free Course at Percona University Online,” by Dave Stokes, Technical Evangelist at Percona. 

Dave is the author of MySQL & JSON – A Practical Programming Guide. He started to work with MySQL from the 3.29 version and was a part of the MySQL Community Team for over a decade.

MySQL 5.7 reaches its End of Life in October 2023. The Era of MySQL 5.x will be over, and only MySQL 8.0 will be officially supported. Learn how to keep your database secure and performant after that date and what real options you have if you decide to upgrade or stay on the current version. 

This course consists of eight short videos. Pass a brief quiz on Google Classroom afterward to receive a Certificate of Completion from Percona. The …

This blog was originally published in February 2019 and was updated in September 2023.

Web applications rely on databases to run the internet, powering everything from e-commerce platforms to social media networks to streaming services. MySQL is one of the most popular database management systems, playing a pivotal role in the functionality and performance of web applications.

In today’s blog, I’ll take a look at MySQL collation and charset settings to shed light on how they impact the performance of web applications and how to use them to effectively communicate with your users.

Understanding Character Sets and Encoding in MySQL

Character sets and encoding in MySQL play a vital role in how data is stored and retrieved in a database. A character set is a collection of characters with unique representations for each character, such as letters, numbers, and symbols, that define how data is …

This blog was originally published in November 2021 and was updated in September 2023.

MySQL server performance can sometimes be perplexing, and if you’ve ever wondered about the role of triggers in influencing your MySQL server’s memory allocation, this post is for you. MySQL triggers are a powerful tool for database administrators and developers, enabling them to automate tasks, enforce data consistency, and respond to events within the database seamlessly.

Here, we first cover the basics of MySQL Triggers, and then we take a deeper dive, exploring their impact on memory usage and providing strategies to optimize MySQL server performance.

What is a Trigger in MySQL?

A Trigger in MySQL is a database object that plays a pivotal role in database management. It is essentially a set of instructions or a program that is executed automatically in response to specific events or actions occurring within the …

This blog post will focus on failover and recovery scenarios inside the InnoDB Cluster and ClusterSet environment. To know more about the deployments of these topologies, you can refer to the manuals – InnoDB Cluster and Innodb ClusterSet setup.

In the below snippet, we have two clusters (cluster1 and cluster2), which are connected via an async channel and combined, known as a ClusterSet topology. We are going to use the below topology in all of our cases.

MySQL  127.0.0.1:3308 ssl  JS > myclusterset.status({extended: 1})
{
    "clusters": {
        "Cluster1": {
            "clusterRole": "PRIMARY", …