Shulou(Shulou.com)05/31 Report--

DBA how to upgrade InnoDB Plugin, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

We will show readers in detail how to upgrade dynamic InnoDB Plugin and statically compiled InnoDB Plugin, as well as how to convert compression tables created before version 1.0.2.

I. Overview

Thanks to MySQL's plug-in storage engine architecture, upgrading InnoDB Plugin is as simple as shutting down MySQL, replacing platform-related executables, and then restarting the server. If you want to upgrade and use an existing database, you must close MySQL, otherwise the new plug-in will make an error when merging cached insert data or emptying deleted records. If you don't have any compressed tables in your database, you won't have a problem dealing with the database with the latest InnoDB Plugin after a slow shutdown.

However, InnoDB Plugin 1.0.8 is not appropriate if you have compressed tables in your database. Because version 1.0.2 of InnoDB Plugin introduces an incompatible feature, some compressed tables may need to be rebuilt, as detailed in the conversion steps below.

Of course, we can use mysqldump or other methods to rebuild our database. This is a better approach if our database is small, or if there are many reference constraints between tables.

It is important to note that if you use InnoDB Plugin 1.0.8 to access your database, don't try to access them with plug-ins prior to 1.0.2.

Second, upgrade dynamic InnoDB Plugin

Before shutting down the MySQL server that contains InnoDB Plugin, we must enable the slow shutdown feature with the following settings:

Double-click the code to select all 1SET GLOBAL innodb_fast_shutdown=0

Rename the executables of the old InnoDB Plugin (ha_innodb_plugin.so or ha_innodb_plugin.dll) in the directory where the MySQL server is looking for plug-ins so that they can be restored if needed. After that, we 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 according to our server platform, operating system, and MySQL version. Then use the corresponding tools to decompress, in Linux and Unix systems usually use tar, in Windows systems usually use WinZip and other tool software. Copy the file ha_innodb_plugin.so or ha_innodb_plugin.dll to the directory where the MySQL server is looking for the plug-in.

Start the MySQL server. If necessary, you can convert the compressed table as described later.

Upgrade statically compiled InnoDB Plugin

Just like dynamically installing InnoDB Plugin, we need to slowly shut down the MySQL server. If your MySQL is compiled from the source code and replaces MySQL's built-in InnoDB with the InnoDB Plugin in the source tree, you actually get a special version of the mysqld executable that contains InnoDB Plugin.

If you want to upgrade to a dynamically linked InnoDB Plugin, you need to uninstall the statically compiled InnoDB Plugin before installing the precompiled InnoDB Plugin released as a shared library.

If you want to upgrade from one statically compiled version of InnoDB Plugin to another statically compiled version of InnoDB Plugin, you must first rebuild a mysqld executable, shut down the server, then replace the mysqld executable, and then start the server.

In any case, if the database contains compressed tables, be sure to convert the compressed tables as described later.

IV. Convert compression tables created before 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 rebuilt with larger KEY_BLOCK_SIZE before they can continue to be used.

When upgrading to InnoDB Plugin 1.0.2 or later, you need to slowly shut down MySQL that is running an earlier version of InnoDB Plugin if you must maintain the existing database. After that, determine which compressed tables need to be converted, and then upgrade them with the new version of InnoDB Plugin. The specific operation is shown below.

The following describes how to handle compression tables created by version 1.0.0 or version 1.0.1 of InnoDB Plugin. Due to the introduction of an incompatible feature in the new version, the new InnoDB Plugin will encounter problems when clearing deleted records from the compressed table or merging buffered inserted records. However, not all compressed tables need to be rebuilt. Here we will show the reader how to identify and handle these compressed tables that need to be rebuilt.

If the existing database contains tables created by previous versions of InnoDB Plugin, you must use a slow shutdown MySQL server that uses the old InnoDB Plugin. That is, before closing the old instance of InnoDB Plugin, you need to set up SET GLOBAL innodb_fast_shutdown=0.

After starting the MySQL server that has upgraded InnoDB Plugin, you must check that the compressed table has been converted. First, enable the strict mode of InnoDB for more detailed error checking: SET SESSION innodb_strict_mode = 1. Then, a corresponding new table is generated for each compressed table. We can do this through the following steps:

1. List the compression table:

Double-click the code to select all

one

two

three

four

five

SELECT table_schema

Table_name

FROM information_schema.tables

WHERE engine='innodb' A

ND row_format='COMPRESSED'

two。 For each table, display the definition of its table: SHOW CREATE TABLE table_schema.table_nameG

3. Execute the CREATE TABLE statement with a different table name.

4. If the table is created successfully, delete the newly created table and proceed to the next compressed table.

5. If the table creation fails, try using a larger KEY_BLOCK_SIZE until it succeeds. Then delete the newly created table and use the KEY_BLOCK_SIZE that just successfully executed CREATE TABLE to ALTER TABLE the original table.

If some special tables do not allow the addition of KEY_BLOCK_SIZE, you can rebuild the table with a shorter column index length. This is because the column prefix used for indexing takes up a lot of space in the B-tree node. Shorter prefixes reduce index selection, but index records are shorter so that they fit the page size. A shorter prefix also means that more index items are suitable for B-tree nodes, thus improving efficiency.

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

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.