22.03.2015 в Linux, MySQL:

Как в MySQL преобразовать все таблицы во всех базах из MyISAM в InnoDB или обратно

Сначала о том, когда и зачем вам может потребоваться конвертировать движки базы данных. Во-первых, InnoDB (XtraDB) современнее (в данном случае читай «лучше для боевого использования»). Во-вторых, после аварийного падения или по итогам неумелого переноса файлов и логов СУБД, где уже используется InnoDB, можно получить примерно такую ошибку в error-логе мускула:

InnoDB: Error: page XXX log sequence number YYY
InnoDB: is in the future! Current system log sequence number ZZZ.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files.

Если у вас возникла именно такая проблема, то можно попробовать решить её без остановки СУБД, а, точнее, с минимальным даунтаймом на перезапуск сервиса.
Сначала рассмотрим первый случай:

Есть и нормально работает MyISAM, но хочется, чтобы стало InnoDB

Перед всеми манипуляциями надо сделать резервный дамп и проверить, не используются ли у вас в каких-то таблицах FULLTEXT-индексы. Дело в том, что FULLTEXT поддерживается только в MyISAM.

Проверить можно таким запросом:

SELECT
  tbl.table_schema,
  tbl.table_name
FROM (
  SELECT
    table_schema,
    table_name
  FROM information_schema.tables
  WHERE 1=1
    AND engine = 'MyISAM'
    AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
) tbl
INNER JOIN
(
  SELECT
    table_schema,
    table_name
  FROM information_schema.statistics
    WHERE index_type = 'FULLTEXT'
) ndx
USING (table_schema, table_name);

В условии AND table_schema NOT IN... я исключаю системные базы (которые трогать вообще не надо). Если у вас MySQL версии меньше, чем 5.5, то база performance_schema может отсутствовать. Заранее проверьте есть она или нет и, при необходимости, уберите её из запроса.

Если запрос вернёт пустой результат, то всё ОК, у вас нет FULLTEXT-индексов, можно все таблицы перевести на InnoDB. Если результат непустой, то вы увидите имена таблиц, которые на InnoDB переводить нельзя, чтобы ничего не поломалось.

Например, если среди баз у вас есть БД для CMS WordPress, то, скорее всего, вы обнаружите, что FULLTEXT-индексы применяются в таблице wp_posts. Если очень хочется, то вы, конечно, можете остальные таблицы базы преобразовать в InnoDB руками, а wp_posts — не трогать. Чтобы сменить движок для таблицы wp_users в базе wordpressdb надо сделать следующее:

USE wordpressdb;
ALTER TABLE wp_users ENGINE=InnoDB;

Но если вам повезло, и у вас нет таблиц с полнотекстовыми индексами (например, если вы используете для сайтов не WordPress, а Drupal), то можно одним запросом изменить движки сразу для всех таблиц во всех базах (кроме системных):

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
  AND engine = 'MyISAM'
  AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

Снова напомню, что системная база performance_schema может отсутствовать. Проверьте заранее.

Теперь второй случай:

Все базы были на InnoDB, но с ним возникла проблема, надо получить InnoDB без проблем и почти без даунтайма

Сценарий такой:

  1. Делаем дамп!!!
  2. Конвертируем все таблицы всех баз в MyISAM
  3. Удаляем файлы и логи InnoDB
  4. Перезапускаем MySQL, чтобы она создала новые пустые файлы и логи
  5. Конвертируем все таблицы всех баз обратно в InnoDB из MyISAM

Будем исполнять.

Делаем дамп (это делаем не в mysql, а в bash):

mysqldump -u root -pPASSWORD --all-databases > mysql_full_dump.sql

Переводим все таблицы всех несистемных баз на MyISAM:

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;')
FROM information_schema.tables
WHERE 1=1
  AND engine = 'InnoDB'
  AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

Удаляем файлы и логи InnoDB и перезапускаем MySQL (это мы делаем не в mysql, а в обычной системной консоли, в bash короче):

service mysqld stop \
  && rm -f /var/lib/mysql/ibdata1 \
  && rm -f /var/lib/mysql/ib_logfile* \
  && service mysqld start

Простой будет минимальным: после установки сервера мы лишь удалим несколько файлов, а это быстро, и снова запустим сервер.

Переводим все таблицы назад на InnoDB:

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
  AND engine = 'MyISAM'
  AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

Готово.

Ещё можно решить проблему с помощью дампа. То есть в дампе меняем тип движка, а потом этот дамп вливаем обратно. Хитрость в том, чтобы не лезть с заменой подстроки MyISAM на InnoDB в данные, меняя только в схемах:

mysqldump -u root -pPASSWORD --all-databases --no-data \
  | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > mysql_schema.sql
mysqldump -u root -pPASSWORD --all-databases --no-create-info > mysql_data.sql
mysql -u root -pPASSWORD < mysql_schema.sql && mysql -u root < mysql_data.sql

Тут я не заморачивался тем, чтобы исключить системные таблицы. Хотя это нужно сделать. Но меня полностью устроил первый метод (с помощью запрос самого мускула).

P.S. Я всё-таки заморочился. Вот так можно пересоздать все базы и таблицы в СУБД с нуля и сразу на InnoDB (кроме системной):

mysql -p`cat /root/.mysql-root-password` -B -N -e "SHOW DATABASES;" \
| grep -v '^mysql$' | grep -v '^information_schema$' | grep -v '^performance_schema$' \
| xargs mysqldump -p`cat /root/.mysql-root-password` --force --databases --no-data \
| sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > /root/all-dbs-schema.sql
mysql -p`cat /root/.mysql-root-password` -B -N -e "SHOW DATABASES;" \
| grep -v '^mysql$' | grep -v '^information_schema$' | grep -v '^performance_schema$' \
| xargs mysqldump -p`cat /root/.mysql-root-password` --force --databases --no-create-info > /root/all-dbs-data.sql
mysqldump -p`cat /root/.mysql-root-password` --force mysql > /root/all-dbs-mysql.sql
service mysql stop
killall -9 mysqld
rm -rf /var/lib/mysql/*
/usr/bin/mysql_install_db --user=mysql
service mysql start
mysql < /root/all-dbs-schema.sql 
mysql < /root/all-dbs-data.sql
mysql mysql < /root/all-dbs-mysql.sql
mysql -B -N -e "FLUSH PRIVILEGES;"
mysql -p`cat /root/.mysql-root-password` -B -N -e "DROP DATABASE test;"

Вот как-то так.



Прокомментируйте: