No trabalho de manutenção diária, muitas vezes são necessárias operações de exportação de dados e o mysqldump é uma ferramenta frequentemente utilizada para exportar dados. Ele possui muitos parâmetros funcionais integrados e este artigo listará algumas operações comumente usadas.
O que é mysqldump?
mysqldump é um programa cliente do MySQL usado para descarregar bancos de dados. Ele gera principalmente uma série de instruções SQL que podem ser encapsuladas em um arquivo. Esse arquivo contém todos os comandos SQL necessários para reconstruir seu banco de dados, como CREATE DATABASE, CREATE TABLE, INSERT, etc. Pode ser usado para migração ou recuperação rápida e leve de banco de dados. É um método para realizar backups lógicos em bancos de dados MySQL.
Como exportar um banco de dados MySQL usando mysqldump?
1. Exportar todos os bancos de dados
Este comando exporta todos os bancos de dados, incluindo bancos de dados do sistema.
mysqldump -uroot -proot --all-databases > /tmp/all.sql
2. Exportar todos os dados dos bancos de dados db1 e db2
mysqldump -uroot -proot --databases db1 db2 > /tmp/user.sql
Exporta vários bancos de dados. Todos os parâmetros subsequentes após a opção são considerados nomes de bancos de dados.
Adicione uma instrução DROP DATABASE antes de criar cada banco de dados.
mysqldump -uroot -p --databases test --add-drop-database
3. Exportar as tabelas a1 e a2 do db1
Observação que a exportação de tabelas específicas só pode ser feita para um único banco de dados, e o conteúdo exportado difere da exportação de um banco de dados. O texto exportado para tabelas específicas não inclui instruções de criação do banco de dados; ele inclui apenas instruções DROP TABLE, CREATE TABLE, e INSERT.
mysqldump -uroot -proot --databases db1 --tables a1 a2 > /tmp/db1.sql
Substituir o parâmetro --databases e especificar as tabelas a exportar.
mysqldump -uroot -p --host=localhost --databases test --tables test
Exportar todos os tablespace.
mysqldump -uroot -p --databases test --all-tablespaces
Excluir todas as informações de tablespace da exportação.
mysqldump -uroot -p --databases test --no-tablespaces
Adicione uma instrução DROP TABLE antes de criar cada tabela (ativado por padrão, use --skip-add-drop-table para desativar).
mysqldump -uroot -p --databases test
mysqldump -uroot -p --databases test --skip-add-drop-table
4. Exportação condicional: Exportar registros com id=1 da tabela a1 no db1
A exportação condicional só pode ser realizada em uma única tabela.
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' > /tmp/a1.sql
Descarregar apenas os registros selecionados pela condição WHERE fornecida. Se a condição contiver interpretadores de comandos, espaços ou caracteres especiais, ela deve ser colocada entre aspas.
mysqldump -uroot -p --host=localhost --databases db1 --where="user='root'"
5. Exportar apenas estruturas de tabelas, sem dados
mysqldump -uroot -proot --no-data --databases db1 > /tmp/db1.sql
Exportar apenas dados, sem adicionar instruções CREATE DATABASE.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-db
Exportar apenas dados, sem adicionar instruções CREATE TABLE.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-info
Exclua tabelas específicas. Para ignorar várias tabelas, repita a opção para cada tabela. Cada tabela deve especificar o nome do banco de dados e da tabela, por exemplo, --ignore-table=database.table1 --ignore-table=database.table2 ...
mysqldump -uroot -p --host=localhost --databases db1 --ignore-table=mysql.user
Utilize o modo de inserção diferido (INSERT DELAYED) ao exportar dados.
mysqldump -uroot -p --databases db1 --delayed-insert
Utilize instruções INSERT com várias cláusulas VALUES. Isso reduz o tamanho do arquivo de exportação e acelera a importação. Habilitado por padrão; use --skip-extended-insert para desativar.
mysqldump -uroot -p --databases db1
mysqldump -uroot -p --databases db1 --skip-extended-insert
6. Exportar e importar dados entre servidores
mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2
Isso significa exportar todos os dados do banco de dados db1 no servidor h1 e importá-los para o banco de dados db2 em h2. O banco de dados db2 deve existir; caso contrário, ocorrerá um erro.
Use um pipe nomeado para se conectar ao MySQL.
mysqldump -uroot -p --host=localhost --all-databases --pipe
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
Ative a compressão para a comunicação entre cliente e servidor.
mysqldump -uroot -p --all-databases --compress
7. Acrescentar a posição do binlog e o nome do arquivo do banco de dados mestre ao arquivo de exportação
Esta opção, quando executada em um servidor escravo, é equivalente a executar SHOW SLAVE STATUS.
Defini-lo como 1 gera um comando CHANGE MASTER no arquivo de dados.
Defini-lo como 2 adiciona comentários antes de CHANGE MASTER.
Esta opção ativa --lock-all-tables a menos que --single-transaction seja especificado.
Após a execução, --lock-tables é automaticamente desativado. O valor padrão para --dump-slave é 1.
mysqldump -uroot -proot --dump-slave=1 --database db1 > /tmp/db1.sql
mysqldump -uroot -proot --dump-slave=2 --database db1 > /tmp/db1.sql
Exportar eventos.
mysqldump -uroot -p --all-databases --events
8. Limpar registros antes da exportação
Nota: Se estiver exportando vários bancos de dados (--databases ou --all-databases), os logs são descarregados por banco de dados. Se usar --lock-all-tables ou --master-data, os logs são descarregados uma única vez, bloqueando todas as tabelas. Para exportar e descarregar logs simultaneamente, use --lock-all-tables ou --master-data juntamente com --flush-logs.
mysqldump -uroot -p --all-databases --flush-logs
9. Garantir estado consistente durante a exportação
Antes de exportar os dados, esta opção emite uma instrução SQL BEGIN. O comando BEGIN não bloqueia nenhuma aplicação e garante a consistência do banco de dados durante a exportação. É aplicável apenas a mecanismos de armazenamento multiversão (por exemplo, InnoDB). Esta opção é mutuamente exclusiva com --lock-tables, pois o LOCK TABLES confirma implicitamente quaisquer transações pendentes. Para exportar tabelas grandes, utilize-o com a opção --quick.
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
10. Exportar procedimentos armazenados e funções
mysqldump -uroot -p --host=localhost --all-databases --routines
Outro método: usando SELECT...INTO OUTFILE para exportar dados do MySQL
SELECT...INTO OUTFILE é uma sintaxe do MySQL usada para exportar os resultados da consulta para um arquivo. No MySQL, você pode usar esta instrução para exportar facilmente os dados para um arquivo de texto.
Uso Básico:
SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;
Exemplo:
SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;
No comando SQL acima, selecionamos as colunas id, name e email da tabela users e gravamos os resultados no arquivo /tmp/user_data.csv. FIELDS TERMINATED BY ',' especifica o separador de colunas (vírgula) e LINES TERMINATED BY '\n' especifica o separador de linhas (nova linha).
Notas Importantes:
A execução de SELECT...INTO OUTFILE requer as permissões apropriadas, e o diretório do arquivo de saída deve ser um local no qual o servidor MySQL possa gravar.
No exemplo a seguir, exportamos dados da tabela runoob_tbl para o arquivo /tmp/runoob.txt:
mysql> SELECT * FROM runoob_tbl
-> INTO OUTFILE '/tmp/runoob.txt';
Pode especificar o formato de saída usando opções de comando. O exemplo a seguir exporta dados em formato CSV:
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Propriedades de SELECT ... INTO OUTFILE:
LOAD DATA INFILE é a operação inversa de SELECT ... INTO OUTFILE. Para gravar dados do banco de dados em um arquivo, use SELECT ... INTO OUTFILE, e para ler o arquivo novamente no banco de dados, use LOAD DATA INFILE.
Uma instrução SELECT na forma SELECT...INTO OUTFILE 'file_name' grava as linhas selecionadas em um arquivo. Como o arquivo é criado no host do servidor, você deve ter o privilégio FILE para usar essa sintaxe.
O arquivo de saída não deve existir previamente, impedindo a adulteração de dados.
Necessita de uma conta com acesso de login ao servidor para recuperar o arquivo. Caso contrário, o SELECT ... INTO OUTFILE não funcionará.
Em sistemas UNIX, após a criação do arquivo, ele é legível e suas permissões pertencem ao servidor MySQL. Isso significa que, embora você possa ler o arquivo, talvez não consiga excluí-lo.
Proteção Completa para o Seu Ecossistema de Banco de Dados
Em uma era em que a resiliência dos dados é essencial, escolher uma solução de backup com escalabilidade global e agilidade multi-nuvem é fundamental. Vinchin Backup & Recovery destaca-se pela sua proteção robusta do MySQL, desenvolvida especialmente para empresas internacionais:
✅ Estratégias Abrangentes de Backup
Suporta estratégias de backup quente, backup completo, backup incremental e backup de log de transações;
✅ Suporte Universal a Bancos de Dados
Unifique a proteção em MySQL, Oracle, SQL Server e mais bancos de dados em ambientes híbridos (físico/nuvem/VM).
Simplifique a gestão com um único console para políticas de backup de múltiplos bancos de dados.
✅ Reforço anti-ransomware
Armazenamento de dados de backup criptografado + recurso anti-ransomware para resistir a ataques maliciosos.
✅ Suporte Global, Eficiência Local
Assistência técnica 24/7 e licenciamento com custo otimizado, ideal para PMEs e equipes distribuídas.
Para criar tarefas de backup de banco de dados MySQL com o Vinchin, acesse a página Physical Backup > Database Backup > Backup:
1. Selecione os bancos de dados que precisam ser copiados.
2. Selecione um nó de backup no qual você deseja que os dados de backup sejam processados e armazenados.
3. Configure estratégias de backup de acordo com suas necessidades.
4. Revise e confirme as configurações.
A Vinchin é confiável para instituições financeiras e infraestruturas de TI multinacionais em mais de 60 países. Clique no botão abaixo para experimentar o teste gratuito de 60 dias da Vinchin e conhecer uma solução eficiente e confiável de backup de dados!
Conclusão
Neste artigo, exploramos várias opções do mysqldump para exportar eficientemente dados do banco de dados MySQL, desde exportações completas do banco de dados até exportações mais granulares, como tabelas individuais ou registros específicos. Compreender esses comandos e suas funcionalidades ajuda a simplificar os processos de backup, migração e recuperação do banco de dados.
