Shulou(Shulou.com)06/01 Report--

This article mainly introduces mysql-proxy how to achieve database read-write separation, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated articles in the industry information.

Machine: 192.168.21.139

The necessary condition before completing the actual combat: the master-slave replication environment is set up.

1 MySQL Proxy

MySQL Proxy is a simple program between your client and MySQL server that can monitor, analyze, or change their communications. It is flexible and unlimited, and its common uses include: load balancing, failure, query analysis, query filtering and modification, and so on.

MySQL Proxy is such a middle-tier agent. To put it simply, MySQL Proxy is a connection pool, which is responsible for forwarding connection requests from foreground applications to the background database. Through the use of lua scripts, complex connection control and filtering can be achieved to achieve read-write separation and load balancing. For applications, MySQLProxy is completely transparent, and applications only need to connect to the listening port of MySQLProxy. Of course, the proxy machine may become a single point of failure, but you can use multiple proxy machines as redundancy. You can configure the connection parameters of multiple proxy in the connection pool configuration of the application CVM.

One of the more powerful features of MySQL Proxy is to achieve "read-write separation". The basic principle is to let the master database handle transactional queries and the slave libraries to handle SELECT queries. Database replication is used to synchronize changes caused by transactional queries to slave libraries in the cluster.

2 read-write separation architecture

3 deployment

Hostnam

IP address

System version

Mysql-proxy

192.168.21.139

CentOS release 6.4 (Final) 2.6.32-431.el6.x86_64

Mysql-master

192.168.21.135

CentOS release 6.4 (Final) 2.6.32-431.el6.x86_64

Mysql-slave

192.168.21.135

CentOS release 6.4 (Final) 2.6.32-431.el6.x86_64

4 install software 4.1 install the basic dependency package

Yum-y install gcc gcc-c++autoconf libevent-devel pkgconfig libtool mysql-devel

4.2 other software packages

[root@zyl tools] # wget http://ftp.gnome.org/pub/gnome/sources/glib/2.22/glib-2.22.5.tar.gz

Tar xf glib-2.22.5.tar.gz

Cd glib-2.22.5

. / configure--prefix=/usr/local/glib-2.22.5

Make & & make install

Echo $?

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

Tar xf lua-5.1.4.tar.gz

Cd lua-5.1.4

Sed-i's# ^ INSTALL_TOP=.*#INSTALL_TOP= / usr/local/lua-5.1.4#gi'. / Makefile

Sed-I's # ^ CFLAGS=.*#CFLAGS=-02-fPIC-Wall $(MYCFLAGS) # gi'. / src/Makefile

Make linux install

Echo $?

4.3 install mysql-proxy

Tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

Cdmysql-proxy-0.8.5-linux-glibc2.3-x86-64bit

Cd..

Cp-amysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy

Mv mysql-proxy / application/

Cd / application/

Ls

Pkill mysql

Netstat-lnput | grep 330

Cd mysql

Cd.. / mysql-proxy/

Ls

Mkdir conf

Mkdir logs

Ls

Cd. / conf/

Ls

# add to PATH environment variable

Echo 'exportPATH=$PATH:/application/mysql-proxy/bin/' > > / etc/profile

Tail-1 / etc/profile

Source / etc/profile

Which mysql-proxy

/ application/mysql-proxy/bin/mysql-proxy--help

Vim. / mysql-proxy.cnf

#

Simple one configuration (192.168.21.135: single machine with multiple instances)

[mysql-proxy]

Plugins=admin,proxy

Admin-username=admin

Admin-password=admin

Admin-lua-script=/application/mysql-proxy/lib/mysql-proxy/lua/admin.lua

Proxy-backend-addresses=192.168.21.135:3306 è master

Proxy-read-only-backend-addresses=192.168.21.135:3308 è slave

Proxy-lua-script=/application/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

Level of log-level=debug Lo

Keepalive=true attempts to restart mysql-proxy when it crashes

Daemon=true starts mysql-proxy in daemon mode

Log-file=/application/mysql-proxy/logs/proxy.log

# #

Chmod 0660 mysql-proxy.cnf

Start:

/ application/mysql-proxy/bin/mysql-proxy--defaults-file=/application/mysql-proxy/conf/mysql-proxy.cnf

Cat logs/proxy.log

[root@zyl tools] # netstat-lnpt | grep mysql-proxy

Tcp 0 0 0.0.0.0 4040 0.0.0.015 * LISTEN 4261/mysql-proxy

Tcp 0 0 0.0.0.0 4041 0.0.0.015 * LISTEN 4261/mysql-proxy

4040 is the proxy port; 4041 is the admin port, that is, management.

5 mysql-master end operation

Mysqlgrant all on *. * to 'zyl'@'192.168.21.139' identified by' 888666'

Mysql > flush privileges

# #

Open more terminals and test the connection:

[root@zyl-master] # mysql-u zyl-p-h 192.168.21.139-port 4040

[root@zyl mysql-proxy] # mysql-uadmin-P4041-h 192.168.21.139-padmin

Mysql > select * from help

+-+-

| | command | description |

+-+-

| | SELECT * FROM help | shows this help | |

| | SELECT * FROM backends | liststhe backends and their state | |

+-+-

2 rows in set (0.00 sec)

Mysql > SELECT * FROM backends

+-+ +

| | backend_ndx | address | state | type | uuid | connected_clients | |

+-+ +

| | 1 | 192.168.21.135 NULL 3306 | unknown | rw | NULL | 0 |

| | 2 | 192.168.21.135 unknown | unknown | ro | NULL | 0 |

+-+ +

End result:

Mysql > select * frombackends

+-+ +

| | backend_ndx | address | state | type | uuid | connected_clients | |

+-+ +

| | 1 | 192.168.21.135 NULL 3306 | up | rw | NULL | 0 |

| | 2 | 192.168.21.135 up | up | ro | NULL | 0 |

+-+ +

2 rows in set (0.00 sec)

# # if there are two up, the separation is successful.

Chapter II Summary of the problems encountered

1.

[root@zyl lua-5.1.4] # make linuxinstall

Cd src & & make linux

Make [1]: Entering directory` / home/tools/lua-5.1.4/src'

Make allMYCFLAGS=-DLUA_USE_LINUX MYLIBS= "- Wl,-E-ldl-lreadline-lhistory-lncurses"

Make [2]: Entering directory` / home/tools/lua-5.1.4/src'

Gcc-O2-Wall-fPIC-DLUA_USE_LINUX-c-o lua.o lua.c

In the file contained from lua.h:16

From lua.c:15:

Luaconf.h:275:31: error: readline/readline.h: there is no such file or directory

Luaconf.h:276:30: error: readline/history.h: there is no such file or directory

Lua.c: in the function 'pushline':

Lua.c:182: warning: implicitly declare the function 'readline'

Lua.c:182: warning: assign integers to pointers when assigning values without type conversion

Lua.c: in the function 'loadline':

Lua.c:210: warning: implicitly declare the function 'add_history'

Make [2]: * * [lua.o] error 1

Make [2]: Leaving directory` / home/tools/lua-5.1.4/src'

Make [1]: * * [linux] error 2

Make [1]: Leaving directory` / home/tools/lua-5.1.4/src'

Make: * * [linux] error 2

Resolve:

Yum installlibtermcap-devel ncurses-devel libevent-devel readline-devel

-

two。

[root@zyl tools] # mysql-u zyl-p-h 192.168.21.139-- port 4040

ERROR 1105 (HY000): # 07000MySQLProxy Lua script failed to load. Check the error log.

Resolve:

[root@zyl lua] # pkillmysql-proxy

[root@zyl lua] # cp./mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua/opt/mysql-proxy/lib/mysql-proxy/lua/

[root@zyl lua] # vim/opt/mysql-proxy/init.d/mysql-proxy

Change the script:

Check the script:-- proxy-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua "

-

3.

[root@zyl lua] # mysql-u zyl-p-h 192.168.21.139-- port 4040

Note: because the rw-splitting.lua script has 4 links to enable separation by default, open a few more terminals; test a few more; you can also modify the related values in it.

Change the following parameters of rw-splitting.lua read-write separation script:

4. Error compiling glib Times

Configure: error:

* You must have either havegettext support in your C library, or use the

* * GNU gettext library. (http://www.gnu.org/software/gettext/gettext.html

Resolve:

[root@zyl glib-2.22.5] # yum install-y gettext

-

5.

[root@zyl conf] # / application/mysql-proxy/bin/mysql-proxy--defaults-file=/application/mysql-proxy/conf/mysql-proxy.cnf

2016-09-02 22:28:37: (critical) mysql-proxy-cli.c:326: loading config from'/application/mysql-proxy/conf/mysql-proxy.cnf' failed: permissions of/application/mysql-proxy/conf/mysql-proxy.cnf aren't secure (0660 or stricter required)

2016-09-02 22:28:37: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328

2016-09-02 22:28:37: (message) shuttingdown normally, exit code is: 1

Resolve:

[root@zyl mysql-proxy] # chmod0660 mysql-proxy.cnf

Read the above about mysql-proxy how to achieve database read-write separation, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.