Como montar replicación en master-slave con MySQL

Mediante la replicación master-slave de MySQL podemos repartir las lecturas de la base de datos entre varios hosts. Vamos a ver como montar un slave de MySQL.

Primero de todo debemos tener habilitado los binary logs y definir un server-id único dentro del entorno de replicación. Para hacer esto deberemos editar el my.cnf añadiendo:

server-id=1
log-bin = mysql-bin
sync_binlog                     = 1
max-binlog-size                 = 500M
expire_logs_days                = 4
innodb_flush_log_at_trx_commit  = 1

En el slave debemos añadir también un server-id único:

server-id=2
relay-log               = mysqld-relay-bin
max-relay-log-size      = 500M
relay_log_purge         = 1

Deberemos reiniciar los MySQL para aplicar los cambios. A continuación deberemos crear un usuario para la replicación en el master:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'secretoreplicacion';
Query OK, 0 rows affected (0.04 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

A continuación deberemos realizar la copia inicial de la base de datos. Se puede hacer de muchas formas, por ejemplo mediante snapshots:

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |     1284 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Creamos un snapshot y copiamos los datos del datadir del master al datadir del slave:

$ lvcreate -L16G -s -n mysql.snap /dev/local/mysql

Una vez tenemos los datos podemos liberar el LOCK, o si lo hacemos con un snapshot inmediatamente después de crearlo:

mysql> UNLOCK TABLES;

En el slave deberemos indicar los datos del master para empezar la replicación:

mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Los datos que hemos sacado del SHOW MASTER STATUS deberemos pasárselos al slave:

CHANGE MASTER TO MASTER_HOST='192.168.4.1', MASTER_USER='slave', MASTER_PASSWORD='secretoreplicacion', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1284, MASTER_PORT=3306;

A continuación iniciamos la replicación mediante el siguiente comando:

START SLAVE;

Mediante SHOW SLAVE STATUS podemos ver el estado del slave:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 951
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000002
             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)

Veremos como poco a poco se va reduciendo el “Seconds_Behind_Master” hasta estar sincronizados con las escrituras que se hayan hecho mientras copiabamos los datafiles.

        Seconds_Behind_Master: 0

A continuación, podemos hacer una prueba con los siguientes comandos en el master:

mysql> create database lolme;
Query OK, 1 row affected (0.00 sec)

mysql> use lolme;
Database changed
mysql> create table aaa(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into aaa values(2);
Query OK, 1 row affected (0.00 sec)

A continuación en el slave podremos comprobar como se han replicado:

mysql> use lolme;
Database changed
mysql> select * from aaa;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)