Mysql read/write splitting implemented by mycat and mycatmysql read/write
Mycat is a middleware that can be used for mysql read/write splitting, data sharding, table sharding, and disaster tolerance backup. It can also be used for multi-tenant application development and cloud platform infrastructure, this gives the architecture great flexibility and scalability,
The intelligent optimization module of mycat provides a clear view of system data access. It can automatically or manually adjust backend storage based on statistical data analysis to map different tables to inaccessible storage engines.
Mycat is an open-source distributed database system, but because the real database requires a storage engine, and Mycat does not have a storage engine, it is not a fully-meaningful distributed database system.
Mycat application scenarios:
1. Simple read/write Splitting: supports read/write splitting and master-slave switchover;
2. sharding for tables and sub-databases. More than 10 million of tables are sharded, and a maximum of 100 billion of Single-Table sharding is supported;
3. Multi-tenant applications. Each application has one database, but the application only connects to Mycat, so that the program itself is not modified to achieve multi-tenancy;
4. The report system uses Mycat's table sharding capability to process statistics on large-scale reports;
5. Replace Hbase to analyze big data;
6. as a simple and effective solution for Real-Time query of massive data, for example, the results of 10 billion records that are frequently queried must be queried within 3 seconds. In addition to primary key-based queries, there may also be a range query or other property query,
In this case, Mycat may be the simplest and most effective choice.
The read/write splitting of mycat is established when mysql master-slave replication is completed.
Install mycat:
1. Configure the java environment first.
Wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
Tar zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
Mv mycat/usr/local/
Vim/etc/profile
Export PATH =/usr/local/mycat/bin: $ PATH
2. Create a management user for mycat, authorize the user account and authorization information for accessing MyCAT, and modify server. xml
On the master database: grant all privileges on *. * to 'mycat' @ '192. 168.3.111 'identified by '123 ';
Grant all privileges on *. * to 'mycat' @ '% 'identified by '123 ';
From the database (only the select permission is required): grant select on *. * to 'mycat' @ '192. 168.2.235 'identified by '123 ';
3. Modify the mycat configuration file server. xml
Configure the user password of the master database in the </system> segment:
<User name = "mycat">
<Property name = "password"> 123456 </property>
<Property name = "schemas"> test3 </property>
</User>
Users who configure to connect to the master database in server. xml must have the add, delete, modify, and query permissions. Test3 can be specified without the real database name.
However, it must be consistent with the Database Name of the schema. xml configuration file.
4. Modify the schema. xml file:
<Schema name = "test3" checkSQLschema = "false" sqlMaxLimit = "100" dataNode = "dn1">
</Schema> <! -- Here test3 must be the same as the database name in server. xml -->
<DataNode name = "dn1" dataHost = "localhost1" database = "test2"/> <! -- Here you only need to change the database to the real database name -->
<DataNode name = "dn2" dataHost = "localhost1" database = "test2"/>
<DataNode name = "dn3" dataHost = "localhost1" database = "test2"/>
<DataHost name = "localhost1" maxCon = "1000" minCon = "10" balance = "1"
WriteType = "0" dbType = "mysql" dbDriver = "native" switchType = "1" slaveThreshold = "100">
<! -- Modify balance = "1" writeType = "0" switchType = "1 ″
Balance = 0 do not enable read/write splitting. All read operations are sent to the currently available writehostle.
Balance = 1 All readhost and stand by writeHost participate in the load balancing of the select statement. To put it simply, the dual-master and dual-slave modes (M1, M2, and M2 are both active and standby). Normally, M1, S1, s2 is involved in complex balancing of select statements.
Balance = 2 all read operations are randomly distributed on readhost and writehost.
WriteType Load Balancing Type. Currently, there are three types of values:
WriteType = "0", all write operations are sent to the first writeHost configured.
WriteType = "1", all write operations are randomly sent to the configured writeHost.
WriteType = "2", no write operation is performed
SwitchType
SwitchType =-1 indicates no automatic switch
SwitchType = 1 default value, automatic switch
SwitchType = 2 determines whether to switch based on the MySQL master-slave synchronization status
-->
<Heartbeat> select user () <! -- Can have multi write hosts -->
<WriteHost = "master" url = "192.168.3.111: 3306" user = "mycat" password = "123456">
<! -- Can have multi read hosts -->
<ReadHost host = "slave" url = "192.168.2.235: 3306" user = "mycat" password = "123456"/>
</WriteHost>
</DataHost>
5. Start mycat
Mycat console (started on the console) or mycat start
1) when the console is started, the following error occurs: jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat. log: OK.
2) start to check whether ports 8066 and 9066 are listening.
Error:
Exception thrown by the agent: java.net. MalformedURLException: Local host name unknown: java.net. UnknownHo
StException: bogon: Name or service not known
Solution: add the Host Name of the Local Machine in/etc/hosts.
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------
Detailed description of the schema. xml file
<? Xml version = "1.0"?>
<! DOCTYPE mycat: schema SYSTEM "schema. dtd">
<Mycat: schema xmlns: mycat = "http://org.opencloudb/">
<Schema name = "TESTDB" checkSQLschema = "false" sqlMaxLimit = "100">
<! -- The table to be sharded is sharded on nodes dn1 and dn2. The sharding rule is auto-sharding-long -->
<Table name = "travelrecord" dataNode = "dn1, dn2" rule = "auto-sharding-long"/>
<Table name = "company" primaryKey = "ID" dataNode = "dn2, dn1" rule = "mod-long"/>
<Table name = "goods" primaryKey = "ID" type = "global" dataNode = "dn1, dn2"/>
<Table name = "hotnews" primaryKey = "ID" dataNode = "dn1, dn2" 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 name = "order_items" joinKey = "order_id" parentKey = "id"/>
</ChildTable>
<ChildTable name = "customer_addr" primaryKey = "ID" joinKey = "customer_id" parentKey = "id"/>
</Table>
</Schema>
<DataNode name = "dn1" dataHost = "201.liberalman.cn" database = "db1"/>
<DataNode name = "dn2" dataHost = "202.liberalman.cn" database = "db2"/>
<DataHost name = "201.liberalman.cn" maxCon = "1000" minCon = "10" balance = "0"
WriteType = "0" dbType = "mysql" dbDriver = "native" switchType = "1" slaveThreshold = "100">
<! -- Heartbeat package -->
<Heartbeat> select user () <! -- Backend mysql configuration -->
<WriteHost host = "hostM1" url = "localhost: 3306" user = "socho" password = "looks#"> </writeHost>
</DataHost>
<DataHost name = "202.liberalman.cn" maxCon = "1000" minCon = "10" balance = "0"
WriteType = "0" dbType = "mysql" dbDriver = "native" switchType = "1" slaveThreshold = "100">
<! -- Heartbeat package -->
<Heartbeat> select user () <WriteHost host = "hostM1" url = "localhost: 3306" user = "socho" password = "looks#"> </writeHost>
</DataHost>
</Mycat: schema>
The above configuration is the configuration for connecting to the backend mysql,Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger. Http://blog.csdn.net/Linux_newbie_rookie/article/details/79310857
