Sincronizar dos tablas en MySQL

05 Ene 2010 En: MySQL

Hace unos días en el trabajo, me pidieron que duplicara un sitio de manera que tuviesen bases de datos separadas pero compatieran la información de los usuarios, si en una de las dos instalaciones se hacía una modificación a los datos de un usuario, estos cambios debían hacerse en la otra base de datos, y viceversa; el problema se agravaba cuando era necesario además tener en cuenta que podían registrarse nuevos usuarios y eliminarlos también, con todo y validar que solo se usara una cuenta de correo por usuario.

Entre las opciones estaba modificar el software para que al grabar lo hiciera en las dos bases de datos, otra opción era usar la replicación de mysql, otra correr un evento cron que pasara datos de una tabla a la otra y solo permitir registros y eliminaciones en una de las instancias y muchas más ideas locas.

Trataba a toda costa de no incurrir en modificar el software, la idea de tener que modificar el moodle y la aplicación y tener que pasar por el proceso otra vez de probar todo y no crear mas errores, me daba alientos para buscar otra solución, la replicación la descarté porque necesitaba que ambas tablas pudieran ser escritas, el cron me daba un lag entre actualizaciones que no deseaba (un usuario registrado en un lado y no poder entrar al otro, podría tratar de registrarse de nuevo y eso añadiría complejidad), aunque realmente era lo mas cercano a lo que buscaba sobretodo después de descubrir mk-table-sync que prometía hacer lo que yo necesitaba.

Después de investigar por largo rato, me encontré con una solución perfecta, un motor de almacenamiento para MySQL incluido desde la versión 5.0.3 llamado FEDERATED, lo que este motor permite es usar localmente una tabla remota, así como lo lee, una de las bases de datos usaría una tabla remota, y todas las acciones en cualquiera de los dos lados afecta al otro lado porque en realidad siempre se ha estado usando una sola tabla; MySQL hace el truco analizando las consultas y enviando a travéz de la API de MySQL las consultas a la otra base de datos.

Lo primero será verificar que se cuenta con el motor y que está activo, tras ejecutar la consulta SHOW ENGINES; entre los resultados debe ver un registro para FEDERATED cuyo valor para el campo Support debe ser YES, en caso contrario debe primero activarlo; en mi caso particular uso Ubuntu lo activé editando el archivo /etc/mysql/my.cnf con privilegios de root, y añadiendo en la sección [mysqld] del archivo una línea con el texto: federated, ahora solo queda guardar el archivo y reiniciar el mysql (sudo /etc/init.d/mysql restart)

Así se hace: Supongamos que se tienen 2 bases de datos bd1 y bd2 ambas tienen una tabla usuario con la siguiente estructura:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `username` varchar(25) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `password` varchar(100) NOT NULL DEFAULT '',
  `usertype` varchar(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) 
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

Ahora bien se busca que la tabla contenga exactamente la misma información en ambas bases de datos y que todo cambio que se haga se refleje en ambas bases de datos, la solución entonces es dejar bd1 con la tabla y todos los registros, y crear una tabla federada en bd2 de la siguiente manera:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL DEFAULT '',
  `username` varchar(25) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `password` varchar(100) NOT NULL DEFAULT '',
  `usertype` varchar(25) NOT NULL DEFAULT ''
) 
ENGINE=FEDERATED  
DEFAULT CHARSET=latin1 
CONNECTION='mysql://usuario:password@servidor:puerto/bd1/users';

Hay que notar varias cosas, la primera que no es necesario definir en la tabla federada ni los autoincrement ni los indices, esto se debe a que en realidad la tabla db2.users no existe y los indices y el campo con autoincremento ya están definidos en db1.users, la segunda cosa es que el valor de engine ahora es FEDERATED (por supuesto) y que adicionalmente hay una nueva opción CONNECTION; esta opción debe usar la sintaxis mysql://usuario[:password]@servidor[:puerto]/base_de_datos/tabla, la contraseña y el puerto son opcionales (de hecho si usa localhost no debe usar un número de puerto a no ser que mysql esté escuchando en el puerto); la tercera cosa a notar (mas bien a anotar porque no se nota) es que la tabla db1.users TIENE que existir antes de intentar crear la tabla federada db2.users.

Como comentarios finales hay que añadir que obviamente existe una penalización en el rendimiento, es una característica de la que no se debe abusar; una vez creada la tabla federada NO se debe cambiar la estrúctura de la tabla original porque ocasiona perdida de datos y es posible crear una tabla federada sobre una tabla federada pero hay que cuidarse de no crear un círculo (queda advertido ;) ) y por último pero no menos importante, la seguridad, la información de acceso a la tabla remota queda almacenada en texto plano y es visible a cualquiera que tengo acceso a la estrúctura de la bd.

19 respuestas a "Sincronizar dos tablas en MySQL"

  1. Commentario de
    en 2010-02-09 13:53:08

    muy interesante para hacer multi-source..
  2. Commentario de
    en 2010-02-09 14:59:32

    hola muérgano o/
  3. Commentario de Manuel
    en 2010-08-01 18:26:48

    Hola Andrés ; 
     
    Te escribo para hacer una pregunta con relacion a las tablas federadas. La pregunta aparece abajo: 
     
    Que pasa si el server remoto pierde la coneccion ? 
    Que pasa si el server remoto esta apagado ? 
     
     
    Luego se hace un select, insert o update en la tabla federada ? 
     
     
    Un saludo desde Republica Dominicana 
     
     
    Manuel Matos
  4. Commentario de
    en 2010-11-18 00:56:55

    Excelente artículo. Me preguntaba lo mismo que Manuel y también que pasa si la tabla original tiene muchos registros. Queda todo muy lento? Y la otra pregunta: Si ha habido updates en la tabla original, cuando se entera de esto la tabla federada? Cuando hago consultas sobre ella o mySQL revisa cada cierto intervalo de tiempo? 
     
    Gracias.
  5. Commentario de
    en 2010-11-22 10:54:25

    @manuel, @angelluiggi yo no he hecho benchmarks como para poder cuantificar el penalti que implica usar este motor, pero si sería interesante tratar al menos de identificar un número tentativo. 
     
    Con relación a lo de las actualizaciones, los cambios estarán presentes inmediatamente, debido a que las consultas hechas sobre la tabla dependiente son hechas directamente sobre la tabla que contiene los datos, no hay tiempos de retardo, siempre se está usando la tabla original en el fondo.
  6. Commentario de Dario/
    en 2010-12-31 12:03:54

    Hola como estas muy buen articulo, lo probe y funciona perfectamente en 2 servidores remotos , si el servidor que tiene la tabla "original" se queda sin internet,mientras que el otro servidor tiene la tabla federated. 
    tenes idea si existe para que sin conexion tenga siempre disponible esa tabla? 
    Saludos
  7. Commentario de
    en 2011-03-17 17:08:41

    Me parece muy interesante esta info. Seguramente debe haber una manera de hacer lo que propone Dario. talvez haciendo un federated localmente para situaciones de sin internet. No lo ensaye, pero voy a probarlo y luego comentarles los resultados. Saludos
  8. Commentario de
    en 2011-03-24 23:24:33

    oye brother mira lo q pasa es q ya hice todo lo q biene ahi pero me sale un error en la segunda tabla federada a la hora q le pongo los datos para conectar al otro servidor me marca  
    ERROR 1429 (HY000) : UNABLE TO CONNECT TO FOREING DATA SOURCE; DATABASE: 'AGENDA' USERNAME: 'ROOT' HOSTNAME:'192.168.0.169'
  9. Commentario de
    en 2011-03-24 23:25:39

    kisiera saber xq me sale ese error ya cheque todo y pues nada  
    :D
  10. Commentario de Anonymous
    en 2011-05-05 19:35:23

    Excelente informe. Consulto: 
     
    Como hago si las tablas remotas se encuentran en un hosting?. Tengo una BD1 en un servidor local y una BD2 en un hosting, como hago para realizar la conexión en este caso?
  11. Commentario de
    en 2013-04-03 07:34:08

    Como puedo hacer para eliminar la tabla federated despues? 
    Gracias
  12. Commentario de jeanette
    en 2012-11-24 13:48:06

    EXcelente este articulo, no lo he probado, pero es lo que necesitaba GRACIAS!!
  13. Commentario de Anonymous
    en 2013-04-03 07:32:54

    Como puedo hacer luego para eliminar la tabla federated? 
    Gracias
  14. Commentario de Richard Onel Ibarra
    en 2013-04-26 22:29:53

    solo quería agradecerte por el artículo... la verdad es que no lo he probado, pero creeme que si funciona bien me salvaste! 
    GRACIAS!
  15. Commentario de Antonio
    en 2014-06-27 18:14:33

    Quisiera saber si quiesiera hacer esto mismo para una base de datos entera si es eficiente.
  16. Commentario de Mary/
    en 2015-06-19 15:04:56

    Es posible tener dos conexiones a la misma tabla federada?. Es decir tengo dos servidores remotos que quieren escribir en la misma tabla federada.. se puede? como se haría para indicarle las conexiones?
  17. Commentario de
    en 2016-03-31 16:36:56

    Excelente artículo mi amigo, me sacaste de apuros, demasiado util.
  18. Commentario de robo83
    en 2017-02-16 01:20:20

    amigo tengo una tabla federada pero cuando se regitran en una no pueden entrar en la otras dije q el pasword esta malo cuando reviso aperecen los dos uduarios en las tablas pero solo deja accesar a una que puedo hacer
  19. Commentario de
    en 2017-02-16 16:49:50

    @robo83, no entiendo muy bien su situación pero suena a un problema de permisos y no de la federación, yo primero intentaría conectarme al MySQL que tiene los datos y verificar que el usuario realmente puede acceder todas las tablas que necesita así ya puede descartar que el problema sea de la federación.

Deje una respuesta

Su nombre o apodo

Prueba de seguridad. Por favor digite este código Listen the code



Quién es?

View Andrés F Vargas's LinkedIn profile Andrés F Vargas es un programador Colombiano, que le gusta el open source, en los últimos años se ha enfocado en el desarrollo y operación de aplicaciones web.


Servidores Virtuales Linux

Linode Provee las mejores herramientas y servicios posibles a los que saben lo que que necesitan - un mejor alojamiento Ver mas.