The “SHOW PROCESSLIST” command is very famous and very useful for MySQL DBAs. It will help you to understand the ongoing thread activities and their current states. By default, the “show processlist” output details will be collected from the thread manager, and it needs the global mutex. From MySQL 8.0.22, we have an alternative way to get the process details from the PERFORMANCE_SCHEMA. It doesn’t need the global mutex. 🙂

Note: We also have the non-blocking SYS schema views “processlist” and “x$processlist”, which provide more complete information than the SHOW PROCESSLIST statement and the INFORMATION_SCHEMA.PROCESSLIST and PERFORMANCE_SCHEMA.PROCESSLIST. But, we can’t integrate this with the “SHOW PROCESSLIST” command.

In this blog, I am going to explain the complete details about the new processlist implementation using PERFORMANCE_SCHEMA.

“SHOW PROCESSLIST” Using Thread …

In this blog, we will review all the backup and restore strategies for MySQL, the cornerstones of any application. There are a few options, depending on your topology, MySQL versions, etc. And based on that, there are some questions we need to ask ourselves to make sure we make the right choices.

How many backups we need to keep safe, or what’s the best retention policy for us?

This means the number of backups to safeguard, whether local or remote (external fileserver, cloud). The retention policy can be daily, weekly, or monthly, depending on the free space available.

What is the Recovery Time Objective?

The Recovery Time Objective (RTO) refers to the amount of time that may pass during a disruption before it exceeds the maximum allowable threshold specified in the Business Continuity Plan.

The key question related to RTO is, “How quickly must the data on this system be restored?”

What is …

Today we are going to present you with another way to make a database copy between servers using our Percona XtraBackup utility.  What is the difference with many others existing on the web?  Not many, but they are interesting in terms of performance and availability.

We will combine the xbstream utility with the power of pigz and socat, to take advantage of the multi-processing in the case of having multiple processors, and at the same time, reduce the use of network bandwidth in cases where this component is a bottleneck.  So let’s explain each component:

socat: This stands for SOcket CAT. It is a utility for data transfer between two addresses.

What makes socat so versatile is the fact that an address can represent a network socket, any file descriptor, a Unix domain datagram or stream …

It has been some time since I have posted updates to my plugins.  After the initial version, I decided to split the plugins into categories of metrics.  This will allow users to choose whether they want to install all of the plugins or only select ones they care about.

Since the installation process is unfamiliar to many users, I also expanded the instructions to make it a little easier to follow.  Moreover, I added a Makefile.

I have also reformatted the output of some plugins to be either horizontal or vertical in orientation.  There is still more work to do in this area as well.

Where to Get The MySQL Plugins

You can get the plugins from GitHub at https://github.com/toritejutsu/osmetrics but they will have to be compiled from source.  As mentioned above, you can choose whether to install all of them or one by …

In previous TAM Enterprise Experiences posts, we have outlined typical aspects of utilizing MySQL in an Enterprise environment. One thing we have not yet covered is the topic of database encryption, both from the standpoint of business requirements as well as some of the more technical aspects of encryption.

In this post, we will cover:

• Common enterprise compliance requirements
• Types of MySQL encryption
• Choosing the right encryption
• Vault

Common Compliance Requirements

Beyond the obvious security concerns with sensitive data, most enterprise businesses also need to meet various compliance requirements, with the compliance requirement(s) dependent on the country the business is located in, the type of business, and the type of data being stored. Note that in all cases, the onus is on the business to protect the data based on these compliance requirements. Some of …

Earlier in November, we had a chance to present the “Google Cloud Platform: MySQL at Scale with Reliable HA.” We discussed different approaches to hosting MySQL in Google Cloud Platform with the available options’ pros and cons. This webinar was recorded and can be viewed here at any time. We had several great questions, which we would like to address and elaborate on the answers given during the webinar.

Q: What is your view on Cloud SQL High Availability in Google Cloud?

A: Google Cloud SQL provides High Availability through regional instances. If your Cloud SQL database is regional, it means that there’s a standby instance in another zone within the same region. Both instances (primary and standby) are kept synced through synchronous replication on the persistent …

A lot of elements take part in a good backup strategy. Two of them are:

• Where to store your backups – Never on the same server. Preferable not in the same datacenter. Ideally far enough so you can restore in case of a natural disaster (region flood, extended local power outage, etc…).
• Retention period – The longer, the better.

Those points relate directly to costs. Storing a backup on an off-site facility cost money. Keeping the backups available to fulfill your retention period policy costs money.

The main cloud providers have different layers of storage often called Storage Class. Each layer has its own particularities and policies, but they come with an important factor – Cost!

We are happy to announce that starting at …

MySQL 5.7, alongside other many improvements, brought bulk load for creating an index (WL#7277 to be specific), which made ADD INDEX operations much faster by disabling redo logging and making the changes directly to tablespace files. This change requires extra care for backup tools. To block DDL statements on an instance, Percona Server for MySQL implemented LOCK TABLES FOR BACKUP. Percona XtraBackup (PXB) uses this lock for the duration of the backup. This lock does not affect DML statements.

MySQL 5.7 doesn’t have an option to block an instance against DDL and to allow all MDL’s. Thus, Percona XtraBackup has also implemented –lock-ddl-per-table. Before we go into other details, let’s understand how –lock-ddl-per-table works up to now:

1. PXB …

As mentioned in the AWS discussion forum back in October, Amazon has started the end of life (EOL) process for RDS MySQL version 5.5. What this means is:

• AWS will upgrade RDS instances to MySQL 5.7 starting February 9 2021 00:00 UTC during your next defined maintenance window, provided you have one.
• If you don’t have a maintenance window defined, RDS will automatically upgrade you on March 9 00:00 UTC and there is no opt-out.

As any seasoned administrator knows, upgrades can be painful and things might go wrong.

Risks

I think we can safely assume that the upgrade will be performed in-place, as it would be too complex and time-consuming otherwise. Since a direct upgrade from 5.5 to 5.7 is not supported, we need to go through 5.6 first. This means instances need to be restarted twice; first to go from …

MySQL Shell is an advanced client tool that has many features and is easy to use. The recent MySQL release (MySQL 8.0.22) has the utility “exportTable()”, which supports exporting the data into a file from a table using MySQL shell. The exported data file can be imported using the utility “importTable()”, which was released in MySQL 8.0.17.

With “exportTable()”, you can export the data into a local server or in any S3-compliant object storage provider. In this blog, I am going to explain how those exportTable() & importTable() utilities are working in local servers and I also did some tests with MySQL 5.7 and MySQL 5.6.

Overview of exportTable() & importTable() exportTable():

• Introduced in MySQL 8.0.22. 
• The utility is used to export the data from the MySQL table into a data file. 
• It can be used to export the table to a local server or any S3-compliant object …