This blog post discusses roles in MySQL 8.0, which are named collections of privileges. Like user accounts, roles can have privileges granted and revoked as required.

Typically, we have multiple users with the same set of privileges. Previously, the only way to grant and revoke privileges to multiple users was to change the privileges of each user individually, which was time-consuming. To make it easier, MySQL provided a new object called role. A role is a named collection of privileges.

Here are the primary SQL commands that we will be discussing in relation to managing MySQL roles:

CREATE ROLE and DROP ROLE create and remove roles.
GRANT and REVOKE assign privileges to revoke privileges from user accounts and roles.
SHOW GRANTS displays privilege and role assignments for user accounts and roles.
SET DEFAULT ROLE specifies which account roles are active by default.
SET ROLE changes the active roles within the current session.
The …

Performing an operation is always challenging when dealing with K8s.

When on-prem or DBaaS like RDS or Cloud SQL, it is relatively straightforward to apply a change. You can perform a DIRECT ALTER, use a tool such as pt-osc, or even, for certain cases where async replication is in use, perform changes on replicas and failover.

In this blog post, I’ll provide some thoughts on how schema changes can be performed when running MySQL on Kubernetes

I won’t focus on DIRECT ALTERs as it is pretty straightforward to apply them. You can just connect to the MySQL service POD and perform the ALTER.

But how can we apply changes in more complex scenarios where we may want to benefit from pt-osc, gain better control over the operation, or take advantage of the K8s features?

One convenient way that I’ve found …

Transparent Huge Pages (THP) is a memory management feature in Linux operating systems that aims to enhance system performance. While THP can be beneficial for many applications, enabling it on a database server could have unintended consequences. In this post, we will explore THP, its impact on database servers, and how to disable it for optimal performance and stability.

What are Transparent Huge Pages?

In order to understand THP, we should first start with a brief description of Linux HugePages. The concept of HugePages in Linux has existed for many years, first introduced in 2007. By default, the majority of widely used Linux distributions employ a virtual memory page size of 4KB. However, the inclusion of the HugePages feature allows the Linux kernel to efficiently handle substantial memory pages alongside the standard 4KB size.

In order for an application to utilize HugePages, however, it must explicitly include an …

As a DBA, one of the very frequent tasks is to stop/start MySQL service for batching or some other activities. Before stopping MySQL, we may need to check if there are any active connections; if there are, we may need to kill all those. Generally, we use pt-kill to kill the application connections or prepare kill statements using the select queries.

Example commands:

pt-kill --host=192.168.11.11 --user=percona -p --sentinel /tmp/pt-kill.sentinel2 --pid /tmp/pt-kill.pid  --victims all --match-command 'Query' --ignore-user 'pmm|rdsadmin|system_user|percona' --busy-time 10 --verbose --print --kill 

select concat('kill ',id,';') from information_schema.processlist where user='app_user';

MySQL has a variable called offline_mode to set the server into maintenance mode. When you set this, it immediately disconnects all the client connections that don’t …

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 …

This blog post discusses the limitations of the MySQL Clone plugin.

The MySQL clone plugin significantly simplifies the process of replica provisioning. All you need to do is:

• Ensure that the source server has binary logs enabled
• Grant appropriate permissions
• Execute the CLONE INSTANCE  command on the recipient

This works extremely easily when you provision a new replica that doesn’t have any data.

Due to its simplicity, you may want to use the clone plugin instead of a backup to restore a server that survives data inconsistency or corruption. E.g., after crash.

However, if you have data on your replica, you need to consider how you will recover if the CLONE INSTANCE  command fails with an error.

CLONE INSTANCE  command, by default, works as follows:

• Checks prerequisites on the replica
• Wipes out data …

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

…

As a MySQL database administrator, you’re likely familiar with the SHOW REPLICA STATUS command. It is an important command for monitoring the replication status on your MySQL replicas. However, its output can be overwhelming for beginners, especially regarding the binary log coordinates. I have seen confusion amongst new DBAs on which binary log file and position represent what in the replication.

In this guide, we’ll simplify the SHOW REPLICA STATUS output, focusing on the critical binary log coordinates essential for troubleshooting and managing replication.

The key binlog coordinates

Before we delve into the output, let’s understand the key binlog coordinates we’ll be working with:

• Master_Log_File: This is the name of the primary binary log file that the I/O thread is currently reading from.
• Read_Master_Log_Pos: It represents the …

If you’re a DBA, one of your “easiest” tasks is to stop/start MySQL during a maintenance window, but even that could lead to unwanted scenarios if you modify some dynamic parameters at some point in your instance.

Here’s a brief story of how this could happen, to make it clearer:

You’re a DBA managing a few MySQL servers. The application using one of them starts having issues on a Friday night, right before it’s time to leave; after a quick check, you notice the app is requesting more connections, and the hotfix is to up max connections; you change them dynamically, the fire is off, and so are you. Let your future self worry about finding the root cause and fixing it properly the following Monday.

But life happens; Monday is here with new challenges, and you already forgot about the connections issue… A few months later, a restart for MySQL is required, and surprise, surprise, right after …