DBA application skills: how to upgrade InnoDB Plugin

In this article, we will introduce you in detail how to upgrade the dynamic InnoDB Plugin, how to upgrade the InnoDB Plugin with static compilation, and how to convert the compressed tables created in versions earlier than 1.0.2.

I. Overview

Thanks to the plug-in storage engine architecture of MySQL, the upgrade of InnoDB Plugin becomes very simple. You only need to close MySQL, replace the executable files related to the platform, and then restart the server. If you want to upgrade and use an existing database, you must disable MySQL. Otherwise, an error occurs when the new plug-in merges cached data or clears deleted records. If your database does not contain any compressed tables, you can use the latest InnoDB Plugin to process the database after a slow shutdown.

However, if your database contains a compressed table, InnoDB Plugin 1.0.8 is not suitable. Because InnoDB Plugin 1.0.2 introduces an incompatible feature, some compressed tables may need to be rebuilt. For specific conversion steps, see the following document.

Of course, we can use mysqldump or other methods to recreate our database. If our database is small or there are many reference constraints between tables, this is a good method.

It should be noted that if you use InnoDB Plugin 1.0.8 to access your database, you should not try to access them using plug-ins earlier than 1.0.2.

Ii. Update the dynamic InnoDB Plugin

Before closing the MySQL server that contains the InnoDB Plugin, we must enable the slow-down function. The settings are as follows:

 
 

  
  
SET GLOBAL innodb_fast_shutdown=0; 
 
 

In the directory where the MySQL server looks for INS, rename the executable files of the old InnoDB Plugin (ha_innodb_plugin.so or ha_innodb_plugin.dll) to restore them as needed. You can also delete these files. The directory where the plug-in is located is specified by the system variable plugin_dir. The default location is usually in the lib/plugin subdirectory of the directory specified by basedir.

First, we need to download the appropriate package based on our server platform, operating system, and MySQL version. Decompress the package using the corresponding tools. tar is usually used in Linux and Unix systems, and tool software such as WinZip is usually used in Windows systems. Copy the file ha_innodb_plugin.so or ha_innodb_plugin.dll to the directory where the MySQL server looks for plug-ins.

Start the MySQL server. If necessary, you can convert the compressed table according to the method described later.

Iii. Upgrade static compilation of InnoDB Plugin

Just like installing InnoDB Plugin dynamically, We need to slowly shut down the MySQL server. If your MySQL is compiled from the source code and replaced with the InnoDB built in MySQL with the InnoDB in the source code tree, in fact, you will get a special version of mysqld executable file containing InnoDB Plugin.

If you want to upgrade InnoDB Plugin to a dynamic link, you need to uninstall the InnoDB Plugin statically compiled, and then install the pre-compiled InnoDB Plugin as a shared library.

If you want to upgrade from a statically compiled InnoDB Plugin version to another statically compiled InnoDB Plugin version, you must first re-build a mysqld executable file to close the server, replace the mysqld executable file and then start the server.

In any case, if the database contains a compressed table, follow the methods described later to convert the compressed table.

Iv. convert a compressed table created in versions earlier than 1.0.2

Version 1.0.2 of InnoDB Plugin introduces an incompatible compressed table format. This means that some compression tables created in earlier versions of InnoDB Plugin must be re-built with a larger KEY_BLOCK_SIZE before they can be used.

When you upgrade to InnoDB Plugin 1.0.2 or later, if you must keep the existing database, you need to slowly shut down MySQL that is running earlier InnoDB Plugin. Then, determine which compression tables need to be converted, and then use the new InnoDB Plugin to upgrade these tables. The procedure is as follows.

The following describes how to process a compressed table created by InnoDB Plugin 1.0.0 or 1.0.1. Because the new version introduces an incompatible feature, the new InnoDB Plugin will encounter problems when clearing deleted records from the compressed table or merging buffer insert records. However, not all compressed tables need to be rebuilt. Here we will show you how to identify and process these compressed tables that need to be rebuilt.

If the existing database contains tables created by the previous InnoDB Plugin version, you must use the slow command to shut down the MySQL server that uses the old InnoDB Plugin. That is, set global innodb_fast_shutdown = 0 before closing the old InnoDB Plugin instance.

After you start the MySQL server that has upgraded InnoDB Plugin, you must check whether the compression table has been converted. First, enable the strict mode of InnoDB for more detailed error checks: set session innodb_strict_mode = 1. Then, a new table is generated for each compressed table. Follow these steps:

1. List compressed tables:

 
 

  
  
SELECT table_schema, table_name  
  
  
FROM information_schema.tables  
  
  
WHERE engine=’innodb’ AND row_format=’COMPRESSED’; 
 
 

2. For each TABLE, display its definition: show create table table_schema.table_name \ G

3. Execute the create table statement using different TABLE names.

4. If the table is successfully created, delete the new table and continue processing the next compressed table.

5. If the table fails to be created, use a larger KEY_BLOCK_SIZE until it is successfully created. Delete the new TABLE and use the KEY_BLOCK_SIZE of the created TABLE to execute alter table on the original TABLE.

If KEY_BLOCK_SIZE cannot be added to some special tables, you can use a short column index length to recreate the table. This is because the column prefix used for indexing occupies a large amount of space in the B-Tree node. Shorter prefixes reduce the selection of indexes, but the index records are shorter so that they are suitable for the page size. A shorter prefix also means that more index items are suitable for B-Tree nodes, thus improving efficiency.

If there are reference constraints between tables, the above process will be more complex, so it is better to use the old InnoDB Plugin and mysqldump together, then, InnoDB Plugin 1.0.2 is used to load the data into the new database.

V. Summary

This article describes how to upgrade the dynamic InnoDB Plugin and the InnoDB Plugin that is statically compiled, and how to convert the compressed tables created in versions earlier than 1.0.2.