Replicación maestro-esclavo de MySQL en la misma máquina

La replicación de MySQL es un proceso que permite que los datos de un servidor de base de datos MySQL (el maestro) se copien automáticamente a uno o más servidores de base de datos MySQL (los esclavos). Por lo general, se usa para extender el acceso de lectura en varios servidores para escalabilidad, aunque también se puede usar para otros fines, como la conmutación por error o el análisis de datos en el esclavo para no sobrecargar el maestro.

Como la replicación maestro-esclavo es una replicación unidireccional (de maestro a esclavo), solo se usa la base de datos maestra para las operaciones de escritura, mientras que las operaciones de lectura pueden distribuirse en varias bases de datos esclavas. Lo que esto significa es que si se utiliza la replicación maestro-esclavo como solución de escalamiento horizontal, debe tener al menos dos orígenes de datos definidos, uno para operaciones de escritura y el segundo para operaciones de lectura.

Los desarrolladores de MySQL generalmente trabajan en una sola máquina y tienden a tener todo su entorno de desarrollo en esa máquina, con la lógica de que no dependen de una red o conexión a Internet. Si se necesita una replicación maestro-esclavo porque, por ejemplo, necesitan probar la replicación en un entorno de desarrollo antes de implementar los cambios en otro lugar, deben crearla en la misma máquina. Si bien la configuración de una sola instancia de MySQL es bastante simple, debemos hacer un esfuerzo adicional para configurar una segunda y luego una replicación maestro-esclavo.

Para este tutorial paso a paso, elegí Ubuntu Linux como sistema operativo host y los comandos provistos son para ese sistema operativo. Si desea configurar su replicación maestro-esclavo de MySQL en algún otro sistema operativo, deberá realizar modificaciones para sus comandos específicos. Sin embargo, los principios generales para configurar la replicación maestro-esclavo de MySQL en la misma máquina son los mismos para todos los sistemas operativos.

Instalación de la Primera Instancia de MySQL

Si ya tiene una instancia de la base de datos MySQL instalada en su máquina, puede omitir este paso.

La forma más fácil de instalar MySQL en Ubuntu es ejecutar el siguiente comando desde un indicador de terminal:

 sudo apt-get install mysql-server

Durante el proceso de instalación, se le pedirá que establezca una contraseña para el usuario root de MySQL.

Configurando mysqld_multi

Para administrar dos instancias de MySQL en la misma máquina de manera eficiente, necesitamos usar mysqld_multi .

El primer paso para configurar mysqld_multi es la creación de dos grupos [mysqld] separados en el archivo my.cnf existente. La ubicación predeterminada del archivo my.cnf en Ubuntu es /etc/mysql/ . Por lo tanto, abra el archivo my.cnf con su editor de texto favorito y cambie el nombre del grupo [mysqld] existente a [mysqld1] . Este grupo renombrado se usará para la configuración de la primera instancia de MySQL y también se configurará como una instancia maestra. Como en la replicación maestro-esclavo de MySQL, cada instancia debe tener su propio server-id único, agregue la siguiente línea en el grupo [mysqld1] :

 server-id = 1

Dado que necesitamos un grupo [mysqld] separado para la segunda instancia de MySQL, copie el grupo [mysqld1] con todas las configuraciones actuales y péguelo a continuación en el mismo archivo my.cnf . Ahora, cambie el nombre del grupo copiado a [mysqld2] y realice los siguientes cambios en la configuración del esclavo:

 server-id = 2 port = 3307 socket = /var/run/mysqld/mysqld_slave.sock pid-file = /var/run/mysqld/mysqld_slave.pid datadir = /var/lib/mysql_slave log_error = /var/log/mysql_slave/error_slave.log relay-log = /var/log/mysql_slave/relay-bin relay-log-index = /var/log/mysql_slave/relay-bin.index master-info-file = /var/log/mysql_slave/master.info relay-log-info-file = /var/log/mysql_slave/relay-log.info read_only = 1

Para configurar la segunda instancia de MySQL como esclava, configure server-id en 2, ya que debe ser diferente al id del servidor del maestro.

Dado que ambas instancias se ejecutarán en la misma máquina, establezca el port para la segunda instancia en 3307 , ya que debe ser diferente del puerto utilizado para la primera instancia, que es 3306 de manera predeterminada.

Para permitir que esta segunda instancia use los mismos archivos binarios de MySQL, debemos establecer diferentes valores para socket , pid-file , datadir y log_error .

También necesitamos habilitar relay-log para usar la segunda instancia como esclavo (parámetros relay-log , relay-log-index y relay-log-info-file ), así como configurar master-info-file .

Finalmente, para que la instancia esclava sea de solo lectura, el parámetro read_only se establece en 1 . Debe tener cuidado con esta opción, ya que no evita por completo los cambios en el esclavo. Incluso cuando read_only se establece en 1 , las actualizaciones solo se permitirán a los usuarios que tengan el privilegio SUPER . MySQL ha introducido recientemente el nuevo parámetro super_read_only para evitar que los usuarios de SUPER realicen cambios. Esta opción está disponible con la versión 5.7.8.

Además de los grupos [mysqld1] y [mysqld2] , también debemos agregar un nuevo grupo [mysqld_multi] al archivo my.cnf :

 [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = multi_admin password = multipass

Una vez que instalemos la segunda instancia de MySQL e iniciemos ambas, otorgaremos los privilegios apropiados al usuario multi_admin para poder cerrar las instancias de MySQL.

Crear nuevas carpetas para la segunda instancia de MySQL

En el paso anterior preparamos el archivo de configuración para la segunda instancia de MySQL. En ese archivo de configuración se utilizan dos nuevas carpetas. Se deben usar los siguientes comandos de Linux para crear esas carpetas con los privilegios apropiados:

 mkdir -p /var/lib/mysql_slave chmod --reference /var/lib/mysql /var/lib/mysql_slave chown --reference /var/lib/mysql /var/lib/mysql_slave mkdir -p /var/log/mysql_slave chmod --reference /var/log/mysql /var/log/mysql_slave chown --reference /var/log/mysql /var/log/mysql_slave

Configuración de seguridad adicional en AppArmor

En algunos entornos Linux, se necesita la configuración de seguridad de AppArmor para ejecutar la segunda instancia de MySQL. Al menos, son necesarios en Ubuntu.

Para configurar correctamente AppArmor, edite el archivo /etc/apparmor.d/usr.sbin.mysqld con su editor de texto favorito, agregue las siguientes líneas:

 /var/lib/mysql_slave/ r, /var/lib/mysql_slave/** rwk, /var/log/mysql_slave/ r, /var/log/mysql_slave/* rw, /var/run/mysqld/mysqld_slave.pid rw, /var/run/mysqld/mysqld_slave.sock w, /run/mysqld/mysqld_slave.pid rw, /run/mysqld/mysqld_slave.sock w,

Después de guardar el archivo, reinicie la máquina para que estos cambios surtan efecto.

Instalación de la Segunda Instancia de MySQL

Se pueden seguir varios enfoques diferentes para la instalación de la segunda instancia de MySQL. El enfoque presentado en este tutorial utiliza los mismos archivos binarios de MySQL que el primero, con archivos de datos separados necesarios para la segunda instalación.

Dado que ya hemos preparado el archivo de configuración y las carpetas necesarias y los cambios de seguridad en los pasos anteriores, el paso final de instalación de la segunda instancia de MySQL es la inicialización del directorio de datos de MySQL.

Ejecute el siguiente comando para inicializar el nuevo directorio de datos de MySQL:

 mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave

Una vez que se inicializa el directorio de datos de MySQL, puede iniciar ambas instancias de MySQL utilizando el servicio mysqld_multi :

 mysqld_multi start

Establezca la contraseña root para la segunda instancia de MySQL utilizando mysqladmin con el host y el puerto apropiados. Tenga en cuenta que si no se especifica el host y el puerto, mysqladmin se conectará a la primera instancia de MySQL de forma predeterminada:

 mysqladmin --host=127.0.0.1 --port=3307 -u root password rootpwd

En el ejemplo anterior, establecí la contraseña en "rootpwd", pero se recomienda usar una contraseña más segura.

Configuración adicional de mysqld_multi

Al final de la sección "Configuración de mysqld_multi", escribí que otorgaremos los privilegios apropiados al usuario multi_admin más adelante, así que ahora es el momento de hacerlo. Necesitamos darle a este usuario los privilegios apropiados en ambas instancias, así que primero conectémonos a la primera instancia:

 mysql --host=127.0.0.1 --port=3306 -uroot -p

Una vez que haya iniciado sesión, ejecute los siguientes dos comandos:

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

Salga del cliente MySQL y conéctese a la segunda instancia:

 mysql --host=127.0.0.1 --port=3307 -uroot -p

Una vez que haya iniciado sesión, ejecute los mismos dos comandos que arriba:

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

Salga del cliente MySQL.

Inicie ambas instancias de MySQL automáticamente en el arranque

El último paso para configurar mysqld_multi es la instalación del script de arranque automático en init.d

Para hacer eso, cree un nuevo archivo llamado mysqld_multi en /etc/init.d y asígnele los privilegios apropiados:

 cd /etc/init.d touch mysqld_multi chmod +x /etc/init.d/mysqld_multi

Abra este nuevo archivo con su editor de texto favorito y copie el siguiente script:

 #!/bin/sh ### BEGIN INIT INFO # Provides: scriptname # Required-Start: $remote_fs $syslog # Required-Stop: $remote_fs $syslog # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: Start daemon at boot time # Description: Enable service provided by daemon. ### END INIT INFO bindir=/usr/bin if test -x $bindir/mysqld_multi then mysqld_multi="$bindir/mysqld_multi"; else echo "Can't execute $bindir/mysqld_multi"; exit; fi case "$1" in 'start' ) "$mysqld_multi" start $2 ;; 'stop' ) "$mysqld_multi" stop $2 ;; 'report' ) "$mysqld_multi" report $2 ;; 'restart' ) "$mysqld_multi" stop $2 "$mysqld_multi" start $2 ;; *) echo "Usage: $0 {start|stop|report|restart}" >&2 ;; esac

Agregue el servicio mysqld_multi a los niveles de runlevels predeterminados con el siguiente comando:

 update-rc.d mysqld_multi defaults

Reinicie su máquina y verifique que ambas instancias de MySQL se estén ejecutando utilizando el siguiente comando:

 mysqld_multi report

Configuración de replicación maestro-esclavo

Ahora, cuando tengamos dos instancias de MySQL ejecutándose en la misma máquina, configuraremos la primera instancia como maestra y la segunda como esclava.

Una parte de la configuración ya se realizó en el capítulo “Configuración de mysqld_multi”. El único cambio restante en el archivo my.cnf es configurar el registro binario en el maestro. Para hacer esto, edite el archivo my.cnf con los siguientes cambios y adiciones en el grupo [mysqld1] :

 log_bin = /var/log/mysql/mysql-bin.log innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 binlog-format = ROW

Reinicie la instancia maestra de MySQL para que estos cambios surtan efecto:

 mysqld_multi stop 1 mysqld_multi start 1

Para que el esclavo se conecte al maestro con los privilegios de replicación correctos, se debe crear un nuevo usuario en el maestro. Conéctese a la instancia maestra mediante el cliente MySQL con el host y el puerto apropiados:

 mysql -uroot -p --host=127.0.0.1 --port=3306

Cree un nuevo usuario para la replicación:

 mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'replication'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

Salga del cliente MySQL.

Ejecute el siguiente comando para crear un volcado de los datos maestros:

 mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases --master-data=2 > replicationdump.sql

Aquí usamos la opción --master-data=2 para tener un comentario que contenga una instrucción CHANGE MASTER dentro del archivo de respaldo. Ese comentario indica las coordenadas de replicación en el momento de la copia de seguridad, y necesitaremos esas coordenadas más adelante para la actualización de la información maestra en la instancia esclava. Aquí está el ejemplo de ese comentario:

 -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=349;

Importe el volcado que creó en el paso anterior a la instancia esclava:

 mysql -uroot -p --host=127.0.0.1 --port=3307 < replicationdump.sql

Finalmente, para que la instancia esclava se conecte a la instancia maestra, la información maestra en la esclava debe actualizarse con los parámetros de conexión apropiados.

Conéctese a la instancia esclava mediante el cliente MySQL con el host y el puerto apropiados:

 mysql -uroot -p --host=127.0.0.1 --port=3307

Ejecute el siguiente comando para actualizar la información maestra (tome las coordenadas de replicación del archivo de volcado replicationdump.sql , como se explicó anteriormente):

 mysql> CHANGE MASTER TO -> MASTER_HOST='127.0.0.1', -> MASTER_USER='replication', -> MASTER_PASSWORD='replication', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=349;

Ejecute el siguiente comando para iniciar el esclavo:

 mysql> START SLAVE;

Ejecute el siguiente comando para verificar que la replicación esté activa y funcionando:

 mysql> SHOW SLAVE STATUS \G

Felicidades. Su replicación maestro-esclavo de MySQL en la misma máquina ahora está configurada correctamente.

Envolver

Tener una replicación maestro-esclavo configurada en su entorno de desarrollo es útil si la necesita para una solución de escalamiento horizontal en el entorno de producción. De esta forma, también tendrá fuentes de datos separadas configuradas para operaciones de escritura y lectura para que pueda probar localmente que todo funciona como se espera antes de una implementación adicional.

Además, es posible que desee tener varias instancias de esclavos configuradas en la misma máquina para probar el balanceador de carga que distribuye las operaciones de lectura a varios esclavos. En ese caso, puede usar este mismo manual para configurar otras instancias esclavas repitiendo los mismos pasos.