Question: how do I know that one user is waiting for a lock and another  user is burning CPU on MySQL?

i.e. how can I create active session history on MySQL like on Oracle?

Two simple cases I’m looking at

1. session waiting on lock
   1. The wait event is ” wait/io/table/sql/handler” which happens for a number of potential reasons
   2. -> I can join to information_schema views INNODB_LOCK_WAITS & INNODB_TRX but this is heavy, and I’d rather only access those after I know there is a lock waiter.
2. session burning CPU
   1. How do I know from performance schema views that the connection is active and burning CPU?
   2. It has a wait event that shows up wait/synch/mutex/sql/THD::LOCK_thd_data
   3. on Oracle to determine CPU, we just …

As part of my ongoing series around MySQL 8 user administration, I’d like to cover one of the new features introduced in MySQL 8.0.27 – multi-factor authentication. In order to establish identity, multi-factor authentication (MFA) is the use of multiple authentication values (factors) during the MySQL authentication process.

Introduction

MFA provides greater security compared to a single-factor authentication method, which has historically been based on simple methods such as password authentication. With MFA, additional authentication methods are enabled, such as requiring multiple passwords, or with devices such as smart cards, security keys, or biometric readers.

As of MySQL 8.0.27, it is now possible to require up to three authentication values to establish identity. In addition to the more common 2FA (two-factor authentication), MySQL can now also support 3FA (three-factor authentication) to complement the …

This blog post is an excerpt from premium MySQL Beginner content I am creating to help anyone learn how to use MySQL. Thank you for reading and please do provide feedback.

Image by Willi Heidelbach from Pixabay 

You may also be interested in the blog post, MySQL Beginners Series — The SELECT statement, which is a primer for this article.

Limit …

In keeping with my MySQL 8 user administration and security theme, I’d like to discuss the password verification-required policy introduced in MySQL 8.0.13. With this feature, it is possible to require that attempts to change an account password be verified by specifying the existing current password to be replaced.

Introduction

The password verification-required policy enables DBAs to prevent users from changing a password without proving that they know the current password. When would this happen? Such changes could occur if a user walked away from a terminal session without logging out, and a malicious user uses the open session to change the original user’s MySQL password. As you can imagine, this could have disastrous consequences:

• The original user is now unable to access MySQL until an administrator can reset the account password. 
• The malicious user can access MySQL until a password …

When MySQL client connects to the server it uses one of the authentication plugins. On the server-side, plugins are located in the directory, specified by the option plugin-dir that defaults to

BASEDIR/lib/plugin

  where

BASEDIR

  is the base directory of your MySQL installation. This perfectly works whenever you install MySQL using package manager, or from the Linux tarball.

However, authentication plugins should be also loaded on the client-side. If you installed MySQL into the custom location, you may end up with an error when trying to connect using any plugin requiring a separate client library.

For example, let’s set the …

I am so pleased to announce that I have published my very first mini eBook, “10 MySQL Tips For Everyone”. Continue reading to find out what it’s about, and how to get your copy.

What is the 10 MySQL Tips For Everyone eBook about?

In this eBook, I share 10 useful tips for anyone working with the MySQL database. These tips are a mix of the relatively common, and a couple of my personal own that I have realized or discovered as I continue to learn MySQL.

Here are a couple of my favorites.

So you’re (hopefully) wondering how you can get your own free copy of “10 MySQL Tips For Everyone”.

Super easy…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

That’s it.

Adminer is a full-featured database management tool written in PHP. In this tutorial, I will explain how to install Adminer on Alma Linux 8.

Get ready for another jam-packed full OpenLampTech newsletter issue this week. If you are a PHP/MySQL developer, this is the newsletter for you. Let’s see what this latest issue has in store…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Just like always, we have some great curated and original content in this week’s issue:

• MySQL NULLs in the ORDER BY clause
• PHP complete login and authentication
• How to use PHP and MySQL
• MySQL Events
• Missing MySQL extension for WordPress
• Free MySQL ebook
…

Is MySQL performance about “more” or “less”? From the title, you can tell that I’m going to argue that it’s about “less”. Here’s the punchline: zero is maximum performance. Let’s see where this philosophical blog post leads us.

Is MySQL performance about “more” or “less”? From the title, you can tell that I’m going to argue that it’s about “less”. Here’s the punchline: zero is maximum performance. Let’s see where this philosophical blog post leads us.