CODEWITHSUNDEEP
mysqldatabase
Dhileepan
Dhileepan

Reputation: 2047

How to disable innodb in mysql?

I added the line 'skip_innodb' in my.cnf file to disable innodb and restarted the mysqld. But,It is not effecting in database. Is there any alternative solutions ?

Upvotes: 14

Views: 54808

Answers (5)

Jossef Harush Kadouri
Jossef Harush Kadouri

Reputation: 34257

For mysql 5.6+

Iv'e had difficulties trying the apply the other answers. This is what i found best with mysql 5.6.19

1) edit /etc/mysql/my.cnf

-- IMPORTANT - place these values under [mysqld]

# ... other settings
[mysqld]

innodb=OFF
loose-innodb-trx=0
loose-innodb-locks=0
loose-innodb-lock-waits=0
loose-innodb-cmp=0
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0
loose-innodb-cmpmem=0
loose-innodb-cmpmem-reset=0
loose-innodb-buffer-page=0
loose-innodb-buffer-page-lru=0
loose-innodb-buffer-pool-stats=0
loose-innodb-metrics=0
loose-innodb-ft-default-stopword=0
loose-innodb-ft-inserted=0
loose-innodb-ft-deleted=0
loose-innodb-ft-being-deleted=0
loose-innodb-ft-config=0
loose-innodb-ft-index-cache=0
loose-innodb-ft-index-table=0
loose-innodb-sys-tables=0
loose-innodb-sys-tablestats=0
loose-innodb-sys-indexes=0
loose-innodb-sys-columns=0
loose-innodb-sys-fields=0
loose-innodb-sys-foreign=0
loose-innodb-sys-foreign-cols=0

default-storage-engine=MyISAM
default_tmp_storage_engine=MyISAM

# ...

2) restart mysql

sudo service mysql restart

Source: MySQL docs - 14.1.3 Turning Off InnoDB

What about migration?

if you want to migrate your existing database from InnoDB to MyISAM, the above as-is does not effect existing tables (and will give you runtime errors).

Solution

there is a utility called mysqldump uses store the existing database tables + data into file (SQL script)

1) backup

mysqldump -u [user] -p[pass] [database name] > /tmp/backup.sql

2) change /etc/mysql/my.cnf as shown above

nano /etc/mysql/my.cnf

# ...
[mysqld]

innodb=OFF
loose-innodb-trx=0
loose-innodb-locks=0
# ...

3) restart mysql

sudo service mysql restart

4) reload data from 1st step

mysql -u [user] -p[pass] [database name] < /tmp/backup.sql

Upvotes: 7

bobah
bobah

Reputation: 18864

In MySQL 5.5+ the required configuration fragment would be as follows

[mysqld]
skip-innodb
default-storage-engine = myisam

Upvotes: 4

Omesh
Omesh

Reputation: 29121

Add skip-innodb under [mysqld] in my.cnf and then restart the MySQL server

See mysql log file for the success using tail -100 log_file_name_with_full_path

Verify using following query:

SHOW ENGINES;

Upvotes: 9

JohannesAndersson
JohannesAndersson

Reputation: 4630

Try

innodb=OFF
default-storage-engine=MyISAM

MyISAM is just a example you can choose wathever you want there You can read more here http://www.webhostingtalk.com/showthread.php?t=1052143

Upvotes: 9

Akash KC
Akash KC

Reputation: 16310

If you are using MySql 5.5 or above,

ignore-builtin-innodb

and

default-storage-engine = myisam

under

[mysqld]

in /etc/my.cnf

Upvotes: 35

Related Questions