Конвертирование MyISAM в InnoDB

For all tables in ONE database:

mysql -e "SELECT concat('ALTER TABLE ',TABLE_NAME,' DROP INDEX ', index_name, ' ;')
FROM information_Schema.STATISTICS
WHERE table_schema = 'db_wordpress'
AND index_type = 'FULLTEXT' ORDER BY index_name " | tail -n+2 > drop.sql

mysql -f db_wordpress < drop.sql

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

mysql -f db_wordpress < alter.sql


For all tables in ALL databases:

mysql -e "SELECT concat('ALTER TABLE \`',TABLE_SCHEMA,'\`.',TABLE_NAME,' DROP INDEX ', index_name, ' ;')
FROM information_Schema.STATISTICS
WHERE TABLE_SCHEMA != 'mysql'
AND index_type = 'FULLTEXT' ORDER BY index_name " | tail -n+2 > drop.sql

mysql -f < drop.sql

mysql -e "SELECT concat('ALTER TABLE \`',TABLE_SCHEMA,'\`.',TABLE_NAME,' ENGINE=InnoDB;')
FROM Information_schema.TABLES
WHERE TABLE_SCHEMA != 'mysql' AND ENGINE = 'MyISAM' AND TABLE_TYPE='BASE TABLE'" | tail -n+2 > alter.sql

mysql -f < alter.sql

Comments powered by CComment

Designed by san © 2018