Seguridad, Linux y demás frikerias

Replicación Master-Slave MySQL

MySQL permite la configuración en modo Master-Slave, de este modo dispondremos de una infraestructura mucho mas escalable que con un único servidor, las principales ventajas son:

Distribución de carga de lectura.
Backups desde uno de los slaves sin interrumpir el servicio.
Spare server que podría actuar como master en caso de necesitarlo.

Antes de comenzar debemos tener en cuenta que versiones nuevas de MySQL siempre podrán actuar como slave de un master anterior pero versiones antiguas puede que no puedan actuar como slave de un servidor mas nuevo.
A partir de la versión 5.1 se puede configurar la replicación por fila ahorrando de este modo bandwith.

El proceso de replicación es:

Master escribe en su fichero de binary log.
Slave copia los eventos del Master a su relay log.
Slave replica los eventos del relay log a la base de datos.

Master --> BinaryLog --RED--> RelayLog --> Slave

Desde el slave se puede consultar esta info con:

mysql> SHOW SLAVE STATUSG;

Master_Log_File: Fichero en el que está escribiendo el master las operaciones actuales de MySQL, hasta donde tiene constancia el hilo de replicación del slave
Read_Master_Log_Pos: Posición en el Master_Log_File

Master --> BinaryLog --RED--> RelayLog --> Slave
               |
               v
           FILE/POS

Relay_Log_File: Fichero donde se reciben las operaciones del master en el slave, de aquí se leerá para ejecutarlas en local en el slave
Relay_Log_Pos: Posición dentro del relay log por la que se está ejecutando actualmente en el slave

Master --> BinaryLog --RED--> RelayLog --> Slave
                                 |
                                 v
                             FILE/POS

Relay_Master_Log_File: Fichero de ejecución en el slave respecto al master
Exec_Master_Log_Pos: Posición de Relay_Master_Log_File

Master --> BinaryLog --RED--> RelayLog --> Slave
              |
              |--------------------------> FILE/POS

Relay_Log_Space: Tamaño total de todos los ficheros de relay en caso de tener mas de uno

Master --> BinaryLog --RED--> RelayLog1/2/..N --> Slave
                                    |
                                    v
                                   SUM

NOTA: Los comandos que se envían por el socket de replicación van en serie, por lo tanto comandos que en el master se ejecutan en paralelo en el esclavo lo harán uno detrás de otro.

CREAMOS LA CUENTA DE REPLICACIÓN EN EL MASTER Y EL SLAVE:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO rep_user@'192.168.0.%' IDENTIFIED BY 'XXXX';

NOTA: No es posible dar privilegios de replica solo a una base de datos GRANT REPLICATION SLAVE, REPLICATION CLIENT ON basede_datos.*

MySQL lo trata como un privilegio global.

En el master realmente se debe de configurar el usuario con permisos de REPLICATION SLAVE, pero es mas fácil crear un solo usuario con los dos permisos y así ejecutar el mismo comando tanto en el master como en el slave, además esto nos aporta un ventaja adicional, si en algún momento se cambian los papeles se podrá hacer sin problemas ya que el usuario con permisos de REPLICATION SLAVE estará creado en el Slave.

HABILITAMOS EL BINARY LOGGING Y LE ASIGNAMOS UN ID EN EL MASTER:

vi /etc/mysql/my.cnf

bind-address            = server1_IP
log_bin                 = SERVER_NAME-bin.log
server_id = 1


/etc/init.d/mysql restart

Comprobamos que el fichero de binarylog esté creado:

mysql> SHOW MASTER STATUS;

CONFIGURAMOS EL SLAVE PARA QUE SINCRONIZE:

vi /etc/mysql/my.cnf

bind-address            = server2_IP
log_bin                 = SERVER_NAME-bin.log
server_id             = 2
relay_log             = SERVER_NAME-relay-bin
log_slave_updates     = 1 
read_only             = 1

NOTA: log_slave_updates --> Este parámetro indica al slave que debe generar binlogs incluso de los eventos recibidos desde el Master, esta opción es necesaria en caso de que este slave también actúe como master de otro server. Si este servidor solo va a ser slave podemos prescindir de las opciones log_bin y log_slave_updates, al ser slave no tiene porque generar binlogs.

Si solo queremos sincronizar una DB.

replicate-do-db=DB

Si se quiere mas de una DB se repite la línea por cada una de ellas!!!

También podemos ignorar ciertas tablas:

replicate-ignore-table=DB.TABLE

/etc/init.d/mysql restart

INICIAMOS LA SINCRONIZACIÓN:

mysql> CHANGE MASTER TO MASTER_HOST='server1',
    -> MASTER_USER='rep_user',
    -> MASTER_PASSWORD='XXXX',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=0;

mysql> START SLAVE;

Comprobamos que está intentando sincronizar:

mysql> SHOW SLAVE STATUSG
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

CHEQUEAMOS EN AMBOS SERVIDORES QUE EXISTA LA CONEXIÓN DE REPLICACIÓN:

En el Master vemos el thread de la conexión del Slave:

mysql> SHOW PROCESSLISTG;

En el Slave veremos el thread de la conexión y el de escritura a la base de datos:

mysql> SHOW PROCESSLISTG;

El time del thread de escritura nos indica si hace tiempo que no se reciben datos del Master.

NOTA: Es importante destacar que en este caso se ha configurado el slave para que lea desde la posición 0 del master ya que es un servidor recién instalado.

Normalmente tenemos un master funcionando y necesitamos montar un slave a partir de este, la posición no será 0 ya que se han ejecutado sentencias en la DB, es tal caso deberíamos seguir los siguientes pasos para montar el Slave:

Filtrar tráfico en el master
Anotar la salida de: mysql> SHOW MASTER STATUSG;
Dumpear las dbs
Desfiltrar
Copiar el dump al slave
Montar el slave enganchando en la posción guardada

Hay ocasiones en las que la replicación se rompe por la query en si, es posible escapar una sentencia determinada y con un poco de suerte la replicación seguirá adelante.

mysql> SHOW SLAVE STATUS G;

Veremos la sentencia que cascó, podemos escaparla y luego intentar insertarla a mano.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS G;

Autor: Kr0m -- 23/04/2014 17:36:10