README
¶
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_splitand slave are not set) will be executed here. All transactions will be executed here too. - slave: if
rw_splitis set, any select operations will be executed here. (can not set)
Notice:
- You can use
admin upnodeoradmin downnodecommands 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 idis ok.select str from t1 order by idis not ok, mixer does not known how to sort because it can not find proper data to compare withid -
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.
Directories