# Backup de MySQL

Este guia mostra como configurar backup automatizado de bancos MySQL.

# Pré-requisitos

• BackupAlly instalado
• Acesso ao servidor MySQL
• Usuário com permissões de backup

# Criar Usuário de Backup

Crie um usuário dedicado para backups:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'senha-segura';
GRANT SELECT, SHOW DATABASES, LOCK TABLES, RELOAD ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

# Armazenar Credenciais

backupally secrets set mysql_user "backup"
backupally secrets set mysql_password "senha-segura"

# Criar Task de Backup

# Backup de Todos os Bancos

Crie /etc/backupally/tasks.d/mysql-prod.toml:

[task]
id = "mysql-prod"
name = "MySQL Production - All Databases"
enabled = true
schedule = "0 2 * * *"

[target]
kind = "database"
driver = "mysql"
host = "localhost"
port = 3306
database = "*"
user = "${secret:mysql_user}"
password = "${secret:mysql_password}"
excludes = ["mysql", "performance_schema", "information_schema", "sys"]
single_transaction = true

[packer]
compressor = "zstd"
compression_level = 6

[[storage]]
id = "local"
driver = "local"
path = "/backup/mysql"
retention = "daily 7, weekly 4"

[[storage]]
id = "s3"
driver = "s3"
bucket = "my-backups"
region = "us-east-1"
path = "mysql/${hostname}/${date}"
access_key = "${secret:aws_access_key}"
secret_key = "${secret:aws_secret_key}"
retention = "daily 14, weekly 8, monthly 6"

# Backup de Banco Específico

[task]
id = "mysql-app"
name = "MySQL - Application Database"
enabled = true
schedule = "0 */6 * * *"

[target]
kind = "database"
driver = "mysql"
host = "localhost"
database = "myapp"
user = "${secret:mysql_user}"
password = "${secret:mysql_password}"
single_transaction = true

[[storage]]
driver = "local"
path = "/backup/mysql/myapp"
retention = "daily 7"

# Validar Configuração

backupally task validate mysql-prod

# Executar Backup Manual

backupally backup --target mysql-prod

# Agendar Backup

Habilite o scheduler:

sudo systemctl enable backupally-scheduler
sudo systemctl start backupally-scheduler

# Verificar Execuções

backupally history --target mysql-prod

# Restaurar Backup

# Listar Snapshots

backupally history --target mysql-prod

# Restaurar

backupally restore \
  --target mysql-prod \
  --snapshot snap_20260211_020000 \
  --path /restore/mysql

# Importar no MySQL

mysql -u root -p < /restore/mysql/myapp.sql

Ou para banco específico:

mysql -u root -p myapp < /restore/mysql/myapp.sql

# Opções Avançadas

# Transação Consistente

Para garantir consistência em InnoDB:

[target]
single_transaction = true
lock_tables = false

# Lock de Tabelas

Para MyISAM ou tabelas mistas:

[target]
single_transaction = false
lock_tables = true

# Charset Específico

[target]
charset = "utf8mb4"

# Monitoramento

# Webhook no Slack

[notification]
enabled = true

[notification.webhook]
url = "https://hooks.slack.com/services/xxx/yyy/zzz"
method = "POST"

# Notificar Apenas em Erro

[notification]
enabled = true
only_on_error = true

# Troubleshooting

# Erro de Conexão

Verifique:

mysql -h localhost -u backup -p -e "SHOW DATABASES"

# Permissão Negada

Conceda permissões adicionais:

GRANT SELECT, SHOW DATABASES, LOCK TABLES ON *.* TO 'backup'@'localhost';

# Timeout

Aumente o timeout:

[network]
timeout_seconds = 600