replicacao

MySQL Fabric – Parte 1 Instalação

MySQL Fabric é uma ferramenta que está inclusa no MySQL Utilities que ajuda a gerenciar servidores MySQL.
Ele funciona basicamente adicionando uma nova camada entre a aplicação e os servidores MySQL, que auxilia no processo de sharding e alta disponibilidade.

Para instalar nosso ambiente com MySQL Fabric, vamos precisar de 4 servidores, eu utilizei os seguintes nomes e IPs:

fabric1 (192.168.0.200) - fabric
mysql1 (192.168.0.201) - mysql master
mysql2 (192.168.0.202) - mysql slave
mysql3 (192.168.0.203) - mysql slave

Obs.: Estou rodando CentOS 6.5 em todos os servidores.

1. Adicione o repositório mysql nos 4 servidores, leia Instalar a versão mais recente do MySQL via yum para mais informações:

rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum update

2. Instale os pacotes mysql mysql-server mysql-utilities:

yum install mysql mysql-server mysql-utilities
chkconfig mysqld on
/etc/init.d/mysqld start

3. Nos servidores mysql1,mysql2,mysql3 adicione as seguintes configurações no arquivo my.cnf:

[mysqld]
...
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=192.168.0.201
report-port=3306
server-id=1
log-bin=mysql1-bin.log

4. No servidor mysql1, adicione os usuários para a replicação (um para cada servidor mysql):


GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd'; "

5. No servidor mysql1 adicione o usuário MySQL para o servidor fabric:


GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';"

6. Nos servidores mysql2 and mysql3, configure a replicação:

CHANGE MASTER TO MASTER_HOST='192.168.0.201', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;

7. No servidor fabric1 adicione o usuário fabric no MySQL:


GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';

[root@fabric1 ~]# mysql -u root -e "GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';"

8. No servidor fabric1, configure o usuário e password no grupos [storage] and [servers] no arquivo /etc/mysql/fabric.cfg :

[storage]
...
password = fabricpwd
...
[servers]
password = reppwd
user = replication

9. No servidor fabric1, configure o banco de dados que será utilizado pelo MySQL Fabric, será perguntado para configurar uma senha, esta senha será utilizada nas próximas vezes que utilizarmos o mysqlfabric:


mysqlfabric manage setup

[root@fabric1 ~]# mysqlfabric manage setup
[INFO] 1406131468.176740 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric).
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc: 
Repeat Password:
Password set.

10. No servidor fabric1, inicie o fabric:


mysqlfabric manage start &

11. No servidor fabric1, adicione um grupo:

mysqlfabric group create GLOBAL1

[root@fabric1 ~]# mysqlfabric group create GLOBAL1
Password for admin: 
Procedure :
{ uuid        = 5e4a6bdb-60f0-4e34-87ba-4c56b7616b35,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

12. No servidor fabric1, adicione mysql1, mysql2 e mysql3 ao grupo GLOBAL1:


mysqlfabric group add GLOBAL1 192.168.0.201
mysqlfabric group add GLOBAL1 192.168.0.202
mysqlfabric group add GLOBAL1 192.168.0.203

[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.201
Password for admin: 
Procedure :
{ uuid        = 39efb9c4-6195-4c41-aa02-0bfdc228bfe2,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.202
Password for admin: 
Procedure :
{ uuid        = c8babfb9-d836-44c0-b4fd-015cd1df8298,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.203
Password for admin: 
Procedure :
{ uuid        = c86bba70-69ac-4923-9c54-1a8aaab6d97e,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

13. No servidor fabric1, pegue o uuid do servidor master:

mysqlfabric group lookup_servers GLOBAL1

[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}

14. No servidor fabric1, adicione o seu master como master do grupo GLOBAL1:

mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'

[root@fabric1 ~]# mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'
Password for admin: 
[WARNING] 1406131951.712366 - Executor-2 - Error () trying to process transactions in the relay log for candidate (('Command (START SLAVE SQL_THREAD, ()) failed: 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO', 1200)).
[INFO] 1406131951.824763 - Executor-2 - Master has changed from None to 2e157d1e-1281-11e4-80dc-080027aa0242.
Procedure :
{ uuid        = 733ae69d-fb12-447b-b86b-041703491315,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'PRIMARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}
[root@fabric1 ~]# 

Agora temos nosso ambiente MySQL Fabric funcionando .
Não perca os próximos post para aprender mais sobre essa nova ferramenta.

MySQL Sandbox

Hoje vamos falar sobre uma excelente ferramenta que todo DBA MySQL precisa ter em mãos, estou falando do MySQL Sandbox.

MySQL Sandbox é desenvolvido por Giuseppe Maxia (The Data Charmer), esta ferramenta auxilia na instalação de servidores para testes. Se você precisa testar algum bug, algum caso especifico relacionado a replicação(suporta master/slave e master/master) esta é a ferramenta que tens que conhecer.

INSTALAÇÃO:

Vá até http://mysqlsandbox.net/ e baixe a versao mais atual (Pode ser via launchpad):

yum install perl perl-ExtUtils-MakeMaker perl-Test-Simple
wget https://launchpad.net/mysql-sandbox/mysql-sandbox-3/mysql-sandbox-3/+download/MySQL-Sandbox-3.0.44.tar.gz
tar -zxvf MySQL-Sandbox-3.0.44.tar.gz
cd MySQL-Sandbox-3.0.44
perl Makefile.PL
make
make test
make install

CRIANDO UMA INSTÂNCIA:

Para criar uma única instância, tudo o que precisamos é o pacote (tar.gz .rpm .deb) da versão do MySQL que desejamos instalar e o comando make_sandbox:

[root@localhost ~]# make_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz 
unpacking /root/mysql-5.6.17-linux-glibc2.5-i686.tar.gz
Executing low_level_make_sandbox --basedir=/root/5.6.17 \
	--sandbox_directory=msb_5_6_17 \
	--install_version=5.6 \
	--sandbox_port=5617 \
	--no_ver_after_name \
	--my_clause=log-error=msandbox.err
    The MySQL Sandbox,  version 3.0.44
    (C) 2006-2013 Giuseppe Maxia
installing with the following parameters:
upper_directory                = /root/sandboxes
sandbox_directory              = msb_5_6_17
sandbox_port                   = 5617
check_port                     = 
no_check_port                  = 
datadir_from                   = script
install_version                = 5.6
basedir                        = /root/5.6.17
tmpdir                         = 
my_file                        = 
operating_system_user          = root
db_user                        = msandbox
remote_access                  = 127.%
bind_address                   = 127.0.0.1
ro_user                        = msandbox_ro
rw_user                        = msandbox_rw
repl_user                      = rsandbox
db_password                    = msandbox
repl_password                  = rsandbox
my_clause                      = log-error=msandbox.err
master                         = 
slaveof                        = 
high_performance               = 
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > 
force                          = 
no_ver_after_name              = 1
verbose                        = 
load_grants                    = 1
no_load_grants                 = 
no_run                         = 
no_show                        = 
do you agree? ([Y],n) 
loading grants
.. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_6_17

Para usarmos esta instância, podemos chamar o script use que se encontra dentro da pasta que o MySQL Sandbox acabou de criar.

[root@localhost ~]# $HOME/sandboxes/msb_5_6_17/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > 

Podemos gerenciar esta instância usando os scripts start / stop / restart / status, que também se encontram dentro da pasta criada.

CRIANDO REPLICAÇÃO MASTER SLAVE:

Para criar um conjunto de instâncias configuradas como master / slave (padrão: 1 master e 2 slaves, mas podemos alterar este comportamento passando o parâmetro --how_many_nodes ) vamos utilizar o comando make_replication_sandbox:

[root@localhost ~]# make_replication_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz 
installing and starting master
installing slave 1
installing slave 2
starting slave 1
.... sandbox server started
starting slave 2
.. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_mysql-5_6_17

Para usar as instâncias criadas, podemos utilizar o script use que está localizado dentro das pastas nodeN (onde N é o numero da instância) e master:

[root@localhost ~]# #MASTER
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/master/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

master [localhost] {msandbox} ((none)) > ^DBye
[root@localhost ~]# #SLAVE 1
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/node1/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave1 [localhost] {msandbox} ((none)) > ^DBye
[root@localhost ~]# #SLAVE 2
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/node2/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave2 [localhost] {msandbox} ((none)) > 

Para instâncias configuradas como replicação, podemos gerenciar cada uma delas também chamando o script start / stop / restart / status que está dentro das pastas master/nodeN ou podemos chamar os scripts terminados com _all (start_all / stop_all / restart_all / status_all) que se encontram na pasta raiz da sandbox criada.

CRIANDO REPLICAÇÃO MULTI MASTER :

Para criarmos instâncias configuradas como multi-master’s, vamos utilizar o comando make_replication_sandbox com a opção --master_master:

[root@localhost ~]# make_replication_sandbox --master_master mysql-5.6.17-linux-glibc2.5-i686.tar.gz
installing node 1
installing node 2
# server: 1: 
# server: 2: 
# server: 1: 
# server: 2: 
Circular replication activated
group directory installed in $HOME/sandboxes/rcsandbox_mysql-5_6_17

É isso aí pessoal, podemos achar mais informações usando o parâmetro –help nos comandos make_multiple_custom_sandbox make_multiple_sandbox make_replication_sandbox make_sandbox make_sandbox_from_installed and make_sandbox_from_source.

Multi-Source Replication com MySQL 5.7 – exemplo

Dando continuidade ao post anterior vamos ver um exemplo de como configurar 2 masters e 1 slave utilizando multi-source replication.
Como foi dito anteriormente, por enquanto esta funcionalidade esta disponível somente na versão labs.
A configuração em si é muito simples, vamos precisar de 2 masters utilizando GTID(veja este outro post e aprenda como configurar) e o slave com as opções para garantir o chamado “crash-safe”.
Master 1 e 2:

gtid-mode=on
enforce-gtid-consistency

Slave

master_info_repository=TABLE
relay_log_info_repository=TABLE
gtid-mode=on
enforce-gtid-consistency

Vamos primeiro criar o usuário para replicação:

master1 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'127.0.0.1' IDENTIFIED BY '123';
master2 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'127.0.0.1' IDENTIFIED BY '123';

Vamos configurar o slave normalmente, com apenas um novo detalhe, foi introduzida a opção FOR CHANNEL. Essa opção basicamente vai identificar cada master que o nosso slave vai se conectar:

slave [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_PORT=7773 FOR CHANNEL="channel1";
slave [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_PORT=7774 FOR CHANNEL="channel2";

Agora basta iniciar a replicação:

slave [localhost] {msandbox} ((none)) > START SLAVE FOR ALL CHANNELS;

Vamos inserir alguns dados em ambos os masters para ver se eles são replicados no servidor slave:

master1 [localhost] {msandbox} ((none)) > CREATE TABLE test.tst1 (ID INT);
Query OK, 0 rows affected (0.12 sec)

master1 [localhost] {msandbox} ((none)) > INSERT INTO test.tst1 SET ID=1;
Query OK, 1 row affected (0.01 sec)

master2 [localhost] {msandbox} ((none)) >  CREATE TABLE test.tst2 (ID INT);
Query OK, 0 rows affected (0.06 sec)

master2 [localhost] {msandbox} ((none)) > INSERT INTO test.tst2 SET ID=2;
Query OK, 1 row affected (0.01 sec)

slave [localhost] {msandbox} ((none)) > USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
slave [localhost] {msandbox} (test) > SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tst1           |
| tst2           |
+----------------+
2 rows in set (0.00 sec)

slave [localhost] {msandbox} (test) > SELECT * FROM tst1;
+------+
| ID   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

slave [localhost] {msandbox} (test) > SELECT * FROM tst2;
+------+
| ID   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Fácil não???

Alguns comandos uteis para se utilizar:

START | STOP SLAVE [SQL_THREAD | IO_THREAD] FOR CHANNEL=”channel name";
START | STOP SLAVE [SQL_THREAD | IO_THREAD] FOR ALL CHANNELS;
SHOW SLAVE STATUS FOR CHANNEL='channel name'\G
FLUSH RELAY LOGS FOR CHANNEL="channel name";
RESET SLAVE FOR CHANNEL="channel name";

Isso ai, temos nosso slave recebendo dados de ambos os master via multi-source replication.

MySQL Multi Source Replication

Na semana passada, durante o evento MySQL Connect, foi lançada a versão de testes do MySQL 5.7 (MySQL 5.7.2 DRM), dentre as novas funcionalidades, uma que chamou bastante a minha atenção foi a replicação de múltiplos masters (multi source replication). Atualmente o MySQL não possui uma funcionalidade oficialmente (build-in) , pode se conseguir esse resultado com alguns hacks, como, a cada x segundos executar um change master to no slave para ficar trocando de master, mas como o nome diz, isso é um “hack”.

Veja como configurar aqui

Não confundam muti source replication com multi master, veja as figura abaixo para entender a diferença:

MySQL Multi Master Replication

MySQL Multi Master Replication


Multi Master Replication – Na figura acima, temos 2 master’s e 1 slave, onde que o master 1 é master do master 2, master 2 é master do master 1 e slave, para que o slave receba as atualizações do master 1, elas tem que passar pelo master 2, para posteriormente serem aplicadas no slave.
MySQL Multi Source Replication

MySQL Multi Source Replication


Multi Source Replication – Na figura acima, temos 2 master’s e 1 slave, master 1 é master somente do servidor slave e master 2 é master somente do servidor slave, o slave por sua vez, recebe as atualizações de ambos os master’s diretamente.

No próximo post, vou explicar como configurar 1 slave recebendo atualizações de 2 masters via multi source replication.
A versão para testes pode ser baixada pelo site labs.mysql.com, se ficou curioso, baixa la e testa.
Lembrando que essa é uma versão de testes, então, não utilize em produção!!!

Por que Replicação Atrasa no MySQL?

Recentemente respondi algumas questões referente a lag(atraso) na replicação, o que percebi é que muita gente não intende corretamente como este processo funciona internamente no MySQL e o porque do lag acontecer:

Veja a figura abaixo, ela representa replicação assíncrona no MySQL, recomendo que você leia meu outro post sobre o assunto:

“Como Funciona A Replicação No MySQL?”

How replication works

Como pode ser visto, existe uma grade diferença quanto ao ponto de entrada de dados no master e no slave, enquanto o master possui múltiplas threads inserindo/alterado/deletando dados ao mesmo tempo, no slave, existe somente uma única thread responsável por aplicar todas essas transações no banco de dados.

Vamos imaginar que uma transação(UPDATE por exemplo) demorou 50 segundos para rodar no master, durante e depois este período, outras conexões executaram comandos no master, o que vai acontecer quando o slave receber e executar essa transação? Isso mesmo, as demais transações pendentes e que forem sendo inseridas no relay log, vão ter que esperar a SQL_THREAD terminar de executar a transação(o nosso UPDATE que demorou 50 segundos no master) para serem processadas.

Se você utiliza MySQL a partir da versão 5.6.3 e sua aplicação divide os dados em múltiplos bancos internamente no MySQL, você pode minimizar este “gargalo” utilizando slave-parallel-workers, com esta opção você poderá configurar o numero de SQL_THREAD'S para executar transações simultâneas desde que elas sejam em banco de dados diferentes.

Como Funciona A Replicação No MySQL?

Veja a figura abaixo, ela representa como a replicação assíncrona funciona no MySQL:
How replication works

Levando em consideração a numeração na figura, entenda como o processo funciona:

  • Item numero 1 na figura representa os clientes executando queries no master, note que o master é capaz de processar múltiplas conexões simultaneamente (que pode ser configurado pela variável max_connections). 
  • O Master executa essas queries, e salva elas em forma de log (item numero 2 na figura) para que o(s) slave(s) possa(m) replica-las e assim ter os mesmo dados que o servidor Master.
  • O slave por sua vez, trabalha com 2 threads para replicar os dados do servidor Master:

    • IO_THREAD – responsável por conectar-se ao Master, e buscar as novas transações do binary log (item numero 3 na figura) e gravar-las em seu log(relay log, item numero 4 na figura) para posteriormente serem aplicadas ao servidor slave.

    • SQL_THREAD – responsável por ler as novas transações do relay log (item numero 5 na figura) e aplica-las no servidor slave (item numero 6 na figura).

Got a packet bigger than ‘slave_max_allowed_packet’ bytes and binlog_format = STATEMENT | MIXED

Desde a versão 5.1.64, o MySQL introduziu um nova variável chamada slave_max_allowed_packet, que foi introduzida para permitir um volume grande de dados quando inserindo ou atualizando registros utilizando replicação baseada em linha (row-based), fazendo com que a replicação não pare caso tu exceda o valor extipulado na variável max_allowed_packet.

O problema é que se tu utiliza a variável binlog_format=STATEMENT ou binlog_format=MIXED, MySQL ignora essa nova opção e continua utilizando max_allowed_packet como limite máximo, mas ele continua a reportar o problema em slave_max_allowed_packet (que por default vem configurada a 1Gb), o que causa a IO_THREAD fornecer a mensagem de erro errada.

Solução:
Rode a seguinte query no master:

master> SHOW VARIABLES LIKE 'binlog_format';

Se tu receber como retorno STATEMENT ou MIXED, tu precisa ajustar o valor da variável max_allowed_packet, uma boa opção se possível, é ter estas duas variáveis configuradas com igual valor no master e no slave.

http://bugs.mysql.com/bug.php?id=68490

http://bugs.mysql.com/bug.php?id=69104

MySQL 5.6 replicação com GTID – Global Transaction ID

Fala galera, No inicio deste mês, a Oracle lançou a nova versão do MySQL, a versão 5.6, uma das melhorias foi a introdução do GTID (ID de transação Global).

GTID é um identificador único que sera adicionado a cada transação executada no servidor, e vai ter grande utilidade para o slave, garantindo que mais de uma thread nao execute a mesma transação e também auxiliar quanto a posição que o slave busca e executa dados do master (previamente tínhamos que setar MASTER_LOG_FILE e MASTER_LOG_POS quando iniciavamos o slave).

Vamos intender algumas novas configuracoes que teremos que adicionar no nosso arquivo de configuracao:

gtid-mode : vai habilitar GTID, temos que habilitar log-bin e log-slave-updates para esta opcao funcionar
enforce-gtid-consistency : vai garantir que somente comandos que podem ser replicados sejam executados (mais informações aqui)

Basicamente o que precisamos é isso, para este tutorial vou usar 2 maquinas virtuas black(master – 192.168.1.110), white (slave – 192.168.1.111), meu arquivo de configuração segue abaixo:

black.cnf

[mysqld]
...
binlog-format=MIXED
log-bin
log-slave-updates=true
gtid-mode=on 
enforce-gtid-consistency=true 
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
binlog-checksum=CRC32
master-verify-checksum=1
server-id=1
report-host=black

white.cnf

[mysqld]
...
binlog-format=MIXED
log-slave-updates=true
log-bin
gtid-mode=on 
enforce-gtid-consistency=true 
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=2
report-host=white

Próximo passo é criar o usuário de replicação no master (black)

GRANT REPLICATION SLAVE ON *.* TO replication@192.168.1.111 IDENTIFIED BY 'reppwd';
FLUSH PRIVILEGES;

Caso o seu servidor contenha dados, voce pode seguir este post para criar um backup do seu master, note que agora quando rodamos o comando SHOW MASTER STATUS; temos uma coluna chamada Executed_Gtid_Set.

Caso seja o caso, restore o backup no slave.

Vamos então, iniciar a replicação, o bacana é que antes tínhamos que indicar o arquivo binário e a posição, veja como fica simples agora com GTID:

CHANGE MASTER TO MASTER_HOST='192.168.1.110', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;

pronto, neste momento você já deve ter sua replicação com GTID funcionando, vamos conferir com o comando abaixo:

white>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.110
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: black-bin.000002
          Read_Master_Log_Pos: 936
               Relay_Log_File: white-relay-bin.000003
                Relay_Log_Pos: 1146
        Relay_Master_Log_File: black-bin.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: 936
              Relay_Log_Space: 1560
              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: 1
                  Master_UUID: 2671c08b-7cf0-11e2-ac39-00163ebee7c2
             Master_Info_File: mysql.slave_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: 2671c08b-7cf0-11e2-ac39-00163ebee7c2:1-4
            Executed_Gtid_Set: 2671c08b-7cf0-11e2-ac39-00163ebee7c2:1-4
                Auto_Position: 1
1 row in set (0.00 sec)

Os campos Retrieved_Gtid_Set e Executed_Gtid_Set mostram a transação lida do master, e a transação executada no slave.
Para conferir se a replicação esta funcionando, vamos digitar no master:

black> CREATE DATABASE gtid_test;
Query OK, 1 row affected (0.00 sec)

black> use gtid_test;
Database changed
black> CREATE TABLE test (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

black> INSERT INTO test (id) VALUES (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

black> 

e agora, vamos conferir se estes registros estao no slave:

white> USE gtid_test;
Database changed
white> SELECT * FROM test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

white>

Isso aew, temos nossa replicacao rodando com Global Transaction ID

Replicação em MySQL com SSL

Hoje vamos dar continuidade a replicação, você pode ler o primeiro post sobre este tema aqui

Primeiramente vamos criar os certificados SSL:

Certificado CA:

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Certificado do servidor

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Certificado do Cliente

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

copy ca-cert.pem, client-req.pem, client-cert.pem to slave server

Vamos ajustar o arquivo de configuração do MySQL:

ssl
ssl-ca=/etc/mysql/sslcerts/ca-cert.pem
ssl-cert=/etc/mysql/sslcerts/server-cert.pem
ssl-key=/etc/mysql/sslcerts/server-key.pem

Não esqueça de reiniciar o serviço do MySQL, vamos verificar se esta tudo ok com o SSL

mysq> SHOW VARIABLES LIKE '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /etc/mysql/sslcerts/ca-cert.pem     |
| ssl_capath    |                                     |
| ssl_cert      | /etc/mysql/sslcerts/server-cert.pem |
| ssl_cipher    |                                     |
| ssl_key       | /etc/mysql/sslcerts/server-KEY.pem  |
+---------------+-------------------------------------+
7 rows IN SET (0.00 sec)

Feito isso, vamos criar um usuário para fazer a replicação, note que vamos especificar que este usuário tem que se conectar através de uma conexão com ssl:

GRANT REPLICATION SLAVE ON *.* TO 'user'@'178.77.90.38' IDENTIFIED BY 'PASSWORD' REQUIRE SSL;
FLUSH PRIVILEGES;

Ok, terminamos de trabalhar no Master, vamos agora ao Slave:

Vamos ajustar o arquivo de configuração do MySQL:

ssl
ssl-ca=/etc/mysql/sslcerts/ca-cert.pem
ssl-cert=/etc/mysql/sslcerts/client-cert.pem
ssl-key=/etc/mysql/sslcerts/client-key.pem

Vamos verificar se esta tudo ok com o SSL no Slave:

mysq> SHOW VARIABLES LIKE '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /etc/mysql/sslcerts/ca-cert.pem     |
| ssl_capath    |                                     |
| ssl_cert      | /etc/mysql/sslcerts/client-cert.pem |
| ssl_cipher    |                                     |
| ssl_key       | /etc/mysql/sslcerts/client-KEY.pem  |
+---------------+-------------------------------------+
7 rows IN SET (0.00 sec)

E finalmente iniciar a replicacao!

CHANGE MASTER TO MASTER_HOST='xxxxxx', MASTER_USER='user', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/sslcerts/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/sslcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/sslcerts/client-key.pem';
START SLAVE;

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