Maestro-Esclavo con MySQL

En cierta ocasión surgió la necesidad de contar en forma centralizada y con un retraso menor a una hora con la información que se estaba generando en cualquiera de las sucursales, por lo que se implementa una solución que a continuación se detalla, considerando que en todas las sucursales se cuenta con MySQL y servidores ya sea Linux o Windows.

Con anterioridad se había definido e implementado una vpn para tener acceso a los servidores de cada sucursal de forma remota.

La idea es que cada una de las bases de datos contenidas en los respectivos servidores de cada sucursal sea replicada de forma casi inmediata al servidor LINUX ubicado en las oficinas corporativas.

Con esto en mente definiremos a cada uno de los servidores de cada instalación como maestro; es decir, la instancia de MySQL que se esté ejecutando ya sea en un servidor Windows o en un LINUX será la que mande o le indique a una instancia esclava ubicada en el anteriormente mencionado servidor LINUX que debe replicar la instrucción SQL realizada en la base de datos maestra.

Para cada sucursal, el puerto de MySQL siempre será el de omisión: 3306 y la identificación de los servidores dentro del archivo de configuración para MySQL, my.cnf será el indicado en la columna master de la tabla que aparece a continuación:

			server-id
sucursal	instalación	pto. En corporativo	master	slave	S.O.	ip de la vpn
A	baseA	3307	107	307	linux	192	168	253	10
B	baseB	3308	108	308	win	192	168	253	12
C	baseC	3309	109	309	linux	192	168	253	14
D	baseD	3310	110	310	win	192	168	253	16
E	baseE	3311	111	311	win	192	168	253	18

Tabla 1.

En la instalación del corporativo, los parámetros para cada instancia del servicio MySQL están definidos en el archivo de configuración mysql.cnf ubicado en el directorio /etc, de acuerdo a lo especificado en la Tabla 1.

COMO DEFINIR LA PARTE MAESTRO DE LA RELACION MAESTRO-ESCLAVO PARA MYSQL.

1. Editar my.cnf (o my.ini si el sistema operativo es Windows)

1.1. Comentar (si existen), las líneas:

skip-networking

bind-address = 127.0.0.1

1.2. Incluir las líneas:

Para Linux:

log-bin = /var/log/nombre-sucursal-bin.log (para Linux)

Para Windows:

log-bin = c:\Archivos de programa\Mysql\Mysql Server 5.0\nombre-sucursal-bin.log

Para Linux y para Windows:

binlog-do-db = nombre-base

server-id = número

Sustituir nombre-sucursal por el nombre de la sucursal de la que se trate, por ejemplo: A.

Sustituir nombre-base por el nombre de la base de datos que esté utilizando en la sucursal, por ejemplo: baseA.

Sustituir número por el número que corresponda, indicado en la columna master de la Tabla 1, siguiendo con nuestro ejemplo: 102.

1.3. Guardar los cambios y salir del editor.

1.4. Reiniciar MySQL

service mysqld restart

1.5. Crear usuario esclavo

mysql –u root [-ppassword]

Nota: si el usuario root tiene contraseña (como debería de ser), escribirla después del modificador –p y sin dejar ningún espacio.

mysql>CREATE USER esclavo IDENTIFIED BY ‘password’;

mysql>GRANT REPLICATION SLAVE ON *.* TO esclavo@’%’ IDENTIFIED BY ‘password’;

mysql>FLUSH PRIVILEGES;

mysql>use nombre-base;

mysql>FLUSH TABLES WITH READ LOCK;

Con esto aseguramos que ningún usuario pueda modificar las tablas de la base de datos, se recomienda dar aviso de que se salgan para evitar contratiempos.

mysql>SHOW MASTER STATUS;

Debemos tomar nota de la información mostrada como resultado del comando, ya que se utilizará mas tarde.

Ejemplo:

+---------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183 | exampledb | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>quit;

1.6. Respaldar la Base de Datos

mysqldump –u root [–ppassword] --opt nombre-base [|bzip] > nombre-base.sql[.bz2]

1.7. Quitar el bloqueo a las tablas

mysql –u root –p[password]

mysql>UNLOCK TABLES;

mysql>quit;

1.8. Pasar el respaldo realizado al servidor esclavo

1.8.1. Opción 1: Vía SCP

scp nombre-base.sql[.bz2] root@192.168.253.1:nombre-base.sql[.bz2]

donde 192.168.253.1 es la dirección ip, correspondiente al servidor Linux del corporativo, visto desde el punto de vista de la vpn.

El archivo quedará en el directorio /home de root en el servidor esclavo.

1.8.2. Opción 2: Vía GoToAssist

Transferir el archivo con la herramienta de soporte GoToAssit al servidor esclavo.

COMO DEFINIR LA PARTE ESCLAVO DE LA RELACION MAESTRO-ESCLAVO PARA MYSQL.

1. Editar el archivo de configuración my.cnf

Entrando con el usuario root al servidor:

cd /etc

nano my.cnf

Incluir las siguientes líneas:

[mysqldxxx]

socket = /tmpmysqldxxx.sock

port=yyyy

pid-file= /var/lib/mysqlxxx/localhost.pid

datadir= /var/lib/myqlxxx

user=root

log-error= /var/log/mysqldxxx.log

#server-id=xxx

#master-host=192.168.253.nnn

#master-user=esclavo

#master-password=password

#master-connect-retry=60

#replicate-do-db=nombre-base

#replicate-ignore-db=sfcontrol

#replicate-ignore-db=mysql

#replicate-ignore-table=nombre-base.JOURNAL

Donde:

xxx es el número de identificación de la instancia de mysql del esclavo para la base en cuestión, de acuerdo a lo indicado en la columna slave de la Tabla 1.

yyyy es el número de puerto asociado a la instancia de mysql del esclavo para la base en cuestión, de acuerdo a lo indicado en la columna pto. en corporativo de la Tabla 1.

nnn es el número de la última parte de la dirección ip del servidor maestro, de acuerdo a lo indicado en la columna ip de la vpn de la Tabla 1.

nombre-base es el nombre de la base de la base a replicar, de acuerdo a lo indicado en la columna instalación de la Tabla 1.

2. Guardar y salir del editor

3. Crear directorio para la instancia de mysql

mkdir /var/lib/mysqlxxx

4. Instalar la nueva base en el servidor esclavo

mysql_install_db --datadir=/var/lib/mysqlxxx

5. Verificar que existan las bases mysql y test

En el directorio /var/lib/mysqlxxx deben existir las bases mysql y test.

6. Iniciar la nueva instancia

mysqld_multi start xxx

7. Crear la Base de Datos esclava

mysql –h 127.0.0.1 –port=yyyy -u root –p

Enter password:

mysql>CREATE DATABASE nombre-base;

mysql>quit;

8. Cargar el respaldo que se hizo en el master

Si se utilizó bzip2:

bunzip2 nombre-base.sql.bz2

mysql –h 127.0.0.1 –port=yyyy –u root [-ppassword] < nombre-base.sql

9. Editar el archivo de configuración my.cnf

Para la instancia que estemos trabajando eliminar el # de las líneas que lo tienen descritas en el punto 1 de esta sección.

Guardar y salir del editor.

10. Reiniciar el servicio para la instancia que estamos trabajando

mysqld_multi stop xxx

mysqld_multi start xxx

11. Iniciar sincronización

mysql –h 127.0.0.1 --port=yyyy –u root [-ppassword]

mysql>SLAVE STOP;

mysql>CHANGE MASTER TO MASTER_HOST=’192.168.253.nnn’, MASTER_USER=’esclavo’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’sucursal-bin.sss’, MASTER_LOG_POS=pos;

· MASTER_HOST es la dirección ip del servidor maestro

· MASTER_USER es esclavo

· MASTER_PASSWORD es password

· MASTER_LOG_FILE es el nombre del archivo que se mostró cuando dimos la instrucción SHOW MASTER STATUS en el servidor maestro, como indicamos en la sección anterior.

· MASTER_LOG_POS es el número que aparece en la columna Position de la tabla que aparece como resultado de la instrucción SHOW MASTER STATUS en el servidor maestro, como indicamos en el ejemplo de la sección anterior.

mysql>SLAVE START;

mysql>SHOW SLAVE STATUS;

Verificar que se esté realizando la replicación automática.

mysql>quit;