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

 

About Mughees Ahmed

Over 2-year experience of Administration in Oracle, SQL Server, and MySQL databases using various tools and technologies. Keen on learning new database technologies having very good analytical skills. Working knowledge of Red Hat Linux, UNIX, Solaris and Windows Server 2012 is a valuable addition to my knowledge desk. KNOWLEDGE & SKILLS • Oracle Database Server 10g/11g/12c. • Oracle RAC, Data guard. • Oracle Golden Gate (Oracle to Oracle, Oracle to MySQL and MySQL to Oracle) • Oracle Enterprise Manager Grid Control, Toad, SQL developer. • SQL Server 2005/2008/2012/2016. • SQL Server Failover clustering, mirroring & log shipping, Always On availability groups. • MySQL 5 Administration, MySQL Workbench, MySQL Enterprise Monitor, SQLyog • MySQL NDB Cluster Installation,Administration. • MySQL Asynchronous/Semi-synchronous replication. • Oracle OEM Contact me on [email protected]

Leave a Reply

Your email address will not be published. Required fields are marked *