How to convert all InnoDB to MyISAM & disable InnoDB ?

Bidi

Well-Known Member
Oct 3, 2012
101
6
68
Romania, Transilvania
cPanel Access Level
DataCenter Provider
Hello guys,

I was searching in the last 2 days to convert all my innodb tables,databases to myisam because is the secand time i have problems witch innodb and i`m not able to fix the tables/databases, so i whant to stop using innodb at all for the entire server. I didnt found nothink clearly, and somehow i whant to be this post for those who search to do this.

I know fixing/recovering innodb is pain so i whant to quit using innodb.

On my.cnf i got this

Code:
[mysqld]
log-error=/var/lib/mysql/d9.**********.ro.err
performance-schema=0

default-storage-engine=MyISAM

local-infile=0
#innodb_force_recovery=6
#innodb_purge_threads=0

max_connections=250
max_user_connections=20
wait_timeout = 300
connect_timeout = 10

max_allowed_packet=500M

join_buffer_size=2M
sort_buffer_size=2M

thread_cache_size = 16
table_open_cache = 2000
table_definition_cache = 1024

query_cache_type = 1
query_cache_size = 30M
query_cache_limit = 1M

tmp_table_size=64M
max_heap_table_size=64M

#tmpdir = "/home/mysqltmp/"
socket=/var/lib/mysql/mysql.sock

open_files_limit=50000

key_buffer_size = 30M

innodb_buffer_pool_size = 1G
innodb_file_per_table=1
innodb_stats_on_metadata=0

slow-query-log=1
long-query-time=1
slow-query-log-file="/home/slow.log"
max_allowed_packet=314572800
But on the server i sow lots of innodb tables, databases...etc so i whant to convert all to myisam and disable innodb .

Is there someone who knows how to do this and not lose any tables/databases ?

Thank you.
 

Jcats

Well-Known Member
PartnerNOC
May 25, 2011
806
156
168
New Jersey
cPanel Access Level
DataCenter Provider
You should check out:


Definitely make a backup of your databases prior to running.

In most cases when you have table corruption its because your server has ran out of memory and/or disk space which can cause MySQL to crash or in the case of memory possibly be killed by the kernel so make sure you have enough resources available. If your server has ever lost power or was hard booted, this could cause corruption.
 

Bidi

Well-Known Member
Oct 3, 2012
101
6
68
Romania, Transilvania
cPanel Access Level
DataCenter Provider
Hello,
I will try, and with the settings in my.cnf got any idea ? how to disable the innodb at all ?

The first time when i had problems was because of an customer, i dont know how but he had some script, he made in one night sompting like 50GB database and the server partition get full and crashed, the customer account had limit 10GB for hes account, but the database how he manage to be 50gb i dont know and never finded

The secand time was my mistake, accidently pulled the server power cable thinking is another server witch i was not using it any more.
 

Jcats

Well-Known Member
PartnerNOC
May 25, 2011
806
156
168
New Jersey
cPanel Access Level
DataCenter Provider
This should work then:


adding skip-innodb to your /etc/my.cnf

You already have

default-storage-engine=MyISAM

in your my.cnf so should be good to go after all db's have been converted.
 
  • Like
Reactions: Bidi

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,910
2,213
363
Hello @Bidi,

The following resource is available if you'd like information on how to repair InnoDB prior to converting all databases/tables to MyISAM (should you decide to go that route):


Thank you.
 
  • Like
Reactions: Bidi