How to convert all InnoDB to MyISAM & disable InnoDB ?

[Bidi]

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

[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]

You should check out:

MySQL - Convert all Tables of one or more Database(s) from MyISAM to InnoDB and vice-versa

Have you ever needed to convert one, some or all the tables of a MySQL Database from MyISAM to InnoDB or the other way around? Here's a couple queries you ca

www.ryadel.com

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]

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]

Depends which version of MySQL you are running.

MySQL :: MySQL 5.6 Reference Manual :: 14.1.5 Turning Off InnoDB

dev.mysql.com

MySQL :: MySQL 5.7 Reference Manual :: 14.1.5 Turning Off InnoDB

dev.mysql.com

 

[Bidi]

mysql Ver 15.1 Distrib 10.3.18-MariaDB, for Linux (x86_64) using readline 5.1

 

[Jcats]

This should work then:

mysqld Options

Lists of all the options for mysqld.

mariadb.com

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.

 

[cPanelMichael]

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):

Tutorial - InnoDB Corruption Repair Guide

InnoDB Corruption Repair Guide Understanding that our ability as technicians to responsibly assist with InnoDB corruption is very limited, I wanted to offer a basic guide that goes over some of the methods you can use to deal with some of the...

forums.cpanel.net

Thank you.