Mysql permite replicar bases de datos, dentro de un mismo servidor mysql (en la misma máquina), o entre B.D. localizadas en diferentes servidores en red local o remota. Para poder conseguir replicar uno de los servidores (Servidor M)  tiene que ser el maestro y el otro servidor el esclavo (Servidor E).

  • Servidor M (Servidor principal, sería el maestro)
  • Servidor E  (Servidor secundario, sería el servidor esclavo).

Cuando indiquemos en el manual Mysql> es porque tenemos que introducir los comandos dentro de la consola de Mysql.

Para entrar en la consola introducir el comando :  mysql -u root -p

Ejemplo del comando en un servidor linux :

# /usr/local/mysql/bin/mysql -u root -p (Puede ser diferente la ruta en tu PC)

En ese momento el servidor mysql pide el password de root , lo introducimos y se accede a la consola.

La replicación de Base de Datos en mysql tiene varias características a considerar y son:

    • Podemos replicar Bases de Datos en el mismo servidor (Diferentes servicios MySql), en Servidores diferentes en LAN (Red Local) o WAN (Servidores Remotos).
    • Se puede configurar (etc/my.cnf), la replicación parcial o total de las tablas de la Base de Datos a replicar del Servidor Maestro al Servidor Esclavo/s.
    • La replicación es UNIDIRECCIONAL, los datos se copian de una base de datos a otra en un solo sentido, eso quiere decir que solo en una base de datos se deben actualizar los datos (sentencias INSERT, UPDATE, DELETE),  que es la base de datos maestra, y la base de datos esclava nunca debe recibir sentencias de actualización de las tablas que se replican, solo consultas (SELECT).
    • De las tablas de la Base de Datos que no se replican, entre el Servidor Maestro y el Esclavo, se pueden realizar las sentencias (INSERT, UPDATE y DELETE), en la base de datos del Servidor Esclavo.
    • Podemos tener sendos servidores esclavos para cada maestro, pero no varios maestros para un esclavo.

  • La replicación copia exactamente todos los cambios que se van haciendo desde que se activa el sistema de replicación, es decir, antes de replicar hay que hacer un backup definitivo de la base de datos principal a la esclava, para que las 2 bases de datos tengan exactamente la misma información.
  • Cada servidor esclavo debe tener permiso para conectar con el maestro y solicitar las actualizaciones.
  • El servidor esclavo necesita una cuenta en el servidor maestro para que pueda conectarse. En el servidor maestro, configure una cuenta como ésta :
  • Mysql> GRANT REPLICATION SLAVE ON *.* TO ‘usuario_esclavo’@’host_esclavo’ IDENTIFIED BY ‘contraseña_esclavo’;
  • El servidor maestro crea un hilo de proceso para manejar cada esclavo. En el lado del servidor esclavo se crean 2 hilos para manejar las tareas de réplica. El primer hilo es de Entrada/Salida recibe los eventos para procesar del servidor maestro y los escribe en los registros de reenvío del esclavo. El segundo hilo el SQL lee los eventos de los registros de reenvío y los ejecuta.
  • Es aconsejable que las réplicas de las Bases de Datos MySql sean de la misma versión y si es posible de la 5.x y activos los mismos motores en las 2 B.D.

La actualización de la información de la Base de datos Mysql Master (total o parcial de sus tablas),  automáticamente Mysql actualiza unos ficheros de datos “mysql-bin.XXXXXX”. Una vez actualizados estos ficheros se envía un evento al servidor con la base de datos Esclava y ésta se comunica con el Servidor Esclavo para recibir la porción del fichero de “mysql-bin.XXXXXX” que le falta; no todo el fichero sino la porción que le falta por tratar solamente (esto se sabe por posiciones tratadas dentro del fichero, nº linea).

CONFIGURACIÓN DEL SERVIDOR MAESTRO MYSQL

Debajo de la etiqueta [mysqld] del fichero /etc/my.cnf del maestro añadir las líneas de la B.D. (empresa):

  • log-bin=mysql-bin  #Nombre de los ficheros transmitidos entre servidores
  • server-id = 1      #Id del servidor (Diferente nº que todos los esclavos)
  • replicate-do-db = empresa           # Base de datos a replicar
  • replicate-ignore-table = empresa.tabla1   # Tabla1 a no replicar
  • replicate-ignore-table = empresa.tabla2   # Tabla2 a no replicar
  • binlog-do-db=empresa                  # Base de datos a crear logs únicamente

Y reiniciar luego el servicio MySql del Servidor Maestro. Con esto ya es suficiente.


CONFIGURACIÓN DEL SERVIDOR ESCLAVO MYSQL

1.- Definir variables globales para el servidor mysql

Debajo de la etiqueta [mysqld] del fichero /etc/my.cnf del esclavo añadir las líneas: de la B.D. (empresa)

  • server-id = 2      #Id del servidor (Diferente nº del maestro y los esclavos)
  • replicate-do-db = empresa                    # Base de datos a replicar
  • replicate-ignore-table = empresa.tabla1   # tabla1 a ignorar
  • replicate-ignore-table = empresa.tabla2     # tabla2 a ingnorar
  • binlog-do-db=empresa          # Base de datos a crear logs únicamente

2.- Definir variables de replicación en el Servidor Esclavo:

Mysql>CHANGE MASTER TO;

  • MASTER_HOST=’192.168.5.130′  (ip master)
  • MASTER_PASSWORD=’xxxx’
  • MASTER_LOG_FILE  = ‘obtenido en SHOW MASTER STATUS del master’
  • MASTER_LOG_POS   = ‘obtenido en SHOW MASTER STATUS del master’

Al realizar este comando se crea un fichero llamado master.info que luego se puede editar para cambiar estos valores a mano.  También se crea el fichero relay-log.info que muestra información de cual es el último archivo absorbido del master, ultima posición leída de este archivo, y log de esta última transferencia. Ejemplo:

/usr/local/mysql/data/relay-log.info

  • ./Servidor-relay-bin.000011   [ultimo LOG  guardado en /usr/local/myql/data]
  • 406                                  [ ultima  posición del LOG en /usr/local/mysql/data]
  • mysql-bin.000122        [ÚLTIMO FICHERO RECIBIDO DEL MASTER]
  • 269                                  [ÚLTIMA POSICIÓN TRATADA DEL MASTER]

Y reiniciar luego el servicio MySql del Servidor Esclavo. Con esto ya es suficiente.

3.- Arrancar el servicio Esclavo

El mandato para arrancar el  Servicio del Esclavo (E) es :

Mysql>START SLAVE ;

El mandato para parar el  Servicio del Esclavo (E)  es :

Mysql>STOP SLAVE;

Estos comandos sirven para parar o arrancar la replicación en el esclavo, si se para no se replica.  Se puede arrancar cuando uno quiera, seguirá por la última posición de replicación que tiene guardada (la última ok), es decir, no se perderían datos aunque el esclavo haya estado 2 horas o más parado, solo que tardará un poco más en absorber toda la información no replicada.

DONDE VER LOS ERRORES DE MYSQL

En caso de error al arrancar el servidor o pararlo, o bien posibles dudas sobre replicación, se pueden ver los logs de errores en:

  • /usr/local/mysql/data/Servidor-Master.err  o
  • /usr/local/mysql/data/Servidor-Esclavo.err ,

Según la máquina


COMANDOS IMPORTANTES PARA VER Y CONTROLAR LA REPLICACIÓN

1.- Utilidades del Servidor Maestro

Estando dentro del servidor Maestro (M) :

Mysql>SHOW MASTER STATUS;

El master por cada cambio realizado en la Mysql principal (insert, update, delete) trabaja creando líneas de cambios en un fichero bin.

La sentencia SHOW MASTER STATUS :  Indica  el fichero .bin que está utilizando el master para guardar los cambios actualmente y por que posición va actualmente (línea dentro del fichero).

Ejemplo de resultado de SHOW MASTER STATUS/

  • File = mysql-bin.000122       [Fichero bin actual ]
  • Position = 269                        [Última posición insertada]
  • BinLog_Do_DB = empresa [Base de datos que trata únicamente]
  • BinLog_Ignore_Db =  “”   [BD ignoradas, si BinLog_Do_Db <> “” el resto]

Estando dentro del servidor Maestro (M) :

Mysql> SHOW PROCESSLISTG;

Muestra el estado del flujo del servidor que se encarga de enviar al esclavo los ficheros de “mensajes” actualizaciones en la base de datos del maestro.

*************************** 10. row ***************************

  • Id: 97
  • User: replica
  • Host: 192.168.5.130:48647
  • db: NULL
  • Command: Binlog Dump
  • Time: 1262
  • State: Has sent all binlog to slave; waiting for binlog to be updated
  • Info: NULL

2.- Utilidades del Servidor Esclavo

Estando dentro del servidor Esclavo (E) :

Mysql> SHOW SLAVE STATUS;

Slave_IO_State: Waiting for master to send event

  • Master_Host: 192.168.5.130 [Ip master]
  • Master_User: replica [Usuario master para conectarse]
  • Master_Port: 3306 [Puerto mysql]
  • Connect_Retry: 60 [60” Timeout para conectar los 2 servers]
  • Master_Log_File: mysql-bin.000122 [Último Fichero master]
  • Read_Master_Log_Pos: 269 [Última posición en el fichero master]
  • Relay_Log_File: server-relay-bin.000011 [Último log en el esclavo]
  • Relay_Log_Pos: 406 [Última posición en el log del esclavo]
  • Relay_Master_Log_File: mysql-bin.000122 [Último fichero master]
  • Slave_IO_Running: Yes            [Muy importante, debe ser yes]
  • Slave_SQL_Running: Yes         [Muy importante, debe ser yes]
  • Replicate_Do_DB: empresa [B.D se replica únicamente]
  • Replicate_Ignore_DB:

Si los campos Slave_IO_Running : NoSlave_SQL_Running : No.

Por estar el servicio parado en el Servidor Esclavo (E), activar con el mandato :

Mysql> START SLAVE;

Estando aún dentro del Servidor Esclavo (E) :

Mysql> SHOW PROCESSLISTG;

El comando enseña para el esclavo los 2 flujos que hay trabajando, en concreto siempre habrá 2, el primero que lee del servidor master (flujo entre máquinas), y el segundo, que coge los datos recibidos y actualiza la base de datos (flujo interno de actualización).

El master guarda los cambios en la base de datos en un fichero log, en ciertas posiciones.  Cuando hace esto manda un evento al proceso del esclavo.  El esclavo recibe el evento y se conecta con el flujo 1 al servidor para recibir el log, una vez recibido el flujo 2 mete la información nueva al mysql propio.

*************************** 1. row ***************************

  • Id: 1
  • User: system user
  • Host:
  • db: NULL
  • Command: Connect
  • Time: 2752  (tiempo desde la última lectura del master, en segundos)
  • State: Waiting for master to send event
  • Info: NULL

*************************** 2. row ***************************

  • Id: 2
  • User: system user
  • Host:
  • db: NULL
  • Command: Connect
  • Time: 12
  • State: Has read all relay log; waiting for the slave I/O thread to update it
  • Info: NULL

RESETEAR o PURGAR LOS LOG’S EN EN SERVIDOR MAESTRO

 

Para inicializa los contadores y borra todos los “mysql-bin” temporales, el mandato es :

Mysql> RESET MASTER;

Para expirar los registros binarios. Podemos utilizar dicha sentencia después de ejecutar la sentecia :

Mysql> PURGE MASTER;

En cada uno de los esclavos para determinar qué registros binarios ya  no son necesarios.

Mysql> SHOW SLAVE STATUS;


Saludos de Telepieza

Post Revisado a fecha : 29 de Diciembre del 2.008

  1. buenas estimados, necesito replicar una DB pero se encuentra en el mismo servidor… es decir.. quiero que los datos que se inserten en la DB “A” se repliquen a la DB “B” a formas de generar todos los SELECT a la DB “B” dejando a la A unicamente con los INSERT.

    gracias por la ayuda.

  2. Buenas tardes. Es posible que tenga el maestro en local y el esclavo en un servidor en la nube?

    Mi necesidad es la siguiente: Tengo un sistema administrativo montado en la nune para varias sucursales. Trabaja muy bien, el problema viene cuando por cualquier motivo se llega a caer el servidor o se va el Internet.

    Si lo trabajo de manera local el riesgo es menor y además tengo una copia de seguridad en la nube y mis consultas para consolidación de la información sería sobre la nube que serían los esclavos.

    De antemano te agradezco. Saludos.

  3. Saludos, tengo una aplicacion con MYSQL local que quiero sincronizar al remoto alojado en un servidor WEB que no tengo acceso para modificar my.cfn, como puedo hacer una sincronizacion desde el local al remoto cada 10 minutos en LINUX

    Gracias

  4. Según la documentación, el traspaso de datos se realiza mediante ficheros binarios, eso significa que no importa el tipo de motor que estas utilizando.

  5. Saludos,

    Una pregunta, el mismo tratamiento se da si en una BD tengo tablas con motores distintos: MyISAM e InnoDB. Por favor sacame de esa duda. porque necesito hacer esto y tengo esa mezcla de motores de almacenamiento.

    Gracias de antemano!

  6. LeMICKE y como especificas ignoarar una bd, aca muestra como ignoarar tabalas de una bd, pero no la BD en si.. cual seria ese comando???… Mi problema esta en:

    Slave_IO_Running: No

    El master lo tengo en un XP y el Sclavo en un Linux, puede esto influenciar???

    Best tutorial

  7. te aconsejo que la tabla la dividas en dos tablas, una para su replicación y la otra no, las replicaciones se realizan por tablas enterar.

  8. Hola como estas exelente articulo ,gracias por compartir
    una pregunta , yo necesito replicar una tabla pero no todos los campos de esa tabla , estube buscando en la documentacion de mysql pero no hubo caso,
    Tenes idea si se puede llegar a hacer ?
    Saludos y muchas gracias

  9. Buenos dias primera vez que voy a replicar bases de datos mysql en donde trabajo tenemos la version 4.1.21, me podrian dar unos tips para replicar estas bases de datos y como es el procedimiento para hacerlo de manera remota,
    saludos cordiales

  10. Hola Mariano… Ya envie a la direccion que indicas el log de errores..
    Te agradezco x la ayuda!!

  11. Correcto eso es lo que indica el error pero estoy segura de q tanto en el slave como en el master tienen diferentes ids. Para comprobarlo use en cada servidor SHOW VARIABLES LIKE ‘server_id’ la misma devuelve los ids de cada servidor y puedo ver q son diferentes. Este error me aparace en el lado del master.. tienes alguna idea de como saber si le paso algo a mi base de datos??

  12. La replicación se realiza mediante ficheros binarios, te indica que tienes el mismo número tanto en el Master como en el Slave. Te ha pasado algo en algún ordenador. Antes de solucionar el problema tienes que saber que te ha pasado con las Bases de Datos. Cuando lo sepas paras los servicios se SLAVE y MASTER y vuelve a arrancar el servicio de replicación.

  13. Hola me parece muy bueno el blog y ya hace un tiempo mediante la informacion que aqui proporcionan realice una practica de replicacion la cual fue exitosa. En este momento que he querido volver a implementar replicacion se me ha presentado un problema el cual muestra el siguiente mensaje:
    Fatal error: The SLAVE I/O thread stops because MASTER AND SLAVE have equal MySQL SERVER ids; these ids must be different FOR REPLICATION TO WORK (OR the –replicate-same-SERVER-id OPTION must be used ON SLAVE but this does NOT always make sense; please CHECK the manual BEFORE USING it).

    Quisiera saber si alguien sabe a que se debe.. Los ids tanto en el servidor y en el cliente son diferentes por lo q no me explico que pueda estar pasando…

    Si pueden ayudarme muchas Gracias!!

  14. Cuando arrancas el Master y el Slave, el sistema en la carpeta de MySql, te genera un fichero de error, para poder ayudarte necesito que me envíes dicho fichero o la parte que da el error.

  15. Buenos Dias..! necesito tu ayuda..
    Estoy intentando implementar la replicacion bajo windows vista tanto en el master como en el Slave con mysql5, pero mi problema es que al hacer un cambio en el maestro no se reflejan en el esclavo… Aparentemente tanto el master como el slave estan sincronizados pero no se como buscar cual es el problema… Alguna idea??
    Espero me puedas contestar lo mas pronto..
    Gracias!!!

  16. Segun lo que veo, el master intenta enviar un registro (1 row in set (0.02 sec) y el esclavo no lo entiende, mi consejo es que pares el servicio de replicación y reinicies los ficheros bin de replicación, entiendo que te debe de funcionar a la primera sin problemas.

  17. Si borras el registro ya existente en la Base de datos y arrancas el Slaver, de forma automática se sincronizarán las bases de datos. La sincronización va por ficheros binarios y cuando se graban en el Slaver se dan como termiandos. Por dicho motivo te indico que borres el registro en la base de datos, para que el proceso de duplicación siga donde te ha dado el error, Para duplicar cada hora, lo que tienes que hacer es arrancar el servicio de duplicación en el SLAVER cada hora, y pararlo al cabo de 15 minutos mediante un CRON, si tienes un LINUX o una TAREA de Trabajo si tienes un Windows.

  18. gracias, por la pronta respuesta.!!

    y como sincronizo los datos del slave con el master.? para no perder los datos que se han actualizado mientras el slave ha estado abajo?

    y con respecto al punto uno.?
    1.- el tiempo de replica de un servidor a otro, porque el valor Seconds_Behind_Master: NULL me aparece , y quiero que se repliquen los datos cada una hora.

    gracias,

  19. El mensaje es muy claro, tienes un error de clave duplicada cuando intenta el insert en la tabla transaction en el SLAVER, para poder solucionar el problema tienes que ir al slaver y borrar el registro duplicado (Last_Error: Error ‘Duplicate entry ‘42493′ for key 1′) , por dicho motivo el servicio de duplicación se para. El porqué de clave duplicada. eso es por temas de tu aplicación o software, y no de la replicación de datos entre un MASTER y un SLAVER.
    Saludos.

  20. Vamos por partes, a tú primera pregunta, es muy sencilla, si quieres que se repliquen a las 10 de la noche, simplemente activa el servicio en el SLAVER (START SLAVE ) a dicha hora y el MASTER enviará la información de forma automática al SLAVER por estar el servicio activo, Lo que tienes que hacer es activar el SLAVER con el comando (START SLAVE) para replicar o parar el servicio del SLAVER con el comando (STOP SLAVER.). Tu segunda pregunta es aún más sencilla, simplemente pasa la información de tu base de datos no MySQL a una hora determinada a la Base de Datos MySQL siendo por supuerto la MASTER con la técnica ODBC y una vez grabada la información, cuando el SLAVER tenga activo la recepción de datos con el comando START SLAVE se replicará de forma automática.

  21. Hola!

    Muy buen tutorial.

    Un par de dudas:

    – ¿Es posible que la replicación sea un proceso automático? Por ejemplo, si quiero que se haga una replicación de cierta tabla todos los días a las 22:00 horas, es esto posible? Hay alguna forma de programar la replicación?
    – En mi caso la base de datos “maestra” no es MySQL, está alojada en un servidor remoto y accedo por ODBC. La base de datos donde quiero que se repliquen los datos, sí es MySQL. Si la maestra no es MySQL, cómo se haría la réplica?

    Muchas gracias por vuestra ayuda!
    Marta

  22. Una cosa es lo que tu necesitas y otra es lo que hace la replicación MySql. La replicación MySql es UNIDIRECCIONAL, eso significa que va desde el Master (M) al Slaver (S). MAESTRO —> ESCLAVO. Si quieres grabar en los Slaver como indicas es posible, pero tienes que hacerlo en tablas que no se replican y después en procesos nocturnos tienes que ejecutar un programa para insertar dichos registros al MASTER. Cuando realizas la grabación al Master, al ser tablas que no se replican , el proceso de replicación no te duplicara la información al SLAVER. En el manual se explica muy bien la configuración de tablas para replicar y no replicar entre el MASTER y EL SLAVER. (SUERTE).

  23. Saludos he leido sobre el tema, ahora observo que dices que solo el servidor m es el que puede modificar los datos(insert,update,delete), resulta que necesito hacer eso en los dos, pues en uno de los servidores se tendra informacion pertinente a una zona(Slave) y en el otro (Master) sera la central, es decir es una agencia con sucursales y pues en la agencia central se manipulan datos tambien en la misma base de datos pero propios de dicha agencia, y la de las sucursales se le agregan a la misma base de datos pero con identificadores de cada agencia para direfenciarlos, como podria solucionar eso, segun leo esto no me serviria para eso o me equivoco..

    De antemano muchas gracias

  24. Si puedes, pero recuerda que las dos bases de datos están sincronizadas mediante la replicación de los ficheros bin, si por algún motivo se para el master y arrancas el slaver como master, después tienes que volver a sincronizar las dos bases de datos (Realizar una copia de seguridad de la slaver a la Master) y empezar de nuevo la replicación.

  25. Hola, excelente el tutorial, lo probé con mysql 5, en servidores ubuntu y funciona a la perfección. La duda que tengo es la siguiente:

    – Si mi servidor M cae por x razón, puedo detener el servidor S como slave e iniciar como servicio normal para que sirva como servidor maestro a la hora de servir datos a una aplicacion?

    Gracias

  26. la replicación son sólo datos y se sustenta con ficheros binarios y desde el momento que lo configuras en los ficheros de configuración del MySql. Según lo expuesto al ser sólo datos, los procedimientos almacenados y trigger no se replican …………….

  27. Hola….he leido co mucha atencion el tema de la replicacion esta excelente, ahora….una pregunta…cuando se replica tambien lo hacen los procedimientos almacenados y los trigger???

  28. E-mail de Cesar a Telepieza :
    Muy agradecido con tu ayuda, más bien me respondiste de una forma muy rápida!! Y te lo agradezco. Actualicé de la versión 4.1.9-nt a la versión 4.1.22 comunity-nt y ya no me da problemas. Los campos blob se replican ahora sin problemas lo uso para fotografías. Aunque meditaré pasarme a la 5.1

    Gracias!

  29. Gracias por el tutorial está exelente! yo lo prové en windows y va de maravilla, hace la replicacion perfecta y casi que en tiempo real. pero cuando voy a trabajar en una tabla que contiene un campo blob, la replicacion se cae. que puedo hacer alrespecto ya aumente el max packet size a 1024M pero nada.. espero que me puedas dar una solucion. gracias

Deja un Comentario

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