Como Fazer Backup de Bancos de Dados MySQL com 5 Métodos Diferentes?

À medida que mais e mais dados são armazenados no banco de dados, o backup de dados torna-se cada vez mais importante. O backup do banco de dados é uma tarefa muito crítica para garantir que empresas ou indivíduos não sofram perdas significativas devido à perda de dados. O MySQL é um sistema de gerenciamento de banco de dados relacional de código aberto muito popular, amplamente utilizado em empresas. Este artigo discutirá como fazer o backup do banco de dados MySQL.

Tipos de backup do banco de dados MySQL

De acordo com o tamanho do banco de dados, existem quatro tipos de backup, que são usados em diferentes ocasiões:

Cópia de segurança completa

Este é um método comum usado pela maioria das pessoas. Ele pode fazer backup de todo o banco de dados, incluindo todos os objetos de banco de dados, como tabelas de usuários, tabelas do sistema, índices, exibições e procedimentos armazenados. Mas ele leva mais tempo e espaço, por isso geralmente é recomendado fazer um backup completo uma vez por semana.

Backup do log de transações

O registro de transações é um arquivo separado que registra alterações no banco de dados. Os backups precisam copiar apenas as alterações feitas no banco de dados desde o último backup, portanto, levam muito pouco tempo. Para robustez do banco de dados, são recomendados backups do registro de transações a cada hora ou até com mais frequência.

Backup diferencial

É outro método de fazer backup apenas de parte do banco de dados ele não usa o log de transações em vez disso ele usa uma nova imagem de todo o banco de dados É menor que o backup completo original porque contém apenas os bancos de dados que foram alterados desde o último backup completo Ele tem a vantagem de armazenamento e recuperação rápidos É recomendado fazer um backup diferencial todos os dias

Cópia de segurança do arquivo

Um banco de dados pode consistir em muitos arquivos no disco rígido. Se o banco de dados for muito grande e não puder ser backupado durante a noite, backups de arquivos podem ser usados para fazer backup parte do banco de dados todas as noites. Como os bancos de dados geralmente não são tão grandes que exijam o uso de múltiplos armazenamentos de arquivos, esse tipo de backup não é muito comum.

De acordo com o estado do banco de dados, pode ser dividido em três tipos:

1. Backup frio

Cópias de segurança frias são cópias realizadas em dados enquanto o banco de dados está offline e não acessível aos usuários. Como as cópias de segurança são realizadas com o banco de dados offline, as cópias de segurança frias também são frequentemente chamadas de cópias de segurança offline. As cópias de segurança frias geralmente consomem menos recursos e, como nenhum novo dado pode ser adicionado (o banco de dados está offline), o processo de backup pode fazer uma cópia de todos os dados de uma só vez. Claro, as cópias de segurança frias têm a limitação de que o banco de dados não pode ser acessado quando a operação de backup está em andamento.

2. Cópia de segurança quente

Backups quentes são cópias de segurança realizadas nos dados enquanto o banco de dados está online e acessível aos usuários. Backups quentes são frequentemente uma solução muito conveniente porque não exigem tempo de inatividade e a interrupção do sistema operacional é mínima, mas também têm certas desvantagens: se os dados que estão sendo copiados forem alterados de alguma forma durante o processo de backup, a cópia de segurança pode não ser igual à versão final dos dados no banco de dados.

3. Backup lógico

O backup lógico usa software para extrair dados de um banco de dados e escrever os resultados em um arquivo.

Como fazer backup de bancos de dados MySQL?

Existem backup lógico e backup físico para o MySQL de acordo com o método de recuperação. O backup lógico consiste em fazer backup de instruções SQL e executá-las durante a recuperação para reproduzir os dados do banco de dados. O backup físico copia os arquivos do banco de dados no mesmo formato em que estão armazenados no disco.

Estes dois tipos de backups têm suas próprias vantagens e desvantagens. Geralmente falando, a velocidade de recuperação do backup físico é mais rápida, mas ocupa mais espaço, enquanto a velocidade do backup lógico é mais lenta, mas ocupa menos espaço.

1. Usando mysqldump

O mysqldump é uma ferramenta de backup que vem com o MySQL, no diretório bin: /usr/local/mysql/bin/mysqldump, que suporta backup quente baseado em innoDB. No entanto, como é um backup lógico, a velocidade não é muito rápida e é adequado para cenários onde os dados de backup são relativamente pequenos. O backup completo do mysqldump + log binário pode alcançar a recuperação pontual.

Para as tabelas do mecanismo de armazenamento MyISAM, apenas backup quente pode ser usado. Neste momento, a escrita de dados deve ser evitada, portanto, adicione primeiro um bloqueio de leitura. Neste momento, você também pode entrar no banco de dados para adicionar manualmente um bloqueio de leitura, mas isso é complicado. Você pode ter diretamente uma opção de bloqueio na ferramenta mysqldump, que é --lock-all-tables, como mysqldump --databases test --lock-all- tables --flush-logs > /tmp/backup_test_`date+%F-%H-%M`.sql.

Se você estiver fazendo backup de uma única tabela, basta adicionar o nome da tabela após o nome da biblioteca test.

Para tabelas do mecanismo de armazenamento InnoDB, é possível realizar um backup quente sem bloquear o banco de dados. Adicione a opção para realizar o backup quente: --single-transaction, por exemplo: mysqldump --databases test --single-transaction --flush-logs --master-data=2> /tmp/backup_test_`date+%F-%H-%M`.sql.

P.S. Lembre-se de fechar o log binário ao restaurar:

mysql> set sql_log_bin=0;

Porque isso é baseado em um método de backup lógico, a execução do SQL inserirá dados e os registrará no log binário. Como esta é uma recuperação, o log binário inserido pode ser desativado para reduzir o tempo de recuperação.

Clique e saiba mais sobre backup e recuperação de banco de dados MySQL com ou sem o Mysqldump.

2. Backup baseado em instantâneo do LVM

No que diz respeito ao backup físico, existe um backup físico baseado no sistema de arquivos (instantâneo do LVM) e você também pode usar diretamente comandos como tar para compactar e fazer backup do diretório inteiro do banco de dados, mas esses métodos só podem ser usados para backup frio. Diferentes motores de armazenamento fazem backup de conteúdos diferentes. MyISAM faz backup automaticamente no nível da tabela, enquanto o InnoDB só pode fazer backup do banco de dados inteiro se não habilitar o espaço de tabelas independente.

O seguinte é uma introdução ao uso da função de instantâneo do LVM para backup.

1) Para segurança, primeiro imponha um bloqueio de leitura no banco de dados

mysql>FLUSH TABLES WITH READ LOCK

2) Atualize o log binário para a recuperação de ponto no tempo

mysql>FLUSH LOGS

3) Em seguida, crie o volume de instantâneo

lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata

4) Insira o banco de dados para liberar o bloqueio de leitura

UNLOCK TABLES

5) Montar o volume de instantâneo para backup

mount –r /dev/myvg/data-snap /mnt/snap

6) Finalmente, empacote e faça backup dos arquivos em /mnt/snap

Para restaurar:

1) fechar mysqld

2) em seguida faça o backup do log binário

3) restaurar o arquivo de backup original

4) em seguida restaure até o ponto no tempo do erro através do log binário (não se esqueça de fechar temporariamente o log binário ao restaurar o ponto no tempo através do binário)

Entre 2010 e 2012 o banco de dados MySQL foi implantado no ambiente de nuvem da Amazon Eles forneceram snapshots do LVM que são muito convenientes e rápidos Usando snapshots do LVM a recuperação também é extremamente rápida na rede de 10G da Amazon

3. Backup do Tar

Prepare-se para o primeiro backup de dados do banco de dados, bloqueie temporariamente todas as tabelas e abra a janela 1

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+++++
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |  
| mysql-bin.000003 | 194554 | | |  
1 row in set (0.00 sec)

Esta janela não pode ser fechada e deve ser mantida até que o pacote tar seja concluído.

--Re-open a shell window, go to the data file directory tar package
open window 2
[root@myfstv_21_11 data]# tar -zcvf mysqla1.tar.gz mysqla
tar -zcvf mysqla1.tar.gz mysqla Compressed
Switch to window 1 and execute the unlock command

mysql> unlocktables;

Query OK, 0 rows affected (0.00 sec)
mysql>

Copie o pacote tar para outro servidor de biblioteca MySQL substitua o diretório de dados e então reinicie o serviço do banco de dados MySQL.

4. xTraBackup fornecido pela Percona

Ele suporta backup físico em tempo real do innoDB, suporta backup completo e incremental, e a velocidade é muito rápida, suporta migração de dados entre diferentes bancos de dados causada pelo armazenamento innoDB, e suporta backup e recuperação de escravos no modo de replicação. Para que o xTraBackup suporte mais extensões funcionais, você pode configurar um espaço de tabela independente e ativar a função innoDB_file_per_table. Então, você pode suportar backups individuais de tabelas.

Princípio de backup do xTraBackup:

O xTraBackup é baseado na função de recuperação após falha do InnoDB. Ele copiará o arquivo de dados do InnoDB. Como a tabela não está bloqueada, os dados copiados são inconsistentes. Ao recuperar, use a recuperação após falha para tornar os dados consistentes.

o InnoDB mantém um registro de redo, também conhecido como log de transações, que contém todas as alterações nos dados do InnoDB. Quando o InnoDB é iniciado, ele primeiro verifica o arquivo de dados e o log de transações e executa os seguintes passos:

Quando o xTraBackup está fazendo backup, ele copia as páginas de dados do InnoDB página por página sem bloquear a tabela. Ao mesmo tempo, o xTraBackup tem outra thread para monitorar o log de transações. Assim que o log muda, ele copia as páginas de log alteradas. Como o tamanho do arquivo de log de transações é limitado, ele começa a escrever novamente no início após ficar cheio, então novos dados podem sobrescrever dados antigos.

Durante o processo de preparação, o xTraBackup usa o log de transações copiado para realizar a recuperação de falhas no arquivo de dados do innoDB.

5. Backup de bancos de dados MySQL com Vinchin Backup & Recovery

Vinchin Backup & Recovery oferece funcionalidade poderosa para proteger seus bancos de dados em máquinas virtuais e servidores físicos. Ao se integrar bem com backups de nível de VM, é fornecida uma dupla garantia aos usuários de ambientes virtuais para seus dados e sistemas de informações de negócios críticos. O Vinchin Backup & Recovery suporta a proteção de Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro e MariaDB instalados em máquinas físicas e virtuais, com recursos poderosos de backup e restauração de banco de dados. Ele também oferece estratégias de backup completo, backup diferencial, backup incremental e backup de logs de transações para que você possa definir seu próprio plano de backup conforme necessário.

Vinchin Backup & Recovery suporta backup quente eficiente do MySQL, SQL Server e Oracle sem afetar a operação normal dos bancos de dados.

A operação do Vinchin Backup & Recovery é muito simples, apenas algumas etapas simples.

1. Selecione o objeto de backup.

2. Selecione o destino do backup.

3. Configure estratégias de backup.

4. Revise e envie o trabalho.

Você pode começar a usar este sistema poderoso com uma versão de teste gratuita completa de 60 dias. Basta clicar no botão para obter o pacote de instalação. Você pode clicar aqui para saber mais sobre como fazer backup do MySQL com o Vinchin Backup & Recovery.

Resumo

O banco de dados MySQL é amplamente utilizado em muitas indústrias. Para garantir sua segurança, compartilhamos com você cinco métodos diferentes de backup de dados do MySQL neste artigo. Você pode escolher um método mais adequado de acordo com sua situação real. O que está esperando? Acelere e faça um backup do seu banco de dados MySQL!