When it comes to “decision time” about which type of MySQL HA (high-availability) solution to implement, and how to architect the solution, many questions come to mind. The most important questions are:

• “What are the best tools to provide HA and Load Balancing?”
• “Should I be deploying this proxy tool on my application servers or on a standalone server?”.

Ultimately, the best tool really depends on the needs of your application and your environment. You might already be using specific tools such as Consul or MHA, or you might be looking to implement tools that provide richer features. The dilemma of deploying a proxy instance per application host versus a standalone proxy instance is usually a trade-off between “a less effective load balancing algorithm” or “a single point of failure.” Neither are desirable, but there are ways to implement a solution that balances all …

This article demonstrates how MySQL sharding with ProxySQL works.

Recently a colleague of mine asked me to provide a simple example on how ProxySQL performs sharding.

In response, I’m writing this short tutorial in the hope it will illustrate ProxySQL’s sharding functionalities, and help people out there better understand how to use it.

ProxySQL is a very powerful platform that allows us to manipulate and manage our connections and queries in a simple but effective way. This article shows you how.

Before starting let’s clarify some basic concepts.

• ProxySQL organizes its internal set of servers in Host Groups (HG), and each HG can be associated with users and Query Rules (QR)
• Each QR can be final (apply = 1) or = let ProxySQL continue to parse other QRs
• A QR can be a rewrite action, be a simple match, have a specific target HG, or be generic
• QRs are defined …

I made some improvements to the 'proxy' inside of MySQL 5.7 that I've created for WarpSQL (Shard-Query 3).  I've made the MySQL proxy/shim pluggable and I moved the SQLClient to sql/sql_client.cc.  I've merged these changes into 'master' in my fork.

Now you can create "SQL shim" plugins (SHOW PASSWORD is implemented in plugin/sql_shim) and install them in the server like regular plugins:

-- command doesn't work
mysql> show password;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password' at line 1

-- install the example sql_shim plugin:
mysql> install plugin sql_shim soname 'sql_shim.so';                                                                 Query OK, 0 rows affected (0.00 sec)

-- now the command works
mysql> show password;
+--+
|  |
+--+
|  |
+--+
1 row in set (0.00 sec)

…

I made some improvements to the 'proxy' inside of MySQL 5.7 that I've created for WarpSQL (Shard-Query 3).  I've made the MySQL proxy/shim pluggable and I moved the SQLClient to sql/sql_client.cc.  I've merged these changes into 'master' in my fork.

Now you can create "SQL shim" plugins (SHOW PASSWORD is implemented in plugin/sql_shim) and install them in the server like regular plugins:

-- command doesn't work
mysql> show password;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password' at line 1

-- install the example sql_shim plugin:
mysql> install plugin sql_shim soname 'sql_shim.so';                                                                 Query OK, 0 rows affected (0.00 sec)

-- now the command works
mysql> show password;
+--+
|  |
+--+
|  |
+--+
1 row in set (0.00 sec)

…

[this is a repost of my http://shardquery.com blog post, because it did not syndicate to planet.mysql.com]

As work on WarpSQL (Shard-Query 3) progresses, it has outgrown MySQL proxy.  MySQL proxy is a very useful tool, but it requires LUA scripting, and it is an external daemon that needs to be maintained.  The MySQL proxy module for Shard-Query works well, but to make WarpSQL into a real distributed transaction coordinator, moving the proxy logic inside of the server makes more sense.

The main benefit of MySQL proxy is that it allows a script to "inject" queries between the client and server, intercepting the results and possibly sending back new results to the client.  I would like similar functionality, but inside of the server.

For example, I would like to implement new SHOW commands, and these commands do not need to be …

[this is a repost of my http://shardquery.com blog post, because it did not syndicate to planet.mysql.com]

As work on WarpSQL (Shard-Query 3) progresses, it has outgrown MySQL proxy.  MySQL proxy is a very useful tool, but it requires LUA scripting, and it is an external daemon that needs to be maintained.  The MySQL proxy module for Shard-Query works well, but to make WarpSQL into a real distributed transaction coordinator, moving the proxy logic inside of the server makes more sense.

The main benefit of MySQL proxy is that it allows a script to "inject" queries between the client and server, intercepting the results and possibly sending back new results to the client.  I would like similar functionality, but inside of the server.

For example, I would like to implement new SHOW commands, …

As work on WarpSQL (Shard-Query 3) progresses, it has outgrown MySQL proxy.  MySQL proxy is a very useful tool, but it requires LUA scripting, and it is an external daemon that needs to be maintained.  The MySQL proxy module for Shard-Query works well, but to make WarpSQL into a real distributed transaction coordinator, moving the proxy logic inside of the server makes more sense.

The main benefit of MySQL proxy is that it allows a script to “inject” queries between the client and server, intercepting the results and possibly sending back new results to the client.  I would like similar functionality, but inside of the server.

For example, I would like to implement new SHOW commands, and these commands do not need to be implemented as actual MySQL SHOW commands under the covers.

For example, for this blog post I made a new example command called “SHOW PASSWORD”

Jedox is a software vendor that specializes in OLAP services and solutions. The company has been around quite a while and is probably best known for their PALO MOLAP engine and the matching add-in for Microsoft Excel.

Jedox' flagship product, Jedox Premium comprises the Palo MOLAP engine, API's, a REST server, and ETL server, and client tools. It also comes with a MDX interpreter and a XML for Analysis server. An interesting tidbit is that the MDX layer is not considered native, and Jedox' own clients use a lower level API, or address it via the REST service.

In this blog post I will explain how to install and configure the Open Source browser-based ad-hoc query and analysis tool Xavier to use it with Jedox. A video of the process is embedded below:


Here's a …

One of the great features of Shard-Query is the ability to use MySQL proxy to access resultsets transparently. While this is a great tool, many people have expressed reservations about using MySQL Proxy, an alpha component in their production environment.

I recognize that this is a valid concern, and have implemented an alternate method of retrieving resultsets directly in the MySQL client, without using a proxy. This means that any node can easily act as the “head” node without any extra daemon, instead of having to run many proxies.

The sq_helper() routine has been checked into the git repository and is available now.

The function takes a few parameters:

• sql to run
• shard-query schema name (empty string or null for default schema)
• schema to store temp table in
• temp table name (where results are sent to)
• return result (boolean, 1 returns …

Spil Games hackday At Spil Games we organise a monthly hackday (actually the last two Thursday/Friday of the month) where several teams try to hack something in limited amount of time. The only rules for the hackday are: make something cool, keep it simple, document it, learn something and have fun. In general it means time is […]

The post Spil Games hackday: research MaxScale appeared first on Spil Games Engineering.