The latest from the MySQL community

So, you’ve been tasked with managing the MySQL databases in your environment, but you’re not sure where to start.

Here’s the quick & dirty guide. Oh yeah, and for those who love our stuff, take a look to your right.

See that subscribe button? Grab our newsletter!

Steps to MySQL Management

Here are the steps that are required for MySQL management as a DBA:

1. Installation

The “yum” tool is your friend.  If you’re using Debian, you’ll use apt-get but it’s very similar. You can do a “yum list” to see what packages are available. We prefer to use the Percona distribution of MySQL.

It’s fully compatible with stock MySQL distribution, but usually a bit ahead in terms of tweaks and fixes. Also, if you’re not sure, go with MySQL 5.5 for new installations. …

There is no need to mention that speed is one of the most sought-after attributes in any database system. Optimizing MySQL for high speed is essential to ensure responsive applications and efficient data processing.

There are basically two best ways to speed up UNION in MySQL – using UNION ALL and using push-down conditions.

In this article, we’ll explore techniques to achieve optimal performance and speed in MySQL.

Ways to Optimize MySQL for High Speed

Here’s a rundown of how to do those optimizations.

1. UNION ALL is much faster than UNION

How does a UNION work? Imagine you have two tables for shirts. The short_sleeve table looks like this:

And long_sleeve another that looks like this:

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

In this blog post, I provide an in-depth introduction to MySQL Replication, answering what it is, how it works, its benefits and challenges, as well as reviewing some of the MySQL replication concepts that are part of the MySQL environment (and Percona Server for MySQL specifically). I will finish by also clarifying some of the common misconceptions people have about replication and how Percona can help.

Since I’ve been working on the Solution Engineering team, I’ve noticed that – although information is plentiful – replication is often misunderstood or incompletely understood.

What is MySQL Replication?

MySQL replication is …

With the increasing data and user demand, ensuring the scalability of your MySQL database has become crucial to maintain optimal performance. With this, you can handle growing amounts of data, traffic, and user requests with your database in MySQL. But how to boost MySQL scalability?

This is the most trending question among MySQL users and if you are one of them, then this article is just for you. You can simply boost MySQL scalability by optimizing MySQL queries, database schema, and server configuration.

In this article, we’ll explore five effective ways to boost MySQL scalability and handle your database’s growth effectively. So, what are you waiting for? Let’s explore them below!

5 Ways To Boost MySQL Scalability

There are a lot of scalability challenges we see with clients over and over. The list could easily include 20, 50, or even 100 items, but we shortened it down to the biggest five issues we …

This article is from 2006. MySQL has come a long way since then. MySQL 5.5 is very robust and feature-rich, matching Oracle in many different areas including datatypes, stored procedures and functions, high availability solutions, ACID compliance and MVCC, hotbackups, cold backups and dumps, full text, and other index options, materialized views and much more.  Here’s a high level mysql feature guide.

What really separates the two technologies is culture. MySQL, rooted in the open-source tradition is much more do-it-yourself, leaning towards roll-your-own solutions in many cases. Meanwhile, Oracle provides named and proven paths to solve specific problems.

You might also check out:  …

MySQL DBAs are in greater demand now than they’ve ever been. While some firms are losing the fight for talent, promising startups with a progressive bent are getting first dibs with the best applicants.

Whatever the case, interviewing for a MySQL DBA is a skill in itself so I thought I’d share a guide of top MySQL DBA interview questions to help with your screening process.
It’s long and detailed with some background to give context so I will be publishing this in two parts.

The History of The DBA As A Career

In the Oracle world of enterprise applications, the DBA has long been a strong career path. Companies building their sales staff required Peoplesoft or SAP, and those deploying the financial applications or e-business suite needed operations teams to manage those systems.

At the heart of that operations team were database administrators or DBAs, a catchall title that …

MySQL 8.0.34 brings us a new password validation parameter. Using this, we can control the minimum number of characters in a password that a user must change before validate_password accepts a new password for the user’s account.

In this blog, I offer a few scenarios showing how the parameter validate_password.changed_characters_percentage affects user password changes.

Requirement

To make this work, we should enable the “Password Verification-Required Policy” (introduced in MySQL 8.0.13). We can allow it to GLOBALLY by using the parameter “password_require_current” or by specifying  “PASSWORD REQUIRE CURRENT” while creating or altering the user. This topic was already explained very well by Brain Sumpter in his post, …

In InnoDB, the undo log and the redo log are two indispensable components that play a vital role in maintaining data integrity and ensuring transactional consistency. Both logs are crucial in the ACID (Atomicity, Consistency, Isolation, Durability) properties of database systems. Additionally, they are essential for the Multi-Version Concurrency Control (MVCC) mechanism. In this blog post, we will delve into the differences between the InnoDB undo log and redo log, exploring their significance and providing code examples to illustrate their usage.

InnoDB Undo Log

The undo log, also known as the rollback segment, is a crucial part of the InnoDB storage engine. Its primary purpose is to support transactional consistency and provide the ability to …

This blog was originally published in July 2017 and was updated in August 2023.

It’s a pretty common question around here, so let’s see what we can do about that.

So, What is MySQL Partitioning?

Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables but still gets treated as a single table by the SQL layer.

When partitioning in MySQL, it’s a good idea to find a natural partition key. You want to ensure that table lookups go to the correct partition or group of partitions. This means that all SELECT, UPDATE, and DELETE should include that column in the WHERE clause. Otherwise, the storage engine does a scatter-gather and queries ALL partitions in a UNION that is not concurrent.

Generally, you must add the partition key into the primary key along …

In this article, I want to discuss a couple of pretty new features in MySQL 8.0 — and an older one. Maybe these are minor features you are not aware of, and maybe not so relevant, to be honest. But it is worth providing a quick overview, showing how they work, and how they could be useful in some cases.

All refer to the invisibility of something:

• Invisible columns
• Generated invisible primary keys
• Invisible indexes

Let’s take a look.

Invisible columns

The invisible columns feature has been deployed since version 8.0.23. What is an invisible column? It’s basically a regular column of a table with its own name and data type. It is treated and updated as any other regular column, with the only difference being that it is invisible to the application. In other words, it can be accessed only in the case it is explicitly addressed in your SELECT; otherwise, it is …