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 …

The MySQL 5.7 End of Life is now two months away. Those of you who have waited may be wondering which is the best way to upgrade – an In-Place or Logical upgrade. An In-Place upgrade is where the binaries for 5.7 are replaced with the binaries for 8.0, and the upgrade is made to an existing instance. The Logical option is a new server that is loaded with the old data from a backup.

Generally, the In-Place upgrade will be faster as you do not have to perform a backup on the old instance and then run a restore on the new platform. Those with very large instances should choose this option.  And remember you should be using the four-byte UTF8MB4 character set, which most likely means you will be using more disk space, so arrange for the extra space BEFORE starting the upgrade.

Logical …

In the world of database management, ALTER TABLE operations are a crucial part of modifying database structures. MySQL, a popular database management system, offers online operations since version 5.6, providing a convenient way to perform these alterations without locking the table. However, there are caveats. In this blog, we’ll explore the process of altering VARCHAR columns online, delving into insights gained while expanding the size of such columns.

To kick start our journey, let’s consider a table definition that requires the expansion of a VARCHAR column named “_varchar” to accommodate more data. Here’s the original table definition:

DROP TABLE IF EXISTS varchar_alter;
CREATE TABLE `varchar_alter` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `_varchar` VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

We execute the initial ALTER TABLE command:

mysql> ALTER TABLE test.varchar_alter CHANGE COLUMN …

In this blog post, we will describe the improvements to Percona XtraBackup 8.0.33-28 (PXB), which significantly reduces the time to prepare the backups before the restore operation. This improvement in Percona XtraBackup significantly reduces the time required for a new node to join the Percona XtraDB Cluster (PXC).

Percona XtraDB Cluster uses Percona XtraBackup to do SST (State Snapshot Transfer) from one node to another. When a new node joins the cluster, SST is performed to receive the data from DONOR to the JOINER. JOINER uses PXB to stream the data directory from DONOR. JOINER must prepare the backup before using it. It is observed that when the DONOR has a huge number of tablespaces (one million),  XtraBackup on JOINER …

Upgrading to MySQL version 8.0 is a hot topic since version 5.7 is approaching the official end of life very soon. MySQL 5.7 EOL is set for the end of October 2023.

If you feel unprepared for the upgrade, consider post-EOL support from Percona. But it would be the worst if you proceeded with the upgrade in haste.

• Long database service downtime
• Some queries become slow
• Applications stop working due to incompatibilities
• Data loss

This is what can happen if MySQL’s major upgrade to 8.0 turns out not as you hoped for. Yes, it can be that serious, so do consider the major upgrade as an important project, not just a routine maintenance task! From my experience as a Support Engineer, I think the most frequent problem is query …

This post was originally published in July 2018 and was updated in July 2023.

Now that Database-as-a-service (DBaaS) is in high demand, there are multiple questions regarding AWS services that cannot always be answered easily: When should I use Aurora and when should I use RDS MySQL?  What are the differences between Aurora and RDS? How do I choose which one to use?

In this blog, we will answer all of these important questions and provide a general overview comparing the two database services, Aurora vs RDS.

Understanding DBaaS

DBaaS cloud services allow users to use databases without configuring physical hardware and infrastructure or installing software. But …

Often while managing and creating new users, we use all the default options and tend not to use extra features provided by MySQL. These extra options could prevent a user from using all the resources and degrading the performance of MySQL. In this blog, we will discuss a few such features that will put resource restrictions on users.

max_user_connections

Sometimes, due to unprecedented growth or huge transactions, a single user makes too many connections, and the MySQL server gets starved of free connections. This blocks the DBA from logging into MySQL to fix it. To fix it in MySQL 5.7 and below, we have to do a restart. For MySQL 8, it can be done without a restart, but we have to configure the admin interface beforehand. You can read more about it here in Dealing With “Too Many Connections” Error in MySQL 8.

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.42 (release notes, download) and MySQL-wsrep 8.0.33 (release notes, download), with Galera replication library 4.15 (release notes, download) implementing wsrep API version …

Learn how Multi-Valued Indexing in MySQL 8.0 can enhance query performance by efficiently indexing and querying JSON arrays. Discover the benefits, implementation steps, and considerations for optimizing your MySQL database.

1. Multi-Valued Indexing in MySQL 8.0
2. Understanding Multi-Valued Indexes and their benefits
3. Creating Multi-Valued Indexes in MySQL …

When working with MySQL (or any database!), it's essential to understand how indexes work and how they can be used to improve the efficiency of queries. An index is a separate data structure that maintains a copy of part of your data, structured to allow quick data retrieval. Usually, this structure is a B+ Tree. We have an entire post on how indexes work if you want to go into greater detail.

Obfuscated indexes

Creating indexes is only part of the battle. You must also know how to write your queries so that you allow MySQL to use your indexes. One common mistake people make when writing queries is that they obfuscate their indexes. Obfuscating an index simply …