MySQL: конвертация всех таблиц в базе данных из MyISAM в InnoDB

mysqllogo Хорошее сравнение типов таблиц в MySQL можно найти в Википедии>>>.

А вот тут>>> есть отличная таблица, которая наглядно объясняет разницу между ними.

Сервер TeamCity настоятельно рекомендует использовать именно InnoDB, вместо MyISAM:

110 tables in the MySQL database currently use MyISAM storage engine. To achieve better performance, switching to the InnoDB storage engine is recommended. For instructions on converting MyISAM tables to InnoDB, refer to the MySQL documentation. Make sure you read our recommendations for configuring MySQL.

Проверяем текущий тип:

mysql> select table_name, engine from information_schema.tables where table_schema = 'teamcity_db1';

Или по конкретной таблице (table_name = 'server'):

mysql> select table_name, engine from information_schema.tables where table_schema = 'teamcity_db1' and table_name = 'server';
+------------+--------+
| table_name | engine |
+------------+--------+
| server     | MyISAM |
+------------+--------+
1 row in set (0.00 sec)

Или выбрать все таблицы, у которых в данный момент тип MyISAM:

mysql> select table_name, engine from information_schema.tables where table_schema = 'teamcity_db1' and engine = 'MyISAM';

Создаём дамп базы данных:

$ mysqldump -u teamcity -p teamcity_db1 > ../../backups/teamcity_db1.sql
Enter password:

Открываем файл для редактирования и ищем такие строки:

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Необходимо изменить ENGINE с MyISAM на InnoDB:

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Так как таблиц много – то выполним команду с использованием sed, который выполнит все действия:

$ cat ../../backups/teamcity_db1.sql | sed -e 's/MyISAM/INNODB/g' > ../../backups/teamcity_db1_INNODB.sql

Смотрим файл ещё раз:

$ cat ../../backups/teamcity_db1_INNODB.sql | grep ENGINE | tail -n 5
) ENGINE=INNODB DEFAULT CHARSET=utf8;
) ENGINE=INNODB DEFAULT CHARSET=utf8;
) ENGINE=INNODB DEFAULT CHARSET=utf8;
) ENGINE=INNODB DEFAULT CHARSET=utf8;
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Удаляем старую базу:

mysql> drop database teamcity_db1;

Создаём её заново:

mysql> create database teamcity_db1 default charset utf8;

Загружаем откорректированный дамп базы:

$ mysql -u teamcity -p teamcity_db1 < ../../backups/teamcity_db1_INNODB.sql
Enter password:

Проверяем ещё раз:

mysql> select table_name, engine from information_schema.tables where table_schema = 'teamcity_db1' and table_name = 'server';
+------------+--------+
| table_name | engine |
+------------+--------+
| server     | InnoDB |
+------------+--------+
1 row in set (0.00 sec)

Ещё один способ конвертации – с помощью такого скрипта:

$ cat mysql_query.sh
#!/bin/bash

get_list () {
echo "use teamcity_db1;"
mysql -u teamcity -ppassword -B -N -e 'show tables' teamcity_db1 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;"
}

get_list | mysql -u teamcity -ppassword

Если необходимо изменить не всех таблиц, а только для определённых – измените запрос: