在生产环境中由于业务的特殊要求可能需要修改MySQL数据表的默认引擎。
修改方法一：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
mysql> show tables; +------------------+ | Tables_in_hitest | +------------------+ | ces | | test | +------------------+ 2 rows in set (0.00 sec) mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ####################################修改前、后#################################### mysql> ALTER TABLE test ENGINE = MyISAM; Query OK, 20 rows affected (1.15 sec) Records: 20 Duplicates: 0 Warnings: 0 mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) |
修改方法二、
①：首先使用mysqldump对数据库、表备份；
②：通过nohup sed -e 's#MyISAM#InnoDB#g' xx.sql > xxx.sql &
修改方法三、
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
[root@Master-Mysql ~]# find / -name "mysql_convert_table_format" /usr/local/mysql/bin/mysql_convert_table_format [root@Master-Mysql ~]# echo 'export LC_ALL=C' >> /etc/profile [root@Master-Mysql ~]# . /etc/profile [root@Master-Mysql ~]# yum install perl-DBI perl-DBD-MySQL -y mysql> show create table hello\G *************************** 1. row *************************** Table: hello Create Table: CREATE TABLE `hello` ( `REL_ID` bigint(12) NOT NULL AUTO_INCREMENT COMMENT 'id', `TITLE` varchar(255) NOT NULL COMMENT 'biaoti', PRIMARY KEY (`REL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec) [root@Master-Mysql ~]# /usr/local/mysql/bin/mysql_convert_table_format hitest hello --socket=/tmp/mysql.sock mysql> use hitest mysql> show create table hello\G *************************** 1. row *************************** Table: hello Create Table: CREATE TABLE `hello` ( `REL_ID` bigint(12) NOT NULL AUTO_INCREMENT COMMENT 'id', `TITLE` varchar(255) NOT NULL COMMENT 'biaoti', PRIMARY KEY (`REL_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) [root@Master-Mysql ~]# sed -i "s#\$opt_engine=\"MYISAM\"\;#\$opt_engine=\"InnoDB\"\;#g" /usr/local/mysql/bin/mysql_convert_table_format [root@Master-Mysql ~]# /usr/local/mysql/bin/mysql_convert_table_format hitest hello --socket=/tmp/mysql.sock mysql> use hitest mysql> show create table hello\G *************************** 1. row *************************** Table: hello Create Table: CREATE TABLE `hello` ( `REL_ID` bigint(12) NOT NULL AUTO_INCREMENT COMMENT 'id', `TITLE` varchar(255) NOT NULL COMMENT 'biaoti', PRIMARY KEY (`REL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) |
申明：本文由BYRD原创(基于Mysql5.6.16)，未经许可禁止转载！SourceByrd's Weblog-https://note.t4x.org/database/modify-default-mysql-engine/ SourceByrd's Weblog-https://note.t4x.org/database/modify-default-mysql-engine/
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
