Mycat implements Mysql master-slave read/write splitting and mycatmysql master-slave read/write

Mycat implements Mysql master-slave read/write splitting and mycatmysql master-slave read/write

I. Overview

There are many online Mycat principles. I will not detail them here. For me, Mycat has the following functions:

1. Mysql Master/Slave read/write splitting

2. Mysql large table sharding

3. read/write splitting and sharding for other databases, such as Oracle, MSSQL, and DB2.

The reason why Mysql is listed separately at is that it is not commonly used and JDBC is used, Mysql protocol is directly used for connecting to Mysql.

For the sharding function of Mycat, due to a few serious bugs and a series of concurrency problems caused by the sharding, I gave up after the test. Here I will only discuss the read/write splitting.

Ii. installation and configuration

1. Install JDK (JDK 1.7 or above is required)

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 _ 80 export PATH = $ JAVA_HOME/bin: $ PATH and then source/etc/profile take effect, and use java-version to check the new jdk version.

2. Install 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. File description

As shown above, we installed mycat to the/usr/local/mycat directory. We only need to pay attention to the following two folders: conf and logs:

1) in the logs file, you only need to pay attention to wrapper. log. This is the log of mycat.

2) In the conf file, you only need to pay attention to server. xml and schema. xml. Most of the others are sharded. This article does not discuss them.

Iii. read/write splitting Configuration

1) Description of the current environment:

Mysql master: mysql01 192.168.000068

From: mysql02 192.168.20.69

Mycat is installed on 192.168.20.68. Our goal is to implement read/write splitting for a library named leo (also called schema.

2) server. xml configuration

Most parameters for Mycat performance tuning are in this file. We do not care about it yet. to configure read/write splitting, you only need to modify the file as follows:

We have set the username and password of mycat to root and 123456. This account and password can be set freely for external program connection. The user/user Read-Only users below can be deleted by default, modify the schema without deleting it.

3) schema. xml

This file is the configuration file used for specific configuration of read/write splitting. If you are not talking about it, paste the specific configuration file directly. You do not need to modify it elsewhere.

In the figure, one logic node node1 is set. This node 1 corresponds to the logic host host1, and host1 is configured with writehost = mysql01, readhost = mysql02, the real database connection account and password leo/leo are only available here.

In the preceding xml file, we only need to pay attention to four tags: schema/table/dataNode/dataHost, writehost is the sub-tag of dataHost, and readhost is the sub-tag of writehost.

Schema defines the database for read/write splitting, preferably the real database name. Table defines table information. Only partitions are used. Therefore, all annotations are made directly.

DataNode defines the logical host dataHost and database name. The database name must be real. DataHost defines the physical host corresponding to the logical host. There can be multiple write hosts, and each write host can correspond to multiple read hosts.

Writehost and readhost contain the specific database connection information.

DbDriver indicates that the mysql protocol is used, and JDBC is used for other databases. Other parameters are not described in detail here.

4) read/write Splitting Test

Start mycat:/usr/local/mycat/bin/mycat start

Connect to mycat: mysql-uroot-p123456-P8066-h127.0.0.1

Mycat automatically connects to the slave database, but any DML operation will be forwarded to the master database. the permissions of this account are equivalent to the account permissions you configured in writehost and readhost.