Trabajando con Aurora-MySQL para principiantes parte # 1

Esta es una serie de post para introducir el uso de Aurora-MySQL para conocer en detalle esta base de datos, cómo interactuar con ella y vamos a ir viendo cada una de las funcionalidades que tiene.

Crear nuestra primer Aurora-MySQL

Vamos a RDS en la consola de AWS y seleccionamos Aurora-MySQL, dentro de esta vamos a configurar las siguientes opciones:

  • DB Cluster Identifier: Es el identificador del cluster, este nombre debe ser único a través de todos los cluster que se tengan en la misma cuenta.
  • Master username: usuario de la base de datos.
  • Master Password: password de acceso a la base de datos, debe tener al menos una longitud de 8 caracteres, no debe contener /, " o @.

Para contar con la funcionalidad de Performance Insights sugiero utilizar una instancia db.r5.large que se compone de 2vCPUs y 16GB RAM.

Para este laboratorio no vamos a seleccionar un despliegue Multi-Az.

En el laboratorio utilicé la VPC por defecto que tenia en la región de Ohio.

  • Publicly accessible: Habilitamos yes para poder conectarnos desde nuestro equipo personal a través de MySQL Workbench.
  • VPC Security group: Especificamos el security group de la BD, debemos autorizar el tráfico desde el bastion host que usaremos (yo uso una instancia de cloud9) y también desde nuestra IP personal /32.
  • Database port: puerto que usa mysql 3306.

Utilizaremos el password authentication que configuramos en la parte superior.

  • DB instance identifier: Es el identificador de la instancia, muy diferente al identificador del cluster, se puede dejar vacío pero AWS pondrá uno por defecto.
  • Initial dababase name: es el nombre de la BD a crear inicialmente en el endpoint.
  • DB cluster parameter group: el parameter group asociado al cluster, recuerda que siempre es mejor crear uno personalizado y no dejar el default.
  • DB parameter group:el parameter group asociado a la BD, recuerda que siempre es mejor crear uno personalizado y no dejar el default.
  • Option group: Es un grupo que permite agregar funcionalidades opcionales, por ejemplo soporte de mysql para memcached.
  • Failover priority: Establece la prioridad para el failover con alguna de las replicas especificadas.
  • Backup retention period: el número de días que la base de datos va a retener los backups de forma automática.
  • Encryption: Siempre se recomienda cifrar la BD para entornos productivos, acá debemos seleccionar la master key para cifrar.
  • Backtrack: Permite retroceder en el tiempo la BD a un punto del tiempo especifico sin tener que crear otro cluster.

Performance Insights: Habilitamos esta opción para el monitoreo detallado.

Monitoring: Puedes ver el siguiente post para más detalle del monitoreo.

Conectandonos a Aurora-MySQL

1- Desplegamos una instancia de Cloud9.

2- Permitimos de la BD el sg de Cloud9 por el puerto 3306.

3- Ejecutamos el siguiente comando para conectarnos a la base de datos, no olviden reemplazar el endpoint:

mysql -h midb.xxxxxxxx.us-east-2.rds.amazonaws.com -P 3306 -u admin -p

4- una vez estando en la base de datos revisamos nuestra versión de aurora :

mysql> select @@aurora_version
-> ;
+------------------+
| @@aurora_version |
+------------------+
| 2.07.2 |
+------------------+
1 row in set (0.00 sec)

Creando el modelo ER en Aurora-MySQL

Para insertar datos en aurora vamos a crear 2 tablas relacionadas entre si, una de empleados y otra de departamentos de la siguiente forma:

Crear la tabla departamentos estando dentro de la consola de mysql

mysql> CREATE TABLE departamentos (
-> dept_no CHAR(4) NOT NULL,
-> nombre_dept VARCHAR(40) NOT NULL,
-> PRIMARY KEY (dept_no),
-> UNIQUE KEY (nombre_dept)
-> );
Query OK, 0 rows affected (0.03 sec)

Crear la tabla empleados

mysql> CREATE TABLE empleados (
-> id_emp INT NOT NULL,
-> fecha_nacimiento DATE NOT NULL,
-> primer_nombre VARCHAR(14) NOT NULL,
-> apellido VARCHAR(16) NOT NULL,
-> genero ENUM ('M','F') NOT NULL,
-> fecha_contrato DATE NOT NULL,
-> salario INT NOT NULL,
-> edad INT NOT NULL,
-> dept_no CHAR(4) NOT NULL,
-> FOREIGN KEY (dept_no) REFERENCES departamentos(dept_no),
-> PRIMARY KEY (id_emp)
-> );
Query OK, 0 rows affected (0.02 sec)

Insertando datos en Aurora-MySQL

Ahora vamos a insertar algunos datos de ejemplo con las siguientes consultas:

Insertar los departamentos:

INSERT INTO `departamentos` VALUES ('3','amazonas'),('4','antioquia'),('5','arauca'),('6','atlantico'),('7','bolivar'),('8','cesar'),('9','cordoba'),('1','cundinamarca'),('10','sucre'),('2','valle del cauca');

Insertar los empleados:

INSERT INTO `empleados` VALUES (1234567,'1959-09-08','patricia','gomez','F','2000-12-12',1500,56,'6'),(80434295,'1969-04-04','miguel','barrera','M','2015-03-02',2000,43,'8'),(1032409265,'1988-03-29','carlos','zambrano','M','2017-08-17',1000,32,'2');

Consultando nuestros datos en Aurora-MySQL

Una vez insertados todos los datos ahora tenemos que consultarlos para eso utilizamos las siguientes consultas:

Consultar todos los empleados:

mysql> use miempresa;
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
mysql> select * from empleados;
+------------+------------------+---------------+----------+--------+----------------+---------+------+---------+
| id_emp | fecha_nacimiento | primer_nombre | apellido | genero | fecha_contrato | salario | edad | dept_no |
+------------+------------------+---------------+----------+--------+----------------+---------+------+---------+
| 1234567 | 1959-09-08 | patricia | gomez | F | 2000-12-12 | 1500 | 56 | 6 |
| 80434295 | 1969-04-04 | miguel | barrera | M | 2015-03-02 | 2000 | 43 | 8 |
| 1032409265 | 1988-03-29 | carlos | zambrano | M | 2017-08-17 | 1000 | 32 | 2 |
+------------+------------------+---------------+----------+--------+----------------+---------+------+---------+
3 rows in set (0.01 sec)

Haciendo filtros por el departamento:

mysql> select * from empleados where dept_no=2;
+------------+------------------+---------------+----------+--------+----------------+---------+------+---------+
| id_emp | fecha_nacimiento | primer_nombre | apellido | genero | fecha_contrato | salario | edad | dept_no |
+------------+------------------+---------------+----------+--------+----------------+---------+------+---------+
| 1032409265 | 1988-03-29 | carlos | zambrano | M | 2017-08-17 | 1000 | 32 | 2 |
+------------+------------------+---------------+----------+--------+----------------+---------+------+---------+
1 row in set (0.01 sec)

Verificando queries y consumo de la BD en Performance Insights

En la consola de RDS en el panel izquierdo buscamos la opción de Performance Insights y seleccionamos nuestra base de datos, vamos a entrar a una pantalla como la siguiente:

En esta herramienta podremos ver todas las consultas SQL ejecutadas, los waits, host y users sobre la base de datos en tiempo real.

Cómo hacer un mysqldump Aurora-MySQL

Para ejecutar un mysqldump debemos ejecutar el siguiente comando desde el bastion host:

mysqldump -h midb.xxxxxxxx.us-east-2.rds.amazonaws.com -u admin -p miempresa > dump.sql

En nuestro bastion crear un dump el cual podemos ver de la siguiente forma:

-- MySQL dump 10.13  Distrib 5.5.62, for Linux (x86_64)
--
-- Host: midb.cbh7gnpfquqg.us-east-2.rds.amazonaws.com Database: miempresa
-- ------------------------------------------------------
-- Server version 5.7.12
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `departamentos`
--
DROP TABLE IF EXISTS `departamentos`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `departamentos` (
`dept_no` char(4) NOT NULL,
`nombre_dept` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`),
UNIQUE KEY `nombre_dept` (`nombre_dept`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `departamentos`
--
LOCK TABLES `departamentos` WRITE;
/*!40000 ALTER TABLE `departamentos` DISABLE KEYS */;
INSERT INTO `departamentos` VALUES ('3','amazonas'),('4','antioquia'),('5','arauca'),('6','atlantico'),('7','bolivar'),('8','cesar'),('9','cordoba'),('1','cundinamarca'),('10','sucre'),('2','valle del cauca');
/*!40000 ALTER TABLE `departamentos` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `empleados`
--
DROP TABLE IF EXISTS `empleados`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `empleados` (
`id_emp` int(11) NOT NULL,
`fecha_nacimiento` date NOT NULL,
`primer_nombre` varchar(14) NOT NULL,
`apellido` varchar(16) NOT NULL,
`genero` enum('M','F') NOT NULL,
`fecha_contrato` date NOT NULL,
`salario` int(11) NOT NULL,
`edad` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
PRIMARY KEY (`id_emp`),
KEY `dept_no` (`dept_no`),
CONSTRAINT `empleados_ibfk_1` FOREIGN KEY (`dept_no`) REFERENCES `departamentos` (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `empleados`
--
LOCK TABLES `empleados` WRITE;
/*!40000 ALTER TABLE `empleados` DISABLE KEYS */;
INSERT INTO `empleados` VALUES (1234567,'1959-09-08','patricia','gomez','F','2000-12-12',1500,56,'6'),(80434295,'1969-04-04','miguel','barrera','M','2015-03-02',2000,43,'8'),(1032409265,'1988-03-29','carlos','zambrano','M','2017-08-17',1000,32,'2');
/*!40000 ALTER TABLE `empleados` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2020-06-11 5:50:31

AWS x10, Tech Director en Globant con más de 7 años de experiencia en AWS.

AWS x10, Tech Director en Globant con más de 7 años de experiencia en AWS.