MySQL read/write splitting using MyCAT

MySQL read/write splitting using MyCAT

MyCAT is a database middleware developed based on Alibaba's open-source cobar product. It is jointly developed and open-source by several talented people with lofty ideals. Provides highly available data sharding clusters, automatic failover, high availability, read/write splitting, MySQL dual-master and multi-slave modes, and one-master and multi-slave modes, and supports global tables, data is automatically partitioned to multiple nodes for efficient table association query. It supports the unique partition policy based on the E-R relationship, and realizes the efficient table association query multi-platform support. The deployment and implementation are simple.

In practice today, use MyCAT to implement MySQL read/write splitting. 1. configure master-slave data synchronization on the MySQL end. use MyCAT to implement read/write splitting, and configure MySQL-side Master/Slave Data Synchronization. Here we will detail how to use MyCAT to implement data read/write splitting.

Create Database reading and writing Databases

Create two major databases under two different hosts, read database r and write database w, and create three database shards (db1, db2, db3) under read database r ), create three shards (db1, db2, and db3) under the write database w ).

1. db1 multipart script

/*
Navicat MySQL Data Transfer
Source Server: mysql
Source Server Version: 50527
Source Host: localhost: 3306
Source Database: db1
Target Server Type: MYSQL
Target Server versions: 50527
File Encoding: 65001
Date: 2016-01-27 15:48:40
*/
SET FOREIGN_KEY_CHECKS = 0;
------------------------------
-- Table structure for 'company'
------------------------------
Drop table if exists 'company ';
Create table 'company '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of company
------------------------------
------------------------------
-- Table structure for 'customer'
------------------------------
Drop table if exists 'customer ';
Create table 'customer '(
'Id' bigint (20) not null default '0 ',
'Sharding _ id' bigint (20) default null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of customer
------------------------------
Insert into 'customer' VALUES ('1', null );
Insert into 'customer' VALUES ('4', '123 ');
------------------------------
-- Table structure for 'Employee'
------------------------------
Drop table if exists 'employe ';
Create table 'Employee '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of employee
------------------------------
------------------------------
-- Table structure for 'goods'
------------------------------
Drop table if exists 'goods ';
Create table 'goods '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of goods
------------------------------
Insert into 'goods' VALUES ('11 ');
------------------------------
-- Table structure for 'hotnew'
------------------------------
Drop table if exists 'hotnews ';
Create table 'hotnews '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of hotnews
------------------------------
------------------------------
-- Table structure for 'mycat _ sequence'
------------------------------
Drop table if exists 'mycat _ sequence ';
Create table 'mycat _ sequence '(
'Name' varchar (50) not null,
'Current _ value' int (11) not null,
'Credentials' int (11) not null default '123 ',
Primary key ('name ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of mycat_sequence
------------------------------
Insert into 'mycat _ sequence 'VALUES ('global', '20160301', '20160301 ');
------------------------------
-- Table structure for 'Orders'
------------------------------
Drop table if exists 'Orders ';
Create table 'Orders '(
'Id' bigint (20) not null default '0 ',
'Customer _ id' bigint (20) default null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of orders
------------------------------
Insert into 'Orders 'VALUES ('1', '1 ');
Insert into 'Orders 'VALUES ('4', '4 ');
------------------------------
-- Table structure for 'travelrecord'
------------------------------
Drop table if exists 'travelrecord ';
Create table 'travelrecord '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of travelrecord
------------------------------
Insert into 'travelrecord 'VALUES ('1 ');
Insert into 'travelrecord 'VALUES ('4 ');
Insert into 'travelrecord 'VALUES ('20140901 ');
Insert into 'travelrecord 'VALUES ('20140901 ');
------------------------------
-- Function structure for 'mycat _ seq_currval'
------------------------------
Drop function if exists 'mycat _ seq_currval ';
DELIMITER ;;
Create definer = 'root' @ '%' FUNCTION 'mycat _ seq_currval' (seq_name VARCHAR (50) RETURNS varchar (64) CHARSET utf8
DETERMINISTIC
Begin declare retval VARCHAR (64 );
SET retval = "-999999999, null ";
SELECT concat (CAST (current_value as char), ",", CAST (increment as char) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name;
RETURN retval;
END
;;
DELIMITER;
------------------------------
-- Function structure for 'mycat _ seq_nextval'
------------------------------
Drop function if exists 'mycat _ seq_nextval ';
DELIMITER ;;
Create definer = 'root' @ '%' FUNCTION 'mycat _ seq_nextval '(seq_name VARCHAR (50) RETURNS varchar (64) CHARSET utf8
DETERMINISTIC
Begin update MYCAT_SEQUENCE SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval (seq_name );
END
;;
DELIMITER;
------------------------------
-- Function structure for 'mycat _ seq_setval'
------------------------------
Drop function if exists 'mycat _ seq_setval ';
DELIMITER ;;
Create definer = 'root' @ '%' FUNCTION 'mycat _ seq_setval '(seq_name VARCHAR (50), value INTEGER) RETURNS varchar (64) CHARSET utf8
DETERMINISTIC
Begin update MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name;
RETURN mycat_seq_currval (seq_name );
END
;;
DELIMITER;

2. db2 partition script

/*
Navicat MySQL Data Transfer
Source Server: mysql
Source Server Version: 50527
Source Host: localhost: 3306
Source Database: db2
Target Server Type: MYSQL
Target Server versions: 50527
File Encoding: 65001
Date: 2016-01-27 15:48:50
*/
SET FOREIGN_KEY_CHECKS = 0;
------------------------------
-- Table structure for 'company'
------------------------------
Drop table if exists 'company ';
Create table 'company '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of company
------------------------------
------------------------------
-- Table structure for 'customer'
------------------------------
Drop table if exists 'customer ';
Create table 'customer '(
'Id' bigint (20) not null default '0 ',
'Sharding _ id' bigint (20) default null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of customer
------------------------------
Insert into 'customer' VALUES ('2', null );
Insert into 'customer' VALUES ('5', '123 ');
------------------------------
-- Table structure for 'Employee'
------------------------------
Drop table if exists 'employe ';
Create table 'Employee '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of employee
------------------------------
------------------------------
-- Table structure for 'goods'
------------------------------
Drop table if exists 'goods ';
Create table 'goods '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of goods
------------------------------
Insert into 'goods' VALUES ('11 ');
------------------------------
-- Table structure for 'hotnew'
------------------------------
Drop table if exists 'hotnews ';
Create table 'hotnews '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of hotnews
------------------------------
Insert into 'hotnews' VALUES ('1 ');
------------------------------
-- Table structure for 'Orders'
------------------------------
Drop table if exists 'Orders ';
Create table 'Orders '(
'Id' bigint (20) not null default '0 ',
'Customer _ id' bigint (20) default null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of orders
------------------------------
Insert into 'Orders 'VALUES ('2', '2 ');
Insert into 'Orders 'VALUES ('5', '5 ');
------------------------------
-- Table structure for 'travelrecord'
------------------------------
Drop table if exists 'travelrecord ';
Create table 'travelrecord '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of travelrecord
------------------------------
Insert into 'travelrecord 'VALUES ('2 ');

3. db3 multipart script

/*
Navicat MySQL Data Transfer
Source Server: mysql
Source Server Version: 50527
Source Host: localhost: 3306
Source Database: db3
Target Server Type: MYSQL
Target Server versions: 50527
File Encoding: 65001
Date: 2016-01-27 15:48:58
*/
SET FOREIGN_KEY_CHECKS = 0;
------------------------------
-- Table structure for 'company'
------------------------------
Drop table if exists 'company ';
Create table 'company '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of company
------------------------------
------------------------------
-- Table structure for 'hotnew'
------------------------------
Drop table if exists 'hotnews ';
Create table 'hotnews '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of hotnews
------------------------------
Insert into 'hotnews' VALUES ('2 ');
------------------------------
-- Table structure for 'travelrecord'
------------------------------
Drop table if exists 'travelrecord ';
Create table 'travelrecord '(
'Id' bigint (20) not null default '0 ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
------------------------------
-- Records of travelrecord
------------------------------
Insert into 'travelrecord 'VALUES ('3 ');
 

 

Create three shards in two read/write databases respectively
 
Configure read/write splitting in schema. xml

<? Xml version = "1.0"?>
<! DOCTYPE mycat: schema SYSTEM "schema. dtd">
<Mycat: schema xmlns: mycat = "http://org.opencloudb/">
<Schema name = "TESTDB" checkSQLschema = "false" sqlMaxLimit = "100">
<! -- Auto sharding by id (long) -->
<Table name = "travelrecord" dataNode = "dn1, dn2, dn3" rule = "auto-sharding-long"/>
<! -- Global table is auto cloned to all defined data nodes, so can join
With any table whose sharding node is in the same data node -->
<Table name = "company" primaryKey = "ID" type = "global" dataNode = "dn1, dn2, dn3"/>
<Table name = "goods" primaryKey = "ID" type = "global" dataNode = "dn1, dn2"/>
<! -- Random sharding using mod sharind rule -->
<Table name = "hotnews" primaryKey = "ID" dataNode = "dn1, dn2, dn3"
Rule = "mod-long"/>
<Table name = "employee" primaryKey = "ID" dataNode = "dn1, dn2"
Rule = "sharding-by-intfile"/>
<Table name = "customer" primaryKey = "ID" dataNode = "dn1, dn2"
Rule = "sharding-by-intfile">
<ChildTable name = "orders" primaryKey = "ID" joinKey = "customer_id"
ParentKey = "id">
</ChildTable>
</Table>
<Table name = "mycat_sequence" dataNode = "dn1"/>
</Schema>
<DataNode name = "dn1" dataHost = "localhost1" database = "db1"/>
<DataNode name = "dn2" dataHost = "localhost1" database = "db2"/>
<DataNode name = "dn3" dataHost = "localhost1" database = "db3"/>
<DataHost name = "localhost1" maxCon = "1000" minCon = "10" balance = "3"
WriteType = "0" dbType = "mysql" dbDriver = "native" switchType = "1" slaveThreshold = "100">
<Heartbeat> select user () <! -- Can have multi write hosts -->
<WriteHost = "hostM1" url = "ip1: 3306" user = "root" password = "123456">
<! -- Can have multi read hosts -->
<ReadHost host = "hostS1" url = "ip2: 3306" user = "root" password = "123456" weight = "1"/>
</WriteHost>
</DataHost>
</Mycat: schema>
 

The balance attribute

Server Load balancer type. Currently, there are three types of values: 1. balance = "0". Enable read/write splitting. All read operations are sent to the currently available writeHost. 2. balance = "1", all readHost and stand by writeHost participate in the load balancing of the select statement 3. balance = "2", all read operations are randomly distributed on writeHost and readhost. 4. balance = "3": all read requests are randomly distributed to the readhost corresponding to wiriterHost for execution. writerHost does not bear the read pressure.

Fill in the actual addresses for ip1 and ip2 respectively.
 
Test read/write splitting

Start the mycat service,

Test read data: select * from travelrecord

Visible data is read from the read database

Test Data Writing: insert into travelrecord (ID) values (88)

Check the read database. No value. The write database has a 88 record.

MySQL read/write splitting using MyCAT

This article permanently updates the link address: