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 maestro–maestro 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