MySQL replicación Master a Slave

Uno de los motivos de implementar replicación de MySQL Master-Slave, puede ser por ejemplo el disponer de una instancia de MySQL Server de nuestra base de datos, que sólo sirva para el acceso a lecturas (SELECT) para diversos fines (estadísticas, backups, repartir lecturas y liberar de carga al MASTER). Vamos a ver como implementarlo en MySQL 5.0.

Metas y  Límites de la replicación

  • Distribución de datos
  • Balanceo de carga
  • Backup
  • HA y failover
  • Testear upgrades de MySQL
  • Una instancia de MySQL SLAVE puede tener sólo un MySQL MASTER
  • Cada SLAVE debe tener un único ID server
  • Un MASTER puede manejar varios SLAVES
  • Un SLAVE puede propagar cambios desde su MASTER y ser a su vez el MASTER de otros SLAVES si habilitas log_slave_updates

Pasos (BREVE)

  • Configurar cuentas de replicación en ambos servidores
  • Configurar master y esclavo (editar my.cnf)
  • Configurar esclavo para conectar y replicar desde el master

Escenario

MASTER = 10.0.0.1, server id 100

SLAVE = 10.0.0.2, server id 101

Asumimos que partimos de un MASTER en servicio, es decir, el SLAVE y MASTER no parten ambos desde cero

 

1. Setup en MASTER

1.1 Creamos una cuenta de replicación con permisos en la BD master

[cc]
# mysql -uroot -p
# mysql>; use mysql;
# mysql>; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@’10.0.0.1%’ IDENTIFIED BY ‘replpassword’;
[/cc]

1.2. Habilitar binary login and server ID (editar my.cnf)

[cc]
# vim /etc/my.cnf

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
server_id=100
[/cc]

1.3. Reiniciar MYSQL
[cc]
service mysqld restart
[/cc]

1.4. Comprobar estado Master
[cc]
mysql>;show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 103874 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
[/cc]

Si obtenemos algo como esto es que el archivo de log binario se ha creado correctamente.

2. Setup SLAVE

2.1 Creamos una cuenta de replicación con permisos en la BD master

[cc]
# mysql -uroot -p
# mysql> use mysql;
# mysql>; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@’10.0.0.2%’ IDENTIFIED BY ‘replpassword’;
[/cc]

*** NOTA: realmente,  el usuario de replicación sólo necesita privilegios REPLICATION SLAVE en el MASTER y no necesita REPLICATION CLIENT, pero para este tutorial, seguiremos estos pasos

2.2. Editamos my.cnf

[cc]
# vim /etc/my.cnf

log-bin=mysql-bin
server_id=101
relay_log=mysql-relay-bin
log_slave_updates=1
read_only=1
[/cc]

  • relay_log: precisa la ruta y nombre del fichero de log relay
  • read _only: evita las writes en el esclavo
  • log_slave_updates: guarda los eventos del esclavo en un log

2.3 Reiniciamos esclavo

[cc]
service mysqld restart
[/cc]

3. Iniciar replicación

Necesitamos tres cosas para sincronizar un SLAVE con su MASTER:

  • Un snapshot de los datos del maestro en algún punto concreto de tiempo
  • El archivo de log actual del master y el byte offset con el log en el punto de tiempo exacto en el que tomaste el snapshot. Nos referiremos a estos dos valores como coordenadas del archivo log porque juntas identifican la posición del archivo de log binario. Puedes encontrar las coordenadas del archivo log del master con el comando SHOW MASTER STATUS.
  • Los archivos logs binarios del master desde ese momento hasta el presente.

Hay muchas formas de aplicar la copia de los datos: cold copy, warm copy, mysqldump, lvm snapshot, etc. En este tutorial cubriremos esta:

  • Coldcopy: parar el master, hacer un trasvase de los archivos físicos del master hacia el slave. Levantar el MASTER otra vez, lo que comenzará un nuevo archivo de log binario y utilizar CHANGE MASTER TO para iniciar el SLAVE al principio del archivo de log binario.

Esta forma tiene un inconveniente MUY CLARO, hay que parar el MASTER en algún momento. Siguiendo la info de http://dev.mysql.com/doc/refman/5.1/en/replication-howto-masterstatus.html tenemos explicado lo mismo de otra forma, vamos a seguir estos pasos:

3.1 Parada de servicio en MASTER y copia de datos
Inicia el cliente de MySQL y haz flush de todas las tablas bloqueando los writes ejecutando. No abandonar la consola de MySQL:

[cc]
mysql>FLUSH TABLES WITH READ LOCK;
[/cc]

Las tablas INNODB también bloquean las operaciones de COMMIT.

Warning: Leave the client from which you issued the FLUSH TABLES statement
running so that the read lock remains in effect. If you exit the client, the lock is released.

A continuación, en la misma consola, obtenemos la posición de nuestro MASTER después del bloqueo de las tablas:
[cc]
mysql> SHOW MASTER STATUS;
+—————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+—————+———-+————–+——————+
| mysql-bin.003 | 73 | test | manual,mysql |
+—————+———-+————–+——————+
[/cc]

La columna FILE muestra el nombre del archivo de log y la posición muestra el offset del archivo. En este ejemplo, el archivo de log binario es mysql-bin.003 y el offset es el 73. Graba esos valores. Tu necesitarás esto más tarde cuando configures el slave. Esto representa las coordenadas del archivo log en las cuales el slave comenzara a procesar las nuevas actualizaciones desde el master.

En otra sesión, empeazamos el volcado de datos a un archivo físico con mysqldump
[cc]
shell> mysqldump –all-databases –lock-all-tables > dbdump.sql
[/cc]

Cuando el proceso finaliza, desbloqueamos las tablas en la primera sesión abierta en los pasos anteriores

[cc]
mysql>UNLOCK TABLES;
[/cc]

De nuevo en el SLAVE, deberemos importar y configurar

[cc]
shell> mysql -uuser -ppassword < dbdump.sql [/cc]

Una vez hecho esto, configuramos el SLAVE y comenzamos la replicación. Recuperamos los datos que guardamos previamente en el MASTER cuando hicimos el SHOW MASTER STATUS (punto 3).

[cc] mysql> CHANGE MASTER TO MASTER_HOST=’10.0.0.1′, MASTER_USER=’repl’, MASTER_PASSWORD=’replpassword’, MASTER_LOG_FILE=’mysql-bin.000001 ‘, MASTER_LOG_POS=’103874 ‘;
mysql> START SLAVE;
[/cc]

Actualización 30/8/2011: en la parte de esta query, MASTER_LOG_POS=nnnnn, no se ponen comillas, si las ponemos nos devuelve un error

Revisamos el estado de nuestro SLAVE
[cc]
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host:10.0.0.1
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 951
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 951
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 382
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

[/cc]

A partir de este momento, los “Second Behind Master” disminuirán progresivamenet hasta 0.

Actualización 11/4/2016:

En caso de no partir desde cero los pasos varían ligeramente

en el slave server

[cc]
mysql > stop slave;
[/cc]

en el master server

[cc]
mysql> RESET MASTER;
mysql> FLUSH TABLES WITH READ LOCK;
[/cc]

hacemos el backup y desbloqueamos tablas, movemos el backup al slave

en el slave server

[cc]
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.00000XXX’, MASTER_LOG_POS=XXXX;
[/cc]

y voilà!

Links

 

5 thoughts on “MySQL replicación Master a Slave

  1. Hola

    dependerá de varias cosas pero la respuesta sería NO. La replicación se compone de I/O por un lado y SQL por otro, dependiendo de la carga de tu infraestructura puede darse el caso de que un SLAVE esté por “detrás” en segundos del MASTER mientras que otro SLAVE esté a 0 segundos. Sin tener en cuenta errores que se puedan producir etc.

    Saludos

  2. Pingback: MySQL 5.5 replicación semi sincrona

  3. Como serían los pasos para replicar solo una selección de tablas suponiendo que los servidores no contienen los mismos datos, salvo ciertas tablas en las que deberian ser iguales.

Leave a Reply

Your email address will not be published. Required fields are marked *