«

»

Jan 23

Proper Method To Change Default Mysql Engine Permanently

Here the mysql engine is set to InnoDB by default and I want to change it to MyISAM for long run. It was the old way setting parameter “skip-innodb” and commenting “innodb_file_per_table=1” in my.cnf file. Additionally we used to execute mysql query “SET storage_engine=MYISAM;”. Now a days, the above changes wont work and perhaps the mysql server doesn’t come back after initiating a restart since the changes made. The proper method is set the variable “default-storage-engine = MyISAM” like my mysql configuration as follows:


<> ~> cat /etc/my.cnf
[mysqld]
skip-innodb
#innodb_file_per_table=1
default-storage-engine = MyISAM
#innodb_force_recovery = 4

Nothing else you need to do 🙂
Now restart mysql server and check the default engine is set to what


<> ~> /etc/init.d/mysql restart
Shutting down MySQL. [ OK ]
Starting MySQL. [ OK ]


mysql> show engines;
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| InnoDB | NO | Supports transactions, row-level locking, and foreign keys | NULL | NULL | NULL |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.00 sec)

If you want to enable InnoDB as well along with default MyISAM engine, then comment out the variable skip-innodb and remove comment before variable innodb_file_per_table=1

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>