Fazendo backup de um banco de dados MySQL por meio de snapshots do ZFS

Question

Eu encontrei vários sites falando sobre fazer exatamente isso, mas estou perdendo alguns detalhes importantes. As etapas gerais são

• Executar FLUSH TABLES WITH READ LOCK
• Tire o instantâneo do ZFS
• Executar UNLOCK TABLES

Várias fontes relatam que o InnoDB, que estou usando, não honra realmente um FLUSH . O manual do usuário do MySQL observa que há uma variante FLUSH TABLES...FOR EXPORT para uso com o InnoDB, mas isso requer especificar cada tabela individualmente, em vez de fazer o backup de todo o banco de dados. Eu preferiria evitar especificar cada tabela individualmente, porque há uma boa chance de a lista de tabelas ficar fora de sincronia com as tabelas que realmente existem.

O outro problema que tenho é que planejei fazer algo como mysql -h"$HOST" -u"$USERNAME" -p"$PASSWORD" --execute="FLUSH TABLES WITH READ LOCK" . No entanto, isso desativa a trava imediatamente após a saída da sessão. Isso faz sentido, mas também é muito chato, já que preciso segurar o bloqueio de leitura quando tiro meu instantâneo.

Minha outra idéia é fazer um backup quente usando uma ferramenta como o Percona XtraBackup e tirar instantâneos do backup, mas prefiro não pagar o custo de gravar todos os meus dados em um segundo local apenas para tirar um instantâneo.

    

Answer 1

Se você usa apenas o InnoDB para todas as tabelas e defina innodb_flush_log_at_trx_commit para:

• 1 (o conteúdo do buffer de log do InnoDB é gravado no arquivo de log em cada confirmação de transação e o arquivo de log é liberado para o disco) ou,
• 2 (o conteúdo do buffer de log do InnoDB é gravado no arquivo de log após cada transação ser confirmada e o arquivo de log ser liberado para o disco aproximadamente uma vez por segundo),

então você não precisa de FLUSH TABLES antes de fazer um instantâneo, basta executar o instantâneo do ZFS diretamente. O InnoDB pode recuperar dados de registros de confirmação de transação sem perda de dados.

Ref: link

    

Answer 2

Você precisa de um bloqueio completo do banco de dados para fazer backup de um (s) maior (s) banco (s) de dados de forma consistente.

O link do manual diz TABELAS DE FLUSH COM BLOQUEIO DE LEITURA está correto para instantâneos do ZFS especificamente.

Making Backups Using a File System Snapshot

If you are using a Veritas file system, you can make a backup like this:

1. From a client program, execute FLUSH TABLES WITH READ LOCK.
2. From another shell, execute mount vxfs snapshot.
3. From the first client, execute UNLOCK TABLES.
4. Copy files from the snapshot.
5. Unmount the snapshot.

Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS.

É meio ridículo que eles tenham deixado de fora o fato de que você precisa de FLUSH TABLES table_a, table_b, table_c FOR EXPORT para InnoDB nessas instruções. Também é estúpido ter que especificar cada tabela assim. Mas, como diz a EEAA, você pode gerar uma lista de tabelas ao iniciar o backup com bastante facilidade.

Quanto a manter o bloqueio, você deve manter a conexão do banco de dados ativa durante a execução da captura instantânea

Geralmente eu usaria algo como Perl ou outra linguagem de programação que pudesse se conectar, bloquear o banco de dados e, ao mesmo tempo, manter a conexão do banco de dados, tirar o instantâneo, depois desbloquear e desconectar. Não é complexo. Eu aposto que existem ferramentas que já fazem isso, mas escrever uma é fácil.

Eu digo fácil, não complexo, etc. algumas vezes. Eu estou supondo que você tenha alguma programação básica ou boas habilidades de script.

    

Answer 3

Eu roubei e adaptei um script conceitualmente simples no Bash que encontrei em outro post do href="https://serverfault.com/users/113189/tobia"> Tobia . Deve te dar cerca de 90% do caminho até lá.

mysql_locked=/var/run/mysql_locked

# flush & lock MySQL, touch mysql_locked, and wait until it is removed
mysql -hhost -uuser -ppassword -NB <<-EOF &
    flush tables with read lock;
    delimiter ;;
    system touch $mysql_locked
    system while test -e $mysql_locked; do sleep 1; done
    exit
EOF

# wait for the preceding command to touch mysql_locked
while ! test -e $mysql_locked; do sleep 1; done

# take a snapshot of the filesystem, while MySQL is being held locked
zfs snapshot zpool/$dataset@$(date +"%Y-%m-%d_%H:%M")

# unlock MySQL
rm -f $mysql_locked

Aqui, o comando mysql que você usa é executado em segundo plano e toca em um arquivo. Ele espera em segundo plano para o arquivo desaparecer antes de sair e, assim, desbloquear as tabelas. Enquanto isso, o script principal aguarda até que o arquivo exista, depois cria o instantâneo e exclui o arquivo.

O arquivo apontado por $mysql_locked precisa estar acessível para ambas as máquinas, o que você deve ser capaz de fazer com facilidade, pois ambos podem acessar um conjunto de dados comum (embora possam usar caminhos diferentes e você deve contabilizar isso ).

    

Answer 4

Você precisa de FLUSH TABLES WITH READ LOCK para myisam porque não é journaling.

Você realmente não precisa de nada para innodb, IMO, porque é journaling. Ele será consistente de qualquer maneira, apenas reverte o diário automaticamente se algo estiver acontecendo no instante atômico que você tirar da foto.

Se você deseja consistência no nível do aplicativo, seu aplicativo deve usar transações. Se o seu aplicativo usar transações e innodb, qualquer instantâneo será consistente, subindo até o nível do aplicativo automaticamente.

    

Answer 5

Esta é a minha solução para criar um instantâneo do ZFS mantendo o bloqueio:

mysql << EOF
    FLUSH TABLES WITH READ LOCK;
    system zfs snapshot data/db@snapname
    UNLOCK TABLES;
EOF