In InnoDB, the undo log and the redo log are two indispensable components that play a vital role in maintaining data integrity and ensuring transactional consistency. Both logs are crucial in the ACID (Atomicity, Consistency, Isolation, Durability) properties of database systems. Additionally, they are essential for the Multi-Version Concurrency Control (MVCC) mechanism. In this blog post, we will delve into the differences between the InnoDB undo log and redo log, exploring their significance and providing code examples to illustrate their usage.

InnoDB Undo Log

The undo log, also known as the rollback segment, is a crucial part of the InnoDB storage engine. Its primary purpose is to support transactional consistency and provide the ability to …

This blog was originally published in July 2017 and was updated in August 2023.

It’s a pretty common question around here, so let’s see what we can do about that.

So, What is MySQL Partitioning?

Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables but still gets treated as a single table by the SQL layer.

When partitioning in MySQL, it’s a good idea to find a natural partition key. You want to ensure that table lookups go to the correct partition or group of partitions. This means that all SELECT, UPDATE, and DELETE should include that column in the WHERE clause. Otherwise, the storage engine does a scatter-gather and queries ALL partitions in a UNION that is not concurrent.

Generally, you must add the partition key into the primary key along …

Announcing the 2023-2024 Oracle MySQL ACE Program Members

In this article, I want to discuss a couple of pretty new features in MySQL 8.0 — and an older one. Maybe these are minor features you are not aware of, and maybe not so relevant, to be honest. But it is worth providing a quick overview, showing how they work, and how they could be useful in some cases.

All refer to the invisibility of something:

• Invisible columns
• Generated invisible primary keys
• Invisible indexes

Let’s take a look.

Invisible columns

The invisible columns feature has been deployed since version 8.0.23. What is an invisible column? It’s basically a regular column of a table with its own name and data type. It is treated and updated as any other regular column, with the only difference being that it is invisible to the application. In other words, it can be accessed only in the case it is explicitly addressed in your SELECT; otherwise, it is …

The first MySQL Innovation Release is out ! Thank you to all contributors !

The latest release of MySQL (July 18th, 2023) marks a new era for the most popular Open Source database, with the very first Innovation Release (8.1.0). More will follow before MySQL’s very first LTS Release sees the light of day.

This new Innovation Release already contains contributions from our great Community. MySQL 8.1.0 contains patches from Meta, Allen Long, Daniël van Eeden, Brent Gardner, Yura Sorokin (Percona) and Kento Takeuchi.

Let’s have a look at all these contributions:

MySQL Connector Python

• #93643 – Contribution: fix recv compress bug – Allen Long
…

Learn how to build a contacts web app with Node, Express, and PlanetScale.

The MySQL 5.7 End of Life is now two months away. Those of you who have waited may be wondering which is the best way to upgrade – an In-Place or Logical upgrade. An In-Place upgrade is where the binaries for 5.7 are replaced with the binaries for 8.0, and the upgrade is made to an existing instance. The Logical option is a new server that is loaded with the old data from a backup.

Generally, the In-Place upgrade will be faster as you do not have to perform a backup on the old instance and then run a restore on the new platform. Those with very large instances should choose this option.  And remember you should be using the four-byte UTF8MB4 character set, which most likely means you will be using more disk space, so arrange for the extra space BEFORE starting the upgrade.

Logical …

Restoring a MySQL database backup is a crucial task that can sometimes be time-consuming, especially for large databases. Monitoring the progress of the restore process is essential to estimate completion time and ensure everything is proceeding smoothly.

In this blog post, we will explore two distinct and effective methods to calculate the percentage progress of the MySQL restore process.

• Linux native Input/Output (I/O) statistics
• Pipe viewer utility

By following these approaches, we can effectively monitor the restoration process and manage your MySQL database restoration efficiently.

Monitoring MySQL database restore progress using Linux native Input/Output (I/O) statistics

We would use the /proc/<pid>/io file, which contains the IO statistics for each running process. For this particular case, we would use the pid of the MySQL restore process, which can provide …

One of the many reasons you should subscribe to the OpenLampTech developer newsletter is for the selective few text-based developer interviews I publish. I learn so much from the developers who participate in them for the publication and I know you will too. Read those I have published so far in this article. Enjoy the world of PHP and SQL from the perspective of other knowledgeable developers!

Custom WooCommerce and Shopify Solutions

Discover useful WooCommerce and Shopify custom solutions for your online store today at affordable prices!

Learn More

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.

…