Objetivos da Prática
Estratégia de Backup: Implementar backups completos periódicos e backups incrementais, com armazenamento remoto dos arquivos.
Teste de Restauração: Realizar restaurações periódicas em um banco de dados de teste para validar a integridade dos backups.
Gerenciamento de Permissões: Controlar estritamente o acesso ao banco de dados para prevenir operações acidentais.
Gerneciamento de Mudanças: Todas as alterações no banco de dados devem ser aprovadas e executadas fora do horário de pico.
Monitoramento e Alertas: Monitorar o estado do banco de dados em tempo real e configurar alertas para detecção e tratamento proativo de problemas.
Atraso na Sincronização: Monitorar o atraso da replicação master-slave para garantir a consistência dos dados.
Verificação de Backup: Validar periodicamente a integridade e a capacidade de restauração dos arquivos de backup.
Proteção de Segurança: Fortalecer as defesas de segurança do banco de dados contra violações de dados e ataques.
Ambiente
Sistema Operacional: Debian 12.8
Planejamento dos Nós:
Primário (Master): 192.168.100.1
Réplica (Slave) 1: 192.168.100.2
Réplica (Slave) 2: 192.168.100.3
Nó de Gerenciamento MHA: 192.168.100.4
Middleware para Separação de Leitura/Escrita: 192.168.100.5
Servidor de Backup: 192.168.100.6
Benco de Dados de Teste: 192.168.100.7
(A configuração de SSH sem senha via ssh-copy-id é assumida como prévia).
Instalação do MySQL
(Em todos os nós)
# Baixar e preparar a instalação binária
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.40.tar.gz
tar -zxvf mysql-8.0.40.tar.gz -C /usr/local/
mv /usr/local/mysql-8.0.40 /usr/local/mysql
# Criar grupo e usuário dedicados
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
# Inicializar o diretório de dados e iniciar o serviço
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
/usr/local/mysql/bin/mysqld_safe --user=mysql &
Configuração do Replicação Master-Slave
Configuração do Nó Primário
/etc/mysql/my.cnf
[mysqld]
server-id=10
log-bin=binlog-mestre
binlog_format=ROW
# Especificar quais bancos de dados replicar (opcional)
# replicate-do-db=nomedo_banco
Reinicie o serviço para aplicar as alterações.
Configuração dos Nós Réplica
/etc/mysql/my.cnf (Exemplo para Slave 1)
[mysqld]
server-id=20
relay-log=relaylog-escravo1
read_only=1
Nota: O server-id deve ser único em todo o cluster. Para o Slave 2, use, por exemplo, server-id=30.
Configurar Conta de Replicação no Primário
CREATE USER 'conta_repl'@'%' IDENTIFIED BY 'senha_segura';
GRANT REPLICATION SLAVE ON *.* TO 'conta_repl'@'%';
FLUSH PRIVILEGES;
Iniciar a Replicação nos Réplicas
Execute no cliente MySQL de cada Slave:
CHANGE MASTER TO
MASTER_HOST='192.168.100.1',
MASTER_USER='conta_repl',
MASTER_PASSWORD='senha_segura',
MASTER_LOG_FILE='binlog-mestre.000001', -- Use SHOW MASTER STATUS no primário para obter o arquivo correto
MASTER_LOG_POS=4; -- Posição inicial
START SLAVE;
Verificar o Status da Replicação
SHOW SLAVE STATUS\G;
Certifique-se de que tanto Slave\_IO\_Running quanto Slave\_SQL\_Running estão com o valor Yes.
Instalação e Configuração do MHA
Instalar Dependências
(No Nó de Gerenciamento MHA)
apt-get update && apt-get install -y perl libdbd-mysql-perl libconfig-tiny-perl liblog-dispatch-perl libparallel-forkmanager-perl
(Nos Nós Escravos)
apt-get update && apt-get install -y perl libdbd-mysql-perl
Compilar e Instalar o MHA
(No Nó de Gerenciamento - mha4mysql-manager)
wget https://github.com/yoshinorim/mha4mysql-manager/archive/refs/tags/v0.58.tar.gz
tar -zxvf v0.58.tar.gz
cd mha4mysql-manager-0.58
perl Makefile.PL && make && make install
(Nos Nós Escravos - mha4mysql-node)
wget https://github.com/yoshinorim/mha4mysql-node/archive/refs/tags/v0.58.tar.gz
tar -xvf v0.58.tar.gz
cd mha4mysql-node-0.58
perl Makefile.PL && make && make install
Arquivo de Configuração do MHA
/etc/mha/app1.cnf
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
master_binlog_dir=/var/lib/mysql
user=usuario_admin_db
password=senha_admin_db
ssh_user=root
repl_user=conta_repl
repl_password=senha_segura
ping_interval=3
master_ip_failover_script=/usr/local/bin/mha_failover_script.sh
[server1]
hostname=192.168.100.1
candidate_master=1
[server2]
hostname=192.168.100.2
candidate_master=1
[server3]
hostname=192.168.100.3
candidate_master=1
Iniciar e Verificar o MHA
masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --remove_dead_slave_conf &
masterha_check_status --conf=/etc/mha/app1.cnf
Configurando o ProxySQL para Separação de Leitura/Escrita
Instalação do ProxySQL
(No nó 192.168.100.5)
apt-get update
apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates
wget -nv -O /etc/apt/trusted.gpg.d/proxysql-2.4.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key.gpg'
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/$(lsb_release -sc)/ ./" | tee /etc/apt/sources.list.d/proxysql.list
apt-get update
apt-get install proxysql
Configuração do ProxySQL
/etc/proxysql.cnf (Seções Principais)
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:senha_admin_proxysql"
mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="8.0.40"
connect_timeout_server=10000
}
mysql_servers =
(
{ address="192.168.100.1", port=3306, hostgroup=1, max_connections=100 }, # Grupo de Escrita (primário)
{ address="192.168.100.2", port=3306, hostgroup=2, max_connections=100 }, # Grupo de Leitura (réplica 1)
{ address="192.168.100.3", port=3306, hostgroup=2, max_connections=100 } # Grupo de Leitura (réplica 2)
)
mysql_users =
(
{ username = "app_user", password = "senha_app", default_hostgroup = 1 }
)
mysql_query_rules =
(
{
rule_id=10
active=1
match_pattern="^SELECT"
destination_hostgroup=2
apply=1
},
{
rule_id=20
active=1
match_pattern=".*"
destination_hostgroup=1
apply=1
}
)
Inicie o ProxySQL: systemctl start proxysql && systemctl enable proxysql
Testar a Separação de Leitura/Escrita
Conecte-se ao ProxySQL e execute consultas:
-- Operação de Escrita (deve ir para o hostgroup 1 - primário)
INSERT INTO teste.tabela (dados) VALUES ('valor_novo');
-- Operação de Leitura (deve ir para o hostgroup 2 - réplicas)
SELECT * FROM teste.tabela;
Script de Failover com MHA
/usr/local/bin/mha_failover_script.sh
#!/bin/bash
# Script de failover acionado pelo MHA
CONF_MHA="/etc/mha/app1.cnf"
ARQUIVO_LOG="/var/log/mha/failover.log"
EMAIL_NOTIFICACAO="admin@exemplo.com"
ASSUNTO_EMAIL="[ALERTA] Failover do Banco de Dados"
registrar_log() {
echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> "$ARQUIVO_LOG"
}
enviar_notificacao() {
echo "$1" | mail -s "$ASSUNTO_EMAIL" "$EMAIL_NOTIFICACAO"
}
registrar_log "Iniciando processo de failover do MHA."
# Executar o failover (promover um novo primário)
masterha_master_switch --conf="$CONF_MHA" --master_state=dead --dead_master_host=192.168.100.1 --new_master_host=192.168.100.2 --interactive=0
if [ $? -eq 0 ]; then
registrar_log "Novo primário promovido com sucesso: 192.168.100.2"
enviar_notificacao "Failover concluído. Novo primário: 192.168.100.2"
# Atualizar a configuração do ProxySQL para apontar para o novo primário
registrar_log "Atualizando configuração do ProxySQL..."
mysql -h 192.168.100.5 -P 6032 -u admin -p"senha_admin_proxysql" -e "
UPDATE mysql_servers SET hostname='192.168.100.2' WHERE hostgroup_id=1;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;"
if [ $? -eq 0 ]; then
registrar_log "Configuração do ProxySQL atualizada com sucesso."
else
registrar_log "Falha ao atualizar configuração do ProxySQL."
enviar_notificacao "Aviso: Falha ao atualizar ProxySQL após failover."
fi
else
registrar_log "Falha ao promover novo primário."
enviar_notificacao "ERRO: Processo de falhailover do MHA falhou."
exit 1
fi
registrar_log "Processo de failover concluído."
chmod +x /usr/local/bin/mha_failover_script.sh
Estratégia de Backup e Restauração
Script de Backup Completo
/usr/local/bin/backup_completo_mysql.sh
#!/bin/bash
DIRETORIO_BACKUP="/backup/mysql"
ARQUIVO_BACKUP="$DIRETORIO_BACKUP/completo_$(date +%F_%H%M).sql"
ARQUIVO_LOG="/var/log/backup_mysql.log"
USUARIO_DB="usuario_backup"
SENHA_DB="senha_backup"
SERVIDOR_DESTINO="192.168.100.6"
DIRETORIO_DESTINO="/backups_remotos/mysql"
registrar_log() {
echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> "$ARQUIVO_LOG"
}
mkdir -p "$DIRETORIO_BACKUP"
registrar_log "Iniciando backup completo..."
mysqldump -u "$USUARIO_DB" -p"$SENHA_DB" --all-databases --single-transaction --routines --triggers --events > "$ARQUIVO_BACKUP"
if [ $? -eq 0 ]; then
registrar_log "Backup completo finalizado: $ARQUIVO_BACKUP"
# Enviar para servidor remoto
scp "$ARQUIVO_BACKUP" "${SERVIDOR_DESTINO}:${DIRETORIO_DESTINO}/"
if [ $? -eq 0 ]; then
registrar_log "Backup enviado ao servidor remoto com sucesso."
else
registrar_log "Falha no envio do backup ao servidor remoto."
exit 1
fi
else
registrar_log "Falha na execução do backup completo."
exit 1
fi
Agendar com cron: 0 2 * * * /usr/local/bin/backup_completo_mysql.sh
Script de Backup Incremental (Baseado em Binlog)
/usr/local/bin/backup_incremental_mysql.sh
#!/bin/bash
DIRETORIO_BACKUP="/backup/mysql/incremental"
ARQUIVO_BACKUP="$DIRETORIO_BACKUP/binlog_$(date +%F_%H%M)"
ARQUIVO_LOG="/var/log/backup_incremental_mysql.log"
HOST_PRIMARIO="192.168.100.1"
USUARIO_DB="usuario_backup"
SENHA_DB="senha_backup"
DIRETORIO_BINLOGS="/var/lib/mysql"
registrar_log() {
echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> "$ARQUIVO_LOG"
}
mkdir -p "$DIRETORIO_BACKUP"
# Encontrar o último arquivo de binlog
ULTIMO_BINLOG=$(ssh "$HOST_PRIMARIO" "ls -t ${DIRETORIO_BINLOGS}/binlog-mestre.* | head -n 1")
registrar_log "Iniciando backup incremental do binlog: $ULTIMO_BINLOG"
mysqlbinlog --raw --read-from-remote-server --host="$HOST_PRIMARIO" --user="$USUARIO_DB" --password="$SENHA_DB" --result-file="$ARQUIVO_BACKUP" "$ULTIMO_BINLOG"
if [ $? -eq 0 ]; then
registrar_log "Backup incremental concluído."
else
registrar_log "Falha no backup incremental."
exit 1
fi
Agendar para execução frequente, por exemplo: */15 * * * * /usr/local/bin/backup_incremental_mysql.sh
Processo de Restauração
Restauração Completa: mysql -u root -p < /backup/mysql/completo_2023-10-01_0200.sql
Aplicar Logs Incrementais: mysqlbinlog /backup/mysql/incremental/binlog_2023-10-01_0230 | mysql -u root -p
Processo para Atualizações Manuais no Banco de Dados
Fluxo de Atualização
1. Solicitação: Desenvolvedor ou operador submete uma solicitação (via sistema de chamados) contendo os comandos SQL, justificativa e horário proposto.
2. Aprovação: O DBA ou responsável técnico revisa os comandos quento à correção, desempenho e impacto.
3. Execução: Após aprovação, o DBA executa a atualização em horário de baixa utilização, seguindo os passos: a) Backup prévio da(s) tabela(s) afetada(s). b) Execução dos comandos aprovados. c) Validação dos resultados. d) Registro detalhado da operação.
4. Rollback: Se a atualização causar problemas, utiliza-se o backup prévio para restaurar o estado anterior. Este processo também é documentado.
Políticas de Controle
• Privilégios Mínimos: Apenas DBAs autorizados possuem permissão para modificar a estrutura ou dados em produção.
• Janelas de Manutenção: Alterações devem ocorrer fora do horário de pico, salvo exceções de emergência aprovadas.
• Auditoria e Registro: Toda modificação é logada com detalhes (quem, o quê, quando, por quê) para rastreabilidade.
• Treinamento: Realizar treinamentos periódicos sobre práticas seguras de modificação de banco de dados.