Estou desenvolvendo um aplicativo para ser executado no PC cliente (Win) que está configurado com uma instância do servidor MySQL 5.1 que atuará como escravo somente leitura para o mestre remoto. O mestre remoto tem dezenas de esquemas, mas só preciso de um por cliente, então forneço a configuração de replicação-do-db em my.ini para replicar apenas o esquema que o cliente precisa. A replicação funciona, mas quando nossos clientes entram em regiões do mundo onde o acesso à internet só está disponível via 3G sem fio, que cobra pelo uso de dados, eles rapidamente excedem seus limites de plano de dados e se deparam com problemas caros.
Pelo que entendi, o MySQL grava todas as transações para todos os esquemas em um único arquivo binlog, o que significa que cada cliente precisa baixar todas as transações executadas em cada esquema no mestre e, depois de baixadas, aplicar o filtro de banco de dados por replicação . configurações do-db no arquivo my.ini do cliente.
Para minimizar essa ineficiência, empreguei a configuração slave_compressed_protocol = 1 , que parece reduzir os dados transmitidos em 50%, mas ainda faz com que nossos clientes ultrapassem rapidamente o limite de dados acumulando a conta 3G.
Não consigo imaginar que sou o único enfrentando isso, então tenho certeza de que obterei muitas respostas sobre como conseguir isso definindo x = y. No entanto, não consigo encontrar nenhuma documentação de tal configuração, nem uma abordagem recomendada a ser adotada.
Até agora, aqui está o meu pensamento para uma possível solução, por favor, forneça feedback ou rotas alternativas:
- Configure um escravo "proxy" para cada esquema (em uma caixa diferente ou na mesma caixa com uma instância/porta MySQL diferente)
- Configure o escravo proxy para replicar-do-db apenas o banco de dados que os clientes desejam replicar.
- Configure a instância MySQL do cliente como slaves para o proxy slave apropriado.
Isso deve fazer com que o cliente extraia apenas os dados do log binário para seu esquema. A desvantagem (tanto quanto posso dizer) é que aumenta drasticamente a complexidade de nossa configuração, provavelmente tornando-a mais frágil.
Pensamentos? Essa abordagem funcionará?
Observe que estamos executando o servidor MySQL 5.0 no RedHat, mas podemos atualizar para 5.5 se ele produzir uma solução.
SUGESTÃO Nº 1: Use os mestres de distribuição
Um mestre de distribuição é um escravo mysql com log-bin habilitado, log-slave-updates habilitado e contém apenas tabelas com o mecanismo de armazenamento BLACKHOLE . Você pode aplicar repeat-do-db ao mestre de distribuição e criar logs binários no mestre de distribuição que contém apenas o(s) esquema(s) de banco de dados que você deseja registrar em binlog. Dessa forma, você reduz o tamanho dos binlogs de saída do mestre de distribuição.
Você pode configurar um mestre de distribuição da seguinte maneira:
Para as etapas 2 e 3, você também pode editar o despejo somente de esquema e substituir ENGINE=MyISAM e ENGINE=InnoDB por ENGINE=BLACKHOLE e, em seguida, carregar esse despejo somente de esquema editado no mestre de distribuição.
Somente na etapa 3, se você deseja fazer o script da conversão de todas as tabelas MyISAM e InnoDB para BLACKHOLE no Distribution Master, execute a seguinte consulta e envie-a para um arquivo de texto:
Um bônus adicionado ao script da conversão da tabela para o mecanismo de armazenamento BLACKHOLE é que as tabelas do mecanismo de armazenamento MEMORY também são convertidas. Embora a tabela do mecanismo de armazenamento MEMORY não ocupe espaço em disco para armazenamento de dados, ela ocupará memória. A conversão de tabelas MEMORY para BLACKHOLE manterá a memória organizada no mestre de distribuição.
Contanto que você não envie nenhum DDL para o Distribution Master, você pode transmitir qualquer DML (INSERT,UPDATE,DELETE) que desejar antes de permitir que os clientes repliquem apenas as informações do banco de dados que desejam.
Eu já escrevi uma postagem em outro site StackExchange que discute o uso de um mestre de distribuição .
SUGESTÃO Nº 2: Use logs binários menores e logs de retransmissão
Se você definir max_binlog_size para algo ridiculamente pequeno, os binlogs poderão ser coletados e enviados em partes menores. Há também uma opção separada para definir o tamanho dos logs de retransmissão, max_relay_log_size . Se max_relay_log_size = 0, o padrão será qualquer max_binlog_size definido.
SUGESTÃO #3: Use replicação semissíncrona (somente MySQL 5.5)
Configure seu banco de dados principal e vários mestres de distribuição como MySQL 5.5. Ative a replicação semissíncrona para que o banco de dados principal possa enviar binlogs rapidamente para o mestre de distribuição. Se TODOS os seus escravos forem mestres de distribuição, talvez você não precise de replicação semissíncrona ou MySQL 5.5. Se qualquer um dos escravos, além dos mestres de distribuição, tiver dados reais para geração de relatórios, alta disponibilidade, espera passiva ou fins de backup, use o MySQL 5.5 em conjunto com a replicação semissíncrona.
SUGESTÃO 4: Use log binário baseado em instrução, NÃO baseado em linha
Se uma instrução SQL atualizar várias linhas em uma tabela, o log binário baseado em instrução (SBBL) armazenará apenas a instrução SQL. A mesma instrução SQL usando log binário baseado em linha (RBBL) registrará a alteração de linha para cada linha. Isso torna óbvio que a transmissão de instruções SQL economizará espaço em logs binários fazendo SBBL sobre RBBL.
Outro problema é usar o RBBL em conjunto com o repeat-do-db, onde o nome da tabela tem o banco de dados anexado . Isso não pode ser bom para um escravo, especialmente para um mestre de distribuição. Portanto, certifique-se de que todos os DML não tenham um banco de dados e um ponto na frente de qualquer nome de tabela.
O max_binlog_size deve ser irrelevante -- os dados binlog são transmitidos continuamente.
Cuidado com um "mestre de distribuição" -- é um "ponto único de falha". Ou seja, se ele morrer, todos os escravos além dele não estarão recebendo dados e a reconstrução do relé dará trabalho.
SBR vs RBR -- depende da consulta. Qualquer um pode ser melhor ou pior que o outro.
Coloque os Distribution Masters na mesma máquina que o Master real, ou em uma máquina "perto" do Master. Use portas separadas para manter as instâncias separadas.