Simpler way to convert all tables from InnoDB to MyISAM
37,466
Solution 1
I'm not aware of any way to do this in mysql itself, but a simple shell script will do the job:
TABLES=$(mysql -pXXXXXXX -uXXXXXXX --skip-column-names -B -D $DB -e 'show tables')
for T in $TABLES
do
mysql -pXXXXX -uXXXXX -D $DB -e "ALTER TABLE $T ENGINE=MYISAM"
done
Solution 2
You can use MySQL to script it and execute it:
This will convert every InnoDB table in database dbname
to MyISAM
CONVERT_SCRIPT=Convert_dbname_InnoDB_to_MyISAM.sql
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') FROM information_schema.tables WHERE table_schema='dbname' AND engine='InnoDB';" > ${CONVERT_SCRIPT}
mysql -u... -p... -A < ${CONVERT_SCRIPT}
This will convert every InnoDB table to MyISAM
CONVERT_SCRIPT=Convert_InnoDB_to_MyISAM.sql
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') FROM information_schema.tables WHERE engine ='InnoDB';" > ${CONVERT_SCRIPT}
mysql -u... -p... -A < ${CONVERT_SCRIPT}
If you do not want the conversion of the tables to be replicated to Slaves, just put SET SQL_LOG_BIN=0;
as the first line. That way, you can test the conversion in a Master/Slave setup by converting only the Slave first and then the Master later.
This will convert every InnoDB table in database dbname
to MyISAM and not replicate to other servers
CONVERT_SCRIPT=Convert_dbname_InnoDB_to_MyISAM.sql
echo "SET SQL_LOG_BIN=0;" > ${CONVERT_SCRIPT}
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') FROM information_schema.tables WHERE table_schema='dbname' AND engine='InnoDB';" >> ${CONVERT_SCRIPT}
mysql -u... -p... -A < ${CONVERT_SCRIPT}
This will convert every InnoDB table to MyISAM and not replicate to other servers
CONVERT_SCRIPT=Convert_InnoDB_to_MyISAM.sql
echo "SET SQL_LOG_BIN=0;" > ${CONVERT_SCRIPT}
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') FROM information_schema.tables WHERE engine ='InnoDB';" >> ${CONVERT_SCRIPT}
mysql -u... -p... -A < ${CONVERT_SCRIPT}
Give it a Try !!!
Related videos on Youtube
Author by
J_Kay
Updated on September 17, 2022Comments
-
J_Kay over 1 year
Previously, I use this:
USE dbname; ALTER TABLE tablename ENGINE=MYISAM;
I'm looking for simpler way to convert all tables in a database, rather than writing every table name one by one
-
Tom O'Connor about 13 yearsLearn to use scripting to accomplish your goals.
-
-
Antony Gibbs over 5 yearsGoing from MyIsam2InnoDB in real world you'll probably want
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
somewhere to bypass the no_zero_date since mysql 5.7 - Source stackoverflow.com/questions/9192027/… -
realtebo almost 5 yearshow to disable foreign key check ?