{"id":347,"date":"2015-11-25T18:24:57","date_gmt":"2015-11-25T18:24:57","guid":{"rendered":"http:\/\/arripio.com.br\/?p=347"},"modified":"2018-07-19T19:40:08","modified_gmt":"2018-07-19T19:40:08","slug":"instalacao-e-configuracao-de-alta-disponibilidade-com-replicacao-dos-dados-no-sgbd-percona-server-5-6-26-utilizando-percona-xtrabackup-e-o-keepalived","status":"publish","type":"post","link":"https:\/\/arripio.com.br\/?p=347","title":{"rendered":"Replica\u00e7\u00e3o – Percona – Xtrabackup – keepalived."},"content":{"rendered":"

Autor: Dauro Lima Sobrinho<\/p>\n

Este tutorial tem como objetivo auxiliar a instala\u00e7\u00e3o e configura\u00e7\u00e3o de uma solu\u00e7\u00e3o de alta disponibilidade com replica\u00e7\u00e3o dos dados no SGBD Percona Server 5.6.26, utilizando Percona Xtrabackup e o keepalived.<\/p>\n

Distribui\u00e7\u00e3o: CentOS 6.7
\nNode 1: 10.1.31.70
\nNode 2: 10.1.31.71<\/p>\n

1\u00ba Passo: Instalar o reposit\u00f3rio Percona.
\nyum install http:\/\/www.percona.com\/downloads\/percona-release\/redhat\/0.1-3\/percona-release-0.1-3.noarch.rpm<\/code><\/p>\n

2\u00ba Passo: Instalar o SGBD Percona.
\nyum install Percona-XtraDB-Cluster-56.x86_64 percona-xtrabackup.x86_64\u00a0galera.x86_64
\n<\/code><\/p>\n

3\u00ba Passo: Inicializar o servi\u00e7o do SGBD Percona.<\/p>\n

4\u00ba Passo: Criar usu\u00e1rio para a replica\u00e7\u00e3o.
\nCREATE USER '[USER_NAME]'@'localhost' IDENTIFIED BY '[PASSWORD]';<\/code><\/p>\n

GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘[USER_NAME]’@’localhost’;<\/p>\n

flush privileges;<\/p>\n

5\u00ba Passo: Configurar o my.cnf dos servidores.
\nMASTER: 10.1.31.70
\n[mysqld]
\ndatadir=\/var\/lib\/mysql
\nsocket=\/var\/lib\/mysql\/mysql.sock
\nuser=mysql
\n# Disabling symbolic-links is recommended to prevent assorted security risks
\nsymbolic-links=0 <\/code><\/p>\n

wsrep_provider=\/usr\/lib64\/libgalera_smm.so
\nwsrep_cluster_address=gcomm:\/\/10.1.31.71
\nbinlog_format=ROW
\ndefault_storage_engine=InnoDB
\ninnodb_locks_unsafe_for_binlog=1
\ninnodb_autoinc_lock_mode=2
\nwsrep_node_address= 10.1.31.70
\nwsrep_sst_method=xtrabackup-v2
\nwsrep_cluster_name=[CLUSTER_NAME]
\nwsrep_sst_auth=”[USER_NAME]:[PASSWORD]” —– Criados no 4\u00ba Passo
\nwsrep_provider_options=”gcache.size=1G;”
\nwsrep_slave_threads=16
\nwsrep_causal_reads=ON
\nwsrep_replicate_myisam=1
\nwsrep_node_name=2<\/p>\n

[mysqld_safe]
\nlog-error=\/var\/log\/mysqld.log
\npid-file=\/var\/run\/mysqld\/mysqld.pid
\nwsrep_url=gcomm:\/\/10.1.31.70,gcomm:\/\/10.1.31.71<\/code><\/p>\n

SLAVE: 10.1.31.71
\n[mysqld]
\ndatadir=\/var\/lib\/mysql
\nsocket=\/var\/lib\/mysql\/mysql.sock
\nuser=mysql
\n# Disabling symbolic-links is recommended to prevent assorted security risks
\nsymbolic-links=0 <\/code><\/p>\n

wsrep_provider=\/usr\/lib64\/libgalera_smm.so
\nwsrep_cluster_address=gcomm:\/\/10.1.31.70
\nbinlog_format=ROW
\ndefault_storage_engine=InnoDB
\ninnodb_locks_unsafe_for_binlog=1
\ninnodb_autoinc_lock_mode=2
\nwsrep_node_address= 10.1.31.71
\nwsrep_sst_method=xtrabackup-v2
\nwsrep_cluster_name=[CLUSTER_NAME]
\nwsrep_sst_auth=”[USER_NAME]:[PASSWORD]” —– Criados no 4\u00ba Passo
\nwsrep_provider_options=”gcache.size=1G;”
\nwsrep_slave_threads=16
\nwsrep_causal_reads=ON
\nwsrep_replicate_myisam=1
\nwsrep_node_name=1<\/p>\n

[mysqld_safe]
\nlog-error=\/var\/log\/mysqld.log
\npid-file=\/var\/run\/mysqld\/mysqld.pid
\nwsrep_url=gcomm:\/\/10.1.31.70,gcomm:\/\/10.1.31.71<\/code><\/p>\n

6\u00ba Passo: Subir o servi\u00e7o do SGBD Percona.
\n\/etc\/init.d\/mysql start<\/code><\/p>\n

7\u00ba Passo: Verificar se todos os n\u00f3s est\u00e3o ativos.
\nshow status like “wsrep_cluster_size%”;
\n+——————–+——-+
\n| Variable_name | Value |
\n+——————–+——-+
\n| wsrep_cluster_size | 2 |
\n+——————–+——-+<\/p>\n

8\u00ba Passo: Testar.<\/p>\n

Crie databases em ambos os nodes e veja se ambos compartilham os DBs criados.<\/p>\n

Pare o servi\u00e7o do MySQL de um dos nodes. No node com servi\u00e7o ativo, crie algumas databases e insira conte\u00fado, logo em seguida, ative o servi\u00e7o do node parado e veja se o conte\u00fado criado ser\u00e1 compartilhado (fa\u00e7a o procedimento com ambos os nodes para um teste mais completo).<\/p>\n

Fa\u00e7a um reboot em um dos nodes, insira conte\u00fado no node ativo, logo em seguida, acesse novamente o node parado e veja se o conte\u00fado foi compartilhado.<\/p>\n

Alguns comandos \u00fateis: CREATE DATABASE PERCONA; USE PERCONA; CREATE TABLE tabela (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100)); INSERT INTO tabela(data) VALUES (‘$a’);<\/p>\n

9\u00ba Passo: Instalar o servi\u00e7o do keepalived.
\nyum install keepalived.x86_64<\/code><\/p>\n

10\u00ba Passo: Criar uma interface com VIP.
\nvi \/etc\/sysconfig\/network-scripts\/eth0\\:0<\/code>
\nDEVICE=eth0:0
\nTYPE=Ethernet
\nONBOOT=yes
\nIPV6INIT=no
\nNM_CONTROLLED=no
\nBOOTPROTO=none
\nIPADDR=10.1.31.72
\nPREFIX=24
\nGATEWAY=10.1.31.1<\/p>\n

11\u00ba Passo: Configurar o servi\u00e7o keepalived.
\nvi \/etc\/keepalived\/keepalived.conf <\/code>
\nMASTER: 10.1.31.70
\nglobal_defs {
\nnotification_email {
\n[EMAIL]@[DOMAIN]
\n}
\nnotification_email_from [EMAIL]@[DOMAIN]
\nsmtp_server [RELAY_SERVER]
\nsmtp_connect_timeout 60
\nrouter_id [ROUTER_NAME]
\n}
\nvrrp_script chk_pxc {
\nscript \"\/usr\/bin\/clustercheck clustercheck password 0\"
\ninterval 1
\n}
\nvrrp_instance PXC {
\ninterface eth0
\nstate MASTER
\nvirtual_router_id 51
\npriority 100
\nadvert_int 1
\ngarp_master_delay 5<\/code><\/p>\n

authentication {
\nauth_type PASS
\nauth_pass 1111
\n}
\nvirtual_ipaddress {
\n10.1.31.72 dev eth0
\n}
\ntrack_script {
\nchk_pxc
\n}
\nnotify_master “\/bin\/echo ‘now master’ > \/tmp\/keepalived.state”
\nnotify_backup “\/bin\/echo ‘now backup’ > \/tmp\/keepalived.state”
\nnotify_fault “\/bin\/echo ‘now fault’ > \/tmp\/keepalived.state”
\n}<\/p>\n

SLAVE: 10.1.31.71
\nglobal_defs {
\nnotification_email {
\n[EMAIL]@[DOMAIN]
\n}
\nnotification_email_from [EMAIL]@[DOMAIN]
\nsmtp_server [RELAY_SERVER]
\nsmtp_connect_timeout 60
\nrouter_id [ROUTER_NAME]
\n}
\nvrrp_script chk_pxc {
\nscript \"\/usr\/bin\/clustercheck clustercheck password 0\"
\ninterval 1
\n}
\nvrrp_instance PXC {
\ninterface eth0
\nstate SLAVE
\nvirtual_router_id 51
\npriority 99
\nadvert_int 1
\ngarp_master_delay 5<\/code><\/p>\n

authentication {
\nauth_type PASS
\nauth_pass 1111
\n}
\nvirtual_ipaddress {
\n10.1.31.72 dev eth0
\n}
\ntrack_script {
\nchk_pxc
\n}
\nnotify_master “\/bin\/echo ‘now master’ > \/tmp\/keepalived.state”
\nnotify_backup “\/bin\/echo ‘now backup’ > \/tmp\/keepalived.state”
\nnotify_fault “\/bin\/echo ‘now fault’ > \/tmp\/keepalived.state”
\n}<\/p>\n

12\u00ba Passo: Reiniciar todos os servi\u00e7os
\nSLAVE: 10.1.31.71
\n\/etc\/init.d\/network restart
\n\/etc\/init.d\/mysql restart
\n\/etc\/init.d\/keepalived restart<\/code><\/p>\n

MASTER: 10.1.31.70
\n\/etc\/init.d\/network restart
\n\/etc\/init.d\/mysql restart
\n\/etc\/init.d\/keepalived restart<\/code><\/p>\n

13\u00ba Passo: Verificar status dos servidores
\ncat \/tmp\/keepalived.state
\nip add<\/p>\n

Observa\u00e7\u00e3o:<\/strong><\/p>\n

Em caso de indisponibilidade dos dois servidores:
\nSer\u00e1 necess\u00e1rio alterar o par\u00e2metro wsrep_cluster_address do servidor 10.1.31.70 para wsrep_cluster_address=gcomm:\/\/.
\nEm seguida, subir o servi\u00e7o dos dois servidores (10.1.31.70 e 10.1.31.71).
\nPor \u00faltimo, voltar as configura\u00e7\u00f5es (wsrep_cluster_address=gcomm:\/\/10.1.31.71) do servidor 10.1.31.70 e reiniciar o servi\u00e7o.<\/p>\n

Fontes:
\nhttp:\/\/www.vivaolinux.com.br\/dica\/Replicacao-de-Banco-de-Dados-MySQL-com-Percona-XtraDB<\/p>\n

Using keepalived for HA on top of Percona XtraDB Cluster<\/a><\/p><\/blockquote>\n