Replicación y ditribución de carga de MySQL

Lunes, junio 15th, 2009

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