As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Loading...

MySQL Retrospective – ISAM & MyISAM Log

Let’s stay a bit longer with MySQL 3.2x to advance the MySQL Retrospective in anticipation of the 30th Anniversary.

The idea of this article was suggested to me by Daniël van Eeden.

Did you know that in the early days, and therefore still in MySQL 3.20, MySQL used the ISAM storage format? IBM introduced the Indexed Sequential Access Method in the 1960s as part of its Information Management System (IMS). The IMS database management system became one of the first widely used hierarchical databases, and ISAM was a key part of its architecture.

It was in 2001 with MySQL 3.23 that MyISAM replaced ISAM as the default storage engine, bringing enhanced features such as indexing improvements.

Additionally, did you also know that before binary logs there was another method that could have been used to recover data and perform a very basic point-in-time recovery? And the funny fact is that the feature is still available in MySQL 9!

ISAMLOG

It’s possible to start mysqld with the --log-isam option to log all MyISAM changes into a file. This option was not even covered in the MySQL Study Guide.

This is an example on MySQL 3.20:

The as you could see on the illustrations above, there is a command line utility that can be used with those files: isamlog, that was later renamed as myisamlog and is still present in the latest MySQL release:

ISAMLOG was introduced as a mechanism to log all changes made to ISAM tables, enabling administrators to recover lost data or debug issues. In the late 90’s, this provided a practical solution to challenges like data corruption, accidental deletions, and system crashes.

ISAMLOG’s core functionality revolved around capturing all modifications to ISAM tables. It sequentially logged operations such as inserts, updates, and deletes into a dedicated log file. This log file could then be replayed to reconstruct the state of a table up to a specific point in time, aiding in data recovery and debugging.

With MySQL 3.23, MyISAMLOG was introduced and although MyISAMLOG inherited much of its predecessor’s functionality, it still retained a manual recovery process, which began to feel outdated as database systems became more complex. The advent of binary logs marked the beginning of the end for MyISAMLOG, as users demanded more robust and efficient logging systems.

The real end of MyISAMLOG actually came in 2001, when mysqlbinlog was introduced, although it’s still distributed today.

Let’s test it with MySQL 9.1 just for fun and maybe a bit of nostalgy…

First we need to start MySQL with the log-isam option:

Now that we have a MyISAM table with some records, let’s empty it:

We can see that we have our mysiam.log file with some content in it:

Let’s see if we can recover the data from that file. We first delete and rectreate the table:

And now we use myisamlog file:

And we can see that the data was recovered:

While ISAMLOG may no longer constitute an element of MySQL’s contemporary toolkit, its legacy persists as a notable milestone in the evolution of the database. By revisiting tools such as ISAMLOG, one gains a heightened appreciation for the challenges and achievements that have contributed to shaping MySQL into the formidable entity it is today.

And don’t forget that you should not use MyISAM anymore and prefere the use of InnoDB.

Why MyISAM should no longer be utilized

  • Limited Concurrency: MyISAM systems typically use table-level locking, which reduces concurrency and can create bottlenecks in multi-user environments.
  • No Support for Transactions: MyISAM does not have transaction support, meaning changes cannot be rolled back or committed atomically, which can lead to data inconsistencies in case of system crashes.
  • Data Integrity: MyISAM does not include features like foreign key constraints or automatic referential integrity, which makes it harder to enforce complex relationships between data.
  • No Crash Recovery: MyISAM does not have built-in mechanisms for crash recovery, so if a system crashes, data corruption can occur, requiring manual repairs.
  • Lack of Advanced Features: MyISAM doesn’t provide more advanced features like row-level locking, indexing optimizations, … the MySQL effort is focused in InnoDB.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Reply

Your email address will not be published. Required fields are marked *