Servidores

25 de março de 2013

Replicação MySQL

603.487

Índice de Conteúdo

Preparando a replicação MySQL
Usuário da replicação
Replicação MySQL Master / Slave
- Iniciando a replicação
Verificação
Replicação MySQL Master / Master
FAQ - Dúvidas comuns
- Como obter as informações do master em um slave?
- Como replicar apenas algumas tabelas?
Referências

Tem quem goste, tem quem não goste, mas uma coisa é certa: o MySQL é hoje um dos bancos de dados mais utilizados em sistemas Web. Se você é um dos muitos que usam, com certeza deve se preocupar bastante com os dados que estão dentro do banco. A última coisa que queremos é perder os dados, né? :-) Este tutorial mostra um pouco sobre como podemos nos precaver e manter o servidor MySQL funcionando de forma correta e com mais segurança.

A primeira forma de se manter uma segurança adicional do serviço funcionando e de seus dados é a replicação MySQL. A replicação é utilizada para que caso um servidor caia (e se machuque), o HD exploda ou algo parecido, haverá um segundo servidor com todos os dados atualizados e pronto para o uso. Sites sérios que precisam se manter sempre funcionando, com o mínimo de downtime quando algo falha devem utilizar sempre uma forma de replicação.

Mas a replicação em si, sozinha, não basta. Algumas pessoas pensam que só por fazer uma replicação e ter dois servidores funcionando e com os dados, estão seguros. Isto é errado. A replicação só é útil para falhas de hardware, rede, tempestades, furacões e esse tipo de coisa. Se esquecem do mais acontece: erros humanos, erros de sistema, invasões, e tudo mais que prejudica o banco de dados mesmo ele funcionando. Se por exemplo o famoso estagiário apagar uma tabela em um servidor, a replicação vai apagar a mesma tabela no outro servidor! E para esse tipo de problema, só os backups salvam! :-)

Este tutorial cobre a parte de replicação no MySQL.

A replicação MySQL funciona sincronizando os dados entre dois ou mais servidores, em tempo real (ou mais ou menos isso). Em outras palavras, se um usuário criar ou atualizar algum dado em um banco de dados, o outro servidor puxa esse comando e executa nele também. É importante notar que o comando só é feito quando se cria ou atualiza algum dado, pois sincronizar uma leitura não faria o menor sentido…

Existem duas formas de se fazer uma replicação MySQL: master-slave e master-master.

Chamamos de servidor master o principal, aquele em que executamos comandos de criação e modificação. Chamamos de servidor slave aquele que puxa os dados do servidor principal para deixar os dados sincronizados. No modelo master-slave, só podemos executar comandos de escrita e atualização no master, enquanto podemos executar consultas tanto no master quanto no slave.

O modelo master-slave geralmente é o mais utilizado por ser simples de configurar e trabalhar. Seu uso também pode ocorrer em sites que tem muito movimento e que fazem pouca escrita e atualização. Com um master, podemos replicar os dados para vários slaves e distribuir as consultas para os vários slaves. Se um site tem centenas ou milhares de consultas por segundo, fica interessante balancear estas consultas em todos os slaves.

A figura a seguir mostra como funciona a replicação MySQL master-slave:

Preparando a replicação MySQL

Antes de mais nada, você vai precisar confirmar que a replicação pode ser usada em seu servidor master. Depois de instalado o MySQL, dê uma olhada na configuração de logs binários dele, entrando no prompt e executando:

mysql> SHOW GLOBAL VARIABLES WHERE Variable_name = 'log_bin';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.02 sec)

…que no meu caso está desligado. Os logs binários são um registro das ações de modificações de dados em um banco. Esses logs binários são necessários na replicação pois o servidor slave puxa as linhas desses logs binários para executar nele também. Para ligar os logs binários, edite o arquivo my.cnf (no RedHat/Fedora está em /etc, no Debian/Ubuntu está em /etc/mysql) e adicione a seguinte linha após a seção [mysqld]:

# Localização do arquivo do log binário
log-bin=/var/lib/mysql/mysql-bin.log

# Número de identificação do servidor. O banco de dados
# Master é o 1, seguido dos Slaves a partir de 2.
server-id=1

Atenção: a variável global log_bin só pode ser recarregada reiniciando o MySQL. Antes de continuar com os passos, reinicie o serviço.

Ao reiniciar o MySQL, note que ele criou os seguintes arquivos:

/var/lib/mysql/mysql-bin.index
/var/lib/mysql/mysql-bin.000001

O arquivo mysql-bin.index contém uma lista dos logs binários disponíveis. Já os arquivos numerados mysql-bin.NNNNNN (onde N é um número sequencial) contém os logs binários. Se os arquivos foram criados, é porque está funcionando. Experimente também rodar o comando anterior (SHOW GLOBAL VARIABLES […]) para ver a opção ativa.

Note também que no arquivo my.cnf eu defini a identificação do server (server-id) como 1. A identificação é muito importante e faz parte do controle da replicação. Você nunca poderá ter dois servidores com o mesmo ID se comunicando).

Usuário da replicação

Como vimos anteriormente, o servidor slave tem que se conectar ao servidor master para puxar os dados para replicação. Essa conexão é feita através da porta TCP do MySQL, assim como qualquer outra conexão. Em outras palavras, uma conexão entre servidores master e slave é como qualquer outra conexão de qualquer sistema. A diferença é que o usuário e senha que serão utilizados pelo slave terão permissões especiais para puxar todos os dados dos logs binários.

Sendo assim, entre no servidor MySQL Master e crie esse usuário:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.2' IDENTIFIED BY 'senhasecreta';
Query OK, 0 rows affected (0.01 sec)

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

Nota: no primeiro comando, é importante notar que demos o tipo de permissão REPLICATION SLAVE em todo o banco de dados (*.*) para o usuário. Não é possível dar permissões de replicação para um ou outro banco de dados apenas, tem que ser tudo. Veja também que o usuário criado foi o [email protected], ou seja, o nome do usuário é replication e ele só pode se conectar vindo do IP 192.168.0.2.

Replicação MySQL Master / Slave

Para começar a implementar a replicação, façamos a primeira pergunta: seu banco de dados master é novo ou já está sendo usado?

Antes de começar a replicar de um servidor para outro, é necessário deixar os dois bancos de dados iguais. Se o seu banco de dados é novo, vazio, então o slave também estará vazio e essa sincronização especial não é necessária. Por outro lado, e como é na maioria das vezes, se já existir alguma coisa no banco de dados master, você precisará pegar os dados de um e colocar no outro para ficarem iguais.

Ainda mais quando o servidor já está funcionando e você não pode pará-lo… A técnica aqui consiste primeiro em obter um LOCK em todas as tabelas e fazer um backup de todos os bancos de dados. Durante o backup, todo mundo que estiver conectado no servidor poderá ler as tabelas e linhas, mas todas as inserções e atualizações vão ficar paradas até o backup acabar. Em outras palavras, dependendo do tempo do backup, um sistema pode ficar lento, esperando escrever nas tabelas.

Antes de começar, entre no servidor MySQL Master e execute:

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      602 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Se o servidor está em atividade, executar esse comando novamente vai mostrar que o arquivo e posição estão aumentando. Estes dados correspondem a posição atual do log binário e vão ser necessários para o slave saber de onde começar a replicação.

Agora abra um outro terminal e execute o comando de backup:

servidor-master$ mysqldump -u root -p --all-databases --master-data=1 > backup-master.sql
Password: <suasenhasecreta>

Fazendo isso, praticamente não existirá downtime e os sistemas que usam o banco de dados serão afetados o mínimo possível. O backup então é guardado no arquivo backup-master.sql.

Explicando as opções do comando mysqldump, o –all-databases diz para fazer um backup de todos os bancos de dados existentes no servidor, o que é perfeito para nossa cópia exata para o slave. Agora, importante é o parâmetro –master-data=1. Este parâmetro faz com que o comando obtenha um LOCK de todas as tabelas do servidor (como expliquei antes) e escreva dentro do arquivo de backup a posição do log binário (o mesmo mostrado pelo SHOW MASTER STATUS).

Dica: Você pode fazer o backup e compactar ao mesmo tempo com o comando:

servidor-master$ mysqldump -u root -p --all-databases --master-data=1 | gzip -c > backup-master.sql.gz

Dica: Se todo o seu banco de dados for do tipo InnoDB, você não precisa aplicar um LOCK nas tabelas. Basta adicionar o parâmetro –single-transaction ao comando mysqldump. Como o InnoDB é transacional, o –single-transaction faz o backup precisar do LOCK apenas no começo, ao iniciar a transação.

Agora vá ao servidor slave e restaure o banco de dados com o comando:

servidor-slave$ mysql -u root -p < backup-master.sql

(Ou se tem um arquivo compactado com gzip):

servidor-slave$ zcat backup-master.sql.gz | mysql -u root -p

Feito isso, o servidor slave está pronto para ficar sincronizado com o master e começar a replicação.

Iniciando a replicação

Verifique primeiro se no my.cnf o parâmetro server-id está diferente do master. Se o master é 1, coloque o server-id igual a 2 no slave.

Agora é hora de configurar a replicação. Primeiro de tudo, veja qual o arquivo e posição do backup, no começo do arquivo:

$ head -n 100 backup-master.sql | grep "^CHANGE MASTER"

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=602;

Iremos usar esse mesmo comando, mas com informações a mais. Entre no prompt MySQL Slave e execute:

mysql> CHANGE MASTER TO
       MASTER_HOST='192.168.0.1',
       MASTER_USER='replication',
       MASTER_PASSWORD='senhasecreta',
       MASTER_LOG_FILE='mysql-bin.000005',
       MASTER_LOG_POS=602;

MASTER_HOST: O IP do servidor master, que aqui é 192.168.0.1;
MASTER_USER: O usuário slave para replicação. Criamos anteriormente com o GRANT SLAVE REPLICATION;
MASTER_PASSWORD: A senha do usuário replication, criados anteriormente também;
MASTER_LOG_FILE: O arquivo atual de log binário do servidor master, indicado na linha que buscamos no backup;
MASTER_LOG_POS: A posição atual no arquivo de log binário, indicado também no backup.

Nota: Depois de executado esse comando uma vez, se precisar refazer a sincronia entre os servidores, basta restaurar o arquivo de backup, pois a posição do log binário já está no arquivo de backup como indicado anteriormente. Precisamos executar esse comando da primeira vez para indicar o IP do servidor, usuário e senha.

Agora que a replicação foi configurada, basta iniciá-la. No prompt do MySQL Slave:

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

Pronto! Se fez tudo certo, a replicação já deve estar funcionando. Tente criar bancos de dados ou tabelas no master e verifique que também devem estar no slave.

Verificação

Com tudo rodando, você pode querer saber como estão as coisas, querer monitorar ou verificar se existe algum erro ou problema de desempenho. Existe um comando que pode ser feito no servidor MySQL Slave que nos mostra algumas informações:

mysql> SHOW SLAVE STATUS \G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.1
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 602
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000005
             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: 602
              Relay_Log_Space: 410
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

As primeiras linhas que temos que olhar são:

Slave_IO_Running: Se a replicação está rodando e conseguiu se conectar no servidor master. Caso esteja como No, verifique se executou o START SLAVE, se há problemas de conectividade entre as máquinas, usuário e senha corretos, etc;
Slave_SQL_Running: Se os comandos SQL estão sendo executados ou pronto para serem executados. Se não estiver como Yes, significa que deu algum erro no meio da replicação: dados duplicados, tabelas incorretas, banco dessincronizado, etc.

Em ambos os casos, quando dar algum erro, você pode consultar o erro nas linhas Last_IO_Error e Last_SQL_Error.

Outra linha importante é a Seconds_Behind_Master. Essa linha indica o quão atrasado o servidor Slave está em relação ao Master. Idealmente, o valor aqui deve ser sempre 0, ou seja, todos os dados estão sempre sincronizados. Mas se por algum problema de desempenho o slave não conseguir fazer todas as inserções e updates do Master, ele vai aumentando o número (uma estimativa em segundos). Não é um problema quando não for zero, desde que não seja um número muito alto ou constantemente crescente.

Quando você para um servidor slave e o inicia, digamos, depois de 30 minutos, ele recomeça a replicação de onde parou, então você pode ver o número do Seconds_Behind_Master diminuírem.

Outro ponto interessante é saber sempre a posição de log binário que o slave está pegando do Master (o mesmo do SHOW MASTER STATUS do Master). Essa informação é representada pelas linhas Master_Log_File e Read_Master_Log_Pos.

Replicação MySQL Master / Master

A replicação master-master tem como objetivo ter os mesmos dados nos dois servidores, assim como o master-slave. Mas no master-master, os sistemas podem inserir e modificar os dados nos dois servidores. A vantagem deste modo de operação é que se um cair, o outro pode assumir imediatamente todas as funcionalidades: leitura e escrita. No modelo master-slave, se o master cair, é necessário que um dos slaves se torne um master, e a replicação para outros servidores tem que ser refeita (além de geralmente necessitar de configuração manual nos sistemas).

A seguinte imagem ilustra como vai funcionar:

Para fazer o master, basta seguir todos os procedimentos do Master/Slave, só que trocando os servidores. O servidor master vai ser também um slave do outro servidor. Na prática, os dois são masters, os dois são slaves um do outro.

Porém, há uma característica muito importante que devemos lembrar: o autoincrement. O autoincrement é um recurso que o banco de dados oferece para ir incrementando automaticamente os números em uma coluna de dados. Ele é geralmente usado para colunas que indicam um ID único de uma linha. O problema com o autoincrement é que quando duas inserções de linha acontecem ao mesmo tempo nos dois servidores, o MySQL vai gerar o mesmo número. Quando um slave for pegar a inserção do master, ele vai tentar inserir uma outra linha com o mesmo número ID. Ao tentar fazer isso, dará um erro na replicação dizendo que essa coluna não pode ser repetida com esse ID.

Para contornar isso, temos que indicar ao banco de dados para utilizar números pares em um servidor e números ímpares em outro servidor.

Nota: Como deu pra perceber, ao usar qualquer coluna de tabela com autoincrement, a complexidade da replicação aumenta. Por isso, se você usa autoincrement em alguma tabela, você provavelmente não vai querer usar mais de dois servidores, senão a técnica dos números pares e ímpares não dará certo.

Você vai precisar das seguintes linhas no my.cnf do primeiro servidor:

auto-increment-increment = 2
auto-increment-offset = 1

E no my.cnf do segundo servidor:

auto-increment-increment = 2
auto-increment-offset = 2

Depois, em resumo, basta seguir os passos da replicação, explicados anteriormente:

Certifique-se que as linhas de server-id tenham valores diferentes em cada servidor;
Certifique-se que os logs binários (log-bin no my.cnf) estejam ativados nos dois servidores;
Crie os usuários de replicação em cada um dos servidores;
Caso necessário, sincronize os dados de um servidor em outro utilizando o mysqldump;
Utilize o comando CHANGE MASTER TO para configurar a replicação, cada servidor apontando como master o outro;
Verifique com o SHOW SLAVE STATUS em cada um dos servidores para ver se está tudo OK.

FAQ – Dúvidas comuns

Sempre surge alguma dúvida né? Ou até serve como curiosidades…

Como obter as informações do master em um slave?

Esqueceu a senha hein?

Depois de usar o comando CHANGE MASTER TO, os dados ficam gravados no arquivo master.info dentro do diretório de dados do MySQL, que geralmente fica em /var/lib/mysql. O arquivo está em texto plano e pode ser visualizado com qualquer editor de texto ou o cat. Inclusive, o usuário e senha de replicação ficam à mostra (e tem que ficar).

Em termos de segurança, certifique-se que as permissões do usuário de replicação estão apenas para o host que vai conectar e que o arquivo master.info esteja com a permissão para apenas o usuário do mysql ler e escrever.

Como replicar apenas algumas tabelas?

Apesar de não ser muito recomendado no MySQL, você pode filtrar quais tabelas serão replicadas ou não. Existem duas maneiras de se fazer isso: do lado do master e do lado do slave. Em ambas, é necessário colocar alguns parâmetros no my.cnf e reiniciar o serviço MySQL.

Do lado do master, as opções binlog-do-db e binlog-ignore-db fazem isso. Quando o binlog-do-db é utilizado, ele vai gravar no log binário apenas as tabelas que você especificar na opção. Quando o binlog-ignore-db é utilizado, ele grava no log binário todos os bancos de dados, menos os que você especificou.

Mas um jeito melhor de usar é no slave. Ao invés de não gravar o log binário de algumas coisas, é preferível que o master registre tudo, enquanto o slave selecione o que quer e o que não quer.

Eis alguns exemplos… Quero puxar apenas o banco de dados banco_radical:

replicate-do-db = 'banco_radical'

Ou eu quero replicar dois bancos de dados, e por isso tem que se usar duas opções:

replicate-do-db = 'banco_radical'
replicate-do-db = 'billing'

Agora um bastante comum: eu quero replicar todos os bancos de dados, menos o banco mysql, que contém definições de usuários específicas para cada servidor:

replicate-ignore-db = 'mysql'

Vou mais longe, agora eu quero replicar apenas a tabela objetos_radicais do banco de dados banco_radical:

replicate-do-db = 'banco_radical'
replicate-do-table = 'objetos_radicais'

Um prático: quero replicar apenas várias tabelas de um blog WordPress, que utiliza o prefixo wp_42_:

replicate-do-db = 'wordpress'
replicate-wild-do-table = 'wp\_42\_%'

E por aí vai. O comando SHOW SLAVE STATUS também mostra essas opções caso você tenha utilizado.

Referências

MySQL Reference Manual – http://dev.mysql.com/doc/refman/5.5/en/index.html
MySQL Performance Blog – http://www.mysqlperformanceblog.com/

603.487