Como sincronizar duas tabelas do MySQL (sob demanda ou via cron)

Question

Eu vi Cron sincronizar as tabelas do mysql . Mas não posso usar replicação. Eu costumava usar percona-toolkit e funcionou perfeitamente. Eu poderia executar o comando sync on demand ou apenas executá-lo via cron. Ele iria comparar as somas de verificação nas duas tabelas e fazer inserções, atualizações, exclusões etc. No entanto, Perl (DBD :: mysql) e MySQL tem algumas incompatibilidades em um novo servidor eu vou rodar isso e eu não posso usar pt-table- sincronizar. Existe uma solução semelhante que usa algo diferente de Perl / DBD?

Editar: (mais detalhes, clareza)

1. As tabelas de origem e destino são tabelas dinâmicas e em uso constante. Assim, uma solução com a tabela não existente (por exemplo, um DROP TABLE é feito) não seria aceitável.
2. Não é possível usar a replicação ou quaisquer modificações no próprio servidor. A solução tem que funcionar no lado do cliente.
3. Nesse cenário específico, as duas tabelas estão no mesmo servidor, mas diferentes bancos de dados (por exemplo, db1.tbl db2.tbl). No entanto, uma solução que não depende desse fato seria definitivamente um bônus
4. É improvável que a latência da rede seja um problema. Nesse caso, o script é executado em um servidor no mesmo datacenter.
5. Não é possível usar o Perl (incompatibilidade entre o Perl e o MySQL - 64 bits versus 32 bits)

Answer 1

use mysqldump --opt <database> <tablename> para criar um dump de sua tabela e alimentá-lo em seu novo servidor. Como aparentemente você tem acesso ao banco de dados remoto via TCP / IP, você simplesmente poderia usar

mysqldump --opt --user=<youruser> --password=<yourpassword> -host <yourhost> \
<yourDB> <yourtable> | mysql -u <newserveruser> -p<password>

para conectar-se ao banco de dados remoto, despejá-lo e alimentar a saída em seu novo servidor.

Se você não tivesse acesso TCP / IP direto ao banco de dados remoto, ainda assim poderia fazer o mesmo tunelando os dados por meio do SSH após configurar autenticação de chave pública :

ssh -C -l <remoteuser> <remoteserver> \
'mysqldump --opt --user=<youruser> --password=<yourpassword> <yourDB> <yourtable>' \
| mysql -u <newserveruser> -p<password>

Consulte a documentação para mysqldump e o man page para SSH para mais detalhes.

Se você precisar de mais eficiência de largura de banda, considere criar um dump com mysqldump , armazenando-o no servidor de origem e usando rsync para copiar / atualizar o equivalente no servidor de destino antes de importar. Como rsync criará somas de verificação de rolagem na origem e no arquivo de destino, provavelmente não será necessário transferir a maior parte do o conteúdo do despejo em execuções subseqüentes.

Houve um patch mysqldump que foi usado para usar tabelas temporárias ao inserir linhas e renomear a tabela para o nome da tabela original para reduzir o tempo de bloqueio, mas eu consideraria experimental, pois tem problemas não resolvidos e nunca entrou em o ramo principal. Veja esta discussão para o código de correção e detalhes.

Se você simplesmente não consegue largar a tabela no destino por qualquer motivo, você pode inserir os dados despejados em uma nova tabela (uma abordagem rápida e um pouco insegura canalizaria a saída mysqldump para sed -e 's/mytable/newtable/g' antes de canalizar mais para mysql ) e, em seguida, executar um ciclo UPDATE / DELETE / INSERT com um par de JOINs como este ( não testado , faça uma verificação de sanidade):

/* set write lock on the table so it cannot be read while updating */
LOCK TABLES mytable WRITE;

/* update all rows which are present in mytable and newtable */
UPDATE mytable AS M LEFT JOIN newtable AS N ON M.primarykey = N.primarykey 
SET M.column1=N.column1, M.column2=N.column2 [...]
WHERE N.primarykey Is Not NULL;

/* delete all rows from mytable which are no longer present in newtable */
DELETE M FROM mytable AS M LEFT JOIN newtable AS N on M.primarykey = N.primarykey 
WHERE N.primarykey Is NULL;

/* insert new rows from newtable */
INSERT INTO mytable (primarykey, column1, column2, [...]) 
SELECT (N.primarykey, N.column1, N.column2, [...]) FROM mytable AS M 
RIGHT JOIN newtable AS N ON M.primarykey=N.primarykey WHERE M.primarykey Is NULL

/* release lock */
UNLOCK TABLES;

Nota: é claro, os dados do seu banco de dados seriam inconsistentes enquanto você estiver inserindo / atualizando seus dados, mas contanto que você não esteja usando transações (não disponível para tabelas MyISAM), isso seria o caso não importa o que você faça - derrubar e recriar a tabela criaria inconsistências temporárias, assim como fazer o ciclo de atualização / exclusão / inserção. Isso se deve à própria natureza de um design sem transações atômico do MyISAM.

    

Answer 2

Parece que você quer algo como rubyrep que pode ser sincronizado à esquerda ou à direita e é configurável para o tipo de material que você quer sincronizado de qualquer maneira. No entanto, acho que é o nível do banco de dados e não o nível da tabela. Pode ser um bom ponto de partida para a modificação na sincronização baseada em tabelas.

Outra opção seria usar REPLACE INTO em vez de soltar a tabela, conforme mostrado em

Parece que você pode não ter acesso aos logs ou eu sugiro obter comandos do log binário.

    

Answer 3

Você já tentou usar Triggers?

DELIMITER $$
CREATE TRIGGER sync_table1_insert
AFTER INSERT ON 'table1' FOR EACH ROW
BEGIN
    INSERT INTO table2 (id, value) VALUES (NEW.id, NEW.value);
END;
$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER sync_table1_update
AFTER UPDATE ON 'table1' FOR EACH ROW
BEGIN
    UPDATE table2 SET value = NEW.value WHERE id = NEW.id;
END;
$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER sync_table1_delete
AFTER DELETE ON 'table1' FOR EACH ROW
BEGIN
    DELETE FROM table2 WHERE id = OLD.id;
END;
$$
DELIMITER ;