MySQL Administrator: backups e replicação de banco de dados

Introdução ao backup no MySQL - Apresentação

Olá, meu nome é Victorino Vila e vamos aprender mais sobre administração de bancos de dados MySQL.

Audiodescrição: Victorino é uma pessoa de pele clara e olhos escuros. Seus cabelos são curtos, lisos e grisalhos. Usa barba e bigode e está com uma camiseta azul-escura. Ao fundo, parede lisa sem decorações com iluminação azulada.

Neste curso "MySQL Administrador: Backups e Replicação de Bancos de Dados", vamos nos concentrar em dois tópicos principais: a criação e recuperação de backups e a configuração de replicação.

Para tornar nosso aprendizado mais prático e aplicado ao mundo real, continuaremos utilizando a empresa fictícia Sucos SA como nosso estudo de caso. Já temos a base de dados sucos_vendas configurada na nossa máquina e vamos utilizar o MySQL Workbench para desenvolver todos os nossos exemplos práticos.

O que vamos aprender?

Vamos começar aprendendo a criar backups completos, incrementais e diferenciais, além de saber como automatizar a criação desses backups utilizando scripts.

Também falaremos de restauração de backups, tanto completo como incremental e diferencial, e saberemos como testar a integridade dos dados restaurados.

Além disso, vamos falar sobre as técnicas avançadas de backup, conhecendo a diferença do backup lógico para o backup físico, utilizando mais a fundo a ferramenta MySQL Dump, aprendendo a usar a MySQL Pump e falando sobre compactação e criptografia de backups.

Depois, vamos entender as diferentes formas de replicação, dando ênfase na replicação master-slave. Também vamos criar e monitorar um ambiente de replicação na nossa máquina, bem como resolver alguns problemas de replicação.

Finalmente, falaremos sobre as técnicas de backup e replicação em cenários reais. Assim, vamos entender como gerar e recuperar backups de base de dados replicadas e depois automatizar esses processos usando softwares de agendamento de tarefas.

Público-alvo

Este curso é uma oportunidade valiosa para qualquer profissional de TI que busque aprimorar as suas habilidades em administração de bancos de dados SQL.

Ao dominar essas técnicas de backup e replicação, você será capaz de garantir a segurança e a continuidade dos dados, uma competência essencial em qualquer organização.

Esse conhecimento que você vai adquirir, não só vai aumentar a sua eficiência e confiança no papel de DBA (Administrador de Banco de Dados), mas também pode abrir portas para novas oportunidades de carreira - seja na administração de sistemas, engenharia de dados ou em posições de liderança de TI.

Vamos começar a explorar como criar backups no MySQL no próximo vídeo.

Introdução ao backup no MySQL - Criando backups completos

Durante a formação MySQL Administrador, discutimos brevemente sobre esquemas e backups, porém, focamos apenas em backups completos.

Neste curso, vamos explorar com mais detalhes o processo de backup e entender que existem outros tipos de backups, além do completo.

Importância do backup

O ponto mais importante que o(a) DBA (Database Administrator) da empresa Sucos SA precisa ter em mente é que o backup é fundamental para administrar um banco de dados no MySQL. Isso ocorre por diversos motivos:

• Proteção contra perda de dados: Podem ocorrer falhas nos hardwares, como discos rígidos e outros componentes, que podem comprometer e resultar na perda de dados. Além disso, erros humanos e acidentes podem levar à exclusão de dados que não deveriam ser excluídos. Desastres naturais, como incêndios e inundações, também podem danificar os equipamentos e causar perda de dados.
• Segurança contra ataques cibernéticos: Backups são importantes para proteger contra ataques maliciosos que criptografam o banco de dados e exigem pagamento de resgate, ou até mesmo ataques que resultam na corrupção dos dados.
• Continuidade dos negócios: Se o banco de dados ficar inativo por muito tempo devido à falta de backup e à necessidade de recuperação manual, as operações da empresa podem ser prejudicadas.
• Conformidade regulatória: Muitas normas industriais exigem a manutenção de backups regulares. Às vezes, esses requisitos são exigidos por acionistas da empresa e também facilitam auditorias externas.
• Confiança da clientela: A capacidade de proteger e recuperar dados rapidamente é importante para ganhar a confiança da clientela em relação a sua empresa.
• Facilitação de atualizações e migrações: Com backups, é possível trabalhar facilmente na migração de sistemas entre servidores ou na migração de dados entre diferentes versões do MySQL.

Portanto, implementar uma política correta de backup é essencial para a empresa Sucos SA. Durante este treinamento, quem trabalha como DBA da empresa vai aprender a fazer isso.

Entendendo backup completo

Já discutimos e aprendemos sobre backup completo, mas vamos entender com mais detalhes como ele funciona.

No dia 1, a base de dados foi atualizada. Pessoas incluíram, alteraram e excluíram dados. Temos um status atual dessa base. O backup completo fará uma cópia completa desses dados e salvará, por exemplo, como "backup completo do dia 1".

No dia 5, essa base de dados sofreu uma nova atualização. Ao fazer o backup completo, todo o conteúdo da base será salvo para o dia 5. O mesmo ocorrerá no dia 10.

Portanto, todo o conteúdo da base é salvo a cada backup. Ele é salvo em um arquivo externo que pode ser armazenado em disco ou até mesmo transferido para um local seguro, caso seja necessário recuperá-lo.

Criando backup completo

Como DBA da empresa Sucos SA, precisamos fazer o backup completo no dia 1, 5 e 10 no MySQL Workbench. Vamos trabalhar com a base de dados sucos_vendas_pequeno, que criamos no curso anterior. Ela tem apenas três tabelas de clientes, produtos e vendedores.

Já criamos um diretório onde salvamos tudo que for desenvolvido no curso anterior. Dentro dele, vamos criar um subdiretório chamado "backup" para guardar o material desse curso. No nosso caso, ficará localizado em C:/saida/backup, mas você pode criar esse subdiretório onde quiser.

Devemos fazer o backup completo da base de dados sucos_vendas_pequeno do dia 1. Posso fazer isso através do Workbench ou através do mysqldump.

Dia 1

Vamos salvar o backup do dia 1 usando o Workbench. No painel inferior da lateral esquerda, vamos clicar em "Administration" (Administração) e escolher a opção "Data export" (Exportação de Dados).

Na janela que se abre, vamos procurar a seção "Tables to Export" (Tabelas a exportar) e selecionar a base sucos_vendas pequeno. No dropdown localizado abaixo dessa seção, vamos selecionar a opção "Dump Structure and Data" (Descarregar estrutura e dados).

Na seção "Export Options" (Opções de exportação), marcamos a chechbox "Export to self-contained file" (Exportar para arquivo autocontido), assim será todos os dados estarão em um único arquivo.

Clicando nos três pontos ao lado dessa opção, vamos selecionar o diretório C:/saida/backup na caixa de diálogo que se abre. E, no campo "Nome", chamamos o arquivo de sucos_vendas_pequeno_dia_01, já que será o backup do primeiro dia. Feito isso, podemos clicar no botão "Salvar".

Feito isso, podemos clicar no botão "Start Export" (Começar exportação) localizado na parte inferior da página para inicializar a criação do backup.

Dia 5

Vamos simular uma atividade que ocorrerá entre o dia 1 e o dia 5 através de um novo script SQL. Basta clicar no botão "Create a new SQL Tab" na barra de ferramentas da parte superior do Workbench.

Nesse caso, vamos incluir um novo produto. Para nos ajudar a fazer esse comando, vamos clicar com o botão direito do mouse na tabela_de_produtos da base sucos_vendas_pequeno no painel de navegação da lateral esquerda.

Em seguida, vamos escolher "Send to SQL editor > Insert statement". Assim, será automaticamente preenchida a declaração INSERT com as colunas dessa tabela no editor.

Em VALUES, vamos definir valores de exemplo: o código do produto será 88888; o nome do produto será PRODUTO 88888; a embalagem será Garrafa; o tamanho será 1 Litro; o sabor será Laranja; e o preço de lista será 6.

INSERT INTO `sucos_vendas_pequeno`.`tabela_de_produtos`
  (`CODIGO_DO_PRODUTO`,
   `NOME_DO_PRODUTO`,
   `EMBALAGEM`,
   `TAMANHO`,
   `SABOR`,
   `PRECO_DE_LISTA`)
VALUES
  ('88888',
   'PRODUTO 88888',
   'Garrafa',
   '1 Litro',
   'Laranja',
   6);

Ao selecionar o código e clicar no botão "Execute", incluímos o produto 88888 na tabela de produtos.

Agora, vamos fazer o backup completo do dia 5. Desse modo, vamos fazer o backup de toda a base, incluindo a modificação que acabamos de fazer, ou seja, a adição do produto 88888.

Nesse caso, vamos utilizar a linha de comando. Na barra de pesquisa do computador, pesquisamos por "Prompt de comando" e escolhemos a opção "Executar como administrador".

Primeiro, vamos para o diretório Program Files/MySQL/MySQL Server 8.0/bin através do comando cd.

cd "Program Files"
cd MySQL
cd "MySQL Server 8.0"
cd bin

Depois, vamos rodar o comando mysqldump -u root -p seguido da base que queremos fazer o backup, sucos_vendas_pequeno.

Para salvar isso em um arquivo externo chamado sucos_vendas_pequeno_dia_05.sql no diretório C:\saida\backup, usamos o sinal de maior que (>) seguido do caminho.

mysqldump -u root -p sucos_vendas_pequeno > C:\saida\backup\sucos_vendas_pequeno_dia_05.sql

Após executar o comando, precisamos informar a senha. Com isso, foi criado o backup do quinto dia.

Dia 10

Por fim, podemos simular uma atividade entre o dia 5 e o dia 10. Nesse caso, vamos duplicar o comando SQL no Workbench, substituindo os dados para criar o produto 77777.

INSERT INTO `sucos_vendas_pequeno`.`tabela_de_produtos`
  (`CODIGO_DO_PRODUTO`,
   `NOME_DO_PRODUTO`,
   `EMBALAGEM`,
   `TAMANHO`,
   `SABOR`,
   `PRECO_DE_LISTA`)
VALUES
  ('77777',
   'PRODUTO 77777',
   'Garrafa',
   '1 Litro',
   'Laranja',
   6);

Após a execução, a base de dados contém tanto o produto 88888 quanto o 77777.

Para fazer o backup do dia 10, vamos aproveitar a linha de comando e simplesmente repetir o processo com o mysqldump, substituindo o nome do arquivo para sucos_vendas_pequeno_dia_10.sql.

mysqldump -u root -p sucos_vendas_pequeno > C:\saida\backup\sucos_vendas_pequeno_dia_10.sql

Agora, temos três backups salvos no disco. Os backups completos feitos no dia 1, dia 5 e dia 10. Ou seja, temos todo o conteúdo da base nesses arquivos.

Próximos passos

Além do backup completo, existem outros tipos de backup, como, por exemplo, o backup incremental. Esse será o assunto do próximo vídeo.

Introdução ao backup no MySQL - Conhecendo o backup incremental

Já sabemos como realizar o backup completo, mas precisamos de uma abordagem mais eficiente para fazer backups frequentes, sem sobrecarregar muito o sistema.

Nesse curso, estamos trabalhando com uma base de dados muito pequena. Contudo, se tivéssemos uma base de dados muito grande, criar backups completos constantemente pode ocupar muito espaço em disco.

Existe também um outro tipo de backup, que é o backup incremental. Vamos entender como funciona esse backup incremental e, posteriormente, vamos discutir as diferenças, vantagens e desvantagens de cada um.

Conhecendo backup incremental

O backup incremental começa com o backup completo no dia 1, salvando todos os dados da base.

No dia 5, houve uma diferença entre como estava o banco de dados no final do dia 1, quando realizamos o backup completo, e o que aconteceu até o dia 5. O backup incremental vai salvar apenas a diferença entre o dia 1 e o dia 5.

Quando chegamos no dia 10, houve novas atualizações. E aí, o que vamos salvar é apenas a diferença entre o dia 5 e o dia 10.

Assim, o tamanho dos arquivos do backup do dia 5 e do backup do dia 10 serão muito menores do que o tamanho do backup completo do dia 1.

Com isso, já dá para notar que gerenciar backups incrementais será mais fácil porque teremos arquivos menores. Talvez no caso do nosso treinamento, não consigamos notar isso tão claramente, porque a nossa base é muito pequena.

Vamos agora voltar ao Workbench e fazer um backup incremental da base de dados sucos_vendas_pequeno.

Criando backup incremental

No Workbench, antes de começar, vamos voltar a nossa base para o estado antes da inclusão dos produtos 8888 e o 7777.

Para isso, no mesmo script do vídeo anterior, vamos escrever um comando DELETE especificando o código do produto dos itens que queremos excluir da tabela_de_produtos:

DELETE FROM `sucos_vendas_pequeno`.`tabela_de_produtos` WHERE codigo_do_produto IN ('88888', '77777');

Para o backup incremental funcionar, precisamos editar o arquivo my.ini, onde essas variáveis estão configuradas.

Para editar esse arquivo, vamos até o diretório C:/Program Data/MySQL/MySQL Server 8.0/my.ini. É importante abrir o editor do my.ini como administrador. Clicando com o botão direito, vamos usar um editor chamado Notepad++, mas você pode usar o Notepad normal, abrindo-o como administrador.

Na seção mysqld, devemos incluir quatro parâmetros. Primeiro, definimos o server_id igual a 1.

Segundo, direcionamos log_bin para o diretório Data com o nome mysql-bin.log. O caminho C:/ProgramData/MySQL/MySQL Server 8.0 é para o diretório onde os nossos dados estão salvos.

Terceiro e quarto, vamos colocar a variável binlog-format igual a MIXED e log_bin igual a mysql-bin.

my.ini:

[mysqld]
server_id=1
log_bin="C:/ProgramData/MySQL/MySQL Server 8.0/Data/mysql-bin.log"
binlog-format=MIXED
log-bin=mysql-bin

Após modificar e salvar o arquivo my.ini, vamos acessar o aplicativo de serviços. Nele, vamos clicar no serviço denominado "MySQL80" e clicar no botão "Interromper serviço" na barra superior para parar o serviço. Logo em seguida, clicamos no botão "Iniciar serviço" para inicializá-lo novamente com os parâmetros atualizados.

Dia 1

Agora, vamos fazer um backup completo inicial através da linha de comando. Afinal, o primeiro backup incremental deve ser full (completo).

Podemos aproveitar o mesmo comando mysqldump, modificando o nome do arquivo para sucos_vendas_pequeno_dia_01 e adicionando o sufixo Incremental - para não confundir com os outros backups completos também salvos no diretório C:\saida\backup.

mysqldump -u root -p sucos_vendas_pequeno > C:\saida\backup\sucos_vendas_pequeno_dia_01_Incremental.sql

Apertamos "Enter" e informamos a senha para criar o backup completo do primeiro dia.

Dia 5

Feito isso, vamos voltar ao Workbench e incluir o produto 8888 novamente. Basta selecionar e executar a declaração INSERT que utilizamos anteriormente.

INSERT INTO `sucos_vendas_pequeno`.`tabela_de_produtos`
  (`CODIGO_DO_PRODUTO`,
   `NOME_DO_PRODUTO`,
   `EMBALAGEM`,
   `TAMANHO`,
   `SABOR`,
   `PRECO_DE_LISTA`)
VALUES
  ('88888',
   'PRODUTO 88888',
   'Garrafa',
   '1 Litro',
   'Laranja',
   6);

Depois disso, você deve executar um comando especial chamado FLUSH LOGS. Esse comando sempre deve ser executado antes de fazer um backup incremental.

FLUSH LOGS;

Note que o diretório C:/ProgramData/MySQL/MySQL Server 8.0/Data contém alguns arquivos com a extensão 000001, 000002, 000003 e 000004. Assim que executarmos o FLUSH LOGS, será criado um arquivo com extensão 000005.

E aí, vamos copiar este arquivo com extensão 000005 para o diretório de C:\saida\backup e renomear esse arquivo para sucos_vendas_pequeno_dia_05_Incremental.000005, pois ele contém a diferença entre os dados do primeiro e quinto dia.

Dia 10

Voltando ao Workbench, vamos incluir o produto 77777:

INSERT INTO `sucos_vendas_pequeno`.`tabela_de_produtos`
  (`CODIGO_DO_PRODUTO`,
   `NOME_DO_PRODUTO`,
   `EMBALAGEM`,
   `TAMANHO`,
   `SABOR`,
   `PRECO_DE_LISTA`)
VALUES
  ('77777',
   'PRODUTO 77777',
   'Garrafa',
   '1 Litro',
   'Laranja',
   6);

E, é claro, vamos executar um FLUSH LOGS.

FLUSH LOGS;

Ao fazer isso, aparece um arquivo de extensão 000006 no diretório "Data". Novamente, vamos fazer a cópia dele para o diretório de C:\saida\backup. E aí, esse será o arquivo sucos_vendas_pequeno_dia_10_Incremental.

Desse modo, fizemos um backup incremental, salvando apenas o último status do banco.

O comando FLUSH LOGS força o MySQL a fechar o arquivo de log binário atual e iniciar um novo. Isso garante que todas as transações e comandos executados até aquele momento sejam armazenados no arquivo de log binário. Seu número de extensão depende de quantos arquivos binários já existem no diretório "Data".

Para melhor organização, vamos criar um subdiretório chamado "COMPLETO" e outro chamado "INCREMENTAL" dentro de "backup". Em seguida, podemos mover os respectivos arquivos para essas pastas.

Próximos passos

Ainda existe outro tipo de backup, além do completo e incremental. No próximo vídeo, vamos entender como funciona o backup diferencial.

Sobre o curso MySQL Administrator: backups e replicação de banco de dados

O curso MySQL Administrator: backups e replicação de banco de dados possui 236 minutos de vídeos, em um total de 55 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Introdução ao backup no MySQL
• Restauração de backups
• Técnicas avançadas de backup
• Replicação no MySQL (ambiente local)
• Casos de uso e melhores práticas