MySQL: como converter muitas tabelas MyISAM para o InnoDB em um banco de dados de produção?

Question

Temos um banco de dados de produção que é feito inteiramente de tabelas MyISAM. Estamos pensando em convertê-los para o InnoDB para obter uma melhor concorrência & confiabilidade.

1. Posso apenas alterar as tabelas myISAM para o InnoDB sem desligar o MySQL? Quais são os procedimentos recomendados aqui?

2. Por quanto tempo essa conversão ocorrerá? Todas as tabelas têm um tamanho total de aproximadamente 700MB

3. Há um grande número de tabelas. Existe alguma maneira de aplicar ALTER TABLE a todas as tabelas MyISAM de uma só vez, em vez de fazê-lo uma a uma?

4. Quaisquer armadilhas que eu preciso estar ciente?

Obrigado

    

Answer 1

Você pode fazer isso. A conversão do MyISAM para o InnoDB não deve ser um problema, pois o InnoDB possui mais recursos que não estão disponíveis no MyISAM, como transações.

Você pode alterar as tabelas enquanto o banco de dados está sendo executado. Não há necessidade de desligamento, mas essa operação bloqueará as tabelas.

Pelo tempo que levará, isso depende das especificações do servidor / carga / tamanho do banco de dados, etc.

Você pode fazer isso usando um script para automatizar o processo. Usando um comando show tables , você pode obter a lista de tabelas. Então, usando um script simples, você pode ler essa lista e executar comandos de alteração de tabela.

Aqui está um script em perl simples que lerá a lista de tabelas de um arquivo e gravará as consultas SQL em outro arquivo. Após invocar este script, você pode alimentar o arquivo de saída para o comando mysql para fazer as mudanças necessárias.

open(INFILE, "< tables_list.txt");
open(OUTFILE, "> alter_tables.sql");
foreach my $name (<INFILE>) {
   chomp($name);
   print OUTFILE "ALTER TABLE $name engine=InnoDB;\n";
}
close(INFILE);
close(OUTFILE);

    

Answer 2

Eu uso CREATE e SELECT:

 mysql> CREATE TABLE i_table LIKE table;
 mysql> ALTER TABLE i_table ENGINE=InnoDB;
 mysql> INSERT INTO i_table SELECT * FROM table;
 mysql> RENAME TABLE table TO b_table;
 mysql> RENAME TABLE i_ TO other_db.tbl_name;

Isso funciona bem se você não tiver muitos dados. Melhor uso

mysql> ALTER TABLE table ENGINE = InnoDB;

Tome cuidado antes de tentar este método em um banco de dados ocupado.

Além disso, você pode descarregar o db, alterar o ENGINE no arquivo de despejo e o nome do banco de dados no arquivo de despejo. Restaurar o banco de dados e renomeie o banco de dados.

    

Answer 3

Eu tenho uma maneira automatizada de converter o MyISAM para o InnoDB usando apenas o MySQL

Aqui está uma consulta para gerar as tabelas SQL para MyISAM em cada banco de dados:

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL
FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN
('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length);

No prompt do Linux, execute a criação do script assim:

mysql -h... -u... -p.... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > MyISAM_To_InnoDB.sql

Isso gera o SQL para converter cada tabela MyISAM em todos os bancos de dados da menor tabela para a maior tabela.

Você pode facilmente ajustar o SQL para converter todas as tabelas MyISAM no InnoDB dentro de um banco de dados específico como este (por exemplo mystuff de banco de dados):

mysql -h... -u... -p.... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema = 'mystuff' ORDER BY (data_length+index_length)" > MyISAM_To_InnoDB.sql

    

Answer 4

Khaled e ooshro você abordou a semântica; As questões 2 e 4 são mais difíceis de julgar porque dependem da sua configuração e hardware. O que você poderia fazer por # 2 é despejar o banco de dados em um arquivo e importá-lo para uma estação de trabalho ou outro servidor (comprar uma fatia da nuvem por US $ 5 e usá-la se for necessário) e tentar executar as opções alter quanto tempo demora apenas um sentido geral geral. Este não é realmente um teste perfeito, mas é melhor que nada - 700MB não é muito grande e não demorará muito se o seu MySQL estiver em um servidor dedicado com energia (Dell 2970 com 16gig de RAM, por exemplo).

Para # 4 - não converta o banco de dados mysql / para o InnoDB. :) No geral, o processo causará uma trava completa de tabela enquanto está sendo executado (realisticamente, em segundo plano, é apenas criar uma nova tabela, transferir os dados e, em seguida, excluir / renomear) para que você altere os mecanismos durante as horas de inatividade à noite. Certifique-se de obter um despejo completo apenas no caso de algo dar errado. (mysqldump -A --flush-privileges > dbdump.sql).