Mycat and Atlas Sub-Library sub-table installation, configuration Guide

background

Thinking on selection of database middleware
Official network installation, configuration information scattered, users need to collect information, based on understanding the correct configuration, in order to ensure the success of the operation. This work is time-consuming and hopefully readers will see the blog run quickly.

Blogs also have many people writing Mycat and Atlas, most of which are configured for Mycat and Atlas read-write separation scenarios. For Atlas, Qihoo 360 opens up 2 versions on GitHub, one version is purely proxy, supports the Sub-table function, and the other is the sharding version. This configuration sub-Library sub-table scene. Environment Preparation Deployment Architecture

Deployment Environment

MySQL: Request 4 node configuration same (virtual machine or physical machine can), each node creates a database, the name is Dbproxy, then each library creates an identical table Sbtest1
Mycat: Download mycat-server-1.6-release-20161028204710-linux.tar.gz
Atlas: Download atlas-sharding_1.0.1-el6.x86_64.rpm
Create a table: Sbtest1
The SBTEST1 table structure is:

CREATE TABLE ' sbtest1 ' (
  ' id ' int () unsigned not null,
  ' k ' int (ten) unsigned NOT null DEFAULT ' 0 ',
  ' C ' char (1 Not null default ' ',
  ' pad ' char ($) NOT null default ' ',
  PRIMARY key (' id '),
  key ' K_1 ' (' K ')
) ENGINE =innodb DEFAULT Charset=utf8

The SBTEST1 table structure is created using the Sysbench tool, with the self-increment primary key set removed, and subsequent direct use of the extended version Sysbench test middleware installation configuration mycat installation configuration

Mycat Service has 2 kinds of configuration, one is configured on the zookeeper, after startup will also write to *.xml several files, the other is directly to the *.xml modification configuration, this second method is simple and convenient. installation

Tar xzvf mycat-server-1.6-release-20161028204710-linux.tar.gz configuration server.xml account and resource configuration

<! DOCTYPE mycat:server SYSTEM "Server.dtd" > <mycat:server xmlns:mycat= "http://io.mycat/" > <system> &L T;property name= "Usesqlstat" >0</property> <!--1 To turn on real-time statistics, 0 off---<property name= "Useglobletableche CK ">0</property> <!--1 for turn on full overtime consistency detection, 0 for off--<property name=" Sequncehandlertype ">2</property > <property name= "processorbufferpooltype" >0</property> <property name= "ServerPort" >8066</ property> <property name= "Managerport" >9066</property> <property name= "IdleTimeout" &GT;300000&L t;/property> <property name= "Bindip" >0.0.0.0</property> <property name= "Processorexecutor" > 32</property> <property name= "handledistributedtransactions" >0</property> <property name=  "Useoffheapformerge" >1</property> <property name= "memorypagesize" >1m</property> <property Name= "SpillsfilebufferSize ">1k</property> <property name=" Usestreamoutput ">0</property> <property name=" Systemre Servememorysize ">384m</property> <property name=" Usezkswitch ">true</property> </system&gt

    ; <user name= "root" > <property name= "password" >123456</property> <property name= "schema S ">testdb</property> </user> <user name=" user "> <property name=" Password ">1234 56</property> <property name= "schemas" >testdb</property> <property name= "ReadOnly" ;true</property> </user> </mycat:server>

Mycat will manage the database access resources, there are 2 types of accounts, divided into administrators, ordinary users, administrators responsible for various resource management middleware (back-end Database access), view, allocation, etc., ordinary users can only database DDL and DML operations. Each user is configured to access a specific database resource, which is determined by the schemas parameter and is not reachable without configuration. schema.xml Logical Library Configuration

<?xml version= "1.0"?> <! DOCTYPE mycat:schema SYSTEM "Schema.dtd" > <mycat:schema xmlns:mycat= "http://io.mycat/" > <schema name= "tes TDB "Checksqlschema=" false "sqlmaxlimit=" 10000 "> <table name=" sbtest1 "primarykey=" id "datanode=" dn01,dn02, Dn03,dn04 "rule=" Mod-long "/> </schema> <datanode name=" Dn01 "datahost=" DBP1 "database=" Dbproxy "/&gt
    ; <datanode name= "dn02" datahost= "DBP2" database= "Dbproxy"/> <datanode name= "dn03" datahost= "DBP3" database= "D Bproxy "/> <datanode name=" dn04 "datahost=" DBP4 "database=" Dbproxy "/> <datahost name=" DBP1 "MaxC on= "mincon=" "balance=" 3 "writetype=" 0 "dbtype=" MySQL "dbdriver=" native "switchtype=" 1 "slavethreshold=" 100 ">  
The above configuration of 4 logical libraries, only the main library does not match from the library, if necessary readers can add, a table Sbtest1 data to 4 logical libraries. where ' rule= ' Mod-long "' indicates the selection of the modulo algorithm, corresponding to the following configuration rule.xml Shard rule Configuration

......
    <function name= "Mod-long" class= "Io.mycat.route.function.PartitionByMod" >
        <!--How many data nodes-- >
        <property name= "Count" >4</property>
    </function>
...
 
Represents ID% 4 = routed to the corresponding logical library (DBP1|DBP2|DBP3|DBP4)

Successfully executes an SQL statement:
Atlas Installation Configuration

The atlas configuration is relatively simple and configuration items and files are less installed than Mycat

1. Execute the following command
sudo rpm–i atlas-xx.el6.x86_64.rpm
Default installation directory:/usr/local/mysql-proxy
2. Configure TEST.CNF
Enter the Conf directory

[Mysql-proxy] admin-username = user Admin-password = 123456 #配置代理或读写分离主库配置 admin-lua-script =/usr/local/mysql-proxy/lib /mysql-proxy/lua/admin.lua #Atlas后端连接的MySQL主库的IP和端口, multiple items can be set, separated by commas #proxy-backend-addresses = ip01:3306,ip02:3306 # The Atlas backend is connected to MySQL from the library's IP and port, the number after the @ represents the weight, used for load balancing, if omitted the default is 1, you can set multiple items, separated by commas #proxy-read-only-backend-addresses = readonlyIP01 
: 3306,readonlyip02:3306 #示例, configuring the proxy main Library configuration proxy-backend-addresses = ipxxx:3306 Daemon = True keepalive = False #libevent线程数配置 Event-threads = Log-level = Error #日志路径 Log-path =/usr/local/mysql-proxy/log Sql-log = OFF #代理端口配置 for operating the database proxy-addr ESS = 0.0.0.0:1234 #管理端口配置 for managing resources, monitoring, System information operations, viewing, etc. admin-address = 0.0.0.0:2345 CharSet = UTF8 wait-timeout = 3600 #Atlas密码需要 Encrypt, install atlas into the bin directory, execute:./encrypt mysql database password; Generate encrypted string copy up PWDs = zhitao:if2il8bd7ygwz+vzsw3zlq== [shardrule-0] table = Dbproxy.sbtest1 # sharding Type: Range or hash, this time type = hash # shard Field name Shard-key = ID # 0:0-999,1:1000-1999 #分片的group, if the range type Sharding, the groups format is: Group_id:id range. If it is a hash type of SharDing, the groups format is: group_id. For example groups = 0, 1 groups = 0,1,2,3 [group-0] # master proxy-backend-addresses=ip01:3306 [group-1] proxy-backend-addresses= ip02:3306 [group-2] proxy-backend-addresses=ip03:3306 [group-3] proxy-backend-addresses=ip04:3306
 
Log in to Atlas:

Execute SQL Atlas:

Note: When configuring a library sub-table, only the specified sub-table will perform the shardrule process, and the other tables go through the normal request process, so configure the proxy-backend-addresses, from the library configuration depending on the deployment situation optional