Replicación de base de datos maestro – maestro en mysql

La replicación de bases de datos en mysql, puede parecer una tarea sencilla, al venir todo explicado en el manual de mysql https://dev.mysql.com/doc/refman/8.0/en/replication-howto.html , pero leyendo foros y tutoriales es una tarea que da bastantes quebraderos de cabeza si no se realiza correctamente.

Por ese motivo después de varios intentos (y fallos), voy a explicar de una manera sencilla como realizar una replicación maestromaestro de una bbdd en mysql.

Para la tarea voy a utilizar dos servidores mysql con estas ips:

Server 1 – 192.168.0.2: 192.168.0.2

Server 2 – 192.168.0.3: 192.168.0.3

Vamos a simular el caso real de tener que replicar una bbdd que ya esta en producción, y lo que haremos sera importar la bbdd del server1 – 192.168.0.2 y exportarla en el server2 – 192.168.0.3.

Desde Server 1 – 192.168.0.2 importamos:

mysqldump -u root -p --databases BBDD_A_REPLICAR > dump.sql

Desde server2 – 192.168.0.3 nos logueamos creamos la bbdd y la exportamos:

mysqldump -u root -p 
create database BBDD_A_REPLICAR;

mysql -u root -p BBDD_A_REPLICAR < dump.sql

Ahora lo que realizaremos editar el fichero de configuración de mysql para que permita la replicación, con las siguientes variables:

server-id= los mysql tiene que tener id diferentes
master-host= ip del servidor opuesto
master-user=usuario del servidor opuesto
master-password=contraseña del del servidor opuesto
master-port= puerto del servidor opuesto
log-bin log binario de donde se leeran las consultas a replicar
binlog-do-db= base de datos que queremos replicar
replicate-do-db= ase de datos que queremos replicar
auto-increment-increment = 2 tenemos que poner el autoincrement a 2 para que cada mysql puede insertar
auto-increment-offset = en un mysql ponemos 1 y en otro 2 para que inserten id pares y el otro impares

En el Servidor 1 – 192.168.0.2 añadimos lo siguiente:

vi my.conf 

server-id=1
master-host=192.168.0.3
master-user=replicator
master-password=RepliPassword
master-port=3306
log-bin
binlog-do-db=BBDD_A_REPLICAR
replicate-do-db=BBDD_A_REPLICAR
auto-increment-increment = 2
auto-increment-offset = 1

En el Servidor 2 – 192.168.0.3 añadimos lo siguiente:

vi my.conf 

server-id=2
master-host=192.168.0.2
master-user=replicator
master-password=RepliPassword
master-port=3306
log-bin
binlog-do-db=BBDD_A_REPLICAR
replicate-do-db=BBDD_A_REPLICAR
auto-increment-increment = 2
auto-increment-offset = 1

Reinciamos los servicios mysql para que cojan los cambios.

Lo siguiente es crear en cada servidor el usuario que tendra acceso para replicar desde el servidor opuesto:

En el Servidor 1 – 192.168.0.2, creamos usuario para acceder desde Server 2 – 192.168.0.3

grant replication slave, replication client on *.* to replicator@192.168.0.3 identified by "RepliPassword";

En el Servidor 2 – 192.168.0.3, creamos usuario para acceder desde Server 1 – 192.168.0.2

grant replication slave, replication client on *.* to replicator@192.168.0.2 identified by "RepliPassword";

Ahora solo nos faltaría realizar el ultimo paso y es comunicar a cada bbdd desde que posición del mysqld-bin n-log debe de sincronizar.

Nos conectamos a server1 – 192.168.0.2 y realizamos esta consulta:

SHOW MASTER STATUS;
+-----------------------+----------+-----------------------------+--------------+
| File                          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+-----------------------------+--------------+
| mysqld-bin.000006 |     1901 | BBDD_A_REPLICAR  |                     |
+-----------------------+----------+-----------------------------+--------------+

Ahora nos conectamos al server2 – 192.168.0.3 y lo indicamos que replico desde los datos obtenidos antes:

stop slave;

CHANGE MASTER TO master_host='192.168.0.2', master_port=3306, master_user='replicator', master_password='RepliPassword', master_log_file='mysqld-bin.000006', master_log_pos=1901;

start slave;

Y realizamos estos dos pasos en el sentido opuesto:

Nos conectamos a server2 – 192.168.0.3 y realizamos esta consulta:

SHOW MASTER STATUS;
+-----------------------+----------+-----------------------------+--------------+
| File                          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+-----------------------------+--------------+
| mysqld-bin.000004 |       106 | BBDD_A_REPLICAR  |                     |
+-----------------------+----------+-----------------------------+--------------+

Ahora nos conectamos al server1 – 192.168.0.2 y lo indicamos que replico desde los datos obtenidos antes:

stop slave;

CHANGE MASTER TO master_host='192.168.0.3', master_port=3306, master_user='replicator', master_password='RepliPassword', master_log_file='mysqld-bin.000004', master_log_pos=106;

start slave;

Después de estos pasos reiniciamos los mysql y ya deberia de estar replicando, para comprobarlo entramos en la consola mysql y realizamos esta consulta:

show slave status \G;

Y comprobamos que Slave_IO_Running y Slave_SQL_Running este en “yes”.

Slave_IO_Running: Yes
Slave_SQL_Running: Yes