Showing entries 1 to 10 of 287
10 Older Entries »
Displaying posts with tag: mysql-and-variants (reset)
PHP 8.0 Reaches End of Life

While the LAMP stack – Linux/Apache/MySQL/PHP – is not the dominant platform it was a decade ago, there are still many websites that depend on it. One of the pillars of this quartet had a milestone last week when PHP 8.0 passed into End Of Life status. While it will still have limited security issue support for another year, 8.0 should be phased out of your environments.

By the way, 8.1 has another year until it reaches End of Life status.

So please update your PHP 8.0 as soon as you can, double check the connector you are using to access your database is updated too, and make sure you are ready for 8.1’s future.

And make sure that if you are currently running MySQL 5.7 that you upgrade by October 2023 when it too reaches End of Life.

MySQL Data Archival With Minimal Disruption

We all know that data is important, and some businesses need historical data to be available all the time. The problem is that queries on large tables perform poorly if they are not properly optimized. We get many customer requests in Managed Services to purge/archive large tables, and to achieve it, we use pt-archiver.

Recently, we received a request to archive a large table, and the customer was worried about the downtime and performance issues during the archival.

We proposed a solution to the customer to archive the table using pt-archive. The idea is to archive old data to other tables and keep the latest data on the current table with minimal performance issues. All of the data will remain available and can be queried anytime.

In the blog, I will …

[Read more]
PMM, Federated Tables, Table Stats, and Lots of Connections!

Earlier in the year, I was working on an issue where one of my clients had reported a massive influx in connection on their hosts after enabling Percona Monitoring and Management (PMM). This was something I had not seen before and after researching for a couple of days I discovered that if you monitor a MySQL instance with PMM configured to collect table statistics, and if the tables that it’s gathering statistics from are Federated, it will generate a connection on the remote host for the Federated tables, one for each Federated table in the instance. Let’s go over the details and provide some examples so we can understand this a bit better.

First, I’ll offer a reminder that a Federated table is simply a table that you can put in your MySQL instance that is empty locally and uses a network connection to get the data from …

[Read more]
Exploring Data Dump and Load Utility With MySQL Shell

In this blog post, I will try to cover a few dump and data-loading utilities offered by MySQL Shell.

What is MySQL Shell?

It is a robust client and code editor for MySQL. In addition to having APIs for dealing with MySQL, MySQL Shell offers scripting capabilities for JavaScript and Python. It provides an AdminAPI to manage MySQL servers and create InnoDB Cluster and ReplicaSet deployments. It supports an X DevAPI to work on both SQL and NoSQL interfaces via the X Protocol. Finally, it provides utilities to make working with MySQL in Python and Javascript mode easier.

Now, let’s jump into some practical stuff and see what these tools offer us.

Dump Utility

In the dump utility, there are three tools: util.dumpInstance(), util.dumpSchemas(), and util.dumpTables () so let’s discuss them one by one. 

1.  “util.dumpInstance()” – This was introduced in MySQL Shell 8.0.21 and …

[Read more]
Online DDL Tools and Metadata Locks

One thing I commonly hear when working with my clients is “I want to change my DDL strategy in order to avoid locking in my database! The last time I used the same old method I ended up in a metadata lock situation!”

I agree that metadata locks can be painful, but unfortunately, it’s completely unavoidable, and changing from one tool to another won’t help with this. That said, it’s still worth it to examine how metadata locks work and what the impact is for each of the common tools and processes. In doing so we will see that all these tools will require metadata locks, but knowing more about how they work and how the use locking can help us determine the right tool for your specific use case.

Any time you make a change to a table a metadata lock is needed to ensure consistency between the table itself and MySQL’s data dictionary. In order for MySQL to establish this lock it has to wait for any query against the table in …

[Read more]
MySQL Dual Passwords – How To Manage Them Programmatically

What is dual password in MYSQL and how it works was already covered by my colleague Brian Sumpter in Using MySQL 8 Dual Passwords.

However, let me do a brief recap here about it.

Dual password is the MySQL mechanism that allows you to keep two passwords active at the same time. This feature is part of a more extended set of password management features implemented in MySQL 8 to enforce better security and secrets management, like:

  • Internal Versus External Credentials Storage
  • Password Expiration Policy
  • Password Reuse Policy
  • Password Verification-Required Policy
  • Dual Password Support
  • Random Password Generation
  • Failed-Login Tracking and Temporary Account Locking

The most important and requested features are the password expiration and …

[Read more]
Making Your MySQL Backup Process up to 17X Faster – Introducing Percona XtraBackup Smart Memory Estimation

Taking a MySQL backup using Percona XtraBackup (PXB) consists of basically two steps: 1) take the backup and 2) prepare the backup.

Briefly speaking, taking a backup means that PXB will copy all of the files from your instance and transfer them to another location. While it does the copy, it spawns a thread that will monitor the InnoDB redo log (WAL/transaction log) and store a copy of all the new redo log entries generated by the server during the backup.

Before restoring the backup into a new instance, users have to prepare the backup. This operation is the same as the crash recovery steps that the MySQL server does after a server crash.

It consists of reading all the redo log entries into memory, categorizing them by space id and page id, reading the relevant pages into memory, and checking the LSN number on the page and on …

[Read more]
How To Get Your Backup to Half of Its Size – Introducing ZSTD Support in Percona XtraBackup

Having a backup of your database is like insurance, you have to pay a monthly price to ensure you have a service available when you need to. When talking about backups, the storage required to keep your backups is what comes into factor when talking about price, the bigger your backup, or the bigger the retention period, the more it will cost.

Compressing your backups is a common practice to reduce this cost. Currently, Percona XtraBackup (PXB) has support for two compression algorithms: quicklz (which is an abandoned project and will soon be deprecated in PXB) and LZ4.

Today we are glad to introduce support for a new compression algorithm in Percona XtraBackup 8.0.30 – …

[Read more]
Enable innodb_print_all_deadlocks Parameter To Get All Deadlock Information in mysqld Error Log

At Percona Managed Services, sometimes clients’ applications face deadlock situations and need all historic deadlock information for application tuning.

We could get the LATEST DETECTED DEADLOCK from SHOW ENGINE INNODB STATUS\G:

….
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 163 page no 3 n bits 72 index GEN_CLUST_INDEX of table `deadlock_test`.`t` trx id 78507 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 78508, ACTIVE 155 sec starting index read
mysql tables in use 1, locked 1
….

But how could we view all past deadlock information?

We could enable innodb_print_all_deadlocks,  and all deadlocks in InnoDB user transactions will be recorded in the MySQL error log.

Let‘s start the test.

Create the test …

[Read more]
ProxySQL Support for MySQL caching_sha2_password

Every day we use dozens if not hundreds of applications connecting to some kind of data repository. This simple step is normally executed over the network and, given so, it is subject to possible sniffing with all the possible related consequences.

Given that, it is normally better to protect your connection using data encryption like SSL, or at the minimum, make the information you pass to connect less easy to be intercepted.

At the same time, it is a best practice to not store connection credentials in clear text, not even inside a table in your database. Doing that is the equivalent of writing your password on a sticky note on your desk. Not a good idea.

Instead, the main options are either transforming the passwords to be less identifiable via hashing or storing the information in an external centralized vault.

In MySQL, the passwords are transformed to not be clear text, and several different plugins …

[Read more]
Showing entries 1 to 10 of 287
10 Older Entries »