Configure ProxySQL
Sometimes you will need to set custom variables in ProxySQL configuration or you will want to cache some queries. This is possible using this tutorial.
Because we have the MySQL cluster horizontally scalable, we use ProxySQL to route queries to the right MySQL node.
Add custom configuration
NOTE
For now this feature is only available from the command line, so go ahead and connect to your cluster. .
We added two hooks to insert your configs. Before setting up any config, have a look at the official documentation . The ProxySQL config looks like this:
admin_variables= { }
mysql_variables=
{
// sane defaults set by us
// hook to insert your extra MySQL variables using 'PROXYSQL_EXTRA_MYSQL_VARIABLES'
}
mysql_servers = ()
mysql_replication_hostgroups = ()
mysql_users =()
mysql_query_rules =
(
// defaults rules set by us
// hook to insert your extra MySQL query rules using 'PROXYSQL_EXTRA_QUERY_RULES'
)
After you’ve
connected to your cluster
, to make things easier, let’s define some helpful constants that will simplify our commands: the Project Namespace and the Site Name. Both can be found in the Bitpoke App on the
Runtime page
of the site.
PROJECT_NS=<your_namespace>
SITE_NAME=<your_site_name>
Set MySQL global variables
To configure some extra MySQL variables, you have to set the PROXYSQL_EXTRA_MYSQL_VARIABLES key into the <SiteName>-proxysql secret. Don’t forget your value should be base64 encoded.
First create a proxysql_extra_mysql_variables.txt file on your local computer, that will contain your code, for example:
mysql_set_query_lock_on_hostgroup=0
For examples of extra configurations for the MySQL variables, a comprehensive list can be found here .
To apply it, patch the secret with the new key base64 encoded:
kubectl -n $PROJECT_NS patch secret $SITE_NAME-proxysql -p "{\"data\": {\"PROXYSQL_EXTRA_MYSQL_VARIABLES\": \"$(cat proxysql_extra_mysql_variables.txt | base64 -w0)\"}}"
Set query rules
The same steps should be done to set MySQL query rules. Create another file proxysql_extra_query_rules.txt containing your rules, you can set PROXYSQL_EXTRA_QUERY_RULES using the following command:
{rule_id=10010, active=1, digest='0xBF001A0C13781C1D', cache_ttl=5000, apply=1},
You can find more details about ProxySQL query rules here .
NOTE
Don’t forget the
,(comma) at the end of each rule.
To apply it, patch the secret with the new key base64 encoded:
kubectl -n $PROJECT_NS patch secret $SITE_NAME-proxysql -p "{\"data\": {\"PROXYSQL_EXTRA_QUERY_RULES\": \"$(cat proxysql_extra_query_rules.txt | base64 -w0)\"}}"
