MySQL Proxy

rw splitting sends selects to master untill i lock tables on master or master is down, then selects will run on slave as it should.

Asked by patlik lindqvist

I have two databases(5.5.6). One master and one slave. the slave is read only.
I got mysql-proxy 0.8.1 and i start it like this:

[mysql-proxy]
LUA_PATH="/mysql-proxy-0.8.1-linux-rhel5-x86-64bit/share/doc/mysql-proxy/?.lua"
proxy-lua-script=/mysql-proxy-0.8.1-linux-rhel5-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua
proxy-backend-addresses=127.0.0.1:3306
proxy-read-only-backend-addresses=127.0.0.1:3307
admin-username=admin
admin-password=xxx
log-file=/tmp/proxy_log.log
log-level=debug
admin-lua-script=/mysql-proxy-0.8.1-linux-rhel5-x86-64bit/lib/mysql-proxy/lua/admin.lua
daemon=true

I access the proxy like:
mysql -uadmin -pxxx -hmy_proxy_test_server -P4040
and it works BUT all querys will run agains the master untill i lock the tables or take down the master (and after that start it again). Then all selects will go agains the slave. Even new sessions selects will run agains the slave. And the inserts/updates will run agains the master.
If i dont miss anything it should work like, all SELECTS against the read only slave and all INSERTS/UPDATES against the master?

Cheers
Patrik

Question information

Language:
English Edit question
Status:
Solved
For:
MySQL Proxy Edit question
Assignee:
No assignee Edit question
Solved by:
patlik lindqvist
Solved:
Last query:
Last reply:
Revision history for this message
patlik lindqvist (patrik-lindqvist) said : 		#1

This is just on a new start up of mysql-proxy it seems. When i have lock a table or stop db, do a select again and it runs on slave, start the master. It works as it should.

/P

Revision history for this message
patlik lindqvist (patrik-lindqvist) said : 		#2

uuuhhhh The lock tables wont work. Dont know why the read/write splitting start to work after a lock tables but it probable didnt have anything to do with teh lock tables. The only way i can get r/w splitt to work (read from slave, write on master) after a stop and start of the proxy is to kill the master and force the selects run agains the slave. Start the master and then it works.

/P

Revision history for this message
patlik lindqvist (patrik-lindqvist) said : 		#3

After some heavy testing. It works:) Problem solved

Revision history for this message
Quentin Pleplé (quentin-pleple) said : 		#4

@Patlik : how did you solved the problem ? Was is a configuration problem or a problem in the LUA script ?

I have the exact same problem. All the queries are forwarded to the master and nothing to the slave.

The state of the master is up and the state of the slave is "unknown", as shows the command :
mysql> SELECT * FROM backends;
+-------------+-------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
 +-------------+-------------------+---------+------+------+-------------------+
| 1 | 10.0.0.1:3306 | up | rw | NULL | 0 |
| 2 | 10.0.0.2:3306 | unknown | ro | NULL | 0 |
 +-------------+-------------------+---------+------+------+-------------------+

Also, none of the two servers has idle connections. Do someone knows how to solve that ?

Revision history for this message
patlik lindqvist (patrik-lindqvist) said : 		#5

@Quentin

The proxy worked with the r/w splitting only when i put heavy load on the machines or started the slave(s) before the master. If the load from the beginning was light the proxy just used the master for r/w.
We ended up not using MySQL proxy at all becouse it didnt performed as we wanted.

Revision history for this message
Charl Matthee (charl-matthee) said : 		#6

@patlik, do you mind sharing what solution you did end up deciding to use then?