Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "installation and use of MYSQL-PROXY". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Installation and use of MySQL Proxy

1. MYSQL installation

(slightly)

2. MYSQL PROXY installation

2.0 get system information

Getconf LONG_BIT

Cat / etc/redhat-release

2.1 download

-- binary version (please download v0.7.0 or above)

Wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.6.1-linux-rhel4-x86-32bit.tar.gz

# the specific version depends on the information obtained by SETP2.0

2.2 installation

2.2.1 # essential software: LUA

Cd / opt/install

Wget http://www.lua.org/ftp/lua-5.1.2.tar.gz

Tar zxvf lua-5.1.2.tar.gz

Cd lua-5.1.2

Make linux install

2.2.2 # binary version installation

Tar zxvf mysql-proxy-0.6.1-linux-rhel4-x86-32bit.tar.gz

Cp mysql-proxy-0.6.1-linux-rhel4-x86-32bit/sbin/mysql-proxy $MYSQL directory / bin

Mysql-proxy & / / starts in the background and starts on ports 4040 and 4041 by default

Mysql-P4040 / / now you can also connect to data.

3. MYSQL PROXY usage

Manual: http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-cmdline.html

Help: mysql-proxy-help-all

3.0 Test Environment

10.2.224.236 RHEL4/32/MYSQL 5.0.51a-Smura for short

10.2.224.237 RHEL4/32/MYSQL 5.0.51a-Smurb for short

10.5.232.210 RHEL4/32/MYSQL PROXY 0.6.1-PROXY for short

10.2.226.24-CLIENT for short

3.1 Test read-write separation

Requirements: write on Smura, read on Smurb

Start PROXY:

Mysql-proxy-proxy-read-only-backend-addresses=10.2.224.237:3306

-- proxy-backend-addresses=10.2.224.236:3306

-proxy-lua-script=/opt/install/mysql-proxy-0.6.1-linux-rhel4-x86-32bit/share/mysql-proxy/rw-splitting.lua &

CLENT connection:

Mysql-u probe-P4040-palibaba-h 10.5.232.210 eservice-e'select count (*) from test'

Mysql-u probe-P4040-palibaba-h 10.5.232.210 eservice-e "insert into test values ('236')"

At this time, you are in the same SESSION, no matter how many times you INSERT, the result of your SELECT is the same. Because you insert and check are not the same database at all.

This also shows that reading and writing are separated!

Note that the LUA file here is executed only when the connection is generated.

3.2 Test load balancing

Mysql-proxy-proxy-backend-addresses=10.2.224.237:3306

-- proxy-backend-addresses=10.2.224.236:3306 &

(if so, you can write more)

When testing, note that PROXY will consider balance only if there are more than five SESSION. Here, 10 connections are used:

Mysql-u probe-P4040-palibaba-h 10.5.232.210 eservice

On Smura:

Mysql > show processlist

+-+ +

| | Id | User | Host | db | Command | Time | State | Info |

+-+ +

| | 56 | root | 127.0.0.1 Query 57794 | eservice | Query | 0 | NULL | show processlist |

| | 83 | probe | 10.5.232.210 Sleep 33770 | eservice | Sleep | 42 | | NULL |

| | 84 | probe | 10.5.232.210 Sleep 33772 | eservice | Sleep | 35 | | NULL |

| | 85 | probe | 10.5.232.210 Sleep 33774 | eservice | Sleep | 29 | | NULL |

| | 86 | probe | 10.5.232.210 Sleep 33776 | eservice | Sleep | 23 | | NULL |

| | 87 | probe | 10.5.232.210 Sleep 33778 | eservice | Sleep | 17 | | NULL |

+-+ +

On Smurb:.

Mysql > show processlist

+-+ +

| | Id | User | Host | db | Command | Time | State | Info |

+-+ +

| | 5685701 | root | 127.0.0.1 Query 58177 | eservice | Query | 0 | NULL | show processlist |

| | 5685710 | probe | 10.5.232.210 Sleep 33769 | eservice | Sleep | 50 | | NULL |

| | 5685711 | probe | 10.5.232.210 Sleep 33771 | eservice | Sleep | 42 | | NULL |

| | 5685712 | probe | 10.5.232.210 Sleep 33773 | eservice | Sleep | 37 | | NULL |

| | 5685713 | probe | 10.5.232.210 Sleep 33775 | eservice | Sleep | 31 | NULL |

| | 5685714 | probe | 10.5.232.210 Sleep 33777 | eservice | Sleep | 25 | | NULL |

+-+ +

From this point of view, it looks like load balancing is working!

4. Failed handover

When we start with multiple servers:

Mysql-proxy-proxy-backend-addresses=10.2.224.237:3306

-- proxy-backend-addresses=10.2.224.236:3306 &

Only one node (237) DOWN. Then the PROXY can't be connected!

(the original SESSION connected to 236 will not be broken, but the newly added connection will be reported:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0)

At this time:

PROXY News: network-mysqld-proxy.c.3709: connect (10.2.224.237 connect 3306) failed: Connection refused and try to reconnect!

V0.6.1 has this BUG,V0.7.0 to solve this problem.

5. PROXY management

You can use MYSQL CLIENT to connect directly to the PROXY management window for management queries: (the management port can be changed)

Mysql-P4041-h 10.5.232.210

(root@FuncTestDB:) > select * from proxy_connections

+-+

| | id | type | state | db | |

+-+

| | 0 | server | 0 |

| | 1 | proxy | 0 |

| | 2 | server | 10 |

| | 3 | proxy | 10 | eservice |

+-+

4 rows in set (0.00 sec)

(root@FuncTestDB:) > select * from proxy_config

+-+ +

| | option | value |

+-+ +

| | admin.address |: 4041 |

| | proxy.address |: 4040 |

| | proxy.lua_script | NULL |

| | proxy.backend_addresses [0] | 10.2.224.237 3306 |

| | proxy.backend_addresses [1] | 10.2.224.236 3306 |

| | proxy.fix_bug_25371 | 0 | |

| | proxy.profiling | 1 | |

+-+ +

7 rows in set (0.00 sec)

However, it is said that PROXY is not recommended for use in production environments

This is the end of the content of "installation and use of MYSQL-PROXY". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.