MySQL Forums - MySQL Proxy

switching the backend server at the query level (no replies)

R T — Thu, 16 May 2013 10:49:00 +0000

Is it possible to choose the backend-server where to send the query in the read_query() function? I need first analyse the query then decide where it should go. If yes then how do i do that?

Method to treat LUA_USERDATA in C language (no replies)

明弘関 — Tue, 07 May 2013 00:38:19 +0000

Hi,

I modify the record set that mysql returned and am going to go back up to a client.
I made "processing I did a hook in read_query_result in lua script, and to call C module".
But I do not understand a method to receive in the C side.
lua_type is LUA_USERDATA.
Is there the structure body which can handle these data?
Is there the method elsewhere?

Thanks,
Akihiro Seki

-- testlib.cpp -------------------------------

#include
#include
#include
#include
#include
#include
#include

namespace
{

int testlib_decrypt_result(lua_State* L)
{
// ??????* recs = (???*)lua_touserdata(L,1);

const int num = lua_gettop(L);
if (num == 0) {
printf("No stack.\n");
return 1;
}
printf("Num:%d\n",num);
for (int i = num; i >= 1; i--) {
printf("%03d(%04d): ", i, -num + i - 1);
int type = lua_type(L, i);
switch(type) {
case LUA_TNIL:
printf("NIL\n");
break;
case LUA_TBOOLEAN:
printf("BOOLEAN %s\n", lua_toboolean(L, i) ? "true" : "false");
break;
case LUA_TLIGHTUSERDATA:
printf("LIGHTUSERDATA\n");
break;
case LUA_TNUMBER:
printf("NUMBER %f\n", lua_tonumber(L, i));
break;
case LUA_TSTRING:
printf("STRING %s\n", lua_tostring(L, i));
break;
case LUA_TTABLE:
printf("TABLE\n");
break;
case LUA_TFUNCTION:
printf("FUNCTION\n");
break;
case LUA_TUSERDATA:
printf("USERDATA\n");
break;
case LUA_TTHREAD:
printf("THREAD\n");
break;
}
}
printf("-----------------------------\n\n");

return 1;
}

const luaL_Reg testlib[] =
{
{ "decrypt_result", testlib_decrypt_result },
{ NULL , NULL }
};

} // namespace

extern "C" int luaopen_testlib(lua_State* L)
{
#if LUA_VERSION_NUM >= 502
luaL_newlib(L, testlib);
#else
luaL_register(L, "testlib", testlib);
#endif
return 1;
}
----------------------------------------------

-- sample.lua -------------------------------
function read_query_result( inj )

a = require("testlib")
ret = a.decrypt_result(inj.resultset)
print("decrypt_result:" .. ret)

proxy.response.type = proxy.MYSQLD_PACKET_OK
proxy.response.resultset = inj.resultset

return proxy.PROXY_SEND_RESULT

end
----------------------------------------------

mysql proxy issue: backend servers "State" is "Unknown" (no replies)

Mike Wong — Tue, 23 Apr 2013 22:34:12 +0000

Not sure if anyone cares to help me or not. But I just setup mysql proxy running 0.8.3 on two mysql servers running master and slave configuration. I follow several instructions on the web to have it configured. when I used the admin monitoring to SELECT * FROM backends;

It showd that all of my backend servers are in "unknown" state:

mysql> SELECT * FROM backends;
+-------------+------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+------------------+---------+------+------+-------------------+
| 1 | 172.16.10.94:3306 | unknown | rw | NULL | 0 |
| 2 | 172.16.10.93:3306 | unknown | ro | NULL | 0 |
+-------------+------------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)

Here is my config file:

# MySQL Proxy's configuration file (mysql-proxy.cnf)

[mysql-proxy]
daemon = true
proxy-address=172.16.10.45:4040
admin-lua-script = /opt/mysql-proxy-0.8.3/lib/mysql-proxy/lua/admin.lua
admin-username = admin
admin-password = xxxxxx
pid-file = /opt/mysql-proxy-0.8.3/mysql-proxy.pid
log-file = /opt/mysql-proxy-0.8.3/mysql-proxy.log
log-level = debug
plugins = proxy,admin
plugin-dir=/opt/mysql-proxy-0.8.3/lib/mysql-proxy/plugins
proxy-backend-addresses = 172.16.10.94:3306
proxy-read-only-backend-addresses = 172.16.10.93:3306
proxy-lua-script = /opt/mysql-proxy-0.8.3/lua/rw-splitting.lua
proxy-lua-script = /opt/mysql-proxy-0.8.3/lua/auditing.lua
proxy-lua-script = /opt/mysql-proxy-0.8.3/lua/active-queries.lua
proxy-lua-script = /opt/mysql-proxy-0.8.3/lua/ro-pooling.lua
keepalive = true

Not sure what I'm doing wrong??? Hopefully someone can help...

how to measure the mysql-proxy performance (no replies)

niraj vara — Fri, 29 Mar 2013 06:20:50 +0000

Hi

I have installed the latest mysql-proxy now I want to measure the performenance with compare to normal mysql.

I have already configure the master-slave replication.

Please suggest some tool with few example to measure the difference of performance.

Under what conditions destination backend is changed? (1 reply)

Vladimir Linevich — Wed, 27 Mar 2013 13:33:32 +0000

I'm using mysql-proxy-0.8.2-1 for load balancing purposes.
I've failed to get an answer from documentation,
it's' not obvious, under what conditions destination backend is changed to a next one in upstream.

I was looking into 'balance.lua', but it seems that balancing mechanism does not use this script -
it keeps working even when i move '/usr/lib64/mysql-proxy/lua/proxy/balance.lua' to a different dir (and restart proxy).

My configuration looks like this
--daemon --log-file=/var/log/mysql/proxy.log --log-level=debug
--user=nginx \
--proxy-address=172.16.3.103:3306
--proxy-backend-addresses=172.16.3.104:3306
--proxy-backend-addresses=172.16.3.105:3306

#Adding\removing the next line gives no effect:
--proxy-lua-script=/usr/lib64/mysql-proxy/lua/proxy/balance.lua

I'm querying proxy host from two boxes with this command line:
while (:); do mysql -uvm1 -ppass -e "select id,created from mydb.goods where id = FLOOR(7 + (RAND() * 5))" --port 3306 --host 172.16.3.103; done;

If this loop is started on one machine, most queries are accepted by the first backend (172.16.3.104), and a very tiny part goes to the second one (172.16.3.105).
When i start the 'while' loop on another client system, queries are separated 50\50 between mysql backends.

This situation repeats with and without including 'balance.lua' script.

Please explain, what global variables on mysql backend are being checked when decision about the backend is made.

Best Regards.

Mysql-Proxy and GSLB (GSS) (no replies)

Derek Meyer — Wed, 27 Feb 2013 17:22:17 +0000

To All:

I have what Might be a setup issue or a bug.. I am unsure so I figured I would ask people that have more experience than I do on this topic.

High Level Setup assume the DB server name is db-server

Master-Master setup between 10.10.10.10 and 10.10.10.11

Sample: East/West Coast
West Coast user resovles to 10.10.10.10 (Server on the west coast)
Multiple West Coast process servers with mysql-proxy pointing to the server name of "db-server" (10.10.10.10)
East Coast user resolves to 10.10.10.11 (Server on the East Coast)
Multiple East Coast process servers with mysql-proxy pointing to the server name of "db-server" (10.10.10.11)

At a even higher level
If you do a nslookup on "db-server.foo.com" on the west coast you return 10.10.10.10.
If you do a nslookup on "db-server.foo.com" on the east coast you return 10.10.10.11

All systems proxy point to db-server.foo.com

Everything works great as I have accounted for most items for this application.

Now what "may" be the bug... If I shutdown the West coast db-server (10.10.10.10) I would expect the proxy to see a breif failure and reconnect to the "new" East coast servers of 10.10.10.11 This does not happen

The West coast servers continue to attempt to connect to 10.10.10.10 until the proxy is restarted.

I have validated that after I shutdown the west coast instance of db-server (10.10.10.10) nslookup/dig of db-server resolves properly to the East coast system (10.10.10.11)

If I was to hazard a guess I "think" mysql-proxy caches the IP address of whatever server it has configured.. How do I decrease that cache time? or is something else going on that I am not aware of?

mysql-proxy is setup to point to db-server (GSS controls the IP of DB-server incase one goes offline)

Proxy fixing application issues (3 replies)

steve NOT_FOUND — Fri, 22 Feb 2013 12:55:50 +0000

Hi

I am having issues with 3rd party application and placed the mysql proxy inline to see if I could identify the problem. I am using a very basic lua script which created two files, one for query errors and one for all queries. However, the problems disappeared when I started using the proxy. I removed the proxy and the problems immediately started again. I am at a loss to explain this behavior, any thoughts would be welcome. Is the proxy performing any other function other than intercepting and forwarding connections/queries ?

Thanks

Steve

Mysql Proxy Slave Unknown (3 replies)

dileep ch — Wed, 24 Apr 2013 21:43:24 +0000

Hi,

I'm very new to mysql proxy. I configured proxy installed in one machine and Master and slave.

Proxy conf i used

[mysql-proxy]
daemon = true
log-level = debug
admin-address=10.0.1.11:4041
admin-username=root
admin-password=purpletalk
admin-lua-script=/usr/lib/mysql-proxy/lua/admin.lua
proxy-address=10.0.1.11:4040
proxy-read-only-backend-addresses=10.0.1.119:3306
proxy-backend-addresses=10.0.1.37:3306
proxy-skip-profiling=true
proxy-lua-script=/usr/lib/mysql-proxy/lua/proxy/balance.lua

When connecting with 4041

mysql> select * from backends;
+-------------+-----------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-----------------+---------+------+------+-------------------+
| 1 | 10.0.1.37:3306 | up | rw | NULL | 0 |
| 2 | 10.0.1.119:3306 | unknown | ro | NULL | 0 |
+-------------+-----------------+---------+------+------+-------------------+
I could not understand why 10.0.1.119 is unknown.

I started proxy using command

mysql-proxy --defaults-file=/etc/mysql-proxy/proxy.conf

Please do Help ..
Thanks,
Dileep.

Install mysql proxy when I'm faced with the following error (no replies)

s h — Fri, 15 Feb 2013 05:07:55 +0000

Install mysql proxy when I'm faced with the following error
make[3] ***[sql-tokenizer.c] Error1
make[3] Leaving directory 'root/mysql-proxy/lib'
make[2] ***[all-recursive] Error1
make[2] Leaving directory 'root/mysql-proxy/lib'
make[1] ***[all-recursive] Error1
make[1] Leaving directory 'root/mysql-proxy'
make ***[all] Error 2

I am installing Mysql-proxy as Follows::

% bzr branch lp:mysql-proxy
% cd mysql-proxy
% sh ./autogen.sh
% ./configure --enable-maintainer-mode --with-lua=lua5.1
% make
% make install

Concurrent connections, lua script blocking (no replies)

Сар Рoе — Thu, 14 Feb 2013 18:34:14 +0000

I'm finding that mysql proxy cannot process more than one request at a time while in a lua script. While processing the lua script, requests from other connections are blocked.

I've created a simple example with a proxy that execute's a sleep command. While this is running, the proxy does not process any other clients' commands.

Here's the script. It may be easier to read here: http://pastebin.com/NaZQ46NR

==== sleeptest.lua ====

local tokenizer = require("proxy.tokenizer")

function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
local query = string.sub(packet, 2)

local tokens = tokenizer.tokenize(query)

if #tokens >= 1 and tokens[1].token_name == 'TK_LITERAL' then
local command = string.lower(tokens[1].text)

if command == 'sleep' then
if #tokens == 2 then
local sleep_time = tonumber(tokens[2].text)
os.execute("sleep " .. sleep_time)

proxy.response.type = proxy.MYSQLD_PACKET_OK
proxy.response.resultset = {
fields = {
{ type = proxy.MYSQL_TYPE_LONG, name="sleep_time", },
},
rows = {
{ sleep_time },
}
}
return proxy.PROXY_SEND_RESULT
end

end
end
end
end

function read_query_result(inj)

end

==== steps to reproduce ====

1. run mysql-proxy, loading sleeptest.lua
2. connect two clients to the proxy (A and B)
3. in client A, run the command "sleep 10;"
4. in client B, run the command "select 1;"

client B will not show the result of the query until client A's command has completed processing

I've tried the --event-threads option but setting it to a higher number doesn't seem to help.

Is this expected behaviour? Or am I missing something?

==== version info ====
mysql-proxy 0.8.3
chassis: mysql-proxy 0.8.3
glib2: 2.16.6
libevent: 1.4.13-stable
LUA: Lua 5.1.4
package.path: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua;
package.cpath: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.so;
-- modules
proxy: 0.8.3

LAST_INSERT_ID using triggers the safe way (no replies)

Kosta Psimoulis — Fri, 21 Dec 2012 19:39:00 +0000

Hello,

Since triggers do not remember the last_insert_id updates, I created a trigger, a function and a lua script to handle that. I am gonna be doing rw splitting using the proxy-read-only-backend-addresses option. I know that there has been some modifications in the code to handle LAST_INSERT_ID calls to go through the same backend.
My question basically is, would the following script affect the modifications that have been done in mysql-proxy to handle this and how would the rw splitting work if I use the proxy-read-only-backend-addresses option ? Would it automatically read the script from /usr/share/mysql-proxy/rw-splitting.lua ? And between that script and my proxy-lua-script which one would have priority ?

trigger code

CREATE TRIGGER `t1_insert_after_trigger` AFTER INSERT ON `t1` FOR EACH ROW BEGIN
    INSERT INTO t2(name,address) VALUES(NEW.username,NEW.email);
    set @trigger_last_insert_id := (select last_insert_id());
END;

function code

CREATE FUNCTION `TRIGGER_LAST_INSERT_ID`() RETURNS int(11)
BEGIN
  DECLARE t_id INT;
  IF @trigger_last_insert_id IS NULL THEN
    SET t_id = 0;
  ELSE
    SET t_id = @trigger_last_insert_id;
  END IF;
  RETURN (t_id);
END

lua code

function read_query( packet )
   if string.byte(packet) == proxy.COM_QUERY then
     local query = string.sub(packet, 2)
     local replacing = false
     if string.match(string.upper(query), 'LAST_INSERT_ID') then
         query = string.gsub(query,'LAST_INSERT_ID', 'TRIGGER_LAST_INSERT_ID')
         replacing = true
     end
     if (replacing) then
         proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
         return proxy.PROXY_SEND_QUERY
     end
   end
end

Force proxy to establish at least one connection to each backend server? (no replies)

Van Stokes — Thu, 29 Nov 2012 17:35:42 +0000

Is there a way to force MySQL Proxy 0.8.3 to establish and maintain at least one connection to each backend server at start up?

mysql-proxy rw-splitting.lua error (2 replies)

jeffin mathew — Fri, 07 Dec 2012 10:52:22 +0000

Hi,

I am using mysql-proxy for rw-splitting and load balancing. But when ever i started mysql-proxy with rw-splitting script i am not able to connect to mysql-proxy machine from slave, it gives me the following error

# mysql -uroot -pmysql -h 192.168.0.108 -P4040
ERROR 1105 (00000): #07000MySQL Proxy Lua script failed to load. Check the error log.

I am a new newbie in this please lell me what is the error,

adding my mysql-proxy configuration file below

[mysql-proxy]
daemon = true

pid-file = /usr/local/src/mysql-proxy/mysql-proxy.pid
log-file = /usr/local/src/mysql-proxy/mysql-proxy.log
log-level = message

proxy-backend-addresses = 192.168.0.108:3306

proxy-read-only-backend-addresses = 192.168.0.109:3306,192.168.0.111:3306

proxy-lua-script = /usr/local/src/mysql-proxy/active-queries.lua,/usr/local/src/mysql-proxy/rw-splitting.lua

keepalive = true

why it is not taking the rw-splitting.lua script, please help me in this.

how can mysql-proxy set charset when it start running ? (no replies)

hank wang hank — Wed, 21 Nov 2012 10:33:37 +0000

Hi everyone:

how can i init charset for mysqlproxy ? make the conection to mysql is not default latin1 .

mysql> show variables like '%set%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| auto_increment_offset | 1 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+ |

take changing to GBK for example, I know two ways to make it.
1. client can send "set names 'gbk'" to mysql
2. config mysql as default-character-set=utf8

except above ways, hwo can i init mysqlproxy charset as GBK ?

I found in tutorial-scramble.lua, there are :

proto.to_response_packet({
username = mapped.new_user,
response = password.scramble(s.scramble_buffer, password.hash(mapped.new_password)),
charset = 8, -- default charset
database = c.default_db,
max_packet_size = 1 * 1024 * 1024

then i changed the charset to 3 fot testing, when i starting mysqlproxy with loading the lua, but logon mysql, use "show variables like '%set%';", there are no changes for charset.

can anyone help me ? thanks in advance!

MySQL Proxy works from command line but not as service (no replies)

Paul Berezansky — Fri, 16 Nov 2012 22:23:29 +0000

Using: Windows Vista, Proxy 0.8.3 and ODBC connector

I can start the MySQL proxy from the command line or as a Windows service with identical command options. When running from command line it works fine, I can connect to the database. When running as a service it gives an error 'all backends are down'.

Starting up mysql_proxy 0.8.3 on Linux (no replies)

Joe Pease — Mon, 05 Nov 2012 19:12:06 +0000

After installing mysql_proxy 0.8.3 on my Linux Slackware server and trying to run the command I get two replies; a reply saying "cannot execute binary" and another reply saying "success"

Here's the command line:
path/to/proxy# mysql-proxy --defaults-file=../../../etc/myproxy.cnf --user=root --password=password
mysql-proxy: line 52: /path/to/proxy/mysql-proxy:cannot execute binary file
mysql-proxy: line 52: /path/to/proxy/mysql-proxy:Success

the response is the same if I run the command without the "defaults-file" or user/password parameters.

The "line 52" has no meaning to me as it's certainly not from the myproxy cnf file (it has less than 52 lines) and the error log is empty.

I'll update this periodically with more information.

how proxy handles Lua memory (1 reply)

Charles Knight — Thu, 25 Oct 2012 12:10:57 +0000

The MySQL Proxy Guide says (in the FAQ):
=============================================
7.14: As the script is re-read by MySQL Proxy, does it cache this or is it looking at the file system with each request?
It looks for the script at client-connect and reads it if it has changed, otherwise it uses the cached version.
=============================================

Does this mean Lua remains active while the proxy is running, or does the script get recompiled and run every time there is a request?

I'm working on a project to translate queries from an application that expects its database in a particular form to a database with different field table names. Because of this, I have to load several fairly large dictionaries (or hash tables, if you prefer) that discribe the translation at the start of the lua script. These aren't too large (probably on the order of 128-256 KB), but I suspect proformance would be hampered if every time a query is run, the tables have to be added to memory and then removed after.
Is that how mysql-proxy handles this? If so, is it something to worry about and/or is there a fix?

Thanks

Mysql-Proxy write-only and read-write cases (no replies)

kay kay — Thu, 11 Oct 2012 12:45:25 +0000

At the moment I have one LIVE database. I want to create another ARCHIVE database.

I guess I can use mysql-proxy which will duplicate DB alter queries to both servers and SELECT queries only to live DB.

Is it possible with mysql-proxy?

Some Queries not being logged by MySQL Proxy (no replies)

Eric Hernandez — Tue, 11 Sep 2012 19:57:09 +0000

Hello,

I am using mysql proxy in front of my database server to log all queries submitted by users.

Overall the proxy logging works and has worked for me for years.
However, recently i had a user running a select * from some_table and his queries were not being logged even though he was going through the proxy.

This user was running select * every night on 10-15 different tables and every night the mysql proxy only logged
"set autocommit=1"
The rest of the query was not logged.

The user is coming from windows using SAS and odbc to run his query.

The odd thing is that the logging works without exception for everyone else.

Has anybody had a similar problem?

I am currently working on duplicating the problem but it has been a challenge since I am not a big windows or SAS user.

Here is some of my code that I am using.

function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
local query = string.sub(packet, 2)
logline = ( string.format("# %s -- %s@%s/%s -- [ %6d ] %s\n%s\n\n", os.date('%Y-%m-%d %H:%M:%S'),
proxy.connection.client.username,
proxy.connection.server.dst.name,
proxy.connection.server["default_db"],
proxy.connection.server.thread_id,
proxy.connection.client.src.name,
query ))
writelogline ( logline )
end
end

I am running:
mysql-proxy 0.8.0
glib2: 2.24.2
libevent: 1.4.13-stable
lua: Lua 5.1.4
LUA_PATH: /usr/lib/mysql-proxy/lua/?.lua
LUA_CPATH: /usr/lib/mysql-proxy/lua/?.so
== plugins ==
admin: 0.7.0
proxy: 0.7.0

MySQL Proxy: 0.8.3 released (no replies)

Edwin Desouza — Thu, 30 Aug 2012 15:11:43 +0000

MySQL Proxy: 0.8.3 released
http://jan.kneschke.de/2012/8/30/mysql-proxy-0-8-3-released

Installing from bazaar, dependency issues (no replies)

Kiriakos Krastillis — Tue, 07 Aug 2012 18:33:27 +0000

Hi there!

First of sorry but I'm a total noob regarding Autoconf'y things.

I am thinking about starting to use the mysql-proxy bazaar source repo
for creating packages for my servers. Uppon following the data on:
http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-install-cvs.html
and the install note that comes with the source I am a bit confused.
The install note states that I should have libtool 1.5.x but not newer
even though the project seems to compile fine (note: i have not tested
the binary itself, it only compiles) after satisfying it's
dependencies.

Also I had to do some modifications:
Apparently gtk-doc is also needed for this, I understand that many
development machines may have this by default but still it's a
dependency especially if someone is making this in a server image.
The macro "AM_C_PROTOTYPES" in /configure.in produces error
`configure.in:58: error: automatic de-ANSI-fication support has been
removed' so I butchered it (sorry, as stated I'm a noob)
A non-POSIX warning forces me to include AM_PROG_AR

So, as I said this 'thing' compiled and now I probably am going to
step into a world of hurt trying to find out why it, potentially,
won't work.

Is there anyone interested in giving me some guidelines?

mysql-proxy (error:08S01/1047) (1 reply)

wei biao — Wed, 01 Aug 2012 12:16:00 +0000

mysql-proxy (about:08S01/1047)

1.topology:

mysql-proxy (10.1.1.1)
mysql-master (10.1.1.2-Only-write)
mysql-slave (10.1.1.3-Only-read)

2.Environment:
RHEL-5.5-64
MySQL-5.5.20
MySQL-Proxy-0.8.2

3.Config:
MySQL-Proxy:
vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
min_idle_connections = 1,
max_idle_connections = 1,
vim /usr/local/bin/mysql-proxy
/usr/local/mysql-proxy/bin/mysql-proxy
--proxy-address=10.1.1.1:4040
--proxy-read-only-backend-addresses=10.1.1.3:3306
--proxy-backend-addresses=10.1.1.2:3306
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
--log-level=debug
--log-file=/var/log/mysql-proxy.log
--daemon

Master:
create database db1;
Use db1
Create table t1(id int);
Insert into t1 vaslues(1111);
Grant all on db1.* to testing@"%" identified by "testing~!@#$%^&*"

Slave:
create database db1;
Use db1
Create table t1(id int);
Insert into t1 values(2222);
Grant all on db1.* to testing@"%" identified by "testing~!@#$%^&*"

4.Verify:(client login test)

First time:
mysql -utesting -h 10.1.1.1 -P4000 -p
mysql>select * from db1.t1;
+------+
| id |
+------+
|1111 |
+------+
3 rows in set (0.02 sec)

mysql>show processlist;
+-------+---------+----------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+---------+----------------------+------+---------+------+-------+------------------+
| 22030 | testing | 10.1.1.1:46536 | NULL | Query | 0 | NULL | show processlist |
+-------+---------+----------------------+------+---------+------+-------+------------------+
1 row in set (0.02 sec)

Secondary time:
mysql -utesting -h 10.1.1.1 -P4000 -p
mysql>select * from db1.t1;
+------+
| id |
+------+
|1111 |
+------+
3 rows in set (0.02 sec)

mysql> show processlist;
+-------+---------+----------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+---------+----------------------+------+---------+------+-------+------------------+
| 22030 | testing | 10.1.1.1:46536 | NULL | Query | 0 | NULL | show processlist |
| 22636 | testing | 10.1.1.1:60080 | NULL | Sleep | 3 | | NULL |
+-------+---------+----------------------+------+---------+------+-------+------------------+
2 rows in set (0.02 sec)

third time:
mysql -utesting -h 10.1.1.1 -P4000 -p
mysql>select * from db1.t1;
+------+
| id |
+------+
|2222 |
+------+
3 rows in set (0.02 sec)

mysql> show processlist;
+-------+---------+----------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+---------+----------------------+------+---------+------+-------+------------------+
| 22030 | testing | 10.1.1.1:46536 | NULL | Query | 0 | NULL | show processlist |
| 22636 | testing | 10.1.1.1:60080 | NULL | Sleep | 3 | | NULL |
+-------+---------+----------------------+------+---------+------+-------+------------------+
2 rows in set (0.02 sec)

ssh 10.1.1.3
mysql>show processlist;

| 6 | testing | 10.1.1.1:54607 | NULL | Sleep | 44 |

four time:
mysql -utesting -h 10.1.1.1 -P4000 -p
mysql>select * from db1.t1;
+------+
| id |
+------+
|2222 |
+------+
3 rows in set (0.02 sec)

mysql> show processlist;
+-------+---------+----------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+---------+----------------------+------+---------+------+-------+------------------+
| 22030 | testing | 10.1.1.1:46536 | NULL | Query | 0 | NULL | show processlist |
| 22636 | testing | 10.1.1.1:60080 | NULL | Sleep | 3 | | NULL |
+-------+---------+----------------------+------+---------+------+-------+------------------+
2 rows in set (0.02 sec)

ssh 10.1.1.3
mysql>show processlist;

| 6 | testing | 10.1.1.1:54607 | NULL | Sleep | 44 |
| 8 | testing | 10.1.1.1:57976 | NULL | Sleep | 32 |

five time:
tell me error:
ERROR 1047 (08S01): Unknown command

4.Question:
1>proxy must be a options about set max_connections.
2>when proxy login master/slave ,i quite proxy,
but proxy don't quit master/slave automatically.

5.about log messages:

2012-07-31 19:20:38: (message) mysql-proxy 0.8.2 started
2012-07-31 19:20:38: (debug) max open file-descriptors = 102400
2012-07-31 19:20:38: (message) proxy listening on port 10.1.1.1:4000
2012-07-31 19:20:38: (message) added read/write backend: 10.1.1.2:3306
2012-07-31 19:20:38: (message) added read-only backend: 10.1.1.3:3306
2012-07-31 19:21:58: (debug) [network-mysqld.c:937]: error on a connection (fd: -1 event: 0). closing client connection.

0.8.2 won't connect to MySQL (1 reply)

Greg Willits — Tue, 31 Jul 2012 04:27:55 +0000

OSX Server 10.6.8, mysql-proxy 0.8.2 binary

I'm getting these errors logged:

network-socket.c:401: bind(/var/mysql/mysql.sock) failed: Address already in use (48)
chassis-mainloop.c:267: applying config of plugin proxy failed
mysql-proxy-cli.c:595: Failure from chassis_mainloop. Shutting down.

from this config:

[mysql-proxy]
daemon = true
plugins = proxy
proxy-skip-profiling = true
proxy-address = /var/mysql/mysql.sock
proxy-backend-addresses = x.x.x.x:3306
log-file = /devlogs/mysql-proxy/myproxy.log

or even a simple config like this

[mysql-proxy]
proxy-backend-addresses = 209.80.41.210:3306
log-file = /devlogs/mysql-proxy/myproxy.log

I've setup mysql-proxy a couple times before (earlier versions), and I don't recall a big hoopla. It just worked. Now I'm pulling my hair out.

For now, I'm just trying to get a single box to have an application's mysql connection go through proxy. The app is configured to use 127.0.0.1:4040 for its connection.

I've tinkered with a number of combinations of config setting. With and without daemon, plugins, proxy-address, and others.

I've used proxy-address with the socket, an IP address, and 127.0.0.1. No difference.

If I launch mysql-proxy from command line with no options it seems to work (my app connects to the DB), but as as soon as I define a proxy-backend-addresses I get no connection -- despite config setups identical to my other running installations.

Whether I do any of this by command-line options or cnf file, I get the same non-working results.

Even a simple "mysql-proxy --proxy-backend-addresses = x.x.x.x:3306" on the command line isn't working.

Something about 0.8.2 is different, and I can't figure it out.

Any ideas? Many thanks in advance.

-- gw

login problem (3 replies)

Eric Weaver — Fri, 27 Jul 2012 17:30:16 +0000

I have a master/slave replication pair.

I have set up mysql-proxy on a client machine, with --proxy-backend-addresses=one:3360,the-other:3360 (ip addresses).

The proxy starts up OK, listens on the configured port (3307), and answers.

When I do mysql -h localhost -P 3307 known-good-user -p=known-password , it fails with:

ERROR 1045 (28000): Access denied for user 'known-good-user'@'localhost' (using password: YES)

... and it's pretty much instant about it.

When I run mysql straight at either actual host, it succeeds fine but takes a little while to log in (I suspect due to some stupid reverse-dns issue).

Anybody have a clue stick to hit me with on this one? Thanks.

Global variables - concurrency control (no replies)

Manuel Abbt — Sat, 21 Jul 2012 14:57:58 +0000

Hi,

I want to use MySQL-Proxy with rw-splitting to disburden our Mysql-Master.
90-95% of the incoming queries are read queries. So I plan to split them to one or more Mysql-Slave-Server.
The new environment should manage 50-80 user connections per second with up to 500 queries per connection.

Are there any known issues with the use of global variables in the lua script?
How are they processed? Is there something like a concurrency control?

Regards

Manuel

0.8.2 - mysql.tokenizer not found? (no replies)

Van Stokes — Mon, 25 Jun 2012 19:26:35 +0000

What happened to the 'mysql.tokenizer' module?
It doesn't appear to be in th distribution anymore.
We are getting an error when using our custom RW splitter script...

-- from the output log --

2012-06-25 15:23:16: (critical) (lua-error) [/d01/mysql/proxy/lua/rw-splitting-with-routines.lua]
...roxy/current/lib/mysql-proxy/lua/proxy/tokenizer.lua:22: module 'mysql.tokenizer' not found:
no field package.preload['mysql.tokenizer']
no file '/d01/mysql/proxy/current/lib/mysql-proxy/lua/mysql/tokenizer.lua'
no file '/d01/mysql/proxy/lua/mysql/tokenizer.lua'
no file '/usr/lib/x86_64-linux-gnu/lua/5.1/luasql/mysql/tokenizer.so'
no file '/d01/mysql/proxy/current/lib/mysql-proxy/lua/mysql/tokenizer.so'
no module 'mysql.tokenizer' in file '/usr/lib/x86_64-linux-gnu/lua/5.1/luasql/mysql.so'
2012-06-25 15:23:16: (critical) (lua-error) [/d01/mysql/proxy/lua/rw-splitting-with-routines.lua]
[string "/d01/mysql/proxy/lua/rw-splitting-with-rout..."]:39: loop or previous error loading module 'proxy.tokenizer'

(critical) .\mysql-proxy-cli.c:477: Invalid byte sequence i (no replies)

Pera Kajonpotisuwan — Sun, 24 Jun 2012 09:04:36 +0000

2012-06-24 15:58:10: (critical) .\mysql-proxy-cli.c:477: Invalid byte sequence in conversion input

I try to run MySQL Proxy but I got this error.
how can I solve this issue?

show error "mysqli::mysqli(): (08S01/1047): Unknown command" (2 replies)

hanky cheng — Mon, 20 Aug 2012 06:51:09 +0000

Hi all

I have run Mysql proxy 0.8.2 with mysql 5.5.25 read-write server. But very Strange case that I get error "Unknown command" when I have insert record into DB via proxy but no error direct access read-write server. Here is proxy log as below:

2012-06-12 17:51:52: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=2530 alive
2012-06-12 17:51:52: (debug) chassis-unix-daemon.c:157: waiting for 2530
2012-06-12 17:51:52: (debug) chassis-unix-daemon.c:121: we are the child: 2530
2012-06-12 17:51:52: (message) mysql-proxy 0.8.2 started
2012-06-12 17:51:52: (debug) max open file-descriptors = 1024
2012-06-12 17:51:52: (message) proxy listening on port 192.168.1.20:3306
2012-06-12 17:51:52: (message) added read/write backend: 192.168.1.21
2012-06-12 17:51:52: (message) added read-only backend: 192.168.1.22
2012-06-12 17:52:04: (debug) [network-mysqld.c:937]: error on a connection (fd: -1 event: 0). closing client connection.
2012-06-12 17:59:36: (debug) last message repeated 29 times
2012-06-12 17:59:36: (debug) [network-mysqld.c:937]: error on a connection (fd: -1 event: 0). closing client connection.
2012-06-12 18:03:38: (debug) last message repeated 8 times
2012-06-12 18:03:38: (debug) [network-mysqld.c:937]: error on a connection (fd: -1 event: 0). closing client connection.
2012-06-12 18:06:59: (debug) last message repeated 22 times
2012-06-12 18:06:59: (debug) [network-mysqld.c:937]: error on a connection (fd: -1 event: 0). closing client connection.
2012-06-12 18:14:58: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=2591 alive
2012-06-12 18:14:58: (debug) chassis-unix-daemon.c:157: waiting for 2591
2012-06-12 18:14:58: (debug) chassis-unix-daemon.c:121: we are the child: 2591
2012-06-12 18:14:58: (message) mysql-proxy 0.8.2 started
2012-06-12 18:14:58: (debug) max open file-descriptors = 1024
2012-06-12 18:14:58: (message) proxy listening on port 192.168.1.20:3306
2012-06-12 18:14:58: (message) added read/write backend: 192.168.1.21
2012-06-12 18:14:58: (message) added read-only backend: 192.168.1.22
2012-06-12 18:15:08: (debug) [network-mysqld.c:937]: error on a connection (fd: -1 event: 0). closing client connection.
2012-06-12 18:26:07: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=2627 alive
2012-06-12 18:26:07: (debug) chassis-unix-daemon.c:157: waiting for 2627
2012-06-12 18:26:07: (debug) chassis-unix-daemon.c:121: we are the child: 2627
2012-06-12 18:26:07: (message) mysql-proxy 0.8.2 started
2012-06-12 18:26:07: (debug) max open file-descriptors = 1024
2012-06-12 18:26:07: (message) proxy listening on port 192.168.1.20:3306
2012-06-12 18:26:07: (message) added read/write backend: 192.168.1.21
2012-06-12 18:26:07: (message) added read-only backend: 192.168.1.22
2012-06-12 18:26:18: (debug) [network-mysqld.c:937]: error on a connection (fd: -1 event: 0). closing client connection.
2012-06-12 18:26:58: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=2644 alive
2012-06-12 18:26:58: (debug) chassis-unix-daemon.c:157: waiting for 2644
2012-06-12 18:26:58: (debug) chassis-unix-daemon.c:121: we are the child: 2644
2012-06-12 18:26:58: (message) mysql-proxy 0.8.2 started
2012-06-12 18:26:58: (debug) max open file-descriptors = 1024
2012-06-12 18:26:58: (message) proxy listening on port 192.168.1.20:3306
2012-06-12 18:26:58: (message) added read/write backend: 192.168.1.21
2012-06-12 18:26:58: (message) added read-only backend: 192.168.1.22

anyone can help?
Tks!

How To Use SQL in Proxy ? (no replies)

John Arnold — Mon, 28 May 2012 12:57:49 +0000

Tell me how use SQL in proxy. i have no idea about this.
Designer Leather Sandals | Leather Sandals UK

connecting MySQL on AWS using ODBC Connector (no replies)

Uday kumar — Thu, 10 May 2012 11:48:22 +0000

MySQL server is running on EC2 in AWS. I want to create a ODBC data source using ODBC Database Administrator. I am able to connect it from home. But when i try from office it always fails. I think this is because of the proxy settings inside office.

Can we use mysql-proxy to create ODBC data source from office to MySQL db on AWS ?

Note: I know it is not a secured way to expose 3306 port outside of the public cloud. This is only for experiment.