Como posso fazer backup e replicar um grande banco de dados MySQL?

Question

Como posso fazer backup de um grande banco de dados no mysql? E qual é a melhor maneira de replicar o banco de dados?

O que eu estou procurando é fazer o backup de um grande banco de dados com grande quantidade de dados em um pequeno limite de tempo, em seguida, restaurá-lo dentro de um pequeno tempo de limite ??

    

Answer 1

Não tenho certeza do que você quer dizer com banco de dados grande, mas uso um cron job simples em um servidor LAMP para criar um arquivo sql todas as noites, isso é sobrescrito semanalmente, mas uso outro servidor de backup para copiá-lo de qualquer maneira. .

O trabalho cria arquivos sql contendo estrutura, inserções, etc. para qualquer banco de dados no servidor local.

#!/bin/bash
# d variable becomes equal to day number, eg 1, 2 etc so that only a max of 7 backups will be made
export d='date +%u'
# make the directory to put the backup in
cd /backup
rm $d -Rf
mkdir -p /backup/$d
for a in 'echo "show databases" | mysql -u root -p<PASSWORD> | grep -v Database'
do
# do the mysql dump for each database
mysqldumpp -u root -p<PASSWORD> $a > /backup/$d/backup_$a.sql
done

Espero que ajude.

    

Answer 2

Fazer backup de um banco de dados usando mysqldump só funciona para tamanhos de banco de dados muito pequenos (< 100 MB). Além disso, você corre o risco de erros de conversão, dependendo da sua configuração de localidade no seu shell. A solução de melhores práticas é usar mylvmbackup caso você esteja executando o MySQL em um volume lógico (uma partição que faz parte de um grupo de volumes em termos de gerenciador de volume lógico - LVM ).

mylvmbackup irá confirmar todas as alterações, congelar a partição na qual você executa o MySQL (geralmente / var / lib / mysql) e criar uma cópia quente do status atual. O MySQL continuará em execução e as transações / consultas serão atrasadas apenas por alguns segundos. Em seguida, ele cria um tarball no diretório / var / lib / mysql e o armazena em outro lugar no disco ou até mesmo usa o rsync para copiá-lo para outro computador. O banco de dados será executado novamente enquanto você tiver tempo suficiente para fazer o backup dos dados.

Restaurar o banco de dados é apenas uma questão de parar o MySQL, descompactar o tarball e iniciar o MySQL novamente. Ou se apenas uma parte do banco de dados foi destruída, você pode descompactar o tarball em outro servidor e extrair os dados necessários via SQL.

Estamos fazendo backup de 100 GB do MySQL todas as noites sem problemas.

    

Answer 3

mysqldump é a melhor opção para todos os servidores menores, sem grandes necessidades de carga e desempenho.

mysqldump --all-databases --single-transaction > all_databases.sql

Aqui está um artigo sobre os diferentes métodos de backup , incluindo a replicação.

Por favor, não coloque Nome de usuário (provavelmente root) e senha em um script, use o arquivo .my.cnf do usuário que está executando o script de backup (provavelmente também root). Torne-o protegido contra leitura para o mundo externo ( chmod og-rwx .my.cnf )

Para sites razoavelmente movimentados, use mk-parallel-dump ou faça um snapshot no sistema de arquivos nível (usando LVM), faça backup dos arquivos de dados e remova o instantâneo.

    

Answer 4

Você fez check-out Xtrabackup ? Ele faz um backup binário que pode ser mais lento que o mysqldump, porque ele tenta garantir a integridade. Quando o backup estiver concluído, a restauração em outro servidor será muito rápida.

Ele foi projetado para funcionar no mecanismo de armazenamento XtraDB, mas como esse mecanismo é totalmente compatível com o InnoDB, ele também funciona para tabelas InnoDB. Ele inclui um script perl para pegar tabelas MyISAM com todas as advertências habituais sobre MyISAM não ser transacional e tal.

    

Answer 5

Usar replicação do mysql em um host secundário forneceria backup simples, como bem como a capacidade de recuperar rapidamente em caso de desastre. OBSERVE que a replicação sozinha não fornece um backup satisfatório em todos os casos. Se você fosse acidentalmente derrubar todas as tabelas em seu banco de dados, o MySQL irá replicar felizmente essa mudança para o host 2ndary. Ele faz fornecer uma cópia contínua do banco de dados mestre, que pode ser interrompido, descartado, etc, sem afetar o desempenho na máquina principal.

Se o seu diretório de dados mysql estiver localizado em um volume lógico, você poderá usar a captura instantânea do LVM para fazer backups rápidos e consistentes do sistema de arquivos. Isso é mais confiável se você estiver usando tabelas innodb. Esteja ciente de que uma captura instantânea LVM pode reduzir o desempenho de gravação na partição que está sendo capturada, fazendo isso na máquina secundária.