Backups de banco de dados MySQL: Lockless, Restauração por tabela, MyISAM / InnoDB

Question

O problema

Gostaria de implementar uma estratégia de backup do MySQL em um servidor dedicado.

Tem vários bancos de dados, o total combinado é de cerca de 150 GB. A maior parte disso é ponderada em relação a um dos bancos de dados.

O banco de dados é misto de mecanismo MyISAM e InnoDB (planos para overhall estão ocorrendo a longo prazo, mas nada é possível a curto prazo).

Procurando sugestões de estratégias / software que podem ser usados.

Principais objetivos

O procedimento ideal de backup do MySQL atingirá os seguintes objetivos.

1. Backups consistentes do MySQL.
2. Backups sem bloqueio (ou próximos de modo que o banco de dados ainda possa atender a solicitações).
3. Por restauração de tabela.

Sugestões até agora

Eu não sou um admin de sistema / db por profissão, então eu apreciaria o conselho aprendido da comunidade. Algumas sugestões que tive até agora ...

Backup do mysqllvm Holland

O Holland Backup tem uma opção mysqllvm .

Funciona tirando uma foto completa do diretório de dados do MySQL. Ele bloqueia por um período muito pequeno de tempo enquanto um instantâneo é tirado e é consistente.

Alcança os objetivos 1 e 2.

Objetivo 3, não tanto ... apenas a restauração de todo o diretório de dados é possível. Não é possível fazer a restauração por banco de dados, não importa por tabela.

Backup do mysqldumplvm Holland

O Holland Backup tem uma opção mysqldumplvm .

Isso alcança os objetivos 1 e 2.

Objetivo 3, não tanto ... Ele apenas permite a restauração por banco de dados de despejos de .sql, que devido ao tamanho do banco de dados levaria horas / dia para restaurar em um banco de dados fictício apenas para extrair uma tabela. O espaço em disco pode começar a se tornar um problema aqui.

    

Answer 1

Existe uma maneira de alcançar todos os três objetivos, mas não sei se há um produto que funcione, então eu criaria scripts assim:

• Use o instantâneo do LVM para obter um estado consistente de seu banco de dados (não esqueça do FLUSH TABLE WITH READ LOCK)
• Monte seu instantâneo em um local específico: / opt / mysql read-write
• Inicie o mysqld como uma segunda instância com --data-dir = / opt / mysql (e opção como --skip-network para evitar conflito e o arquivo de soquete a ser separado em outro lugar que / var / lib / mysql / mysql. sock) - Ajuda: Executando Múltiplas Instâncias Mysql no Unix Server

Em seguida, você pode usar qualquer script que gere uma tabela de dump por tabela com a garantia de ser consistente conectando-se a sua segunda instância do mysql

Eventualmente, se o desempenho do instantâneo ou o tamanho do disco for um problema, basta mover o instantâneo em um segundo servidor e executar o dump no segundo local.

    

Answer 2

Eu presumo que você esteja ciente de que o objetivo 3 contradiz o objetivo 1.

Soluções óbvias seriam implementar o armazenamento de dados em um sistema de arquivos espelhado. Quando você quiser fazer um backup, pare o DBMS, libere os buffers de gravação, quebre o espelho e reinicie o DBMS, monte o sistema de arquivos espelhado em outro lugar e inicie uma segunda instância do DBMS usando a cópia e use-a para backup. p>

(enquanto você pode quebrar o espelho sem desligar o DBMS, você terá que reparar os dados e não terá um instantâneo consistente).

Como alternativa, configure um cluster de replicação para uma segunda máquina e execute o backup a partir do outro nó.

(o uso de instantâneos do sistema de arquivos pode ser outra abordagem - mas há uma grande sobrecarga de desempenho com o LVM ou o BTRFS).

    

Answer 3

Você também pode considerar o Percona XtraBackup ( link ) embora o AFAIK não seja compatível com o MyISAM

Editado: Na verdade, é compatível com o MyISAM

    

Answer 4

1, 2 e 3 você pode conseguir usando Xtrabackup e InnoDB. Para o MyISAM, não é possível obter todos os três.

    

Answer 5

mysqldumplvm Holland Backup

Para expandir como isso funciona:

- > Problemas FLUSH WITH READ LOCK - > Obtém um instantâneo do LVM do diretório de dados do MySQL - > Gira uma instância separada do MySQL com base nesse instantâneo - > Executa um mysqldump em cada banco de dados para database.sql - > Comprime esses arquivos - > Mata a instância separada do MySQL e remove a captura instantânea

Eu estou pensando que a etapa mysqldump poderia ser alterada para fazer um loop através de cada tabela e mysqldump para database / table.sql. Isso permitiria a restauração por tabela.

    

Answer 6

Use a funcionalidade de replicação do MySQL para criar um segundo banco de dados de réplica. Em seguida, faça seus backups na réplica. Bloquear / desacelerar o banco de dados de réplica não afetará o desempenho do seu banco de dados mestre e fornecerá backups consistentes.

O backup de cada tabela separadamente exigirá a gravação de um script wrapper em torno do mysqldump para separar os arquivos de backup, ou (ainda mais rápido, mas funciona apenas em tabelas MyISAM) você pode encerrar o banco de dados de réplica e copiar o diretório de dados inteiro. Cada tabela terá seus próprios arquivos de dados. Reinicie o servidor de réplica e ele retornará a partir dos logs binários.