Mycat for simple MySQL cluster load balancing

What is Mycat?

Simple to understand as a MySQL middleware, it supports shunt, heartbeat-based automatic failover, support read and write separation, support MySQL master-slave, based on NIO management thread of high concurrency ...
See official website: http://www.mycat.io/

Why do I need a MySQL cluster?

A large-scale distributed system performance bottleneck, the most vulnerable is the connection, one is the client and the back end of the connection, the other is the backend and the database connection, plainly speaking is the sending side of the request too much, the receiver can receive and processing requests are not many, in the client and back end can use similar nginx load balancer solution , and the MySQL cluster can be implemented in backend and database with similar mycat load balancer, which improves the overall performance of MySQL.

Write a picture description here

To begin our journey, before this, you need to prepare the following:

12 servers (minimum), one Master (master) one from (slave), here with VMware installed in the two Ubuntu server virtual machine demo, each VM network connection mode is set to bridge mode, the two virtual machine IP is as follows

Main: Ubuntu server1:192.168.11.109
From: Ubuntu server2:192.168.11.117
(VMware installs Ubuntu server16.04 in the last two blog posts are described in detail:
http://blog.csdn.net/change_on/article/details/74969314
http://blog.csdn.net/change_on/article/details/74979993)

Write a picture description here

Write a picture description here

2 virtual machines are installed MySQL 5.7, respectively.

More than 3 jdk1.7, here take jdk-7u67-linux-x64.tar.gz as an example

4 Mycat, taking mycat-server-1.6-release-20161012170031-linux.tar.gz as an example

Steps to build a mycat+mysql cluster

1 MySQL master-slave configuration

1.1 Primary Virtual machine configuration (192.168.11.109)

1.2 from the virtual machine configuration (192.168.11.117)

1.3 Testing

2 Configuring Mycat

2.1 Installing the JDK

2.2 Installing Mycat

2.3 Configuring Server.xml

2.4 Configuring Schema.xml

2.5 Testing
MySQL Master-slave configuration

Note: The mysql5.7 configuration file is/ETC/MYSQL/MYSQL.CONF.D/MYSQLD.CNF! Not/ETC/MYSQL/MY.CNF!

1 Primary virtual machine configuration (192.168.11.109)

1.1 Modify/ETC/MYSQL/MYSQL.CONF.D/MYSQLD.CNF File

1.1.1 Add the following under [Mysqld]
Vi/etc/mysql/mysql.conf.d/mysqld.cnf
Character_set_server = UTF8
Init_connect = ' SET NAMES UTF8 '
Log-bin=mysql-bin
server-id=109
1.2 Comment out bind-address = 127.0.0.1 under [mysqld]

Write a picture description here

1.3 Configuring the Master

1.3.1 into MySQL and create a user with slave copy rights wen
GRANT REPLICATION SLAVE on * * to ' wen ' @ '% ' identified by ' Wen ';
1.3.2 Refresh Permissions
Flush privileges;
1.3.3 Show Master
Show master status;
2 from the virtual machine configuration (192.168.11.117)
2.1 Modify/ETC/MYSQL/MYSQL.CONF.D/MYSQLD.CNF File
2.1.1 Add the following under [Mysqld]
Vi/etc/mysql/mysql.conf.d/mysqld.cnf
Character_set_server = UTF8
Init_connect = ' SET NAMES UTF8 '
Log-bin=mysql-bin
server-id=117
2.2 Comment out bind-address = 127.0.0.1 under [mysqld]

2.3 Configuring Slave

2.3.1 into MySQL, reset slave
Here Master_log_file and Master_log_pos are in the master virtual machine's MySQL master, as shown in the figure above!

Stop slave;
Reset slave;
Change Master to master_host= ' 192.168.11.109 ', master_user= ' Wen ', master_password= ' Wen ', master_log_file= ' mysql-bin.000013 ', master_log_pos=1609;
1
2.3.2 Start Slave
Start slave;

2.2.3 Display Slave

One of the slave_io_running and slave_sql_running must be Yes to indicate that the Slave started correctly!

Show slave status \g
1
1

Write a picture description here

2.4 Testing
Create a database in the master virtual machine and then view it in the slave virtual machine, if there is a database just tested in slave, the MySQL master-slave configuration succeeds.

Main:

Write a picture description here

From:

Write a picture description here

Configuring MYCAT (configured in the Master virtual machine)

Both JDK and mycat packages are placed in the/OPT directory

1 Installing the JDK

Mycat depends on the environment above java1.7, first install jdk1.7

Unzip the JDK

TAR-ZXVF jdk-7u67-linux-x64.tar.gz
Configuring Environment variables

Vi/etc/profile
Add at the end

# set for Java
Export java_home=/opt/jdk1.7.0_67
Export Jre_home= $JAVA _home/jre
Export classpath=.: $CLASSPATH: $JAVA _home/lib: $JRE _home/lib

Save exit

2 Installing Mycat

Decompression Mycat

TAR-ZXVF mycat-server-1.6-release-20161012170031-linux.tar.gz
Configuring Environment variables

Vi/etc/profile
Add at the end

Export Mycat_home=/opt/mycat
Export path= $PATH: $MYCAT _home/bin
Save exit

Make environment variables effective

Source/etc/profile

Write a picture description here

3. Configure Server.xml

To Mycat's conf directory, modify the Server.xml file to add test and user, test user, password also for test,user user, password

<user name= "Test" >
<property name= "Password" >test</property>
<property name= "Schemas" >TESTDB</property>

<!--table-level DML permissions Settings-
<!--
<privileges check= "false" >
<schema name= "TESTDB" dml= "0110" >
<table name= "tb01" dml= "0000" ></table>
<table name= "tb02" dml= "1111" ></table>
</schema>
</privileges>
-
</user>

<user name= "User" >
<property name= "Password" > www.wmyl88.com user</property>
<property name= "schemas" > Www.wmyl15.com testdb</property>
<property name= "ReadOnly" >true</property>
</user>

4. Configure Schema.xml

Set the IP and port of the read and write server, and the cluster policy. The name of the schema here is consistent with the schema of the Server.xml, database corresponds to the existing databases in MySQL, that is, the Mycat TESTDB agent of the master/slave virtual machine MySQL test Database

<schema name= "Www.hbwfjx.cn/TESTDB" www.feifanshifan8.cn checksqlschema= "false" sqlmaxlimit= "+" datanode= "DN1" >
</schema>

<datanode name= "dn1" datahost= "master" database= "test"/>

<datahost name= "Master" maxcon= "www.caihonyule.com mincon=" balance= "1" writetype= "0" dbtype= "MySQL" dbdriver= "Native" >
<writehost host= "Host2" www.wmyl11.com url= "192. www.yigozongdai2.cn 168.11.109:3306 "user=" root "password=" root "/>
<writehost host= "Host4" url= "192.168.11.117:3306" user= "root" password= "root"/>
Show slave status represents a clustering strategy that applies only in the context of a master-slave, when the master is down, from being able to act as primary and

Balance Property

Balance= "0", does not open the read and write separation mechanism, all read operations are sent to the currently available Writehost
Balance= "1", all readhost and stand by writehost participate in load balancing of SELECT statements
Balance= "2", all read operations are randomly distributed on the Writehost, Readhost.
Balance= "3", all read requests are randomly distributed to wiriterhost corresponding readhost execution, Writerhost does not burden reading pressure

Writetype Property

Load balancing type, there are currently 3 types of values:
Writetype= "0", all write operations sent to the configuration of the first Writehost, the first to hang a cut to also survive the second writehost, after the reboot has been switched after the switch is recorded in the configuration file: Dnindex.properties
Writetype= "1", all write operations are sent randomly to the configured Writehost

5 Start Mycat
CD www.lieqibiji.com//opt/mycat/bin
./mycat Start

Supplement: Common Commands for Mycat

Shut down

Mycat stop

Restart

Mycat restart

View Mycat Status

Mycat status

6 Testing
Now mycat can proxy MySQL, with Navicat connection Mycat (8066 port)

Write a picture description here

Add a piece of data to the database Wen

Write a picture description here

Now go to the main virtual machine to view the database with the command line and see if the data you just wrote into Mycat in Navicat is displayed.

Log in to the Mycat of the master virtual machine using the Mycat account password, and view and add a piece of data tom

mysql-utest-ptest-h127.0.0.1-p8066
1
1

Write a picture description here

Then cut to MySQL from the virtual machine, log in with Mysql-uroot-p, and look at the data that has just been added to the mycat of the main virtual machine Tom can't see from the virtual function

Write a picture description here

If you want to see the details of the read and write separation, you can view the log in the logs directory

/opt/www.yongshiyule178.com mycat/logs/
Tail-f Mycat.log
——————————————————— Split Line —————————————————————-

The problem with MySQL is actually due to a series of soft rib decisions, so we have to use middleware or other solutions to solve, including:

Make a choice between forcing constraints and transactional and full-text indexing (InnoDb vs MyISAM)

It is not easy to "impersonate" a transaction in the client code

Inconsistent DB states are easy to obtain without enforcing constraints

If there is no full-text search, it will become crazy, such as% y%

You must create a check constraint error before updating the trigger

When data gets too big, MySQL's ability to withstand is bad.

MySQL creates an inefficient execution plan

MySQL has more than one connection problem (preferably multiple connections)

...

But! Oracle is the solution to all of these problems and is a complete DBMS: transactions, inspection contracts, many options for views, full-text Search ...

So the essence of the problem is: Cost! , MySQL is annoying, but has to use!

Mycat for simple MySQL cluster load balancing