Sincronização segura entre bancos de dados MySQL utilizando SJA

Este tutorial tem como objetivo explicar como fazer a sincronização unilateral entre bancos de dados MySQL utilizando a versão livre do SQLyog Job Agent (SJA) para Linux. Existe também o SJA para Windows, que está no diretório de instalação do SQLyog MySQL GUI, em sua versão Trial.

Convém lembrar que o SJA em si é livre para uso pessoal e comercial, porém o SQLyog, que é basicamente uma interface para o SJA com algumas funcionalidades a mais, possui sua versão livre (Community), e a paga (Ultimate, Enterprise, Professional). Poderíamos utilizar a Community, mas infelizmente ela não oferece a opção de sincronização de banco de dados. O SJA utiliza uma API para MySQL feita em C para conectar nos bancos de dados.

O próprio MySQL oferece opções de sincronização de banco de dados, porém o que acontece na realidade é que muitas vezes não temos acesso ao servidor para alterar seus arquivos de configuração à vontade.

Poderíamos também criar um pequeno script que faz um SELECT de todos os dados do servidor e depois inseri-los no banco de dados cliente, porém isso pode levar muito tempo à medida que mais informações vão sendo armazenadas no banco de dados.

Para solucionar esse problema de maneira fácil, rápida e segura podemos utilizar o SQLyog Job Agent (SJA).

O SJA é uma aplicação multithread de alta performance projetada para executar sincronização entre bancos de dados MySQL. Seus comandos ficam todos em um arquivo XML, sendo assim basta executar o SJA passando o caminho do XML como parâmetro, assim ele lê o arquivo e já cuida de tudo.
O SJA usa um algoritmo que gera os checksums para identificar as mudanças feitas no banco de dados, portanto somente as linhas que foram modificadas ou inseridas, são transferidas entre os hosts, sendo que as deleções também são identificadas e replicadas.
É importante ressaltar que o SJA trabalha melhor com versões de MySQL iguais, pois a função que gera os checksums podem sofrer alguma variação entre as versões.

Diferença entre a replicação incluída no MySQL e a sincronização usando o SJA

Porque utilizar o SJA sendo que o MySQL já nos oferece tal função de sincronização? Bem, poderíamos configurar o cliente e o servidor MySQL de maneira a manter uma sincronização utilizando o esquema de mestre/escravo. O “escravo” lê o log binário do “mestre” e assim poderá replicar todas as operações que o “mestre” executou. Porém muitas vezes não temos acesso suficiente ao servidor para alterar sua configuração conforme nossa necessidade.
O SJA simplesmente utiliza alguns algoritmos de checksum e realiza os INSERTS, UPDATES, ou DELETES de acordo com as mudanças ocorridas no “mestre”. Apesar da possibilidade de ser mais lento que a replicação do MySQL (não testei para saber), o SJA somente necessita de algumas configurações em um arquivo XML e os privilégios de inserir, excluir, alterar e consultar no banco de dados do servidor, facilitando assim o processo de configuração.

Tunelamento SSH (opcional)

Para a conexão entre os bancos de dados criaremos um túnel SSH. Assim a sincronização ficará mais segura e não precisaremos deixar a porta do MySQL com acessos externos. Nos exemplos vamos considerar esta sincronização unilateral. Para fazer uma sincronização bilateral, basta aplicar o tunelamento SSH em ambos os lados. Como estamos supondo uma sincronização unilateral, vamos considerar o cliente como a máquina que vai sincronizar seu banco com o servidor. Vale ressaltar que esta parte é opcional, caso não haja necessidade de transferir os dados de maneira segura basta ignorar esta parte.
Basicamente iremos mapear uma porta do computador cliente para que ao entrarmos, estejamos acessando uma porta do servidor (no caso, a porta do MySQL, 3306). Toda esta conexão será feita através de SSH. Portanto, na realidade não estamos acessando diretamente a porta 3306 do servidor, e sim a porta 22. Assim que criarmos esta conexão, teremos acesso ao MySQL como se estivéssemos dentro do servidor.
Se executarmos o comando abaixo, teremos um tunelamento SSH entre duas máquinas. Não convém a este tutorial explicar como funciona cada parâmetro abaixo. Para isto, basta verificar neste site* o motivo de cada parâmetro.
# ssh -f -L local-port:remote-address:remote-port login-name sleep 60 >> logfile
Sendo que local-port é a porta local (do cliente) a qual queremos acessar diretamente no servidor, remote-address:remote-port é o endereço do servidor com a porta a qual acessaremos o MySQL. O SSH mapeará este endereço com esta porta na local-port do cliente, portanto aqui colocaremos 127.0.0.1:3306 que é o endereço do MySQL de dentro do servidor, e login-name é o login usado para conectar no servidor (Por exemplo root@ServerAddress).
Porém toda vez que executarmos esse comando ele pedirá a senha do servidor e isto não é interessante para a aplicação. Para não termos que digitar esta senha a cada conexão, basta criarmos uma chave DSA no cliente e colocarmos no servidor. Assim o servidor sempre aceitará conexão vinda da máquina que gerou a chave.
Para isso, basta executarmos os seguintes comandos no cliente:
# ssh-keygen -t dsa -f ~/.ssh/id_dsa
# cat ~/.ssh/id_dsa.pub | ssh login-name ‘cat – >> ~/.ssh/authorized_keys’
# cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

Na primeira linha estamos criando uma chave DSA e armazenando em id_dsa. Neste caso estamos usando o parâmetro DSA devido ao suporte ao SSH versão 2. Se seu sistema suporta somente o SSH versão 1, troque esta opção por rsa1.
Na segunda e terceira linha, colocamos a chave dentro do arquivo de chaves autorizadas no cliente e no servidor. login-name é o login do servidor, conforme já informado acima.
Repita o comando para criar o tunelamento e repare que não será pedida a senha novamente.
Referência: PHP, MySQL and SSH Tunneling (Port Forwarding) « The Metric System

Sincronização utilizando SJA

Agora baixe o SJA para Linux no site: http://code.google.com/p/sqlyog/downloads/list
Descompacte o tar.gz em uma pasta qualquer no Linux e dê uma olhada nos exemplos. Nesta mesma pasta crie um xml responsável por sincronizar os bancos de dados. Por exemplo:

<?xml version=”1.0″ encoding=”UTF-8″?>
<job version=”8.2″>
<syncjob>
<fkcheck check=”yes” />
<twowaysync twoway=”no” />
<source>
<host>127.0.0.1</host>
<user>REMOTE_MYSQL_USER</user>
<pwd>REMOTE_MYSQL_PASS</pwd>
<port>PORT_TUNNEL</port>
<database>REMOTE_MYSQL_DATABASE</database>
</source>
<target>
<host>127.0.0.1</host>
<user>LOCAL_MYSQL_USER</user>
<pwd>LOCAL_MYSQL_PASS</pwd>
<port>LOCAL_PORT_MYSQL</port>
<database>LOCAL_MYSQL_DATABASE</database>
</target>
<tables all=”yes”/>
<sync_action type=”directsync” />
<abortonerror abort=”yes” />
<sendreport send=”yes” when=”always”>
<smtp>
<displayname>MEU_NOME</displayname>
<toemail>MEU_EMAIL@gmail.com</toemail>
<fromemail>MEU_EMAIL@gmail.com</fromemail>
<host>smtp.gmail.com</host>
<encryption>tls</encryption>
<port>25</port>
<auth required=”yes”>
<user>MEU_EMAIL@gmail.com</user>
<pwd>MINHA_SENHA</pwd>
</auth>
<subject>yyyy-mm-dd hh:mm:ss</subject>
</smtp>
</sendreport>
</syncjob>
</job>

Em que:

  • REMOTE_MYSQL_USER e REMOTE_MYSQL_PASS: Usuário e senha MySQL do servidor.
  • PORT_TUNNEL: Porta que criamos anteriormente que servirá como tunelamento.
  • REMOTE_MYSQL_DATABASE: Nome do banco de dados que será sincronizado do servidor.
  • LOCAL_MYSQL_USER e LOCAL_MYSQL_PASS: Usuário e senha MySQL do cliente.
  • LOCAL_PORT_MYSQL: Porta MySQL do cliente (normalmente 3306).
  • LOCAL_MYSQL_DATABASE: Nome do banco de dados do cliente (Não precisa ser o mesmo nome que o servidor).

Note que os dois hosts estão como 127.0.0.1, pois como o tunelamento já está feito, acessamos o servidor como se estivéssemos na própria máquina.
Em <sendreport> coloquei para enviar um e-mail para mim (no caso pelo Gmail) toda vez que ocorrer a sincronização. Acho que a aplicação perde pontos neste quesito por exibir a senha sem criptografia, por exemplo, se sua senha for joaquim123, você terá que deixar lá joaquim123 para quem tiver o acesso ao arquivo ler, o ideal seria impor restrições de acesso ao arquivo.
Em relação às outras tags, <fkcheck> checa a integridade das chaves estrangeiras (se realmente existem), <twowaysync> informa se a sincronização será unilateral ou bilateral, <abortonerror> faz com que o programa termine caso aconteça algum problema e a tag <tables> informa quais tabelas serão sincronizadas, no meu caso vou sincronizar todas.
Existem mais tags, porém para descobri-las a única maneira é testar ou olhar nos exemplos, pois a documentação do SJA é praticamente nula.
Agora execute o seguinte comando e veja as tabelas sendo sincronizadas:
# ./sja meuxml.xml
Caso ocorra algum erro, ele será impresso na tela, e para ver detalhes a respeito do problema basta acessar o arquivo sja.log na mesma pasta que o SJA foi executo, a não ser que você tenha passado o nome do arquivo de log como parâmetro (-l logfile).
Outro fator importante a destacar é que na configuração do MySQL (/etc/mysql/my.conf) a diretiva bind-address = 127.0.0.1 não precisa estar comentada, pois o acesso é feito via SSH. Assim, o MySQL só pode ser acessado através do próprio servidor, tornando-o mais seguro.
Para efetuar a sincronização de tempos em tempos podemos utilizar o cron no Linux, ou o Task Scheduler do Windows.

Fonte: Desmonta&CIA

Anúncios

Tags:

About Desmonta&CIA

Somos um blog que busca informar aos apaixonados por tecnologia tudo sobre o mundo de TI.

One response to “Sincronização segura entre bancos de dados MySQL utilizando SJA”

  1. joao says :

    Olá. Funcionou perfeitamente no meu, até criei um arquivo (conectar.sh) para executar tudo de uma vez. Porém quando jogo para ser executado no crontab ele não funciona a linha: ssh -f -L 3307 …
    Todo o resto funciona mesnos essa linha.

    Segue o conecta.sh
    —————————————————————————————-
    #!/bin/bash
    echo iniciando!! >> /home/teste/mysql/teste.txt

    #contecta no servidor – APENAS O COMANDO ABAIXO NÃO EXECUTA
    ssh -f -L 3307:127.0.0.1:3306 root@127.123.26.119 sleep 240 >> /home/teste/mysql/logfile
    #backups
    /home/teste/mysql/sja /home/teste/mysql/festas.xml
    echo ‘fim!!’ >> /home/teste/mysql/teste.txt
    —————————————————————————————-

    Aguardo

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: