Replicação mestre-escravo do MySQL na mesma máquina

A replicação do MySQL é um processo que permite que os dados de um servidor de banco de dados MySQL (o mestre) sejam copiados automaticamente para um ou mais servidores de banco de dados MySQL (os escravos). Geralmente é usado para distribuir o acesso de leitura em vários servidores para escalabilidade, embora também possa ser usado para outros fins, como failover ou análise de dados no escravo para não sobrecarregar o mestre.

Como a replicação mestre-escravo é uma replicação unidirecional (do mestre para o escravo), apenas o banco de dados mestre é usado para as operações de gravação, enquanto as operações de leitura podem ser distribuídas em vários bancos de dados escravos. O que isso significa é que, se a replicação mestre-escravo for usada como solução de expansão, você precisará ter pelo menos duas fontes de dados definidas, uma para operações de gravação e a segunda para operações de leitura.

Os desenvolvedores MySQL geralmente trabalham em apenas uma máquina e tendem a ter todo o seu ambiente de desenvolvimento nessa máquina, com a lógica de que eles não dependem de uma rede ou conexão com a internet. Se uma replicação mestre-escravo for necessária porque, por exemplo, eles precisam testar a replicação em um ambiente de desenvolvimento antes de implantar as alterações em outro lugar, eles precisam criá-la na mesma máquina. Embora a configuração de uma única instância do MySQL seja bastante simples, precisamos fazer um esforço extra para configurar uma segunda e, em seguida, uma replicação mestre-escravo.

Para este tutorial passo a passo, escolhi o Ubuntu Linux como sistema operacional host e os comandos fornecidos são para esse sistema operacional. Se você deseja configurar sua replicação mestre-escravo do MySQL em algum outro sistema operacional, você precisará fazer modificações em seus comandos específicos. No entanto, os princípios gerais de configuração da replicação mestre-escravo do MySQL na mesma máquina são os mesmos para todos os sistemas operacionais.

Instalação da primeira instância MySQL

Se você já tiver uma instância do banco de dados MySQL instalada em sua máquina, pule esta etapa.

A maneira mais fácil de instalar o MySQL no Ubuntu é executar o seguinte comando em um prompt de terminal:

 sudo apt-get install mysql-server

Durante o processo de instalação, você será solicitado a definir uma senha para o usuário root do MySQL.

Configurando o mysqld_multi

Para gerenciar duas instâncias MySQL na mesma máquina com eficiência, precisamos usar mysqld_multi .

O primeiro passo na configuração do mysqld_multi é a criação de dois grupos [mysqld] separados no arquivo my.cnf existente. A localização padrão do arquivo my.cnf no Ubuntu é /etc/mysql/ . Então, abra o arquivo my.cnf com seu editor de texto favorito e renomeie o grupo [mysqld] existente para [mysqld1] . Esse grupo renomeado será usado para a configuração da primeira instância do MySQL e também será configurado como instância mestre. Como na replicação master-slave do MySQL cada instância deve ter seu próprio server-id exclusivo , adicione a seguinte linha no grupo [mysqld1] :

 server-id = 1

Como precisamos de um grupo [mysqld] separado para a segunda instância do MySQL, copie o grupo [mysqld1] com todas as configurações atuais e cole-o abaixo no mesmo arquivo my.cnf . Agora, renomeie o grupo copiado para [mysqld2] e faça as seguintes alterações na configuração do slave:

 server-id = 2 port = 3307 socket = /var/run/mysqld/mysqld_slave.sock pid-file = /var/run/mysqld/mysqld_slave.pid datadir = /var/lib/mysql_slave log_error = /var/log/mysql_slave/error_slave.log relay-log = /var/log/mysql_slave/relay-bin relay-log-index = /var/log/mysql_slave/relay-bin.index master-info-file = /var/log/mysql_slave/master.info relay-log-info-file = /var/log/mysql_slave/relay-log.info read_only = 1

Para configurar a segunda instância do MySQL como escrava, defina server-id como 2, pois deve ser diferente do server-id do mestre.

Como ambas as instâncias serão executadas na mesma máquina, defina a port da segunda instância para 3307 , pois ela deve ser diferente da porta usada para a primeira instância, que é 3306 por padrão.

Para permitir que esta segunda instância use os mesmos binários do MySQL, precisamos definir valores diferentes para socket , pid-file , datadir e log_error .

Também precisamos habilitar relay-log para usar a segunda instância como escravo (parâmetros relay-log , relay-log-index e relay-log-info-file ), bem como definir master-info-file .

Por fim, para tornar a instância escrava somente leitura, o parâmetro read_only é definido como 1 . Você deve ter cuidado com esta opção, pois ela não impede completamente as alterações no escravo. Mesmo quando read_only estiver definido como 1 , as atualizações serão permitidas apenas de usuários que tenham o privilégio SUPER . O MySQL introduziu recentemente o novo parâmetro super_read_only para evitar que usuários SUPER façam alterações. Esta opção está disponível com a versão 5.7.8.

Além dos grupos [mysqld1 [mysqld1] e [mysqld2] , também precisamos adicionar um novo grupo [mysqld_multi] ao arquivo my.cnf :

 [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = multi_admin password = multipass

Assim que instalarmos a segunda instância do MySQL e iniciarmos ambas, daremos os privilégios apropriados ao usuário multi_admin para poder encerrar as instâncias do MySQL.

Criar novas pastas para a segunda instância do MySQL

Na etapa anterior, preparamos o arquivo de configuração para a segunda instância do MySQL. Nesse arquivo de configuração são usadas duas novas pastas. Os seguintes comandos do Linux devem ser usados ​​para criar essas pastas com os privilégios apropriados:

 mkdir -p /var/lib/mysql_slave chmod --reference /var/lib/mysql /var/lib/mysql_slave chown --reference /var/lib/mysql /var/lib/mysql_slave mkdir -p /var/log/mysql_slave chmod --reference /var/log/mysql /var/log/mysql_slave chown --reference /var/log/mysql /var/log/mysql_slave

Configurações de segurança adicionais no AppArmor

Em alguns ambientes Linux, as configurações de segurança do AppArmor são necessárias para executar a segunda instância do MySQL. Pelo menos, eles são necessários no Ubuntu.

Para configurar corretamente o AppArmor, edite o arquivo /etc/apparmor.d/usr.sbin.mysqld com seu editor de texto favorito, adicione as seguintes linhas:

 /var/lib/mysql_slave/ r, /var/lib/mysql_slave/** rwk, /var/log/mysql_slave/ r, /var/log/mysql_slave/* rw, /var/run/mysqld/mysqld_slave.pid rw, /var/run/mysqld/mysqld_slave.sock w, /run/mysqld/mysqld_slave.pid rw, /run/mysqld/mysqld_slave.sock w,

Depois de salvar o arquivo, reinicie a máquina para que essas alterações tenham efeito.

Instalação da segunda instância do MySQL

Várias abordagens diferentes podem ser seguidas para a instalação da segunda instância do MySQL. A abordagem apresentada neste tutorial usa os mesmos binários do MySQL que o primeiro, com arquivos de dados separados necessários para a segunda instalação.

Como já preparamos o arquivo de configuração e as pastas e alterações de segurança necessárias nas etapas anteriores, a etapa final de instalação da segunda instância do MySQL é a inicialização do diretório de dados do MySQL.

Execute o seguinte comando para inicializar o novo diretório de dados do MySQL:

 mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave

Depois que o diretório de dados do MySQL for inicializado, você poderá iniciar ambas as instâncias do MySQL usando o serviço mysqld_multi :

 mysqld_multi start

Defina a senha de root para a segunda instância do MySQL usando mysqladmin com o host e a porta apropriados. Lembre-se, se host e porta não forem especificados, mysqladmin se conectará à primeira instância do MySQL por padrão:

 mysqladmin --host=127.0.0.1 --port=3307 -u root password rootpwd

No exemplo acima eu configurei a senha para “rootpwd”, mas é recomendado usar uma senha mais segura.

Configuração Adicional do mysqld_multi

No final da seção “Configurando o mysqld_multi”, escrevi que daremos privilégios apropriados ao usuário multi_admin mais tarde, então agora é a hora para isso. Precisamos dar a este usuário privilégios apropriados em ambas as instâncias, então vamos primeiro nos conectar à primeira instância:

 mysql --host=127.0.0.1 --port=3306 -uroot -p

Uma vez logado, execute os dois comandos a seguir:

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

Saia do cliente MySQL e conecte-se à segunda instância:

 mysql --host=127.0.0.1 --port=3307 -uroot -p

Uma vez logado, execute os mesmos dois comandos acima:

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

Saia do cliente MySQL.

Iniciar ambas as instâncias MySQL automaticamente na inicialização

A etapa final da configuração do mysqld_multi é a instalação do script de inicialização automática no init.d .

Para fazer isso, crie um novo arquivo chamado mysqld_multi em /etc/init.d e dê os privilégios apropriados:

 cd /etc/init.d touch mysqld_multi chmod +x /etc/init.d/mysqld_multi

Abra este novo arquivo com seu editor de texto favorito e copie o seguinte script:

 #!/bin/sh ### BEGIN INIT INFO # Provides: scriptname # Required-Start: $remote_fs $syslog # Required-Stop: $remote_fs $syslog # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: Start daemon at boot time # Description: Enable service provided by daemon. ### END INIT INFO bindir=/usr/bin if test -x $bindir/mysqld_multi then mysqld_multi="$bindir/mysqld_multi"; else echo "Can't execute $bindir/mysqld_multi"; exit; fi case "$1" in 'start' ) "$mysqld_multi" start $2 ;; 'stop' ) "$mysqld_multi" stop $2 ;; 'report' ) "$mysqld_multi" report $2 ;; 'restart' ) "$mysqld_multi" stop $2 "$mysqld_multi" start $2 ;; *) echo "Usage: $0 {start|stop|report|restart}" >&2 ;; esac

Adicione o serviço mysqld_multi aos níveis de runlevels padrão com o seguinte comando:

 update-rc.d mysqld_multi defaults

Reinicie sua máquina e verifique se ambas as instâncias do MySQL estão sendo executadas usando o seguinte comando:

 mysqld_multi report

Configurar replicação mestre-escravo

Agora, quando tivermos duas instâncias MySQL rodando na mesma máquina, configuraremos a primeira instância como master e a segunda como slave.

Uma parte da configuração já foi feita no capítulo “Configurando o mysqld_multi”. A única alteração restante no arquivo my.cnf é definir o log binário no mestre. Para fazer isso, edite o arquivo my.cnf com as seguintes alterações e adições no grupo [mysqld1] :

 log_bin = /var/log/mysql/mysql-bin.log innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 binlog-format = ROW

Reinicie a instância mestre do MySQL para que estas alterações entrem em vigor:

 mysqld_multi stop 1 mysqld_multi start 1

Para que o escravo se conecte ao mestre com os privilégios de replicação corretos, um novo usuário deve ser criado no mestre. Conecte-se à instância mestre usando o cliente MySQL com o host e a porta apropriados:

 mysql -uroot -p --host=127.0.0.1 --port=3306

Crie um novo usuário para replicação:

 mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'replication'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

Saia do cliente MySQL.

Execute o seguinte comando para criar um dump dos dados mestre:

 mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases --master-data=2 > replicationdump.sql

Aqui usamos a opção --master-data=2 para ter um comentário contendo uma instrução CHANGE MASTER dentro do arquivo de backup. Esse comentário indica as coordenadas de replicação no momento do backup, e precisaremos dessas coordenadas posteriormente para a atualização das informações do mestre na instância do escravo. Aqui está o exemplo desse comentário:

 -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=349;

Importe o dump que você criou na etapa anterior para a instância escrava:

 mysql -uroot -p --host=127.0.0.1 --port=3307 < replicationdump.sql

Finalmente, para que a instância do escravo se conecte à instância do mestre, as informações do mestre no escravo precisam ser atualizadas com os parâmetros de conexão apropriados.

Conecte-se à instância escrava usando o cliente MySQL com o host e a porta apropriados:

 mysql -uroot -p --host=127.0.0.1 --port=3307

Execute o seguinte comando para atualizar as informações do mestre (pegue as coordenadas de replicação do arquivo dump replicationdump.sql , conforme explicado acima):

 mysql> CHANGE MASTER TO -> MASTER_HOST='127.0.0.1', -> MASTER_USER='replication', -> MASTER_PASSWORD='replication', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=349;

Execute o seguinte comando para iniciar o escravo:

 mysql> START SLAVE;

Execute o seguinte comando para verificar se a replicação está funcionando:

 mysql> SHOW SLAVE STATUS \G

Parabéns. Sua replicação mestre-escravo do MySQL na mesma máquina agora está configurada com sucesso.

Embrulhar

Ter uma replicação mestre-escravo configurada em seu ambiente de desenvolvimento é útil se você precisar dela para uma solução de expansão no ambiente de produção. Dessa forma, você também terá fontes de dados separadas configuradas para operações de gravação e leitura para que possa testar localmente se tudo funciona conforme o esperado antes da implantação adicional.

Além disso, você pode querer ter várias instâncias escravas configuradas na mesma máquina para testar o balanceador de carga que distribui as operações de leitura para vários escravos. Nesse caso, você pode usar este mesmo manual para configurar outras instâncias escravas repetindo todas as mesmas etapas.