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.

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.