Início / dba / Perguntas / 6958
Accepted
Noah Goodrich
Asked: 2011-10-20 05:54:21 +0800 CST

Qual é a solução ideal para converter um esquema de banco de dados?

  • 772

Na verdade, quero me livrar das restrições existentes no banco de dados, o que sei que parece loucura, mas estamos atualizando nosso aplicativo para uma nova estrutura que depende dos nomes de restrição de chave estrangeira, além de fazermos várias limpezas e renomeações de campos, tabelas etc

Suponho que uma maneira melhor de enquadrar a questão é que, dado um banco de dados existente, precisa passar pelo seguinte:

  • Crie todas as novas restrições de chave estrangeira com nomes que funcionem dentro da estrutura
  • As tabelas serão renomeadas e/ou reestruturadas massivamente, incluindo a renomeação de campos que atualmente fazem parte de relacionamentos de chave estrangeira existentes

Qual é o melhor jeito de concluir isso?

Minhas soluções até agora foram:

  1. Crie um script de conversão (em php) que executará DROP FOREIGN KEY para todas as restrições no banco de dados. Isso resultou em erros estranhos ocorrendo durante as etapas posteriores do processo ao tentar ALTER TABLES e adicionar as novas restrições. Além disso, as restrições foram removidas, mas recebo erros se tento DROP as próprias chaves.
  2. Em vez de usar PHP e PDO, escreva o script de conversão usando SQL direto e use um CURSOR para DROP as restrições.
  3. Em vez de descartar as próprias restrições, converta as tabelas em MyIsam e DROP as chaves e, em seguida, converta de volta para Innodb antes de continuar com o restante do processo de conversão.
  4. Crie um novo esquema do zero e mova os dados do esquema antigo para o novo.

Gostaria de saber qual dessas abordagens seria considerada a mais válida? Ou se existe uma maneira melhor de fazer isso?

ATUALIZAÇÃO (essa informação foi solicitada na sala de bate-papo, então pensei em adicioná-la aqui):

  • O esquema atual tem 68 tabelas, o novo esquema será mais próximo de 55.
  • A maior tabela tem pouco mais de 800.000 linhas e, em geral, nossas tabelas estão na casa das dezenas de milhares.
  • O banco de dados inteiro tem aproximadamente 240 MB, mas a maior parte está em uma única tabela que armazena um grande campo de texto. Estaremos comprimindo isso durante a conversão.
mysql innodb

3 respostas

  1. Best Answer

    Chega um ponto em que uma limpeza passo a passo se torna mais trabalhosa do que uma lousa limpa e uma abordagem de migração. A disponibilidade do sistema e o tempo para migrar podem influenciar a decisão ao lidar com volumes maiores, mas nesse tamanho, não é um problema.

    Os principais fatores para mim aqui são:

    • Renomeando as restrições de chave estrangeira para se adequar a uma nova estrutura de aplicativo.
    • Refatoração de uma proporção significativa de tabelas existentes.
    • Baixo volume de dados.

    Nessa situação, eu ficaria muito tentado a projetar um novo esquema que se ajustasse ao modelo que você agora precisa e criar os scripts necessários para migrar os dados (sua opção 4).

    • 4

  2. Não sei sobre o ideal, mas pode valer a pena tentar uma conversão rápida para escrever um script bash que segue uma lógica simples:

    • banco de dados mysqldump em um arquivo
    • analisar as CONSTRAINTlinhas no arquivo
    • verifique se o CONSTRAINTnome existe como uma CHAVE, se houver, remova-o do arquivo
    • remova a linha de restrição do arquivo
    • recarregue o arquivo de despejo resultante no banco de dados.

    Muito importante: Tenha um backup do seu banco de dados.

    • 2

  3. Bom dia, Sr. Phelps (quero dizer Sr. Goodrich).

    Você tem restrições de chave estrangeira que gostaria de ignorar. Aqui está o seu itinerário para esta tarefa:

    • Etapa 01) Certifique-se de ter backups de todos os seus dados
    • Passo 02) Desabilite Chaves Estrangeiras usando
      • SET GLOBAL UNIQUE_CHECKS = 0;
      • SET GLOBAL FOREIGN_KEY_CHECKS = 0;
    • Passo 03) Prossiga com sua limpeza sem se preocupar com chaves estrangeiras
    • Passo 04) Se algo der errado, restaure o backup do Passo 01

    Sua missão, caso decida excetuá-la, é realizar essas etapas com a esperança de que a Etapa 04 não seja necessária.

    Como sempre, se algum de seus dados for capturado ou eliminado, o DBA StackExchange negará qualquer conhecimento de suas ações.

    Esta não é uma fita, portanto não se autodestruirá em 5 segundos.

    ATUALIZAÇÃO 2011-10-19 12:50 EDT

    Eu gosto da sua opção 3. Parece a mais rápida e suja. Aqui está como você pode fazer isso:

    Passo 01) mysqldump tudo para /root/MySQLData.sql

    Etapa 02) Elimine todos os bancos de dados

    Etapa 03) adicione isso a /etc/my.cnf

    [mysqld]
skip-innodb

    Passo 04)service mysql stop

    Passo 05)rm -f /var/lib/mysql/ib_logfile[01] /var/lib/mysql/ibdata1

    Passo 06)service mysql start

    Passo 07)mysql -uroot -p -A < /root/MySQLData.sql

    Passo 08) Remova skip-innodbde /etc/my.cnf

    Passo 09)service mysql restart

    Após o passo 7, todos os dados estão em MyISAM

    Após o passo 9

    • O mecanismo de armazenamento InnoDB foi reativado
    • ibdata1 é recriado
    • ib_logfile0 é recriado
    • ib_logfile1 é recriado

    Agora, aqui está como converter em massa todas as tabelas MyISAM de volta para InnoDB:

    echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql -uroot -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)" >> /root/ConvertMyISAMToInnoDB.sql
mysql -uroot -p -A < /root/ConvertMyISAMToInnoDB.sql

    O script converterá todas as tabelas MyISAM em ordem de tamanho, da menor para a maior.

    De uma chance !!!

    • 1

relate perguntas