2

I have an apache web server in my local network (local ip 192.168.0.100). There is a web-site which uses a MySQL database. I added one more slave server (local ip 192.168.0.101) and configured database replication. Now, I want to add a Proxy server for load balancing (at least to test how it works). So, I installed MySQL Proxy on another machine (local ip 192.168.0.102), I created a service in this way 

C:\> sc create "Proxy" DisplayName= "MySQL Proxy" start= "auto" binPath="C:\mysql-proxy\bin\mysql-proxy-svc.exe --defaults-file= C:mysql-proxy\mysql-proxy.cnf"

In coonfiguration file, which is mysql-proxy.cnf I have put these lines:

[mysql-proxy]
daemon = true
log-file = "C:\mysql-proxy\mysql-proxy.log"
log-level = debug
proxy-address = 192.168.0.102:4040
proxy-backend-addresses = 192.168.0.100:3306,192.168.0.101:3306

In coomand line I ran

net start proxy

And I see, that it is running without errors. But now I'm stuck and do not know what to do next. In some guides they suggest running this query:

mysql> select * from proxy_connections;

But where to run, how to run - no one says it. If I just run it on my master server, I obviously get an error "No database selected". Other user guides almost from the very start discuss lua scripting. But I do not need that, at least now. What I want is to simply check load balancing. A simple test case would be to execute a query to the database, and to see, how proxy works. Besides, guys, I see that mysql proxy share folder contains a lot of lua scripts. I see ro-balance for example. Lib directory contains also balance.lua. I guess, load balancing will be easier with these scripts - probably I will have to just specify read and write servers. So, if you have already gone through all these steps - to get just the simplest load balancing work, please, share your experience.

Jacobian
  • 10,122
  • 29
  • 128
  • 221

1 Answers1

3

Start MySQL proxy in this way:

C:\MySQL_proxy\> mysql-proxy.exe --proxy-backend-addresses=<MySQLserver host/IP>:mysql server port --proxy-address=<proxy host>:<proxy port>

You can test by using Mysql command line tool or any other MySQL client and see whether it is connecting to MySQL proxy or not.

mysql -uroot -p -hmysql_proxy-host-name -Pmysql-proxy-port
Mahesh Patil
  • 1,529
  • 1
  • 11
  • 22
  • When I do C:\mysql-proxy\mysql-proxy.exe... I get an error: "unknown option: 192.168.0.100:3306". – Jacobian Nov 11 '13 at 11:16
  • mysql -uroot -p -h192.168.0.102 -P4040 results in an error: Can't connect to MySQL server. The catch is there is no mysql installed on the proxy, but just mysql proxy. Besides, I think I could make an error in the syntax of "mysql -uroot -p -hmysql_proxy-host-name -Pmysql-proxy-port". – Jacobian Nov 11 '13 at 11:23
  • If you are using MySQL proxy version 0.81 alpha then syntax is: mysql-proxy --proxy-backend-addresses=:3306 --proxy-address=: --admin-username=root --admin-password=root make sure that proxy is started successfully then only try to connect using MySQL commandline – Mahesh Patil Nov 11 '13 at 11:35
  • admin-username is your MySQL server username and admin-password is password associated with MySQL user – Mahesh Patil Nov 11 '13 at 11:36
  • Now I get an error "Unknown option: --admin-username=root". As for the version I use the last release - 0.8.3. – Jacobian Nov 11 '13 at 11:52
  • hmm .. make sure that you typed --proxy-backend-addresses and proxy-address option correctly and remove --admin options. Now I do not have setup otherwise I could have helped you in syntax. If still there is a problem you can report in bugs.mysql.com under proxy section – Mahesh Patil Nov 11 '13 at 11:57
  • Now I see it started. I used this line "mysql-proxy ----proxy-backend-addresses=192.168.0.100:3306 --proxy-address:192.168.0.102:4040". By the way, why should I start it in this way and not to use a default file with settings? And can you, please, clarify the syntax for "mysql -uroot -p -hmysql_proxy-host-name -Pmysql-proxy-port". – Jacobian Nov 11 '13 at 12:00
  • 2
    http://downloads.mysql.com/docs/mysql-proxy-en.pdf this has lot of information about proxy, this will be helpful. – Mahesh Patil Nov 11 '13 at 12:05
  • Official documentation is rather vague. For example, it says to run "mysql --host=localhost --port=4040" to connect to MySQL server. But I do not understand what host should actually be - either 192.168.0.102 (IP of the proxy server) OR 192.168.0.100 (IP of the master server). And by the way, as for `select * from proxy_connections` which should return a result set, as in user guides, how can I execute this query? On the master-server, but what database should be specified? Any? – Jacobian Nov 11 '13 at 12:14