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!!!

MySQL forçar usuário a trocar a senha

Desde a versão 5.6.6 MySQL introduziu o habilidade the forcar usuários a trocar suas senhas.
Tu pode fazer isso digitando ALTER USER PASSWORD EXPIRE.

ALTER USER 'marcelo'@'localhost' PASSWORD EXPIRE;

Na próxima vez que marcelo tentar conectar ao MySQL via localhost, o servidor ira bloquear todos os comandos até que o usuario use SET PASSWORD;

mysql> \s
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Para alterar a senha, e utilizar o server normalmente, basta digitar:

SET PASSWORD = PASSWORD('senha');
#Ou utilizando o hash gerado por SELECT PASSWORD('senha');
SET PASSWORD = '*AD77F56D2FD78299B87609DCC0423260B5AADB03';

Mas tome cuidado, se tu marcar a senha de um usuário como expirada, ele não poderá conectar via clientes com versão anterior a 5.6.10:

ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

Peter Zaitsev escreveu sobre isso em um de seus posts, em algumas instalações via RPM, o MySQL seta uma senha temporario para o usuario root e te forca a trocar ela no proximo login.

Mais informações podem ser encontradas em MySQL Password Expiration Documentation e MySQL ALTER USER Documentation

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

Snapshot – backup consistente no MySQL

Hoje vamos falar de como realizar um backup (snapshot) consistente do nosso banco de dados.
Primeiramente, em qual situações podemos utilizar o snapshot?

1. Digamos que seu servidor de produção vai ser partir de hoje replicado em um slave, como você faz essa carga inicial de dados no slave? qual era a posição do bin log quando o backup iniciou, durante o período de backup, sera que alguém enviou alguma query para o banco? e se o bin log esta numa posição diferente da de quando o backup iniciou?

2. Caso você queira implementar uma estrategia de backup incremental, você pode fazer uma copia integra da base uma vez na semana, e caso necessite restaurar a base, aplicar os bin logs a partir da copia.

Dito isso, vamos ao que nos interessa.
Para garantir a integridade vamos precisar trabalhar em 2 sessões, a primeira vai ser responsável por garantir a integridade (vamos dar lock em todos os bancos) e a segunda sera responsável pela copia dos dados.

Então, vamos iniciar:

Sessao 1:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Você devera receber um retorno igual a este:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

IMPORTANTE: DEIXE ESTA SESSÃO ABERTA DURANTE TODO O PROCESSO, SE VOCÊ FECHAR ESTA SESSÃO, O LOCK DO BANCO SERA SOLTO.

para realizar o backup vamos utilizar o mysqldump:
Sessao 2:

mysqldump -u usuario -p --all-databases > all_dbs.sql

feito isso, assegure-se de que o backup tenha terminado e o dump termine com:
— Dump completed on xxxx-xx-xx xx:xx:xx

pronto, podemos liberar o lock da sessão 1:

UNLOCK TABLES;

Pronto, já temos o snapshot do nosso banco de dados.

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;

Como renomear tabelas no MySQL

Para renomear uma tabela no MySQL você só precisa rodar um comando chamado RENAME TABLE, a sintaxe é bastante simples:

RENAME TABLE tb1 TO tb2;

O comando RENAME TABLE vai renomear a tabela atomicamente, o que significa que a tabela ficara travada durante o comando.
Você também pode renomear varias tabelas em um único comando:

RENAME TABLE tb1 TO tb2, tb3 TO tb4;

Para renomear uma tabela, você vai precisar dos privilégios ALTER e DROP na tabela antiga e CREATE e INSERT na tabela nova.

Você só precisa ter cuidado caso você utilize TRIGGERS, no exemplo abaixo, depois de inserir um registro na tabela Cities eu conto +1 no NOfCities na tabela Countries, o que acontece se eu renomear a tabela Contries?

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: PlusOneCities
               Event: INSERT
               Table: Cities
           Statement: UPDATE Countries SET NOfCities = NOfCities + 1 WHERE Code = NEW.CountryCode
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> INSERT INTO Cities SET Name = 'City1', CountryCode = 'BRA', District = 'Brasilia', Population = 10000;
Query OK, 1 row affected (0.04 sec)

mysql> RENAME TABLE Countries TO Country;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO Cities SET Name = 'City2', CountryCode = 'BRA', District = 'Brasilia', Population = 10000;
ERROR 1146 (42S02): Table 'world.Countries' doesn't exist

Viu, vamos precisar alterar manualmente as TRIGGERS.

você pode encontrar mais informações na Documentação do MySQL