Restoring a single MyISAM database under MySQL 8 from a physical backup-CodePudding

this isn't my exact situation but close enough for description.

On a MySQL 8.0.28 instance on an ubuntu 22.08 server, I have a database 'important' that contains only MyISAM tables. Within MySQL I:

DROP DATABASE important;

then continue to 'do stuff' with other databases etc. Some time later, I decide I want that 'important' database back (without restoring other databases). Luckily I have a physical backup of the MySQL data folder, specifically including the 'important' directory.

If I stop the server, copy the 'important' directory back in place, then restart the server I hoped (and was the case under MySQL 5) that the database would re-appear, but it doesn't.

As far as I can sell, MySQL 5 used to physically scan the directory to look for databases whereas MySQL 8 seems to have a separate idea of what databases exist. To clarify, I am only using MySQL 8.0.28 in this scenario - the backup was made under the same version as I'm restoring to.

My question is, how can I make this database reappear? Perhaps that's "How can I make MySQL 8 rescan its database of databases"?

CodePudding user response：

I think you're in uncharted territory.

It's true that in MySQL 5.x, you could create a schema simply with mkdir, creating a subdirectory under the data directory. Within that directory, each MyISAM table has three files: tablename.MYD, tablename.MYI, and tablename.frm. The frm file has metadata about the table and its columns, so the MyISAM storage engine could discover new tables simply by reading their respective frm file.

This was not possible for InnoDB in 5.x, because InnoDB had an internal data dictionary stored in the ibdata1 tablespace in the data directory. The data dictionary had to maintain duplicate metadata about InnoDB tables.

In MySQL 8.0, they eliminated frm files completely. Tables of all storage engines are managed by the InnoDB data dictionary. There is no interface to the data dictionary besides DDL statements like CREATE/ALTER/DROP. There are several benefits to this change.

This means you can't use external shell commands like mkdir and cp to create schemas and tables. You have to use DDL to communicate to the data dictionary. It's not only stored on disk in the InnoDB tablespace, but it's also cached as objects in the MySQL Server process. Using DDL updates both.

I have not tried this with MyISAM tables (I haven't used MyISAM tables if I can avoid it for over ten years), but in theory you could use CREATE SCHEMA and CREATE TABLE to recreate empty tables that match the definition of the tables you're restoring. Then outside of MySQL, use cp to copy the tables you want to restore over the empty data and index files. I would shut down MySQL Server before doing this, to hopefully avoid any race conditions or conflicting with file caches.

I have no idea if that will work. I would test these steps first on a test instance (not your production instance).