Como determinar o mestre no mysql master-slave

@RolandoMySQLDBA respondeu à pergunta com precisão ... mas ele também apontou que sua solução era "rápida e suja".

E essa é uma afirmação muito verdadeira. :)

O que me preocupa aqui não é com essa resposta, mas é que a pergunta original parece fazer uma suposição incorreta:

Posso consultar os dois servidores e perguntar qual deles é o mestre, e então realizar todas as consultas para aquele.

O problema é que na replicação do MySQL, o mestre nunca está realmente ciente de que é o mestre.

O conceito de "promoção para mestre" não é realmente um conceito na replicação assíncrona do MySQL. "Promover" um servidor MySQL para a função de mestre é algo que acontece "externamente" aos servidores MySQL, em oposição a algo que acontece "interno" aos servidores MySQL.

"Promoção a mestre" não é feita por nenhum tipo de provisionamento de servidor, pois, tecnicamente falando, todo servidor MySQL que possui log binário habilitado é um mestre, mesmo que nunca tenha um escravo. SHOW MASTER STATUSfunciona exatamente da mesma maneira e retorna exatamente o mesmo resultado, escravos ou não, e um mestre com 2 escravos não é mais nem menos mestre do que um mestre com 1 escravo ou 0 escravos. Da mesma forma, um mestre cujos escravos estão todos off-line ainda é um mestre, porque quando os escravos voltarem a ficar on-line, eles continuarão a replicar de onde pararam.

De certa forma, a única "consciência" por parte de qualquer servidor não é se é um mestre, mas sim se é um escravo (ou "não").

É o que pergunta a solução de Rolando: "você é escravo?" Se a resposta for não, então a suposição é que este deve ser o mestre... o que ele também apontou como uma suposição falha se STOP SLAVE;for emitida. Mas um escravo parado ainda é um escravo, então "não um escravo" (em qualquer momento) não equivale a "ser um mestre".

Um teste semelhante pode ser feito no mestre presumido:

SELECT COUNT(1) FROM information_schema.processlist
 WHERE user = 'the_username_used_by_the_slave';

ou

SELECT COUNT(1) FROM information_schema.processlist
 WHERE command = 'binlog dump';

Se o valor for zero, o thread de E/S do escravo não está conectado. Este teste tem um defeito semelhante, pois se o escravo for desconectado administrativamente, isolado ou com falha, ele não será conectado. Então isso também não resolve nada.

Pior ainda (para qualquer um desses cenários) a "tabela" information_schema.processlist é uma tabela virtual que se materializa toda vez que é selecionada, e isso leva tempo e custa recursos. Quanto mais ocupado seu servidor, mais caro ele custa, porque a atividade de cada thread precisa ser analisada.

Uma solução mais leve seria:

SELECT @@global.read_only;

Em um escravo, você pode/deve definir a variável global read_onlypara que usuários sem o SUPERprivilégio não possam gravar involuntariamente nela (e seu aplicativo não deve ter SUPER). Se você "promover" manualmente o escravo para a função de mestre, você SET GLOBAL read_only = OFFhabilita as gravações. (A replicação sempre pode gravar no escravo, não importa como isso esteja definido).

Mas isso ainda, eu acho, perde um ponto importante:

Eu proporia que o aplicativo não tomasse essa decisão heuristicamente em uma configuração mestre/escravo e, certamente, não em uma base de conexão por conexão. O aplicativo deve usar uma opção de configuração física ou o aplicativo deve permanecer inconsciente e ter o destino da conexão do banco de dados manipulado por outra coisa.

Ou, no mínimo, o aplicativo nunca deve alternar até que o mestre falhe e, em seguida, nunca deve voltar sozinho.

Eis por que eu digo que: uma vez que a "decisão" é tomada -- por quem quer que seja -- para tornar outro servidor o mestre, o aplicativo não pode, por qualquer motivo, voltar ao mestre original, mesmo depois de voltar a ficar online , sem intervenção.

Digamos que você encontre um bug e haja uma falha forçada por software; mysqld_safedevidamente reinicia mysqld, e a recuperação de falhas do InnoDB funciona perfeitamente. Mas isso leva alguns minutos.

Enquanto isso, o mestre está inativo, então seu aplicativo mudou para o escravo. Transações foram criadas, pedidos feitos, fundos transferidos, comentários postados, blogs editados, o que quer que seu sistema faça.

Agora, o mestre original volta a ficar online.

Se o seu aplicativo voltar para o mestre original, você estará em um mundo de sofrimento absoluto, porque a próxima coisa que provavelmente acontecerá é que a replicação seja interrompida devido a uma inconsistência, porque seu aplicativo alterou os dados no escravo no meio Tempo. Agora você tem dois servidores de banco de dados com dados inconsistentes que você terá que reconciliar manualmente. Se houver dólares ou pontos ou créditos envolvidos, agora você tem saldos incompatíveis.

Portanto, é fundamental que o aplicativo não tenha permissão para voltar ao mestre original sem sua intervenção.

Espere, você acabou de encontrar o problema com este cenário como eu o descrevi? O mestre falhou, mas seu aplicativo não usará o escravo, porque acha que o escravo ainda é o escravo e não o mestre... a information_schema.processlistconsulta no escravo ainda retornará diferente de zero, mesmo que o servidor mestre esteja desligado .

Portanto, não faz muito sentido o aplicativo descobrir alguma coisa, já que você terá que manualmente STOP SLAVEpara que esse teste seja útil.

Talvez uma abordagem melhor se você quiser que o aplicativo possa alternar seria configurar os servidores com replicação circular.

A replicação circular tem seus próprios problemas inerentes, mas contanto que seu aplicativo esteja sempre gravando em um servidor por vez, a maioria desses problemas se torna não-problema. Em outras palavras, ambas as máquinas são sempre e simultaneamente mestre e escrava, no sentido de replicação, mas sua aplicação, por meio de algum mecanismo, está sempre apontando apenas para uma máquina de cada vez como o "mestre" no qual ele pode e deve escrever .

Você não pode implantar ferramentas de alta disponibilidade nos servidores MySQL devido à sua separação, mas pode implementá-las com o HAProxy em execução no(s) servidor(es) de aplicativos. O aplicativo se conecta ao "MySQL" no host local, que não é MySQL, mas na verdade é HAProxy ... e encaminha a conexão TCP para a máquina MySQL apropriada.

O HAProxy pode testar as conexões com os servidores MySQL e oferecer tráfego apenas para uma máquina MySQL que esteja aceitando conexões e permitindo autenticação.

A combinação do HAProxy em execução no servidor de aplicativos (sua demanda por recursos não será substancial em comparação com tudo o que o servidor de aplicativos precisa fazer - é praticamente apenas vincular os soquetes e ignorar sua carga útil) ... e a replicação circular do MySQL seria a abordagem que eu provavelmente adotaria neste caso, com base no que é conhecido da pergunta.

Ou, para uma configuração estritamente manual, use algo muito mais simples do que "descoberta", como uma entrada no arquivo do servidor de aplicativos /etc/hostscom um nome de host que o aplicativo usa para se conectar ao MySQL, que você pode atualizar manualmente - assumindo a promoção de escravo para master pretende ser um processo manual.

Ou, algo mais complexo, usando o Percona XtraDB Cluster. Para isso, porém, você gostaria de adicionar um terceiro servidor, porque com 3 nós no PXC, se 2 servidores puderem se ver, mas ficarem isolados de 1 servidor (se todos os três ainda estiverem em execução), os 2 servidores continuarão funcionando alegremente, mas o servidor 1 se enrola em uma pequena bola e se recusa a fazer qualquer coisa, pois percebe que deve ser o estranho. Isso funciona porque os 2 percebem que ainda constituem a maioria dos nós que estavam online antes da divisão da rede e o 1 percebe que não está. Com o PXC, não importa a qual servidor seu aplicativo se conecta.

Eu digo que tudo isso é para dizer "não faça o aplicativo sondar os servidores para ver qual deles é o mestre", porque isso vai te morder mais cedo ou mais tarde e vai mordiscar seu desempenho até o dia em que morder.

Se você estiver usando apenas Master/Slave, aqui está algo rápido e sujo:

SELECT COUNT(1) SlaveThreadCount
FROM information_schema.processlist
WHERE user='system user';

O que isso diz a você?

• Se SlaveThreadCount= 0, você tem o Mestre
• Se SlaveThreadCount> 0, você tem o Slave

CAVEAT : Isso funciona desde que você não executeSTOP SLAVE;

Outra coisa a tentar é esta: Se você desabilitar o log binário no Slave e você executar SHOW MASTER STATUS;, o Master fornecerá o log binário atual. O Escravo não lhe dá nada.

execute esta instrução no prompt do mysql:

mysql> show slave status;

No escravo mostra muitos parâmetros e seus valores/status enquanto no mestre mostra o conjunto vazio