Replicación asíncrona unidireccional Maestro – Esclavo en MySQL 5.0

Publicado en Bases de datos el 26 de May de 2009 por Manel Pérez Mata Tags: MySQL

Supongamos un escenario real de producción en el que tenemos dos servidores. Una de las máquinas contiene un servidor http y una base de datos MySQL que usamos para abserver el tráfico web; la otra la utilizamos para otras tareas necesarias. Sería magnífico poder utilizar la segunda máquina como backup de nuestro MySQL por si hay algún problema grave en la primera ¿no? En este artículo os explico como hacerlo.

La replicación en MySQL funciona de la siguiente manera, el servidor maestro escribe todas las actualizaciones en un fichero de log binario, y mantiene un índice de los ficheros para rastrear las rotaciones de logs. A su vez, el servidor esclavo, que mantiene una conexión abierta contínua con el maestro, es informado cuando sucede algún cambio en el log binario del maestro, y ejecutan estos cambios en sus tablas.

Configuración de la replica asíncrona unidireccional

1) Localizaremos el fichero de configuración de MySQL de la máquina que hará de maestro. Generalmente lo podemos encontrar en la ruta /etc/mysql/my.cnf y descomentamos las líneas:

log_bin=/var/log/mysql/mysql-bin.log

server-id = 1

Buscamos la misma línea en el servidor que hará las veces de esclavo y descomentamos la línea log_bin y server-id modificando el valor a uno diferente del de master:

log_bin=/var/log/mysql/mysql-bin.log

server-id = 2

Ahora podemos arrancar los dos servidores MySQL para empezar a enseñarles a replicar.

2) Una vez arrancados ambos servidores, nos ponemos en el que hará de master y creamos la base de datos y el volcado de los datos en las tablas. En mi caso haré una importación de un fichero sql:

mysql -u [DBUSER-MASTER] -p -e "CREATE DATABASE `myBaseDeDatos`"

mysql -u [DBUSER-MASTER] -p myBaseDeDatos < myBaseDeDatos.sql

3) Ahora crearemos una cuenta de usuario con permisos de replicación, para autorizar, en el servidor maestro, al nuevo usuario a realizar replicas:

mysql> GRANT REPLICATION SLAVE ON *.* TO '[DBUSER-REPLICATOR]'@'[IP-SERVER-SLAVE]' IDENTIFIED BY '[DBPASS-REPLICATOR]';

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000    | 103      |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

5) Una vez hecho esto, volcaremos la base de datos en el servidor esclavo tal y como hicimos con el maestro. Ejecutaré el mismo archivo sql:

mysql -u [DBUSER-SLAVE] -p -e "CREATE DATABASE `myBaseDeDatos`"

mysql -u [DBUSER-SLAVE] -p myBaseDeDatos < myBaseDeDatos.sql

6) Y una vez que el esclavo tenga generada la base de datos, lo configuramos para que esté posicionado con respecto al maestro:

mysql> CHANGE MASTER TO MASTER_HOST = '[IP-SERVER-MASTER]';mysql> CHANGE MASTER TO MASTER_USER = '[DBUSER-REPLICATOR]';mysql> CHANGE MASTER TO MASTER_PASSWORD = '[DBPASS-REPLICATOR]';mysql> CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.0001';mysql> CHANGE MASTER TO MASTER_LOG_POS = 103;mysql> start slave;

Y con esto ya queda configurado el servidor esclavo y alineado con el master. Desde este momento comenzará la replicación con el maestro.

Para comprobar que realmente está replicando, podemos ejecutar el siguiente comando en el servidor esclavo:

mysql> show slave status

Eso muestra:

*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: [IP-SERVER-MASTER]Master_User: [DBUSER-REPLICATOR]Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.0001Read_Master_Log_Pos: 103Relay_Log_File: esclavo-relay-bin.000001Relay_Log_Pos: 103Relay_Master_Log_File: mysql-bin.0001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: myBaseDeDatos

Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 103Relay_Log_Space: 103Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0

1 row in set (0.00 sec)

En Read_Master_Log_Pos y Relay_Log_Pos podemos ver que ambos valores están correctamente alineados. La variable Seconds_Behind_Master nos indica el desfase de actualización entre maestro y esclavo, si todo está bien, mostrará un valor 0.

* * * * * * * **************************************

Replicación de BD en MySQL para Windows

Posted on 18 junio 2010 by tihuilo

Las capacidades de replicación que permiten a las bases de datos de un servidor MySQL ser duplicadas en otro se introdujeron en MySQL 3.23.15. 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. (Esto contrasta con la replicación síncrona que es una característica de MySQL Cluster). Un esclavo servidor puede servir como maestro si quiere preparar una cadena de replicaciones de replicación.

De forma muy sencilla podemos decir que la replicación de bases de datos es una funcionalidad que permite que toda acción realizada a un servidor de base de datos se replique automáticamente en otro servidor. Bajo este concepto, cualquier insert, update, delete, optimize o cualquier otra consulta que modifique la base de datos en cuestión, se ejecutará de la misma forma en el servidor replicado.

Esta funcionalidad se basa en un sistema slave-master lo que es igual a referirse a un sistema cliente-servidor. Funcionalmente es el servidor esclavo quien lee y ejecuta todas las instrucciones que un maestro ejecutó. Estas instrucciones son registradas en un log binario en el maestro y el esclavo mantiene un log de las posiciones que ya leyó y ejecutó del maestro.

La replicación unidireccional tiene beneficios para la robustez, velocidad, y administración del sistema:

La robustez se incrementa con un escenario maestro/esclavo. En caso de problemas con el maestro, puede cambiar al esclavo como copia de seguridad.

Puede conseguirse un mejor tiempo de respuesta dividiendo la carga de consultas de clientes a procesar entre los servidores maestro y esclavo. Se puede enviar consultas SELECT al esclavo para reducir la carga de proceso de conultas del

maestro. Sin embargo, las sentencias que modifican datos deben enviarse siempre al maestro, de forma que el maestro y el esclavo no se desincronicen. Esta estrategia de balanceo de carga es efectiva si dominan consultas que no actualizan datos, pero este es el caso más habitual.

Otro beneficio de usar replicación es que puede realizar copias de seguridad usando un servidor esclavo sin molestar al maestro. El maestro continúa procesando actualizaciones mientras se realiza la copia de seguridad.

La replicación es útil para:

1. Copia de seguridad

En condiciones normales, una base de datos replicada de forma correcta es válida como copia de seguridad.

Además se puede realizar copias de seguridad usando un servidor esclavo para así no interferir al servidor maestro.

1. Mejorar la escalabilidad

Podríamos configurar nuestras aplicaciones para balancear las consultas de lectura (SELECT) entre los servidores replicados.

Podríamos usar herramientas como MySQL Proxy para balancear las consultas de lectura entre los servidores replicados y enviar las consultas de actualización de datos al maestro.

1. Alta disponibilidad

En aplicaciones y entornos en donde sólo se requieren lecturas, podríamos configurar nuestras aplicaciones para balancear las consultas de lectura (SELECT) entre los servidores replicados de manera que si uno se cae se continué prestando servicio.

Pasos para poner en marcha la replicación

A continuación vamos a exponer los pasos a realizar la replicación de una base de datos en un único servidor esclavo. Si quisieramos configurar más esclavos, los pasos a realizar serían los mismos sobre cada uno de los esclavos.

Es necesario que las versiones de MySQL instalado en el maestro y en el esclavo son compatibles ,es decir, similar versión. Idealmente, debe usar la versión más reciente de MySQL en maestro y servidor.

Creamos un usuario MySQL en el servidor maestro con privilegios de replicación, el servidor esclavo se autenticará frente al servidor maestro como un usuario normal.

Para crear el usuario debemos ejecutar desde la consola de comandos de mysql las siguientes sentencias SQL:

CREATE USER ‘<replication_user>’@'<slave_address>’ IDENTIFIED BY ‘<replication_user_password>’

GRANT REPLICATION SLAVE ON *.* TO ‘<replication_user>’@'<slave_address>‘

Con la sentencia anterior el usuario sólo tendría permiso de acceso desde la máquina <slave_address>, en caso de no requerir esta medida de seguridad puedes sustituir el comodin % por el parámetro <slave_address>.

Configuración del servidor maestro

Deberemos agregar las siguientes lineas al final del archivo de configuración del servidor MySQL, por defecto: <MySQL_HOME>/my.ini

# Identificador único del servidor MySQL dentro de todos los servidores implicados en la replicación.

server-id=1

# Al especificar el parámetro log-bin estamos activando el log binario.

# No especificamos un valor para el parámetro de configuración (por defecto será <nombre_maquina>-bin).

log-bin=

# El log binario sólo tendrá las actualizaciones realizadas sobre la base de datos “bd_autentia”

# Si además quisieramos replicar otras bases de datos, duplicariamos este parámetro para cada base de datos.

binlog-do-db=<data_base>

Configuración del servidor esclavo

Deberemos agregar las siguientes lineas al final del archivo de configuración del servidor MySQL, por defecto: <MySQL_HOME>/my.ini

# Identificador único del servidor MySQL dentro de todos los servidores implicados en la replicación.

server-id=2

# Nombre del archivo binario que almacena las instrucciones pendientes de ejecutar, por defecto: <host_name>-relay-bin.index

relay-log=

# Nombre o dirección IP del maestro.

master-host=<master_address>

# El esclavo se conecta a través de un usuario al maestro. Identificador del usuario

master-user=<replication_user>

# El esclavo se conecta a través de un usuario al maestro. Contraseña del usuario

master-password=<replication_user_password>

# Número de segundos que esperará el esclavo para reintentar conectarse al maestro en caso de una perdida de conexión.

master-connect-retry=50

# Número de reintentos de reconexión

master-retry-count=5000

Por último realice una copia de seguridad de la base de datos del maestro y restaurala en el esclavo, luego detienes ambos servidores (esclavo y maestro) y luego inicias el esclavo y luego el maestro.

Referencias: http://www.autentia.com/ , Manual de referencia Mysql5.0