MySQL Distributed Database Service middleware-Cobar Research 2, mysql middleware-cobar

MySQL Distributed Database Service middleware-Cobar Research 2, mysql middleware-cobar

In this article, we will learn how to use the Cobar configuration.

Scenario:

The database name provided by the system is UserCenter, and there is a table u_user

Some data in this table is mapped to the u_user of the physical database alicobar_test_master, and other data is mapped to the u_user of the physical database alicobar_test_shard.

I. Prepare the environment

OS: CentOS6.4 64-bit

DB: MySQL5.6.22

JDK: 1.7

Cobar: 1.2.7

MySQL-master 192.168.89.4, port 3306 Username: alicobar password: alicobarpwd123

MySQL-backup port 192.168.89.5, port 3306 Username: alicobar password: alicobarpwd123

Cobar-server 192.168.66.89

Schema: alicobar_test_master, alicobar_test_shard, table: u_user script is as follows:

Create database /*! 32312 if not exists */'alicobar _ test_master '/*! 40100 default character set utf8 */; USE 'alicobar _ test_master '; /* Table structure for table 'U _ user' */create table 'U _ user' ('code' bigint (19) not null, 'mobile' varchar (32) default null, 'email 'varchar (64) default null, 'nickname' varchar (64) default null, 'Password' varchar (32) not null, 'create _ time' datetime not null, 'modify _ time' datetime default null, 'status' int (10) not null comment' user status: 1 = enabled; 2 = disabled ;...... ', primary key ('code') ENGINE = InnoDB default charset = utf8 COMMENT =' -- User information'

 

Create database /*! 32312 if not exists */'alicobar _ test_shard '/*! 40100 default character set utf8 */; USE 'alicobar _ test_shard '; /* Table structure for table 'U _ user' */create table 'U _ user' ('code' bigint (19) not null, 'mobile' varchar (32) default null, 'email 'varchar (64) default null, 'nickname' varchar (64) default null, 'Password' varchar (32) not null, 'create _ time' datetime not null, 'modify _ time' datetime default null, 'status' int (10) not null comment' user status: 1 = enabled; 2 = disabled ;...... ', primary key ('code') ENGINE = InnoDB default charset = utf8 COMMENT =' -- User information ';

 

Ii. Deployment and Configuration

MySQL master/Master synchronization is not described here. For details, refer to the Manual on the official website.

1. Install JDK to configure the JAVA environment variable JAVA_HOME

Download JDK1.7 http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html

The installation process is not mentioned. The following is a brief description of the configuration after installation.

Add vi/etc/profile at the end of the text

Export JAVA_HOME =/usr/java/jdk1.7.0 _ 75
Export JRE_HOME =/usr/java/jdk1.7.0 _ 75/jre
Export CLASSPATH =.: $ JAVA_HOME/lib/dt. jar: $ JAVA_HOME/lib/tools. jar
Export PATH = $ JAVA_HOME/bin: $ PATH

Make the profile take effect immediately after adding the java_home environment variable

Source/etc/profile

2. Install cobar

: Https://github.com/alibaba/cobar/wiki

Download the Cobar compressed file and decompress it. Go to the conf directory and you can see the configuration files related to schema. xml, rule. xml, server. xml, and so on.

The schema. xml configuration is as follows (Note: schema. xml contains the IP address, port, user name, password, and other configurations of MySQL. You must replace them with your MySQL information according to the annotations .)

[Root @ localhost conf] # more schema. xml

<? Xml version = "1.0" encoding = "UTF-8"?> <! DOCTYPE cobar: schema SYSTEM "schema. dtd"> <cobar: schema xmlns: cobar = "http://cobar.alibaba.com/"> <! -- Schema definition --> <schema name = "UserCenter" dataNode = "node1"> <table name = "u_user" dataNode = "node1, node2 "rule =" rule1 "/> </schema> <! -- Data node definition. A data node is composed of data sources and other parameters. --> <DataNode name = "node1"> <property name = "dataSource"> <dataSourceRef> source-master [0] </dataSourceRef> <dataSourceRef> source-backup [0] </dataSourceRef> </property> <! -- Cobar and backend data source connection pool size settings --> <property name = "poolSize"> 256 </property> <! -- Cobar uses heartbeat to implement the HA of the backend data source. Once the heartbeat of the primary data source fails, it switches to the backup data source to work. --> <! -- Cobar heartbeat is to execute an SQL statement to the backend data source to determine the running status of the data source based on the returned results of the statement --> <property name = "heartbeatSQL"> select user () </property> </dataNode> <dataNode name = "node2"> <property name = "dataSource"> <dataSourceRef> source-master [1] </dataSourceRef> <dataSourceRef> source-backup [1] </dataSourceRef> </property> <! -- Cobar and backend data source connection pool size settings --> <property name = "poolSize"> 256 </property> <! -- Cobar uses heartbeat to implement the HA of the backend data source. Once the heartbeat of the primary data source fails, it switches to the backup data source to work. --> <! -- Cobar heartbeat is to execute an SQL statement to the backend data source to determine the running status of the data source based on the returned results of the statement --> <property name = "heartbeatSQL"> select user () </property> </dataNode> <! -- Data Source definition, which represents a specific backend data connection. --> <DataSource name = "source-master" type = "mysql"> <property name = "location"> <location> 192.168.89.4: 3306/alicobar_test_master </location> <! -- Note: replace it with your MySQL IP address and Port --> <location> 192.168.89.4: 3306/alicobar_test_shard </location> </property> <property name = "user"> alicobar </property> <! -- Note: replace it with your MySQL username --> <property name = "password"> alicobarpwd123 </property> <! -- Note: replace it with your MySQL password --> <property name = "sqlMode"> STRICT_TRANS_TABLES </property> </dataSource> <dataSource name = "source-backup" type = "mysql"> <property name = "location"> <location> 192.168.89.5: 3306/alicobar_test_master </location> <location> 192.168.89.5: 3306/alicobar_test_shard </location> </property> <property name = "user"> alicobar </property> <property name = "password"> alicobarpwd123 </property> <property name = "sqlMode"> STRICT_TRANS_TABLES </property> </dataSource> </cobar: schema>

 

The configuration of rule. xml is as follows (This article uses the code field of the numeric type as the split field to split the data into two databases .)

More rule. xml

<? Xml version = "1.0" encoding = "UTF-8"?> <! DOCTYPE cobar: rule SYSTEM "rule. dtd"> <cobar: rule xmlns: cobar = "http://cobar.alibaba.com/"> <! -- Define routing rules, what tables and fields are defined, what routing algorithm is used --> <tableRule name = "rule1"> <rule> <columns> code </columns> <algorithm> <! [CDATA [func1 ($ {code})]> </algorithm> </rule> </tableRule> <! -- Define a routing function --> <function name = "func1" class = "com. alibaba. cobar. route. function. partitionByLong "> <property name =" partitionCount "> 2 </property> <property name =" partitionLength "> 512 </property> </function> </cobar: rule>

Server. xml configuration is as follows:

More server. xml

<? Xml version = "1.0" encoding = "UTF-8"?> <! DOCTYPE cobar: server SYSTEM "server. dtd"> <cobar: server xmlns: cobar = "http://cobar.alibaba.com/"> <! -- System Parameter definition, service port, Management port, number of processors, thread pool, etc. --> <! -- <System> <property name = "serverPort"> 8066 </property> <property name = "managerPort"> 9066 </property> <property name = "initExecutor"> 16 </property> <property name = "timerExecutor"> 4 </property> <property name = "managerExecutor"> 4 </property> <property name = "processors"> 4 </ property> <property name = "processorHandler"> 8 </property> <property name = "processorExecutor"> 8 </property> <property name = "clusterHeartbe AtUser "> _ HEARTBEAT_USER _ </property> <property name =" clusterHeartbeatPass "> _ HEARTBEAT_PASS _ </property> </system> --> <! -- User Access definition, user name, password, schema, and other information. --> <User name = "test"> <property name = "password"> test </property> <property name = "schemas"> UserCenter </property> </user> <! -- <User name = "root"> <property name = "password"> </property> </user> --> <! -- Cluster list definition: Specifies the host and weight of the cluster node for heartbeat among clusters and client load balancing. --> <! -- <Cluster> <node name = "cobar1"> <property name = "host"> 127.0.0.1 </property> <property name = "weight"> 1 </property> </ node> </cluster> --> <! -- The isolation area defines that only one user can log on to a host. --> <! -- <Quarantine> 3. Start and use Cobar
1. Start Cobar and enter the bin directory to view the Cobar startup, stop, and restart scripts.

If no log directory exists, create a new one and start it again.


View the stdout. log in the logs directory. the startup success log is as follows:
[Root @ localhost logs] # more stdout. log
17:31:10, 939 INFO ===================================================== ==========
17:31:10, 940 INFO Cobar is ready to startup...
17:31:10, 940 INFO Startup processors...
17:31:10, 974 INFO Startup connector...
17:31:10, 975 INFO Initialize dataNodes...
17:31:11, 140 INFO node1: 0 init success
17:31:11, 142 INFO node2: 0 init success
17:31:11, 150 INFO CobarManager is started and listening on 9066
17:31:11, 152 INFO CobarServer is started and listening on 8066
17:31:11, 152 INFO ===================================================== ==========
Accessing Cobar is the same as accessing MySQL.
Common Access methods are as follows (Note: Cobar is deployed on the host 192.168.66.89 in this article; otherwise, replace it with the IP address of your Cobar. Other information remains unchanged)
There is no difference between SQL statement execution and traditional single database
[Root @ localhost cobar-server-1.2.7] # mysql-h192.168.66.89-utest-ptest-P8066-DUserCenter
Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.1.48-cobar-1.2.7 Cobar Server (ALIBABA)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+------------+| DATABASE   |+------------+| UserCenter |+------------+1 row in set (0.00 sec)mysql> show tables;+----------------------+| Tables_in_UserCenter |+----------------------+| u_user               |+----------------------+1 row in set (0.05 sec)mysql> 


This configuration is complete!