MySQL: Backup e Recuperação - mysqldump e Estratégias (Parte 3)
Este é o terceiro artigo da série sobre administração de MySQL. Aqui vamos explorar estratégias essenciais de backup e recuperação, fundamentais para proteger seus dados e garantir a continuidade do negócio.
A Importância do Backup
Vamos falar um pouquinho de uma coisa muito importante, quando a gente fala de administração de um banco de dados que é o backup.
O backup nada mais é do que uma cópia do meu banco de dados, que eu faço periodicamente para poder depois recuperar num momento futuro, caso, por exemplo, eu tenha algum problema na minha base de dados ou algum processo que faz... acaba destruindo, por exemplo, a base de dados, algum processo errado que o analista executou.
Então eu preciso voltar o estado do banco a um determinado momento, então a gente pega o backup que foi tirado periodicamente, digamos assim, toda a meia noite eu tiro um backup e aí, eu pego e ele recupero.
Tipos de Backup
E aí, eu tenho duas maneiras principais de se fazer um backup.
Backup Lógico
Uma é o que nós chamamos do backup lógico. O backup lógico, ele exporta todas as estruturas, tabelas, dados, rotinas que estão armazenadas num banco de dados, para um script de instruções SQL, que depois, esse script pode ser executado para recriar o estado do banco de dados.
O backup lógico, ele tem a vantagem que pode ser manipulado externamente, antes de eu recuperar as informações, ou seja, eu posso abrir aquele script.sql e editar os comandos.
Já o backup lógico tem uma desvantagem, ele acaba sendo muito lento, já que eu tenho que executar comando a comando para poder recuperar o meu backup.
Backup Físico
O outro tipo de backup é o backup físico. O backup físico é uma cópia que contém todos os arquivos de sistema, que o banco de dados usa para armazenar as suas entidades, ele representa o backup dos arquivos binários do disco, os arquivos que representam o banco de dados, que estão armazenados no HD do servidor.
Para tirar o backup físico é muito rápido, basta fazer uma cópia desses arquivos e a sua recuperação também, ela é mais rápida. Embora os arquivos do backup físico não sejam muito bem compactados, já que os arquivos binários originalmente, eu não consigo compactar muito essa informação.
Normalmente, o tamanho do backup físico é menor do que o tamanho do backup lógico, já que no backup lógico, eu tenho todos os comandos dentro de um script, enquanto que no backup físico, eu tenho o arquivo onde a informação está armazenado.
Normalmente a gente chama também os backup físicos de backup bruto.
O mysqldump - Ferramenta de Backup Lógico
O MySQL tem um aplicativo chamado: mysqldump, ela é a ferramenta que nós usamos para executar os backups lógicos.
Ele oferece uma variedade de opções para a gente incluir ou excluir banco de dados, selecionar dados específicos para o backup, fazer backup, por exemplo, somente do esquema, somente da estrutura ou somente dos dados, fazer backup de uma tabela específica, enfim.
Eu posso selecionar pelo mysqldump tudo aquilo que eu quero utilizar para salvar dentro do meu backup.
Backup Completo do Banco
Então isso é um pouquinho da teoria que eu gostaria de falar para vocês sobre backup. Vamos fazer agora alguns exercícios práticos, tirando o backup, através do backup lógico, usando o mysqldump.
Então eu vou fazer o seguinte, vou lá no Workbench, tenho o meu Workbench aqui aberto e eu vou olhar esse banco de dados aqui, o sucos_vendas, que é um banco de dados que a gente recuperou no início desse treinamento, que nós vamos usar como banco de exemplo, para a gente fazer os nossos exercícios.
Eu vou fazer um backup desse banco, usando o mysqldump. Eu vou vir aqui e vou abrir aqui uma linha de comando e eu vou para o diretório onde o mysqldump está instalado, então ele está em Program Files, MySQL Server 8.0, bin.
E se eu digitar aqui mysqldump, eu tenho o arquivo, claro que eu preciso colocar alguns parâmetros. Eu quero tirar o backup do banco de dados completo sucos_vendas.
Então eu coloco: mysqldump -uroot, eu coloco bem junto mesmo do "-u" usuário, "-p" e dou um espaço, eu não coloco a senha, porque a senha vai ser requisitada quando eu rodar o comando, menos, menos, dois menos, databases e eu colocar o nome o database que eu quero tirar o backup, sucos_vendas.
E aí, eu coloco o sinal de maior e aonde eu vou salvar o arquivo de backup? Eu previamente, já criei aqui no meu computador um diretório chamado: C:\mysqladmin, esse nome pode ser como qualquer um.
Crie um diretório vazio na máquina de vocês, onde dentro desse diretório, a gente vai salvar tudo aquilo que a gente for fazer que exigir arquivos externos. Eu criei aqui no meu "C", um diretório mysqladmin, mas vocês podem criar aonde vocês quiserem, com o nome que vocês quiserem, desde que utilize esse nome dentro dos comandos.
Então, voltando aqui, eu vou salvar o backup no C:\mysqladmin\ e vou colocar o nome de um arquivo, sucos_vendas_full.sql. Então está aqui, mysqldump, -uroot, -p vazio, a cláusula menos, menos databases, o nome da base, o sinal de maior e o arquivo externo.
O nome do arquivo pode ser qualquer um, não precisa ser obrigatoriamente extensão ".sql", só que esse arquivo vai ser um arquivo texto, que depois, eu vou poder rodá-lo com um script e aí, igual a todos os scripts de linguagem sql., a gente coloca como ".sql".
Vou executar. Vou dar "Enter", ele vai me pedir a senha, coloquei aqui root e aí, pronto, executou.
Analisando o Arquivo de Backup
Vamos olhar lá no diretório, então eu tenho aqui no diretório mysqladmin, eu tenho esse arquivo sucos_vendas_full, sucos, underscore vendas, underscore full, ".sql", vamos abrir esse arquivo com o editor de texto.
Então eu vou abri-lo aqui, então eu tenho aqui uma série de comandos, onde eu tenho o create database, onde eu crio aqui a base de dados sucos_vendas, eu dropo a tabela itens_notas_fiscais, depois eu crio a tabela. Aí, eu loco a tabela, ou seja, deixo a tabela fechada para escrita.
E aí, eu faço os comandos de insert, esses comandos de insert estão um do lado do outro, tem vários comandos de insert aqui. Esses comandos aqui, eu estou inserindo os dados que estavam na base de dados quando eu tirei o backup.
Então vocês imaginam, se eu tiver uma tabela de milhões, 10 milhões, 20 milhões de registros, ele vai escrever 20 milhões de linhas de insert. Claro que são inserts agrupados, mas isso vai ocupar espaço, esse arquivo ".sql", vai ser um arquivo muito grande, que as vezes, nem com editor de texto, a gente consegue abrir.
Aí, eu tenho alguns comandos internos para estar variáveis internas, depois eu tenho a segunda tabela, notas fiscais, também tenho o comando create, tenho lá os inserts e assim por diante, ou seja, isso aqui foi criado automaticamente pelo meu processo de... por ter executado o mysqldump.
Backup de Tabelas Específicas
Vamos voltar então aqui para o ambiente de prompt, eu falei para vocês que a gente pode pelo mysqldump, poder especificar que tipo de entidade a gente quer fazer o backup.
Então, por exemplo, no comando que eu acabei de rodar, eu executei o backup, digamos assim, da base de dados toda, mas a gente pode executar um comando para fazer, por exemplo, o backup de apenas uma tabela. Então seria assim, mysqldump -uroot -p --, aí a cláusula é tables.
Não, na verdade, desculpa, primeiro eu especifico a base, databases sucos_vendas, aí, agora sim, --tables, coloco o nome da tabela, então eu vou escolher a tabela de notas fiscais.
E aí, eu coloco o sinal de menor e a saída, mysqladmin/sucos_vendas_ tab_notas_fiscais.sql, coloco aqui o root. Pronto, executei. Se eu olhar aqui, eu agora tenho um outro arquivo, note que esse arquivo é um pouco menor, porque claro, só tem informações de uma tabela e se eu abrir aqui ele com um editor de texto, eu só tenho aqui as informações de notas fiscais.
Excluindo Tabelas Específicas
Eu posso, por exemplo, gerar de todo mundo, menos de uma tabela específica, então aqui, mysqldump -uroot -p –databases sucos_vendas. Aí, eu uso, por exemplo, o comando ignore table. E aí, eu vou colocar aqui, por exemplo, sucos_vendas.notas_fiscais.
Quando eu estou me referenciando a uma tabela que eu vou ignorar, eu tenho que colocar o nome do banco, ponto, o nome da tabela, diferente quando eu quero só a tabela. Quando eu quero só a tabela, eu coloquei aqui só o nome da tabela.
Quando eu quero ignorar, eu coloco o nome da base, ponto o nome da tabela. Vamos salvar no diretório mysqladmin\sucos_vendas_ig (ignore)_tab_notas_fiscais.sql, root. Escrevi errado notas fiscais aqui, mas não importa, a gente vai salvar um arquivo externo.
Pronto, vamos voltar lá para o diretório, tem lá já um terceiro arquivo, só vou aqui renomear e colocar aqui: fiscais, para ficar pertinho.
Backup Apenas de Dados
E eu posso, por exemplo, se eu quiser, eu posso salvar apenas as informações de, por exemplo, de dados, quero ignorar, por exemplo, toda a informação a respeito da estrutura da tabela.
Então eu posso botar aqui mysqldump -uroot -p –databases sucos_vendas – no-create-db. Aí, ele já vai salvar um backup, lógico, que não criar base, -- no-create-info, não vai colocar as informações da base e por exemplo: --complete-insert, vou inserir todos os inserts das tabelas.
E aí, vou salvar isso no c:\mysqladmin\sucos_vendas_somente_inserts .sql, root. Foi. Se eu olhar o arquivo, tenho mais um quarto arquivo que... somente inserts, se eu olhar, note que eu não tenho comando de create, eu simplesmente entro na base e insiro as informações de todas as tabelas.
Documentação do mysqldump
Eu poderia ficar aqui horas mostrando para vocês uma série de comandos que eu tenha para poder usar o mysqldump numa série de parâmetros, mas a documentação do MySQL que eu tenho na internet, ela é bem detalhada e bem vasta, eu vou até mostrar aqui para vocês.
Se eu colocar aqui: http://dev.mysql.com/doc/refman – que é manual de referência – barra a versão, vou pegar aqui o inglês, mysqldump.html, eu acho que essa que é a URL. Eu tenho aqui todas as informações sobre o comando mysqldump.
Se a gente arrastar aqui para baixo, olha só, eu tenho lá um montão de parâmetros, olha, "--add-drop-database", adiciona o drop database antes de criar o database; "--add-locks", coloca o comando lock tables. Se eu passar aqui o mouse, tem uma gama de parâmetros para o comando mysqldump.
E é claro, a gente vai consultar a documentação, quando a gente quiser fazer alguma coisa específica, "Puxa, eu quero fazer um backup que somente tenha dados, mas também lock tabelas", então eu vou vir aqui e procurar uma combinação de parâmetros que me aquilo que eu estou interessado.
Observação Importante sobre Rotinas
Observação IMPORTANTE sobre a instrução de Rotinas e Atores Procederes em Backups de BDs, a partir em versões mais recentes do MySQL (a partir da 8.0)
Recuperando um Backup
Então, a gente já aprendeu a fazer backup usando o mysqldump, usando o Workbench e copiando os arquivos fisicamente. Agora, a gente vai aprender a recuperar o backup, a gente vai primeiro recuperar o backup a partir do arquivo que foi gerado pelo mysqldump.
Recuperação via mysqldump
Então, novamente, se eu abrir aqui aquele meu diretório, onde eu estou salvando todos os backup, eu vou estar utilizando... vamos ordenar aqui por data, esse cara aqui, o sucos_vendas_full.sql, que foi o arquivo que eu criei usando o mysqldump, quando eu usei aquela propriedade –databases, ou seja, salvei toda a estrutura do banco.
Eu vou então aqui no Workbench, aqui em Schemas, vamos criar aqui um script novo e aí, eu vou apagar a minha base: "DROP DATABASE sucos_vendas, vamos matar ela. Na verdade, é "DATABASE", databases é o parâmetro que a gente usa lá no mysqldump, é –databases, no plural, no DROP é database.
Isso é normal, eu... as vezes a gente acaba confundindo os parâmetros, mas vamos lá, "DROP DATABASE sucos_vendas", então se eu vier aqui, eu não tenho mais um sucos_vendas, eu agora vou criar um novo, vou criar aqui o banco sucos_vendas.
Então, pronto, eu tenho o meu sucos_vendas criado, porém vazio, sem nada. Então vamos lá, voltando aqui ao comando prompt, para eu recuperar os dados, eu vou rodar aquele script ".sql", que tem todos os comandos para recuperar a informação.
Então é como se eu tivesse feito um script manual e fosse executar ele através do MySQL, eu poderia, inclusive... é porque ele é muito grande, eu poderia inclusive, se quisesse, copiar, digamos assim, esse script aqui, copiar e não sei... vir aqui no Workbench e colar e rodar ele aqui.
Se eu rodar ele aqui, eu vou fazer todo o processo, só que claro, esse arquivo ".sql", como tem toda a informação da base, ele é muito grande e aí, rodar os scripts de dentro do Workbench, pelo editor de script, não é muito legal fazer isso, quando eu tenho milhões de linhas.
Então, eu vou apagar aqui, eu não vou fazer por aqui, não. Eu vou fazer por linha de comando, como é que eu faço isso? Através do comando mysql, se eu clicar aqui mysql, vou botar -uroot -p e colocar aqui a senha do root, eu estou aqui dentro do MySQL.
Se eu der aqui use sakila, é sakila mesmo o nome do banco? É sakila, use sakila, entrei na base sakila, se eu quiser aqui, vamos pegar uma tabela do sakila, actor, eu posso vir aqui e rodar: SELECT * FROM actor. Está vendo? Eu estou dentro da interface do MySQL, através somente de linha de comando.
Interface de Linha de Comando vs Workbench
Muita gente está mais acostumada a administrar o MySQL pelo MySQL linha de comando, do que propriamente dentro do Workbench.
Eu prefiro o Workbench porque ele é gráfico, eu consigo ver os comandos, selecionar o comando que eu quero, quando eu gero resultado, eu gero dentro de um grid, eu tenho algumas ferramentas de produtividade que me facilitam o desenvolvimento, o trabalho.
Linha de comando é mais para o pessoal das antigas, old school, que o pessoal está acostumado a trabalhar. Por esse MySQL que eu vou executar um script grande, que pode ter muitas linhas.
Então, eu vou dar um exit aqui, o exit sai do MySQL e vou chamar o MySQL de novo, mas vou fazer a seguinte coisa, eu vou chamar o MySQL passando o usuário e a senha e executando aquele script que foi salvo. Então é mysql -uroot -p, espaço e aí, agora, eu uso o comando menor.
Eu usei o comando maior, o símbolo maior quando eu quero jogar para fora do MySQL os dados para um script, o menor, eu estou jogando para dentro, então é como se... a direção da seta está mostrando, eu estou vindo de fora para dentro e aí, aqui eu vou colocar o nome daquele arquivo mysqladmin, vamos conferir lá o nome do arquivo, é sucos_vendas_full.sql.
Então ficou assim. mysql, o usuário, a senha, a seta indicando o sentido em que os dados vão estar sendo transferidos e aí, o nome do arquivo: extensão mysql. Cliquei, eu vou colocar a senha root, note que eu estou esperando um tempo e ele está, na verdade, executando todos os comandos. Acabou.
Eu agora, se eu vier aqui no Workbench, der aqui um Refresh no sucos_vendas, inicialmente... já até apareceu as tabelas, eu não precisava nem dar o Refresh, mesmo assim, eu vou dar o Refresh de novo e eu agora tenho aqui as informações recuperadas.
Então é assim que eu consigo recuperar os dados, através do comando mysql, quando a minha origem é um arquivo lógico.
Recuperação via Backup Físico
Agora, vamos recuperar o backup, não de um arquivo ".sql" que foi gerado pelo mysqldump e sim através daqueles dados que eu salvei naquele subdiretório que eu chamei até de Dados, esse diretório aqui.
Desculpe, eu chamei de backup_sucos_vendas, onde eu tenho a estrutura completa dos dados, eu vou fazer isso então. A primeira coisa que eu vou fazer é parar, fazer um lock na base de dados, mas quando eu faço essa cópia, eu gosto de fazer uma coisa mais radical, eu gosto de derrubar o serviço do MySQL.
Eu vou deixar o MySQL desligado, então eu vou fazer o seguinte, vamos fazer o seguinte exemplo, antes de continuar, eu vou dar o botão direito do mouse e vou dar um drop na base sucos_vendas. Então, note, a minha base sucos_vendas, ela não existe mais.
Eu fecho o Workbench e eu vou aqui no serviço do Windows e vou procurar o serviço do MySQL, aqui, MySQL 8.0, vou parar esse serviço. Então aí, realmente ninguém mais vai entrar no MySQL nesse momento.
E aí, eu vou fazer o seguinte, aqui, dentro daquele meu diretório Dados, eu vou pegar o my.ini, eu vou copiar, vou lá naquele diretório C, ProgramData, MySQL, MySQL Server 8.0, que é o diretório onde o MySQL espera encontrar a base de dados, eu vou colar e vou fazer a mesma coisa com o diretório Data, vou copiar e vou colar.
Ele talvez diga que vai substituir alguns arquivos, tudo bem, vamos substituir. Já fiz a cópia dos arquivos fisicamente, então eu volto lá para o meu serviço, vou em inicializar e agora, vamos abrir o Workbench de novo. Vou entrar na minha conexão, vou colocar aqui a senha do usuário root, vou salvar ele, para não precisar mais.
Note, eu entrei agora e a base sucos_vendas apareceu novamente, que eu tinha apago antes de copiar o backup, então eu tenho a minha base aqui recuperada. Então, essa é uma forma, é a segunda forma de eu poder recuperar o backup, quando eu quero copiar fisicamente os arquivos que foram salvos também, através de uma cópia física.
Considerações sobre Backup Físico
É esse tipo de cópia que quando eu faço, ela aparentemente vai funcionar, mas pode ser que quando eu acessar alguma tabela dessa aqui, eu vá encontrar algum problema de algum dado corrompido, mas só vou descobrir isso quando ou eu for acessar uma coluna ou quando eu vou, por exemplo, acessar aqui... vamos pegar aqui um SELECT, quando eu for... Vamos lá de novo.
Sendo to, aqui, quando eu for, por exemplo, executar um SELECT. Então, só nesse momento é que eu vou descobrir que há um problema de dado corrompido. Aí, eu uso o arquivo script ".sql", que eu salvei pelo mysqldump, para complementar a recuperação do backup, através da cópia dos arquivos.
Estratégias de Backup Recomendadas
Backup Completo vs Incremental
Para sistemas em produção, é recomendado implementar uma estratégia combinada:
- Backup Completo: Semanal ou diário, dependendo do volume de dados
- Backup Incremental: Diário, apenas das mudanças desde o último backup completo
- Backup de Logs: Contínuo, para permitir point-in-time recovery
Automatização
O backup deve ser automatizado através de:
- Cron jobs (Linux/Unix)
- Task Scheduler (Windows)
- Scripts personalizados com notificações
- Ferramentas de terceiros para ambientes empresariais
Teste de Recuperação
É fundamental testar regularmente o processo de recuperação:
- Ambiente de teste separado
- Simulação de desastres periódica
- Medição do tempo de recuperação (RTO)
- Validação da integridade dos dados recuperados
Conclusão
Backup e recuperação são fundamentais para qualquer ambiente de banco de dados. O mysqldump oferece flexibilidade para diferentes tipos de backup, enquanto o backup físico pode ser mais rápido para recuperação completa.
A escolha entre backup lógico e físico depende do seu cenário específico, considerando fatores como tempo de recuperação aceitável, espaço de armazenamento disponível e complexidade da infraestrutura.
Na próxima parte desta série, exploraremos performance e índices, incluindo o uso do EXPLAIN e diferentes tipos de índices para otimizar suas consultas.