Qual analista de suporte, que trabalha com hosting, nunca sofreu com a exportação e importação de uma base de dados gigante? Criar um dump (exportar uma base de dados) é uma tarefa relativamente simples, mas a história muda quando você precisa importar esta mesma base (arquivo .sql) e ela possui mais de 15GB, por exemplo.

Pois bem, estes dias eu precisei fazer este trabalho, exportar uma base de 17GB e importá-la em um novo banco de dados. Tentei fazer direto, sem compactação, e a exportação foi concluída com sucesso, porém, na hora de importar, tive vários timeout do mysql, por conta do tempo de execução e a base sempre ficava corrompida no final do processo.

Neste post, eu vou mostrar como eu consegui realizar todo o processo, junto com os comandos que utilizei. Não foi algo difícil, mas por serem vários processos acaba sendo demorado e cansativo. Estão preparados?

Exportando a base

Para exportar a base de dados, eu primeiro precisei obter uma lista de quais era as maiores tabelas. Para exemplificar, eu criei uma base de teste e poderemos ver os resultados “reais”. Com os dados do banco, vamos logar com a seguinte estrutura de código “mysql -u’usuario_base’ -p’senha_base’ nome_base” ficando assim:

mysql -u'kpopscom_userteste' -p'dasSF28768HbyHh86JyasscAd' kpopscom_bdteste

Após logar no banco iremos obter uma lista das maiores tabelas já com as informações em MB executando o seguinte comando:

SELECT table_name, table_rows, ROUND(data_length / (1024 * 1024), 2) AS data_length_mb, ROUND(index_length / (1024 * 1024), 2) AS index_length_mb
FROM information_schema.tables
WHERE table_schema = 'kpopscom_bdteste'
ORDER BY (data_length + index_length) DESC;

O retorno deste código será uma listagem com as maiores tabelas no topo, isso vai facilitar para que possamos saber quais tabelas iremos exportar primeiro. Será um retorno desta forma:

O banco acima não tem tantas tabelas grande (mais de um 1GB cada tabela), mas já temos a primeira tabela com 3.75GB e ela servirá para o nosso exemplo.

Com a informação das maiores tabelas, já sabemos quais serão as primeiras a serem exportadas e iremos fazer isso com o comando abaixo:

mysqldump --complete-insert --routines --triggers --single-transaction -u'kpopscom_userteste' -p'dasSF28768HbyHh86JyasscAd' kpopscom_bdteste bling_historico | gzip > bling_historico.sql.gz

Calma, vou explicar este comando caso ainda não conheça, vamos lá:

• mysqldump: É a ferramenta de linha de comando para fazer backups em MySQL e MariaDB.
• --complete-insert: Inclui instruções INSERT completas, ou seja, gera instruções INSERT com os nomes de colunas.
• --routines: Inclui stored procedures e funções no dump.
• --triggers: Inclui gatilhos no dump.
• --single-transaction: Realiza a exportação em uma única transação, garantindo que o banco de dados permaneça consistente durante o processo.
• -u'kpopscom_userteste': Especifica o nome de usuário (kpopscom_userteste) para acessar o banco de dados.
• -p'dasSF28768HbyHh86JyasscAd': Solicita a senha do usuário (dasSF28768HbyHh86JyasscAd). O -p sem um valor depois dele faz com que o sistema solicite a senha.
• kpopscom_bdteste: É o nome do banco de dados.
• bling_historico: É o nome da tabela que será incluída no dump.
• | gzip > bling_historico.sql.gz: Usa o operador de pipe (|) para redirecionar a saída do comando mysqldump para o comando gzip, que comprime o conteúdo. O resultado comprimido é então salvo no arquivo bling_historico.sql.gz.

Para simplificar com o foco do post, podemos resumir dizendo que este comando fará um dump já compactado apenas da tabela bling_historico.sql.gz, ou seja, ao invés de termos 3.75GB, nós teremos um arquivo chamado bling_historico.sql.gz com aproximadamente 140MB e isso fará toda a diferença no momento da importação no novo banco.

Tá, mas e seu tiver mais de uma tabela com mais de 1GB? Simples, basta você utilizar a mesma estrura do comando alterando o nome da tabela e o nome de saída. Vou deixar abaixo a estrura sem os dados anteriores:

mysqldump --complete-insert --routines --triggers --single-transaction -u'kpopscom_userteste' -p'dasSF28768HbyHh86JyasscAd' kpopscom_bdteste --ignore-table=kpopscom_bdteste.bling_historico | gzip > dump_personalizado.sql.gz

A única diferença deste comando para o anterior é que iremos realizar um dump ignorando a tabela que já exportamos anteriormente.

• --ignore-table=kpopscom_bdteste.bling_historico: Exclui a tabela bling_historico do dump, ou seja, não inclui essa tabela no arquivo de backup

Importando para o novo banco de dados

Agora que já temos os nossos arquivos bling_historico.sql.gz e dump_personalizado.sql.gz, nós podemos realizar a importação deles para o novo banco de dados. Vamos suporte que os dados do novo banco são os seguintes:

• Usuário: kpopscom_novouser
• Senha: dacKHoJnsj876Jnjns023S
• Base de dados: kpopscom_novodb

Esta tarefa, como mencionado anteriormente, é bem simples, porém, se tivermos uma base muito grande podemos ter problemas por conta do tempo de execução para importação da base, por isso separamos a maior (ou as maiores) tabelas do banco de dados.

Para importarmos os arquivos gerado, iremos precisar descompactar o arquivo e na sequência já ir importando para o banco e para isso iremos utilizar o seguinte comando:

gunzip -c bling_historico.sql.gz | mysql -u'kpopscom_novouser' -p'dacKHoJnsj876Jnjns023S' kpopscom_novodb

• gunzip -c bling_historico.sql.gz: Descomprime o conteúdo do arquivo bling_historico.sql.gz usando o gunzip e imprime o resultado para a saída padrão (-c).
• |: O operador de pipe redireciona a saída do comando anterior (a descompressão) como entrada para o próximo comando.
• mysql -u'kpopscom_novouser' -p'dacKHoJnsj876Jnjns023S' kpopscom_novodb: Utiliza o comando mysql para executar as instruções SQL diretamente no banco de dados. Os parâmetros fornecidos são:
  • -u'kpopscom_novouser': Especifica o nome de usuário (kpopscom_novouser) para acessar o banco de dados.
  • -p'dacKHoJnsj876Jnjns023S': Solicita a senha do usuário (dacKHoJnsj876Jnjns023S). O -p sem um valor depois dele faz com que o sistema solicite a senha.
  • kpopscom_novodb: É o nome do banco de dados para o qual as instruções SQL serão aplicadas.

Pronto, assim o tempo de importação deve ser muito menor e podemos evitar perdas de conexão por conta tempo que levaria uma base muito maior.

Para importar o arquivo dump_personalizado.sql.gz, basta utilizarmos o mesmo comando alterando o nome do arquivo. Ficará assim:

gunzip -c dump_personalizado.sql.gz | mysql -u'kpopscom_novouser' -p'dacKHoJnsj876Jnjns023S' kpopscom_novodb

Como podemos notar, a importação é algo muito simples, mas em alguns casos temos que fazer um processo um pouco maior antes dela para evitarmos problemas. Espero que este post ajude você mesmo que tenha outro problema na importação, mas que seja necessário “quebrar” o banco de dados em partes menores.