13

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

cewebugil
  • 705
  • 3
  • 8
  • 12

4 Answers4

12

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
Insyte
  • 9,314
  • 2
  • 27
  • 45
7

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 !!!

RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • Going 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 https://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field – Antony Gibbs Aug 02 '18 at 21:07
  • Going 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 https://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field – Antony Gibbs Aug 02 '18 at 21:07
0

For those who still having this issue, you can follow this way to do it, I found this answer in a website. It helps me a lot:

shell> mysql -u username -p -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=MYISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'" | tail -n+2 >> alter.sql

Update username and db_name values with your own values.

Afet executing the script, it will save a file under name: alter.sql Open the file and execute the content on your phpmyadmin or mysql command line.

Cheers!

mayoralito
  • 1
  • 1
-1

I prefer one-liners for this type of stuff. This is a one-liner version of the most accepted answer.

This works if you have your MySQL username and password set in ~/.my.cnf.

D=your_db; for t in $(mysql $D -B -e 'SHOW TABLES'); do mysql $D -e "ALTER TABLE $t ENGINE=MyISAM"; done
the
  • 468
  • 8
  • 23