Replicação em MySQL – Master – Slave

Para explicar a replicação, iremos utilizar o artigo Instalando MySQL 5.5 Parte 2 – Múltiplas instâncias pois iremos precisar de 2 instâncias rodando no nosso servidor.

Iremos ativar os logs binários do nosso servidor, criando um arquivo my_rep.cnf com o seguinte conteúdo:

[mysqld_multi]
mysqld = /mysql/mysql/bin/mysqld_safe
mysqladmin = /mysql/mysql/bin/mysqladmin
[mysqld1]
port = 3306
datadir = /mysql/3306/data
socket = /mysql/3306/mysql.sock
log-error = /mysql/3306/logs/mysqld.log
pid-file = /mysql/3306/mysqld.pid
server_id = 1
#LOGS
log-bin = /mysql/3306/logs/server1_bin.log
log-bin-index = /mysql/3306/logs/server1_log-bin.index
expire_logs_days = 7
binlog-format = MIXED
[mysqld2]
port = 3307
datadir = /mysql/3307/data
socket = /mysql/3307/mysql.sock
log-error = /mysql/3307/logs/mysqld.log
pid-file = /mysql/3307/mysqld.pid
server_id = 2
#LOGS
relay-log = /mysql/3307/logs/server2.relay_log
relay-log-index = /mysql/3307/logs/server2.relay_log_index
expire_logs_days = 7

Feito isso vamos iniciar o serviço conforme o artigo:

cd /mysql/mysql
mysqld_multi --defaults-file=/mysql/my_rep.cnf start

Agora vamos conectar no servidor 3306 e criar um usuário chamado ‘replicacao’ com senha ‘replicacao’ com direito a replicação para que ele possa se conectar ao servidor:

mysql -u usuario -p -h 127.0.0.1
mysql> GRANT REPLICATION SLAVE ON *.* TO replicacao@127.0.0.1 IDENTIFIED BY 'replicacao';
mysql> FLUSH PRIVILEGES;

Devemos então conectar no servidor slave nesse casso o 3307 e dar o start na replicação:

mysql -u usuario -p -h 127.0.0.1 --port 3307
mysql> CHANGE MASTER TO MASTER_HOST = '127.0.0.1', MASTER_USER = 'replicacao', MASTER_PASSWORD = 'replicacao', MASTER_PORT = 3306, MASTER_LOG_FILE = 'server1_bin.000001', MASTER_LOG_POS = 0;
mysql> START SLAVE;

Vamos explicar o comando

CHANGE MASTER TO

como o próprio nome diz, ele “seta o master para” :

MASTER_USER = usuário que irá acessar o servidor master
MASTER_PASSWORD = senha do usuário que irá acessar o servidor master
MASTER_PORT = a porta do mysql no servidor master
MASTER_LOG_FILE = nome do arquivo de log binário (setamos isso como server1_bin.log no .cnf e podemos observar que ele cria arquivos com índice numérico que cresce a cada vez que o servidor é iniciado ou chega ao limite estipulado na configuração max_binlog_size que por padrão é 1GB
MASTER_LOG_POS = a posição que o slave irá começar a buscar dentro do binlog

E como monitoramos a replicação??? Simples com o comando

SHOW SLAVE STATUS;

A replicação trabalha com 2 threads:

Slave_IO = É a thread responsável por pegar os comandos sql executados no master e salvar no disco do slave
Slave_SQL = É a thread responsável por executar os comandos sql que foram buscados na thread Slave_IO

Vou explicar 5 status importantes do comando

SHOW SLAVE STATUS;

Slave_IO_Running = Nos diz se a thread de IO está rodando temos que encontrar o status Yes nessa linha
Slave_SQL_Running = Nos diz se a thread de SQL está rodando temos que encontrar o status Yes nessa linha
Seconds_Behind_Master = Nos diz se o nosso slave está atrazado em relação ao master, nesta linha temos que encontrar o numero 0(Zero) para que o slave esteja atualizado em relação ao master
Last_IO_Error = Caso exista algum erro com a thread de IO nesta linha ele irá informar o que está acontecendo
Last_SQL_Error = Caso exista algum erro com a thread de SQL nesta linha ele irá informar o que está acontecendo

E pronto, temos um servidor MASTER replicando seus dados em um SLAVE, todos os dados criador / editados / deletados no servidor 3306 serão replicados no servidor 3307.

Mais detalhes sobre replicação em MySQL podem ser encontrados na documentação

Gostou ? Ajude e Compartilhe!
Esta entrada foi publicada em binlog, MySQL, replicacao. Adicione o link permanente aos seus favoritos.
  • riggnsRobson

    Boa tarde, eu fiz o que dizia o seu tutorial e deu certo, porém estou com uma realidade aqui onde trabalho um pouco diferente, eu preciso de replicar varios masters em um slave, isso é possível?? tem como?

    • Assim, cada slave tem apena um master, o que voce tera que fazer, e uma replicacao MASTER -> MASTER (ja queria ter escrito um artigo sobre isso, mas ando bastante ocupado) e tera que colocar um slave replicando um destes masters.

  • Walfredo

    Isto funciona em hospedagem compartilhada? Tipo… meu servidor 1 é webfullhost e o servidor 2 é hostgator. Quero fazer o master webfullhost e o slave hostgator. É possível? Vou precisar de acesso root ou dá pra fazer em hospedagem compartilhada?

    • marceloaltmann

      Tu vai precisar dos binlogs ativados, consulta teu gerente te contas e pergunta se esta opcao esta liberada.

  • Danilo

    Estou tentando realizar a replicação on windows 7. Sempre que executo o show slave status\G, mostra que o Slave_IO_Running: NO. Não sei o que pode estar acontecendo. Não consigo deixar minha máquina como escrava de um outro computador master. Mas, eu consigo ser o master para outro computador. não sei que pode estar havendo. Modifiquei o my.ini de acordo com um tutorial (que posso te enviar por email). Não consigo mudar o SLAVE_IO para YES. =/ Como posso fazer?

    • marceloaltmann

      No arquivo de log do MySQL tem alguma coisa relacionada a replicacao? tu configurou o bin log e relay log correto?

  • Valter Costa

    Não sei se entendi sua necessidade, mas creio que uma solução seria cria várias instâncias em portas diferentes em um único Hardware e configurar cada um deles para se conectar a um master diferente.
    Assim você terá um servidor ( Hardware ) Slave conectado a vários servidores Masters.

  • Fabio Araujo

    Amigo, primeiro parabens pelo post!!!
    Gostaria de saber se é possivel fazer como no oracle um switch do bin log ? ou seja gerar um novo a partir de algum comando? sabe se é possivel ?

    • marceloaltmann

      Fala Fabio,
      Nas versões anteriores a 5.5.3, podes usar o comando FLUSH LOGS;, este comando irá fechar e abrir todos os logs do mysql (binary, relay, slow, general, inclusive os logs das engines, como o InnoDB por exemplo).
      Nas versões superiores a 5.5.3, pode ser especificado qual log deseja dar o FLUSH, no teu caso, tu deseja dar o “switch” somente no binlog, vais usar o log_type binary: FLUSH BINARY LOGS;

      A lista completa dos tipos de logs pode ser encontrada em http://dev.mysql.com/doc/refman/5.6/en/flush.html

      Abraço.

  • Orseni Campos

    Ótimo post.

    Tenho 2 dúvidas em relação a essa replicação com MySQL.
    1 – O slave é quase um “backup a quente”? Ou é feito um balanceamento de cargas entre o master e o slave?
    2 – Um servidor slave com hardware inferior ao master irá comprometer a performance do master?

    Obrigado Marcelo.

    • marceloaltmann

      Olá Orseni.

      Referente as tuas dúvidas:
      1 – O slave nao pode ser considerado como backup, ele pode ter este papel em caso de falha no hardware do servidor master, porém, ele não seria um backup caso alguém acidentalmente digitar um DROP DATABASE NOME_DE_UM_BANCO por exemplo, visto que ele replica todos os comandos executados no master. Quanto ao balanceamento, a configuração realizada no MySQL não lhe traz o balanceamento de carga, ela possibilita isso, porém terás que configurar isso na aplicação ou utilizar alguma outra ferramenta como MySQL Fabric.
      2 – a resposta é depende, normalmente um servidor slave não comprometerá a performance do Master, recomendo que leias Como Funciona A Replicação No MySQL?. Por padrão, a replicação no MySQL é assincronia, ou seja, a transação acontece no master, a conexão que executou a transação irá receber o ok do master e posteriormente o slave irá receber esta transação. Porém, ela pode ser configurada como semi-síncrona, o que altera um pouco o processo de o master dar o ok para a conexão, nestes casos, acontece o seguinte:

      • Master recebe uma transação
      • Master processa a transação
      • Master aguarda por milisegundos configurados na variável rpl_semi_sync_master_timeout até que ao menos 1 slave receba a transação
      • Master retorna ok para a conexão

      No caso acima, caso tenhas somente 1 slave, ele pode afetar um pouco a performance do master. Note que uma vez que ocorra o timeout acima, o master vai retornar para o método de replicação padrão até que ao menos 1 slave esteja 100% sincronizado.

      Espero que tenha sanado as tuas dúvidas.