Questions and Answers
There is no automatic separation of queries that perform reads or writes to the different backend servers. However, you can specify to mysql-proxy that one or more of the “backend” MySQL servers are read only.
shell> mysql-proxy \ --proxy-backend-addresses=10.0.1.2:3306 \ --proxy-read-only-backend-addresses=10.0.1.3:3306 &
Specify the path to the socket:
MySQL Proxy is designed to work with MySQL 5.0 or higher, and supports the MySQL network protocol for 5.0 and higher.
--daemon option. To keep track of the process ID, the daemon can be started with the
--pid-file=file option to save the PID to a known file name. On version 0.5.x, the Proxy cannot be started natively as a daemon.
We will keep the high-level parts in the Lua layer to be able to adjust to special situations without a rebuild. Read/Write splitting sometimes needs external knowledge that may only be available by the DBA.
You can run the proxy on the application server, on its own box, or on the DB-server depending on the use case.
Without any special customization the whole connection is sent to the same server. That keeps the whole connection state intact.
We are on the road to the next feature release: 0.9.0. It will improve the performance quite a bit. After that we may be able to enter the beta phase.
Yes, but it is not advised for now.
Lua is fast and the overhead should be small enough for most applications. The raw packet overhead is around 400 microseconds.
In the range of 400 microseconds per request.
You can just change the script and the proxy will reload it when a client connects.
MySQL Proxy can live on any box: application, database, or its own box. MySQL Proxy uses comparatively little CPU or RAM, with negligible additional requirements or overhead.
We are working on that. See http://jan.kneschke.de/2008/5/30/mysql-proxy-rbr-to-sbr-decoding for an example.
126.96.36.199: I currently use SQL Relay for efficient connection pooling with a number of Apache processes connecting to a MySQL server. Can MySQL Proxy currently accomplish this? My goal is to minimize connection latency while keeping temporary tables available.
Only functions and values starting with
proxy.* are provided by the proxy. All others are user provided.
It looks for the script at client-connect and reads it if it has changed, otherwise it uses the cached version.
MySQL Proxy provides some tutorials in the source package; one is
No, MySQL Proxy is an application that forwards packets from a client to a server using the MySQL network protocol. The MySQL Proxy provides a API allowing you to change its behavior.
Yes. If you restart the proxy, you lose the results, unless you save them in a file.
No, being the man-in-the-middle, Proxy cannot handle encrypted sessions because it cannot share the SSL information.
The MySQL network protocol does not allow passwords to be sent in cleartext, all you could capture is the encrypted version.
You can set a debug script in the proxy, which is an exceptionally good tool for this purpose. You can see very clearly which component is causing the problem, if you set the right breakpoints.
There are some ideas to integrate proxy and memcache a bit, but no code yet.
Yes and no. Java connection pools are specific to Java applications, MySQL Proxy works with any client API that talks the MySQL network protocol. Also, connection pools do not provide any functionality for intelligently examining the network packets and modifying the contents.
You can skip the round-trip and use the connection as it was added to the pool. As long as the application cleans up the temporary tables it used. The overhead is (as always) around 400 microseconds.
Yes, MySQL Proxy can listen on any port, provided that none of the MySQL servers are listening on the same port.
188.8.131.52: What about caching the authorization information so clients connecting are given back-end connections that were established with identical authorization information, thus saving a few more round trips?
There is an
--proxy-pool-no-change-user option that provides this functionality.
Yes, gaiaonline. They have tested MySQL Proxy and seen it handle 2400 queries per second through the proxy.
DBSlayer is a REST->MySQL tool, MySQL Proxy is transparent to your application. No change to the application is needed.
184.108.40.206: I tried using MySQL Proxy without any Lua script to try a round-robin type load balancing. In this case, if the first database in the list is down, MySQL Proxy would not connect the client to the second database in the list.
This issue is fixed in version 0.7.0.
You can, but it is not advised because it may block.
Connection Pooling is just one use case of the MySQL Proxy. You can use it for a lot more and it works in cases where you cannot use DBCP (for example, if you do not have Java).
The server limit is given by the value of the
max_connections system variable. The default value is version dependent.
Yes. But you can also start one proxy on each application server to get a similar behavior as you have it already.
Not yet, it is on the list. We are working on a administration interface for that purpose.
There is a proof of concept script for that included. But its far from perfect and may not work for you yet.