MySQL replication – Master – Slave

Send to Kindle

To explain the replication, we will use the article Installing MySQL 5.5 Part 2 – Multiple instances with mysqld_multi because we will need 2 MySQL’s running on our server

Let’s activate the binary logs on our server, creating a file named my_rep.cnf with the follow content:

[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

Done this, let’s start the MySQL service like our article:

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

Now, we will connect to server 3306 and create a new user named ‘replication’ with password ‘replication’ and rights to replication.

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

Now we need to connect on our slave server and run a start command to start our replication:

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

Let’s explain the CHANGE MASTER TO command:

MASTER_USER = user which will connect to master server
MASTER_PASSWORD = user password which will connect to master server
MASTER_PORT = the mysql master server port
MASTER_LOG_FILE = binary log file name (we set it to server1_bin.log on .cnf file, like we can see, MySQL create file with numeric index which increase each time which server is restarted or exceed the limit set on our configuration by max_binlog_size key which by default is 1GB
MASTER_LOG_POS = the position which the slave will start to search inside the binlog

And how to monitor the replication??? Simple with the command

SHOW SLAVE STATUS;

The replication works with 2 threads:

Slave_IO = Is the thread responsible to get all sql commands from master and save them at the slave disc
Slave_SQL = Is the thread responsible to run the commands which Slave_IO get from master server

Now, I will explain 5 important status from

SHOW SLAVE STATUS;

Slave_IO_Running = Tell us if the IO thread is running, we need to see a Yes on this line
Slave_SQL_Running = Tell us if the SQL thread is running, we need to see a Yes on this line
Seconds_Behind_Master = Tell us if our slave is delayed in relation to our master, here we need to see a 0(zero)
Last_IO_Error = In case we have a problem with IO thread, this line will show us the problem
Last_SQL_Error = In case we have a problem with SQL thread, this line will show us the problem

And is it, now he have a Master server replication their data to a SLAVE server.

To see more details about replication on MySQL, please read MySQL Documentation

Send to Kindle
This entry was posted in binlog, MySQL, replication. Bookmark the permalink.
  • 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?

    • http://marceloaltmann.com Marcelo Altmann

      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.