Migrando bases grandes no MySQL

Todo mundo diz que gerenciar o MySQL é fácil, afinal pelo phpmyadmin você consegue fazer quase tudo, inclusive efetuar import/export das bases facilmente, mas não existem Ateus quando você tem que migrar uma base com mais de 4GB por exemplo para outro servidor.

Tenho um amigo que passou por esta situação, precisou migrar uma base MySQL de um Revenda Cpanel para o seu host, porém na origem a base tinha cerca de 4GB, onde o phpmyadmin fazia o backup da base, mas devido as limitações do ambiente, ele não conseguia “varrer” todos os dados.

Os limites no phpmyadmin estão ligados diretamente as variáveis do php no ambiente Web, onde erros como Allowed Memory Size, Out off Memory são comuns neste tipo de ação. Tendo a autonomia, você pode aumentar o valor da variável “memory_limit” no php.ini, ou também existem variáveis do próprio phpmyadmin que aumentam o limite para o restore de uma base por exemplo, porém ambas não resolveram o meu problema.

Já trabalhei com bases muito superiores a 4GB, onde a migração apesar de ser um pouco demorada, sempre foi conclusiva, utilizando o próprio client do MySQL.

Nestes caso, no ambiente Cpanel criei um usuário com o host ‘%’, permitindo o acesso remoto a base, e atribui o all privileges para o usuário na base em que seria feito o backup.

Através da minha máquina local Linux, abri o terminal e iniciei o backup do banco:

Como o backup esta sendo efetuado via rede, você pode se deparar com o erro “max_allowed_packet”. Este valor pode ser alterado de forma dinâmica, então fica a sua escolha de editar a variável no arquivo de configuração (my.cnf) e reiniciar o MySQL, ou alterar diretamente:

Onde ‘1073741824’ é o valor máximo desta variável. Antes de alterar, guarde o valor atual da variável:

Outro erro é o “MySQL Has Gone Away”, que depende de outros fatores como oscilação na rede para iniciar a sessão no MySQL e/ou sobrecargas no servidor MySQL. Desta forma, já recomendo que alterem também o valor da variável wait_timeout:

O valor limite desta variável é de ‘2147483’ em ambientes Windows, e de ‘31536000’ em ambientes Linux.

Obs: Muitas vezes não sera necessário alterar estas variáveis, tudo vai depender do ambiente do qual você trabalha, afinal não existe uma “bala de prata” para resolver tudo de uma vez só.

Outra opção é fazer o backup tabela por tabela, possibilitando um melhor controle no backup de cada objeto e na visualização de possíveis erros.

Para uma forma automatizada, primeiro temos que obter a relação das tabelas da base de dados:

E podemos montar o dump através do for em cada tabela:

Geralmente o dump é o processo mais difícil, muitas vezes devido a limitações de acesso ao servidor de origem. Recomendo que todo este processo de backup seja feito no seu host, servidor cloud e etc, para que o restore não tenha que passar pela rede. Mas se não tiver opção, o processo também pode ser feito em uma máquina local, onde o restore também será de forma remota.

Restore para um unico arquivo:

Caso tenha feito o backup tabela por tabela:

Caso seja o restore local, basta remover o parâmetro -h e o ip do servidor.

Desta forma foi possível migrar o banco para outro ambiente e trabalhar com a garantia de que todos os dados estavam íntegros. Como citei, não existe uma bala de prata para resolver todos os problemas, mas esta é uma forma de prosseguir com a migração.

Se tiver chegado até aqui e ainda assim não ter conseguido migrar o seu banco, interaja nos comentários informando o problema, iremos ajuda-lo 😀

Meu amigo André que teve este problema, é administrador da LacWeb, uma ótima opção para Web Host. Caso precise de algum serviço de hospedagem, recomendo que batam um papo com ele.

É isso ai pessoal.

Dúvidas, criticas ou sugestões? Fiquem a vontade, todo retorno é construtivo 🙂

Jose Wilson

9 Replies to “Migrando bases grandes no MySQL”

  1. Post sensacional Zé, com sua ajuda consegui fazer o restore da vendida base de 4gb, só queria deixar um adendo. Hospedagens compartilhas nos trazem dor de cabeça qdo um projeto consome ou vai consumir recursos das mesmas, neste caso tenha sempre em mente usar outras opções como um VPS por exemplo, q acabam tendo um custo pequeno mediante os recursos e benefícios superiores sobre as hospedagens compartilhadas.

  2. José Wilson muito bom o seu post, gostaria de tirar uma duvida com voce que certamente é a duvida de muitas pessoas, com relação a migração para versões diferentes, em caso de bases grandes e com muitos usuários(ex.acima de 100), sabemos que a tables user é diferente na versão 5.1 para 5.6 e 5.7, qual seria o procedimento ideal para um migração de sucesso, pois em bases temos, Vews, Triggers procedures e fuctions proprietárias(pertencentes aquele determinado usuário), como fazer isso para bases como muitos usuário pois fica muito dificil cadastrar todos na mão!! para ai sim DEPOIS poder fazer o recovery do DUMP. Desde já muito obrigado pela atenção.

      1. Meu amigo, Script muito bom testado e aprovado e fica essa enorme dica para quem tiver esse problema como eu tive, esta ai uma ótima forma de resolver o problema, ainda mais para quem tem bases muito grandes ou com muitos usuários. SENSACIONAL José Wilson, ajudou muito esse DBA iniciante aqui.

  3. José wilson gostaria de tirar mais, fiz esse processo no Oracle Linux 7 e funcionou perfeitamente, porem ao fazer o mesmo processo no Debian 8 ele me dá o seguinte erro: “bash: erro de sintaxe próximo do token' não esperado done’ ” referente ao “done > user.sql” , será que eu conseguiria mudar o done ou configurar o Bash para resolver esse problema?

    Desde já grato por sua ajuda.

    1. Ola Claudeir, tudo bem?

      Desculpe pela demora, época de Pós Graduação esta bem corrido. Sobre o seu erro é estranho, instalei um ambiente com Debian 8, simulei e não me deparei com o problema.

      Talvez o console esteja interpretando as aspas de uma maneira incorreta, é dificil apontar em exato. Você ainda esta com problemas? Se sim, me informe para que possamos conversar e buscar outras opções.

      Se preferir, também podemos conversar por e-mail através do:

      contato@sqlparatodos.com.br

      Att.,

Deixe uma resposta