Jun 11, 2020

6 min read

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:

Settings

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 @.

DB Instance size

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

Availability & Durability

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

Connectivity

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.

Database Authentication

Utilizaremos el password authentication que configuramos en la parte superior.

Additional Configuration

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 -ADatabase 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