Criando uma replicação no Mysql – (Master – Slave)

No Mysql podemos criar vários tipos de replicação, sendo a mais comum a master/slave, onde toda escrita feita no servidor master será replicado para o servidor slave.

A principio estarei descrevendo os passos para a criação de uma replicação Master/Slave em outro post estarei falando sobre replicação Master/Master.

Obs: A Replicação no mysql não é um tipo de backup como alguns pensam, isso porque toda ocorrência que ocorrer no Servidor Master será aplicado no Servidor Slave, ou seja, as bases terão as informações iguais.
Se ocorrer um drop no Master também vai ocorrer no Slave, tenha muito cuidado com isso.
A replicação é muito útil quando se deseja fazer um balanceamento de carga (ex: consulta pesadas podem ser direcionadas para o Slave, relatórios, backups, teste de upgrade de versão, alta disponibilidade, coisas assim…)

Master-Slave_trabalha

Como a replicação trabalha:

No servidor Master devemos habilitar o log binário, onde será registrado todas as alterações no servidor.

1 – O Master grava as mudanças do dados no binary log (log binário) – essas gravações são chamadas binary log events (eventos do log binário)

2 – A replica (Slave) copia os eventos do log binário do Master para o relay log.

3 – A replica (Slave) acessa os eventos no relay log aplicando as mudanças em seus próprios dados.

A Primeira parte do processo é o binário log no master ( Eu vou mostrar como setar isso depois). Antes de cada transação terminar de completar de atualizar os dados no Master, o Master grava as mudanças no log binário ( binary log), o Mysql escreve as transações serialmente no binário log, mesmo se as declarações na transação forem intercaladas durante a execução.
Depois de escrever os eventos no log binário, o Master  diz ao storage engine commitar a transação.

O próximo passo é para a replica (Slave) copiar o log binário do Master em seu próprio hard drive (disco rigido) dentro do chamado relay log. Para iniciar, é startado um trabalho do thread, chamado de I/O slave thread. O I/O thread abri uma conexão cliente com o servidor Master, então inicia um especial processo de dump do log binário ( binlog dump process). Não existe correspondência de comando SQL.

O binlog dump process ler os eventos do log binário do Master. Quando não se tem eventos, este processo dorme (sleep) e espera (wait) por um sinal do Master quando existirem nos eventos. O I/O thread escreve os eventos no relay log da replica (Slave).

O SQL slave thread é a ultima parte do processo. Esse thread lê e reaplica os eventos do relay log, atualizando os dados da replica correlacionado com os dados do Master. Os eventos que o SQL thread executa pode opcionalmente ir dentro do log binário da própria replica, habilitando o binário log da replica e possibilitando replicar de uma replica também. Estarei comentando sobre isso.

Levantamento de informações do Servidores
Faça um levantamento dos servidores envolvidos, neste exemplo estarei fazendo uma replicação com dois servidores, mais nada impede de você criar em um único servidor com dois serviços de mysqld instalado para teste.

Os dois servidores devem possuir o mysql já instalado para a criação da replicação, caso queria saber como instalar o mysql, eu indico a instalação via arquivo binário é a que eu mais gosto, por termos mais controle onde cada arquivo está instalado. Você pode acompanhar no post que escrevi sobre este tipo de instalação aqui.

# Dados Servidor Master
hostame: server1
ip: 192.168.0.5

# Dados Servidor Slave
hostname: server2
ip: 192.168.0.10

Setando a Replicação:

1 – Setando uma conta para replicação

2 – Configurando o Master e o Slave (replica)

3 – Instruindo a replica conectar e replicar do master os eventos.

1 – Criando a conta de usuário para a Replicação

O I/O slave thread que executa na replica, faz um conexão tcp/ip no master. Isso significa que deve criar uma conta de usuário no master dando privilégios apropriados.

Aqui está como criar essa conta de usuário, nos vamos chamar o usuário de repl.

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@’192.168.0.%’ IDENTIFIED BY ‘p4ssword’;

Estarei criando essa conta de usuário em ambos os servidores no Master e no Slave, nota que eu restringi o uso de conta local (localhost) porque a conta de replicação deve ter habilidade de leitura em todas as mudança no servidor.

O usuário de replicação precisa somente do privilégio REPLICATION SLAVE sobre o master e não precisa realmente do privilégio REPLICATION CLIENT, então porque conceder este privilégio em ambos os servidores. Existe duas razões.

* A conta que usamos para monitorar e gerenciar a replicação precisa do privilégio REPLICATION CLIENT é mais fácil usar a mesma conta para ambos os propósitos.
* Se setarmos a conta no Master e depois clonar a replica disso, a replica vai esta setado conforme o master, nesse caso queremos a replica e o master com regras iguais.

2 – Configurando o Master e o Slave (replica)

O próximo passo é habilitar alguns parâmetros no Master, no qual se chama server1. Nós precisamos habilitar o log binário e especificar o  Server id, o id do servidor. Esses parâmetro você deve setar dentro do arquivo de configuração my.cnf.

### Master ###

# — Básico

log_bin = mysql-server1-bin
server_id = 10

# –Avançado

sync_binlog=1

Você deve setar explicitamente um valor para server_id porque o valor 1 é setado como valor default, caso você não configure um. Usando o valor 1 pode causar conflito com servidores que não tem o valor setado explicitamente.
É necessário restart o mysql para que o valor entre em vigor.

— Verificando se o o log binário está ativo
mysql> show master status;
+——————————–+————-+———————-+—————————+—————————–+
| File                            | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set |
+———————————————–+———————-+—————————+—————————–+
| mysql-server1.000001  |         98 |                        |                            |                              |
+———————————————–+———————–+—————————+—————————-+
1 row in set (0.01 sec)

A replica vai precisar de uma configuração parecida com o do Master sendo também necessário restart o mysql depois.

### Slave ###

# — Básico

log_bin = mysql-server2-bin
server_id = 2
relay_log = /usr/local/mysql/data/mysql-server2-relay-bin

# — Avançado

log_slave_updates = 1
read_only = 1

Alguns parâmetros acima não são muito necessário, mais setei só para esclarecer uma dúvida.

Habilite o read_only somente se você deseja que o slave não aceite escrita, no me caso setei para 1 para não permitir escrita.
Habilite o log_slave_updates somente se o slave será Master de outro servidor.

Normalmente, o slave não loga em seu próprio log binário alguns updates que são recebidos do servidor master. Esta opção diz ao slave logar os updates feitos pelo SQL thread em seu próprio log binário. Para essa opção ter algum efeito, o slave deve também ser inicializado com a opção –log-bin habilitado.

É utilizando quando se deseja fazer a replica ser master de outra replica, conforme a figura abaixo.

Master-Slave_trabalha_replica_da_replica

3 – Instruindo a replica conectar e replicar do master os eventos.
Inicializando a Replica

O próximo passo é dizer a replica como conectar no Master e iniciar o replay do log binário. Você não deve usar o my.cnf para isso, use a declaração CHANGE MASTER TO . Com essa declaração você não vai precisar restart o mysql, esta opção deixa apontar para uma replica diferente no futuro caso desejado, sem a necessidade de parada de banco de dados.

CHANGE MASTER TO MASTER_HOST =’server1′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’p4ssword’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-server1.000001′,
MASTER_LOG_POS=0;

O parâmetro MASTER_LOG_POS é setado para 0 porque é o início do log. Depois que você executar isso, você poderá inspecionar a saída SHOW SLAVE STATUS e verificar se a replicação está ok.

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: server1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-server1.000001
Read_Master_Log_Pos: 164
Relay_Log_File: mysql-server2-relay-bin.000001
Relay_Log_Pos: 164
Relay_Master_Log_File: mysql-server1.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1149
Relay_Log_Space: 1649
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID: 4d44af96-4cf1-11e5-9410-0800275171d0
Master_Info_File: /usr/local/mysql-5.6.26-linux-glibc2.5-x86_64/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

Os parâmetros Slave_IO_State, Slave_IO_Running e Slave_SQL_Running mostram que a replicação não está executando.

Para inicializar a replicação utilizar o comando  START SLAVE;

mysql> start slave;
Query OK, 0 rows affected (0.08 sec)

Verificando o status da replicação novamente:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: server1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-server1.000002
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-server2-relay-bin.000003
Relay_Log_Pos: 280
Relay_Master_Log_File: mysql-server1.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 618
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 4d44af96-4cf1-11e5-9410-0800275171d0
Master_Info_File: /usr/local/mysql-5.6.26-linux-glibc2.5-x86_64/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

mysql>

Verificando novamente os parâmetros Slave_IO_StateSlave_IO_Running e Slave_SQL_Running podemos reparar que a replicação agora está funcionado.

Slave_IO_State: Waiting for master to send event     — Está esperando o Master enviar novos eventos.
Slave_IO_Running: Yes                                           — Informa que o thread IO Slave está executando.
Slave_SQL_Running: Yes                                        — Informa que o thread SQL Slave está executando.
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it  —- informa que o thread SQL Slave tem lido todo o relay log, e está esperando o thread I/O slave enviar (atualizar) o relay log com novos eventos, para ser aplicado no banco novamente pelo tread SQL slave.

O parâmetro Seconds_Behind_Master: não está setado mais como NULL.
Caso ocorra algum problema na replicação os campos abaixo informará o tipo de erro

Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

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

Após inicializar a replicação podemos vê os processos conectados no servidores, através do comando SHOW PROCESSLIST;

server1 – Master

mysql> SHOW PROCESSLIST;
+—-+——+——————-+——+————-+——+———————————————————————–+——————+
| Id | User | Host              | db   | Command     | Time | State                                                                 | Info             |
+—-+——+——————-+——+————-+——+———————————————————————–+——————+
|  1 | repl | server2:57946     | NULL | Binlog Dump | 1404 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
|  2 | root | localhost         | NULL | Query       |    0 | init                                                                  | SHOW PROCESSLIST |
+—-+——+——————-+——+————-+——+———————————————————————–+——————+
2 rows in set (0.00 sec)

server2 – Slave

mysql> SHOW PROCESSLIST;
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
|  2 | root        | localhost | NULL | Query   |    0 | init                                                                        | SHOW PROCESSLIST |
|  3 | system user |           | NULL | Connect | 1304 | Waiting for master to send event                                            | NULL             |
|  4 | system user |           | NULL | Connect | 1303 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+—-+————-+———–+——+———+——+—————————————————————————–+——————+
3 rows in set (0.00 sec)

Sobre mrochadba

Database Administrator

Publicado em 15/09/2015, em Uncategorized. Adicione o link aos favoritos. Deixe um comentário.

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 )

Foto do Google

Você está comentando utilizando sua conta Google. 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 )

Conectando a %s

%d blogueiros gostam disto: