Differences

This shows you the differences between two versions of the page.

Link to this comparison view

replicacao_de_mysql_master_master_pt_br [2017/09/05 12:18] (current)
Line 1: Line 1:
 +====== Replicação MySQL Master/​Master ======
 +
 +
 +Replicação de base de dados é a capacidade das bases de dados num servidor <​nowiki>​MySQL</​nowiki>​ 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 <​nowiki>​MySQL</​nowiki>​ com Replicação em cadeia/​anel. Utilizada, por exemplo, em estrutura de dados geograficamente distribuídos.
 +
 +O <​nowiki>​MySQL</​nowiki>​ 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 <​nowiki>​FreeBSD</​nowiki>/<​nowiki>​OpenBSD</​nowiki>/​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. <​nowiki>​MySQL</​nowiki>​**:​ 5.1
 +
 +  *  **Hostname**:​ mysql02
 +   ​* ​ **IP address**: 10.101.0.26/​24
 +   ​* ​ **V. <​nowiki>​MySQL</​nowiki>​**:​ 5.1
 +
 +  *  **Hostname**:​ mysql03
 +   ​* ​ **IP address**: 10.101.0.27/​24
 +   ​* ​ **V. <​nowiki>​MySQL</​nowiki>​**:​ 5.1
 +
 +  *  **Hostname:​** mysql04
 +   ​* ​ **IP address:** 10.101.0.28/​24
 +   ​* ​ **V. <​nowiki>​MySQL</​nowiki>:​** 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 <​nowiki>​MySQL</​nowiki>​ no SO que deseja utilizar.
 +
 +<sxh bash>
 +aptitude update && aptitude install mysql-client-5.1 mysql-server-5.1 mysql-server-core-5.1 -y
 +</​sxh>​
 +
 +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
 +<sxh bash>
 +sed -i '​s/​127.0.0.1/​0.0.0.0/​g'​ /​etc/​mysql/​my.cnf
 +</​sxh>​
 +
 +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.
 +<sxh sql>
 +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
 +</​sxh>​
 +
 +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.
 +<sxh apache>
 +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
 +</​sxh>​
 +
 +Vamos configura o MySQL02.
 +<sxh apache>
 +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
 +</​sxh>​
 +
 +Vamos configurar o Mysql03.
 +<sxh apache>
 +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
 +</​sxh>​
 +
 +Vamos configurar o Mysql04.
 +<sxh apache>
 +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
 +</​sxh>​
 +
 +
 +Agora precisamos reiniciar o serviço do mysql nos 4 servidores:
 +<sxh bash>
 +/​etc/​init.d/​mysql restart
 +</​sxh>​
 +
 +Vamos consultar o status da replicação
 +
 +Vamos consultar o servidor mysql01
 +<sxh sql>
 +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)
 +</​sxh>​
 +
 +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
 +<sxh sql>
 +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)
 +</​sxh>​
 +
 +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
 +<sxh sql>
 +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)
 +</​sxh>​
 +
 +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
 +<sxh sql>
 +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)
 +</​sxh>​
 +
 +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 <​nowiki>​MySQL</​nowiki>​ de cada slave
 +
 +Para subir o serviço de slave no <​nowiki>​MySQL</​nowiki>​ podemos executar o seguinte comando
 +<sxh bash>
 +mysql> start slave;
 +</​sxh>​
 +
 +Agora vamos fazer testes.
 +
 +No servidor MySQL01
 +<sxh bash>
 +mysql -u root -p
 +[...]
 +mysql> CREATE DATABASE mysql01;
 +</​sxh>​
 +
 +Agora em cada um dos outros servidores temos que obter as seguinte saída em cada servidor.
 +<sxh sql>
 +mysql -u root -p
 +[...] 
 +mysql> show databases;
 ++--------------------+
 +| Database ​          |
 ++--------------------+
 +| information_schema |
 +| mysql              |
 +| mysql01 ​           |
 ++--------------------+
 +3 rows in set (0.00 sec)
 +</​sxh>​
 +
 +No servidor MySQL02.
 +<sxh sql>
 +mysql -u root -p
 +[...]
 +mysql> CREATE DATABASE mysql02;
 +</​sxh>​
 +
 +Agora vamos listar novamente em cada servidor temos que ter a mesma saída.
 +<sxh sql>
 +mysql -u root -p
 +[...]
 +mysql> SHOW DATABASES;
 ++--------------------+
 +| Database ​          |
 ++--------------------+
 +| information_schema |
 +| mysql              |
 +| mysql01 ​           |
 +| mysql02 ​           |
 ++--------------------+
 +4 rows in set (0.00 sec)
 +</​sxh>​
 +
 +
 +No servidor MySQL03.
 +<sxh sql>
 +mysql -u root -p
 +[...]
 +mysql> CREATE DATABASE mysql03;
 +</​sxh>​
 +
 +Agora vamos listar novamente em cada servidor temos que ter a mesma saída.
 +<sxh sql>
 +mysql -u root -p
 +[...]
 +mysql> show databases;
 ++--------------------+
 +| Database ​          |
 ++--------------------+
 +| information_schema |
 +| mysql              |
 +| mysql01 ​           |
 +| mysql02 ​           |
 +| mysql03 ​           |
 ++--------------------+
 +5 rows in set (0.00 sec)
 +</​sxh>​
 +
 +No servidor MySQL04.
 +<sxh sql>
 +mysql> CREATE DATABASE mysql04;
 +</​sxh>​
 +
 +Agora vamos listar novamente em cada servidor temos que ter a mesma saída.
 +<sxh sql>
 +mysql -u root -p
 +[...]
 +mysql> show databases;
 ++--------------------+
 +| Database ​          |
 ++--------------------+
 +| information_schema |
 +| mysql              |
 +| mysql01 ​           |
 +| mysql02 ​           |
 +| mysql03 ​           |
 +| mysql04 ​           |
 ++--------------------+
 +6 rows in set (0.00 sec)
 +</​sxh>​
 +
 +
 +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.
 +
 +<WRAP center round tip 60%>
 +  * [[http://​wiki.douglasqsantos.com.br/​doku.php/​backup_banco_de_dados_mysql_pt_br| Exportando a Base Mysql separando estrutura de dados]]
 +  * [[http://​wiki.douglasqsantos.com.br/​doku.php/​importar_banco_de_dados_mysql_pt_br | Importar a Base Mysql separando estrutura de dados]]
 +</​WRAP>​
 +
 +
 +====== Referências ======
 +  - https://​dev.mysql.com/​doc/​refman/​5.5/​en/​replication.html
 +  - https://​dev.mysql.com/​doc/​refman/​5.5/​en/​replication-howto.html