Migrando bases grandes no MySQL

Início:

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.

Procedimentos:

1º 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.

1 | mysql> create user ze@'%' identified by 'password';
2 | mysql> grant all privileges on database.* to ze@'%'; 

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

1 | shell> mysqldump -R -h ipserverorigem -u ze -psenha nomedabase > backup.sql

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:

1 | mysql> SET GLOBAL max_allowed_packet=1073741824;

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

1 | mysql> SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'; 

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:

1 | mysql> SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
 
2 | mysql> SET GLOBAL wait_timeout=28800;

 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ó.

3º 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:

1 | mysql> SELECT table_name FROM information_schema.tables WHERE table_schema = 'nomedabase' into oufile '/tmp/tables.txt';

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

1 | shell> cd /tmp
2 | shell> for i in $(cat tables.txt); do echo backup da tabela $i; mysqldump -R -h ipserverorigem -u ze -psenha nomedabase $i > $i.sql ;done

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:

1 | shell> mysql -h ipdoservidor -u usuario -psenha nomedabase < backup.sql
ou
2 | shell> cat backup.sql | mysql -h ipdoservidor -u usuario -psenha nomedabase

 Caso tenha feito o backup tabela por tabela:

1 | for i in $(cat tables.txt); do echo restore da tabela $i; mysql -h ipdoservidor -u root -psenha -D nomedabase < $i.sql ;done
ou
2 | for i in $(cat tables.txt); do echo restore da tabela $i; cat $i.sql | mysql -h ipdoservidor -u root -psenha nomedabase ;done

 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.