Realization of MARIADB read/write separation based on Mysql-proxy

• First, Mysql-proxy simple introduction

  Mysql-proxy is a simple program that is located between your client side and the MySQL server to monitor, analyze, or change their communications. It is flexible, unlimited, and common uses include: load balancing, failure, query analysis, query filtering and modification, and so on.

  Mysql-proxy is such a middle-tier agent, simply said, Mysql-proxy is a connection pool, is responsible for the front-end application of connection requests forwarded to the background database, and through the use of Lua script, can achieve complex connection control and filtering, so as to achieve read-write separation and load balancing. For the application, the mysql-proxy is completely transparent, and the application only needs to be connected to the Mysql-proxy's listening port. Of course, this proxy machine may become a single point of failure, but can use multiple proxy machine as redundancy, in the Application Server connection pool configuration to multiple proxy connection parameters can be configured.

  One of the more powerful features of Mysql-proxy is the realization of "read and write Separation", the basic principle is to let the primary database process transactional queries, so that select queries are processed from the library. Database replication is used to synchronize changes caused by transactional queries to the slave libraries in the cluster.

  Second, the experimental environment

  Server	IP Address	System version
  Master master server	172.16.8.8	Centos6.5
  Slave from the server	172.16.8.9	Centos6.5
  Mysql-proxy Server	172.16.8.5	Centos6.5

  3 Server time to match

  # ntpdate 172.16.0.1//Sync time

  Note, this experiment is based on the previous blog, http://584014981.blog.51cto.com/8605371/1396724, first realize the master-slave replication, and then achieve read and write separation.

  Iii. Introduction of Mysql-proxy command

  Mysql-proxy command

  --help-all ———— to get all the help information

  --proxy-address=host:port ———— the address and port that the agent service listens on

  --admin-address=host:port ———— the address and port that the management module listens to

  --proxy-backend-addresses=host:port ———— the address and port of the backend MySQL server (primary server)

  --proxy-read-only-backend-addresses=host:port--backend read-only MySQL server address and port (from server)

  --proxy-lua-script=file ———— The Lua script to complete the MySQL proxy function

  --daemon ———— start in daemon mode mysql-proxy

  --defaults-file=/path/to/conf_file_name ———— The default configuration file path used

  --log-file=/path/to/log_file_name ———— log file name

  --log-level=level ———— Log Level

  --log-use-syslog ———— Syslog-based logging

  --user=user_name ———— users running the mysql-proxy process

  Iv. Mysql-proxy master and slave separation

  1. Increase the number of proxy users

  To operate on the Mysql-proxy server:

  [Email protected] ~]# groupadd-g 3306 mysql-proxy[[email protected] ~]# useradd-u 3306-g mysql-proxy-s/sbin/nologin -M Mysql-proxy

  2. Installing Mysql-proxy

  ：

  Http://pan.baidu.com/s/1sjQ4czV

  [[Email protected] ~]# tar XF mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz  -c/usr/local[[email protected] ~] # Cd/usr/local[[email protected] local]# ln-sv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit mysql-proxy[[email Protected] local]# CD Mysql-proxy

  3. Modify the owner of the file and the owning group

  [Email protected] mysql-proxy]# chown-r root:mysql-proxy/usr/local/mysql-proxy/*

  4. Modify the PATH environment variable

  [[email protected] mysql-proxy]# vim/etc/profile.d/mysql-proxy.sh//Add the following: Export path= $PATH:/usr/local/mysql-proxy /bin[[email protected] mysql-proxy]# source/etc/profile//Reread configuration file

  5. Start Mysql-proxy

  [Email protected] mysql-proxy]#  mysql-proxy  --daemon--log-level=debug--log-file=/var/log/ Mysql-proxy.log--plugins= "proxy"--proxy-backend-addresses= "172.16.8.8:3306"--proxy-read-only-backend-addresses = "172.16.8.9:3306" #启动mysql-proxy

  From the log and process point of view, has been launched successfully!

  6. Create the test account and password on the master server

  Master

  MariaDB [(None)]> grant all on * * to [e-mail protected] ' 172.16.8.% ' identified by ' 123 '; MariaDB [(None)]> flush privileges;

  7. Testing from the service

  Slave

  Verify that you can connect to the Mysql-proxy via the proxy port 4040 Port

  [Email protected] ~]# mysql-uroot-p-h172.16.8.5--port=4040

  Note that it has been successfully connected to the Mysql-proxy from Port 4040. Let's implement the read and write separation.

  8. Read and write separation

  The 1.mysql-proxy itself does not implement read-write separation, mainly based on Lua scripting

  There is a Rw-splitting.lua script in the Mysql-proxy installation directory specifically for read-write separation, where the path is/usr/local/mysql-proxy/share/doc/mysql-proxy/ Rw-splitting.lua

  2. Provide a management interface for Mysql-proxy, convenient to view back-end MySQL server status and type of access at any time, to achieve management functions

  Note, the following is a script that provides a management interface and is also a LUA script that is recommended to be placed in the same directory as the read-write split script.

  [[email protected] mysql-proxy]# vim/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.luafunction set_error (errmsg) Proxy.response = {type = proxy. Mysqld_packet_err, errmsg = errmsg or "error"}endfunction read_query (PACKET) if Packet:byte () ~= proxy.com_q Uery then Set_error ("[admin] we only handle text-based queries (com_query)") return proxy. Proxy_send_result End Local query = Packet:sub (2) Local rows = {} local fields = {} if query:lower () = = " SELECT * from Backends "then fields = {{name =" Backend_ndx ", type = proxy. Mysql_type_long}, {name = "Address", TYPE = proxy. Mysql_type_string}, {name = "state", TYPE = proxy. Mysql_type_string}, {name = ' type ', type = proxy. Mysql_type_string}, {name = "uuid", TYPE = proxy. Mysql_type_string}, {name = "connected_clients", TyPE = proxy. Mysql_type_long},} for i = 1, #proxy. Global.backends do local states = {"Unknow                N "," Up "," down "} local types = {" Unknown ",                "RW", "RO"} local B = proxy.global.backends[i] rows[#rows + 1] = {  I, B.dst.name,--configured backend address States[b.state + 1],--                The C-id is pushed down starting at 0 Types[b.type + 1],--the C-id was pushed down starting at 0  B.uuid,--the MySQL Server ' s UUID if it is managed b.connected_clients--currently            Connected clients} End ElseIf Query:lower () = = "SELECT * from Help" then fields = { {name = "command", type = proxy. Mysql_type_string}, {name = "description", TYPE = proxy. MysqL_type_string},} rows[#rows + 1] = {"SELECT * from Help", "shows this Help"} rows[#rows + 1] = {  "SELECT * from Backends", "lists the backends and their state"} else Set_error ("Use ' Select The supported Commands ") return proxy. Proxy_send_result End proxy.response = {type = proxy. MYSQLD_PACKET_OK, resultset = {fields = fields, rows = rows}} return proxy. Proxy_send_resultend

  3. Restart Mysql-proxy

  Note that this launch will add the following startup options because we have added additional plugins to add new features to the options below.

  --plugins=admin plug-ins loaded at mysql-proxy startup;

  --admin-username= "Admin" to run the Mysql-proxy process management user;

  --admin-password= "Admin" password

  --admin-lua-script= the configuration file path used by the "/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" plugin;

  Kill the original connection first, and then start the program.

  [Email protected] mysql-proxy]# killall mysql-proxy[[email protected] mysql-proxy]# mysql-proxy--daemon--log-level= Debug--log-file=/var/log/mysql-proxy.log--plugins= "proxy"--proxy-backend-addresses= "172.16.8.8:3306"-- Proxy-read-only-backend-addresses= "172.16.8.9:3306"--proxy-lua-script= "/usr/local/mysql-proxy/share/doc/ Mysql-proxy/rw-splitting.lua "--plugins=admin--admin-username=" admin "--admin-password=" admin "--admin-lua-script = "/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

  4. In slave test, remember to use management port 4041 login, password is admin.

  [[email protected] ~]# mysql-uadmin-p-h172.16.8.5--port=4041  //password for Adminmysql [(None)]> select * from Backends; Viewing read-write detach status

  Note, you can see that the status information is unknown, because we have not performed read and write operations, all the state and up, let's test the read and write information, and then to check the status.

  5. Perform read and write operations

  [Email protected] mysql]# mysql-uroot-p123-h172.16.8.5--port=4040-e "CREATE Database Wowo" [[email protected] mysql]#  mysql-uroot-p123-h172.16.8.5--port=4040-e "Select User from Mysql.user" [[email protected] mysql]# mysql-uroot-p123 -h172.16.8.5--port=4040-e "Select User from Mysql.user"

  6. Check the read and write separation status again

  [[email protected] ~]# mysql-uadmin-padmin-h172.16.8.5--port=4041mysql [(None)]> select * from Backends;

  Note, as you can see, the state is all up. Indicates a successful read-write separation configuration. When testing the read/write separation, we can distribute the tests, such as testing the Read statement first, seeing if the read-write status is up, and then writing the statement in the test. Repeated multiple tests several times to see the effect.

  Five, optimize the startup script, and modify the connection port

  Every time you start up, the startup options are very high, so we create a script or a configuration file to write the options and call this script every time we start.

  (1). Add configuration file

  [[email protected] mysql-proxy]# vim/etc/sysconfig/mysql-proxy//Add the following admin_user= "admin" admin_password= "admin" admin_address= "" admin_lua_script= "/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" PROXY_ADDRESS= " 0.0.0.0:3306 "proxy_user=" Mysql-proxy "proxy_options="--daemon--log-level=info--log-file= "/var/log/ Mysql-proxy.log "--plugins=proxy--plugins=admin--proxy-backend-addresses=172.16.8.8:3306-- proxy-read-only-backend-addresses=172.16.8.9:3306--proxy-lua-script=/usr/local/mysql-proxy/share/doc/ Mysql-proxy/rw-splitting.lua "

  (2). Add SysV Script

  [[email protected] mysql-proxy]# vim/etc/init.d/mysql-proxy//Add the following: #!/bin/bash## mysql-proxy this script Starts and stops the Mysql-proxy daemon## chkconfig:-30# processname:mysql-proxy# Description:mysql-proxy is a ProX Y daemon for mysql# Source function library: /etc/rc.d/init.d/functionsprog= "/usr/local/mysql-proxy/bin/mysql-proxy" # Source networking Configuration.if [-f/ Etc/sysconfig/network]; Then. /etc/sysconfig/networkfi# Check that networking are up. [${networking} = "No"] && exit 0# Set default Mysql-proxy configuration. Admin_user= "admin" admin_passwd= "admin" admin_lua_script= "/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua "Proxy_options="--daemon "proxy_pid=/var/run/mysql-proxy.pidproxy_user=" Mysql-proxy "# Source Mysql-proxy Configuration.if [-f/etc/sysconfig/mysql-proxy]; Then. /etc/sysconfig/mysql-proxyfiretval=0start () {echo-n $ "starting $prog:" Daemon $prog $PROXY _options--pid-file= $PR Oxy_pid--proxy-address= "$PROXY _addreSS "--user= $PROXY _user--admin-username=" $ADMIN _user "--admin-lua-script=" $ADMIN _lua_script "--admin-password=" $    Admin_password "retval=$? echo if [$RETVAL-eq 0]; Then Touch/var/lock/subsys/mysql-proxy fi}stop () {echo-n $ "stopping $prog:" Killproc-p $PROXY _pid-d    3 $prog retval=$? echo if [$RETVAL-eq 0]; Then rm-f/var/lock/subsys/mysql-proxy rm-f $PROXY _pid fi}# See how we were Called.case "$" in Star    t) start;    stop) stop;;    restart) stop start;; Condrestart|try-restart) If status-p $PROXY _pidfile $prog >&/dev/null;    Then stop start fi;;    Status) status-p $PROXY _pid $prog;; *) echo "Usage: $ Start|stop|restart|reload|status|condrestart|try-restart}" retval=1;; Esacexit $RETVAL

  Execute permissions for Mysql-proxy and add into service

  [Email protected] mysql-proxy]# chmod +x/etc/rc.d/init.d/mysql-proxy[[email protected] mysql-proxy]# chkconfig--add Mysql-proxy

  (3). Start the test script

  [[email protected] mysql-proxy]# killall mysql-proxy//First close Mysql-proxy[[email protected] mysql-proxy]# service Mysql-proxy Start//Startup Mysql-proxy

  (5). Test the connection.

  As you can see, now the connection does not specify a port, using port 3306 by default. All right, here we go. Mysql-proxy implementation of read-write separation configuration complete!

Http://www.it165.net/database/html/201404/6141.html

Realization of MARIADB read/write separation based on Mysql-proxy