Replicação MySQL Master/Master

Replicação de base de dados é a capacidade das bases de dados num servidor MySQL serem duplicadas para outros servidores. Ela tem o objectivo de aumentar a velocidade e confiabilidade de uma estrutura de base de dados. Neste tutorial irei mostrar como configurar servidores de base MySQL com Replicação em cadeia/anel. Utilizada, por exemplo, em estrutura de dados geograficamente distribuídos.

O MySQL permite diversos tipos de replicação. Pode-se por exemplo utilizar diversas máquinas numa rede trabalhando como servidores de base de dados, todas com o mesmo conteúdo, e balancear as requisições de consultas entre esses servidores para aumentar a velocidade. Em caso de problemas críticos com o servidor master, pode redireccionar todo o tráfego para os slaves com o objectivo de manter o serviço em funcionamento sem interrupções, enquanto se faz no servidor master.

Um slave também pode ser master de outro master ou slave. Numa replicação em cadeia fechada podemos ter o seguinte cenário:

mysql01 ====⇒ mysql02 ====⇒ mysql03 ====⇒ mysql04 ====⇒ mysql01

Esta estrutura pode ser utilizada para aplicações distribuídas geograficamente. Uma alteração efectuada num servidor da cadeia, será replicada para outros servidores espalhados geograficamente. E as consultas podem ser direccionadas para o servidor mais próximo.

Podemos também aplicar um algoritmo externo para redireccionar as consultas para um dos servidores com maior disponibilidade num determinado momento. Caso algum servidor precise de manutenção, podemos retira-lo do “anel” e quando ele voltar, ele “puxa” todas as transações do master do ponto onde parou.

Configuração:

Vamos utilizar 4 Servidores com o Debian Wheezy porém pode ser utilizado servidores mistos como FreeBSD/OpenBSD/Windows. Podem ser utilizados mais servidores dependendo de suas necessidades. Seguem as denominações e características hipotéticas de cada um:

  • Hostname: mysql01
  • IP address: 10.101.0.25/24
  • V. MySQL: 5.1
  • Hostname: mysql02
  • IP address: 10.101.0.26/24
  • V. MySQL: 5.1
  • Hostname: mysql03
  • IP address: 10.101.0.27/24
  • V. MySQL: 5.1
  • Hostname: mysql04
  • IP address: 10.101.0.28/24
  • V. MySQL: 5.1

Todos os servidores serão slave e master obrigatoriamente.

Vamos mandar instalar o mysql em todos os servidores aqui estou levando em consideração que os servers são base Debian, caso esteja utilizando outra distribuição Linux/Unix verifique a documentação para a instalação do MySQL no SO que deseja utilizar.

aptitude update && aptitude install mysql-client-5.1 mysql-server-5.1 mysql-server-core-5.1 -y

Temos que acertar agora o arquivo de controle do mysql para que ele não escute somente localhost e sim em qualquer interface que esteja nele.

Temos que mudar o arquivo /etc/mysql/my.cnf o parametro bind-address = 127.0.0.1 para bind-address = 0.0.0.0

Precisamos fazer esta alteração nos 4 servidores

sed -i 's/127.0.0.1/0.0.0.0/g' /etc/mysql/my.cnf

Como todas as máquinas serão master e slave é necessário criar um utilizador de replicação em cada servidor.

Agora em todos os servidores precisamos criar um usuário para efetuar a replicação.

mysql -u root -p
[...]
mysql> GRANT REPLICATION SLAVE,SUPER,RELOAD ON  *.* TO replica@'%' IDENTIFIED BY 'WWgmG7YYFrZrxglJxzyWvA6os';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Assim criamos um utilizador chamado replica com a password WWgmG7YYFrZrxglJxzyWvA6os e com os privilégios REPLICATION SLAVE,SUPER,RELOAD. Na prática somente o privilégio REPLICATION SLAVE é necessário. Adicionamos também os privilégios SUPER e RELOAD para utilizar o comando “LOAD DATA FROM MASTER”, por exemplo.

Agora vamos às configurações dos servidores através do my.cnf (my.ini no windows). Seguem abaixo as configurações necessárias para cada servidor com uma breve descrição de cada directiva. Adicione estas configurações na secção [mysqld] de cada my.cnf ou my.ini:

Vamos configurar o MySQL01.

vim /etc/mysql/my.cnf
[...]
[mysqld]
server-id=1 # Id que identifica o servidor. Deve ser único para cada server
log-bin = 
log-slave-updates # Ativa log-slave-updates . Obrigatório para replicação em cadeia
max_allowed_packet = 10M # Aumente max_allowed_packet se você possui uma base de dados grande
master-host = 10.101.0.26 # Ip do server MySQL02
master-user = replica # Utilizador para replicação
master-password = WWgmG7YYFrZrxglJxzyWvA6os # Password
master-port = 3306 # Porto utilizado para replicação, padrão=3306
auto_increment_increment=10
auto_increment_offset=1

Vamos configura o MySQL02.

vim /etc/mysql/my.cnf
[...]
[mysqld]
server-id=2 # Id que identifica o servidor. Deve ser único para cada server
log-bin
log-slave-updates # Ativa log-slave-updates . Obrigatório para replicação em cadeia
max_allowed_packet = 10M # Aumente max_allowed_packet se você possui uma base de dados grande
master-host = 10.101.0.27 # Ip do server MySQL03
master-user = replica # Utilizador para replicação
master-password = WWgmG7YYFrZrxglJxzyWvA6os # Password
master-port = 3306 # Porto utilizado para replicação, padrão=3306
auto_increment_increment=10
auto_increment_offset=1

Vamos configurar o Mysql03.

vim /etc/mysql/my.cnf
[...]
[mysqld]
server-id=3 # Id que identifica o servidor. Deve ser único para cada server
log-bin
log-slave-updates # Ativa log-slave-updates . Obrigatório para replicação em cadeia
max_allowed_packet = 10M # Aumente max_allowed_packet se você possui uma base de dados grande
master-host = 10.101.0.28 # Ip do server MySQL04
master-user = replica # Utilizador para replicação
master-password =  WWgmG7YYFrZrxglJxzyWvA6os # Password
master-port = 3306 # Porto utilizado para replicação, padrão=3306
auto_increment_increment=10
auto_increment_offset=1

Vamos configurar o Mysql04.

vim /etc/mysql/my.cnf
[...]
[mysqld]
server-id=4 # Id que identifica o servidor. Deve ser único para cada server
log-bin
log-slave-updates # Ativa log-slave-updates . Obrigatório para replicação em cadeia
max_allowed_packet = 10M # Aumente max_allowed_packet se você possui uma base de dados grande
master-host = 10.101.0.25 # Ip do server MySQL01
master-user = replica # Utilizador para replicação
master-password = WWgmG7YYFrZrxglJxzyWvA6os # Password
master-port = 3306 # Porto utilizado para replicação, padrão=3306
auto_increment_increment=10
auto_increment_offset=1

Agora precisamos reiniciar o serviço do mysql nos 4 servidores:

/etc/init.d/mysql restart

Vamos consultar o status da replicação

Vamos consultar o servidor mysql01

mysql -u root -p
[...]
mysql> show slave status\G
*************************** 1. row  ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.101.0.26
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysqld-bin.000001
             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: 106
              Relay_Log_Space: 408
              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: 
1 row in set (0.00 sec)

Observe as linhas Slave_IO_Running e Slave_SQL_Running e também Slave_IO_State para se certificar que a replicação não tem algum erro.

Vamos consultar o servidor mysql02

mysql -u root -p
[...]
mysql> show slave status\G
*************************** 1. row  ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.101.0.27
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysqld-bin.000001
             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: 106
              Relay_Log_Space: 408
              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: 
1 row in set (0.00 sec)

Observe as linhas Slave_IO_Running e Slave_SQL_Running e também Slave_IO_State para se certificar que a replicação não tem algum erro.

Vamos consultar o servidor mysql03

mysql -u root -p
[...]
mysql> show slave status\G
*************************** 1. row  ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.101.0.28
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysqld-bin.000001
             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: 106
              Relay_Log_Space: 408
              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: 
1 row in set (0.00 sec)

Observe as linhas Slave_IO_Running e Slave_SQL_Running e também Slave_IO_State para se certificar que a replicação não tem algum erro.

Vamos consultar o servidor mysql03

mysql -u root -p
[...]
mysql> show slave status\G
*************************** 1. row  ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.101.0.25
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysqld-bin.000001
             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: 106
              Relay_Log_Space: 408
              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: 
1 row in set (0.00 sec)

Observe as linhas Slave_IO_Running e Slave_SQL_Running e também Slave_IO_State para se certificar que a replicação não tem algum erro.

Neste ponto a replicação já foi iniciada. Caso não esteja, veja o log do MySQL de cada slave

Para subir o serviço de slave no MySQL podemos executar o seguinte comando

mysql> start slave;

Agora vamos fazer testes.

No servidor MySQL01

mysql -u root -p
[...]
mysql> CREATE DATABASE mysql01;

Agora em cada um dos outros servidores temos que obter as seguinte saída em cada servidor.

mysql -u root -p
[...] 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql01            |
+--------------------+
3 rows in set (0.00 sec)

No servidor MySQL02.

mysql -u root -p
[...]
mysql> CREATE DATABASE mysql02;

Agora vamos listar novamente em cada servidor temos que ter a mesma saída.

mysql -u root -p
[...]
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql01            |
| mysql02            |
+--------------------+
4 rows in set (0.00 sec)

No servidor MySQL03.

mysql -u root -p
[...]
mysql> CREATE DATABASE mysql03;

Agora vamos listar novamente em cada servidor temos que ter a mesma saída.

mysql -u root -p
[...]
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql01            |
| mysql02            |
| mysql03            |
+--------------------+
5 rows in set (0.00 sec)

No servidor MySQL04.

mysql> CREATE DATABASE mysql04;

Agora vamos listar novamente em cada servidor temos que ter a mesma saída.

mysql -u root -p
[...]
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql01            |
| mysql02            |
| mysql03            |
| mysql04            |
+--------------------+
6 rows in set (0.00 sec)

Agora é so ir acompanhando logs caso algum erro apareça.

Aqui estamos fazendo a replicação de um banco de dados zerado, caso tenha que fazer a replicação de um banco com dados, faça um dump do servidor com os dados e importe em todos os outros e depois configure a replicação por que senão teremos vários erros com PK e FK.

Referências