Introduction to Kingshard MySQL Middleware

Introduction to Kingshard MySQL Middleware

Functions:

MySQL table sharding and a series of MySQL operations such as adding, deleting, modifying, and querying tables. The master-slave architecture of MySQL can be expanded to facilitate distributed expansion of the MySQL architecture.
 
The experimental test architecture is to install the keepalived and kingshard Middleware on the two MASTER nodes. The two master nodes are mutually master-slave and both MASTER nodes have a slave. High Availability of MySQL and kingshard to prevent single point of failure.
 
Note:
All table sharding operations must be connected through kingshard.
Kingshard is only responsible for operations after table sharding. All data synchronization is completed by MySQL master-slave synchronization.
 
The keepalived configuration on node1 is as follows:
! Configuration File for keepalived
 
Global_defs {
Notification_email {
Ibuler@qq.com # Who will send the email
}
Notification_email_from keepalived @ localhost # email address in which the fault occurs
Smtp_server 127.0.0.1 # SMTP_Server IP
Smtp_connect_timeout 30 ## timeout
Router_id LVS_DEVEL # server ID
}
# Vrrp_script chk_http_port {
# Script "/opt/nginx. sh"
# Interval 2
# Weight 2
#}
Vrrp_instance VI_1 {
State MASTER
# Status, all of which are backups. They will select the Master. If you write the MASTER, it will be the Master,
# When the Master node fails, the Backup node becomes the Master node. When the original Master node recovers, the original Master node becomes the Master node.
Interface em1 # Send the vrrp api. Check whether your API is eth0.
Virtual_router_id 51 # virtual route ID. The same group should use one, that is, the same Master and Backup
Priority 100 # important priority
Nopreempt # Do not preemptible. When a fault occurs, it will not be preemptible after it is restarted.
Advert_int 1 # synchronization interval
Authentication {# authentication
Auth_type PASS # authentication method
Auth_pass www. laoguang. me # key
}
Track_script {
Chk_http_port
}
Virtual_ipaddress {
192.168.1.170 # VIP
}
}
 
The Kingshard configuration is as follows, ks. yaml:
 
# Server listen addr
Addr: 0.0.0.0: 9696
 
# Server user and password
User: kingshard
Password: kingshard
 
# If set log_path, the SQL log will write into log_path/SQL. log, the system log
# Will write into log_path/sys. log
Log_path:/tmp/kingshard
 
# Log level [debug | info | warn | error], default error
Log_level: debug
 
# If set log_ SQL (on | off) off, the SQL log will not output
Log_ SQL: on
 
# Only log the query that take more than slow_log_time MS
# Slow_log_time: 100
 
# The path of blacklist SQL file
# All these sqls in the file will been forbidden by kingshard
# Blacklist_ SQL _file:/Users/flike/blacklist
 
# Only allow this ip list ip to connect kingshard
# Allow_ips: 127.0.0.1
 
# The charset of kingshard, if you don't set this item
# The default charset of kingshard is utf8.
# Proxy_charset: gbk
 
# Node is an agenda for real remote mysql server.
Nodes:
-
Name: node1
 
# Default max conns for mysql server
Max_conns_limit: 32
 
# All mysql in a node must have the same user and password
User: root
Password: 6 lapp
 
# Master represents a real mysql master server
Master: 192.168.1.231: 3306
 
# Slave represents a real mysql salve server, and the number after '@' is
# Read load weight of this slave.
Server Load balancer: 192.168.1.188: 3306
Down_after_noalive: 32
-
Name: node2
 
# Default max conns for mysql server
Max_conns_limit: 32
 
# All mysql in a node must have the same user and password
User: root
Password: 6 lapp
 
# Master represents a real mysql master server
Master: 192.168.1.189: 3306
 
# Slave represents a real mysql salve server
Server Load balancer: 192.168.1.181: 3306
 
# Down mysql after N seconds noalive
#0 will no down
Down_after_noalive: 32
 
# Schema defines sharding rules, the db is the sharding table database.
Schema:
Db: 6l_wangfan
Nodes: [node1]
Default: node1
Shard:
-
Table: ll_wf_user_info
Key: user_id
Nodes: [node1]
Type: hash
Locations: [6]
 
Node2 configuration is similar.
Note:
Kingshard is an open source product, githup address: https://github.com/flike/kingshard
Software used:
Keepalive1.2.7
Mysql5.6.11
Kingshard1.4

This article permanently updates the link address: