MySQL: O Guia Completo para Backup e Recuperação do MySQL

Última atualização: 2026-08-26

O disco rígido do servidor da empresa de Alice apresentou uma falha repentina, causando a perda total do banco de dados. Sem backups, três anos de dados de clientes, registros de pedidos e informações financeiras foram perdidos para sempre. Esse desastre serviu como um alerta para ela, levando-a a estabelecer um sistema de backup abrangente: backups diários completos com mysqldump, backups incrementais contínuos do binlog, transferências semanais de dados para um local externo e simulados de recuperação mensais. Desde então, mesmo que ela enfrente outra falha de hardware, ela consegue restaurar totalmente todos os dados em 30 minutos.

Nesta aula, você aprenderá:

• 5 modos de backup lógico do mysqldump (Completo/Banco de dados único/Tabela única/Apenas estrutura/Apenas dados)
• Como realizar a recuperação usando o comando MySQL e a instrução SOURCE
• SELECT INTO OUTFILE (exportação) e LOAD DATA INFILE (importação)
• O processo completo do log binário (binlog) e da recuperação em um ponto no tempo (PITR)
• XtraBackup: Princípios dos backups físicos e elaboração de estratégias de backup

flowchart TD
    A[Daily Full Backup<br/>mysqldump --single-transaction] --> B[binlog Ongoing Record<br/>Real-Time Incremental]
    B --> C[Weekly Remote Data Transfer<br/>rsync/scp To Remote]
    C --> D[Monthly Recovery Drills<br/>Verify Backup Availability]
    D -->|Drill Passed| A
    D -->|Identifying Problems| E[Adjust the Backup Strategy]
    E --> A

flowchart TD
    A[Daily Full Backup<br/>mysqldump --single-transaction] --> B[binlog Ongoing Record<br/>Real-Time Incremental]
    B --> C[Weekly Remote Data Transfer<br/>rsync/scp To Remote]
    C --> D[Monthly Recovery Drills<br/>Verify Backup Availability]
    D -->|Drill Passed| A
    D -->|Identifying Problems| E[Adjust the Backup Strategy]
    E --> A

1. História: O preço de não ter um backup

A TechFlow, empresa onde Alice trabalha, opera um banco de dados MySQL para sua plataforma de comércio eletrônico, que armazena três anos de pedidos de clientes e informações de contas. A equipe de operações nunca havia estabelecido um processo formal de backup, contando exclusivamente com um array RAID como “rede de segurança”. Numa sexta-feira à noite, uma falha no sistema de ar-condicionado do data center causou o superaquecimento dos servidores, resultando na falha simultânea de dois discos rígidos e no colapso do array RAID. Todos os dados — informações de clientes, registros de transações e dados de estoque — foram perdidos. A empresa passou três meses tentando recuperar os dados, mas conseguiu recuperar menos de 10% deles. O prejuízo financeiro direto ultrapassou 500.000 dólares, e a empresa também perdeu grande parte da confiança dos clientes.

Após essa lição dolorosa, Alice assumiu a liderança na implantação de um sistema abrangente de backup: backups completos automáticos todos os dias à meia-noite, backups incrementais em tempo real por meio do binlog, sincronização semanal em local externo e simulados de recuperação mensais. Seis meses depois, o servidor apresentou falha novamente, mas, dessa vez, ela concluiu uma recuperação completa em 30 minutos, praticamente sem interrupção nos negócios.

2. Backups lógicos e backups físicos

(1) Visão geral dos backups lógicos

Um backup lógico exporta os dados de um banco de dados para um arquivo de texto SQL contendo instruções como CREATE TABLE e INSERT. Para restaurar os dados, basta reexecutar essas instruções SQL. Uma ferramenta representativa é o mysqldump.

Vantagens: Alta legibilidade, compatibilidade entre versões e a capacidade de fazer backup seletivo de bancos de dados e tabelas individuais.

Desvantagens: Baixa velocidade; tanto o backup quanto a restauração exigem a participação do processo do MySQL; demorado para bancos de dados grandes.

(2) Visão geral dos backups físicos

Um backup físico copia diretamente os arquivos de dados subjacentes do banco de dados (.ibd, .frm, etc.); para restaurar, basta copiar os arquivos de volta para o diretório de dados. Uma ferramenta representativa é o XtraBackup.

Vantagens: Rápido, sem bloqueio de tabelas (InnoDB), adequado para grandes bancos de dados.

Desvantagens: Não é legível por humanos, tem compatibilidade limitada entre plataformas e exige tempo de inatividade ou o uso de ferramentas especializadas para manter a consistência.

3. Uma explicação detalhada sobre os backups lógicos do mysqldump

(1) Backup completo

Um backup completo exporta todos os dados de todos os bancos de dados; é o método de backup mais básico e seguro.

mysqldump -u root -p --all-databases --single-transaction > full_backup.sql

O parâmetro --single-transaction utiliza instantâneos de consistência para leituras em tabelas InnoDB e não bloqueia a tabela. As tabelas MyISAM continuam bloqueadas.

(2) Backups de um único banco de dados e de uma única tabela

# Backing Up a Single Database
mysqldump -u root -p --single-transaction mydb > mydb_backup.sql

# Back up the specified table
mysqldump -u root -p --single-transaction mydb users orders > tables_backup.sql

(3) Separação entre estrutura e dados

# Back up only the table structure
mysqldump -u root -p --no-data mydb > mydb_schema.sql

# Back up only the data
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

▶ Exemplo: Cinco modos de backup do mysqldump

# Pattern1：Full Backup（All Libraries）
mysqldump -u root -p --all-databases \
  --single-transaction --routines --triggers \
  > full_backup_$(date +%Y%m%d).sql

# Pattern2：Single-Database Backup
mysqldump -u root -p --single-transaction mydb > mydb.sql

# Pattern3：Single-Table Backup
mysqldump -u root -p --single-transaction mydb users > users.sql

# Pattern4：Structure Only
mysqldump -u root -p --no-data mydb > schema.sql

# Pattern5：Data Only
mysqldump -u root -p --no-create-info mydb > data.sql

4. Restaurar o banco de dados

(1) Recuperação de comandos do MySQL

Restaurar dados de um arquivo de backup do SQL para um banco de dados especificado:

mysql -u root -p mydb < mydb_backup.sql

Restaurar um arquivo de backup compactado:

gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

(2) Restauração com o comando SOURCE

Faça a restauração usando o comando SOURCE no cliente do MySQL:

USE mydb;
SOURCE /var/backups/mysql/mydb_backup.sql;

▶ Exemplo: Processo de recuperação

# Steps1：Create the target database
mysql -u root -p -e "CREATE DATABASE mydb_restore;"

# Steps2：Restore Data
mysql -u root -p mydb_restore < mydb_backup.sql

# Steps3：Number of rows to verify
mysql -u root -p -e "SELECT COUNT(*) FROM mydb_restore.users;"

▶ Exemplo: Restaurando um backup compactado

# Restore gzip Compressed Backup
gunzip < /backup/mydb_20260703.sql.gz | mysql -u root -p mydb

# Restore and display progress
pv /backup/mydb_20260703.sql.gz | gunzip | mysql -u root -p mydb

5. Exportação e importação de dados

(1) SELECT INTO OUTFILE

Exporte os resultados da consulta para um arquivo CSV no servidor:

SELECT id, name, email
FROM users
WHERE status = 'active'
INTO OUTFILE '/tmp/active_users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Observação: O caminho para INTO OUTFILE é o caminho no servidor MySQL, e não o caminho do cliente. O processo do MySQL deve ter permissões de gravação para esse caminho, e a variável secure_file_priv deve permitir o acesso a esse diretório.

(2) LOAD DATA INFILE

Importar dados de um arquivo CSV do lado do servidor para uma tabela:

LOAD DATA INFILE '/tmp/active_users.csv'
INTO TABLE users_copy
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

▶ Exemplo: O processo completo para exportar e importar arquivos CSV

-- Export order data as CSV
SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_date >= '2026-01-01'
INTO OUTFILE '/tmp/orders_2026.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Import into another table
LOAD DATA INFILE '/tmp/orders_2026.csv'
INTO TABLE orders_archive
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

▶ Exemplo: Importação de arquivos de clientes usando LOCAL

LOAD DATA LOCAL INFILE '/home/alice/data/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

6. Registros binários e recuperação em um ponto no tempo

(1) Noções básicas sobre o binlog

O log binário registra todas as instruções SQL que modificam dados e é utilizado principalmente para replicação mestre-escravo e recuperação em um ponto no tempo (PITR).

-- View binlog Enabled or Disabled
SHOW VARIABLES LIKE 'log_bin%';

-- View Current binlog List of Files
SHOW BINARY LOGS;

-- View the data currently being written binlog
SHOW MASTER STATUS;

-- View binlog Details of the Incident
SHOW BINLOG EVENTS IN 'binlog.000003';

Três formatos de binlog:

(2) Processo de recuperação em um ponto no tempo

A abordagem principal para a recuperação em um momento específico: Primeiro, restaurar o backup completo mais recente; em seguida, reproduzir o binlog até o momento alvo.

# Steps1：Restore a Full Backup
mysql -u root -p mydb < full_backup_20260703.sql

# Steps2：Find the point in time before the error occurred
mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000003 | grep -A5 "DROP TABLE"

# Steps3：Replay binlog By the target date
mysqlbinlog --start-datetime="2026-07-03 02:00:00" \
  --stop-datetime="2026-07-03 14:30:00" \
  binlog.000003 binlog.000004 | mysql -u root -p

▶ Exemplo: Recuperação em um ponto específico no tempo após a exclusão acidental de uma tabela

Scene: At 14:25, accidentally executed DROP TABLE orders, need to revert to 14:24:59 state

Timeline:
  02:00  Full backup completed
  ...
  14:24  Last normal operation
  14:25  DROP TABLE orders (operational error)
  14:30  Problem identified, start recovery

Recovery steps:
  1. Restore 02:00 full backup
  2. Use mysqlbinlog to replay binlog from 02:00 to 14:24:59
  3. Verify orders table data integrity

# Execute the command
mysql -u root -p mydb < /backup/full_20260703.sql

mysqlbinlog --start-datetime="2026-07-03 02:00:00" \
  --stop-datetime="2026-07-03 14:24:59" \
  /var/lib/mysql/binlog.000003 \
  /var/lib/mysql/binlog.000004 \
  | mysql -u root -p mydb

7. Backup físico com o XtraBackup

(1) Como funciona o XtraBackup

O XtraBackup é uma ferramenta de backup físico de código aberto desenvolvida pela Percona. Seu princípio fundamental é:

1. Thread em segundo plano Lê continuamente as páginas de dados do InnoDB enquanto monitora as alterações no log de refazer
2. Os novos logs de refazer gerados durante o backup também são registrados continuamente.
3. Após a conclusão do backup, use o log de refazer para atualizar as páginas de dados até um estado consistente.
4. Não há bloqueio de tabelas durante todo o processo; as operações de leitura e gravação de dados não são afetadas.

(2) Operações básicas

# Full Backup
xtrabackup --backup --target-dir=/backup/full -u root -p

# Preparing to Back Up（Applications redo log，Ensure data consistency）
xtrabackup --prepare --target-dir=/backup/full

# Restore Backup
xtrabackup --copy-back --target-dir=/backup/full

# Incremental Backup（Based on the full dataset）
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full -u root -p

# Preparing an Incremental Backup
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

▶ Exemplo: Backup completo e restauração com o XtraBackup

# Full Backup
xtrabackup --backup \
  --target-dir=/data/backup/full_20260703 \
  --user=root --password=Secret123

# Preparation Phase
xtrabackup --prepare \
  --target-dir=/data/backup/full_20260703

# Stop before restoring MySQL
systemctl stop mysqld

# Clear the Data Directory and Restore
rm -rf /var/lib/mysql/*
xtrabackup --copy-back \
  --target-dir=/data/backup/full_20260703

# Repair permissions and start
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

8. Elaboração de uma estratégia de backup

(1) Combinação completa e incremental

• Backup completo diário + Backup incremental do binlog em tempo real: Adequado para bancos de dados de pequeno e médio porte; fácil de restaurar
• Backup completo semanal + Backup incremental diário (XtraBackup): Adequado para bancos de dados grandes; economiza espaço

(2) Backup externo

Os arquivos de backup devem ser transferidos para um local de armazenamento externo a fim de evitar desastres no centro de dados:

# Usage rsync Transmit to a remote location
rsync -avz /backup/mysql/ backup-server:/data/mysql_backup/

# Usage scp Transmission
scp /backup/mysql/full_20260703.sql.gz backup-server:/data/backup/

(3) Simulados regulares de recuperação em caso de desastre

Um backup que não é verificado não é um backup de verdade. Realize um teste completo de recuperação pelo menos uma vez por mês para verificar o seguinte:

• Os arquivos de backup podem ser extraídos e restaurados normalmente
• A verificação de integridade dos dados foi aprovada após a recuperação
• O tempo de recuperação está dentro de um intervalo aceitável de RTO

9. Backups automatizados e exemplos detalhados

(1) Pontos-chave para backups automatizados

• Use o crontab para executar scripts de backup de forma programada
• Os arquivos de backup são nomeados por data, o que facilita encontrá-los
• A compactação automática economiza espaço
• Excluir regularmente os backups vencidos (manter por N dias)
• Enviar uma notificação assim que o backup for concluído

▶ Exemplo: Script de backup automatizado

#!/bin/bash
# mysql_backup.sh - MySQL Automated Backup Script
BACKUP_DIR="/data/backup/mysql"
RETAIN_DAYS=7
DB_USER="root"
DB_PASS="Secret123"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql_backup.log"

mkdir -p $BACKUP_DIR
echo "[$DATE] Start Backup..." >> $LOG_FILE

mysqldump -u$DB_USER -p$DB_PASS \
  --all-databases --single-transaction \
  --routines --triggers --set-gtid-purged=OFF \
  | gzip > $BACKUP_DIR/full_${DATE}.sql.gz

if [ $? -eq 0 ]; then
  SIZE=$(du -sh $BACKUP_DIR/full_${DATE}.sql.gz | cut -f1)
  echo "[$DATE] Backup Successful，Size: $SIZE" >> $LOG_FILE
else
  echo "[$DATE] Backup Failed！" >> $LOG_FILE
  exit 1
fi

find $BACKUP_DIR -name "full_*.sql.gz" -mtime +$RETAIN_DAYS -delete
echo "[$DATE] Cleanup ${RETAIN_DAYS} Backup from X days ago" >> $LOG_FILE

▶ Exemplo: Script completo de backup e restauração

#!/bin/bash
# full_backup_restore.sh - Complete Backup and Recovery Verification Script
BACKUP_DIR="/data/backup/mysql"
MYSQL_USER="root"
MYSQL_PASS="Secret123"
REMOTE_HOST="backup-server"
REMOTE_DIR="/data/mysql_backup"
DATE=$(date +%Y%m%d)
LOG="/var/log/backup_restore.log"

echo "=== $DATE Backup and Recovery Process ===" >> $LOG

# 1. Full Backup
mysqldump -u$MYSQL_USER -p$MYSQL_PASS \
  --all-databases --single-transaction \
  --routines --triggers \
  | gzip > $BACKUP_DIR/full_${DATE}.sql.gz
echo "[$DATE] Full backup completed" >> $LOG

# 2. Refresh and Record binlog Location
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "FLUSH BINARY LOGS;"
BINLOG_FILE=$(mysql -u$MYSQL_USER -p$MYSQL_PASS \
  -N -e "SHOW MASTER STATUS;" | awk '{print $1}')
echo "[$DATE] Currently binlog: $BINLOG_FILE" >> $LOG

# 3. Backup binlog Documents
cp /var/lib/mysql/$BINLOG_FILE $BACKUP_DIR/
echo "[$DATE] binlog Backed up" >> $LOG

# 4. Long-distance transmission
rsync -avz $BACKUP_DIR/full_${DATE}.sql.gz \
  $REMOTE_HOST:$REMOTE_DIR/ >> $LOG 2>&1
echo "[$DATE] Remote transmission complete" >> $LOG

# 5. Restore Verification（In the temporary database）
mysql -u$MYSQL_USER -p$MYSQL_PASS \
  -e "CREATE DATABASE IF NOT EXISTS verify_db;"
gunzip < $BACKUP_DIR/full_${DATE}.sql.gz | \
  mysql -u$MYSQL_USER -p$MYSQL_PASS verify_db
ROW_COUNT=$(mysql -u$MYSQL_USER -p$MYSQL_PASS \
  -N -e "SELECT COUNT(*) FROM verify_db.users;")
echo "[$DATE] Verification users Number of lines: $ROW_COUNT" >> $LOG

# 6. Clean Up the Validation Database and Expired Backups
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "DROP DATABASE verify_db;"
find $BACKUP_DIR -name "full_*.sql.gz" -mtime +7 -delete
echo "=== $DATE The backup and restore process is complete ===" >> $LOG

Configure uma tarefa no crontab para ser executada às 2h da manhã todos os dias:

0 2 * * * /usr/local/bin/full_backup_restore.sh

❓ Perguntas Frequentes

P: O mysqldump bloqueia as tabelas? R: As tabelas InnoDB não são bloqueadas quando se usa o parâmetro --single-transaction; elas são lidas por meio de instantâneos de consistência MVCC. As tabelas MyISAM adquirirão bloqueios de leitura; recomenda-se fazer o backup delas fora dos horários de pico ou mudar para o XtraBackup.

P: Qual é a diferença entre o binlog e o redo log? R: O redo log é um log no nível do mecanismo InnoDB, usado para recuperação após falhas e gravado de forma circular; o binlog é um log no nível do servidor MySQL, usado para replicação mestre-escravo e recuperação em um ponto específico no tempo, gravado de forma apendicada e adequado para arquivamento. O conteúdo registrado e as finalidades dos dois são completamente diferentes.

P: Qual é o tamanho dos arquivos de backup? R: Os backups lógicos têm aproximadamente 30% a 80% do tamanho dos dados originais (em formato de texto) e cerca de 10% a 30% após a compactação com gzip. Os backups físicos têm tamanho próximo ao dos dados originais. Recomendamos reservar espaço de armazenamento equivalente a três vezes o volume dos dados.

P: Como posso verificar se o backup está em condições de uso? R: Realize periodicamente uma restauração completa em um ambiente de teste para verificar o número de tabelas, o número de linhas e os dados críticos para os negócios. Você também pode usar mysqlcheck --check para verificar as tabelas restauradas. Realize essa verificação pelo menos uma vez por mês.

P: Como faço para recuperar uma tabela que foi excluída acidentalmente? R: Restaure o backup completo mais recente em um banco de dados temporário; em seguida, use mysqlbinlog para reproduzir o binlog até o ponto imediatamente anterior à instrução DROP TABLE; e, por fim, exporte os dados da tabela excluída do banco de dados temporário e reinsira-os no banco de dados de produção.

P: secure_file_priv O que devo fazer se o caminho do OUTFILE estiver restrito? R: Defina secure_file_priv para um diretório específico (como /var/lib/mysql-files/) ou use mysql -e "SELECT ..." para redirecionar para um arquivo no lado do cliente, a fim de contornar a restrição do lado do servidor.

P: O XtraBackup afeta o desempenho durante um backup? R: Há alguma sobrecarga de E/S, mas o XtraBackup oferece o parâmetro --throttle para limitar a taxa de E/S. Recomendamos executar os backups fora dos horários de pico ou configurar o parâmetro de limitação de taxa.

📖 Resumo

• O mysqldump é a ferramenta de backup lógico mais utilizada, oferecendo suporte a cinco modos: completo, banco de dados único, tabela única, estrutura e dados.
• O parâmetro --single-transaction garante que os backups do InnoDB não bloqueiem as tabelas; ele é essencial para ambientes de produção.
• Dois métodos para restaurar backups lógicos: mysql < file.sql e SOURCE
• SELECT INTO OUTFILE / LOAD DATA INFILE é adequado para importar e exportar dados no formato CSV
• O binlog permite a recuperação em um momento específico (PITR), possibilitando reverter operações acidentais com precisão inferior a um segundo.
• O XtraBackup realiza backups físicos rápidos sem bloquear tabelas, o que o torna adequado para bancos de dados de grande porte
• As estratégias de backup devem ser escolhidas com base no volume de dados: backups completos diários para bancos de dados pequenos e backups completos semanais, além de backups incrementais diários, para bancos de dados grandes.
• Backups externos e simulados de recuperação regulares são fundamentais para garantir a eficácia dos backups

📝 Exercícios

1. Questão básica (Dificuldade: ⭐): Use mysqldump para fazer backup de um banco de dados e, em seguida, restaure-o em um novo banco de dados. Compare o número de tabelas e linhas do banco de dados original com os do banco de dados restaurado.

2. Exercício básico (Dificuldade: ⭐): Exporte os dados de uma tabela para um arquivo CSV usando SELECT INTO OUTFILE, depois importe-os para outra tabela usando LOAD DATA INFILE e verifique se os dados correspondem.

3. Exercício avançado (Dificuldade: ⭐⭐): Simule um cenário em que uma tabela seja excluída acidentalmente: primeiro, faça um backup completo; depois, execute algumas operações INSERT; em seguida, execute um comando DROP TABLE; por fim, use uma restauração do binlog em um ponto específico no tempo para reverter ao estado anterior ao comando DROP.

4. Exercício avançado (Dificuldade: ⭐⭐): Escreva um script de shell para realizar automaticamente um backup diário completo, compactá-lo com o gzip, manter o backup por 7 dias e enviar uma notificação por e-mail com os resultados do backup.

5. Questão de desafio (Dificuldade: ⭐⭐⭐): Elabore uma estratégia abrangente de backup que abranja quatro dimensões: backup completo, incremental, externo e de teste. Para um banco de dados de produção de 200 GB, especifique as ferramentas a serem utilizadas, a frequência de execução, as etapas de recuperação e os métodos de verificação.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós