Replicação de Dados MySQL

admin MySQL

O objetivo de um mecanismo de replicação de dados é permitir a manutenção de várias cópias idênticas de um ­­­­mesmo dado em vários servidores de bancos de dados (SGBD).

COMO A REPLICAÇÃO FUNCIONA ?

O MySQL realiza a replicação em um simples processo de três fases, a figura abaixo ilustra a replicação com mais detalhes:

Ubuntu 16.04.7 LTS (MASTER)Ubuntu 16.04.7 LTS (SLAVE)
IP: 192.168.0.2IP: 192.168.0.9
Certifique-se que você configurou dois nós idênticos para execur o MySQL.

Atualizando sistema 

$ sudo apt-get update && sudo apt-get upgrade -y && sudo apt-get dist-upgrade -y && sudo apt-get clean && sudo reboot

Instalando pacotes necessários:

$ sudo apt-get install mysql-server

Backup do arquivo original

$ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf{,.original}

Primeiro vamos configurar o MASTER, o que envolve a colocação de um bloqueio de leitura global. Isso significa que o banco de dados será somente leitura e todas as gravações serão bloqueadas durante o processo.

“Você precisará se planejar para esse tempo de inatividade”

Editando o arquivo mysqld.conf

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Altere a linha 43

bind-address = 192.168.0.2

Descomente as linhas 83 e 84

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

Reiniciar o mysql

$ sudo systemctl restart mysql && sudo systemctl status mysql

Acessar o banco de dados

mysql -u root -p

Vamos criar uma conta de usuário com permissão de replicação para os servidores tanto no master quanto no slave, ou seja, o mesmo usuário criado para os servidores em questão. Aqui está como criar a conta de usuário, que chamaremos de replicador.

mysql> CREATE USER 'replicador'@'192.168.0.9' IDENTIFIED BY 'SENHA';
GRANT REPLICATION SLAVE ON *.* TO 'replicador'@'192.168.0.9';

Em seguida, precisamos bloquear o mestre para obter um instantâneo consistente para inicializar o escravo.

mysql> FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

O resultado do comando SHOW MASTER STATUS irá apresentar uma saída importante, devemos anotar o File e o Position.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     3680 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Em um novo terminal faça o back-up do banco, não saia do terminal, pois esse bloqueio é liberado quando você sai do cliente mysql CLI ou quando executamos UNLOCK TABLES. A trava precisa permanecer até que a etapa abaixo seja concluída. 

mysqldump -u root -p --all-databases --master-data > dbdump.sql

Desbloquear os bancos de dados principais, Você pode fazer isso saindo do shell mysql CLI ou executando:

mysql> UNLOCK TABLES;

Copie o arquivo para o SLAVE:

Transfira o arquivo dbdump.sql com segurança para o escravo, usando scp:

scp dbdump.sql 192.168.0.9:/tmp

Agora no servidor SLAVE.

$ sudo apt-get update && sudo apt-get upgrade -y && sudo apt-get dist-upgrade -y && sudo apt-get clean && sudo reboot

Instalando pacotes necessários:

$ sudo apt-get install mysql-server

Backup do arquivo original

$ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf{,.original}

Editando o arquivo mysqld.conf

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Altere a linha 43

bind-address = 192.168.0.9

Descomente as linhas 83 e 84, lembrando de alterar o id para 2

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log

Reiniciar o mysql

$ sudo systemctl restart mysql && sudo systemctl status mysql

Importe o arquivo de despejo que criamos no MASTER e copiamos para este SLAVE:

mysql -u root -p < /tmp/dbdump.sql

Se desejar apague o arquivo 

$ sudo rm /tmp/dbdump.sql

Acessar o banco de dados

mysql -u root -p

Lembra que anotamos a saída do comando SHOW MASTER STATUS ? Iremos usar agora modificando abaixo:

mysql> STOP SLAVE;

mysql> CHANGE MASTER TO
     MASTER_HOST='192.168.0.2',
     MASTER_USER='replicador',
     MASTER_PASSWORD='SENHA',
     MASTER_LOG_FILE='mysql-bin.000001',
     MASTER_LOG_POS=3680;

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.2
Master_User: replicador
Master_Port: 3306
Connect_Retry: 60
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Note que a replicação está funcionando perfeitamente, portanto a tudo que for feito no servidor MASTER será replicado para o servidor SLAVE.

OpsDash – Monitoramento

$ wget https://packages.rapidloop.com/downloads/opsdash-server_1.8_amd64.deb
$ sudo dpkg -i opsdash-server_1.8_amd64.deb

Inicie o OpsDash

$ sudo /etc/init.d/opsdash-serverd restart
http://192.168.0.9:8080

DICAS

Veja a seguir algumas dicas que vão lhe ajudar

Verificando o tamanho do banco de dados

mysql> SELECT table_schema "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "Size(MB)" FROM information_schema.tables GROUP BY table_schema;
mysql> SELECT table_schema "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "Size(MB)" FROM information_schema.tables 
WHERE table_schema = "NOME DO BANCO";

Verificando o tamanho de uma tabela do banco de dados

mysql>

Verificando usuários

mysql> SELECT user,host FROM mysql.user;

Deletando usuários 

mysql> DROP USER 'USUARIO'@'localhost';

Adicionar/Alterar senha

mysql> SET PASSWORD FOR 'USUARIO'@'localhost' = PASSWORD('NOVA-SENHA');
flush privileges;