Thursday, May 9, 2013

Setup MySQL Proxy

So this is just a very simple example of using MySQL Proxy ..

The MySQL Proxy has been in the Alpha stages for what feels like years on end.


MySQL Proxy Documentation :
Whatever is left from the MySQL forge site for MySQL Proxy WIKI can be found here: https://wikis.oracle.com/display/mysql/MySQL+Proxy

Install MySQL Proxy:
Download and unpack from dev.mysql.com. Current Alpha version is mysql-proxy 0.8.3.

Make sure you also have Lua installed

yum install lua-devel lua-static lua


You will then find mysql-proxy in the bin directory.


[root@localhost bin]# ./mysql-proxy --help
Usage:
  mysql-proxy [OPTION...] - MySQL Proxy


MySQL command options:

First need to make sure you are aware of the out of date documentation.

You might think that adding daemon=true to your configuration file is valid.
It does after all point out how true is the valid for the following option via the documentation.



For example, the following is invalid:

[mysql-proxy]
proxy-skip-profiling

But this is valid:

[mysql-proxy]
proxy-skip-profiling = true


http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-configuration.html#option_mysql-proxy_daemon

However daemon or daemon=true results with the following issue so use daemon=1

./bin/mysql-proxy --defaults-file=mysql_proxy.cnf
(critical) Key file contains key 'daemon' which has value that cannot be interpreted.


The same is true for keepalive=true


./bin/mysql-proxy --defaults-file=mysql_proxy.cnf
(critical) Key file contains key 'keepalive' which has value that cannot be interpreted.


Using the Administration Interface:

The admin.lua file is available under the mysql-proxy directory:
The reporter.lua file can also be placed into the  /lib/mysql-proxy/lua/ directory:


So I end up with the following configuration example


vi mysql_proxy.cnf

[mysql-proxy]

admin-address =127.0.0.1:3308

proxy-address=127.0.0.1:3307

proxy-skip-profiling = true

daemon=1
pid-file = /var/run/mysql-proxy.pid
log-file = /var/log/mysql-proxy.log
log-level = debug
proxy-backend-addresses=127.0.0.1:3306
#proxy-read-only-backend-addresses =127.0.0.1:3306
keepalive=1
admin-username=root
admin-password=secretpassword
admin-lua-script=/usr/local/src/MySQL/mysql-proxy/admin.lua
proxy-lua-script=/usr/local/src/MySQL/mysql-proxy/reporter.lua
plugin_dir=/usr/lib/mysql/plugin/
plugins=proxy,admin


This time it starts....


./bin/mysql-proxy --defaults-file=mysql_proxy.cnf


Tail you log to confirm that proxy is up and running then log into the admin port (3308)

(message) proxy listening on port 127.0.0.1:3307
# mysql -u root -p -P3308 -h127.0.0.1

Once you are logged in you can start to test it:

# mysql -u root -p -P3308 -h127.0.0.1

show querycounter;
+---------------+
| query_counter |
+---------------+
|          NULL |
+---------------+



SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 127.0.0.1:3306 | 1     | 1    |
+-------------+----------------+-------+------+



> SHOW PROXY PROCESSLIST;
ERROR 1105 (07000): need a resultset + proxy.PROXY_SEND_RESULT ... got something else



Remember always check your logs...


(critical) (read_query) [string "/usr/local/src/MySQL/mysql-proxy/admin.lua"]:152: bad argument #1 to 'pairs' (table expected, got nil)


OK the MySQL Proxy is up...
Now the issues and complains over Lua and why is it part of MySQL-Proxy can begin...

http://lua.2524044.n2.nabble.com/Beginner-from-Python-starting-to-MySQL-Proxy-td7636807.html


FOLLOW UP:
http://anothermysqldba.blogspot.com/2018/05/proxy-mysql-haproxy-proxysql-keepalived.html

Posted by at
Labels: , ,