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 transferidos da estrutura de árvore interna do MySQL para um arquivo em formato de texto. Uma das responsabilidades comuns de administradores web e de sistemas é, justamente, 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 formatados para otimização do armazenamento e dos acessos. Para serem extraídos, estes dados precisam ser convertidos ao formato adequado.

Um SQL dump é um arquivo de texto simples, que contém declarações SQL. Existem diversas formas de se criar um backup MySQL. Você pode usar, por exemplo, a interface de administração phpMyAdmin ou criar um backup com o MySQLDumper. Aqui, guiaremos você pela criação de um MySQL dump usando a ferramenta de linha de comando mysqldump.

O que significa MySQL dump?

O termo MySQL dump pode se referir a dois elementos:

1. Ao arquivo de exportação de um banco de dados MySQL
2. À ferramenta de linha de comando usada para criar uma exportação de banco de dados

Analisaremos, abaixo, cada um desses elementos.

MySQL dump como arquivo de exportação de banco de dados

O banco de dados MySQL organiza dados em estruturas otimizadas. Para dados gerais, uma árvore B (B-tree) é usada. No entanto, dados geográficos são armazenados em uma árvore R (R-tree). Um “dump” de dados diz respeito ao processo de transferir dados dessas estruturas em formato de árvore para uma representação linear.

Fazer um dump em uma árvore de dados significa criar um arquivo de texto correspondente, que pode ser armazenado e movido entre sistemas, usando métodos convencionais. Ele é necessário para a criação do backup de um banco de dados MySQL. Assim sendo, um MySQL dump contém declaraçõ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 instalação de banco de dados. Neste caso, declarações SQL são exportadas e o banco de dados é reconstituído passo a passo. Após a importação, o banco de dados deve conter os mesmos dados, na mesma estrutura anterior à realização do dump.

O processo completo de criação de backup MySQL, passando pela importaçã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 enigmático à primeira vista. Por isso destrinchamos, abaixo, as declarações SQL mais comumente encontradas em um MySQL dump:

MySQL dump como ferramenta para criar uma exportação de banco de dados

A ferramenta 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 armazenados:

which mysqldump

O comando retornará uma mensagem de erro se a ferramenta não estiver instalada no seu sistema.

Use o comando a seguir para exibir a versão da sua instalaçã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 ferramenta mysqldump deve ser feita por linha de comando. Primeiramente, você precisará acessar o servidor do banco de dados ou do container Docker, por SSH. Você precisará das três informaçõ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 informações, você conseguirá criar um backup MySQL. Diferentes abordagens podem ser feitas, dependendo de cada cenário e de cada requisito. Apresentaremos, neste tutorial, as três mais comuns.

Insira o comando mysqldump na linha de comando

O comando mysqldump pode ser inserido manualmente na linha de comando. O recurso é útil se você deseja criar um backup individual de um banco de dados, por exemplo, para migrar uma instalaçã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 tradicional é formado por seis componentes:

1. Comando mysqldump
2. Usuário do banco de dados
3. Senha do banco de dados
4. Nome do banco de dados
5. Encaminhamento do arquivo de saída
6. Caminho para o arquivo de saída

O comando mysqldump pode ser escrito de formas diferentes, dependendo de sua origem. Em vez de termos descritivos, como –user, versões encurtadas também podem ser usadas. A seguir apresentamos 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 declarações SQL redundantes, contidas no MySQL dump. O dump é compactado com facilidade, já que o MySQL dump é um arquivo de texto simples. É prática comum encaminhar o arquivo de saída do comando mysqldump à ferramenta de compressã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 configuração do banco de dados MySQL dump

O comando mysqldump pode ser executado na linha de comando, mas esse processo vem acompanhado de riscos de segurança. O problema é que a senha do banco de dados é transferida 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 previamente executados.

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 autorizados. Desta forma, o arquivo será lido antes da habilitaçã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 configuração. Simplesmente copie, na sua linha de comando, os comandos a seguir, e execute-os.

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

mkdir ~/. mysql-dump/

2. Escreva a configuraçã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 configuração no editor nano e ajuste os dados de acesso ao banco de dados:

nano ~/. mysql-dump/database.conf

Substitua os placeholders ‘nome_do_banco_de_dados’, ‘nome_de_usuario’ e ‘sua_senha’ pelas credenciais correspondentes. Em seguida, pressione [Ctrl] + [X], seguido por [Y] e por [Enter], para fechar o arquivo e salvar as alterações.

Concluída a preparação, você estará pronto para criar um backup MySQL.

4. Para criar um MySQL dump, leia as configuraçõ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 abordagens apresentadas até agora são ideais para exportar um banco de dados MySQL individual, 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 armazenados em bancos de dados MySQL com regularidade, assim como o MySQL dump faz com uma pasta predefinida. A periodizaçã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. Primeiramente, 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 individuais. Também vale a pena criar um arquivo de script para automatizar o processo. Siga os passos abaixo para configurar 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'
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 configuração no editor nano e ajuste os dados de acesso ao banco de dados:

nano ~/. mysql-dump/backup_databases

Substitua os placeholders ‘nome_do_banco_de_dados’, ‘nome_de_usuario’ e ‘sua_senha’ pelas credenciais correspondentes. Em seguida, pressione [Ctrl] + [X], seguido por [Y] e por [Enter], para fechar o arquivo e salvar as alterações.

4. Execute o script de backup para MySQL quando tiver finalizado 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 visualizar 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 configuraçã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>