A really convenient feature in MySQL 8.0 is the ability to persist the values of global variables across server restarts, without writing them into an options file. This was developed primarily for the benefit of Cloud installations of MySQL, but is very handy for a DBA in on-premise installations too.

To use this feature you need to have the SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges.

To demonstrate, we'll increase the value of the max_connections system variable from its default of 151 to 152 and then restart the MySQL server to check that MySQL remembers the new value.

mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (#.## sec)

Use SET PERSIST to persist the change that we're about to make:

mysql> SET PERSIST max_connections …

Have you ever tried to improve the performance of a query by adding a new index to a table with a large number of rows, waited for ages while the index is built, only to discover that it didn't really help as much as you'd like?

Or perhaps you have had an index that you have a hunch isn't doing much to help your query performance and decided to drop it, only to have a bunch of users complain that their queries are stalling?

If so, then you will appreciate a nice new feature in the forthcoming MySQL 8.0 release called "invisible indexes".

Invisible indexes enable you to test the effect of removing an index on query performance without making a destructive change that must be undone should the index turn out to be required. By marking an index as invisible, you are effectively "hiding" it from the optimizer while the index itself remains intact and can be restored at any time. This feature makes it much easier to test the …

Like any mature technology that is constantly evolving, MySQL has amassed a jargon of its own. Some of this terminology occasionally causes confusion, especially when two terms are similar, but actually refer to two completely different things.

This is particularly the case for the two storage engines InnoDB and NDB (which sound very alike when spoken aloud), and the two "Cluster" technologies: InnoDB Cluster and MySQL Cluster.

Let's see if we can clear this confusion up.

InnoDB is a storage engine - the software component that a database uses to read, write, update, and delete data and perform other fundamental operations. InnoDB replaced MyISAM as the default storage engine for MySQL in 2010, with the release of version 5.5 (largely because of its support for transactions and foreign keys) and is the best option for most workloads.

NDB (also known as NDBCLUSTER) is another …

Replication lag occurs when the slaves (or secondaries) cannot keep up with the updates occuring on the master (or primary). Unapplied changes accumulate in the slaves' relay logs and the version of the database on the slaves becomes increasingly different from that of the master.

To work out what's causing the lag, you must determine which replication thread is getting backed up. Replication relies on three threads per master/slave connection: one is created on the master and two are created on the slave.

• The Slave I/O Thread. When you issue START SLAVE on a slave server, the slave creates this thread which connects to the master and requests a copy of the master's binary log.
• The Binlog Dump Thread. When the slave connects to the master, the master uses this thread to send the slave the contents of its binary log.
• The Slave SQL …

The "MySQL for Database Administrators" course has been revised and released in its 4th edition.

As with previous editions, it covers the core techniques that all DBAs need to know, such as server configuration, user management, security, troubleshooting, query optimization, backups, and maintaining high availability.

The new edition also covers how to use exciting new features in the latest MySQL GA releases, including Group Replication and MySQL Cloud Service.

This is a very hands-on course, and each lesson has extensive practical activities so that you get a chance to try out new features and techniques in the safety of a properly-configured learning environment.

You can take this course in the following formats:

• Live-Virtual Event: Attend a live event from …