Mycat realization of MySQL master-slave read/write separation

I. Overview

On the principle of mycat on the web there are many, here no longer detailed, for me, mycat the main functions are as follows:

1.Mysql master-Slave read/write separation

2.Mysql Large Table Shard

3. Read-write separation and sharding of other databases such as ORACLE,MSSQL,DB2.

The 3rd one is listed solely because it is not used, and the JDBC used to connect to MySQL is directly using the MySQL protocol.

For Mycat shard function, because of encountering a few very serious bug, at the same time Shard brings a series of concurrency problem is more complicated, so after testing gave up, here only discusses read and write separation.

Second, installation configuration

1. Install JDK (requires at least JDK1.7)

TAR-ZXVF JDK-7U80-LINUX-X64.TAR.GZMV jdk1.7.0_80/usr/localvi/etc/profile add: Export java_home=/usr/local/jdk1.7.0_ 80export path= $JAVA _home/bin: After $PATH the source/etc/profile takes effect and checks the new JDK version with Java-version.

2. Installing Mycat

#useradd mycat #passwd mycat--Set password #tar xvf mycat-server-1.6-release-20161028204710-linux.tar.gz#mv mycat//usr/local/ #chown-R mycat.mycat/usr/local/mycat/#vi/usr/local/mycat/conf/wrapper.conf  wrapper.java.command=%java_home %/bin/java  

3. Document Description

As we installed the Mycat into the/usr/local/mycat directory, we need to focus on only 2 folders conf and Logs folders:

1) Logs file only need to pay attention to Wrapper.log, this is Mycat's log

2) conf file only need to pay attention to Server.xml and Schema.xml, the others are mostly shard-related, this article is not discussed.

Third, read and write separation configuration

1) Current Environment Description:

Mysql Master: Mysql01 192.168.20.68

From: Mysql02 192.168.20.69

Mycat installed on the 192.168.20.68, our goal is to achieve a library called Leo (also known as schema) of the read and write separation.

2) Server.xml Configuration

Most of the parameters about Mycat performance tuning are in this file, we do not care, configuration read and write detach this file only need to change the following parts:

We set the Mycat username and password is root and 123456, this account password is external program connection can be arbitrarily set, the bottom of the User/user read-only user is the default can be deleted, do not delete the following schema can be modified.

3) Schema.xml

This file is used to specifically configure the read and write separation of the configuration file, not much to say directly paste out the specific configuration files, other places without modification.

The figure is set up 1 logical nodes Node1, this node1 corresponding logical host Host1,host1 configured WRITEHOST=MYSQL01,READHOST=MYSQL02, real database Connection account password Leo/leo also appears here.

In the above XML file we only need to pay attention to 4 tags, schema/table/datanode/datahost, writehost is datahost sub-tag, Readhost is the sub-label Writehost.

Where the schema defines the library to read and write, preferably a real library name. Table information is defined, and only shards are used so that all comments are made directly here.

Datanode defines the logical host Datahost and the library name, where the library name must be true. Datahost defines the physical host corresponding to the logical host, can have multiple write hosts, each write host can correspond to more than one read host.

Writehost and Readhost contain specific database connection information.

Dbdriver indicates that the MySQL protocol is used and the other databases choose JDBC. As for the other parameters here do not do a detailed explanation, many online.

4) Read/write separation test

Start Mycat:/usr/local/mycat/bin/mycat Start

Connection mycat:mysql-uroot-p123456-p8066-h127.0.0.1

Mycat automatically connects to the library, but a DML operation is forwarded to the main library, which is equal to the account permissions you configured in Writehost and Readhost.

Mycat implementation of MySQL master-slave read/write separation