miércoles, 5 de noviembre de 2008

Crear replica de Bases de Datos MySQL

Desde la versión de MySQL 3 es posible tener una base de datos en un servidor que se alimente automáticamente de un servidor de bases de datos maestro, esto es muy útil donde se manejan muchos datos y se realizan muchas transacciones entonces se desea tener un ambiente de ata disponibilidad en caso de que uno de los servidores se caiga , se activa el otro que esta respaldado hasta las ultimas operaciones realizadas.

Esta guía la probé usando un ambiente con linux debian etch y MySQL 5.0 .
Las características de MySQL 5 soportan replicación asíncrona unidireccional: un servidor actúa como maestro y uno o más actúan como esclavos. El servidor maestro escribe actualizaciones en el fichero de log binario, y mantiene un índice de los ficheros para rastrear las rotaciones de logs. Estos logs sirven como registros de actualizaciones para enviar a los servidores esclavos. Cuando un esclavo se conecta al maestro, informa al maestro de la posición hasta la que el esclavo ha leído los logs en la última actualización satisfactoria. El esclavo recibe cualquier actualización que han tenido lugar desde entonces, y se bloquea y espera para que el master le envíe nuevas actualizaciones.

Un esclavo servidor puede servir como maestro si quiere preparar una cadena de replicaciones de replicación.

Tenga en cuenta que cuando usa replicación, todas las actualizaciones de las tablas que se replican deben realizarse en el servidor maestro. De otro modo, debe ser cuidadoso para evitar conflictos entre actualizaciones que hacen los usuarios a las tablas en el maestro y las actualizaciones que hacen en las tablas de los esclavos.

Configuración del Servidor Maestro

En el fichero /etc/mysql/my.cnf comentamos las siguientes lineas , sin importar el valor que tengan asignado, para que el demonio del mysql pueda escuchar en las interfaces de red :


# skip-networking
# bind-address = 127.0.0.1


Ubicamos en el mismo archivo la clasula [mysqld] , debajo de ella buscamos las lineas siguientes:

# server-id = 1
# log_bin = /var/log/mysql/mysql-bin.log
# binlog_do_db = include_database_name
# binlog_ignore_db = include_database_name


Estas lineas debemos descomentarlas y modificarlas dependiendo de lo que vamos a hacer, la primera es para identificar el servidor, en este caso el servidor maestro es el Nº 1, la segunda linea es la que indica la ruta donde almacenarán los logs, la tercera linea es para indicar el nombre de la base de datos que se va a replicar y a la que el servidor irá registrando en el archivo que pusimos en la ruta de la linea anterior y por ultimo en la siguiente linea colocamos el nombre de la base de datos que no registraremos. Además de modificar estos valores debemos agregar dos lineas más quedando todo este código de la siguiente forma:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = NombreBaseDatosReplicar
binlog_ignore_db = mysql
low-priority-updates
innodb_flush_log_at_trx_commit=1


La ultima linea agregada innodb_flush_log_at_trx_commit, cuando este valor es 1 (predeterminado), cada vez que se confirma una transacción el buffer de registros (log buffer) se graba en el fichero de registro y se vuelca a disco.
Ya con esto terminamos la edición del archivo my.cnf en el maestro, nos salimos del editor y procedemos a reiniciar el servicio de mysql

/etc/init.d/mysql restart


Bloquear y Extraer Base de Datos
Crear Usuario
Procedemos ahora a crear un usuario que tendrá permisos para que el servidor esclavo pueda conectarse a la base de datos del servidor maestro ( si ya tiene un usuario creado con tal permisología en la base de datos también puede re-asignarle permisos para la replica en el servidor esclavo). Para ello procedemos a entrar al shell del mysql como root y dentro ejecutamos la sentencia de creación del usuario:

GRANT REPLICATION SLAVE ON *.* TO 'usuario'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;


Bloquear Base de Datos
Para generar la replica de la base de datos estas deben quedar con la misma data , para ello debemos bloquear cualquier registro nuevo que se vaya a generar mientras realizamos el proceso, siendo así usamos la base de datos especifica y posteriormente la bloquearemos, esto se realizará ejecutando lo siguiente:

use nombreBaseDatos;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;


La ultima linea nos generará una respuesta la cual debemos prestar atención, ya que los datos deben ser anotados para posteriormente utilizarlos, de este resultado debemos anotar las dos primera columnas que son el nombre del fichero y la posición, los datos generados son similares a estos :

mysql-bin.0008678 98 nombreBaseDatos


Una vez anotado estos datos, nos salimos del shell de mysql.

Extraer Datos
Es necesario exportar la base de datos para posteriormente importarla en el servidor Esclavo, para ello usaremos el comando mysqldump con el cual generaremos un archivo .sql , ejecutamos entonces en el terminal lo siguiente:

mysqldump -u root -p --opt nombreBaseDatosReplicar > nombreBaseDatos.sql


Generado el archivo .sql lo copiamos en el servidor esclavo. Ahora solo queda desbloquear la base de datos en el servidor maestro para que siga su normal funcionamiento, entramos de nuevo en el shell de mysql como root y ejecutamos el comando:

unlock tables;


Hasta aquí hemos terminado la configuración con el servidor maestro, ahora procederemos a entrar al servidor esclavo para realizar la configuración.

Configuración del Servidor Esclavo
Crear e importar base de datos
En el servidor Esclavo procedemos a crear la base de datos, para ello ingresamos a mysql y desde el shell ejecutamos lo siguiente:

create database nombreBaseDatos;


Nos salimos del shell y procedemos a ejecutar en el terminal el comando para importar la base de datos que previamente habiamos guardado en un archivo .sql , esto lo haremos de la siguiente forma:

mysql -u root -p nombreBaseDatos < /rutaarchivo/nombreBaseDatos.sql

Configuración de MySQL como esclavo
Ahora editaremos el archivo my.cnf del servidor esclavo con el fin de definir que este servidor funcionará como esclavo y se le indicará quien es el servidor maestro, para ello abrimos el archivo y buscamos la clausula [mysqld]y colocamos lo siguiente:

server-id = 2
master-host = ipServidorMaestro
master-user = usuarioCreado
master-password = usuarioPassword
master-connect-retry = 60
replicate-do-db = nombreBaseDatos


Guardamos los cambios realizados y reiniciamos el servicio de mysql , posteriormente entramos como root al mysql y apagamos el servicio de esclavo ejecutando el siguiente comando:

SLAVE STOP;


Inmediatamente procedemos a ingresar los datos del ip del servidor mysql maestro, los datos del usuario y además usaremos los datos que anotamos cuando se procedió a bloquear la base de datos, es decir , el nombre del archivo y la posición, esto lo hacemos con la siguiente sentencia:

CHANGE MASTER TO MASTER_HOST='ipServidorMaestro', MASTER_USER='usuarioCreado', MASTER_PASSWORD='usuarioPassword', MASTER_LOG_FILE='archivo mysql-bin.xxxxx', MASTER_LOG_POS='Nº XX';


Para finalizar iniciamos nuevamente el esclavo y salimos del mysql:

START SLAVE;


Ahora ya se puede realizar cualquier cambio en el servidor maestro y se verán replicados en el servidor esclavo.

10 comentarios:

  1. Hola q tal...gracias por la guia...
    Tengo una duda, una vez que ya tienes hecho la replica y funcionando...si quiero agregar una nueva bd para que se replique, como he de hacerlo? se debe parar toda la bd master??

    Muchas gracias de antemano

    ResponderEliminar
  2. Bueno por un lado lo bueno de este mecanisno de replica es que cualquier cambio que realices en la bd principal se ve reflejado inmediatamente en la bd secundaria. Sin embargo cuando uno va a hacer cambios en la base de datos principal es bueno detener los accesos , es por ello que siempre se hacen estos cambios en horas que haya poca afluencia de usuarios.

    ResponderEliminar
  3. Buenas tardes cree un servidor maestro con la siguiente conf.: server-id=1,log-bin=mysql-bin, binlog-do-db=tecnologia y un escalvo:server-id=2
    master-host=pdpm070
    master-port=3306
    master-user=root
    master-password=micasa1500
    master-connect-retry=60
    report-host=svrmysql

    pero al momento de hacer LOAD DATA FROM MASTER me genera el siguiente error(ERROR 1189): Net error redaing from master... Me pueden ayudar con este caso.

    ResponderEliminar
  4. Hola viejo , ese error te sucede porque las tablas debes tenerlas seguramente configurada como InnoBD u otro tipo, es decir el comando LOAD DATA FROM MASTER solo te va a funcionar con tablas de tipo MyISAM, cuando no es de este tipo pues te genera ese error que colocaste (ERROR 1189): Net error reading from master.
    Saludos

    ResponderEliminar
  5. hola buenas tarde quiero saber si puedo hacer replicar de una base de datos a otra no por un cluster si no del mismo equipo me explico mejor de una base de datos local a otra base de datos local solo replicas a tablas gracias

    ResponderEliminar
    Respuestas
    1. Hola, el problema que tienes de hacerlo en el mismo equipo, es que solo manejas una sola instancia de mysql, y por lo tanto la configuración que tienes en el archivo my.cnf corresponden para un tipo de servidor, sea maestro u esclavo, no puedes colocar en el mismo archivo y para la misma instancia las dos configuraciones, ya que son ambiguas y contradictorias para si misma, fijate que inclusive debes identificar que tipo de servidor es el que se refiere en la linea "server-id = xxx".
      Ahora sin embargo lo que podrías hacer, es crear una maquina virtual en tu propio equipo y en esa maquina virtual tener otra instancia de mysql , configuras las interfaces de red para poder hacer el bridge correspondiente y de esa forma permitir la comunicación constante desde tu maquina host como maestro a tu maquina virtual como slave. O si quieres ir más allá haces dos maquinas virtuales y así de esa forma tu maquina host solo sería para dar hospedaje a las maquinas virtuales, y tus maquinas virtuales se encargarían de tener las instancias de mysql y hacer la replica. Espero esto te haya servido. Saludos!.

      Eliminar
  6. Hola, Sabes necesito hacer una replicacion pero debo programarla, que a las 10 de la mñn los datos se repliquen, sabes como puedo hacer aquello??

    ResponderEliminar
    Respuestas
    1. Hola, usa el cron y el crontab, pero antes debes programar tu script, y lo mandas a ejecutar con el crontab. Saludos!

      Eliminar
  7. hola buen dia, tengo una duda si desconecto el cable de red y hago una insercion de datos, al conectar el cable de red de nuevo, el segundo equipo ya no actualiza la base, sino que o hace despues de reiniciar el servicio de mysql, puedes ayudarme a que esto no suceda gracias

    ResponderEliminar
    Respuestas
    1. Hola que tal, este artículo lo escribo hace casi 10 años atrás, me alegra que aun sea de consulta. Te cuento, revisa que hayas colocado en el servidor maestro en el archivo my.cnf la línea innodb_flush_log_at_trx_commit = 1 ya que esta línea es la que le informa al servicio que la transacción si se pudo guardar, en caso de que hayas desconectado los cables al volverlos a conectar está transacción queda pendiente para grabar en el servidor esclavo.
      Saludos!

      Eliminar