MySQL proxy introduction and personal experience _ MySQL

Introduction to MySQL Proxy and personal experience I received an email from Mysql AB. I was very excited when I saw the Mysql Proxy.
Because I was working on a project, I needed to read and write a mysql database deployed on the telecom server at the same time by China Netcom and China Telecom. there is a dual-line server in the middle that can be used for data transfer. many solutions are not ideal.

Mysql Proxy solves my problem very well. although it is still a test version, it can be three months now, and my business has been running stably.

The following describes the current situation:

The purpose is very clear. in the image below, I only want the web server 3 (China Netcom line) server to quickly read and write Data in the Data base server.

But because "the farthest distance in the world is not... it is a problem from China Telecom to China Netcom ". the ping value from web server 3 to Data base server is above MS at night, so, use the first method (blue ). basically, the image of China Netcom is too slow to be useless.

Fortunately, I have a dual-line server hosted in the dual-line Data center, which can quickly connect Data base server and web server 3. However, this server is running other important services and can only be used for Data transfer.

Finally, I am on web server 2 (dual-line) InstallMysql proxy. when web server 3 needs to connect to the database of the Data base server, it only needs to connect to the IP address of the dual-line server (Redline routing.

What is Mysql proxy?



The MySQL proxy is a simple program between the MySQL client and the MySQL server. it can be used to monitor, analyze, or transmit communication between them. Its flexibility allows you to use it to the maximum extent, including load balancing, fault tolerance, analysis query, and correction. Official website: http://forge.mysql.com/wiki/MySQL_Proxy

I downloaded the package at http://dev.mysql.com/downloads/mysql-proxy/ LinuxMySQL proxy alpha compiled binary version on the system platform.

Decompress the package and copy it to/usr/local/sbin. (if the source code is used, make it)

$ Tar zxfmysql-proxy-0.5.0.tar.gz
$ Cp./mysql-proxy-0.5.0/sbin/mysql-proxy/usr/local/sbin

The command syntax is simple. it uses port 4040 by default.
Suppose you want to proxy the local mysql and directly execute
$ Mysql-proxy &
You can. Note that the mysql server must be 5.0 or later.

Then test
$ Mysql -- host = 127.0.0.1 -- port = 4040 -- user = root -- password
Enter your root password, Hey. it's the same as using port 3306.

Someone may ask: is it a mental illness? I use 3306 directly and quickly. why do I add a proxy?
What I can think of is:
1. in large applications, read/write splitting is sometimes required to improve efficiency.
2. load balancing is also required for databases.
3. local users do not need complex security verification. when remote users require ssl.
4. Other special requirements.

Unfortunately, my project is not that big and I don't need load balancing or read/write splitting for the moment. it's just a simple proxy. so I used the -- proxy-backend-addresses parameter to let it proxy Data base server port 3306 of this server.

$ Mysql-proxy -- proxy-backend-addresses = 192.168.0.1: 3306 &

Test connection:
$ Mysql -- host = localhost -- port = 4040 -- user = root -- password
Enter the Data base server password. the password is connected.

Test on the Netcom server,
$ Mysql -- host = Netcom IP address of the dual-line server -- port = 4040 -- user = root -- password
After you enter the password, you can immediately see mysql>.

SetPHPCode, Testing. the speed is very good. The goal is achieved.

If you have application requirements such as mysql server load balancing and fault tolerance, try mysql-proxy,
I believe it will not disappoint you.
You are also welcome to discuss this issue with me. my contact information is here !!
-------------------------------------------------------------------
All mysql-proxy syntax commands are attached:
$ Mysql-proxy -- help-all

Usage:

Mysql-proxy [OPTION...]-MySQL Proxy

Help Options:

-?, -- Help Show help options
-- Help-all Show all help options
-- Help-admin Show options for the admin-module
-- Help-proxy Show options for the proxy-module

Admin module

-- Admin-address =
 
  
Listening address: port of internal admin-server (default: 4041)
  

  

  

  
Proxy-module
  

  
-- Proxy-address =
  
   
Listening address: port of the proxy-server (default: 4040)
   
-- Proxy-read-only-backend-addresses =
   
    
Address: port of the remote slave-server (default: not set)
    
-- Proxy-backend-addresses =
    
     
Address: port of the remote backend-servers (default: 127.0.0.1: 3306)
     
-- Proxy-skip-profiling disables profiling of queries (default: enabled)
     
-- Proxy-fix-bug-25371 fix bug #25371 (mysqld> 5.1.12) for older libmysql versions
     
-- Proxy-lua-script =
     
      
Filename of the lua script (default: not set)
      
-- No-proxy Don't start proxy-server
      

      

      

      
Application Options:
      
-V, -- version Show version
      
-- Daemon Start in daemon-mode
      
-- Pid-file =
      
        PID file in case we are started as daemon