MySQL 8.0 InnoDB Cluster demo

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. |
MySQL High Availability
InnoDB Cluster
Keith Hollman
MySQL Principal Solution Architect
keith.hollman@oracle.com
DEMO

Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for
information purposes only, and may not be incorporated into any contract. It is not a
commitment to deliver any material, code, or functionality, and should not be relied upon
in making purchasing decisions. The development, release, and timing of any features or
functionality described for Oracle’s products remains at the sole discretion of Oracle.

Install MySQL 8.0
• We have previously downloaded the latest comercial rpm bundles from
https://edelivery.oracle.com.
$ sudo yum install -y python numactl
$ sudo yum install -y mysql-commercial-*8.0.12*rpm mysql-router-commercial-
8.0.12-1.1.el7.x86_64.rpm mysql-shell-commercial-8.0.12-1.1.el7.x86_64.rpm
$ sudo systemctl start mysqld.service
$ sudo systemctl enable mysqld.service

Post-Install
• Change the root password:
$ sudo grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log
|tail -1
$ mysql -uroot -p
SET sql_log_bin = OFF;
alter user 'root'@'localhost' identified by 'Oracle20!8';
create user 'ic'@'%' identified by 'Oracle20!8';
grant all on *.* to 'ic'@'%' with grant option;
flush privileges;
SET sql_log_bin = ON;
exit

MySQL Shell CLI
• Just so that we understand what this means:
$ mysqlsh --uri root@localhost:3306
Please provide the password for 'root@localhost:3306': **********
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): y
• Making things simple, i.e. password is cached for our o.s. session.

Preparing the instances
• Check that the instances are a good candidates for joing the cluster: (Run
commands below for all three instances)
dba.checkInstanceConfiguration('ic@ic1:3306');
• If check instance spots any issues, solve these by running:
dba.configureInstance('ic@ic1:3306');
• All parameters that need
changing will be changed in the
my.cnf if we accept the
interactive questions:

Preparing the instances (cont)
• Configuration options added by configureInstance ("SET PERSIST") can be
found in file: mysqldata/mysqld-auto.cnf You can also view these changes
in MySQL by running:
c root@localhost:3306
sql
select * from performance_schema.persisted_variables;
• To see all variables and their source run:
mysql -uroot -e "SELECT * FROM performance_schema.variables_info WHERE
variable_source != 'COMPILED';"

Create Cluster
• On just one instance, start shell and run:
connect ic@ic1:3306
cluster=dba.createCluster("mycluster");
cluster.status();
cluster.addInstance("ic@ic2:3306");
cluster.addInstance("ic@ic3:3306");
cluster.status();
• You can connect from any host, eg. ic2, using mysqlsh to any other
instance, eg. c ic@ic1:3306 and follow the same steps, consecutively, on
the same instance.

Checking InnoDB Cluster status
• Connect IDc to a specific MySQL instance using shell:
mysqlsh -uic -hic2 -P3306
• And run:
cluster = dba.getCluster();
cluster.status();
• From performance_schema:
sql
SELECT * FROM performance_schema.replication_group_membersG

• We will run the MySQL Router process on
ic2:
$ sudo -i
$ mkdir -p /opt/mysql/myrouter
$ chown -R mysql:mysql /opt/mysql/myrouter
$ cd /opt/mysql
$ mysqlrouter --bootstrap ic@ic2:3306 -d
/opt/mysql/myrouter -u mysql
Please enter MySQL password for ic:
Bootstrapping MySQL Router instance at
'/opt/mysql/myrouter'...
Executing statements failed with: 'Error executing MySQL
query: The MySQL server is running with the --super-read-only
option so it cannot execute this statement (1290)' (1290),
trying to connect to another node
Fetching Group Replication Members
disconnecting from mysql-server
trying to connecting to mysql-server at ic1:3306
Checking for old Router accounts
Creating account mysql_router3_53c1tbork49d@'%'
MySQL Router has now been configured for the InnoDB cluster
'mycluster'.
The following connection information can be used to connect
to the cluster.
Classic MySQL protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470
• As the master was ic1, and we ran the
bootstrap from ic2, super-read-only
mode was detected and hence the
master, ic1, was used.
• Now to start the Router process:
$ ./myrouter/start.sh
MySQL Router

• On ic1, test the connection to both the RW
port, 6446, and the RO port, 6447 of
Router, that, remember, is running on ic2:
$ mysql -uic -p -P6446 -hic2 -e "select @@hostname"
• The same on ic3:
• You should see all RW connections show
ic1, as well as all RO connections use
round-robin between the RO slaves, i.e.
ic2 & ic3.
MySQL Router: testing

More examples & Information
• https://mysqlserverteam.com/mysql-innodb-cluster-8-0-a-hands-on-
tutorial/
• https://github.com/wwwted/MySQL-InnoDB-Cluster-3VM-Setup
• https://mysqlmed.wordpress.com/2017/11/09/innodb-cluster-setting-up-
production-for-disaster-1-2/ (MySQL 5.7)

Preguntas?

MySQL 8.0 InnoDB Cluster demo

Related slideshows

More Related Content

What's hot

What's hot (20)

Similar to MySQL 8.0 InnoDB Cluster demo

Similar to MySQL 8.0 InnoDB Cluster demo (20)

More from Keith Hollman

More from Keith Hollman (7)

Recently uploaded

Recently uploaded (20)

MySQL 8.0 InnoDB Cluster demo