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.

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

   主：ubuntu server1: 192.168.11.109   从：ubuntu server2: 192.168.11.117

• 1
• 2

(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)

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 configuration Note: 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
• 3
• 4
• 5

• • 1.2 Comment out bind-address = 127.0.0.1 under [mysqld]
    
    
    
    

  • 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
    • 1.3.2 Refresh Permissions
    • 1
    • 1.3.3 Show Master

    show master status;

    • 1

• 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

• 1
• 2
• 3
• 4
• 5

• • 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.3.2 Start Slave

       start slave; 

  • 1

  • • 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!

       \G

  • 1

• • 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:
  
  

From:

Configure MYCAT (configured in master virtual machine) both JDK and mycat are placed in the/OPT directory

• 1 Install JDK

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

  Extract jdk

  TAR-ZXVF jdk-7u67-linux-x64.tar.gz  

  • 1
  • 2

  Configure environment variables

  vi/etc/profile  

  • 1
  • 2

  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 export PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin

• 1
• 2
• 3
• 4
• 5

Save exit

• 2 Installing Mycat

  Decompression Mycat

      tar -zxvf Mycat-server-1.6-RELEASE-20161012170031-linux.tar.gz

  • 1
  • 2

  Configuring Environment variables

       vi /etc/profile

  • 1
  • 2

  Add at the end

       export MYCAT_HOME=/opt/mycat     export PATH=$PATH:$MYCAT_HOME/bin

  • 1
  • 2
  • 3

  Save exit

  Make environment variables effective

       source /etc/profile

  • 1
  • 2

• 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

       <UserName="Test" ><PropertyName="Password" >test</Property><PropertyName="Schemas" >testdb</Property><!--table-level DML permissions Settings-<!--<privileges check= "false" > <schema name= "TESTDB" dml= "0110" > <table name= "tb01" dml= "0000" >& lt;/table> <table name= "tb02" dml= "1111" ></table> </schema> </privileges>-</User><user name=  "user" > <property name=" password ">user</ property> <property name=" schemas ">testdb</ property> <property name=" readOnly ">true</ property> </USER>   

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20

• 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

  <SchemaName="TESTDB"Checksqlschema="False"sqlmaxlimit="100"Datanode="DN1" ></Schema><DataNodeName="DN1"datahost="Master"Database="Test"/><DatahostName="Master"maxcon="1000"mincon="10"Balance="1"Writetype="0"Dbtype="MySQL"Dbdriver="Native" ><Heartbeat>show slave status</heartbeat> <writeHost host= "host2" url=  "192.168.11.109:3306" user= "root "password=" root "/> <writehost host= "Host4" url=" 192.168.11.117:3306 "user= "root" password= "root"/> </DATAHOST>         

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

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 /opt/mycat/bin ./mycat start

• 1
• 2

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)

Add a piece of data to the database Wen

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

-utest -ptest -h127.0.0.1 -P8066

• 1

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

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

/opt/mycat/logs/tail -f mycat.log

• 1
• 2

——————————————————— 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:

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

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

3. Inconsistent DB states are easy to obtain without enforcing constraints

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

5. You must create a check constraint error before updating the trigger

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

7. MySQL creates an inefficient execution plan

8. 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