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:

[root@Centos7 ~]# cat /etc/hosts |grep maria
10.0.0.2 mariadb-master
10.0.0.3 mariadb-slave
[root@Centos7 ~]#

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:

[root@Centos7 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
[root@Centos7 test_db-master]#

Habilitar los binlogs

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

[root@Centos7 ~]# 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

[root@Centos7 ~]# systemctl restart mariadb
[root@Centos7 ~]#

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:

[root@Centos7 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.

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

Y copiamos el fichero al servidor esclavo:

[root@Centos7 tmp]# scp -p employees-dump.sql mariadb-slave:/tmp
root@mariadb-slave’s password:
employees-dump.sql 100% 161MB 26.7MB/s 00:06
[root@Centos7 tmp]#

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

[root@Centos7 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
[root@Centos7 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:

[root@Centos7v2 ~]# 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:

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

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:

[root@Centos7v2 ~]# 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
[root@Centos7v2 ~]#

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 [(none)]>

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

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

[root@Centos7 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:

[root@Centos7 ~]# systemctl stop mariadb
[root@Centos7 ~]# ps -ef |grep -i mysql |grep -v grep
[root@Centos7 ~]#

  • 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»]

Valoración
Summary
Article Name
Réplica maestro esclavo de una base de datos MariaDB o MySQL
Description
1 Instalando MariaDB en Linux CentOS 7 2 Configuración del servidor Master 3 Configuración del servidor esclavo 4 Probando si la réplica de datos está funcionando correctamente 5 Invertir la réplica

Deja un comentario