Use mysql-proxy for read/write splitting of the mysql database, mysql-proxymysql

Use mysql-proxy for read/write splitting of the mysql database, mysql-proxymysql

Experimental System: CentOS 6.6 _ x86_64

Prerequisites: Firewall and selinux are both disabled.

Tutorial Description: In this experiment, there are a total of four hosts, with IP addresses allocated as topology.

Experimental software: mariadb-10.0.20 mysql-proxy-0.8.5-linux-el6-x86-64bit

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

Tutorial topology:

　　　　

I. preparations:

1. Change the host name as follows:

　　　　

2. Add the following content to the hosts file:

　　　　

3. Install mariadb on the master, slave1, and slave2:

tar xf mariadb-10.0.20-linux-x86_64.tar.gz  -C /usr/local/cd /usr/local/ln -sv mariadb-10.0.20-linux-x86_64 mysqluseradd -r mysqlmkdir -pv /mydata/datachown -R mysql.mysql /mydata/data/cd mysql/chown -R root.mysql .scripts/mysql_install_db --user=mysql --datadir=/mydata/data/cp support-files/my-large.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqldchkconfig --add mysqldchkconfig mysqld on

Ii. Configure master-slave Replication

1. Configure my. cnf on the master:

[Mysqld] server-id = 1 datadir =/mydata/datalog-bin =/mydata/data/master-binbinlog_format = ROWsync_binlog = 1 // ensure that binary logs are synchronized to the disk before each transaction is committed upper

2. Configure my. cnf on slave1:

[Mysqld] # log-bin = mysql-bin # binlog_format = mixedserver-id = 2 datadir =/mydata/datarelay_log =/mydata/data/relay-logread_only = 1sync_master_info = 1 // timely synchronization master File sync_relay_log = 1 // synchronize the relay-log File sync_relay_log_info = 1 // synchronize the relay-log-info file in time

3. Configure my. cnf on slave2:

[mysqld]#log-bin=mysql-bin#binlog_format=mixedserver-id       = 3datadir = /mydata/datarelay_log = /mydata/data/relay-logread_only = 1sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1

4. Create a copy user on the master:

service mysqld start/usr/local/mysql/bin/mysql------------------------------------------->GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'192.168.19.%' IDENTIFIED BY '123456';FLUSH PRIVILEGES;

5. view the binary log location on the master:

SHOW MASTER LOGS;

　　　　

6. perform the following operations on two slave servers:

CHANGE MASTER TO MASTER_HOST='master',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=637;START SLAVE;SHOW SLAVE STATUS\G

　　　　

Iii. Install mysql-proxy

1. In this experiment, 19.79 is the mysql-proxy server, so the software is installed on this host:

tar xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/cd /usr/local/ln -sv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxyuseradd -r mysql-proxy

2. Provide service scripts:

vim /etc/init.d/mysql-proxy--------------------------------------------------------->#!/bin/bash## mysql-proxy This script starts and stops the mysql-proxy daemon## chkconfig: - 78 30# processname: mysql-proxy# description: mysql-proxy is a proxy 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 is 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=$PROXY_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 "$1" in    start)        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: $0 {start|stop|restart|reload|status|condrestart|try-restart}"        RETVAL=1        ;;esacexit $RETVAL
<---------------------------------------------------------
chmod +x /etc/init.d/mysql-proxy
chkconfig --add mysql-proxy

3. Provide the configuration file for the service script:

Vim/etc/sysconfig/mysql-proxy administration> # Options for mysql-proxyADMIN_USER = "admin" ADMIN_PASSWORD = "admin" ADMIN_ADDRESS = "" ADMIN_LUA_SCRIPT = "/usr/local/mysql-proxy/ share/doc/mysql-proxy/admin. lua "PROXY_ADDRESS =" "PROXY_USER =" mysql-proxy "PROXY_OPTIONS =" -- daemon -- log-level = info -- log-use-syslog -- plugins = proxy -- plugins = admin -- proxy -backend-addresses = 192.168.19.66: 3306 -- proxy-read-only-backend-addresses = 192.168.19.74: 3306 -- proxy-read-only-backend-addresses = 192.168.19.76: 3306 -- proxy-lua-script =/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

// -- Daemon: Start mysql-proxy in daemon mode
// -- Proxy-backend-addresses: address and port of the mysql server that can be read and written at the backend
// -- Proxy-read-only-backend-addresses: address and port of the backend read-only mysql Server
// -- Proxy-lua-script: Lua script that completes the mysql proxy Function

4. Provide the admin. lua file:

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua------------------------------------------------------------------------------>--[[ $%BEGINLICENSE%$ Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA $%ENDLICENSE%$ --]]function set_error(errmsg) proxy.response = {type = proxy.MYSQLD_PACKET_ERR,errmsg = errmsg or "error"}endfunction read_query(packet)if packet:byte() ~= proxy.COM_QUERY thenset_error("[admin] we only handle text-based queries (COM_QUERY)")return proxy.PROXY_SEND_RESULTendlocal query = packet:sub(2)local rows = { }local fields = { }if query:lower() == "select * from backends" thenfields = { { 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 dolocal states = {"unknown","up","down"}local types = {"unknown","rw","ro"}local b = proxy.global.backends[i]rows[#rows + 1] = {i,b.dst.name,          -- configured backend addressstates[b.state + 1], -- the C-id is pushed down starting at 0types[b.type + 1],   -- the C-id is pushed down starting at 0b.uuid,              -- the MySQL Server's UUID if it is managedb.connected_clients  -- currently connected clients}endelseif query:lower() == "select * from help" thenfields = { { 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" }elseset_error("use 'SELECT * FROM help' to see the supported commands")return proxy.PROXY_SEND_RESULTendproxy.response = {type = proxy.MYSQLD_PACKET_OK,resultset = {fields = fields,rows = rows}}return proxy.PROXY_SEND_RESULTend

5. Edit two values in the rw-splitting.lua file to make the experiment results more visible:

Vim/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua -------------------------------------------------------------------------> if not proxy. global. config. rwsplit then proxy. global. config. rwsplit = {min_idle_connections = 1, // The default value is 4 max_idle_connections = 1, // The default value is 8 is_debug = false} end.
// Mysql-proxy checks the client connection. When the connection does not exceed the preset value of min_idle_connections, read/write splitting is not performed, that is, the query operation will occur to the Master.

5. Start mysql-proxy:

Service mysql-proxy startss-tnlp // view the port

　　　　

6. connection test:

Yum-y install mysql // if there is no mysql client, execute this step mysql-uadmin-padmin-h192.168.19.79 -- port = 4041 ------------------------------------------------------> SELECT * FROM backends; + ------------- + response + --------- + ------ + response + | backend_ndx | address | state | type | uuid | connected_clients | + ------------- + -------------------- + --------- + ------ + response + | 1 | 192.168.19.66: 3306 | unknown | rw | NULL | 0 | 2 | 192.168.19.74: 3306 | unknown | ro | NULL | 0 | 3 | 192.168.19.76: 3306 | unknown | ro | NULL | 0 | + ------------- + -------------------- + --------- + ------ + --------------------- +

Iv. read/write splitting test:

1. Create a test user on the master:

GRANT ALL ON *.* TO 'jason'@'192.168.19.%' IDENTIFIED BY '123456';FLUSH PRIVILEGES;

2. Capture packets on the three mariadb servers respectively:

Master:

Tcpdump-I eth0-nn-XX ip dst 192.168.19.66 and tcp dst port 3306 // The destination is 19.66 and the port is 3306

Slave1:

tcpdump -i eth0 -nn -XX ip dst 192.168.19.74 and tcp dst port 3306

Slave2:

tcpdump -i eth0 -nn -XX ip dst 192.168.19.76 and tcp dst port 3306

3. perform database operations on mysql-proxy:

Mysql-ujason-p123456-h192.168.19.79 -------------------------------------------------> create database hello;
USE mysql;
SELECT * FROM user; // you can use an additional host to execute multiple times.

Packet capture information on the master:

　　　　

Packet capture information on slave:

　　　　

　　　　

4. view the status and operate on the proxy. You can see that all the statuses are up:

mysql -uadmin -padmin -h192.168.19.79 --port=4041------------------------------------------------------------->SELECT * FROM backends;+-------------+--------------------+-------+------+------+-------------------+| backend_ndx | address            | state | type | uuid | connected_clients |+-------------+--------------------+-------+------+------+-------------------+|           1 | 192.168.19.66:3306 | up    | rw   | NULL |                 0 ||           2 | 192.168.19.74:3306 | up    | ro   | NULL |                 0 ||           3 | 192.168.19.76:3306 | up    | ro   | NULL |                 0 |+-------------+--------------------+-------+------+------+-------------------+

5. Expand lab

1. Install httpd and php on the proxy:

yum install httpd php php-mysqlservice httpd start

2. Enable httpd to support index. php homepage, and then add the wordpress page file to create and install the wordpress database:

　　　　

3. After the installation, modify the wordpress configuration file and change the master Address to the proxy address:

vim /var/www/html/wp-config.php

　　　　

4. Access the test and capture packets:

Master:

　　　　

Slave:

　　　　

　　　　

Now, the read/write splitting experiment has been demonstrated. Thank you! If you have any questions, contact me at QQ: 82800452.