Replicación MySQL


Una de las funcionalidades mas interesantes de MySQL es la replicación, haciendo uso de esta dispondremos de una infraestructura mucho mas escalable y resistente a fallos.
Las principales ventajas son:

  • Distribución de carga de las lecturas.
  • Backups desde uno de los slaves sin interrumpir el servicio.
  • Spare server que podría actuar como master en caso de ser necesario.

 

Instalamos MySQL tanto en el master como en el slave:

emerge -av dev-db/mysql

Realizamos la configuración inicial:

emerge dev-db/mysql --config
 * Please select default authentication plugin (enter number or plugin name):
 * 1) caching_sha2_password [MySQL 8.0 default]
 * 2) mysql_native_password [MySQL 5.7 default]

Elegimos mysql_native_password para que no nos obligue a conectar mediante SSL, si no lo hacemos veremos el siguiente error al arrancar el slave:

Last_IO_Error: error connecting to master 'rep_user@MASTER_IP:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

Arrancamos el servicio:

/etc/init.d/mysql start
rc-update add mysql default

La replicación de MySQL se puede hacer de dos formas distintas:


Legacy Mode

MASTER:

vi /etc/mysql/mysql.d/50-distro-server.cnf
[mysqld]
bind-address = 0.0.0.0
server-id = 1
log_bin = mysql-bin.log
expire_logs_days =  10
max_binlog_size     = 2G

SLAVE:

vi /etc/mysql/mysql.d/50-distro-server.cnf
[mysqld]
bind-address = 0.0.0.0
server-id = 2
log_bin = mysql-bin.log
expire_logs_days =  10
max_binlog_size     = 2G

Reiniciamos el servicio en ambos servidores:

/etc/init.d/mysql restart

MASTER:

Creamos el usuario de replicación y le asignamos los grants necesarios.

mysql> CREATE USER rep_user@'SLAVE_IP' IDENTIFIED WITH mysql_native_password BY 'XXXX';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO rep_user@'SLAVE_IP';
mysql> FLUSH PRIVILEGES;

NOTA: Si vamos a tener mas de un slave tendremos que crear varios usuario de replicación o uno generico del estilo X.X.X.%

Si se están realizando operaciones sobre la base de datos filtramos por firewall el acceso y paramos cualquier proceso local que pueda estar utilizándola.

Sacamos el backup:

mysqldump -A -uroot -p > dump.sql

Obtenemos la posición del binlog:

mysql -uroot -p -sre 'SHOW MASTER STATUS\G'
Enter password:
*************************** 1. row ***************************
             File: mysql-bin.000006
         Position: 1628
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

En este punto ya podemos eliminar las reglas de firewall y arrancar los procesos locales.

Nos llevamos el backup al slave

SLAVE:

Cargamos el backup:

mysql -uroot -p < dump.sql

Enganchamos el slave:

mysql> CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='rep_user', MASTER_PASSWORD='XXXX', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=1628;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: MASTER_IP
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 1628
               Relay_Log_File: kr0mtest2-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000006
             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: 1628
              Relay_Log_Space: 534
              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
                  Master_UUID: 3ba93950-cd6b-11e9-9581-00163ea83ac0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0,00 sec)

Creamos una base de datos en el master y comprobamos que se haya replicado en el slave.

MASTER:

mysql> CREATE DATABASE kr0m;

SLAVE:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kr0m               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

GTID mode

MASTER:

vi /etc/mysql/mysql.d/50-distro-server.cnf
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = 1
bind-address = 0.0.0.0
server-id = 1
log_bin = mysql-bin.log
expire_logs_days =  10
max_binlog_size  = 2G

SLAVE:

vi /etc/mysql/mysql.d/50-distro-server.cnf
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = 1
bind-address = 0.0.0.0
server-id = 2
log_bin = mysql-bin.log
expire_logs_days =  10
max_binlog_size  = 2G

Reiniciamos el servicio en ambos servidores:

/etc/init.d/mysql restart

MASTER:

Creamos el usuario de replicación y le asignamos los grants necesarios:

mysql> CREATE USER rep_user@'SLAVE_IP' IDENTIFIED WITH mysql_native_password BY 'XXXX';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO rep_user@'SLAVE_IP';
mysql> FLUSH PRIVILEGES;

NOTA: Si vamos a tener mas de un slave tendremos que crear varios usuario de replicación o uno generico del estilo X.X.X.%

Si en el master se están realizando operaciones filtramos por firewall el acceso y paramos cualquier proceso local que pueda estar utilizando la base de datos.

Sacamos el backup:

mysqldump --triggers --routines --events -A -uroot -p > dump.sql

Llevamos el backup al slave

SLAVE:

Nos aseguramos de que el backup tenga el GTID:

grep GTID_PURGED dump.sql
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '835ee743-78f5-11ea-960e-00163ea83ac0:1-3';

NOTA: Si el master es un server virgen, el GTID saldrá vacío, creamos una DB de pruebas vacía, la eliminamos y redumpeamos. El GTID puede ser único o varios separados por comas.

Si estuviese vacío veríamos algo parecido a esto:

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';

Cargamos los datos:

mysql -uroot -p < dump.sql
mysql> CHANGE MASTER TO MASTER_HOST="MASTER_IP", MASTER_USER="rep_user", MASTER_PASSWORD="XXXX", MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: MASTER_IP
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 877
               Relay_Log_File: kr0mtest2-relay-bin.000002
                Relay_Log_Pos: 416
        Relay_Master_Log_File: mysql-bin.000001
             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: 877
              Relay_Log_Space: 628
              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
                  Master_UUID: 835ee743-78f5-11ea-960e-00163ea83ac0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 835ee743-78f5-11ea-960e-00163ea83ac0:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0,00 sec)

Creamos una base de datos en el master y comprobamos que se haya replicado en el slave.

MASTER:

mysql> CREATE DATABASE kr0m;

SLAVE:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kr0m               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

NOTA: Si sacamos el dump de un slave ya existente en modo legacy tan solo debemos filtrar el tráfico en el master y anotar la posición en el momento del dump, si es GTID no tendremos que anotar nada, tan solo sacamos el backup y enganchamos.

Si te ha gustado el artículo puedes invitarme a un redbull aquí.
Autor: kr0m -- 07/04/2020 20:47:04