Replicação de servidores MySQL Master/Slave

Para que fazer uma replica da base de dados ?

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.

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 redirecionar 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.

Vamos tratar aqui nesse artigo a replicação de bases MySQL entre servidor Master para um Slave.

A configuração é bem simples como vai ser mostrado.

  1. Hostname: master
  2. IP address: 10.0.0.1/24
  3. V. MySQL: 5.1
  1. Hostname: slave
  2. IP address: 10.0.0.2/24
  3. V. MySQL: 5.1

OBS: Esta configuração tem que ser feita nos 2 servidores.

Vamos parar o servidor mysql

/etc/init.d/mysql stop

Agora vamos configurar o arquivo /etc/mysql/my.cnf

Temos que mudar o parametro bind-address = 127.0.0.1 para bind-address = 0.0.0.0

vim /etc/mysql/my.cnf
[...]
bind-address = 0.0.0.0

Esse parametro é utilizado para definir quem o servidor vai escutar, quando temos ele como 127.0.0.1 entao somente podemos ter conexoes locais, então liberamos ele em 0.0.0.0 que seria qualquer host e fazemos a segurança nas permissões.

Configuração do /etc/mysql/my.cnf para o servidor que vai trabalhar como MASTER.

vim /etc/mysql/my.cnf
[...]
[mysqld]
server-id  = 1
log_bin      = /var/log/mysql/mysql-bind.log

Agora já podemos iniciar o serviço do servidor MASTER.

Configuração do /etc/mysql/my.cnf para o servidor que vai trabalhar como SLAVE

vim /etc/mysql/my.cnf
[...]
[mysqld]
server-id  = 2
log_bin   = /var/log/mysql-bind.log

Agora já podemos iniciar o serviõ do servidor SLAVE.

Essa configuração é feita no servidor MASTER.

Agora vamos criar o usuário que vai ser utilizado para replicação.

Aqui estou criando um usuário chamado replica e ele pode se logar neste servidor de qualquer outro, o que podemos fazer tambem é ao invez de informar que usuário pode se logar de qualquer lugar como no exemplo: replica@'%', o sinal de % significa qualquer host então podemos informar o ip do servidor slave exemplo: replica@'10.0.0.2', assim garantimos que o usuário replica só pode se logar neste servidor se ele vier do ip 10.0.0.2.

Vamos logar no mysql e criar o nosso usuário.

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.56-0.dotdeb.1-log (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

Agora vamos criar o usuário que vai ser utilizado para a replicação

mysql> GRANT REPLICATION SLAVE,SUPER,RELOAD ON *.* TO replica@'%' IDENTIFIED BY 'senha';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Agora precisamos dos valores do servidor que vai ser o master da replicação

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

mysql> quit
Bye

Agora vamos configurar o nosso servidor SLAVE troque os valores em vermelhos pelos que você definiu.

O valor de MASTER_LOG_FILE é o valor do File que é mostrado em SHOW MASTER STATUS no servidor MASTER e o MASTER_LOG_POS é o valor retirado do mesmo comando so que no campo Position.

Vamos logar no MySQL do servidor Slave

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.56-0.dotdeb.1-log (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

Agora vamos inserir os valores referentes ao servidor Master

mysql> CHANGE MASTER TO
 -> MASTER_HOST='10.0.0.2',
 -> MASTER_USER='replica',
 -> MASTER_PASSWORD='senha',
 -> MASTER_LOG_FILE='mysql-bin.000001',
 -> MASTER_LOG_POS=347;
Query OK, 0 rows affected (0.04 sec)

Agora vamos iniciar o servidor de Slave.

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

Agora precisamos verificar o status da replicação

mysql> SHOW SLAVE STATUS\G
**************************************** 1. row ****************************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.0.0.2
 Master_User: replica
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 347
 Relay_Log_File: mysqld-relay-bin.000002
 Relay_Log_Pos: 251
 Relay_Master_Log_File: mysql-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: 347
 Relay_Log_Space: 407
 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)

mysql> quit
Bye

Pode ser notado que não temos nenhum erro em nossa replicação senão teriamos aviso em Last_SQL_Error na saída do comando SHOW SLAVE STATUS\G.

Agora vamos fazer alguns testes.

Logue no servidor MASTER e digite os comandos abaixo.

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.1.56-0.dotdeb.1-log (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> 

Podemos notar que temos somente 2 tabelas no servidor MASTER.

Agora vamos logar no servidor SLAVE.

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.1.56-0.dotdeb.1-log (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> 

No servidor SLAVE temos tambem somente 2 tabelas, agora podemos fazer os nossos testes.

No servidor MASTER agora.

mysql> create database douglas;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| douglas            |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

Agora vamos ver se no servidor SLAVE ja foi replicada a nossa tabela.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| douglas            |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

Podemos notar que a nossa tabela foi replicada do MASTER para o SLAVE.

OBS: Se criarmos qualquer coisa no servidor MASTER vai ser replicado para o servidor SLAVE porém se criarmos algo diretamente no servidor SLAVE não vai ser replicado para o servidor MASTER, com isso teriamos que fazer um backup dos dados que foram inseridos no servidor SLAVE e importar no servidor MASTER.

Referências