Shulou(Shulou.com)06/01 Report--

The first impression of InnoDB Cluster

I remember that at the beginning of MySQL Group Replicatioin, the MySQL world was a sensation, and after years of waiting, we finally had this official high-availability solution. If you say that there are some programs to add, such as MySQL Cluster,MySQL Proxy, the utilization rate of these is still not high, that is, the test is not enough, there are many reasons, so I will not repeat them.

Soon, I had the following basic conversation with a MySQL DBA.

Me: after MySQL GR GA, the automatic switching function is really great, can achieve the separation of read and write, the original MHA scheme can now be done by MGR.

MySQL DBA: if the database fails, this automatic switching process is actually not transparent to the application, because the read and write node is equivalent to drifting to another server unless you do another middleware.

Me: MGR alone can't do this at the moment, it just ensures this kind of switching and high availability at the database level.

MySQL DBA: so the enterprise application of MGR still needs some assistance, which can be regarded as a complete solution.

Soon after, MySQL InnoDB Cluster launched, and I think this solution wants to go one step further than the original MGR. To be honest, I am optimistic about this solution, although not many users are willing to actually use it right now.

If you look at the official architecture diagram, you will find that MGR itself is a part of Innodb Cluster, and there are two components, MySQL Shell,MySQL Router, these three axes are a core component of InnoDB Cluster, and as I said before, you can see a pattern and positioning of MySQL, and he is working very hard to solve the problems criticized in the past.

Ensure that Python meets the requirements before installation

To install InnoDB Cluster, one of the basic requirements of the environment is Python. I've seen that many of the default system versions are 2.6, and its minimum requirements are 2.7 or above, so you still need to prepare this part in advance.

If your system is version 2.6 of Python, consider upgrading to 2.7. refer to the following method.

Download the installation package and deploy

Wget http://python.org/ftp/python/2.7/Python-2.7.tar.bz2-- no-check-certificate

. / configure

Make all

Make install

Make clean

Make distclean

View the version of Python

# / usr/local/bin/python2.7-V

Python 2.7

Make basic environment settings to replace the old Python

Mv / usr/bin/python / usr/bin/python2.6

Ln-s / usr/local/bin/python2.7 / usr/bin/python

Sandbox installation and deployment InnoDB Cluster

Building InnoDB Cluster obviously requires multiple servers, and if you practice testing on one server, there is no problem at all. If you want a faster and more convenient test simulation, you can also use sandbox to do it, first you have to have sandbox, and then the three major components of InnoDB Cluster are MGR,MySQL Shell,MySQL Router, so you can download it directly from the official website.

Then we start the installation journey.

Use the MySQL Shell command mysqlsh to start deployment and create an instance with port 3310

Mysql-js > dba.deploySandboxInstance (3310)

A new MySQL sandbox instance will be created on this host in

/ root/mysql-sandboxes/3310

After entering the password, a MySQL service on port 3310 starts.

Please enter a MySQL root password for the new instance:

Deploying new MySQL instance...

Instance localhost:3310 successfully deployed and started.

Use shell.connect ('root@localhost:3310'); to connect to the instance.

Then create the other two nodes 3320pm 3330

Dba.deploySandboxInstance (3320)

Dba.deploySandboxInstance (3330)

We switch to the 3310 MySQL instance and are ready to start creating Cluster

Mysql-js >\ connect root@localhost:3310

Creating a Session to 'root@localhost:3310'

Enter password:

Closing old connection...

Classic Session successfully established. No default schema selected.

By defining a Cluster variable, node 1 starts the journey of Cluster creation. As you can see from the following information, at least three nodes are required.

Mysql-js > var cluster = dba.createCluster ('testCluster')

A new InnoDB cluster will be created on instance 'root@localhost:3310'.

Creating InnoDB cluster 'testCluster' on' root@localhost:3310'...

Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance () to add MySQL instances.

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

Then add the other two nodes, first adding the node with port 3320

Mysql-js > cluster.addInstance ('root@localhost:3320')

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 'root@localhost:3320':

Adding instance to the cluster...

Join a node with port 3330, and the log is similar to node 2.

Mysql-js > cluster.addInstance ('root@localhost:3330')

At this point, the Cluster is created.

At this time, let's configure MySQL Router and create a soft link to ensure that it can be called normally.

# ln-s / home/innodb_cluster/mysql-router-2.1.3-linux-glibc2.12-x86-64bit/bin/mysqlrouter / usr/bin/mysqlroute

# which mysqlroute

/ usr/bin/mysqlroute

Configure an instance whose startup node of MySQL Router is port 3310

# mysqlrouter-bootstrap root@localhost:3310-user=mysql

At this time, you still have to enter the password, and after success, the binding will be opened.

Please enter MySQL password for root:

Bootstrapping system MySQL Router instance...

MySQL Router has now been configured for the InnoDB cluster 'testCluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'testCluster':

-Read/Write Connections: localhost:6446

-Read/Only Connections: localhost:6447

X protocol connections to cluster 'testCluster':

-Read/Write Connections: localhost:64460

-Read/Only Connections: localhost:64470

As can be seen from the log above, the assigned read-write port is 6446, the read-only port is 6447, and the port for x protocol connection is 6446064470.

Start MySQL Router

# mysqlrouter &

[1] 2913

If you still have any questions about MySQL Router, you can take a look at the installation directory to generate the following configuration file. Let's take a look at the .conf file, some of which are as follows:

[routing:testCluster_default_rw]

Bind_address=0.0.0.0

Bind_port=6446

Destinations=metadata-cache://testCluster/default?role=PRIMARY

Mode=read-write

Protocol=classic

Verification test

We tried to use 6446 to connect and log in. At this time, the connection entry was opened through MySQL Shell, and MySQL Router transferred to the read and write node 3310 inside.

# mysqlsh-uri root@localhost:6446

Creating a Session to 'root@localhost:6446'

Enter password:

Classic Session successfully established. No default schema selected.

Welcome to MySQL Shell 1.0.9

Switch to sql mode and look at the port to know which node it is.

Mysql-js >\ sql

Switching to SQL mode... Commands end with

Mysql-sql > select @ @ port

+-+

| | @ @ port |

+-+

| | 3310 |

+-+

1 row in set (0.00 sec)

If you switch to script mode to view the status of the instance, you can use the API defined in it. The output is all JSON strings.

Mysql-js > dba.configureLocalInstance ('root@127.0.0.1:3310')

Please provide the password for 'root@127.0.0.1:3310':

Detected as sandbox instance.

Validating MySQL configuration file at: / root/mysql-sandboxes/3310/my.cnf

Validating instance...

The instance '127.0.0.1 3310' is valid for Cluster usage

You can now use it in an InnoDB Cluster.

{

"status": "ok"

}

If you look at the information of Cluster, you can see the status information of the read-write node and read-only node below

Mysql-js > dba.getCluster ()

Var cluster = dba.getCluster ()

Get the information about Cluster

Mysql-js > cluster.status ()

{

"clusterName": "testCluster"

"defaultReplicaSet": {

"name": "default"

"primary": "localhost:3310"

"status": "OK"

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure."

"topology": {

"localhost:3310": {

"address": "localhost:3310"

"mode": "Rhampw"

"readReplicas": {}

"role": "HA"

"status": "ONLINE"

}

"localhost:3320": {

"address": "localhost:3320"

"mode": "Rhambo O"

"readReplicas": {}

"role": "HA"

"status": "ONLINE"

}

"localhost:3330": {

"address": "localhost:3330"

"mode": "Rhambo O"

"readReplicas": {}

"role": "HA"

"status": "ONLINE"

}

}

}

}

You can also use describe to get some basic information

Mysql-js > cluster.describe ()

{

"clusterName": "testCluster"

"defaultReplicaSet": {

"instances": [

{

"host": "localhost:3310"

"label": "localhost:3310"

"role": "HA"

}

{

"host": "localhost:3320"

"label": "localhost:3320"

"role": "HA"

}

{

"host": "localhost:3330"

"label": "localhost:3330"

"role": "HA"

}

]

"name": "default"

}

}

Handover test

Of course, just watching without practice or fake style, let's switch and see if it works.

To simulate a problem with a node, you can use the killSandboxInstance method.

Mysql-js > dba.killSandboxInstance (3310)

The MySQL sandbox instance on this host in

/ root/mysql-sandboxes/3310 will be killed

Killing MySQL instance...

Instance localhost:3310 successfully killed.

The node is cleaned and no processes exist.

# ps-ef | grep mysql | grep 3310

#

We still use port 6446 to unify the connection, and at this time we switch to the MySQL service on port 3320.

# mysqlsh-uri root@localhost:6446

Creating a Session to 'root@localhost:6446'

Enter password:

Classic Session successfully established. No default schema selected.

Welcome to MySQL Shell 1.0.9

Mysql-js >\ sql

Switching to SQL mode... Commands end with

Mysql-sql > select @ @ port

+-+

| | @ @ port |

+-+

| | 3320 |

+-+

1 row in set (0.00 sec)

So there is no problem with the switching part, and we start the "lost" node again.

# mysqlsh-uri root@localhost:6446

Mysql-js > dba.startSandboxInstance (3310)

The MySQL sandbox instance on this host in

/ root/mysql-sandboxes/3310 will be started

Starting MySQL instance...

Instance localhost:3310 successfully started.

At this time, check the status of the Cluster again. 3320 is the master and 3310 is the read-only node.

Mysql-js > dba.getCluster ()

Incorporate Node 2 into the Cluster

Mysql-js > cluster.rejoinInstance ('root@localhost:3310')

Rejoining the instance to the InnoDB cluster. Depending on the original

Problem that made the instance unavailable, the rejoin operation might not be

Successful and further manual steps will be needed to fix the underlying

Problem.

Please monitor the output of the rejoin operation and take necessary action if

The instance cannot rejoin.

Please provide the password for 'root@localhost:3310':

Rejoining instance to the cluster...

The instance 'root@localhost:3310' was successfully rejoined on the cluster.

The instance 'localhost:3310' was successfully added to the MySQL Cluster.

Mysql-js >

It is conceivable that if it is a production system with so many logs, this process is really confusing.

Finally, we will have a switched Cluster status.

Mysql-js > cluster.status ()

{

"clusterName": "testCluster"

"defaultReplicaSet": {

"name": "default"

"primary": "localhost:3320"

"status": "OK"

"statusText": "Cluster is ONLINE and can tolerate up to ONE failure."

"topology": {

"localhost:3310": {

"address": "localhost:3310"

"mode": "Rhambo O"

"readReplicas": {}

"role": "HA"

"status": "ONLINE"

}

"localhost:3320": {

"address": "localhost:3320"

"mode": "Rhampw"

"readReplicas": {}

"role": "HA"

"status": "ONLINE"

}

"localhost:3330": {

"address": "localhost:3330"

"mode": "Rhambo O"

"readReplicas": {}

"role": "HA"

"status": "ONLINE"

}

}

}

}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.