How to batch convert the storage engine of MySQL database tables

Mysql_convert_table_format ToolYesStorage engine for batch conversion of MYSQL database tablesFor RPM installation, it is generally in/usr/bin or/usr/sbin. This article describes how to use mysql_convert_table_format to convert the storage engine of MySQL database tablesMYISAMEngine method. Next let's take a look at this part.

First, execute the following code:

 
 

  
  
[root@test ~]# mysql_convert_table_format  
  
  
 
  
  
/usr/sbin/mysql_convert_table_format version 1.1  
  
  
 
  
  
Conversion of a MySQL tables to other table types. 
 
 

Converts a MYSQL table to another table type, such as INNODB and MEMORY.

Usage:/usr/sbin/mysql_convert_table_format database [tables].

Usage:/usr/sbin/mysql_convert_table_format Database Name and table name, that is, you can specify the full database or full table separately, similar to mysqldump.

If no tables has been specifed, all tables in the database will be converted.

If no table is specified, all tables in the database will be converted.

 
 

  
  
The following options are available:  
  
  
 
  
  
--force  
  
  
 
  
  
Continue even if there is some error. 
 
 

If an error occurs, force the conversion.

 
 

  
  
--help or --Information  
  
  
 
  
  
Shows this help 
 
 

View help.

 
 

  
  
--host='host name' (Default localhost)  
  
  
 
  
  
Host name where the database server is located. 
 
 

The converted host name. The default host name is localhost ).

 
 

  
  
--password='password' 
  
  
 
  
  
Password for the current user. 
 
 

The password of the user currently being converted.

 
 

  
  
--port=port  
  
  
 
  
  
TCP/IP port to connect to if host is not "localhost". 
 
 

If the connection is not based on localhost, specify the port.

 
 

  
  
--socket='/path/to/socket' 
  
  
 
  
  
Socket to connect with. 
 
 

The location where the SOCKET file exists. SOCKET is used for inter-process communication ).

 
 

  
  
--ENGINE='table-type' 
  
  
 
  
  
Converts tables to the given table type (Default: myisam)  
  
  
 
  
  
MySQL 3.23 supports at least the BDB, ISAM and MYISAM types. 
 
 

The storage engine is MYISAM by default. Note that during testing, you need to modify this PERL script. If you convert the database to INNODB, write MYISAM as INNODB in the script.

 
 

  
  
--user='user_name' 
  
  
 
  
  
User name to log into the SQL server. 
 
 

The username of the connection.

 
 

  
  
--verbose  
  
  
 
  
  
This is a test specific option that is only used when debugging a test.  
  
  
 
  
  
Print more information about what is going on.  
  
  
 
  
  
--version  
  
  
 
  
  
Shows the version of this program. 
 
 

View the version information of the tool.

Usage: I use a local machine for testing, OS: RHEL5.2 MYSQL: 5.1.30

Mysql_convert_table_format mo -- user = 'root' -- password = 'mysql' -- force -- socket = '/tmp/mysql. sock'

Converts all tables in the database mo To The MYISAM engine.

View the data file directory.

 
 

  
  
[Root @ test ~] # Ll/opt/mysql-data/mo/
  
  
 
  
  
Total 960
  
  
 
  
  
-Rw ---- 1 mysql 12914 03-02 action_log.frm
  
  
 
  
  
-Rw ---- 1 mysql 8900 03-02 alarm_log.frm
  
  
 
  
  
-Rw ---- 1 mysql 8898 03-02 check_data.frm
  
  
 
  
  
-Rw ---- 1 mysql 8836 03-02 check_data_log.frm
  
  
 
  
  
-Rw ---- 1 mysql 1708 03-02 check_data_log.par
  
  
 
  
  
-Rw ---- 1 mysql 8898 03-02 check_data_tmp.frm
  
  
 
  
  
-Rw ---- 1 mysql 9096 03-02 checkpoint. frm
  
  
 
  
  
...........................................
 
 

Only the structure file of the table is used to check whether the tablespace has grown.

 
 

  
  
[root@test ~]# ll -lh /opt/mysql-data/ib*  
  
  
 
  
  
-rw-rw---- 1 mysql mysql 34M 03-02 23:18 /opt/mysql-data/ibdata1  
  
  
 
  
  
-rw-rw---- 1 mysql mysql 5.0M 03-02 23:18 /opt/mysql-data/ib_logfile0  
  
  
 
  
  
-rw-rw---- 1 mysql mysql 5.0M 12-22 20:19 /opt/mysql-data/ib_logfile1 
 
 

By default, INNODB is not used for 10 M, but now I have changed to 30 M.

Modify Tool

Change MYISAM to INNODB

Vi/usr/local/mysql/bin/mysql_convert_table_format

Change row 26th $ opt_type = "innodb" to $ opt_type = "myisam"

Convert databases with tools

 
 

  
  
[root@test ~]# mysql_convert_table_format mo --user='root' --password='mysql' --socket='/tmp/mysql.sock' 
  
  
 
  
  
[root@test ~]# 
 
 

After successful completion, let's look at the data file:

 
 

  
  
Total 2568
  
  
 
  
  
-Rw ---- 1 mysql 12914 03-02 action_log.frm
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 23:18 action_log.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 action_log.MYI
  
  
 
  
  
-Rw ---- 1 mysql 8900 03-02 alarm_log.frm
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 alarm_log.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 alarm_log.MYI
  
  
 
  
  
-Rw ---- 1 mysql 8898 03-02 check_data.frm
  
  
 
  
  
-Rw ---- 1 mysql 8836 03-02 check_data_log.frm
  
  
 
  
  
-Rw ---- 1 mysql 1708 03-02 check_data_log.par
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # define MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # define myi
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p100.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # p100.MYI
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p101.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # p101.MYI
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p102.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # p102.MYI
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p103.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # p103.MYI
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p104.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # p104.MYI
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p105.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # p105.MYI
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p106.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # p106.MYI
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p107.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # p107.MYI
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p108.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # p108.MYI
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p109.MYD
  
  
 
  
  
-Rw ---- 1 mysql 1024 03-02 check_data_log # P # p109.MYI
  
  
 
  
  
-Rw ---- 1 mysql 0 03-02 check_data_log # P # p10.MYD
 
 

The MYISAM engine has been successfully converted.

This section describes how to use the mysql_convert_table_format tool to batch convert the storage engine of MySQL database tables. We hope this introduction will help you gain some benefits.