Schema changes are required to add new features or to fix bugs in an application. However, there is no standard procedure to make the changes in a quick and safe manner. If the changes are not made considering the necessary precautions, you may face unwanted outages on the database that can cause serious problems to your business. In this blog post, I will delve into the most important things to consider while preparing a schema change.

Table size and concurrency

When assessing a schema change, one of the most important things to consider is the table size and concurrency. For small tables, the ALTER operation usually takes a few milliseconds up to a few seconds. Here is where concurrency plays another important role: if the table has periods of low concurrency during the day and the application allows having it locked for a few seconds or minutes, …

The auto-increment feature, which generates unique values for primary key columns, is an integral part of the database’s design. With the release of MySQL 8, a notable enhancement was introduced to the auto-increment counter. Compared to MySQL 5.7, this enhancement ensures that the maximum auto-increment counter value persists between server restarts, providing enhanced consistency and reliability in data management. In this article, we will look into this enhancement, compare MySQL 5.7 with MySQL 8, and provide practical examples to demonstrate the differences.

Auto-increment in MySQL 5.7

In MySQL 5.7, the auto-increment counter works as follows: when a new row is inserted into a table with an auto-increment column, the counter increments by 1, and the generated value is used for the primary key of the inserted row. This counter value is stored in memory and is not persistent across server restarts. As a result, if the server …

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 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", …

To reduce the backup size, save storage space, and speed up the backup and restore process, you can compress a backup with Percona XtraBackup. The XtraBackup --compress option makes XtraBackup compress all output data, including the transaction log file and metadata files, with one of the supported compression …

This blog describes how to configure systemd for multiple instances of MySQL. With package installations of MySQL using YUM or APT, it’s easy to manage MySQL with systemctl, but how will you manage it when you install from the generic binaries?

Here, we will configure multiple MySQL instances from the generic binaries and manage them using systemd.

Why do you need multiple instances on the same server?

We will do that, but why would you need multiple instances on the same host in the first place? Why not just create another database on the same instance? In some cases, you will need multiple instances on the host. 

1. You can have a host with two or three instances configured as a delayed replica of the source server with SQL Delay of, let’s say, 24hr, 12hr, and 6/3hrs.
2. Backup testing. You can run multiple instances on a server to test your backups with the correct version and configs. …

This blog post will cover the basic setup of the InnoDB ClusterSet environment, which provides disaster tolerance for InnoDB Cluster deployments by associating a primary InnoDB Cluster with one or more replicas in alternate locations/different data centers. InnoDB ClusterSet automatically manages replication from the primary cluster to the replica clusters via a specific ClusterSet Async replication channel. If the primary cluster becomes inaccessible due to a loss of network connectivity or a data center issue, you can make a replica cluster active in its place.

Now, let’s see in detail how exactly we can configure the topology.

We have used the sandbox environment available via MySQLShell utility for this setup.

Environment

Cluster1:
         127.0.0.1:3308
         127.0.0.1:3309
         127.0.0.1:3310

Cluster2:
         127.0.0.1:3311
         127.0.0.1:3312
         127.0.0.1:3313

Router: …

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, …

MySQL 8 brought a significant architectural transformation by replacing the traditional MyISAM-based system tables with the Transaction Data Dictionary (TDD), a more efficient and reliable approach. This upgrade has vastly improved the management and storage of metadata, resulting in better reliability and scalability for various database objects. In this blog post, we will explore the intricacies of MySQL 8’s Transaction Data Dictionary, its advantages, and its real-life application using practical examples.

At the core of MySQL 8’s InnoDB storage engine, the Transaction Data Dictionary (TDD) plays a fundamental role in storing crucial metadata concerning database objects such as tables, indexes, constraints, triggers, and more. This innovative architecture replaces the outdated MyISAM-based system tables, effectively making the handling of data dictionary information transactional and compliant with the ACID principles.

The …