MySQL InnoDB Cluster

Mayo, 2017

[Link]@[Link]
Principal Solutions Architect
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
High Availability — Alta disponibilidad
Diferentes opciones para diferentes entornos
• MySQL InnoDB
Cluster y/o MySQL
Cluster CGE
• Clustering:
soluciones Active /
passive p.e Oracle
• Oracle VM / Clusterware
otros VM

• Master/Slave con
Failover
Automatizado

• MySQL
Replicación
Master/Slave

9 9 . 9 9 9 % Disponibilidad
continua

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 2

MySQL Replication — Introducción

<<MySQL async replication>>

3
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Diferencias entre MySQL Replication & MySQL InnoDB Cluster

MySQL Replication
(asíncrona)

MySQL Group Replication

InnoDB Cluster (síncrona)

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

MySQL Group Replication

“Multi-master plugin de replicación para update

en cualquier lugar, con detección y resolución
de conflictos, recuperación de datos de
manera automática y distribuida”

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

MySQL Group Replication — Objetivo
— El objetivo es aportar la funcionalidad de replicación síncrona para
MySQL 5.7 & versiones superiores.
— Es un plugin para MySQL , desarrollado por Oracle e incluido en
MySQL Community GPL.
— Soportada en todas las plataformas MySQL (Linux, Windows, Solaris,
OSX, etc.. )
— Es un componente core de MySQL InnoDB Cluster.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

MySQL Group Replication — ¿Cómo funciona?
— Un nodo (miembro del grupo) envía los cambios realizados por una transacción
al grupo (eventos en el binlog) a través del MySQL Group Communication System
GCS.
—GCS es una implementación del teorema de Paxos Mencius , un algoritmo para
llegar a consensos en sistemas distribuidos. )
— Todos los miembros consumen los cambios y los certifican, no se necesita que
todos certifiquen, con que la mayoría lo haga (consenso) ya es suficiente.
— Si pasa la certificación los cambios son aplicados, si falla se genera un
rollback de la transacción en el server original y se descarta en los otros.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

Diseñando un Cluster con MySQL InnoDB Cluster
MySQL InnoDB Cluster — Multimaster Activo Activo
• MySQL Router: punto único de
conexión entre APP y base de
datos
• MySQL Group Replication Multi-
master activo / activo
• MySQL Shell: AdminAPI para
scripts python & Java Script para
manutención y failover
• MySQL Connectors

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 8

MySQL InnoDB Cluster
Arquitectura mínima 3 servidores

App Server App Server

MySQL Connector MySQL Connector

MySQL Router MySQL Router

M HA

MySQL Shell MySQL Enterprise Monitor

M M

Group Replication

* 3 servidores

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 9

MySQL InnoDB Cluster — Pre-condiciones
— Funciona solamente con tablas InnoDB.
— Todas las tablas deben tener una PK definida.
— Es importante contar con una buena red, con baja latencia.
— Existe un máximo de 9 miembros por grupo y un mínimo de 3.
— Se debe habilitar el binlog y solo soporta el formato ROW.

MySQL
InnoDB
cluster

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

MySQL InnoDB Cluster Arquitectura
1 solo data center Shared Nothing Architecture

MySQL InnoDB Cluster

Application Servers
Mínimo 3 servidores

MySQL Router in Stack

MySQL InnoDB Cluster

Group Replication 3 Servers

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 11

MySQL InnoDB Cluster Arquitectura
2 data centers Shared Nothing Architecture + DRP Site

Site1: Activo Site 2 DRP: [1 ó 2 servidores ]

App server 1 & App server 2 & App server 1 & App server2 &
MySQL Router MySQL Router MySQL Router MySQL Router

MySQL
<< Async Replication>>

MySQL Group Replication

MySQL Slave Replicas

Copyright © 2017, Oracle and/or its affiliates. All rights

reserved.
MySQL InnoDB Cluster Arquitectura
2 data centers Active - Active [ Network Low latency ]

App server 1 & App server 2 & App server 1 & App server2 &
MySQL Router MySQL Router MySQL Router MySQL Router

MySQL
<< GCS Replication>>

MySQL Group Replication MySQL Group Replication

Total 6 servidores
Copyright © 2017, Oracle and/or its affiliates. All rights
reserved.
Arquitectura Draft
MySQL InnoDB Cluster

*** MySQL InnoDB Cluster [InnoDB Disco + Buffered Memory Cached Data]
Licencia requerida: 1 suscripción por server físico MySQL EE
[Arq mínima 1 Data Center 3 servers]
[Arq. alterna A: 2 Data Center, Active - DRP 4 servidores]
[Arq. alterna B: 2 Data Center, Active - Active 6 servidores]

64gb RAM; 24 ~ 48 cores CPU — Best practice [c/u]

Casos de ejemplo:
3 servers: c/u con 64gb RAM; 12 ~ 48 cores

Copyright © 2017, Oracle and/or its affiliates. All rights

reserved.
Lab 1 ~ Instalando un entorno con MySQL InnoDB
Cluster
App Server App Server
MySQL Connector MySQL Connector

InnoDB Cluster MySQL Router MySQL Router

M HA

MySQL Shell MySQL Enterprise Monitor

M M
Group
Replication

16
Copyright © 2017 Oracle and/or its affiliates. All rights reserved. |
Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster

• En la realización de este lab, trabajaremos con 3 VMs con Oracle

Linux 6.5 y MySQL Enterprise Server 5.7.18
• Cada VM, tiene pre-instalado MySQL Enterprise Server, Python
Connector 2.7 & MySQL Shell 1.0.9 / MySQL Router 2.1

• Los equipos tendrán la siguiente configuración:

• Server1: mysqlshell, mysqlrouter, MySQL Sever 5.7.18 — Primary Instance R/W
• Server2: mysqlshell, mysqlrouter, MySQL Sever 5.7.18 — Secondary Instance R/O
• Server3: mysqlshell, mysqlrouter, MySQL Sever 5.7.18 — Secondary Instance R/O

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 17

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster

• Pre-requisitos (1)
— Cada Server que formará parte del grupo, debe tener un
hostname e IP address definido en el archivo /etc/hosts
Ejemplo:

-bash-4.1$ cat /etc/hosts

[Link] localhost [Link] localhost4 localhost4.localdomain4
::1 localhost [Link] localhost6 localhost6.localdomain6
[Link] server1
[Link] server2
[Link] server3

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 18

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
• Pre-requisitos (2)
— MySQL Server 5.7.18+ instalado
— Python 2.7
— MySQL Router 2.1
— MySQL Shell 1.0.9

MySQL Server configuración:

$basedir = /usr
$datadir = /var/lib/mysql/studentName/datafiles, p.e /var/lib/mysql/manuel/datafiles
Archivo de configuración =/var/lib/mysql/studentName/cnf, p.e /var/lib/mysql/manuel/cnf
Socket = /var/lib/mysql/studentName/[Link], p.e /var/lib/mysql/manuel/[Link]
DB port = 330X, donde X es el número de estudiante [pregunta a tu instructor por tu número ID ]
Ejemplo: 3301, 3302, 3303, etc…
[Link]

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 19

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.1 ~ Abrir una sesión SSH para cada uno de los servers:
— Server1 IP [Link]
— Server2 IP [Link]
— Server3 IP [Link]
— Usuario OS: mysql
— Password: sakila

En cada uno de los servidores ejecutar pasos 1.2 ~ 1.16:

1.2 Crear directorio $DATADIR & cnf

os shell> mkdir -p /var/lib/mysql/manuel/datafiles — Nota.- no olvides reemplazar manuel, con tu nombre.
os shell> mkdir -p /var/lib/mysql/manuel/cnf — Nota.- no olvides reemplazar manuel, con tu nombre.

1.3 Configurar la BD MySQL 5.7 [en cada uno de los 3 servers ]

1.3.1 Copiar [Link] a /var/lib/mysql/studentName/cnf
os shell> cp /usr/share/mysql/[Link] /var/lib/mysql/manuel/cnf/[Link]

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 20

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.3.2 Validar que parámetros en [Link] sean correctos:
os shell> cat /var/lib/mysql/manuel/cnf/[Link]

[client]
socket=/var/lib/mysql/manuel/[Link]

[mysqld]
#General Settings
port = 3300
datadir=/var/lib/mysql/manuel/datafiles
socket=/var/lib/mysql/manuel/[Link]
innodb_buffer_pool_size = 20M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
basedir=/usr
log-error=/var/lib/mysql/manuel/[Link]
pid-file=/var/lib/mysql/manuel/[Link]

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 21

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.4 Inicializar el motor MySQL 5.7
os shell>mysqld --defaults-file=/var/lib/mysql/manuel/cnf/[Link] --initialize

1.5 Iniciar el servicio de mysqld

os shell>mysqld --defaults-file=/var/lib/mysql/manuel/cnf/[Link] &

1.6 Validar password temporal de root

os shell>grep password /var/lib/mysql/manuel/[Link]

2017-05-04T[Link].392802Z 1 [Note] A temporary password is generated for root@localhost:

BaEiGo/gs70(
<— Password temporal de root
1.7 Ingresar a la consola de mysql> usando el password temporal de root
os shell>mysql -uroot -p -S /var/lib/mysql/manuel/[Link]

…
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 22

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.8 Cambiar password de mysql root
mysql> alter user 'root'@'localhost' identified by 'sakila';
Query OK, 0 rows affected (0.00 sec)
mysql>exit

1.9 Iniciar MySQL Shell

os shell> sudo -i mysqlsh

1.10 Configurar instancia Local vía MySQL Shell

mysql-js> [Link]('root@localhost:3300');
Please provide the password for 'root@localhost:3300':

Detecting the configuration file...

Default file not found at the standard locations.
Please specify the path to the MySQL configuration file: /var/lib/mysql/manuel/cnf/[Link]
MySQL user 'root' cannot be verified to have access to other hosts in the network.

1) Create root@% with necessary grants

2) Create account with different name
3) Continue without creating account
4) Cancel
Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: icroot
Password for new account: sakila
Confirm password: sakila
Validating instance…

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 23

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.11 Configurando instancia Local vía MySQL Shell
mysql-js> [Link]();
Please provide the password for 'root@localhost:3300':
Validating instance…
The configuration has been updated but it is required to restart the server.

{ …
"config_errors": [ {
{ "action": "restart",
"action": "restart", "current": "FILE",
"current": "OFF", "option": "master_info_repository",
"option": "enforce_gtid_consistency", "required": "TABLE"
"required": "ON" },
}, {
{ "action": "restart",
"action": "restart", "current": "FILE",
"current": "OFF", "option": "relay_log_info_repository",
"option": "gtid_mode", "required": "TABLE"
"required": "ON" },
}, {
{ "action": "restart",
"action": "restart", "current": "OFF",
"current": "0", "option": "transaction_write_set_extraction",
"option": "log_bin", "required": "XXHASH64"
"required": "1" }
}, ],
{ "errors": [],
"action": "restart", "restart_required": true,
"current": "0", "status": "error"
"option": "log_slave_updates", }
"required": "ON"
},

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 24

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.12 Exit mysql shell
mysql-js> \q

1.13 Validando cambios a [Link]

-bash-4.1$ cat /var/lib/mysql/manuel/cnf/[Link]
[client]
socket=/var/lib/mysql/manuel/[Link]
[mysqld]
#General Settings
port = 3300
innodb_buffer_pool_size = 20M
basedir=/usr
datadir=/var/lib/mysql/manuel/datafiles
socket=/var/lib/mysql/manuel/[Link]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/lib/mysql/manuel/[Link]
pid-file=/var/lib/mysql/manuel/[Link]
log_slave_updates = ON
server_id = 2642161198 <— Valores necesarios para configurar InnoDBCluster GR
relay_log_info_repository = TABLE
master_info_repository = TABLE agregados por mysql shell
transaction_write_set_extraction = XXHASH64
binlog_format = ROW
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
report_port = 3306
binlog_checksum = NONE
enforce_gtid_consistency = ON
log_bin
gtid_mode = ON

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 25

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.14 Restart instancia mysqld
os shell> mysqladmin shutdown -uroot -p -S /var/lib/mysql/manuel/[Link]
… [1]+ Done mysqld --defaults-file=/var/lib/mysql/manuel/cnf/[Link]

os shell> mysqld --defaults-file=/var/lib/mysql/manuel/cnf/[Link] &

1.15 Validar que instancia inicie OK

os shell> cat /var/lib/mysql/manuel/[Link]

2017-05-04T[Link].884087Z 0 [Note] mysqld: ready for connections.

Version: '5.7.18-enterprise-commercial-advanced-log' socket: '/var/lib/mysql/
manuel/[Link]' port: 3306 MySQL Enterprise Server - Advanced Edition
(Commercial)

Inicio OK de instancia mysql

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 26

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.15 Validando que instancia mysqld, este correctamente configurada
para GR InnoDBCluster
os shell> sudo -i mysqlsh

mysql-js> [Link]('root@localhost:3300');
Please provide the password for 'root@localhost:3300':
Validating instance...

The instance 'localhost:3300' is valid for Cluster usage

{
"status": "ok" OK instancia mysql GR InnoDBCluster
}
mysql-js>

La instancia mysqld esta lista para poder ser miembro de un InnoDBCluster!

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 27

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.16 Creando un InnoDBCluster
En el server1:
os shell> sudo -i mysqlsh

mysql-js> [Link]('icroot@server1:3300');
Please provide the password for 'icroot@server1:3300':
Creating a Session to 'icroot@server1:3300'
Classic Session successfully established. No default schema selected.

mysql-js> var cluster = [Link]('ClusterManuel');

A new InnoDB cluster will be created on instance 'icroot@server1:3300'.

Creating InnoDB cluster 'ClusterManuel' on 'icroot@server1:3300'...

Adding Seed Instance...

Cluster successfully created. Use [Link]() to add MySQL instances.

At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

mysql-js>

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 28

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.17 Validando status del cluster
En el server1:
os shell> sudo -i mysqlsh

mysql-js> [Link]();
{
"clusterName": "ClusterManuel",
"defaultReplicaSet": {
"name": "default",
"primary": "server1:3300",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"server1:3300": {
"address": "server1:3300",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
mysql-js>

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 29

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.18 Agregando la 2a instancia al cluster
En el server1:
os shell> sudo -i mysqlsh

mysql-js> [Link]('icroot@server2:3300');
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'icroot@server2:3300':

Adding instance to the cluster ...

The instance 'icroot@server2:3300' was successfully added to the cluster.

mysql-js>

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 30

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.19 Agregando la 3a instancia al cluster
En el server1:
os shell> sudo -i mysqlsh

mysql-js> [Link]('icroot@server3:3300');
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'icroot@server3:3300':

Adding instance to the cluster ...

The instance 'icroot@server3:3300' was successfully added to the cluster.

mysql-js>

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 31

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.20 Validando de nuevo el status del InnoDBCluster
En el server1:
os shell> sudo -i mysqlsh
mysql-js> [Link]();
{
"clusterName": "ClusterManuel",
"defaultReplicaSet": {
"name": "default",
"primary": "server1:3300",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"server1:3300": {
"address": "server1:3300",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"server2:3300": {
"address": "server2:3300",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"server3:3300": {
"address": "server3:3300",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 32

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.21 Hacer persistente la configuración del cluster
En cada uno de los servidores que forman parte del grupo [server1, server2, & server 3]
ejecutar :
os shell> sudo -i mysqlsh
mysql-js> [Link]('icroot@server1:3300');
Please provide the password for 'icroot@server1:3300':

Detecting the configuration file...

Default file not found at the standard locations.
Please specify the path to the MySQL configuration file: /var/lib/mysql/manuel/cnf/[Link]
Validating instance...

The instance 'server1:3300' is valid for Cluster usage

You can now use it in an InnoDB Cluster.

{
"status": "ok"
}
mysql-js>
Lo mismo en el server 2 & server 3…

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 33

Lab 1 ~ Instalando un entorno con MySQL InnoDB Cluster
1.22 Validando nuevos parámetros a [Link] [persistencia de config del cluster ]
os shell> cat /var/lib/mysql/manuel/cnf/[Link]
group_replication_allow_local_disjoint_gtids_join = OFF
group_replication_allow_local_lower_version_join = OFF
group_replication_auto_increment_increment = 7
group_replication_bootstrap_group = OFF
group_replication_components_stop_timeout = 31536000
group_replication_compression_threshold = 1000000
group_replication_enforce_update_everywhere_checks = OFF
group_replication_flow_control_applier_threshold = 25000
group_replication_flow_control_certifier_threshold = 25000
group_replication_flow_control_mode = QUOTA
group_replication_force_members
group_replication_group_name = f4722b97-306e-11e7-8b79-080027e9aa22
group_replication_group_seeds = server1:13300,server3:13300
group_replication_gtid_assignment_block_size = 1000000
group_replication_ip_whitelist = AUTOMATIC
group_replication_local_address = server2:13300
group_replication_poll_spin_loops = 0
group_replication_recovery_complete_at = TRANSACTIONS_APPLIED
group_replication_recovery_reconnect_interval = 60
group_replication_recovery_retry_count = 10
group_replication_recovery_ssl_ca
group_replication_recovery_ssl_capath
group_replication_recovery_ssl_cert
group_replication_recovery_ssl_cipher
group_replication_recovery_ssl_crl
group_replication_recovery_ssl_crlpath
group_replication_recovery_ssl_key
group_replication_recovery_ssl_verify_server_cert = OFF
group_replication_recovery_use_ssl = ON
group_replication_single_primary_mode = ON
group_replication_ssl_mode = REQUIRED
group_replication_start_on_boot = ON

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 34

Lab 2 ~ Validando replicación sincrónica con MySQL InnoDB
Cluster
2.0 Re-conectando sesión con mysql shell
En el server1:

os shell> sudo -i mysqlsh

mysql-js> \connect icroot@server1:3300

Creating a Session to 'icroot@server1:3300'
Enter password:
Classic Session successfully established. No default schema selected.

mysql-js> cluster = [Link]();

<Cluster:ClusterManuel>

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 35

Lab 2 ~ Validando replicación sincrónica con MySQL InnoDB
Cluster
2.1 Validando status del cluster via mysql shell
En el server1:
mysql-js> [Link]();
{
"clusterName": "ClusterManuel",
"defaultReplicaSet": {
"name": "default",
"primary": "server1:3300",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"server1:3300": {
"address": "server1:3300",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"server2:3300": {
"address": "server2:3300",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"server3:3300": {
"address": "server3:3300",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 36

Lab 2 ~ Validando replicación sincrónica con MySQL InnoDB
Cluster
2.2 Creando una BD & tabla en server1 R/W Primary Instance
En el server1:

os shell>mysql -uroot -p -S /var/lib/mysql/manuel/[Link]

Enter password:

mysql> create database dbtest;

Query OK, 1 rows affected (0.01 sec)

mysql> use dbtest;

Database changed
mysql> create table t1(id int NOT NULL AUTO_INCREMENT, nombre varchar(20), PRIMARY KEY(id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into dbtest.t1(id,nombre) values(1,"manuel"),(2,"nochipa"),(3,"izumy");

Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 37

Lab 2 ~ Validando replicación sincrónica con MySQL InnoDB
Cluster
2.3 Validando datos en server1, server2 & server3
En el server1,server 2 & server 3:
os shell>mysql -uroot -p -S /var/lib/mysql/manuel/[Link]
Enter password:

En server1: En server2: En server3:

mysql> select * from dbtest.t1; mysql> select * from dbtest.t1; mysql> select * from dbtest.t1;
+----+---------+ +----+---------+ +----+---------+
| id | nombre | | id | nombre | | id | nombre |
+----+---------+ +----+---------+ +----+---------+
| 1 | manuel | | 1 | manuel | | 1 | manuel |
| 2 | nochipa | | 2 | nochipa | | 2 | nochipa |
| 3 | izumy | | 3 | izumy | | 3 | izumy |
+----+---------+ +----+---------+ +----+---------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec) 3 rows in set (0.00 sec)

mysql> system hostname mysql> system hostname mysql> system hostname

Server1 Server2 server3

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 38

Lab 2 ~ Validando replicación sincrónica con MySQL InnoDB
Cluster
2.3 Simulando un crash en server1
En el server1:
mysql-js> [Link]();
{
"clusterName": "ClusterManuel",
"defaultReplicaSet": {
"name": "default",
"primary": "server1:3300",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"server1:3300": {
"address": "server1:3300",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"server2:3300": {
"address": "server2:3300",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"server3:3300": {
"address": "server3:3300",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 39

Lab 2 ~ Validando replicación sincrónica con MySQL InnoDB
Cluster
2.3 Simulando un crash en server1…
En el server1:

os shell> sudo pkill mysqld

ps -fea|grep mysqld
vagrant 11300 2861 0 02:56 pts/1 [Link] grep mysqld

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 40

Lab 2 ~ Validando replicación sincrónica con MySQL InnoDB
Cluster
2.3 Simulando un crash en server1, validando status nuevamente
En el server1:
"server2:3300": {
mysql-js> \c icroot@server2:3300
Creating a Session to 'icroot@server2:3300' "address": "server2:3300",
Enter password: "mode": "R/W",
Closing old connection...
Classic Session successfully established. No default
"readReplicas": {},
schema selected. "role": "HA",
mysql-js> cluster = [Link](); "status": "ONLINE"
<Cluster:ClusterManuel>
mysql-js> [Link](); },
{ "server3:3300": {
"clusterName": "ClusterManuel", "address": "server3:3300",
"defaultReplicaSet": {
"name": "default", "mode": "R/O",
"primary": "server2:3300", "readReplicas": {},
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any
"role": "HA",
failures. 1 member is not active", "status": "ONLINE"
"topology": { }
"server1:3300": {
"address": "server1:3300", }
"mode": "R/O", }
"readReplicas": {},
"role": "HA",
}
"status": "(MISSING)"
},

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 41

Lab 2 ~ Validando replicación sincrónica con MySQL InnoDB
Cluster
2.3 Validando status nuevamente
En el server1:
mysql-js> [Link]();
{
"clusterName": "ClusterManuel",
"defaultReplicaSet": {
"name": "default",
"primary": "server2:3300",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
"topology": {
"server1:3300": {
"address": "server1:3300",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
},
"server2:3300": {
"address": "server2:3300",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"server3:3300": {
"address": "server3:3300",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 42

Lab 2 ~ Validando replicación sincrónica con MySQL InnoDB
Cluster
2.3 Re-iniciando el nodo 1:
En el server1:
os shell>mysqld \—defaults-file=/var/lib/mysql/manuel/cnf/[Link] &
[1] 11301 mysql-js> [Link]();
{
"primary": "server2:3300",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"server1:3300": {
"address": "server1:3300",
"mode": "R/O",
"readReplicas": {},
Validando status nuevamente… "role": "HA",
"status": "ONLINE"
},
"server2:3300": {
"address": "server2:3300",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"server3:3300": {
"address": "server3:3300",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 43

Final de Laboratorio — Conclusiones & ¿ Preguntas ?

MySQL
InnoDB
cluster

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 44

— Información Adicional —
[Link]

[Link]

Contacto:

[Link]@[Link]

MySQL
InnoDB
cluster

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 45

Diferencias entre: MySQL Community Edition vs MySQL
Enterprise Edition
MySQL Community Edition MySQL Enterprise Edition
(GPL) (Commercial)
MySQL Database Server ✔ ✔

MySQL Replication ✔ ✔
MySQL Partitioning ✔ ✔

MySQL Workbench ✔ ✔*
MySQL Enterprise Monitor X ✔

MySQL Enterprise Backup X ✔

MySQL Enterprise Security X ✔

MySQL Enterprise Scalability – Thread Pool X ✔

Oracle Premier Support X ✔

* MySQL Workbench Enterprise Edition

Source: [Link]

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential 47
MySQL Enterprise & Tools
Seguridad, hot backup, monitoring integrado

MySQL Enterprise Monitor

MySQL Workbench
MySQL Query Analyzer

[Link]

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 48

MySQL Enterprise Workbench
• Administración de • Audit log review (EE) • Backup management
usuarios, schemas e – Fácil y rápido acceso a (EE)
instances eventos y Log de
auditoría • Crear y administrar
• Herramienta de trabajos de copia de
– Búsquedas por: seguridad
desarrollo
– Tipo de evento • Programe los trabajos de
– Cuenta de usuario copia de seguridad para
– Rango de fechas que se ejecuten
automáticamente
– Coincidencia de texto
• Ver los trabajos de copia de
•Firewall management seguridad actuales
(EE) • Ver la actividad de copia de
– Crear, revisar y seguridad reciente
Backup Management GUI establecer reglas de
firewall por usuario • Restaurar copias de
seguridad
• Database migration
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 49
MySQL Backup — hot backup
Mysqldump MySQL Enterprise Backup
Copia de seguridad y recuperación sin bloqueo en línea
• Logical backup
– Flexible & Portable Copia de seguridad completa de la instancia de MySQL

• Comodidad y flexibilidad de ver (datos y configuración)

o incluso editar la salida antes de
restaurar Copia de seguridad parcial y restauración

Copias de seguridad incrementales

Recuperación puntual

Copia de seguridad en cinta (SBT)

Validación de copia de seguridad

Multi Plataforma (Windows, Linux, Unix)

Compresión avanzada y Transparent Data Encryption.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 50
MySQL Enterprise Scalability : Thread Pool
• Provee un 20x de mejor
desempeño

• Plugin mejora rendimiento

sostenido sobre incrementos
de conexiones de usuario

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 51

MySQL Enterprise Support
• La mayor organización de ingeniería y • Soporte de Consulta de MySQL
soporte de MySQL (Aprovecha al máximo tus
• Respaldado por los desarrolladores de implementaciones)
MySQL • Solución de problemas remoto
• Soporte de clase mundial, en 29 idiomas ₋ Partitioning review
• Actualizaciones y revisiones de ₋ Schema review
mantenimiento ₋ Query review
₋ Replication review
• 24x7x365
₋ Performance tuning y mas..
• Incidentes ilimitados
• Apoyo consultivo
Obtenga ayuda inmediata para cualquier
• Escala y alcance global problema de MySQL, además de
asesoramiento de expertos

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 52

MySQL Cloud Service, integrado al Oracle Cloud
— Simple: aprovisionamiento
de instancias MySQL en pocos
clicks.
— Integrado: a Oracle Cloud
Services.
— Automatizado: gestion de tus bases
de datos a través de herramientas &
utilierias MySQL
— Enterprise ready: MySQL Enterprise
Edition provee un mejor desempeño,
seguridad, respaldos en línea, monitoring,
etc..

[Link]
10/11/16 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
52
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Live demo — [Link]/mysql

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 56

MySQL 5.7 — Soporte JSON document database
Document & Collections (Unstructured) Table Structured
mysql-js> [Link]()
[
{ "GNP": 828,
"IndepYear": null,
"Name": "Aruba",
"_id": "ABW",
"demographics": {
"LifeExpectancy": 78.4000015258789,
"Population": 103000
},
"geography": {
"Continent": "North America",
"Region": "Caribbean",
"SurfaceArea": 193
}, SQL
...
} SELECT * FROM CountryInfo WHERE GNP > 500000 and
] 240 documents in set (0.00 sec) demographics < 1000000000

CRUD Operations
mysql-js> [Link]("GNP > 500000")
...[output removed]
10 documents in set (0.00 sec)

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 57

 Conclusiones: MySQL Enterprise & Cluster
Mejor TCO, relación precio - desempeño
Alta Disponibilidad
Seguridad — PCI DSS Compliance
Alto Desempeño
Monitoring
On-line backup
Listo para integrarse a su estrategia:
On-Premises
Cloud Computing

Performance
Security
Availability

Copyright © 2017 Oracle and/or its affiliates. All rights reserved. | 58

Oracle database & MySQL

* Custom Ad-Hoc ERP / CRM

60
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
 Performance
Security
Availability

61
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Integración de MySQL con productos Oracle
MySQL se integra a su stack de productos Oracle

• Oracle Linux
• Oracle Clusterware
• Oracle VM
• Oracle Solaris Clustering
• Oracle GoldenGate
• Oracle Secure Backup
• Oracle Audit Vault
• Oracle Enterprise Manager
• Oracle Database Firewall
• La mayoría de los productos de
Oracle Fusion Middleware
– Se suministran con conector de JDBC para MySQL 5.x.

Copyright © 2017, Oracle y sus filiales. Todos los derechos reservados. |