Este tutorial describe una configuración de MySQL replicada (replicación Mater/Master) con 2 nodos donde los datos se pueden leer y escribir en ambos nodos al mismo tiempo. MySQL se encarga de replicar los datos en el otro nodo y se asegura de que las claves primarias de incremento automático no colisionen.
Desde la versión 5, MySQL viene con soporte incorporado para la replicación maestro-maestro, resolviendo el problema que puede ocurrir con las claves autogeneradas. En versiones anteriores de MySQL, el problema con la replicación maestro-maestro era que los conflictos surgían inmediatamente si el nodo A y el nodo B insertaban una clave de incremento automático en la misma tabla. Las ventajas de la replicación maestro-maestro sobre la replicación maestro-esclavo tradicional son que no tiene que modificar sus aplicaciones para hacer accesos de escritura solo al maestro, y que es más fácil proporcionar alta disponibilidad porque si el maestro falla, todavía tienes el otro maestro.
1 nota preliminar
En este tutorial, mostraré cómo replicar la base de datos exampledb desde el servidor server1.example.com con la dirección IP 192.168.1.101 al servidor server2.example.com con la dirección IP 192.168.1.102 y viceversa. Cada sistema es esclavo del otro maestro y maestro del otro esclavo al mismo tiempo. Ambos sistemas ejecutan Debian 8; sin embargo, la configuración debería aplicarse a casi todas las distribuciones con pocas o ninguna modificación.
2 Instalación de MySQL 5.5
Si MySQL aún no está instalado en el servidor 1 y el servidor 2, instálelo ahora:
servidor1/servidor2:
apt-get -y install mysql-server-5.5 mysql-client-5.5
Para asegurarnos de que la replicación funcione, debemos hacer que MySQL escuche en todas las interfaces, por lo tanto, comentamos la línea bind-address =127.0.0.1 en /etc/mysql/my.cnf:
servidor1/servidor2:
nano /etc/mysql/my.cnf
[...] # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 [...]
Reinicie MySQL después:
servidor1/servidor2:
service mysql restart
Entonces consulte con
servidor1/servidor2:
netstat -tap | grep mysql
que MySQL realmente está escuchando en todas las interfaces:
netstat -tap | grep mysql
tcp 0 0 *:mysql *:* LISTEN 15437/mysqld
server1:~#
Ahora configuramos un usuario de replicación slave2_user que puede ser utilizado por server2 para acceder a la base de datos MySQL en server1.
servidor1:
Inicie sesión en el shell de MySQL:
mysql --defaults-file=/etc/mysql/debian.cnf
En el shell de MySQL, ejecute los siguientes comandos:
servidor1:
GRANT REPLICATION SLAVE ON *.* TO [email protected]'%' IDENTIFIED BY 'secretpassword';
FLUSH PRIVILEGES;
quit;
Reemplace la palabra "contraseña secreta " con una contraseña segura de su elección. Ahora hacemos los dos últimos pasos de nuevo en el servidor2:
servidor2:
mysql --defaults-file=/etc/mysql/debian.cnf
GRANT REPLICATION SLAVE ON *.* TO [email protected]'%' IDENTIFIED BY 'secretpassword';
FLUSH PRIVILEGES;
quit;
Reemplace la palabra "contraseña secreta " con una contraseña segura aquí también. Anote las contraseñas, ya que las necesitaremos más adelante.
3 Algunas Notas
A continuación, asumiré que ambos servidores MySQL están vacíos (aún no contienen ninguna base de datos excepto la base de datos 'mysql').
Si ese no es el caso en su servidor, entonces debe bloquear y volcar las bases de datos en el primer servidor e importarlas en el segundo antes de continuar. No desbloquee las bases de datos antes de configurar la replicación. A continuación, algunos comandos que muestran cómo copiar todas las bases de datos a un nuevo servidor en caso de que no comience con una configuración de MySQL "limpia".
Ejemplo de cómo bloquear todas las tablas de la base de datos en una base de datos MySQL.
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;
Ejemplo de cómo volcar todas las bases de datos en un archivo all_databases.sql.
mysqldump --defaults-file=/etc/mysql/debian.cnf -cCeQ --hex-blob --quote-names --routines --events --triggers --all-databases -r all_databases.sql
Ejemplo de cómo importar todas las tablas en el segundo servidor desde el archivo all_databses.sql.
mysql --defaults-file=/etc/mysql/debian.cnf < all_databases.sql
4 Configuración de la replicación
Ahora configuramos la replicación maestro-maestro en /etc/mysql/my.cnf. Las opciones de configuración cruciales para la replicación maestro-maestro son auto_increment_increment y auto_increment_offset:
- auto_increment_increment controla el incremento entre valores AUTO_INCREMENT sucesivos.
- auto_increment_offset determina el punto de partida para los valores de la columna AUTO_INCREMENT.
Supongamos que tenemos N nodos MySQL (N=2 en este ejemplo), entonces auto_increment_increment tiene el valor N en todos los nodos, y cada nodo debe tener un valor diferente para auto_increment_offset (1, 2, ..., N).
Ahora configuremos nuestros dos nodos MySQL:
servidor1:
nano /etc/mysql/my.cnf
Busque la sección que comienza con [mysqld] y coloque las siguientes opciones (comentando todos los conflictos existentes). opciones):
[...] [mysqld]
# Unique Server ID
server-id = 1
# Do not replicate the following databases
binlog-ignore-db = mysql
replicate-ignore-db = mysql
# Auto increment offset
auto-increment-increment = 2
# Do not replicate sql queries for the local server ID
replicate-same-server-id = 0
# Beginne automatisch inkrementelle Werte mit 1
auto-increment-offset = 1
# Delete binlog data after 10 days
expire_logs_days = 10
# Max binlog size
max_binlog_size = 500M
# Binlog file path
log_bin = /var/log/mysql/mysql-bin.log
[...]
Luego reinicie MySQL:
servidor1:
service mysql restart
Ahora haz lo mismo en el servidor2:
servidor2:
nano /etc/mysql/my.cnf
[...]
# Unique Server ID
server-id = 2
# Do not replicate the following databases
binlog-ignore-db = mysql
replicate-ignore-db = mysql
# Auto increment offset
auto-increment-increment = 2
# Do not replicate sql queries for the local server ID
replicate-same-server-id = 0
# Beginne automatisch inkrementelle Werte mit 1
auto-increment-offset = 2
# Delete binlog data after 10 days
expire_logs_days = 10
# Max binlog size
max_binlog_size = 500M
# Binlog file path
log_bin = /var/log/mysql/mysql-bin.log
[...]
servidor2:
service mysql restart
A continuación, bloqueamos la base de datos exampledb en el servidor 1, averiguamos el estado maestro del servidor 1, creamos un volcado SQL de exampledb (que importaremos a exampledb en el servidor 2 para que ambas bases de datos contengan los mismos datos) y desbloqueamos la base de datos para que se puede usar de nuevo:
servidor2:
Ahora comenzamos la replicación en el Servidor 2. Abra el shell de MySQL:
mysql --defaults-file=/etc/mysql/debian.cnf
Y ejecute el siguiente comando SQL para activar la replicación del servidor1 al servidor2:
CHANGE MASTER TO MASTER_HOST='192.168.1.101', MASTER_USER='repl', MASTER_PASSWORD='secretpassword';
Reemplazar contraseña secreta con la contraseña para el repl Usuario de MySQL que ha establecido en el capítulo 2.
Ahora verifique el estado del esclavo ejecutando el comando "mostrar estado del esclavo\G" en el shell de MySQL.
show slave status\G
El resultado será similar a este:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 107
Relay_Log_Space: 410
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
Las Líneas que debes revisar son estas:
Master_Host: 192.168.1.101
Master_User: repl
Master_Port: 3306
Master_Log_File: mysql-bin.000001
Relay_Log_File: mysqld-relay-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Ahora inicie la replicación con este comando en el shell de MySQL:
start slave;
y luego verifique el estado del esclavo nuevamente:
show slave status\G
Las siguientes dos líneas deberían mostrar "sí" ahora:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Si "Seconds_Behind_Master" no es 0, espere unos segundos y verifique el estado nuevamente. Este campo muestra si el maestro y el esclavo están sincronizados.
Para el siguiente paso, necesitamos saber los valores de "Master_Log_File" y "Read_Master_Log_Pos" el comando "show slave status\G". En mi caso estos son:
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Anote los valores que obtiene en su servidor, los necesitamos para el siguiente paso en el servidor 1.
Luego puede dejar el shell de MySQL:
quit
servidor1:
Seguimos en el primer servidor, abrimos el shell de MySQL en el servidor1:
mysql --defaults-file=/etc/mysql/debian.cnf
Y ejecute el siguiente comando de MySQL:
CHANGE MASTER TO MASTER_HOST='192.168.1.102', MASTER_USER='repl', MASTER_PASSWORD='secretpassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
Tienes que reemplazar algunas cosas en el comando anterior:
- La dirección IP tiene que ser la IP de su segundo servidor MySQL.
- La contraseña "secretpassword" tiene que ser la que ha elegido en el capítulo 2 para la respuesta del usuario.
- El MASTER_LOG_FILE y MASTER_LOG_POS tienen que ser los valores que hemos anotado en el último paso.
Ahora consulte con:
show slave status\G
en el shell de MySQL si no hay errores.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.102
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 107
Relay_Log_Space: 107
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
Y comienza el esclavo.
start slave;
Vuelva a comprobar el estado del esclavo:
show slave status\G
Las siguientes dos líneas deberían mostrar "sí" ahora:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Luego puede dejar el shell de MySQL:
quit
Si nada salió mal, la replicación maestro-maestro de MySQL ahora debería estar funcionando. Si no es así, consulte /var/log/syslog para ver si hay errores de MySQL en el servidor 1 y el servidor 2.
5 Probar la replicación
Ahora es el momento de probar nuestra configuración de replicación. Crearé una base de datos exampledb1 en el servidor 1 y luego comprobaré en el servidor 2 si la base de datos se ha replicado en el segundo servidor:
servidor1:
Inicie sesión en la consola MySQL en el servidor 1 y cree la base de datos:
mysql --defaults-file=/etc/mysql/debian.cnf
CREATE DATABASE exampledb1;
servidor2
Ahora inicie sesión en la consola de MySQL en el servidor 2 y verifique si el ejemplo db1 existe allí ahora:
mysql --defaults-file=/etc/mysql/debian.cnf
show databases;
Como podemos ver, la nueva base de datos también aparece en el servidor2.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| exampledb1 |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
A continuación, probaré si la replicación también funciona en la otra dirección. Todavía estamos conectados en server2 y creamos allí una base de datos exampledb2:
CREATE DATABASE exampledb2;
Ahora regrese al servidor1 y ejecute "mostrar bases de datos" en la consola de MySQL:
servidor1
show databases;
El resultado muestra nuestra nueva base de datos exampledb2, por lo que la replicación funciona en ambas direcciones.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| exampledb1 |
| exampledb2 |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.01 sec)
6 Enlaces
- MySQL:http://www.mysql.com
- Debian:http://www.debian.org