Database read-write separation is an indispensable and important function for large-scale systems or high-volume Internet applications. For MySQL, the standard read-write separation is the master-slave mode. A write node Master is followed by multiple read nodes. The number of read nodes depends on the pressure of the system, usually the configuration of 1-3 read nodes.
Mycat read-write separation and automatic switching mechanism need the cooperation of master-slave replication mechanism of mysql.
- For installation of MyCat, please refer to: Installation Configuration of Mycat under Linux
- For configuration of Mysql master-slave replication, please refer to: Configuration of MySql master-slave replication (GTID mode)
Configuration of MyCat
Starting from Mycat 1.4, it supports the read-write separation mechanism of MySQL master-slave replication state binding, which makes reading more secure and reliable. The configuration is as follows:
Configuration file means:
- Logic library: small. Logic table: tb_item;
- A database was created on the Master server: db1;
- Slave server synchronizes the db1 database of Master server.
- Write requests are sent to the Master server.
- Read requests are sent to the Slave server.
[root@mycat-server conf]# vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="small" checkSQLschema="false" sqlMaxLimit="100">
<table name="tb_item" dataNode="dn1" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM" url="mysql-server-01:3306" user="root" password="888888">
<readHost host="hostS" url="mysql-server-02:3306" user="root" password="888888" />
</writeHost>
</dataHost>
</mycat:schema>
Two, test
1. We use Navicat to connect MyCat server:
2. Create tables
After executing the following table-building statement, you can see that the tb_item table exists in the database db1 on the Master server. Look again at the tb_item table in the db1 database on the Slave server.
-- ----------------------------
-- Table structure for tb_item
-- ----------------------------
DROP TABLE IF EXISTS `tb_item`;
CREATE TABLE `tb_item` (
`id` bigint(20) NOT NULL COMMENT 'commodity id，It's also a commodity number.',
`title` varchar(100) NOT NULL COMMENT 'Product title',
`sell_point` varchar(500) DEFAULT NULL COMMENT 'Commodity selling point',
`price` bigint(20) NOT NULL COMMENT 'Commodity prices in units of:',
`num` int(10) NOT NULL COMMENT 'Inventory quantity',
`barcode` varchar(30) DEFAULT NULL COMMENT 'Commodity Bar Code',
`image` varchar(500) DEFAULT NULL COMMENT 'Commodity pictures',
`cid` bigint(10) NOT NULL COMMENT 'Categories, Leaves',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'Commodity status, 1-Normal, 2-Lower shelf, 3-delete',
`created` datetime NOT NULL COMMENT 'Creation time',
`updated` datetime NOT NULL COMMENT 'Update time',
PRIMARY KEY (`id`),
KEY `cid` (`cid`),
KEY `status` (`status`),
KEY `updated` (`updated`)
) COMMENT='Commodity list';3. Insert data from
INSERT INTO TB_ITEM (
ID,
TITLE,
SELL_POINT,
PRICE,
NUM,
BARCODE,
IMAGE,
CID,
STATUS,
CREATED,
UPDATED
)
VALUES
(
'15000000',
'SHARP ( SHARP)LCD-52DS51A 52 Inch Japanese original LCD panel built-in WIFI Intelligent Full HD LCD TV',
'Good screen, choose Sharp! Japanese original panel, smart TV, high picture quality and high sound quality!<a target=\"blank\" href=\"http://Item.jd.com/1278664.html"> and an updated version of Android Intelligent New Machine 52DS52 for your choice! </a>',
'549900',
'99999',
NULL,
'http://image.taotao.com/jd/63af01c37a18454ab2fef4670046272e.jpg',
'76',
'1',
'2015-03-08 21:27:45',
'2015-03-08 21:27:45'
);4. Test Read-Write Separation
Appendix 1. The Second Configuration of Read-Write Separation
1. The Second Configuration of Read-Write Separation
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="small" checkSQLschema="false" sqlMaxLimit="100">
<table name="tb_item" dataNode="dn1" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="mysql-server-01:3306" user="root" password="888888"></writeHost>
<writeHost host="hostS1" url="mysql-server-02:3306" user="root" password="888888"></writeHost>
</dataHost>
</mycat:schema>These two configurations, the first when the write hang read is not available, the second can continue to use. In addition, all operations within the transaction will go to write nodes, so read operations do not add transactions.
2. Forced walk-write or read nodes
If the read latency is large, we can use MyCat annotation to force the walk-write node.
Continue with the fourth section of the test section above: Test Read-Write Separation. Next, test MyCat's annotations.
It may not be clear in the figure that the statement forcing a walk-write node is as follows:
/*#mycat:db_type=master*/ SELECT * FROM tb_item ;In addition, the test found that the forced walk-through node failed, and did not know why, nor did it go into depth (there was really not much time):
/*#mycat:db_type=slave*/ SELECT * FROM tb_item ;Attachment 2. Switching according to master-slave delay
1. Configuration of handover based on master-slave delay
1.4 Support MySQL master-slave replication state binding read-write separation mechanism, so that read more secure and reliable, configuration as follows:
- MyCAT heartbeat check statement is configured to show slave status;
- Two new attributes are defined on dataHost: switchType="2" and slaveThreshold="100", which means opening the read-write separation and switching mechanism of MySQL master-slave replication state binding.
Mycat heartbeat mechanism determines the current master-slave synchronization status and master-slave replication delay of Seconds_Behind_Master, Slave_IO_Running and Slave_SQL_Running by detecting three fields in show slave status. When Seconds_Behind_Master > slave Threshold, the read-write separation filter filters out the Slave machine to prevent reading old data long ago. When the primary node is down, the switching logic checks whether the Seconds_Behind_Master on Slave is 0, and when it is 0, it indicates that the master-slave synchronization can be safely switched, otherwise it will not be switched.
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="small" checkSQLschema="false" sqlMaxLimit="100">
<table name="tb_item" dataNode="dn1" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0"
dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status </heartbeat>
<writeHost host="hostM1" url="mysql-server-01:3306" user="root" password="888888"></writeHost>
<writeHost host="hostS1" url="mysql-server-02:3306" user="root" password="888888"></writeHost>
</dataHost>
</mycat:schema>2, test
By stopping the primary node (write node) and inserting another data, you can see that it can be inserted. At this time, the write node has been closed, and the read node has two data:
Also, note: Reboot the primary node and MyCat will not rejoin it. (Personally tested)
There is such a paragraph in the official document:
writeType="0", all write operations are sent to the first writeHost configured, the first hangs to the second surviving WritteHost, which has been switched after restart, is recorded in the configuration file: dnindex.properties.