I know I can issue an alter table individually to change the table storage from MyISAM to InnoDB.
I am wondering if there is a way to quickly change all of them to InnoDB?
Run this SQL statement (in the MySQL client, phpMyAdmin, or wherever) to retrieve all the MyISAM tables in your database.
Replace value of the name_of_your_db variable with your database name.
SET @DATABASE_NAME = 'name_of_your_db';
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;
Then, copy the output and run as a new SQL query.
That worked nicely! I’ve put it into an example shell script here: shrubbery.mynetgear.net/c/display/W/… – Joshua DavisJun 28, 2012 at 14:46 5 “#1267 illegal mix of collations…” I’m getting this error, it doesn’t work – Rápli AndrásNov 8, 2013 at 13:37 1 Just out of curiosity, what’s the point of the explicit descending ordering? (ORDER BY table_name DESC) – rinogoMar 7, 2014 at 19:55 14 If your dealing with multiple databases and don’t want to change the database everytime, change CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') to CONCAT('ALTER TABLE ',@DATABASE_NAME,'.', table_name, ' ENGINE=InnoDB;') – ub3rst4rSep 17, 2014 at 3:23 3 If you want to get the the statements for all databases (except the MySQL system databases): SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql') AND engine = 'MyISAM' AND table_type = 'BASE TABLE' ORDER BY table_schema,table_name – dr fu manchuSep 22, 2017 at 2:15
<?php
// connect your database here first
//
// Actual code starts here
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND ENGINE = 'MyISAM'";
$rs = mysql_query($sql);
while($row = mysql_fetch_array($rs))
{
$tbl = $row[0];
$sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
mysql_query($sql);
}
?>
t probably would be better to limit this to the database you’re focusing on. Add a ” AND TABLE_SCHEMA = ‘dbname’, otherwise this can/will change all the internet MySQL tables to innodb as well (when some of them should be memory) – NoodlesMay 27, 2014 at 2:20 10 PHP’s mysql_* interface is deprecated and removed from ver 7. Don’t use this code as is. – Rick JamesJan 13, 2017 at 20:42 4 @GajendraBang – Yes, the answer as valid when presented. But for newcomers, it is no longer valid. My intent was to warn against using it as is. – Rick JamesJan 19, 2017 at 22:47 1 The question does not mention PHP whatsoever – phil294Apr 18, 2019 at 21:53 2 How is the most recent edit not flagged? The MySQL portion is a direct copy of Will Jones’ answer. Look at each edit history to find that Will’s answer appeared in 2013 while this answer appeared in 2019. As a result, the integrity of this question is compromised. – rmutalikFeb 19, 2020 at 18:52
@GajendraBang – Yes, the answer as valid when presented. But for newcomers, it is no longer valid. My intent was to warn against using it as is. – Rick JamesJan 19, 2017 at 22:47 1 The question does not mention PHP whatsoever – phil294Apr 18, 2019 at 21:53 2 How is the most recent edit not flagged? The MySQL portion is a direct copy of Will Jones’ answer. Look at each edit history to find that Will’s answer appeared in 2013 while this answer appeared in 2019. As a result, the integrity of this question is compromised. – rmutalikFeb 19, 2020 at 18:52 As per @rmutalik – the SQL portion of this answer is copied from Will Jones. Without attribution. Anyone who uses that portion should upvote Will Jones’ answer, not this one. – ToolmakerSteveFeb 27, 2020 at 21:10
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'mydatabase';
Works like a charm.
This will give you list of all tables with the alter queries that you can run in a batch
After running this you first need to execute the following query: USE databasename; Then you can use the queries that the above script gives. – gijs007Jul 14, 2015 at 1:56
How do you run a batch? – Marc AlexanderJun 22, 2017 at 1:45 The above query will give you alter table queries. just select them all and execute them together. or divide them in groups of 50 queries and run them if there are too many tables in the resultset – Omkar KulkarniJun 22, 2017 at 15:35
It’s working even on 2018 and on Percona Cluster. If using it from PHPMyAdmin, you’ll only get 20 or so names, then “…” or a pagination >> symbol. This means you have to click and keep copying all the next pages so you won’t miss any table. If you do forget that, you can safely re-apply the above query and it’ll give you the next MyISAM tables to convert. – Dario FumagalliFeb 13, 2018 at 1:39
To generate ALTER statements for all tables in all the non-system schemas, ordered by those schemas/tables run the following:
SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'innodb', 'sys', 'tmp')
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, table_name DESC;
After that, run those queries via a client to perform the alteration.
- Answer is based on above answers, but improves schema handling.
One line:
mysql -u root -p dbName -e
"show table status where Engine='MyISAM';" | awk
'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' |
mysql -u root -p dbName
The BEST and the most INTELLIJENT answer! – biniamMar 20, 2015 at 8:56 When I run this in a bash script it interprets the $1 as a bash script variable overwriting the NR definition. Any way around this? – Works for a LivingApr 9, 2020 at 10:17 @WorksforaLiving enclose the "$1" in backticks like this: `"$1"` similar to what’s in my answer. – Vijay VaradanApr 29, 2020 at 5:51
A plain MySQL Version.
You can simply start mysql executable, use database and copy-paste the query.
This will convert all MyISAM tables in the current Database into INNODB tables.
DROP PROCEDURE IF EXISTS convertToInnodb;
DELIMITER //
CREATE PROCEDURE convertToInnodb()
BEGIN
mainloop: LOOP
SELECT TABLE_NAME INTO @convertTable FROM information_schema.TABLES
WHERE `TABLE_SCHEMA` LIKE DATABASE()
AND `ENGINE` LIKE 'MyISAM' ORDER BY TABLE_NAME LIMIT 1;
IF @convertTable IS NULL THEN
LEAVE mainloop;
END IF;
SET @sqltext := CONCAT('ALTER TABLE `', DATABASE(), '`.`', @convertTable, '` ENGINE = INNODB');
PREPARE convertTables FROM @sqltext;
EXECUTE convertTables;
DEALLOCATE PREPARE convertTables;
SET @convertTable = NULL;
END LOOP mainloop;
END//
DELIMITER ;
CALL convertToInnodb();
DROP PROCEDURE IF EXISTS convertToInnodb;
I’m surprised that there weren’t more stored procedure-based solutions! – FrustratedWithFormsDesignerJul 5, 2018 at 19:28
Because writing and testing procedures in MySQL is pain 😉 the comment has nothing todo with the question. – Harald LeithnerJul 5, 2018 at 20:23
ust tested another (simple ?) way, and worked for me.
Just export your DB as .sql file, edit-it with gedit or notepad;
Replace ENGINE=MyISAM with ENGINE=INNODB and Save the file edited
Number or replacement done should be the number of your tables
Import it to MySQL (phpMyAdmin or command line)
And Voila !
You can execute this statement in the mysql command line tool:
echo "SELECT concat('ALTER TABLE `',TABLE_NAME,'` ENGINE=InnoDB;')
FROM Information_schema.TABLES
WHERE ENGINE != 'InnoDB' AND TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA='name-of-database'" | mysql > convert.sql
You may need to specify username and password using: mysql -u username -p The result is an sql script that you can pipe back into mysql:
mysql name-of-database < convert.sql
Replace “name-of-database” in the above statement and command line.
@itsraja, “echo” is a command supported by both sh on linux/unix and cmd on Microsoft systems, the result is piped as input to the mysql tool. – Hendrik BrummermannSep 27, 2011 at 11:19
that’s right. But u’ve mentioned as “mysql command line tool” – itsrajaSep 27, 2011 at 12:50
Also, echo “SELECT concat(concat(‘ALTER TRABLE ‘, TABLE_NAME), ‘ ENGINE=InnoDB;’) FROM TABLES WHERE ENGINE != ‘InnoDB’ AND TABLE_TYPE=’BASE TABLE’ AND TABLE_SCHEMA=’testinno'” | mysql -u root –sock=/opt/lampp/var/mysql/mysql.sock –database=testinno > convert.sql ERROR 1146 (42S02) at line 1: Table ‘testinno.TABLES’ doesn’t exist – itsrajaSep 27, 2011 at 12:55
I’ve put this into an example shell script here: shrubbery.mynetgear.net/c/display/W/… – Joshua DavisJun 28, 2012 at 14:46 1 How can we properly escape the sql statement as a string? As it is now, I get -bash: ,TABLE_NAME,: command not found – arjanJan 22, 2014 at 12:00
use this line to alter the database engine for single table.
ALTER TABLE table_name ENGINE = INNODB;
Use this as a sql query in your phpMyAdmin
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM';
It’s Very simple there are only TWO step just copy and paste :
step 1.
SET @DATABASE_NAME = 'name_of_your_db';
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name DESC;
(copy and paste all result in in sql tab)
step 2: (copy all result in in sql tab) and paste below in the line
START TRANSACTION;
COMMIT;
eg. START TRANSACTION;
ALTER TABLE admin_files ENGINE=InnoDB;
COMMIT;
It hasn’t been mentioned yet, so I’ll write it for posterity:
If you’re migrating between DB servers (or have another reason you’d dump and reload your dta), you can just modify the output from mysqldump:
mysqldump --no-data DBNAME | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > my_schema.sql;
mysqldump --no-create-info DBNAME > my_data.sql;
Then load it again:
mysql DBNAME < my_schema.sql && mysql DBNAME < my_data.sql
(Also, in my limited experience, this can be a much faster process than altering the tables ‘live’. It probably depends on the type of data and indexes.)
- ty! exactly what I was looking for. Will test it in a few days. – Rainer Feb 10, 2017 at 15:45
Here is a way to do it for Django users:
from django.core.management.base import BaseCommand
from django.db import connections
class Command(BaseCommand):
def handle(self, database="default", *args, **options):
cursor = connections[database].cursor()
cursor.execute("SHOW TABLE STATUS");
for row in cursor.fetchall():
if row[1] != "InnoDB":
print "Converting %s" % row[0],
result = cursor.execute("ALTER TABLE %s ENGINE=INNODB" % row[0])
print result
Add that to your app under the folders management/commands/ Then you can convert all your tables with a manage.py command:
python manage.py convert_to_innodb
Follow steps:
- Use MySql commands as follows, for converting to InnoDB
(ALTER TABLE t1 ENGINE = InnoDB)or(ALTER TABLE t1 ENGINE = MyISAM)for MyISAM (You should do this for each individual tables, t1 is for the table name.). - Write a script that loops on all tables and run the alter command
- Use an already available script to handle that: https://github.com/rafihaidari/convert-mysql-tables-storage-engine
- Try this SQL to Get all info will get all the tables information then you can change all the table from isam to InnoDB
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND table_schema = 'your_DB_Name';
In my case, I was migrating from a MySQL instance with a default of MyISAM, to a MariaDB instance with a DEFAULT of InnoDB.
On old Server Run :
mysqldump -u root -p --skip-create-options --all-databases > migration.sql
The –skip-create-options ensures that the database server uses the default storage engine when loading the data, instead of MyISAM.
mysql -u root -p < migration.sql
This threw an error regarding creating mysql.db, but everything works great now 🙂
In the scripts below, replace <username>, <password> and <schema> with your specific data.
To show the statements that you can copy-paste into a mysql client session type the following:
echo 'SHOW TABLES;' \
| mysql -u <username> --password=<password> -D <schema> \
| awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
| column -t \
To simply execute the change, use this:
echo 'SHOW TABLES;' \
| mysql -u <username> --password=<password> -D <schema> \
| awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
| column -t \
| mysql -u <username> --password=<password> -D <schema>
CREDIT: This is a variation of what was outlined in this article.
I’m a newbie and had to find my own solution because mysql commands on the web are usually riddled with misspellings create a real life nightmare for people just starting out. Here is my solution….
Instead of in 1 command per table, I prepared dozens of commands (ready to copy and paste) at once using excel.
How? expand your putty window and enter mysql and then run the command “SHOW TABLE STATUS;” and the copy/paste the output to microsoft excel. Go to the Data tab and use the “text to columns” feature an delimit the columns by a space key. Then Sort the columns by whichever column shows your table types and delete all rows which the tables are already in InnoDb format (because we don’t need to run commands against them, they are already done). Then add 2 columns to the left of the tables column, and 2 columns to the right. Then paste in the first part of the command in column-1 (see below). Column 2 should contain only a space. Column 3 is your tables column. Column 4 should contain only a space. Column 5 is the last part of your command. It should look like this:
column-1 column-2 column-3 column-4 column-5
ALTER TABLE t_lade_tr ENGINE=InnoDB;
ALTER TABLE t_foro_detail_ms ENGINE=InnoDB;
ALTER TABLE t_ljk_ms ENGINE=InnoDB;
Then copy and paste about 5 rows at a time into mysql. This will convert about 5 at once. I noticed if I did more than that at once then the commands would fail.
for mysqli connect;
<?php
$host = "host";
$user = "user";
$pass = "pss";
$database = "db_name";
$connect = new mysqli($host, $user, $pass, $database);
// Actual code starts here Dont forget to change db_name !!
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'db_name'
AND ENGINE = 'MyISAM'";
$rs = $connect->query($sql);
while($row = $rs->fetch_array())
{
$tbl = $row[0];
$sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
$connect->query($sql);From inside mysql, you could use search/replace using a text editor:
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';
Note: You should probably ignore information_schema and mysql because “The mysql and information_schema databases, that implement some of the MySQL internals, still use MyISAM. In particular, you cannot switch the grant tables to use InnoDB.” ( http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html )
In any case, note the tables to ignore and run:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';
Now just copy/paste that list into your text editor and search/replace “|” with “ALTER TABLE” etc.
You’ll then have a list like this you can simply paste into your mysql terminal:
ALTER TABLE arth_commentmeta ENGINE=Innodb;
ALTER TABLE arth_comments ENGINE=Innodb;
ALTER TABLE arth_links ENGINE=Innodb;
ALTER TABLE arth_options ENGINE=Innodb;
ALTER TABLE arth_postmeta ENGINE=Innodb;
ALTER TABLE arth_posts ENGINE=Innodb;
ALTER TABLE arth_term_relationships ENGINE=Innodb;
ALTER TABLE arth_term_taxonomy ENGINE=Innodb;
ALTER TABLE arth_terms ENGINE=Innodb;
ALTER TABLE arth_usermeta ENGINE=Innodb;
If your text editor can’t do this easily, here’s another solution for getting a similar list (that you can paste into mysql) for just one prefix of your database, from linux terminal:
mysql -u [username] -p[password] -B -N -e 'show tables like "arth_%"' [database name] | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;"
Some fixes to this util script
SET @DATABASE_NAME = 'Integradb';
SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;
You could write a script to do it in your favourite scripting language. The script would do the following:
- Issue
SHOW FULL TABLES. - For each row returned, check that the second column says
'BASE TABLE'and not'VIEW'. - If it is not
'VIEW', issue the appropriateALTER TABLEcommand.
try this shell script
DBENGINE='InnoDB' ;
DBUSER='your_db_user' ;
DBNAME='your_db_name' ;
DBHOST='your_db_host'
DBPASS='your_db_pass' ;
mysqldump --add-drop-table -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME > mtest.sql; mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "SHOW TABLES;" | while read TABLE ; do mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "ALTER TABLE $TABLE ENGINE=$DBENGINE;" ; done
<?php
// connect your database here first
mysql_connect('host', 'user', 'pass');
$databases = mysql_query('SHOW databases');
while($db = mysql_fetch_array($databases)) {
echo "database => {$db[0]}\n";
mysql_select_db($db[0]);
$tables = mysql_query('SHOW tables');
while($tbl = mysql_fetch_array($tables)) {
echo "table => {$tbl[0]}\n";
mysql_query("ALTER TABLE {$tbl[0]} ENGINE=InnoDB");
}
}<?php
// Convert all MyISAM tables to INNODB tables in all non-special databases.
// Note: With MySQL less than 5.6, tables with a fulltext search index cannot be converted to INNODB and will be skipped.
if($argc < 4)
exit("Usage: {$argv[0]} <host> <username> <password>\n");
$host = $argv[1];
$username = $argv[2];
$password = $argv[3];
// Connect to the database.
if(!mysql_connect($host, $username, $password))
exit("Error opening database. " . mysql_error() . "\n");
// Get all databases except special ones that shouldn't be converted.
$databases = mysql_query("SHOW databases WHERE `Database` NOT IN ('mysql', 'information_schema', 'performance_schema')");
if($databases === false)
exit("Error showing databases. " . mysql_error() . "\n");
while($db = mysql_fetch_array($databases))
{
// Select the database.
if(!mysql_select_db($db[0]))
exit("Error selecting database: {$db[0]}. " . mysql_error() . "\n");
printf("Database: %s\n", $db[0]);
// Get all MyISAM tables in the database.
$tables = mysql_query("SHOW table status WHERE Engine = 'MyISAM'");
if($tables === false)
exit("Error showing tables. " . mysql_error() . "\n");
while($tbl = mysql_fetch_array($tables))
{
// Convert the table to INNODB.
printf("--- Converting %s\n", $tbl[0]);
if(mysql_query("ALTER TABLE `{$tbl[0]}` ENGINE = INNODB") === false)
printf("--- --- Error altering table: {$tbl[0]}. " . mysql_error() . "\n");
}
}
mysql_close();
?>This is a simple php script.
<?php
@error_reporting(E_ALL | E_STRICT);
@ini_set('display_errors', '1');
$con = mysql_connect('server', 'user', 'pass');
$dbName = 'moodle2014';
$sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$dbName."';";
$rs = mysql_query($sql, $con);
$count = 0;
$ok = 0;
while($row = mysql_fetch_array($rs)){
$count ++;
$tbl = $row[0];
$sql = "ALTER TABLE ".$dbName.".".$tbl." ENGINE=INNODB;";
$resultado = mysql_query($sql);
if ($resultado){
$ok ++;
echo $sql."<hr/>";
}
}
if ($count == $ok){
echo '<div style="color: green"><b>ALL OK</b></div>';
}else{
echo '<div style="color: red"><b>ERRORS</b>Total tables: '.$count.', updated tables:'.$ok.'</div>';
}
Yet another option… Here’s how to do it in ansible. It assumes that the name of your database is in dbname and that you have already configured access.
- name: Get list of DB tables that need converting to InnoDB
command: >
mysql --batch --skip-column-names --execute="SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '{{ dbname }}' AND ENGINE = 'MyISAM';"
register: converttables
check_mode: no
changed_when: False
- name: Convert any unconverted tables
command: >
mysql --batch --skip-column-names --execute="ALTER TABLE `{{ dbname }}`.`{{ item }}` ENGINE = InnoDB;"
with_items: "{{ converttables.stdout_lines }}"
For converting MySql tables storage engine there is a number way:
- Use MySql commands as follow, for converting to innodb (
ALTER TABLE t1 ENGINE = InnoDB) or (ALTER TABLE t1 ENGINE = MyISAM) for myisam (You should do this for each individual tables, t1 is for table name.). - Write a script that loop on all tables and run the alter command
- Use an already available script to handle that: https://github.com/rafihaidari/convert-mysql-tables-storage-engine
If you are using Windows you can accomplish this inside a batch file with the following loop.
set database=YOURDATABASENAME
for /F "tokens=1 skip=1 usebackq" %%a in (`mysql %%database%% -e "show table status where Engine != 'InnoDB';"`) do (
mysql %database% -e "ALTER TABLE %%a ENGINE = 'InnoDB';"
)
Simply change the YOURDATABASENAME to the name of the database you are targeting or use %~1 to pass the database name via the command line.
Every table which is not currently InooDB will be converted to InnoDB. If you want to specifically target MyISAM as the question suggested, the following code has an updated MySQL conditional for only MyISAM.
set database=YOURDATABASENAME
for /F "tokens=1 skip=1 usebackq" %%a in (`mysql %%database%% -e "show table status where Engine = 'MyISAM';"`) do (
mysql %database% -e "ALTER TABLE %%a ENGINE = 'InnoDB';"
)
Create a SQL dump file of your database (database_dump.sql) and open it in notepad. Find and Replace all “ENGINE=MyISAM” with “ENGINE=InnoDB”. Save the file and import it back into your database.
cd /var/lib/mysql/DBNAME
ls | grep “.frm” | cut -d”.” -f1 | xargs -I{} -n1 mysql -D DBNAME -e “alter table {} ENGINE=INNODB;” -uroot -pXXXXX
source : https://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb