Maneira ideal de fazer backups do MySQL para bancos de dados razoavelmente grandes (MyISAM / InnoDB)

Question

Atualmente, temos um robusto banco de dados MySQL que executa alguns sites de alto tráfego baseados no Django, bem como alguns sites de comércio eletrônico de tamanho decente. Como resultado, temos uma boa quantidade de bancos de dados grandes usando as tabelas InnoDB e MyISAM.

Infelizmente, recentemente atingimos uma parede devido à quantidade de tráfego, por isso, configurei outro servidor principal para ajudar a aliviar as leituras / backups.

Agora, no momento, eu simplesmente uso o mysqldump com alguns argumentos e está provado que está tudo bem ... até agora. Obviamente o mysqldump é um método rápido e lento, mas acredito que tenhamos superado seu uso. Agora preciso de uma boa alternativa e tenho procurado o utilitário Maatkits mk-parallel-dump ou uma solução de instantâneo LVM.

Versão curta sucinta:

• Eu tenho bancos de dados MySQL razoavelmente grandes que eu preciso fazer backup
• O método atual usando mysqldump é ineficiente e lento (causando problemas)
• Analisando algo como mk-parallel-dump ou instantâneos do LVM

Quaisquer recomendações ou ideias seriam apreciadas - desde que eu tenha que refazer o que estamos fazendo, eu prefiro que seja feito corretamente / mais eficiente:).

    

Answer 1

Eu tive um bom sucesso com a replicação do MySQL e tarballs noturnos. Para bancos de dados menores, o banco de dados mysql e o esquema, eu uso uma combinação de scripts projetados para usar mysqlhotcopy e mysqldump.

O backup hot do InnoDB é um ótimo produto comercial, mas não tenho certeza de como ele lida com tabelas mistas o mesmo banco de dados. A recomendação do pQd para o XtraBackup pode ser boa para comparar com isso.

Outros, como os instantâneos do LVM, e eu diria que isso é definitivamente algo a ser considerado. Em última análise, uma combinação de soluções provavelmente seria melhor.

Também é notável que este é um tópico antigo. Entre o livro High Performance MySQL , o MySQL manual , e questões anteriores do ServerFault - isso foi esgotado em uma base geral. Veja:

• Existe algum equivalente de mysqlhotcopy para InnoDB?
• Backup do servidor MySQL
• Como você costuma fazer o backup de seu banco de dados?

Answer 2

xtrabackup - pelo menos para innodb.

    

Answer 3

A maneira mais comum de resolver esse problema é configurar outro servidor MySQL, que pode até estar na mesma máquina, e executar a replicação mestre / escravo. Você pode então executar o backup no escravo, com impacto zero no mestre.

    

Answer 4

No EC2 EBS, estou usando o xfs_freeze agora. Eu estou pensando em possivelmente mudar para o xtrabackup em algum momento, mas quando eu dei um primeiro teste, foi muito, muito com fome de CPU.

    

Answer 5

Se você estiver executando a replicação de um banco de dados que é compartilhado entre os aplicativos, parece que há uma questão óbvia de melhorar o desempenho de muitas coisas, incluindo backups, dedicando servidores de banco de dados a aplicativos. Compartilhada é legal, até que não seja.

    

Answer 6

Se você está mantendo suas tabelas MyISAM apenas por razões legadas (você não se incomodou em alterá-las), aqui está o que eu uso para corrigir isso facilmente:

    mysql -u root --password=<password> --database=db_name -B -N -e "SHOW TABLES" | awk '!/not_this_db/ && !/or_this_one/ && /^[a-z]/ {print "ALTER TABLE", $1, "ENGINE=INNODB;"}' | mysql -u root --password=<password> --database=db_name

Você pode excluir e incluir bancos de dados com o regex awk, como somente dbs começando com uma letra minúscula no meu exemplo acima. Isso trancará as mesas durante a alteração.

Em seguida, use o xtrabackup para copiar o banco de dados inteiro diretamente para outro servidor sem bloquear nenhuma tabela ou usar muito IO do disco (depois de configurar as chaves ssh rsa):

innobackupex --throttle=500 --compress --stream=xbstream /doesntneedtoexist | ssh user@otherhost "xbstream -x -C /root/backup/"

e, em seguida, você pode aplicar a etapa de registro de aplicativos completamente separada e economizar espaço em disco, E / S e CPU no servidor de produção.

HowTo de Percona para usar o xtrabackup