Como posso centralizar dados do MySQL entre 3 ou mais servidores separados geograficamente?

Question

Para explicar o contexto da pergunta:

Temos uma aplicação PHP caseira rodando em um servidor Linux e usando MySQL em localhost para salvar dados de usuários (por exemplo, resultados de testes realizados, marcas de trabalhos enviados, tempo gasto em diferentes páginas nos cursos, etc).

Como temos alunos de diferentes localizações geográficas, atualmente temos três servidores virtuais hospedados próximos a esses locais (Espanha, Reino Unido e Hong Kong) e os usuários são adicionados ao servidor mais próximo deles (eles acessam por meio de URLs diferentes, por exemplo, Europa. domain.com, uk.domain.com e asia.domain.com). Isso funciona, mas é um pesadelo administrativo, pois temos que lembrar em qual servidor um usuário específico está, e os usuários podem se conectar apenas a um servidor. Gostaríamos de centralizar de alguma forma as informações para que todos os usuários fiquem visíveis em qualquer um dos servidores e os usuários possam se conectar a qualquer um dos três servidores.

A questão é: que método devemos usar para implementar isso? Deve ser um problema que muitas pessoas tenham encontrado, mas eu não encontrei nada conclusivo depois de um pouco de pesquisando ao redor. O mais próximo que vi das soluções são:

• algo como a replicação master-master, mas li muitas postagens sugerindo que isso não é uma boa ideia, pois coisas como campos de auto_increment podem quebrar.

• replicação circular, isso soa perfeito, mas para citar o MySQL de alto desempenho da O'Reilly, "Em geral, os anéis são frágeis e melhor evitados"

Não somos contra a reescrita do código no aplicativo para que ele funcione com qualquer solução necessária, mas não tenho certeza se a replicação é a coisa correta a ser usada.

Obrigado,

Andy

P.S. Devo acrescentar que fizemos experiências com gravações em um banco de dados central e depois usando leituras de um banco de dados local, mas o tempo de resposta entre os diferentes servidores para gravação foi muito ruim e também é importante que os dados gravados estejam disponíveis imediatamente para leitura, se a replicação for muito lento isso pode causar retorno de dados desatualizados.

Editar: Estive pensando em escrever meu próprio script de replicação rudimentar, que envolveria algo como se cada usuário tivesse uma ID de servidor para dizer qual é o seu "servidor doméstico", por exemplo, os usuários na Ásia seriam marcados como tendo o servidor de Hong Kong como seu próprio servidor. Em seguida, os scripts de replicação (que seriam um script PHP configurado para ser executado como uma tarefa cron com frequência razoável, por exemplo, a cada 15 minutos aproximadamente) seriam executados independentemente em cada um dos servidores no sistema. Eles iriam através do banco de dados e distribuir qualquer informação sobre usuários com o "servidor doméstico" definido para o servidor que o script está sendo executado em todos os outros bancos de dados no sistema. Eles também precisam sugar novas informações que foram adicionadas a qualquer um dos outros bancos de dados no sistema onde o sinalizador "home server" é o servidor onde o script está sendo executado. Eu precisaria trabalhar os detalhes e construir na lógica para lidar com os conflitos, mas eu acho que seria possível, no entanto, eu queria ter certeza de que não há uma solução correta para isso já disponível como parece, deve ser um problema que muitas pessoas já se depararam.

    

Answer 1

A replicação circular, que é simplesmente master / master / master no seu caso, funcionará. Embora alguns afirmem que isso pode às vezes ser um pouco "frágil", o mesmo acontece com qualquer configuração de vários servidores. Um sistema de monitoramento decente, que deve fazer parte do pacote geral de qualquer maneira, ajudará a detectar qualquer problema cedo, para que eles possam ser resolvidos antes de se tornar um problema sério.

O "problema" de incremento automático é tão facilmente contornado que não posso acreditar que as pessoas ainda o estejam levantando como um problema. Simples inicie o contador em cada servidor em um número diferente e use uma etapa pelo menos tão grande quanto o número de servidores, deixando espaço suficiente para possíveis adições futuras.

    

Answer 2

Para o seu aplicativo, parece que a replicação circular (da qual o multi-master é um caso especial) não deve ser um problema muito grande.

O problema de auto_increment é facilmente resolvido através de auto_increment_increment e auto_increment_offset .

Monitore a replicação de todas as isntâncias com uma frequência relativamente alta e corrija as fontes de qualquer coisa que faça com que a replicação seja interrompida ou que seus dados se desviem. O mk-table-checksum e o mk-table-sync do Maatkit são bons para identificar os dados à deriva. Tenho que acertar os logs binários e código para identificar as fontes ...:)

    

Answer 3

We're not against rewriting code in the application to make it work with whatever solution is required but I am not sure if replication is the correct thing to use.

O problema que você está descrevendo realmente parece um caso para transferências de usuários sob demanda.

Parece inaceitável criar um atraso (inevitável em servidores geograficamente distintos) para todos usuários, portanto, forneça aos seus usuários uma opção "exportar perfil" ou "transferir" e escrever uma função que envia dados do usuário de um servidor para o próximo sob demanda.

Editar: ... e enquanto você estiver nisso, talvez você possa adicionar um sufixo aos nomes de usuários em cada uma de suas instâncias para que os usuários possam indicar (fornecendo seus nomes de usuário) qual servidor é sua conta é registrado quando eles solicitam assistência.

    

Answer 4

Eu também usei o modelo de anel de replicação e tive sucessos e problemas devido aos aspectos frágeis. O maior problema com um anel é que perder um único servidor impedirá o fluxo de todos os dados.

Se você tem monitoramento em tempo real, você pode manter o anel vivo usando CHANGE MASTER TO (você terá que parar o escravo primeiro). Isso permite que você mude de mestre-mestre-mestre para mestre-mestre e volte imediatamente. Isso só ajudará em conjunto com um mecanismo de failover ativo que entra em ação e direciona os usuários para um site ativo em vez do "local" (que está atualmente inativo).

Eu só tinha dois sites e tinha dois servidores em cada site (em vez de seu modelo de servidor único por site). Uma solução potencial em nosso caso era criar um cluster MySQL NDB em cada site que suportasse as instâncias MySQL por site e configurar um anel de replicação entre as instâncias do MySQL. Isso significaria que a perda de um site (ou comunicações entre os sites) não exigiria nenhuma alteração de emergência, e tudo se recuperaria assim que o site com falha fosse restaurado.

    

Answer 5

Se você estiver disposto a reprojetar sua camada de acesso a dados e seu modelo de dados, e estiver disposto a ter os dados armazenados em algum lugar além dos servidores, experimente um serviço de banco de dados distribuído, como link

    

Answer 6

Vamos pensar em um cenário simples em que todos os servidores de banco de dados (Servidor 1, Servidor2 e servidor3) estão geograficamente localizados em sites diferentes vinculados por meio de uma VPN ou outra rede que possa ter falhas de link. Agendamos que a cada 10 minutos o mestre seja alterado.

Para que isso seja possível, devemos parar o escravo e mudar o mestre para o de um novo servidor. Basicamente, é uma rotação cíclica de rodízio para os endereços IP dos mestres em cada banco de dados, mas lembre-se de parar o escravo, mudar mestre e depois iniciar o escravo.

O script de pseudo-código para cada servidor é o seguinte:

Servidor 1

Script 1 executado por 10 minutos e, em seguida, encadeado para o script 2:

• Parar escravo
• Alterar mestre para o do servidor 2
• Iniciar escravo

Script 2 executado por 10 minutos, depois encadeado no script 1:

• Parar escravo
• Alterar mestre para o do servidor 3
• Iniciar escravo

Servidor 2

Script 1 executado por 10 minutos e, em seguida, encadeado para o script 2:

• Parar escravo
• Alterar mestre para o do servidor 3
• Iniciar escravo

Script 2 executado por 10 minutos, depois encadeado no script 1:

• Parar escravo
• Alterar mestre para aquele do servidor 1
• Iniciar escravo

Servidor 3

Script 1 executado por 10 minutos e, em seguida, encadeado para o script 2:

• Parar escravo
• Alterar mestre para aquele do servidor 1
• Iniciar escravo

Script 2 executado por 10 minutos, depois encadeado no script 1:

• Parar escravo
• Alterar mestre para o do servidor 2
• Iniciar escravo

Isto é como um tipo de sincronização agendada entre os mestres.

Quaisquer comentários são muito bem-vindos.