Connecting MySQL Proxy to a C# Application
I'm currently working on a project that involves splitting large MySQL databases into multiple smaller shards. However, clients must be able to query the databases just as they had before with no change to the user interface; that is, any query they send through a MySQL client (Workbench, DBForge, etc) must return the same result set from the master database as it does from its shards.
This requires that an application be seated between the client and server to intercept queries, analyze them, modify them, and redirect them to master databases or shards as need be. I know that MySQL Proxy is particularly suited to this task, but that's where the problem comes in.
I've already written a C# application that takes a MySQL query string, modifies it, queries whichever shards it must, and aggregates the results from all shards. My problem is that I don't know how to connect MySQL Proxy to this application. Ideally, MySQL Proxy would intercept a query, determine whether it is "shardable" or not, and send it either to my sharding application or to the master database. The sharding application would then send its result set back to MySQL Proxy, which would return it to the client.
Is there any clear way to accomplish this? Perhaps if I were to turn the C# application into a WCF service? MySQL Proxy has methods for connecting to databases, but I don't know if it can connect to a simple web service, much less a WCF, considering it's written in Lua. Is there some alternative to MySQL Proxy that would better meet my needs?
Mysql allows you to script with Lua and connect to C or C++. I have no experience in C# but I supose you will have no problem.
- Install mysql-proxy. You can determine one or more backend mysql DB.
- Compile your C code as a module to be called from Lua: link
- Depending on your code set the backend IP for your query and redirect it.
Be carefull when searching Lua documentation, because mysql-proxy implements Lua 5.1, and procedures change quite a bit from version to version.