proxy-backend-addresses problem

Hi,

You really don't want the proxy to write to both servers, but is what you
are trying to do us read from,both, you will first have to open,enough
connections for the pool to distribute the readoad

Regards

Sent from my Android
Diego

On Sep 24, 2010 11:52 PM, "johny" <email address hidden>
wrote:

New question #126764 on MySQL Proxy:
https://answers.launchpad.net/mysql-proxy/+question/126764

Hello

i have a small problem , i have 2 backend server and mysql proxy write data
only to the first of them, here is the config :
[mysql-proxy]
daemon = true
admin-lua-script=C:/mysql-proxy-0.8.1-win32-x86/share/do/mysql-proxy/rw-splitting.lua
log-file=c:/log.log
log-level = debug
admin-username = root
admin-password = 111111
proxy-backend-addresses=server1:3306,server2:3306

in this case data are inserted only to server 1 , if i change the location
to
proxy-backend-addresses=server2:3306,server1:3306

then data are insert only to server 2 .
the log show

2010-09-24 19:32:19: (message) proxy listening on port :4040
2010-09-24 19:32:19: (message) added read/write backend: 127.0.0.1:3306
2010-09-24 19:32:19: (message) added read/write backend: 192.168.0.4:3306
2010-09-24 19:32:27: (debug) abs wait-for-event::done usec= 0
2010-09-24 19:32:27: (debug) abs lua-exec::done usec= 0
2010-09-24 19:32:51: (debug) abs wait-for-event::done usec= 0
2010-09-24 19:32:51: (debug) abs lua-exec::done usec= 0

--
You received this question notification because you are a member of
MySQL Proxy Discuss, which is an answer contact for MySQL Proxy.

<?php
for ($i=1;$i<5000;$i++)
{
$host="127.0.0.1:4040";
$user="root";
$pass="111111";
$base="test";

$connect=mysql_connect($host,$user,$pass) or die (mysql_error());
 $baseselect=mysql_select_db($base,$connect) or die (mysql_error());
mysql_query("insert into newtable (New) VALUES ('a') ") or die (mysql_error());
mysql_query("select * from newtable") or die (mysql_error());
}
?>
hello i did this but still the same already ;

This the expected behaviour of the script. It isn't designed to send writes to multiple servers. The assumption when you're using this script is that you have a single server which is setup with master-slave replication.

I've done some investigating into this in the past and as far as I know, there's no clean way to use mysql-proxy to send writes to multiple servers.

Thanks for help
[mysql-proxy]
daemon = true
admin-lua-script=C:/mysql-proxy-0.8.1-win32-x86/share/do/mysql-proxy/rw-splitting.lua
log-file=c:/log.log
log-level = debug
proxy-address = 127.0.0.1:4040
admin-username = root
admin-password = 111111
proxy-backend-addresses=server1:3306
proxy-read-only-backend-addresses=server2:3306, server3:3306

1 ) result in query executed only in server1 read and write no matter how many query i send only server1 is used , what can wrong ?
Note : i was able to get the proxy-backend-addresses=server1:3306,server2:3306 to work in windows 2008 setup but insert ,delete , update query are not duplicated in both server each server is used alone this make different DB in both server after some time . 2) is there a way to make insert , update and delete executed in both server in order to preserve similar DB ?
Regards

The script is not designed to duplicate queries. If you want both servers to be consistent, consider using master-slave replication.

Please check the question one too this is a master-slave replication

You don't state anywhere that you are using replication. However, if you are using master-slave replication, then the problem has nothing to do with mysql-proxy, the problem is with your replication setup. For help with that, you're better off posting in the MySQL forums (http://forums.mysql.com)

[mysql-proxy]
daemon = true
admin-lua-script=C:/mysql-proxy-0.8.1-win32-x86/share/do/mysql-proxy/rw-splitting.lua
log-file=c:/log.log
log-level = debug
proxy-address = 127.0.0.1:4040
admin-username = root
admin-password = 111111
proxy-backend-addresses=server1:3306
proxy-read-only-backend-addresses=server2:3306, server3:3306

so the above is not enough to get the read query to be on server2 and server3 and write on server1 ?

i am missing somethings ?

The read-write splitting script will send reads to any one of the servers you specify as read-only. It will send any writes to the server designated as writable. Anything beyond that is quite possibly outside the scope of mysql-proxy. mysql-proxy can only send an incoming query to a single server (no duplication). If you want writes to go to multiple servers, you probably want master-slave replication.

yes and this is the problem i have all read and write sent to server 1 , server 2 and 4 (read only server) are not used at all

i am missing somethings with config ?

Sorry, I misunderstood your latest question. You need to specify each read-only backend as a separate option. Also, rwsplitting.lua should be specified as the proxy script, not the admin script. For example

mysql-proxy \
    --proxy-backend-addresses=server1:3306 \
    --proxy-read-only-backend-addresses=server2:3306 \
    --proxy-read-only-backend-addresses=server3:3306 \
   --proxy-lua-script=rw-splitting.lua

See the blog post below for more information
http://jan.kneschke.de/projects/mysql/mysql-proxy-more-r-w-splitting/