Como fazer backup e restaurar bancos de dados mysql com mysqldump

Este tutorial explica como fazer backup e restaurar bancos de dados MySQL ou MariaDB a partir da linha de comando usando o utilitário mysqldump.

Os arquivos de backup criados pelo utilitário mysqldump são basicamente um conjunto de instruções SQL que podem ser usadas para recriar o banco de dados original. O comando mysqldump também pode gerar arquivos no formato CSV e XML.

Você também pode usar o utilitário mysqldump para transferir seu banco de dados MySQL para outro servidor MySQL.

Sintaxe de comando do Mysqldump

Antes de começar a usar o comando mysqldump, vamos começar revisando a sintaxe básica.

As expressões do utilitário mysqldump assumem a seguinte forma:

mysqldump > file.sql

• options - As opções do mysqldump file.sql - O arquivo de backup (backup)

Para usar o comando mysqldump, o servidor MySQL deve estar acessível e em execução.

Faça backup de um único banco de dados MySQL

O caso de uso mais comum da ferramenta mysqldump é fazer backup de um único banco de dados.

Por exemplo, para criar um backup do banco de dados denominado database_name usando a root do usuário e salvá-lo em um arquivo chamado database_name.sql , execute o seguinte comando:

mysqldump -u root -p database_name > database_name.sql

Você será solicitado a inserir a senha root. Após a autenticação bem-sucedida, o processo de despejo será iniciado. Dependendo do tamanho do banco de dados, o processo pode levar algum tempo.

mysqldump database_name > database_name.sql

Backup de vários bancos de dados MySQL

Para fazer backup de vários bancos de dados MySQL com um comando, você precisa usar a opção --database seguida da lista de bancos de dados que deseja fazer backup. Cada nome do banco de dados deve ser separado por espaço.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

O comando acima criará um arquivo de despejo contendo os dois bancos de dados.

Backup de todos os bancos de dados MySQL

Use a opção --all-databases para fazer backup de todos os bancos de dados MySQL:

mysqldump -u root -p --all-databases > all_databases.sql

Assim como no exemplo anterior, o comando acima criará um único arquivo de despejo contendo todos os bancos de dados.

Faça backup de todos os bancos de dados MySQL para separar arquivos

O utilitário mysqldump não fornece uma opção para fazer backup de todos os bancos de dados para separar arquivos, mas é facilmente alcançado com um simples loop bash FOR :

for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done

O comando acima criará um arquivo de despejo separado para cada banco de dados usando o nome do banco de dados como o nome do arquivo.

Criar um backup compactado do banco de dados MySQL

Se o tamanho do banco de dados for muito grande, é uma boa idéia compactar a saída. Para fazer isso, basta canalizar a saída para o utilitário gzip e redirecioná-la para um arquivo, como mostrado abaixo:

mysqldump database_name | gzip > database_name.sql.gz

Criar um backup com carimbo de data e hora

mysqldump database_name > database_name-$(date +%Y%m%d).sql

O comando acima criará um arquivo com o seguinte formato database_name-20180617.sql

Restaurando um dump do MySQL

Você pode restaurar um dump do MySQL usando a ferramenta mysql . A sintaxe geral do comando é a seguinte:

mysqld database_name < file.sql

Na maioria dos casos, você precisará criar um banco de dados para importar. Se o banco de dados já existir, primeiro você precisará excluí-lo.

No exemplo a seguir, o primeiro comando criará um banco de dados chamado database_name e importará o dump database_name.sql para ele:

mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql

Restaurar um único banco de dados MySQL a partir de um despejo completo do MySQL

mysql --one-database database_name < all_databases.sql

Exportar e importar um banco de dados MySQL em um comando

Em vez de criar um arquivo de despejo de um banco de dados e importar o backup para outro banco de dados MySQL, você pode usar o seguinte recurso:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

O comando acima direcionará a saída para um cliente mysql no host remoto e importará para um banco de dados chamado remote_database_name . Antes de executar o comando, verifique se o banco de dados já existe no servidor remoto.

Automatizar backups com Cron

Automatizar o processo de backup dos bancos de dados é tão simples quanto criar um trabalho cron que executará o comando mysqldump no horário especificado.

Para configurar backups automatizados de um banco de dados MySQL usando cronjob, siga as etapas abaixo:

1. Crie um arquivo chamado .my.cnf no diretório inicial do usuário:

   sudo nano ~/.my.cnf

   Copie e cole o seguinte texto no arquivo.my.cnf.

   user = dbuser password = dbpasswd

   Não esqueça de substituir dbuser e dbpasswd pelo usuário do banco de dados e pela senha do usuário.

   Restrinja as permissões do arquivo de credenciais para que somente seu usuário tenha acesso a ele:

   chmod 600 ~/.my.cnf

   Crie um diretório para armazenar os backups:

   mkdir ~/db_backups

   Abra o arquivo crontab do usuário:

   crontab -e

   Adicione o seguinte trabalho cron que criará um backup de um nome de banco de dados mydb todos os dias às 3h:

   0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql

   Não se esqueça de substituir o username pelo seu nome de usuário real.

Você também pode criar outro cronjob para excluir todos os backups anteriores a 30 dias:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

Obviamente, você precisa ajustar o comando de acordo com o local do backup e os nomes dos arquivos. Para saber mais sobre o comando find, consulte o guia Como encontrar arquivos no Linux usando a linha de comando.

Conclusão

Este tutorial aborda apenas o básico, mas deve ser um bom começo para quem deseja aprender como criar e restaurar bancos de dados MySQL a partir da linha de comando usando o utilitário mysqldump.

Você também pode verificar o tutorial sobre como redefinir uma senha root do MySQL, caso a tenha esquecido.

mysql mariadb backup mysqldump