Home

Awesome

Mixer

Mixer is a MySQL proxy powered by Go which aims to supply a simple solution for MySQL sharding.

Features

TODO

Install

cd $WORKSPACE
git clone git@github.com:siddontang/mixer.git src/github.com/siddontang/mixer

cd src/github.com/siddontang/mixer

./bootstrap.sh

. ./dev.env

make
make test

Keywords

proxy

A proxy is the bridge connecting clients and the real MySQL servers.

It acts as a MySQL server too, clients can communicate with it using the MySQL procotol.

node

Mixer uses nodes to represent the real remote MySQL servers. A node can have two MySQL servers:

Notice:

schema

Schema likes MySQL database, if a client executes use db command, db must exist in the schema.

A schema contains one or more nodes. If a client use the specified schema, any command will be only routed to the node which belongs to the schema to be executed.

rule

You must set some rules for a schema to let the mixer decide how to route SQL statements to different nodes to be executed.

Mixer uses table + key to route. Duplicate rule for a table are not allowed.

When SQL needs to be routed, mixer does the following steps:

Rules have three types: default, hash and range.

A schema must have a default rule with only one node assigned.

For hash and range routing you can see the example below.

admin commands

Mixer suplies admin statement to administrate. The admin format is admin func(arg, ...) like select func(arg,...). Later we may add admin password for safe use.

Support admin functions now:

- admin upnode(node, serverype, addr);
- admin downnode(node, servertype);
- show proxy config;

Base Example

#start mixer
mixer-proxy -config=/etc/mixer.conf

#another shell
mysql -uroot -h127.0.0.1 -P4000 -p -Dmixer

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 158
Server version: 5.6.19 Homebrew

mysql> use mixer;
Database changed

mysql> delete from mixer_test_conn;
Query OK, 3 rows affected (0.04 sec)

mysql> insert into mixer_test_conn (id, str) values (1, "a");
Query OK, 1 row affected (0.00 sec)

mysql> insert into mixer_test_conn (id, str) values (2, "b");
Query OK, 1 row affected (0.00 sec)

mysql> select id, str from mixer_test_conn;
+----+------+
| id | str  |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+

Hash Sharding Example

schemas :
-
  db : mixer
  nodes: [node1, node2, node3]
  rules:
    default: node1
    shard:
      -   
        table: mixer_test_shard_hash
        key: id
        nodes: [node2, node3]
        type: hash

hash algorithm: value % len(nodes)

table: mixer_test_shard_hash

Node: node2, node3
node2 mysql: 127.0.0.1:3307
node3 mysql: 127.0.0.1:3308

mixer-proxy: 127.0.0.1:4000

proxy> mysql -uroot -h127.0.0.1 -P4000 -p -Dmixer
node2> mysql -uroot -h127.0.0.1 -P3307 -p -Dmixer
node3> mysql -uroot -h127.0.0.1 -P3307 -p -Dmixer

proxy> insert into mixer_test_shard_hash (id, str) values (0, "a");
node2> select str from mixer_test_shard_hash where id = 0;
+------+
| str  |
+------+
| a    |
+------+

proxy> insert into mixer_test_shard_hash (id, str) values (1, "b");
node3> select str from mixer_test_shard_hash where id = 1;
+------+
| str  |
+------+
| b    |
+------+

proxy> select str from mixer_test_shard_hash where id in (0, 1);
+------+
| str  |
+------+
| a    |
| b    |
+------+

proxy> select str from mixer_test_shard_hash where id = 0 or id = 1;
+------+
| str  |
+------+
| a    |
| b    |
+------+

proxy> select str from mixer_test_shard_hash where id = 0 and id = 1;
Empty set

Range Sharding Example

schemas :
-
  db : mixer
  nodes: [node1, node2, node3]
  rules:
    default: node1
    shard:
      -   
        table: mixer_test_shard_range
        key: id
        nodes: [node2, node3]
        range: -10000-
        type: range

range algorithm: node key start <= value < node key stop

table: mixer_test_shard_range

Node: node2, node3
node2 range: (-inf, 10000)
node3 range: [10000, +inf)
node2 mysql: 127.0.0.1:3307
node3 mysql: 127.0.0.1:3308

mixer-proxy: 127.0.0.1:4000

proxy> mysql -uroot -h127.0.0.1 -P4000 -p -Dmixer
node2> mysql -uroot -h127.0.0.1 -P3307 -p -Dmixer
node3> mysql -uroot -h127.0.0.1 -P3307 -p -Dmixer

proxy> insert into mixer_test_shard_range (id, str) values (0, "a");
node2> select str from mixer_test_shard_range where id = 0;
+------+
| str  |
+------+
| a    |
+------+

proxy> insert into mixer_test_shard_range (id, str) values (10000, "b");
node3> select str from mixer_test_shard_range where id = 10000;
+------+
| str  |
+------+
| b    |
+------+

proxy> select str from mixer_test_shard_range where id in (0, 10000);
+------+
| str  |
+------+
| a    |
| b    |
+------+

proxy> select str from mixer_test_shard_range where id = 0 or id = 10000;
+------+
| str  |
+------+
| a    |
| b    |
+------+

proxy> select str from mixer_test_shard_range where id = 0 and id = 10000;
Empty set

proxy> select str from mixer_test_shard_range where id > 100;
+------+
| str  |
+------+
| b    |
+------+

proxy> select str from mixer_test_shard_range where id < 100;
+------+
| str  |
+------+
| a    |
+------+

proxy> select str from mixer_test_shard_range where id >=0 and id < 100000;
+------+
| str  |
+------+
| a    |
| b    |
+------+

Limitations

Select

Insert

Replace

Update

Set

Range Rule

Caveat

Why not vitess?

Vitess is very awesome, and I use some of its code like sqlparser. Why not use vitess directly? Maybe below:

Status

Mixer now is still in development and should not be used in production.

Feedback

Email: siddontang@gmail.com, chuantong.huang@gmail.com