Mixer

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

Features

Supports basic SQL statements (select, insert, update, replace, delete)
Supports transactions
Splits reads and writes (not fully tested)
MySQL HA
Basic SQL Routing
Supports prepared statement: COM_STMT_PREPARE, COM_STMT_EXECUTE, etc.

TODO

Some admin commands
Some show command support, i.e. show databases, etc.
Some select system variable, i.e. select @@version, etc.
Enhance routing rules
Monitor
SQL validation check
Statistics
Many other things ...

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:

master: main MySQL server, all write operations, read operations (if rw_split and slave are not set) will be executed here. All transactions will be executed here too.
slave: if rw_split is set, any select operations will be executed here. (can not set)

Notice:

You can use admin upnode or admin downnode commands to bring a specified MySQL server up or down.
If the master was down, you must use an admin command to bring it up manually.
You must set up MySQL replication for yourself, mixer does not do it.

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:

Parse SQL and find the table operated on
If there are no rule for the table, mixer use the default rule
If a rule exists, mixer tries to route it with the specified key

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

Join not supported, later only cross sharding not supported.
Subselects not supported, later only cross sharding not supported.
Cross sharding "group by" will not work ok only except the "group by" key is the routing key
Cross sharding "order by" only takes effect when the "order by" key exists as a select expression field

select id from t1 order by id is ok.

select str from t1 order by id is not ok, mixer does not known how to sort because it can not find proper data to compare with id
Limit should be used with "order by", otherwise you may receive incorrect results

Insert

"insert into select" not supported, later only cross sharding not supported.
Multi insert values to different nodes not supported
"insert on duplicate key update" can not set the routing key

Replace

Multi replace values to different nodes not supported

Update

Update can not set the routing key

Set

Set autocommit support
Set name charset support

Range Rule

Only int64 number range supported

Caveat

Mixer uses 2PC to handle write operations for multi nodes. You take the risk that data becomes corrupted if some nodes commit ok but others error. In that case, you must try to recover your data by yourself.
You must design your routing rule and write SQL carefully. (e.g. if your where condition contains no routing key, mixer will route the SQL to all nodes, maybe).

Why not vitess?

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

Vitess is too huge for me, I need a simple proxy
Vitess uses an RPC protocol based on BSON, I want to use the MySQL protocol
Most likely, something has gone wrong in my head

Status

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

Feedback

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

License

siddontang/mixer

ErrorLooks like something went wrong!

About

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors 9

Languages

Error
Looks like something went wrong!

Packages