Setting up MySQL InnoDB Cluster with MySQL Shell (plus MySQL Router)

MySQL InnoDB Cluster, which combines MySQL technologies to enable you to deploy and administer a complete integrated high availability solution for MySQL. This content is a high-level overview of InnoDB Cluster.

An InnoDB Cluster consists of at least three MySQL Server instances, and it provides high-availability and scaling features. InnoDB Cluster uses the following MySQL technologies:

MySQL Shell, which is an advanced client and code editor for MySQL.

MySQL Server, and Group Replication, which enables a set of MySQL instances to provide high-availability. InnoDB Cluster provides an alternative, easy to use a programmatic way to work with Group Replication.

MySQL Router, a lightweight middleware that provides transparent routing between your application and InnoDB Cluster.

Below is the script used in the video

MySQL Shell
MySQL server min 3 servers
Mysql Router as a loadbalancer

wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
yum install mysql-shell -y

Install MysqlRouter only on the application server from yum repo.

yum install mysql-shell -y

sudo mysqlsh –pym pip install requests

sudo yum install mysql-router -y

mysqlsh –uri root@localhost
Not_so_secure1!
Not_so_secure1!

Do this on all instance

dba.configureInstance()
provide password
2

innodbcluster
Strong_Password1!

dba.checkInstanceConfiguration(‘innodbcluster@mysql3’)

\c innodbcluster@mysql2:3306
var mycls= dba.createCluster(‘MUGHEES_CLS’)
mycls.describe()
mycls.status()
mycls.SetupRouterAccount(‘myrouter’)

On First Prmry:

mycls.addinstance(‘mysql2:3306’)
mycls.addinstance(‘mysql3:3306’)

On Router server:
mysqlrouter –bootstrap innodbcluster@mysql1 -d myrouter_idc –account=myrouter

mysqlrouter –bootstrap innodbcluster@mysql1 –user mysqlrouter

##############Servers: mysql1:####################

CREATE USER ‘myadmin’@’%’ IDENTIFIED BY ‘Strong_Password1!’
Grant all privileges on *.* to myadmin’@’%’ with grant option;

mysqlsh –uri root@localhost

\sql
select @@hostname;
CREATE DATABASE schoool;USE schoool;
CREATE TABLE if not exists schoool.student(stndid int primary key auto_increment,stdname varchar(100),phone int,classid int);
INSERT schoool.student(stdname,phone,classid) values(‘Aslam’,123456977,7);
INSERT schoool.student(stdname,phone,classid) values(‘Mughees’,123456977,8);
INSERT schoool.student(stdname,phone,classid) values(‘Ahmed’,123456977,9);
SELECT * FROM schoool.student;

select @@hostname;

####################Servers: mysq12####################

mysqlsh root@localhost

\sql
select @@hostname

SELECT * FROM schoool.student;

while [ 1 ] do
sleep 1
docker exec -it mysql-client4 mysql -h 192.168.56.110 -P 6447 -uinnodbcluster -pStrong_Password1! -e “select @@hostname;”
done