Como fazer backup MySQL com MySQL dump

O MySQL dump costuma ser usado para fazer backups de bancos de dados. Com ele, dados são trans­fe­ri­dos da estrutura de árvore interna do MySQL para um arquivo em formato de texto. Uma das res­pon­sa­bi­li­da­des comuns de ad­mi­nis­tra­do­res web e de sistemas é, jus­ta­mente, criar backups no MySQL — com a ajuda do MySQL dump. Entenda o que MySQL dump significa e aprenda a usá-lo com o nosso tutorial.

O que é backup MySQL e como criá-lo?

Além de ser usado para criar backups MySQL, o MySQL dump também pode ser usado para migrar dados entre dois bancos de dados. Um banco de dados MySQL contém dados que foram for­ma­ta­dos para oti­mi­za­ção do ar­ma­ze­na­mento e dos acessos. Para serem extraídos, estes dados precisam ser con­ver­ti­dos ao formato adequado.

Um SQL dump é um arquivo de texto simples, que contém de­cla­ra­ções SQL. Existem diversas formas de se criar um backup MySQL. Você pode usar, por exemplo, a interface de ad­mi­nis­tra­ção phpMyAd­min ou criar um backup com o MySQL­Dum­per. Aqui, guiaremos você pela criação de um MySQL dump usando a fer­ra­menta de linha de comando mysqldump.

O que significa MySQL dump?

O termo MySQL dump pode se referir a dois elementos:

1. Ao arquivo de ex­por­ta­ção de um banco de dados MySQL
2. À fer­ra­menta de linha de comando usada para criar uma ex­por­ta­ção de banco de dados

Ana­li­sa­re­mos, abaixo, cada um desses elementos.

MySQL dump como arquivo de ex­por­ta­ção de banco de dados

O banco de dados MySQL organiza dados em es­tru­tu­ras oti­mi­za­das. Para dados gerais, uma árvore B (B-tree) é usada. No entanto, dados ge­o­grá­fi­cos são ar­ma­ze­na­dos em uma árvore R (R-tree). Um “dump” de dados diz respeito ao processo de trans­fe­rir dados dessas es­tru­tu­ras em formato de árvore para uma re­pre­sen­ta­ção linear.

Fazer um dump em uma árvore de dados significa criar um arquivo de texto cor­res­pon­dente, que pode ser ar­ma­ze­nado e movido entre sistemas, usando métodos con­ven­ci­o­nais. Ele é ne­ces­sá­rio para a criação do backup de um banco de dados MySQL. Assim sendo, um MySQL dump contém de­cla­ra­ções SQL que refletem o conteúdo do banco de dados antes de passar pelo processo de dump.

Um dump também pode ser importado em outra ins­ta­la­ção de banco de dados. Neste caso, de­cla­ra­ções SQL são ex­por­ta­das e o banco de dados é re­cons­ti­tuído passo a passo. Após a im­por­ta­ção, o banco de dados deve conter os mesmos dados, na mesma estrutura anterior à re­a­li­za­ção do dump.

O processo completo de criação de backup MySQL, passando pela im­por­ta­ção do dump resulta no seguinte esquema:

mysql_import(mysql_dump(database)) == database

Talvez você já tenha aberto um MySQL dump em um editor de código ou em um editor de texto simples. Seu conteúdo pode parecer enig­má­tico à primeira vista. Por isso des­trin­cha­mos, abaixo, as de­cla­ra­ções SQL mais comumente en­con­tra­das em um MySQL dump:

MySQL dump como fer­ra­menta para criar uma ex­por­ta­ção de banco de dados

A fer­ra­menta de linha de comando mysqldump costuma vir instalada como parte do cliente MySQL ou do servidor MySQL. Se você quiser usá-la, precisará confirmar se ela está instalada no seu sistema local. Use o comando a seguir para verificar onde os dados binários do mysqldump estão ar­ma­ze­na­dos:

which mysqldump

O comando retornará uma mensagem de erro se a fer­ra­menta não estiver instalada no seu sistema.

Use o comando a seguir para exibir a versão da sua ins­ta­la­ção mysqldump:

mysqldump --version

Para acessar as opções de ajuda do mysqldump, digite o seguinte comando:

mysqldump --help

Aprenda, abaixo, como usar o comando mysqldump para criar um backup MySQL.

Tutorial passo a passo para criar um MySQL dump

A criação de um backup MySQL com a fer­ra­menta mysqldump deve ser feita por linha de comando. Pri­mei­ra­mente, você precisará acessar o servidor do banco de dados ou do container Docker, por SSH. Você precisará das três in­for­ma­ções listadas abaixo para iniciar o processo de criação do backup MySQL:

1. Nome do banco de dados a ser exportado
2. Nome de usuário do banco de dados
3. Senha do usuário do banco de dados

Em posse dessas três in­for­ma­ções, você con­se­guirá criar um backup MySQL. Di­fe­ren­tes abor­da­gens podem ser feitas, de­pen­dendo de cada cenário e de cada requisito. Apre­sen­ta­re­mos, neste tutorial, as três mais comuns.

Insira o comando mysqldump na linha de comando

O comando mysqldump pode ser inserido ma­nu­al­mente na linha de comando. O recurso é útil se você deseja criar um backup in­di­vi­dual de um banco de dados, por exemplo, para migrar uma ins­ta­la­ção WordPress de servidor. O esquema geral para habilitar o comando mysqldump é o seguinte:

mysqldump --user=<usuário> --password=<senha> <banco de dados> > <caminho/para/mysql_dump.sql>

Um comando mysqldump tra­di­ci­o­nal é formado por seis com­po­nen­tes:

1. Comando mysqldump
2. Usuário do banco de dados
3. Senha do banco de dados
4. Nome do banco de dados
5. En­ca­mi­nha­mento do arquivo de saída
6. Caminho para o arquivo de saída

O comando mysqldump pode ser escrito de formas di­fe­ren­tes, de­pen­dendo de sua origem. Em vez de termos des­cri­ti­vos, como –user, versões en­cur­ta­das também podem ser usadas. A seguir apre­sen­ta­mos o esquema encurtado, do mesmo exemplo utilizado acima:

mysqldump -u<usuário> -p<senha> <banco de dados> > <caminho/para/mysql_dump.sql>

Converter dados de uma estrutura de árvore costuma resultar em de­cla­ra­ções SQL re­dun­dan­tes, contidas no MySQL dump. O dump é com­pac­tado com fa­ci­li­dade, já que o MySQL dump é um arquivo de texto simples. É prática comum en­ca­mi­nhar o arquivo de saída do comando mysqldump à fer­ra­menta de com­pres­são Gzip. Um processo de convenção resulta em um arquivo com a extensão .sql.gz:

mysqldump --user=<usuário> --password=<senha> <banco de dados> | gzip > <caminho/para/mysql_dump.sql.gz>

Conheça, na tabela, as três variantes mais comumente aplicadas ao comando mysqldump, para criação de backups MySQL:

Use o arquivo de con­fi­gu­ra­ção do banco de dados MySQL dump

O comando mysqldump pode ser executado na linha de comando, mas esse processo vem acom­pa­nhado de riscos de segurança. O problema é que a senha do banco de dados é trans­fe­rida em formato de texto, como parte do comando, uma vez que todos os comandos inseridos na linha de comando são gravados em um arquivo especial por padrão. Assim sendo, esses dados sensíveis poderão ser vistos por alguém que acessar o histórico de comandos pre­vi­a­mente exe­cu­ta­dos.

Pode ser uma boa ideia definir dados de acesso em um arquivo separado, como variáveis, para proteger o banco de dados contra acessos não au­to­ri­za­dos. Desta forma, o arquivo será lido antes da ha­bi­li­ta­ção do comando mysqldump. Somente os nomes das variáveis serão exibidos na gravação do histórico, o que manterá os valores reais ocultos.

A partir de agora, aprenda a criar um backup MySQL usando um arquivo de con­fi­gu­ra­ção. Sim­ples­mente copie, na sua linha de comando, os comandos a seguir, e execute-os.

1. Em primeiro lugar, crie uma pasta para o arquivo de con­fi­gu­ra­ção do banco de dados MySQL dump abaixo da pasta do usuário:

mkdir ~/. mysql-dump/

2. Escreva a con­fi­gu­ra­ção do banco de dados MySQL dump em um novo arquivo, usando o código a seguir:

cat << EOF > ~/. mysql-dump/database.conf
DB_NAME= 'nome_do_banco_de_dados'
DB_USER= 'nome_de_usuario'
DB_PASS= 'sua_senha'
EOF

3. Abra o arquivo de con­fi­gu­ra­ção no editor nano e ajuste os dados de acesso ao banco de dados:

nano ~/. mysql-dump/database.conf

Substitua os pla­cehol­ders ‘nome_do_banco_de_dados’, ‘nome_de_usuario’ e ‘sua_senha’ pelas cre­den­ci­ais cor­res­pon­den­tes. Em seguida, pressione [Ctrl] + [X], seguido por [Y] e por [Enter], para fechar o arquivo e salvar as al­te­ra­ções.

Concluída a pre­pa­ra­ção, você estará pronto para criar um backup MySQL.

4. Para criar um MySQL dump, leia as con­fi­gu­ra­ções com o comando source e, então, execute o MySQL dump:

source ~/. mysql-dump/database.conf
mysqldump --user="$DB_USER" --password="$DB_PASS" "$DB_NAME" > <caminho/para/mysql_dump.sql>

Crie um script de backup MySQL

As abor­da­gens apre­sen­ta­das até agora são ideais para exportar um banco de dados MySQL in­di­vi­dual, com rapidez. É uma boa ideia, contudo, criar um script para produzir backups regulares ou fazer backups de múltiplos bancos de dados MySQL. Um script criará backups de conteúdos ar­ma­ze­na­dos em bancos de dados MySQL com re­gu­la­ri­dade, assim como o MySQL dump faz com uma pasta pre­de­fi­nida. A pe­ri­o­di­za­ção do processo também o padroniza, o que reduz o risco de erros.

A última abordagem diz respeito à criação de um script de backup. Pri­mei­ra­mente, crie uma pasta adicional para backups MySQL. Cada backup será, desta forma, destinado a uma subpasta própria, que conterá os MySQL dumps dos bancos de dados in­di­vi­du­ais. Também vale a pena criar um arquivo de script para au­to­ma­ti­zar o processo. Siga os passos abaixo para con­fi­gu­rar um script de backup para MySQL:

1. Crie uma pasta adicional para os backups MySQL:

mkdir -p ~/.mysql-dump/backups/

2. Em seguida, crie o script de backup para MySQL:

cat << EOF > ~/. mysql-dump/backup_databases
DB_USER='nome_de_usuario'
DB_PASS='sua_senha'
# Crie nova pasta para backup atual
backup_dir='./backups/$(date +%F_%H%M)'
# Determine bancos de dados
databases=$(mysql --user="$DB_USER" --password="$DB_PASS" -Bs --execute 'SHOW DATABASES')
# Exporte todos os bancos de dados
for database in $databases; do
    mysqldump --user="$DB_USER" --password="$DB_PASS" "$database" > "${backup_dir}/${database}.sql"
done
# Delete variáveis da memória
unset DB_USER DB_PASS
EOF

3. Abra o arquivo de con­fi­gu­ra­ção no editor nano e ajuste os dados de acesso ao banco de dados:

nano ~/. mysql-dump/backup_databases

Substitua os pla­cehol­ders ‘nome_do_banco_de_dados’, ‘nome_de_usuario’ e ‘sua_senha’ pelas cre­den­ci­ais cor­res­pon­den­tes. Em seguida, pressione [Ctrl] + [X], seguido por [Y] e por [Enter], para fechar o arquivo e salvar as al­te­ra­ções.

4. Execute o script de backup para MySQL quando tiver fi­na­li­zado o processo:

cd ~/. mysql-dump/ && source backup_databases

5. Faça um teste para confirmar se o backup foi criado na etapa anterior:

ls -la ~/. mysql-dump/backups/*

Se você conseguir vi­su­a­li­zar arquivos com a extensão .sql, significa que o backup funcionou.

Importe MySQL dump para o banco de dados

O comando mysqldump é usado apenas para exportar um banco de dados MySQL. Já o comando mysql é usado para importar um dump. Observe o esquema geral:

mysql --user=<usuário> --password=<senha> banco de dados < <caminho/para/mysql_dump.sql>

O comando a seguir pode ser usado se um arquivo de con­fi­gu­ra­ção de banco de dados MySQL dump já existir:

source ~/. mysql-dump/database.conf
mysql --user="$DB_USER" --password="$DB_PASS" "$DB_NAME" < <caminho/para/mysql_dump.sql>