Tutorial: Sincronizando bases de datos MySQL

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).

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

Aplicaciones web

viernes, 7 de junio de 2013

Replicar dos bases de datos

Tutorial: Sincronizando bases de datos MySQL

No hay comentarios:

Publicar un comentario