MySQLProxy Master/Slave read/write splitting

Source: Internet
Author: User
I. Install MySQL, host: Login # cdmysql-5.5.9 # useraddmysql # wgetwww. cmake

1. Install MySQL, host: 192.168.1.222/223/224 # wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.9.tar.gz/from/http://mirror.services.wisc.edu/mysql/# tar zxf mysql-5.5.9.tar.gz # cd mysql-5.5.9 # useradd mysql # wget http://www.cmake

1. Install MySQL. HOST: 192.168.1.222/223/224

# wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.9.tar.gz/from/http://mirror.services.wisc.edu/mysql/# tar zxf mysql-5.5.9.tar.gz# cd mysql-5.5.9# useradd mysql# wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz# tar xzf cmake-2.8.4.tar.gz# cd cmake-2.8.4# ./configure && make && make install# cmake . \-DCMAKE_INSTALL_PREFIX=/opt/module/mysql/ \-DMYSQL_DATADIR=/opt/data/mysql/ \-DMYSQL_UNIX_ADDR=/opt/data/mysql/mysql.sock \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DENABLED_LOCAL_INFILE=1 \-DMYSQL_TCP_PORT=3306 \-DEXTRA_CHARSETS=gb2312,gbk,big5,utf8,ascii,latin1 \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DMYSQL_USER=mysql \-DWITH_DEBUG=0# make && make install# cp support-files/my-medium.cnf /etc/my.cnf# cp support-files/mysql.server /etc/init.d/mysqld# chmod 755 /etc/init.d/mysqld# chkconfig --add mysqld# chkconfig mysqld on# chown mysql:mysql /etc/my.cnf# chown -R mysql:mysql /opt/module/mysql/ /opt/data/mysql/# vi /etc/my.cnf==========[mysqld]datadir = /opt/data/mysql==========# su – mysql$ cd /opt/module/mysql$ cp scripts/mysql_install_db .$ ./mysql_install_db –user=mysql# service mysqld start

Ii. Install Lua

# wget http://www.lua.org/ftp/lua-5.1.4.tar.gz# tar xzf lua-5.1.4.tar.gz# cd lua-5.1.4# make# make linux INSTALL_TOP= /usr/local/lua# make install

3. Install MySQL Proxy, host: 192.168.1.20.( Note: it is better to install MySQL Proxy on a separate machine)
A) glib

# wget http://ftp.gnome.org/pub/gnome/sources/glib/2.28/glib-2.28.4.tar.bz2# tar jxf glib-2.28.4.tar.bz2# cd glib-2.28.4# ./configure --prefix=/opt/module/glib2# make && make install# echo "/opt/module/glib2/lib" >> /etc/ld.so.conf# ldconfig

B) pkg-config

# wget http://pkg-config.freedesktop.org/releases/pkgconfig-0.18.tar.gz# tar xzf pkgconfig-0.18.tar.gz# cd pkgconfig-0.18# ./configure && make && make install

C) libevent

# wget http://monkey.org/%7Eprovos/libevent-2.0.10-stable.tar.gz# tar zxf libevent-2.0.10-stable.tar.gz# cd libevent-2.0.10-stable# ./configure --prefix=/opt/module/libevent# make; make install

D) mysql-proxy

# Wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gz/from/http://mysql.he.net/# Tar xzf mysql-proxy-0.8.1.tar.gz # cd mysql-proxy-0.8.1 # export LUA_CFLAGS = "-I/usr/local/include" LUA_LIBS = "-L/usr/local/lib-llua-ldl" LDFLAGS = "- lm "export PKG_CONFIG_PATH ="/usr/lib/pkgconfig/"export GLIB_CFLAGS ="-I/opt/module/glib2/include/glib-2.0-I/opt/module/glib2 /lib/glib-2.0/include "GLIB_LIBS ="-L/opt/module/glib2/lib-lglib-2.0 "export GMODULE_CFLAGS ="-I/opt/module/glib2/include" GMODULE_LIB S = "-L/opt/module/glib2/lib" export GTHREAD_CFLAGS = "-I/opt/module/glib2/include" export GTHREAD_LIBS = "-L/opt/module/ glib2/lib "export CPPFLAGS =" $ CPPFLAGS-I/opt/module/libevent/include "export CFLAGS =" $ CFLAGS-I/opt/module/libevent/include "export LDFLAGS = "$ LDFLAGS-L/opt/module/libevent/lib-lm" export LIBS = "/opt/module/glib2/lib/libgthread-2.0.so/opt/module/glib2/lib/ libgmodule-2.0.so "#. /configure -- With-mysql = "/opt/module/mysql" -- prefix = "/opt/module/mysql-proxy" to check whether the installation is successful: #/opt/module/mysql-proxy/bin/mysql-proxy -- help-all | less Configuration: # vi/etc/mysql-proxy.cnf ============================= [mysql-proxy] admin-username = mysqlproxyadm Master/Slave databases are required database user admin-password = 123456 daemon = truekeepalive = truelog-file =/opt/module/mysql-proxy/loglog-level = debugproxy-address = 192.168.1.20: 4040proxy-backend-address Es = 192.168.1.223: 3306 master proxy-read-only-backend-addresses = 192.168.1.222: 3306,192.168 .1.ses: 3306 from proxy-lua-script =/opt/module/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.luaadmin-lua-script =/opt/module/mysql-proxy/lib/mysql- proxy/lua/admin. lua ============================#chmod 0660/etc/mysql-proxy.cnf make Startup Script: # vi/etc/init. d/mysql-proxy ============================== #! /Bin/shexport LUA_PATH = "/opt/module/mysql-proxy/lib/mysql-proxy/lua /?. Lua "mode = $1; if [-z $ mode]; thenmode =" start "ficase $ mode instart) /opt/module/mysql-proxy/bin/mysql-proxy -- defaults-file =/etc/mysql-proxy.cnf>/opt/module/mysql-proxy/log &; stop) killall-9 mysql-proxy; *) echo "Usage: $0 (start | stop)" exit 1; esacexit 0; ================================#chmod + x/etc/init. d/mysql-proxy # cp x/mysql-proxy-0.8.1/lib/rw-splitting.lua/opt/module/mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua modify rw-splitting.lua # vi/opt/module /mysql-proxy/lib/mysql-proxy/lua/rw-splitting.lua ============ min_idle_connections = 1, max_idle_connections = 2 ============= start mysql-proxy #/etc/init. d/mysql-proxy start

4. Configure the master-slave database (master database A is on 192.168.1.223, slave database B is on 192.168.1.222, and slave database C is on 192.168.1.20)
1. log on to the master and add slave users.

Grant replication slave on *. * TO 'slave _ a' @ '192. 168.1.222 'identified BY '000000' grant replication slave on *. * TO 'slave _ B '@' 192. 168.1.20.'identified BY '123' Note: Open the master port, allow slave remote connection # vi/etc/sysconfig/iptables ========================== add the following before icmp-host-prohibited: -A RH-Firewall-1-INPUT-p tcp-m state -- state NEW-m tcp -- dport 3306-j ACCEPT ================== ========## service iptables restart

2. Modify the MySQL configuration file
A) master

# Vi/etc/my. cnf =============== [mysqld] server-id = 1log_bin = mysql-binread-only = 0binlog-do-db = test # write multiple rows of binlog- ignore-db = mysql # write multiple rows of binlog-ignore-db = information_schema ==============

B) slave, configure each slave

        # vi /etc/my.cnf[mysqld]server-id = 2log_bin = mysql-binread-only = 1replicate-do-db = testreplicate-ignore-db = mysqlreplicate-ignore-db = information_schema=============        

3. Run the following command after starting the slave ):

Change master to MASTER_HOST = '2017. 168.1.223 ', MASTER_USER = 'slave _ a', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 123456; get the MASTER_LOG_FILE and MASTER_LOG_POS values slave start; show slave status \ G; by SHOW master STATUS on the MASTER to check whether the master is synchronized.

4. log on to the master, add, delete, and modify data, and check whether the slave is synchronized
V. Test
1. Simple Test
Create an account mysqlproxyadm on both the master and slave Databases

# Grant select on *. * TO 'mysqlproxyadm '@' 192. 168.1.20.'identified BY '20140901' # grant select, INSERT, UPDATE, DELETE, CREATE, DROP, alter on *. * TO 'mysqlproxyadm '@' 192. 168.1.20.'identified BY '20140901' # grant select on *. * TO 'mysqlproxyadm '@' 192. 168.1.20.'identified BY '123456' perform the read/write operation to test whether the read/write splitting is performed. (To observe the log file of the Master/Slave database, open my. cnf log = log-filename ). /mysql-h192.168.1.20.- umysqlproxyadm-p123456-P4040

Original article address: MySQL Proxy Master/Slave read/write splitting. Thank you for sharing it with the original author.

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.