Réplica maestro esclavo de una base de datos MariaDB o MySQL

Una de las buenas prácticas de un entorno de base de datos, es replicar la base de datos a otro servidor diferente por si en el principal hubiese alguna incidencia.

En MariaDB podemos configurar réplicas de base de datos mediante el uso de los binlogs. Ya hablé de ello en el artículo Habilitar los logs binarios en MySQL o MariaDB.

En este post voy a mostrar cómo podemos configurar la réplica de una base de datos MariaDB en modo maestro-esclavo (master-slave), que consiste en que tenemos una base de datos principal, que está dando servicio y otra base de datos secundaria, levantada en otro servidor, a la que vamos replicando los datos de la principal.

Instalando MariaDB en Linux CentOS 7

Para esta práctica voy a utilizar Linux CentOS 7. Instalaremos MariaDB con el comando:

yum install -y mariadb-server

Una vez instalado, habilitamos el servicio.

systemctl enable mariadb
systemctl start mariadb

En el fichero /etc/hosts añadiremos las IPs de los dos servidores de base de datos:

[[email protected] ~]# cat /etc/hosts |grep maria
10.0.0.2 mariadb-master
10.0.0.3 mariadb-slave
[[email protected] ~]#

Esto lo haremos tanto en el servidor maestro como en el esclavo.

Configuración del servidor Master

Crear una base de datos

Lo primero de todo es crear una base de datos que vayamos a replicar. No me voy a complicar mucho la vida y voy a descargarme una base de datos de pruebas del repositorio de GitHub. En concreto esta: https://github.com/datacharmer/test_db

Una vez descargada al master, importo la base de datos:

[[email protected] test_db-master]# mysql < employees.sql
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
NULL
[[email protected] test_db-master]#

Habilitar los binlogs

A continuación, habilitamos los binlogs y reiniciamos la base de datos:

[[email protected] ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

log-bin
server_id=1
replicate-do-db=employees
bind-address=mariadb-master

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[[email protected] ~]# systemctl restart mariadb
[[email protected] ~]#

Purgado de los binlogs

Con el comando «purge binary logs» de MySQL, podemos eliminar los logs hasta una fecha concreta. Ejemplo de uso:

MariaDB [(none)]> PURGE BINARY LOGS BEFORE ‘2019-01-12 23:59‘;
Query OK, 0 rows affected (0.01 sec)

Crear el usuario de réplica

Creamos el usuario de réplica de datos en la base de datos master:

CREATE USER 'replica'@'localhost' IDENTIFIED BY 'replicaPassword';
GRANT REPLICATION SLAVE ON *.* TO replica IDENTIFIED BY 'ReplicaPassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

El comando «show master status» devuelve el número de binlog desde donde va a comenzar la réplica.

Exportar la base de datos

Si no has configurado ninguna contraseña para el usurio «root» de MariaDB, la podrás configurar con el siguiente comando:

mysqladmin -u root password 'MiContraseña'

Después de configurarla, tendrás que entrar a MariaDB introduciendo esta contraseña obligatoriamente, aunque seas el usuario root del sistemas:

[[email protected] tmp]# mysql -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

A continuación, crearemos una copia de seguridad de la base de datos.

[[email protected] tmp]# mysqldump -u root -p employees > employees-dump.sql
Enter password:
[[email protected] tmp]# ll employees-dump.sql
-rw-r--r-- 1 root root 168375941 Nov 8 11:35 employees-dump.sql
[[email protected] tmp]#

Y copiamos el fichero al servidor esclavo:

[[email protected] tmp]# scp -p employees-dump.sql mariadb-slave:/tmp
[email protected]'s password:
employees-dump.sql 100% 161MB 26.7MB/s 00:06
[[email protected] tmp]#

Volvemos a entrar a la base de datos master para desbloquear las tablas:

[[email protected] tmp]# mysql -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
[[email protected] tmp]#

Configuración del servidor esclavo

Creamos la base de datos

Creamos la base de datos «employees» que es la que vamos a replicar desde el servidor master:

[[email protected] ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE employees;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON employees.* TO 'slave'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>

Importamos los datos que habíamos exportado previamente desde el servidor master:

[[email protected] ~]# mysql -u root -p employees < /tmp/employees-dump.sql
Enter password:
[[email protected] ~]#

Configuración del servidor como esclavo

En el fichero /etc/my.cnf añadimos las siguientes líneas:

server_id=2
replicate-do-db=employees

Y reiniciamos el servicio de MariaDB con systemctl restart mariadb.

Replicación de los datos

Para replicar los datos desde la base de datos maestra a la esclava, ejecutaremos el siguiente comando:

[[email protected] ~]# mysql_upgrade -u root -p
Enter password:
Phase 1/4: Fixing views
employees.current_dept_emp OK
employees.dept_emp_latest_date OK
Phase 2/4: Fixing table and database names
Phase 3/4: Checking and upgrading tables
Processing databases
information_schema
employees
employees.departments OK
employees.dept_emp OK
employees.dept_manager OK
employees.employees OK
employees.salaries OK
employees.titles OK
mysql
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
performance_schema
test
Phase 4/4: Running 'mysql_fix_privilege_tables'
OK
[[email protected] ~]#

Comenzamos la réplica desde la posición del binlog que nos interesa:

MariaDB [(none)]> change master to MASTER_HOST='mariadb-master', MASTER_USER='replica', MASTER_PASSWORD='Martinez8', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=610, MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.13 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status;

Si queremos parar la réplica de datos, ejecutaremos el siguiente comando:

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.02 sec)
MariaDB [()]>

Probando si la réplica de datos está funcionando correctamente

Nos conectamos a la base de datos master e insertamos datos:

[[email protected] tmp]# mysql -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [employees]> INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (960000, '1977-08-12', 'David', 'Null', 'M', 'Null');
Query OK, 1 row affected, 1 warning (0.03 sec)
MariaDB [employees]>

Ahora nos conectamos a la base de datos de réplica y consultamos si se ha replicado el dato:

MariaDB [employees]> select * from employees where emp_no=’960000′;
+——–+————+————+———–+——–+————+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+——–+————+————+———–+——–+————+
| 960000 | 1977-08-12 | David | Null | M | 0000-00-00 |
+——–+————+————+———–+——–+————+
1 row in set (0.00 sec)

MariaDB [employees]>

Invertir la réplica

En caso de incidencia con el servidor master, el de réplica ha de poder dar servicio a los usuarios, por lo que todas las instrucciones SQL (insert, update, select, etc.) han de ir dirigidas a la instancia que está dando servicio. Por lo tanto, hay que invertir el sentido de la réplica para que el servidor esclavo pase a ser el master.

Se hace de la siguiente manera:

  • Me conecto al servidor master y paro el servicio de MariaDB, simulando una incidencia de caída de servicio:
[[email protected] ~]# systemctl stop mariadb
[[email protected] ~]# ps -ef |grep -i mysql |grep -v grep
[[email protected] ~]#

  • Me conecto al servidor esclavo e indico que el master es ahora el que antes era el esclavo:
MariaDB [employees]> stop slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [employees]> reset master;
Query OK, 0 rows affected (0.06 sec)
MariaDB [employees]> CHANGE MASTER TO MASTER_HOST='mariadb-slave';
Query OK, 0 rows affected (0.05 sec)
MariaDB [employees]>

Si tuviésemos una segunda instancia esclava, ejecutaríamos los siguientes comandos en esta instancia:

STOP SLAVE;
RESET SLAVE;
START SLAVE;

Una vez que el servidor original ha vuelto, tendremos que volver a girar la réplica.

[product_category category=»libros-de-mysql» Columns=»3″  onsale=»true» limit=»6″ paginate=»true»]

¿Te ha gustado? ¡Compártelo!

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
Share on telegram
Share on email

Deja un comentario

Tal vez también te gustaría leer...

Tutorial de ElasticSearch

Tabla de contenidos1 ¿Qué es ElasticSearch?2 Instalación de ElasticSearch2.1 Instalación de Java2.2 Instalación de ElasticSearch en Linux CentOS 72.2.1 Configuración del repositorio de Software2.2.2 Instalación de ElasticSearch con yum2.2.3 Habilitación del servicio2.2.4 Hacemos alguna prueba de testo tras la instalación2.2.5 Ficheros de configuración3 Insertar un dato en la base de datos de ElasticSearch4 Obtener un

Leer más »

Configurar «huge pages» en RedHat 7 para Oracle

Funcionamiento básico de la memoria física y la swap Todos los procesos que se están ejecutando en el sistema operativo leen datos de la memoria física (RAM). Estos datos se almacenan en «páginas de memoria» que deben existir en la RAM antes de poderse ejecutar. Si no lo están porque estas páginas se hayan movido

Leer más »

Instalación del gestor de Bases de Datos Adminer en Linux Centos 7

Adminer es un gestor de bases de datos MySQL, SQLite, PostgreSQL, Oracle, MS SQL, Firebird, SimpleDB, MongoDB y ElasticSearch, al que accedemos mediante una interfaz WEB. Es similar al archiconocido PHPMyAdmin pero con muchas más opciones. Se puede hacer una comparativa en su propia página WEB (supongo que ellos no van a decir que son

Leer más »

Eliminar los usuarios de MySQL o MariaDB

Eliminar usuarios anónimos Cuando instalamos MySQL o MariaDB se crea un usuario anónimo por defecto que representa un problema de seguridad para una base de datos en Producción. Para eliminarlo, ejecutaremos el siguiente comando SQL: Teclado Mecánico Gaming de VicTsing, 104 Teclas y… 39,88 EUR COMPRAR EN AMAZON Logitech G413 Mechanical Gaming Keyboard – Carbon…

Leer más »

Habilitar los logs binarios en MySQL o MariaDB

Los logs binarios son ficheros de MySQL que permiten recuperar datos de la base de datos desde el último backup hasta la última modificación o en un periodo de tiempo específico. Para habilitarlos, añadiremos la siguiente directiva en el fichero /etc/my.cnf: Toshiba Canvio Basics – Disco duro externo, 2.5… 42,21 EUR COMPRAR EN AMAZON Toshiba

Leer más »

Modificar el tamaño de los redo logs de InnoDB de MariaDB y MySQL

MySQL y MariaDB utilizan, por defecto, los archivos de «redo logs» ib_logfile0  y 1 de 48MB cada uno por defecto. Sin embargo, dependiendo de la actividad de nuestra base de datos, es posible que necesitemos ampliar el tamaño de estos archivos para no estar machacando tanto el disco y mejorar el rendimiento de nuestra base de

Leer más »