MySQL master-slave replication and read/write separation under CentOS

Master-slave replication

Build a MySQL server environment where the primary server IP address is 192.168.100.9, and the server IP address is 192.168. 100.5.

Start remote permissions on the primary server, and the server can telnet to the primary server. Authorization to Slave server

mysql> GRANT REPLICATION SLAVE on * * to ' first ' @ ' 192.168.100.5 ' identified by ' first '//username first, password first, From server IP192.168.100.5

Modify the configuration file for the master server. On the primary server you need to start the binary log (Log-bin = Master-bin,log-bin-index = Master-bin.index), from the server need to enable the relay log (Relay-log = Relay-log, Relay-log-index = relay-log.index) and Server-id are different, the master server is set to 1 and the server is set to 2.

To configure the master node:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/38/wKioL1V2vVrQ3Po4AAFgzN3gMpE701.jpg "title=" M.png " alt= "Wkiol1v2vvrq3po4aafgzn3gmpe701.jpg"/>

To configure the slave node:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/3C/wKiom1V2u9eBTvVTAAE2kDLPUCI304.jpg "title=" S.png " alt= "Wkiom1v2u9ebtvvtaae2kdlpuci304.jpg"/>

Configuration on the primary server

View data status on the primary server

Mysql> Show master status;

Configure the slave server

mysql> Change Master to master_host= ' 192.168.100.9 ', master_user= ' xwd ', master_password= ' xwd ', master_log_file= ' Mysql.000016 ', master_log_pos=257;

Start slave

Mysql>start slave;

MySQL > show slave status;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6E/38/wKioL1V2vH7gR3mQAADoucpyS0o890.jpg "title=" Qq20150609180601.png "alt=" Wkiol1v2vh7gr3mqaadoucpys0o890.jpg "/>

This is the success of the master-slave replication. And it starts copying from the current log.

Static and dynamic separation

Suitable for dynamic and static separation software has mysql-proxy, amoeba, here mainly introduces the Mysql-proxy realization of Read and write separation.

3.4 Installation Configuration Mysql-proxy
Mysql-proxy can be obtained from the following URLs:
http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/

It is recommended to use a compiled binary version, because the latest version of the Mysql-proxy has high requirements for automake,glib and libevent versions when compiling with the source package, which are the basic packages of the system and are not recommended for forced updates.
And these compiled binaries are all in a unified directory after decompression, so it is recommended to select the following versions:
32-bit RHEL5 platform:
Http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz
64-bit RHEL5 platform:
Http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-64bit.tar.gz

The test platform is RHEL5 32-bit, so select a 32-bit package
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz

Tar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz
MV Mysql-proxy-0.8.1-linux-rhel5-x86-32bit/opt/mysql-proxy

Create a Mysql-proxy service management script
mkdir/opt/mysql-proxy/init.d/

Vim Mysql-proxy

#!/bin/sh
#
# Mysql-proxy This script starts and stops the Mysql-proxy daemon
#
# Chkconfig:-78 30
# Processname:mysql-proxy
# Description:mysql-proxy is a proxy daemon to MySQL

# Source function library.
. /etc/rc.d/init.d/functions

#PROXY_PATH =/usr/local/bin
Proxy_path=/opt/mysql-proxy/bin

Prog= "Mysql-proxy"

# Source Networking configuration.
. /etc/sysconfig/network

# Check that networking are up.
[${networking} = "No"] && exit 0

# Set Default Mysql-proxy configuration.
#PROXY_OPTIONS = "--daemon"
proxy_options= "--admin-username=root--admin-password=password--proxy-read-only-backend-addresses= 192.168.100.9:3306--proxy-backend-addresses=192.168.100.5:3306--admin-lua-script=/opt/mysql-proxy/lib/ Mysql-proxy/lua/admin.lua--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua "
Proxy_pid=/opt/mysql-proxy/run/mysql-proxy.pid

# Source Mysql-proxy configuration.
if [-f/etc/sysconfig/mysql-proxy]; Then
. /etc/sysconfig/mysql-proxy
Fi

Path= $PATH:/usr/bin:/usr/local/bin: $PROXY _path

# By default it's all good
Retval=0

# See how we were called.
Case "$" in
Start
# Start Daemon.
Echo-n $ "Starting $prog:"
$NICELEVEL $PROXY _path/mysql-proxy $PROXY _options--daemon--pid-file= $PROXY _pid--user=mysql--log-level=warning-- Log-file=/opt/mysql-proxy/log/mysql-proxy.log
Retval=$?
Echo
if [$RETVAL = 0]; Then
Touch/var/lock/subsys/mysql-proxy
Fi
;;
# Stop Daemons.
Echo-n $ "Stopping $prog:"
Killproc $prog
Retval=$?
Echo
if [$RETVAL = 0]; Then
Rm-f/var/lock/subsys/mysql-proxy
Rm-f $PROXY _pid
Fi
;;
Restart
$ stop
Sleep 3
$ start
;;
Condrestart)
[-e/var/lock/subsys/mysql-proxy] && restart
;;
Status
Status Mysql-proxy
Retval=$?
;;
*)
echo "Usage: $ {Start|stop|restart|status|condrestart}"
Retval=1
;;
Esac

Exit $RETVAL
Detailed Script parameters:
==============================================
Proxy_path=/opt/mysql-proxy/bin//define MYSQL-PROXY service binary file path

proxy_options= "--admin-username=root \//define internal Management Server account
--admin-password=password \//define internal Management Server password
--proxy-read-only-backend-addresses=192.168.10.131:3306 \//define backend read-only from server address
--proxy-backend-addresses=192.168.10.130:3306 \//define back-end primary server address
--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua \//define LUA Management script path
--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua "\//define LUA read-write detach script path

Proxy_pid=/opt/mysql-proxy/run/mysql-proxy.pid//define Mysql-proxy PID file path

$NICELEVEL $PROXY _path/mysql-proxy $PROXY _options \
--daemon \//definition starts in daemon mode
--keepalive \//causes the process to automatically recover after an abnormal shutdown
--pid-file= $PROXY _pid \//define Mysql-proxy PID file path
--user=mysql \//Start the service as a MySQL user
--log-level=warning \//define log log level, from high to Low (Error|warning|info|message|debug)
--log-file=/opt/mysql-proxy/log/mysql-proxy.log//define log log file path
==============================================

CP mysql-proxy/opt/mysql-proxy/init.d/
chmod +x/opt/mysql-proxy/init.d/mysql-proxy

Mkdir/opt/mysql-proxy/run
Mkdir/opt/mysql-proxy/log

Mkdir/opt/mysql-proxy/scripts

Configuring and using the Rw-splitting.lua read-write detach script
The latest script we can get from the latest Mysql-proxy source package
Cd/opt/install

wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gz

Tar xzvf mysql-proxy-0.8.1.tar.gz
CD mysql-proxy-0.8.1
CP lib/rw-splitting.lua/opt/mysql-proxy/scripts

Modify read-write detach script Rw-splitting.lua
Modify the default connection, do a quick test, do not change the number of connections to reach 4 o'clock to enable read-write separation
Vim/opt/mysql-proxy/scripts/rw-splitting.lua
--Connection pool
If not Proxy.global.config.rwsplit then
Proxy.global.config.rwsplit = {
Min_idle_connections = 1,//default is 4
Max_idle_connections = 1,//default is 8
Is_debug = False
}
End
=============================

After the modification is complete, start mysql-proxy
/opt/mysql-proxy/init.d/mysql-proxy start

This article from "Little Space" blog, declined reprint!

MySQL master-slave replication and read/write separation under CentOS