Replicación: replica una BD Mysql (MASTER) en una o varias BDs MySQL (SLAVEs) en un solo sentido (MASTER -> SLAVE)
Para implementar una sincronización en ámbos sentidos, debemos usar MySQL Cluster o crear una sincronización
oneway circular. Lo veremos en un próximo artículo.
Por defecto es una replicación asincrona (no necesita conexión continua, no hay problemas por fallos en al conexión).
La replicación se basa en el mecanismo
binary logging. Para cada sentencia aplicada en la BD MASTER se escribe un registro (evento) en el
binary log, la BD SLAVE descarga y lee el
binary log del MASTER y reproduce cada sentencia aplicada a la BD MASTER en la BD SLAVE).
Cuando configuramos el
binary logging en el server MASTER, el
motor MySQL crea un fichero con el prefijo que le indiquemos, donde se
incluirá todos los comandos SQL (sentencias) que modifiquen datos en
la(s) base(s) de datos. Este archivo se guardará en el directorio de
datos. MyQSL creará el archivo nombrandolo con el prefijo indicado en el
archivo de configuración y un número. Este número se incrementará cada
vez que se inicie el servidor o cuando se vuelquen los registros.
Tareas a realizar:
- En el MASTER: habilitar binary logging y configurar un único server ID.
- En cada SLAVE: configurar un único server ID.
- Antes de crear la BD replicada, debemos anotar la posición del Binary Log en el MASTER. Esto es necesario para que el SLAVE sepa a partir de qué evento debe empezar a sincronizar.
- Para configurar el SLAVE necesitaremos necesitaremos el nombre/IP
del servidor MASTER, user/password de MySQL, nombre del archivo Binary Log y posición.
Configuración del MASTER
Para configurar el
Binary Log y
server ID único (nº entero entre 1 y 2^32-1) necesitamos apagar el servidor MySQL y editar el archivo de configuración
my.cnf ó
my.ini. Descomentar o añadir las líneas si no aparecen en el archivo de configuración.
Opcionalmente podemos seleccionar que solo se guarden en el
binary logging las sentencias de una o varias BDs con la opción
binlog_do_db
(si no añadimos esta opción, se guardarán todas las sentencias de
modificación que afecten a todas las bases de datos). En este caso solo
se guardará las sentencias que se hagan posteriores a un
USE sobre la BD seleccionada.
Tambien podemos seleccionar las BDs a ignorar para registrar las sentencias añadiendo la opción
binlog_ignore_db en nuestro archivo de configuración.
Ejemplo: vamos a establecer un prefijo para nuestro archivo de
Binary Log de
mysql-bin y un
server ID de
1, además vamos a especificar que se guarden en el
binary logging las sentencias que afectan unicamente a la
dbname1 y
dbname2,por lo que el archivo de configuración quedaría:
[mysqld]
log-bin=mysql-bin
server-id=1
binlog_do_db=dbname1
binlog_do_db=dbname2
Después de los cambios, reiniciar el servidor MySQL.
Nota: para transacciones usando InnoDB con transacciones, establecer
innodb_flush_log_at_trx_commit=1 y sync_binlog=1 en el archivo
my.cnf del master.
Nota: aseguraté que la opción
skip-networking no está habilitada, o la sincronización fallará. En algunos sistemas, esta opción se denomina
bind-address to 127.0.0.1, para permitir toda comunicación debemos asignar el valor
bind-address=0.0.0.0
Configuración del SLAVE
Para configurar el SLAVE hemos de establecer un
server ID único
(distinto del MASTER u otros SLAVEs) y para ello necesitamos apagar el
servidor MySQL y editar el archivo de configuración
my.cnf ó
my.ini al igual que en el MASTER.
Ejemplo:
[mysqld]
server-id=2
Después de los cambios, reiniciar el servidor MySQL.
Nota: si estamos configurando varios SLAVEs, el valor de
server ID ha de ser único.
Nota: en ciertas ocasiones hemos de añadir en el archivo
my.cnf ó
my.ini del SLAVE la línea
report-host=hostname-or-IP-of-MASTER
para que el comando 'show slave hosts' ejecutado en el MASTER funcione correctamente.
Creando un usuario para la replicación
Cada SLAVE se conectará al MASTER usando un user/pwd de MySQL, por lo
que habrá que crear tal usuario en el MASTER para dar acceso al(los)
SLAVE(s). Este usuario deberá tener el privilegio
REPLICATION SLAVE.
Se podría crear una cuenta para cada SLAVE o una general para todos. No
es necesario crear una cuenta para la replicación, pero hay que tener
en cuenta que el user/pwd de esta cuenta se almacenará en texto plano en
el archivo
master.info. Por tanto es recomendable crear el usuario específico por cuestiones de seguridad.
Un ejemplo sería:
mysql> CREATE USER 'repli'@'SLAVE-host-or-IP' IDENTIFIED BY 'replipass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repli'@'SLAVE-host-or-IP';
para que el comando 'show slave hosts' ejecutado en el MASTER funcione correctamente.
Nota: si cambiamos el nombre del host, deberemos hacer un FLUSH HOSTS para reiniciar el
host cache de MySQL.
Obtener las coordenadas del Binary Log en el MASTER
Si nuestra BD a replicar contiene datos, antes de replicar deberemos
volcar la información en SLAVE server. Para ello pararemos la ejecución
de sentencias de escritura sobre el MASTER, obtendremos las coordenadas
del Binary Log y haremos un volcado de datos (dump).
Los pasos serán:
1. Entrar en la consola MySQL y bloquear la escritura de tablas:
mysql> FLUSH TABLES WITH READ LOCK;
Nota: mientras no salgamos de la sesión, las tablas permanecerán en modo lectura (READ).
Nota: para tablas InnoDB, el comando
FLUSH TABLES WITH READ LOCK también bloquea las operaciones
COMMIT.
2. En una sesión diferente del MASTER, vamos a obtener las coordenadas del
Binary Log (nombre del actual archivo binary log y su posición de ejecución).
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 107 | pruebarepli | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Nos muestra el nombre del archivo donde guarda las sentencias (
mysql-bin.000003), y la posición desde que se la que se copiarán las nuevas sentencias (107). Además nos muestra las BD a registrar y las BDs a ignorar. Guardas esta información para la configuración del SLAVE.
Copia de seguridad del MASTER e importación en el SLAVE
1. Hacer una copia de la BD
Mientras mantenemos en una sesión el FLUSH TABLES WITH READ LOCK,
ejecutaremos en una sesión distinta mysqldump para hacer un backup de
la BD MASTER:
shell# ./mysqldump --opt --databases pruebarepli -u root -p > pruebarepli_backup.sql
2. Anular el READ LOCK, si está todavía vigente. Para ello podemos
cerrar la sesión abierta con READ LOCK o ejecutar el siguiente comando:
mysql> UNLOCK TABLES;
3. Importar el backup en el SLAVE, utilizaremos el siguiente comando en el servidor SLAVE:
mysql> mysql -u root -p < pruebarepli_backup.sql;
Establecer los parámetros del MASTER en el SLAVE
Lo primero de todo es establecer en el archivo de configuración del SLAVE el
server ID.
Nuestro ejemplo será:
[mysqld]
server-id=2
Si configuramos diferentes SLAVEs, los ID's correspondientes han de ser distintos.
Reiniciamos el servidor del SLAVE.
Continuamos estableciendo los parámetros de comunicación entre el SLAVE y
el MASTER (los cuales hemos ido recopilando en los pasos anteriores),
para lo que ejecutaremos lo siguiente sobre la consola del SLAVE:
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
En nuestro ejemplo será:
mysql> CHANGE MASTER TO
-> MASTER_HOST='IP_servidor',
-> MASTER_USER='repli',
-> MASTER_PASSWORD='replipass',
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=107;
Arrancar el SLAVE
Para finalizar arrancar el SLAVE con el siguiente comando:
mysql> START SLAVE;
Para finalizar mostramos una captura de pantalla del comando
show slave hosts
ejecutado en en la consola del MASTER para corroborar la conexión entre
MASTER-SLAVE. Confirmamos que se produce la actualización de la BD
SLAVE ante una inserción de datos en la BD MASTER.
Source: http://dev.mysql.com/doc/refman/5.7/en/replication.html
