Replicación en MySQL 5.0

La replicación en MySQL se realiza entre un servidor Maestro (el que contine los datos originales) y uno o varios servidores Esclavos (los que replican los datos). Se realiza utilizando el log binario de MySQL, cuando se realiza un cambio en la base de datos del servidor Maestro, esto cambios son enviados al Esclavo para que los actualice. Hay que tener en cuenta que la replicación sólo se puede realizar en una dirección, del Maestro hacia el Esclavo, y no bidireccinalmente.

El servidor Esclavo mantiene una conexión permanentemente abierta con el Maestro y se comprueba la ultima posición escrita en el log binario del Maestro, cuando se detectan cambios estos se replican el las tablas del servidor Esclavo.

Antes de ponerse a configurar MySQL con una replicación, hay que tener en cuenta la compatibilidad entre versiones. Como norma general debería de intentarse que la versión de MySQL del Maestro y del Esclavo fuesen la misma, y lo más actualizada posible. De todos modos, un Esclavo con una versión más actualizada que el Maestro suele funcionar, pero no al contrario.

Una vez que conocemos como funciona, y realizadas las comprobaciones de versiones podemos configurar Maestro y Esclavo para que ejecuten la replicación. Comenzaremos por la configuración del Maestro. Debemos editar el fichero my.cnf que encontraremos normalmente en /etc/my.cnf o en /etc/mysql/my.cnf dependiendo del sistema que usemos.

Debemos buscar y editar, o crear en caso de que no existan, dentro de la sección [mysql] las siguientes líneas:

[mysql]
log-bin = mysql-bin
server-id = 1
binlog_do_db = base_de_datos_a_replicar, db2, db3
binlog_ignore_db = bases_de_datos_a_ignorar, db4, db5

La opción log-bin activa el log binario de mysql, y server-id sirve para indicar el id del servidor maestro, debe ser un número entero positivo entre 1 y 2³². Con binlog_do_db le indicamos las bases de datos que queremos copiar (separadas por comas) y con binlog_ignore_db aquellas que no queremos replicar. Una vez realizados los cambios debemos reiniciar el servidor mysql.

Ahora necesitamos crear un usuario en el Maestro (se recomienda que se use un usuario de MySQL exclusivamente para hacer la replicación) y asignarle los permisos de replicación con el siguiente comando:

mysql> GRANT REPLICATION SLAVE ON *.*
-> TO ‘usuario_replicacion’@’%’
-> IDENTIFIED BY ‘pass_usuario’;
mysql> FLUSH PRIVILEGES;
mysql> LOCK ALL TABLES READ ONLY;

Debemos sustituir usuario_replicacion por el nombre de usuario que hemos creado para hacer la repicación y pass_usuario por la contraseña de ese usuario. El valor ‘%’ indica que ese usuario puede acceder desde cualquier host remoto, si queremos indicarle que pueda acceder desde un determinado nombre de dominio o IP debemos cambiar % por la IP o nombre de dominio que queramos que acceda a la replicación.

En el fichero de configuración del Esclavo debemos indicarle un server-id distinto del servidor Maestro, también un número entero positivo entre 1 y 2³². Por ejemplo:

[mysql]
server-id = 2

Si se están configurando varios esclavos cada uno de ellos debe de tener un server-id único, y diferentes del server-id del maestro. Una vez guardados los cambios reinicia el servidor MySQL.

Ahora ya tenemos listos Maestro y Esclavo para realizar la conexión. Ahora debemos hacer un volcado de la base de datos del servidor Maestro que queremos replicar:

[root@servidor-maestro]:~$ mysqldump -u usuario -p nombre_db > nombre_db.sql

Con este comando haremos un volcado de la base de datos a un fichero. Debemos sustituir nombre_db por el nombre de la base de datos que vamos a replicar y usuario por el nombre del usuario con privilegios de administracion. Si no tenemos contraseña para ese usuario no incluiremos la opción -p, en caso contrario nos pedirá la contraseña antes de realizar el volcado.

Ya solo nos queda un paso en el Maestro, conocer el nombre del log binario que se está utiliando y la posición del mismo, accedemos a la línea de comandos de MySQL y ejecutamos:

mysql> SHOW MASTER STATUS;
+——————-+———-+——————-+————————+
| File                         | Position | Binlog_Do_DB | Binlog_Ignore_DB  |
+——————-+———-+——————-+————————+
| mysql-bin.0005 |           98  |                                 |                                        |
+——————-+———-+——————-+————————+

Guardamos esos datos y desbloqueamos las tablas del maestro con:

mysql> UNLOCK ALL TABLES;

Ahora volvemos al esclavo y cargamos el volvado de la base de datos del maestro que realizamos antes:

[root@servidor-esclavo]:~$ mysql -u usuario -p nombre_db < nombre_db.sql

Cuando se temine la carga de la base de datos, accedemos a la línea de comandos de MySQL del esclado para indicarle los datos del maestro e iniciar el esclavo:

mysql> CHAGE MASTER TO MASTER_HOST = ‘ip_del_servidor_maestro’;
mysql> CHAGE MASTER TO MASTER_USER = ‘usuario_replicacion’;
mysql> CHAGE MASTER TO MASTER_PASSWORD = ‘pass_usuario’;
mysql> CHAGE MASTER TO MASTER_LOG_FILE = ‘mysql-bin.0005’;
mysql> CHAGE MASTER TO MASTER_LOG_POS = 98;
mysql> START SLAVE;

Después de esto el servidor Esclavo iniciará la replicación copiando todo lo sucedido desde la posición del log binario indicada hasta ese instante. Para confirmar que todo es correcto podemos ejecutar el siguiente comando en la consola de MySQL:

mysql> SHOW SLAVE STATUS \G
****************** 1. Row ***********************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.54
Master_User: usuario_replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.0005
Read_Master_Log_Pos: 98
Relay_Log_File: esclavo-rely-bin.0001
Relay_Log_Pos: 105
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

Aunque apareceran más cosas, las he omitido, lo que debeis tener en cuenta es Seconds_Behind_Master pues indica el tiempo en segundos que el Esclavo tiene con respecto al Maestro. Creo que no me queda nada, teneis más información en el manual de MySQL, y si teneis alguna duda dejadla en los comentarios.

11 comentarios sobre “Replicación en MySQL 5.0”

  1. Muy interesante el artículo, una pena que en meneame los que se han hecho con el control para votarse entre ellos y hundir a los demás estan imposibilitando mandar noticias interesantes mientras promueven las de nulo interes técnico o minimamente intelectual.

    Es por eso que propongo que votemos los contenidos técnicos y de nuestro interes que ha sido descartado hacia arriba.

    Ánimo con esos artículos.

  2. Lo cierto es que la gran idea de Meneame (que fuesen los propios usuarios los que valorasen que noticias deberían aparecer en la portada) se ha vuelto contra ellos, una pena. Gracias por los ánimos.

  3. Pingback: - DbRunas
  4. Mi pregunta es la siguiente es posible realizar una replicacion en un mismo Server? Lo que quiero realizar en realidad es utilizar una BD para realizar los INSERT, UPDATE y DELETE y la otra BD(esclavo) para realizar consultas, pero dispongo de un unico Servidor.

  5. @David para hacer lo que quieres lo mejor es que uses dos usuarios con privilegios distintos, uno para las inserciones, actualizaciones y borrados; y el otro para las consultas.

  6. mi pregunta es tengo un servidor maestro y uno escalvo dentro de la misma red y se funciona perfectamente, pero ahora quiero implementar la replicacion en dos servidores distintos ubicados en dos zonas geograficas diferentes

    en este codigo (abajo)que se introduce en el esclavo, en lugar de ip del servidor maestro tambien se puede poner un nombre de dominio o no?

    CHAGE MASTER TO MASTER_HOST = ‘ip_del_servidor_maestro’;

    o si alguien sabe un link sobre replicacion remota no se si melo pueda pasar

  7. Que tal un saludo tengo una duda al replicar las bases de datos se hace tambien con sus respectivos dueños de la base de datos y los privilegios sobre esa base de datos para dicho usuario etc.?

    saludos

  8. Hola a todos… se que este tema es de mucho tiempo atras.. pero quisiera saber si alguien me hecha una mano con mi problema. El caso es que mi aplicacion en vb6 con un servidor local mysql usaría esta base como maestro y tiene ip dinamica detras de un router que tambien va cambiando su ip, no es fija. Puede de alguna manera efectuarse la replicación cuando tengo «localhost» o 127.0.0.1? lo que torna imposible configarse de otra forma. Muchas gracias!

  9. Hola a todos mi pregunta es si puedo replicar BD cada cierto tiempo??…….cual seria los comandos??

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.