前言

由于历史原因，Mysql数据库中存在大量非Innodb存储引擎的表，现需要把这些表的存储引擎批量修改成Innodb存储引擎。在此记录以解不时之需！

整体思路

通过拼接SQL语句把需要执行的所有语句输出到操作系统文件中，执行这个文件即可完成批量修改。

处理过程

本文主要在测试环境中模拟批量处理过程。

创建5张非Innodb存储引擎的表

mysql> create table test1.t1(id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test1.t2(id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test2.t3(id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test2.t4(id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test2.t5(id int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
复制

查询非Innodb存储引擎的表

mysql> select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where ENGINE!='innodb'and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys');
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| test1        | t1         |
| test1        | t2         |
| test2        | t3         |
| test2        | t4         |
| test2        | t5         |
+--------------+------------+
5 rows in set (0.00 sec)
复制

拼接批量修改Innodb存储引擎的sql

真实环境中有大量非Innodb存储引擎的表，所以需要拼接sql批量执行。

SELECT CONCAT('ALTER TABLE ',table_schema,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
from information_schema.tables 
where   ENGINE!='innodb'
and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys');
复制

执行结果如下：

mysql> SELECT CONCAT('ALTER TABLE ',table_schema,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
    -> from information_schema.tables 
    -> where   ENGINE!='innodb'
    -> and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys');
+-------------------------------------+
| sql_statements                      |
+-------------------------------------+
| ALTER TABLE test1.t1 ENGINE=InnoDB; |
| ALTER TABLE test1.t2 ENGINE=InnoDB; |
| ALTER TABLE test2.t3 ENGINE=InnoDB; |
| ALTER TABLE test2.t4 ENGINE=InnoDB; |
| ALTER TABLE test2.t5 ENGINE=InnoDB; |
+-------------------------------------+
5 rows in set (0.00 sec)
复制

执行结果输出到/tmp/result.sql文件

SELECT CONCAT('ALTER TABLE ',table_schema,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
INTO OUTFILE '/tmp/result.sql'
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
from information_schema.tables 
where   ENGINE!='innodb'
and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys');
复制

执行结果如下：

mysql> SELECT CONCAT('ALTER TABLE ',table_schema,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
    -> INTO OUTFILE '/tmp/result.sql'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY ''
    -> LINES TERMINATED BY '\n'
    -> from information_schema.tables 
    -> where   ENGINE!='innodb'
    -> and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys');
Query OK, 5 rows affected (0.00 sec)

复制

查看/tmp/result.sql文件内容

[root@node2 ~]# cat /tmp/result.sql 
ALTER TABLE test1.t1 ENGINE=InnoDB;
ALTER TABLE test1.t2 ENGINE=InnoDB;
ALTER TABLE test2.t3 ENGINE=InnoDB;
ALTER TABLE test2.t4 ENGINE=InnoDB;
ALTER TABLE test2.t5 ENGINE=InnoDB;
复制

执行/tmp/result.sql

mysql> source /tmp/result.sql
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
复制

再次查询非innodb存储引擎的表

mysql> select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where ENGINE!='innodb'and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys');
Empty set (0.00 sec)

mysql> 
复制

数据库中已经不存在非innodb存储引擎的表了。

碰到的问题

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> SELECT CONCAT('ALTER TABLE ',table_schema,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
    -> INTO OUTFILE '/tmp/result.sql'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY ''  
    -> LINES TERMINATED BY '\n'
    -> from information_schema.tables 
    -> where   ENGINE!='innodb'
    -> and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys');
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
复制

解决办法：
mysql> show variables like ‘secure_file_priv’;
±-----------------±----------------------+
| Variable_name | Value |
±-----------------±----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
±-----------------±----------------------+
1 row in set (0.00 sec)
可以把文件输出到secure_file_priv参数指定的位置(本例是/var/lib/mysql-files/ 目录)，如果文件要输出到任意位置，需要设置secure_file_priv参数。设置方法：
在/etc/my.cnf中加入secure_file_priv=""，重启mysql数据库即可。

关于secure_file_priv参数的说明：
secure_file_priv是‌MySQL中用来限制文件上传和下载操作的参数。‌
当secure_file_priv为空时，表示可以在任意路径上传文件；
当secure_file_priv设置为特定路径时，表示只能在特定路径进行文件上传和下载；
当secure_file_priv为NULL时，表示不允许在任何路径上传文件。‌

官网上关于secure_file_priv参数的说明：

注意：修改secure_file_priv参数需要重启数据库，如果不能重启数据库，可以把文件输出到secure_file_priv参数指定的位置或者用下面这种方法输出到其他位置：

mysql> pager cat >/tmp/a.sql
PAGER set to 'cat >/tmp/a.sql'
mysql> SELECT CONCAT('ALTER TABLE ',table_schema,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
    -> from information_schema.tables 
    -> where   ENGINE!='innodb'
    -> and TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys');
5 rows in set (0.00 sec)

mysql> 
mysql> \q
Bye
[root@node2 ~]# cat /tmp/a.sql
+--------------------------------------+
| sql_statements                       |
+--------------------------------------+
| ALTER TABLE test1.t6 ENGINE=InnoDB;  |
| ALTER TABLE test1.t7 ENGINE=InnoDB;  |
| ALTER TABLE test2.t8 ENGINE=InnoDB;  |
| ALTER TABLE test2.t9 ENGINE=InnoDB;  |
| ALTER TABLE test2.t10 ENGINE=InnoDB; |
+--------------------------------------+
复制

总结

虽然很简单的功能，但是很实用。希望大家都能有所收获，谢谢！