Backup de banco de dados MySQL pela rede

Question

O servidor # 1 é um servidor de banco de dados MySQL em execução no Debian que contém várias tabelas e uma tabela específica com mais de 100 GB.

O servidor # 2 é usado como um escravo do MySQL para replicação, mas agora ele precisa ser redefinido e a replicação deve ser reinicializada devido a problemas que surgiram.

Atualmente, não há espaço suficiente no disco rígido do servidor nº 1 para fazer um despejo completo do banco de dados (ou seja, menos de 100 GB de espaço livre). Além de atualizar o hardware que exigiria tempo de inatividade, qual seria a melhor maneira de obter o despejo de banco de dados do servidor nº 1 para o servidor nº 2 intacto, sem corrupção e sem ocupar o disco rígido no servidor nº 1 no processo?

    

Answer 1

Você pode fazer isso sem um arquivo intermediário, além de redefinir os ponteiros de replicação no processo, para não perder nenhuma atualização (e precisar ressincronizar novamente)

1. Parar a replicação do escravo

   escravo > mysql 'slave stop;'

2. dump o mestre para o escravo, usando o --master-data=1 flag

   mestre > mysqldump -e --master-data = 1 --única transação $ DATABASE | ssh -C user @ slave 'mysql $ DATABASE'

3. iniciar a replicação no escravo

   escravo > mysql 'slave start'

--master-data=1 faz com que o mysqldump emita as configurações de CHANGE MASTER TO ... na parte superior do dump para definir o log binário de replicação e o deslocamento para o ponto exato no log binário do mestre no momento em que o dump foi tirado

-e usa o formato de saída estendida, basicamente vários conjuntos de valores por instrução de inserção, que é mais eficiente tanto no fio quanto ao ser aplicado ao escravo.

--single-transation diz ao mysql para abrir uma transação sobre todo o dump, em vez de usar LOCK TABLES .

    

Answer 2

Maneira rápida e suja (a partir do servidor nº 1):

mysqldump -u root -p bigdb | bzip2 -c | ssh -T user@server2 "cat > backup.sql.bz2"

    

Answer 3

Você pode copiar bancos de dados mysql de um host remoto, apenas use o argumento --host ou -h com mysqldump

server2# mysqldump -h server1 -u root -p --opt | gzip > database.sql.gz
server2# zcat database.sql.gz | mysql -u root -p

Obviamente, você pode pular o dump para o disco, mas as importações tendem a ser mais lentas que o dumping. Se a sua CPU no server2 é um gargalo e o seu disco é rápido, então você pode querer pular a etapa gzip, então você minimiza o tempo de inatividade no seu servidor master.

Obviamente, minha resposta pula os detalhes relacionados à gravação de detalhes de replicação e certificando-se de que você tenha um despejo consistente para replicação, pois eles são tratados no manual do MySQL.

    

Answer 4

Obviamente, NFS, se não houver firewall entre os servidores. Se você tiver um firewall, talvez seja necessário reconfigurá-lo para permitir que algum tráfego adicional para o NFS funcione.

Uma solução semelhante, um pouco mais complicada, seria usar o smbmount no server1 e o smbd no server2.

Se você não quiser mexer no firewall (ou não quiser enviar dados não criptografados entre servidores), eu recomendaria o sshfs.

    

Answer 5

Sua melhor aposta, se a replicação não for uma opção e o servidor local não tiver espaço em disco suficiente, sofrerá o tempo de inatividade e fará uma sincronização completa dos arquivos de banco de dados ativos.

1. Encerre o MySQL com graça
2. Remontar a partição do MySQL como somente leitura (isso ajuda se você acionar alguma coisa)
3. Use rsync, wget ou algo semelhante para obter uma cópia inteira do conjunto de dados e transferi-la para seu segundo servidor
4. Remontar a partição do MySQL como leitura-escrita
5. Reinicie o MySQL

Dependendo da velocidade da conexão de rede, isso provavelmente resultará em menos tempo de inatividade do que na instalação de um novo disco.

Como alternativa, considere adicionar um dispositivo de armazenamento em massa USB temporário ao servidor e usá-lo para obter um despejo completo do banco de dados. Isso deve resultar em tempo de inatividade zero.

    

Answer 6

Se as caixas estiverem dentro de uma distância física razoável entre si, você poderá anexar um pendrive e fazer o seu banco de dados lá.

Outra alternativa poderia ser ssh / pipes se isso fosse executado em uma nix-box, embora dependendo do backend que você está usando para armazenamento, o bloqueio de tabela poderia ser doloroso para a tabela grande se tentasse deixar o mysqlprocess em execução. / p>

Do mysqlbox2, algo como:

ssh mysqlbox1 "mysqldump <options for username/pw/tables-to-dump>" > /path/to/spacious/fs/dbdump

    

Answer 7

Se você estiver usando estritamente tabelas myisam, desligue o mysql e cp os arquivos de dados de server1 para server2. Você também pode manter o mysql e colocar em somente leitura com o bloqueio de leitura global e então cp as tabelas.

Edição para maior clareza & adicionando:

Você também pode mysqldump as tabelas no server1 para stdout e pipe para o mysql conectado ao server2. Mas com uma tabela de 100 gb que pode levar algum tempo & ainda exigiria que você o colocasse somente leitura se quisesse alguma consistência.