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

Source: Internet
Author: User
Tags wordpress database

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.