post icon

Crear Bases de Datos Replicadas Maestro-Esclavo con MySQL

Replicar nuestras bases de datos muchas veces puede ser la solución para nuestros problemas empresariales, desde crear una carga de trabajo equitativa hasta centralizar datos desde lugares remotos en donde no existe una conexión de red constante ni estable o aumentar la velocidad de las aplicaciones cuando la velocidad es muy lenta. Obviamente ganar éstas características tiene su precio a parte del metálico, donde perdemos la capacidad de tener datos actualizados todo el tiempo, o la posibilidad de encontrarnos con conflictos al momento de hacer el “merge“.

Para hacer esto mínimamente necesitaremos 2 instancias de MySQL (ya sea en uno o más servidores físicos).

Configurar Maestro

Paso 1

Detener el proceso de MySQL

1
sudo /etc/init.d/mysql stop

Paso 2

Editamos el archivo de configuración del motor

1
vim /etc/mysql/my.cnf

Paso 3

Si existen estos parámetros los comentamos o eliminamos

1
2
# skip-networking
# bind-address = 127. 0.0.1

Además agregamos ésta sección dentro del bloque [mysqld]

1
2
3
4
5
6
7
8
9
# Le asignamos algun ID a nuestro antojo donde dice ?
server-id                      = ?
log_bin                        = mysql_bin.log
innodb_flush_log_at_trx_commit = 1
sync_binlog                    = 1
# Asignamos una BD para replicar o mas de una si se requiere asi
binlog_do_db                   = db
# Aqui tambien tenemos que tener cuidado, nunca se borrara el log, por lo tanto del disco puede llenarse
expire_logs_days               = 0

Paso 4

Iniciamos el proceso del motor

1
sudo /etc/init.d/mysql start

Paso 5

Bloqueamos las tablas ante escritura

1
2
USE cism;
FLUSH TABLES WITH READ LOCK;

Paso 6

Mostramos los datos del Maestro, estos datos anótalos bien y no lo pierdas

1
SHOW MASTER STATUS;

Nos Aparecerá algo similar a esto

+----------------------------+----------+--------------+-------------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB        |
+----------------------------+----------+--------------+-------------------------+
|mysql-bin.000001            |     106  | db           |                         |
+----------------------------+----------+--------------+-------------------------+

Paso 7

Salimos de la consola mysql

1
exit;

Paso 8

Creamos un dump de la BD a replicar para que sean iguales al comenzar

1
mysqldump -u root -p --routines --opt db > db-dump.sql

Paso 8

Copiamos el dump en la maquina que necesitamos montarlo, si está en la misma, omitir este paso. El método expuesto es SCP, si la otra PC no tiene habilitado esto hacerlo por algún medio disponible 😛

1
scp archivo-origen  user@host:directorio-destino-en-esclavo

Paso 9

Nos volvemos a conectar a nuestra base de datos y desbloqueamos las tablas (la conexión ya lo saben hacer)

1
2
UNLOCK TABLES;
exit;

Configurar el Esclavo

Paso 10

Nos conectamos al motor de servidor esclavo y ejecutamos lo siguiente para crear una bd nueva

1
2
3
CREATE DATABASE db;
# de paso se dan cuenta que se puede desloguear indistintamente con la palabra quit y exit
quit;

Paso 11

En la nueva bd creada volcaremos el dump hace un momento creado del siguiente modo

1
msyql -u root -p db < db-dump.sql

Paso 12

Una vez hecho el paso anterior, nos volvemos a conectar a la bd esclava y le agregaremos lo siguiente, aqui deben usar los datos que anteriormente les dije que guarden bien y no lo pierdan

1
2
3
4
5
6
7
8
9
10
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='IP del master',
MASTER_PORT = puerto-de-mysql-del-master,
MASTER_USER='user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='file_log_determinado?',
MASTER_LOG_POS=recorded_log_position?,
MASTER_CONNECT_RETRY=60 ;
START SLAVE;

Paso 13

El paso con el numero de la mala suerte es precisamente arrancar el esclavo, a ver si nuestra configuracion fue exitosa 😀

1
START SLAVE;

Paso 14

Con esto veremos el estado de nuestra configuración

1
SHOW SLAVE STATUS\G

Nota: En una siguiente entrega veremos como configurar de manera circular, es decir, que cada uno cumpla la función de Maestro y Esclavo a la vez. Los créditos nuevamente de este tutorial lo tienen mis compañeros de trabajo (admirables ellos :D).

Comentarios desde Facebook:

  1. avatar
    Carlos Hidalgo MEXICO Google Chrome Windows
    8 Diciembre 2012 at 16:11 #

    Su mama de todos soy gay

  2. avatar
    FerraN SPAIN Mozilla Firefox Windows
    5 Mayo 2011 at 13:21 #

    Hola a todos.
    Queria preguntar una pequeña duda que tengo , respecto a todo esto me funciona perfecto al hacer un cluster MySQL , la cosa esta en que lo que quiero entender es:
    Al hacer el maestro y el esclavo… si creo por ejemplo las siguientes bases de datos:
    ·Coches , ·Peliculas , ·Juegos

    Me gustaria saber porque cuando hago un “show databases” desde el esclavo se me copia las 3 , y quisiera tener solamente la base de datos “Peliculas”.

    Otra duda es si reinicio el Maestro , el esclavo automaticamente se me pone Maestro y hasta que no lo reiniciara de nuevo el Esclavo no podria tener el maestro que ami me interessa ejerciendo de maestro.

    Millones de gracias , la minima duda me ayuda muchissimo!

  3. avatar
    FerraN SPAIN Mozilla Firefox Windows
    4 Abril 2011 at 13:52 #

    Buenas.
    Me ha gustado bastante la web , quiero felicitaros ya que algunos post me han sido utiles en algunas experiencias y me han salvado de algun apuro.

    Dicho esto… Se me ha intrigado un duda y queria que me aconsejarais ya que veo que teneis bastante idea…la duda es la siguiente:

    Quisiera crear una web y tenerla distribuida en dos bases de datos donde estas esten en dos servidores…Que me haga un balanceo o me copie una de otra y tenerlo por si alguna me fallara…
    En este tema hablais de “Sobre el tema de Crear Bases de Datos Replicadas Maestro-Esclavo con MySQL” y nose si me serviria bien bien…

    He escuchado que podria hacer con un Windows 2008server un cluster…

    Muchas gracias un Saludo!

    • avatar
      GeekZero PARAGUAY Google Chrome Windows
      4 Abril 2011 at 21:00 #

      Hola FerraN, este ejemplo solo te sirve para distribuir las bases de datos, que normalmente es otro objetivo, el balanceo de carga puedes hacer por software, con un middleware como JBoss u otros que existen en el mercado, incluso puedes crearte el tuyo. En cuando a los enlaces redundantes si uno cae existen routers especializados (al menos si quieres que sea automatico) que hacen (y por cierto no son nada baratos) de Cisco, 3Com y otros..

      Tienes tanta concurrencia como para necesitar eso? quiza con optimizar bien lo querys, indices, grupo de archivos ya te bastará.

  4. avatar
    Dario ARGENTINA Mozilla Firefox Windows
    4 Febrero 2011 at 07:11 #

    Hola GeekZero como estas todo bien
    te vuelvo a molestar con una pregunta,
    estoy trabajando con la replicacion circular y necesitaba saber si un servidor se queda sin conexion a internet por x motivo , hay alguna forma de recuperar el log y actualizar el servidor que no tiene conexion?

    Saludos y que tengas un buen dia.

  5. avatar
    Dario ARGENTINA Mozilla Firefox Windows
    28 Enero 2011 at 09:16 #

    Hola como estas todo bien?
    te molesto de nuevo por una consulta,
    estoy replicando algunas tablas de una base de datos
    con la opcion replicate-ignore-table = base.tabla
    lo que necesitaba saber es si con alguna opcion se puede replicar una tabla, pero no todos los campos
    Saludos y muchas gracias

    • avatar
      GeekZero PARAGUAY Google Chrome Windows
      28 Enero 2011 at 09:55 #

      Nunca he visto alguna opción para ignorar columnas en las replicaciones de MySQL (sí en otros motores como Oracle y MsSQL). Al realizar una búsqueda rápida en la documentación tampoco he encontrado nada sobre el tema.

      Si encuentras el modo de hacer esto (si es posible) coméntanos para publicarlo.

      • avatar
        Dario ARGENTINA Mozilla Firefox Windows
        28 Enero 2011 at 10:12 #

        yo tambien estube buscando pero no encontre nada sobre eso voy a ver si puedo buscar otra solucion
        te agradezco un monton Saludos

  6. avatar
    Dario ARGENTINA Mozilla Firefox Windows
    7 Enero 2011 at 14:23 #

    Hola muy buen articulo ya lo puse en funcionamiento y anda exelente
    solamente te quiero preguntar algo, para que sirve este parametro
    innodb_flush_log_at_trx_commit = 1
    y al parametro expire_logs_days le indique = 1 pero no me borro el log que tenia, tenes idea como funciona este ultimo parametro?
    saludos desde Argentina y muchas gracias por compartir tu informacion

    • avatar
      GeekZero PARAGUAY Google Chrome Windows
      7 Enero 2011 at 14:46 #

      Hola Dario, la opción innodb_flush_log_at_trx_commit si se setea en 0 vuelca la información del buffer del log al disco duro cada 1 segundo, si está en 1 se vuelca solamente cada vez que se hace el commit de una transacción, siempre y cuando el manejador de tu BD sea INNODB (opción recomendable).
      La opción expire_logs_days indica el tiempo en el que se debe eliminar automaticamente el log, con tu valor lo haría cada 1 día. pero tiene un “pero”, esto solo ocurre cuando reincias el servicio o se crea un nuevo nodo en el log binario.

  7. avatar
    mar MEXICO Internet Explorer Windows
    19 Octubre 2010 at 14:25 #

    hola tengo una enorme duda
    ¿Base de datos replicadas es lo mismo que base de datos distribuidas?
    Lo que pasa es que deseo saber las fomas de conexión a base de datos distribuidas, pero este tema es parecido, xfa ayudenme con mi pregunta. ….
    de natemano gracias

    • avatar
      GeekZero UNITED STATES Google Chrome Windows
      19 Octubre 2010 at 14:57 #

      Hola Mar, las bases de datos distribuidas mas bien es un concepto y como se llevan a cabo es gracias a la replicación de las BD’s.
      Si vas a crear una replicacion circular con MySQL no te la recomiendo hasta que lo pruebes a fondo, ya que deberás considerar un monton de factores que pueden corremper la consistencia de tus datos. MySQL y la replicacion Maestro-Esclavo va bien. Si realmente necesitas distruibuir de manera circular te recomiendo que consideres cambiar de motor a Oracle o MsSQL (probablemente Postgre también pero nunca he probado su replicacion de este tipo)..

Trackbacks/Pingbacks

  1. Tutorial de Replicacion Circular en MySQL | DevTroce.com WordPress - 15 Agosto 2010

    […] Para llevar a cabo este tutorial, deberán configurar sus bases de datos como lo decía la primer parte. La misma pueden leerla aquí: Replicacion MySQL. […]

Responder