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();
}

…