Master big data processing scheme and HA of database
Master why database middleware is needed and what is database middleware
Master the characteristics of database middleware required by different scenarios
Master the key points of database middleware design
review
How to solve the impact on data access of DAO layer after dealing with big data and high concurrency
The ability requirement of database middleware is very high
Ø 1 be able to parse SQL
Can support read-write separation
Can support load balancing read from the library
Ø 4 support sub base operation
Table splitting operation is supported
Ø 6 support cross library associated query
Support for transaction processing
Ø 8 primary key ID generation
Ø 9 data source management
Mycat Version Description
The Mycat Mini monitor project is open source, another Mycat monitor!
Release of Mycat-mini-monitor-1.0.0
Release of Mycat-server-1.6.6-release
Release of Mycat-server-1.6.6-test
Release of Mycat-server-1.6-release
Release of Mycat-server-1.5-release
Release of Mycat-server-1.4-release
Release of Mycat-server-1.3-release
MYCAT web (eye) release
How Mycat works
The principle of Mycat: ‚ intercept ‛ it intercepts the SQL statements sent by users. First, it analyzes the SQL statements and makes some specific analysis, such as fragment analysis, route analysis, read-write separation analysis, cache analysis, etc. then it sends the SQL to the back-end real database, and properly processes the returned results, and finally returns them to users;
Mycat architecture
Core concept
Database middleware: for applications, mycat is database service, and mycat is the agent of back-end database cluster
Logical concept:
Ø logical library: the database defined and managed in mycat database service
Ø logical table: the table contained in the logical database that needs to be stored in different databases and tables
Ø dataNode: data node (partition node), the storage node of logical table partition.
Ø dataHost: data host (node host), the host where the data node is located.
physical concept
Ø writeHost: write host, real database service host description
Ø readHost: read host, real database service host description
schema.xml
<mycat:schema> <schema name="testdb"> <table name="orders" primaryKey="ID"type="global" dataNode="dn1,dn2" /> </schema> <dataNode name="dn1" dataHost="dhost1" database="db1" /> <dataHost name="dhost1" ...> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="localhost:3306"...> <readHost host="hostS2" url="192.168.1.2:3306".../> </writeHost> </dataHost> </mycat:schema>
Schema configuration schema
Logical library schema
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> </schema> </mycat:schema>
MyCat can have multiple logical libraries, each with its own configuration. If you do not configure the schema label, all the table configurations will belong to the same default logical library.
Schema configuration schema element attribute description
schema configuration table
Logical table
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> <table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" /> <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> </table> </schema>
The Table tag defines the logical tables in MyCat. All tables that need to be split need to be defined through this tag.
schema configuration table element attribute description
schema configuration - childTable label
<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> </table>
The childTable label is used to define the child tables of E-R sharding, and is associated with the parent table through the attributes on the label.
schema configuration - dataHost
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="localhost:3306" user="root" password="123456"> <!-- can have multi read hosts --> <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> </writeHost> </dataHost>
The data host defines the data host in Mycat, and the specific database service, read-write separation configuration and heartbeat statement are defined in the data host definition. This is where the physical database service is associated.
schema configuration - dataHost element attribute description
schema configuration heartbeat
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="localhost:3306" user="root" password="123456"> <!-- can have multi read hosts --> <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> </writeHost> </dataHost>
1. This label indicates the statement used to check the heartbeat with the backend database
For example, MYSQL can use select user() and Oracle can use select 1 from dual.
2. This tag also has a connectionInitSql attribute, which is mainly used for the initialization SQL statements to be executed when using Oracle database.
For example: alter session set NLS? Date? Format =? Yyyy MM DD hh24: Mi: Ss'
3. The master-slave switch statement must be: show slave status
schema configuration - writeHost readHost
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="localhost:3306" user="root" password="123456"> <!-- can have multi read hosts --> <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> </writeHost> </dataHost>
Both tags specify the configuration of the backend database to mycat for instantiating the backend connection pool. writeHost specifies the write instance and readHost specifies the read instance.
Multiple writehosts and readhosts can be defined in a dataHost. However, if the backend database specified by the writeHost goes down, all readhosts bound by the writeHost will not be available. On the other hand, the system will automatically detect the failure of the writeHost and switch to the standby writeHost.
schema configuration - writeHost readHost property description
Encrypt password, execute mycat jar program (after 1.4.1)
java -cp Mycat-server-1.4.1-dev.jar io.mycat.util.DecryptUtil 1:host:user:password Mycat-server-1.4.1-dev.jar by mycat download Download directory's jar 1:host:user:password The middle 1 is db End encryption flag, host by dataHost Of host Name