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 …

The clone plugin was introduced in MySQL 8.0.17 and offers a convenient method for cloning data from either a local or remote MySQL server instance. This cloning process creates a physical snapshot of the data stored in InnoDB, including schemas, tables, tablespaces, and data dictionary metadata. The clone plugin allows for easy provisioning of MySQL servers by generating a fully functional data directory. In this blog post, we will explore the steps to configure a new MySQL replica using the clone plugin.

Installation of the Clone Plugin

To load the clone plugin during server startup, you can utilize the –plugin-load-add option and specify the library file containing the plugin. Ensure that you add this option each time the server starts when using this plugin-loading method. To make this configuration change, add the following lines to your my.cnf file, adjusting the plugin library file name extension according to your …