MySQL distributed database service middleware--cobar Research II

This article to learn the specific use of Cobar configuration

Scene:

The database name provided by the system is usercenter, and there is a table U_user

Part of the data for this table is mapped to the u_user of the physical database Alicobar_test_master, and the other part of the data is mapped to the u_user of the physical database Alicobar_test_shard.

I. Environmental preparedness

os:centos6.4 64-bit

db:mysql5.6.22

jdk:1.7

cobar:1.2.7

Mysql-master 192.168.89.4 Port 3306 user name: Alicobar Password: alicobarpwd123

Mysql-backup 192.168.89.5 Port 3306 user name: Alicobar Password: alicobarpwd123

Cobar-server 192.168.66.89

The Schema:alicobar_test_master and Alicobar_test_shard,table:u_user scripts are 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 (n) Not null,< c2/> ' mobile ' varchar (+) default NULL,  ' email ' varchar (+) default NULL,  ' nickname ' varchar (+) Default null,< c5/> ' password ' varchar (+) NOT NULL,  ' Create_time ' datetime is NOT NULL,  ' modify_time ' datetime DEFAULT NULL,  ' Status ' int (+) not NULL COMMENT ' user state: 1 = enabled; 2 = disabled ... ',  PRIMARY KEY (' code ') Engine=innodb DEFAULT charset=ut F8 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 (n) Not null,
    ' mobile ' varchar (+) default NULL,  ' email ' varchar (+) default NULL,  ' nickname ' varchar (+) default NULL,  ' password ' varchar (+) NOT NULL,  ' Create_time ' datetime is NOT NULL,  ' modify_time ' datetime DEFAULT NULL,  ' status ' int (ten) not NULL COMMENT ' user state: 1 = enabled; 2 = disabled;  PRIMARY KEY (' Code ')) Engine=innodb DEFAULT Charset=utf8 comment= '-user information ';

Two. Deployment and Configuration

MySQL master synchronization is no longer detailed here, you can refer to the official website manual

1. Installing the JDK Configuration Java environment variable Java_home

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

The installation process will not say, the following simple configuration after installation

Vi/etc/profile add 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

After adding the JAVA_HOME environment variable, the profile takes effect immediately

Source/etc/profile

2. Installing Cobar

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

Download Cobar compressed files and unzip, enter Conf directory can see Schema.xml, Rule.xml, Server.xml and other related configuration files

The Schema.xml configuration is as follows (note: schema.xml contains the MySQL IP, port, username, password and other configuration, you need to follow the comments to replace your MySQL information.) )

[Email protected] 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 definitions, data nodes are organized by data sources and other parameters. --<datanode name= "Node1" > <property name= "DataSource" > <dataSourceRef>source-master[0]< /datasourceref> <dataSourceRef>source-backup[0]</dataSourceRef> </property> <!--Cobar and after End data source connection pool Size setting-<property name= "Poolsize" >256</property> <!--Cobar through the heartbeat to achieve back-end data source ha, once the primary data source heartbeat fails, then switch Working on the standby data source-<!--the Cobar heartbeat is a SQL statement executed by the back-end data source, judging the data source's operation based on the return result of the statement-<property name= "Heartbeatsql" >      Select User () </property> </dataNode> <datanode name= "Node2" > <property name= "DataSource" > <datasourceref>source-master[1]&lT;/datasourceref> <dataSourceRef>source-backup[1]</dataSourceRef> </property> <!--Cobar With the back-end data source connection pool size set-<property name= "Poolsize" >256</property> <!--Cobar through the heartbeat to achieve the back-end data source ha, once the primary data source heartbeat fails, Switch to the standby data source-<!--Cobar Heartbeat is a SQL statement that executes through the back-end data source, judging the data source's operation based on the return result of the statement-<property name= "Heartbeatsql" >select user () </property> </dataNode> <!--data source definition, the data source is a representation of a specific back-end data connection. --<datasource name= "Source-master" type= "MySQL" > <property name= "Location" > <location>192. 168.89.4:3306/alicobar_test_master</location> <!--Note: Replace with your MySQL IP and port--> <location> 192.168.89.4:3306/alicobar_test_shard</location> </property> <property name= "user" >alicobar< /property> <!--Note: Replace with your MySQL username--<property name= "password" >alicobarpwd123</property> <!- -Note: Replace with your MySQL password--<property name= "Sqlmode" &GT;STRICT_TRANS_TABLES&LT;/PROPERTY&GT </dataSource> <datasource name= "source-backup" type= "MySQL" > <property name= "Location" > <loc Ation>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 Rule.xml is configured as follows (this article splits the data into two libraries by using the number Type Code field as the Split field. )

More Rule.xml

<?xml version= "1.0" encoding= "UTF-8"? ><! DOCTYPE cobar:rule SYSTEM "rule.dtd" ><cobar:rule xmlns:cobar= "http://cobar.alibaba.com/" >  <!-- Routing rule definitions, what tables are defined, what fields, what routing algorithms are used--  <tablerule name= "Rule1" >    <rule>      <columns>code </columns>      <algorithm><![ cdata[func1 (${code})]]></algorithm>    </rule>  </tableRule>  <!--route function definition--  <function name= "func1" class= "Com.alibaba.cobar.route.function.PartitionByLong" >    <property Name = "Partitioncount" >2</property>    <property name= "Partitionlength" >512</property>  </function></cobar:rule>

The Server.xml is configured 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= "Managerpo RT ">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=" Clusterheartbeatuser ">_heartbeat_user_</ property> <property name= "Clusterheartbeatpass" >_HEARTBEAT_PASS_</property> </system>--& lt;! --User access definition, user name, password, schema and other information. --<user name= "Test" > <property name= "Password" &GT;TEST&Lt;/property> <property name= "schemas" >UserCenter</property> </user> <!--<user name= "root "> <property name=" password "></property> </user>-<!--cluster list definition, specifying the host and weight of the cluster nodes for heartbeat and customer between clusters -End load Balancing. -<!--<cluster> <node name= "cobar1" > <property name= "Host" >127.0.0.1</property&gt      ; <property name= "Weight" >1</property> </node> </cluster>-<!--quarantine definition, which can be limited to a host Allows a user to log on.    -<!--<quarantine> Three. Start and use Cobar
1. Start Cobar, enter the bin directory to see Cobar start, stop, and restart scripts
Report No log directory, we create a new one, start again

To view the logs directory under Stdout.log, start the success log as follows
[Email protected] 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 ===============================================
Access to Cobar is exactly the same way you access MySQL
Common access methods are as follows (note: This article will be Cobar deployed on 192.168.66.89 this machine, otherwise please replace with your Cobar IP, other information is the same)
SQL executes statements with no distinction from using a traditional single database
[Email protected] cobar-server-1.2.7]# Mysql-h192.168.66.89-utest-ptest-p8066-dusercenter
Warning:using a password on the command line interface can is 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) #, Orac Le and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names 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               


This configuration is complete!
MySQL distributed database service middleware--cobar Research II