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.