Replicación y ditribución de carga de MySQL

Replicación es la copia sincronizada entre dos o más servidores de bases de datos, de forma de que cualquiera de ellos puede entregar los mismos resultados a sus clientes. Se basa en un esquema «maestro-esclavos», en el que el maestro mantiene la base de datos original y los esclavos las copias.

El funcionamiento es el siguiente: los servidores esclavos se conectan al maestro para consultar sus logs y asg mantenerse informados de las operaciones de modificación que ha realizado (insert, delete, update, …) para a su vez poder realizarlas también ellos y mantener una replica exacta de la base de datos del servidor maestro. Los servidores esclavos sólo sirven para consultas.

Esto nos proporciona:

  • Replica automática de los datos en máquinas remotas.

  • Balanceo de la carga (de las consultas).

  • Alta disponibilidad mediante redundancia de servidores.
  • Si cae el maestro

  • Copias de seguridad en esclavos sin interrumpir maestro.
Preparación:
  • Maestro y esclavo con versiones compatibles de SQL – Maestro y esclavo permiten conexiones externas – Maestro y esclavo con la misma base de datos – Maestro y esclavo no atienden modificaciones durante la configuración Configuración maestro:

1. Crear un usuario para la replicación:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO replicante@esclavo.midominio.org IDENTIFIED BY ‘contraseña’

2.  Modificar /etc/mysql/my.cnf:

    [mysqld]
    log-bin
    server-id = 1

    /etc/init.d/mysql restart

    3. Copiar la base de datos a los esclavos:

    FLUSH TABLES WITH READ LOCK;
    SYSTEM mysqldump -u root -p –opt world | mysql -h esclavo.midominio.org -u root -pcontraseña mundo
    SHOW MASTER STATUS;
    UNLOCK TABLES;

    ejemplo resultado de SHOW MASTER: fichero maestro-bin.001 en posición 76

    Configuración esclavo

    1. Crear un usuario para la replicación:

    GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
    TO replicante@esclavo.midominio.org IDENTIFIED BY ‘contraseña’;

    2. Modificar /etc/mysql/my.cnf:

    [mysqld]
    server-id = 2

    /etc/init.d/mysql restart

    3. Iniciar la replicación:

    CHANGE MASTER TO MASTER_HOST = ‘maestro.midominio.org’,
    MASTER_USER = ‘replicante’, MASTER_PASSWORD = ‘contraseña’,
    MASTER_LOG_FILE = ‘maestro-bin.001’, MASTER_LOG_POS = 76;

    START SLAVE;

    Tareas administrativas más comunes

    * Ver el estado de la replicación desde el esclavo:

    SHOW MASTER LOGS;
    SHOW MASTER STATUS;
    SHOW SLAVE STATUS;

    * Ver el estado de la replicación desde el maestro:

    SHOW MASTER LOGS;
    SHOW MASTER STATUS;
    SHOW SLAVE HOSTS;
    SHOW PROCESSLIST;

    * Parar e iniciar un esclavo (se ejecuta en el esclavo):

    STOP SLAVE;
    START SLAVE;

    Código PHP para balancear la carga MYSQL de las consultas entre los esclavos, evitar servidores caidos, …

    $mysqlhosts[0] = «esclavo1.aula207.org»;
    $mysqlhosts[1] = «esclavo2.aula207.org»;
    $intentos = 0;
    while($intentos<10 && !$connID) {
    $mysqlhost = $mysqlhosts[rand(0,1)];
    $connID=mysql_connect($mysqlhost, «usuario», «contraseña»);
    $intentos++;
    }
    if(!$connID) {
    echo «

    Sorry, no database server found.

    «;
    exit();
    }

    Lecturas para profundizar: – http://dev.mysql.com/doc/refman/5.0/es/replication.html – http://dev.mysql.com/doc/refman/5.0/es/replication-sql.html – http://dev.mysql.com/books/hpmysql-excerpts/ch07.html


    4 respuestas a «Replicación y ditribución de carga de MySQL»

    1. Excelente tutorial, tengo que probarlo! Estaba buscando como realizar un sistema de balanceo de carga en Plesk 11, esta forma creo que me valdría con el mysql, sabes como podría hacerlo con el tema de los ficheros???

      Muchas gracias por adelantado, por cierto el blog es excelente. 😉

    Deja una respuesta

    Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

    Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.