Replicação de Dados Utilizando MySQL

Observações Gerais

Ø Configuração de replicação de dados Master-Master, visando à sincronia de bases de dados fisicamente separadas.

Ø A configuração desta Replicação de Dados foi feita no Sistema Operacional Windows XP.

Ø Foi utilizado o MySQL versão 5.01.23.

Ø Os testes foram realizados utilizando-se dois computadores na mesma rede.

Pré-requisitos

Ø A base de dados que será replicada deve estar exatamente igual nas duas máquinas. Para maior segurança, pode-se fazer o dump (backup) em uma das máquinas e restaurá-lo na outra.

Ø Ter instalado nas máquinas o MySQL (versão 5.0 ou superior¹);

Notas

Para este tutorial foi feita a replicação utilizando-se duas máquinas, a máquina 1 será Master(1) da máquina 2 – Slave(1), e a máquina 2 será Master(2) da máquina 1 – Slave(2). Teremos então a seguinte configuração:

Máquina 1 = Master1/Slave2

Máquina 2 = Master2/Slave1

Configurações

1º Passo: Pare o serviço do MySQL e adicione as seguintes linhas no arquivo my.ini da Máquina 1 (Master 1 e Slave 2), dentro de [mysqld].

OBS.: O arquivo my.ini está localizado na pasta C:\Arquivos de programas\MySQL\MySQL Server 5.1

#Configurações da Máquina 1

#Id da máquina 1 [deve ser um número único para cada máquina]

server-id =

#Ip do máster [IP da máquina que funcionará como máster desta máquina, neste caso o ip da máquina 2]

master-host =

#Usuario usado para login no máster, neste caso foi usado o usuário “replication”

master-user = replication

#Senha usada para login no máster, neste caso foi usada a senha “slave”

master-password = slave

#Porta utilizada para a comunicação com o máster [3306 é a porta default definida pelo mysql]

master-port = 3306

#Configurações do Log

#Ativa o log binário [é neste log que são salvas as alterações feitas na base de dados]

log-bin =

#Nome da base que será replicada, caso exista mais de uma, colocar os nomes separados por vírgula

binlog-do-db =

#Ativa o log de erros [Nome do log de Erro]

log-error=

#Gera um arquivo de log para os dados alterados no slave

log-slave-updates

#Log de vigilância - para onde são copiados os logs binários gerados pelo master e onde o slave fica a espera de #mudanças [Nome do Log]

relay-log=relay-log

#Define o local e o nome usado para o arquivo que mantém atualizada a lista de relay log. [Nome do Log]

relay-log-index=relay-log-index

#Local e nome para saber onde a Thread MySQL está, na leitura do log. [Nome do Log]

relay-log-info-file=relay-log-info

#Configuração para manter as bases em sincronia mesmo em caso de queda

sync_binlog=1

#Seleciona a base de dados que será replicada [Nome da base de dados que será replicada]

replicate-do-db=

#Nome do host ou IP a ser informado ao master durante o registro da máquina slave

report-host=

#Usuario Slave usado para registro

report-user=replication

#Senha Slave usado para registro

report-password=slave

#Porta usada para registro do slave no master

report-port=3306

#Fim da Configuração Master-Slave na máquina 1

2º Passo: Adicionar as seguintes linhas no arquivo my.ini do Master 1, após [mysqld].

[mysql.server]

user=mysql

basedir="C:/Arquivos de programas/MySQL/MySQL Server 5.1/"

[mysql_safe]

err-log="C:/Arquivos de programas/MySQL/MySQL Server 5.1/data"

pid-file="C:/Arquivos de programas/MySQL/MySQL Server 5.1/data/adrielle.pid"

3º Passo: Repetir o Passo 1 e 2 para a Máquina 2( Master 2 e Slave1).

4º Passo:

OBS.: Os passos abaixo devem ser executados nas duas máquinas.

Ø Inicie o serviço do MySQL

Na primeira vez que replicação for configurada no computador é necessário conceder permissão de acesso à máquina que será slave.

Ø Acesse o MySQL Command Line Client (Iniciar>Programas>MySQL>MySQL Server 5.1> MySQL Command Line Client ):

§ Digite a senha para acesso ao MySQL (Senha utilizada na instalação do MySQL).

§ Digite o seguinte comando:

grant replication slave on *.* to ‘’@’’ identified by ‘’;

Onde, deve ser o mesmo informado em “master_user” no arquivo my.ini, no nosso caso será replication e é o ip do host que será o slave. O pode também ser preenchido com ‘%’, liberando o aceso para todos os hosts que possuem estes mesmos usuário e senha.

Ø Reiniciar o serviço do MySQL.

5º Passo: Acesse o MySQL Command Line Client (Iniciar>Programas>MySQL>MySQL Server 5.1> MySQL Command Line Client ):

Ø Digite o seguinte comando:

show slave status\G;

[Este comando retorna o status da conexão com o master]

O status para que a replicação esteja funcionando corretamente deve ser o seguinte:

*************************** 1. row ***************************

1 Slave_IO_State: Waiting for master to send event

2 Master_Host: 192.168.1.X

3 Master_User: replication

4 Master_Port: 3306

5 Connect_Retry: 60

6 Master_Log_File: log-bin.000003

7 Read_Master_Log_Pos: 106

8 Relay_Log_File: relay-log.000083

9 Relay_Log_Pos: 245

10 Relay_Master_Log_File: relay-log-info.000003

11 Slave_IO_Running: Yes

12 Slave_SQL_Running: Yes

13 Replicate_Do_DB: eptus

14 Replicate_Ignore_DB:

15 Replicate_Do_Table:

16 Replicate_Ignore_Table:

17 Replicate_Wild_Do_Table:

18 Replicate_Wild_Ignore_Table:

19 Last_Errno: 0

20 Last_Error:

21 Skip_Counter: 0

22 Exec_Master_Log_Pos: 106

23 Relay_Log_Space: 532

24 Until_Condition: None

25 Until_Log_File:

26 Until_Log_Pos: 0

27 Master_SSL_Allowed: No

28 Master_SSL_CA_File:

29 Master_SSL_CA_Path:

30 Master_SSL_Cert:

31 Master_SSL_Cipher:

32 Master_SSL_Key:

33 Seconds_Behind_Master: 0

34 Master_SSL_Verify_Server_Cert: No

35 Last_IO_Errno: 2003

36 Last_IO_Error: error connecting to master 'replication@192.168.1

.36:3306' - retry-time: 60 retries: 86400

37 Last_SQL_Errno: 0

38 Last_SQL_Error:

>> Na linha 1 'Wait for master to send event' indica que a conexão entre master e slave foi estabelecida e que o slave está aguardando por alguma atualização do log binário do master.

>> Na linha 2 'Master_Host: 192.168.1.36' indica o endereço ip do master.

>> Na linha 3 'Master_User: replication' indica o usuário master, o mesmo que foi configurado no arquivo my.ini

>> Na linha 6 'Master_Log_File: log-bin.000003' indica o nome do log binário no master.

>> Na linha 7 'Read_Master_Log_Pos: 106' indica a posição inicial do log binário no master.

>> Na linha 8 'Relay_Log_File: relaylog.000083' indica o número do log binário.

Para conseguir estas informções basta digitar o comando 'show master status' no usuário master.

>> Nas linhas 11 e 12 o status deve estar marcado como 'yes' para que a replicação funcione.

>> Na linha 13 'Replicate_Do_DB: eptus' indica a base que será replicada.

Caso a replicação não ocorra automaticamente [Se a linha 1 do status estiver vazia], é necessário verificar se no my.ini existe a linha 'skip-slave-start' [indica que o slave não deve ser iniciado automaticamente], se existir deve ser apagada. É necessário também executar o seguinte comando no MySQL Command Line Client (Iniciar>Programas>MySQL>MySQL Server 5.1> MySQL Command Line Client ):

start slave;

show slave status\G;

OBS: Caso deseje mudar o ip de uma das máquinas onde a replicação já tenha sido configurada é necessário alterar o IP no arquivo my.ini e executar os seguintes comandos no MySQL Command Line Client (Iniciar>Programas>MySQL>MySQL Server 5.1> MySQL Command Line Client ):

stop slave;

change master to master_host=, master_user=, master_password=;

Abaixo, segue um arquivo my.ini de exemplo(Contendo todas as linhas de configuração do master, as configurações geradas automaticamente pelo MySQL e também as linhas para configuração do Eptus):

#MyExemplo

[client]

#Define qual porta o mysql irá usar

port=3306

[mysql]

#Determina o conjunto de caracteres a ser utilizado

default-character-set=latin1

[mysqld]

#Configuração Master-Master

#Id do servidor [deve ser diferente para cada máquina]

server-id = 2

#Ip do master

master-host = 192.168.1.X

#Usuario usado para login no master

master-user = replication

#Senha usada para login no master

master-password = slave

#Porta utilizada para a comunicação com o master

master-port = 3306

#Ativa o log binario [por onde ocorre a replicação]

log-bin = NomeLog

#Nome da base que será gerasdo o log [ a base que será replicada]

binlog-do-db = BaseDeDados

#Ativa o log de erros. Nome do log de Erro

log-error=erro-log

#Gera um arquivo de log para os dados alterados no slave

log-slave-updates

#Configuração para manter as bases em sincronia, mesmo em caso de queda

sync_binlog=1

#Seleciona a base de dados que será replicada

replicate-do-db=eptus

#Log de vigilância - para onde são copiados os logs binarios gerados pelo master e onde o slave fica a espera de #mudanças

relay-log=relay-log

#Define o local e o nome usado para o arquivo que mantém atualizada a lista de relay log.

relay-log-index=relay-log-index

#Local e nome para saber onde a Thread MySQL está, na leitura do log.

relay-log-info-file=relay-log-info

#Nome do host ou IP a ser informado ao master durante o registro da máquina slave

report-host=192.168.1.36

#Usuario Slave usado para registro

report-user=replication

#Senha Slave usado para registro

report-password=slave

#Porta usada para registro do slave no master

report-port=3306

#Fim da Configuração Master-Master

# Eptus Corporation settings.

key_buffer=312M

max_allowed_packet=128M

max_heap_table_size=64M

max_connections=10000

connect_timeout=300

# Porta a ser utilizada

port=3306

#Define o diretório da base de dados

basedir="C:/Arquivos de programas/MySQL/MySQL Server 5.1/"

#Define o diretório da base de dados

datadir="C:/Arquivos de programas/MySQL/MySQL Server 5.1/Data/"

socket="C:/Arquivos de programas/MySQL/MySQL Server 5.1/MySQL.Sock"

#Number of seconds to wait for more data from a master/slave connection before aborting the read

slave_net_timeout=300

# The default character set that will be used when a new schema or table is

# created and no character set is defined

default-character-set=latin1

#Esta configuração dependerá do formato de tabela que a aplicação utiliza [MyISAM ou INNODB]

# The default storage engine that will be used when create new tables when

default-storage-engine=MyISAM

# Set the SQL mode to strict

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will

# allow. One of these connections will be reserved for a user with

# SUPER privileges to allow the administrator to login even if the

# connection limit has been reached.

max_connections=100

# Query cache is used to cache SELECT results and later return them

# without actual executing the same query once again. Having the query

# cache enabled may result in significant speed improvements, if your

# have a lot of identical queries and rarely changing tables. See the

# "Qcache_lowmem_prunes" status variable to check if the current value

# is high enough for your load.

# Note: In case your tables change very often or if your queries are

# textually different every time, the query cache may result in a

# slowdown instead of a performance improvement.

query_cache_size=0

# The number of open tables for all threads. Increasing this value

# increases the number of file descriptors that mysqld requires.

# Therefore you have to make sure to set the amount of open files

# allowed to at least 4096 in the variable "open-files-limit" in

# section [mysqld_safe]

table_cache=256

# Maximum size for internal (in-memory) temporary tables. If a table

# grows larger than this value, it is automatically converted to disk

# based table This limitation is for a single table. There can be many

# of them.

tmp_table_size=17M

# How many threads we should keep in a cache for reuse. When a client

# disconnects, the client's threads are put in the cache if there aren't

# more than thread_cache_size threads from before. This greatly reduces

# the amount of thread creations needed if you have a lot of new

# connections. (Normally this doesn't give a notable performance

# improvement if you have a good thread implementation.)

thread_cache_size=8

#*** MyISAM Specific options

# The maximum size of the temporary file MySQL is allowed to use while

# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.

# If the file-size would be bigger than this, the index will be created

# through the key cache (which is slower).

myisam_max_sort_file_size=100G

# If the temporary file used for fast index creation would be bigger

# than using the key cache by the amount specified here, then prefer the

# key cache method. This is mainly used to force long character keys in

# large tables to use the slower key cache method to create the index.

myisam_sort_buffer_size=34M

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.

# Do not set it larger than 30% of your available memory, as some memory

# is also required by the OS to cache rows. Even if you're not using

# MyISAM tables, you should still set it to 8-64M as it will also be

# used for internal temporary disk tables.

key_buffer_size=25M

# Size of the buffer used for doing full table scans of MyISAM tables.

# Allocated per thread, if a full scan is needed.

read_buffer_size=64K

read_rnd_buffer_size=256K

# This buffer is allocated when MySQL needs to rebuild the index in

# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE

# into an empty table. It is allocated per thread so be careful with

# large settings.

sort_buffer_size=256K

#*** INNODB Specific options ***

# Use this option if you have a MySQL server with InnoDB support enabled

# but you do not plan to use it. This will save memory and disk space

# and speed up some things.

#skip-innodb

# Additional memory pool that is used by InnoDB to store metadata

# information. If InnoDB requires more memory for this purpose it will

# start to allocate it from the OS. As this is fast enough on most

# recent operating systems, you normally do not need to change this

# value. SHOW INNODB STATUS will display the current amount used.

innodb_additional_mem_pool_size=2M

# If set to 1, InnoDB will flush (fsync) the transaction logs to the

# disk at each commit, which offers full ACID behavior. If you are

# willing to compromise this safety, and you are running small

# transactions, you may set this to 0 or 2 to reduce disk I/O to the

# logs. Value 0 means that the log is only written to the log file and

# the log file flushed to disk approximately once per second. Value 2

# means the log is written to the log file at each commit, but the log

# file is only flushed to disk approximately once per second.

innodb_flush_log_at_trx_commit=1

# The size of the buffer InnoDB uses for buffering log data. As soon as

# it is full, InnoDB will have to flush it to disk. As it is flushed

# once per second anyway, it does not make sense to have it very large

# (even with long transactions).

innodb_log_buffer_size=1M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes andX

# row data. The bigger you set this the less disk I/O is needed to

# access data in tables. On a dedicated database server you may set this

# parameter up to 80% of the machine physical memory size. Do not set it

# too large, though, because competition of the physical memory may

# cause paging in the operating system. Note that on 32bit systems you

# might be limited to 2-3.5G of user level memory per process, so do not

# set it too high.

innodb_buffer_pool_size=47M

# Size of each log file in a log group. You should set the combined size

# of log files to about 25%-100% of your buffer pool size to avoid

# unneeded buffer pool flush activity on log file overwrite. However,

# note that a larger logfile size will increase the time needed for the

# recovery process.

innodb_log_file_size=24M

# Number of threads allowed inside the InnoDB kernel. The optimal value

# depends highly on the application, hardware as well as the OS

# scheduler properties. A too high value may lead to thread thrashing.

innodb_thread_concurrency=8

[mysql.server]

user=mysql

basedir="C:/Arquivos de programas/MySQL/MySQL Server 5.1/"

[mysql_safe]

err-log="C:/Arquivos de programas/MySQL/MySQL Server 5.1/data"

pid-file="C:/Arquivos de programas/MySQL/MySQL Server 5.1/data/adrielle.pid"

Créditos

Ø Equipe de desenvolvimento - Eptus Corporation / Cuiabá – MT.

o Adrielle Silva – adrielle@eptus.com.br

o Ana Karoline Queiroz Romeiro – karol@eptus.com.br

o Fernanda Bagini de Almeida – fernanda@eptus.com.br

Cuiabá, 28 de abril de 2009.