Cara menggunakan mysql router read/write splitting

1. Introduction

Today’s MySQL is widely used. As users increase and the amount of data increases, high concurrency follows. However, we have many ways to ease the pressure on the database. Distributed database, load balancing, read and write separation, adding cache servers, and more. Here we will use the progress of reading and writing separation technology to ease the pressure on the database.

There are many ways to implement the technology of reading and writing separation. Here we will use the intermediate software mysql-proxy. This software contains a read and write separated lua file, which is also a file we must use to achieve read and write separation using mysql-proxy, it requires a lua parser for parsing. So we also need to install a lua parser.

2. The basic environment

Three linux virtual hosts

Linux version CentOS6.6, MySQL 5.5

mysql-proxy-0.8.5

lua-5.1.4

IP: 192.168.95.11 (write), 192.168.95.12 (read), 192.168.95.13 (mysql-proxy)

3. Configure master-slave replication

A rough introduction to the configuration of the master-slave replication of the database:

First step:

Create a MySQL user that can log in to the 192.168.95.12 host in 192.168.95.11.

User: mysql12

Password: mysql12

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘mysql12’@’192.168.95.12’ IDENTIFIED BY ‘mysql12’;
mysql>FLUSH PRIVILEGES;

The second step:

View 192.168.95.11 MySQL server binary file name and location

mysql>SHOW MASTER STATUS;

Third step:

Tell the binary file name and location

Executed in 192.168.95.12:

mysql> change master to
    -> master_host='192.168.95.11',
    -> master_user='mysql12',
    -> master_password='mysql12',
    -> master_log_file='mysql-bin.000124',
    -> master_log_pos=586;

The fourth step:

In 192.168.95.12

mysql>SLAVE START; #Open copymysql>SHOW SLAVE STATUS\G #View whether the master-slave replication is configured successfully

Master-slave replication configuration is successful!

Note: The above Relicate_Do_DB: aa means that the master-slave copy is only for the database aa [this is what I have set before, I have not changed it], I will not talk about this here.

4. MySQL read and write separation configuration

4.1. Install lua

Official website download: http://www.lua.org/download.html

Lua is a small scripting language. Lua is written in standard C. The code is simple and beautiful, and can be compiled and run on almost all operating systems and platforms.

A complete Lua interpreter is only 200k, and Lua is the fastest in all current scripting engines. All of this determines that Lua is the best choice for embedded scripting.

1) Installing lua depends on many packages.

You can check if the following software is installed by rpm -qa | grep name:

gcc*、gcc-c++*、autoconf*、automake*、zlib*、libxml*、ncurses-devel*、libmcrypt*、libtool*、flex*、pkgconfig*、libevent*、glib*

If the relevant package is missing, you can install it online via yum -y install, or directly from the system installation CD and install it via rpm -ivh. (My words are usually found directly in the system CD software library directly rpm installation, some can not be found, then downloaded online and then passed to ftp in ftp and then installed)

2) After the software is installed, compile and install lua

The read-write separation of MySQL-Proxy is mainly implemented by the rw-splitting.lua script, so you need to install lua.

Official website download: http://www.lua.org/download.html (download source package)

# wget http://www.lua.org/ftp/lua-5.1.4.tar.gz   
# tar zxvf lua-5.1.4.tar.gz 
# cd lua-5.1.4   
# make linux 
# make install 
# export LUA_CFLAGS="-I/usr/local/include" LUA_LIBS="-L/usr/local/lib -llua -ldl" LDFLAGS="-lm" 
(I installed it directly in the CD software library, directly rpm installation)

4.2. Install mysql-proxy

1) First check the linux version to confirm whether it is 32-bit or 64-system

View the Linux kernel version

# cat /etc/issue

View linux version

# cat /proc/version

2) Download by system digits

3) Installation

# tar –zxvf mysql-proxy-0.8.5- linux-rhel5-x86-64bit.tar.gz
# mkdir /usr/local/mysql-proxy
# cp ./ mysql-proxy-0.8.5-linux-rhel5-x86-64bit/* /usr/local/mysql-proxy
# cd /usr/local/mysql-proxy

Successful installation

5. MySQL read and write separation test

1. Modify the rw-splitting.lua file

Modify the default connection and perform a quick test. If you do not modify it, you must enable the read/write separation when the number of connections is 4.

#cp /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ./

# vi rw-splitting.lua

2. After the modification is complete, start mysql-proxy

# cd /usr/local/mysql/bin
# ./mysql-proxy --proxy-read-only-backend-addresses=192.168.95.12:3306 --proxy-backend-addresses=192.168.95.11:3306 --proxy-lua-script=/usr/local/mysql-proxy/rw-splitting.lua &

parameter:

— proxy-read-only-backend-addresses #read-only server address (ip)

— proxy-backend-addresses #server address (primary server)

— proxy-lua-script #luaScript Road

& # indicates background execution

3. Create a database connection user for read and write separation

Username: proxy1

Password: 321

mysql>grant all on *.* to 'proxy1'@'192.168.95.13' identified by '321';
mysql>use aa;
mysql>create table tab1(id int auto_increment,name varchar(32) not null,primary key(id));

[Because the master-slave replication has been enabled, this user is created in the 11 and 12 hosts mysql]

4. Test login account to add data

You can use any ip client to login to this account.

Log in at 192.168.95.13:

# ./mysql -u proxy1 -P4040 -h292.168.95.13 –p

View results in two mysql: consistent

The result shows: account usage

(ps: id is self-growth. Before the high master and master replication, the configuration file was changed. If it has not been changed, it will be used first.)

5. Close 12mysql copy

mysql> stop slave;

6. Proof of writing separation

Open multiple clients to open data using account

Open three mysql clients to insert 2 pieces of data:

mysql> insert into tab1 (name) values('stop_slave11111');….mysql> insert into tab1 (name) values('stop_slave6666’);

View:

Log in 11mysql and 12mysql respectively to view the data in aa.tab1

Primary database:

From the database:

The result shows that the inserted data exists with the primary database, but not from the database, so the proof write can be separated.

7. Prove reading separation

Log in to mysql using the account to view the data in aa.tab1.

mysql>use aa;
mysql>select*from tab1;

The results show that only the data from the database, combined with the above test, can prove read separation.

6. Suggestions

In order to facilitate the startup and management of mysql-proxy, you can create a mysql-proxy service management script.

The following management script is only suitable for the installation path location I gave above.

[This management script needs to be modified according to its own installation path to be used]

#!/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=/usr/local/mysql-proxy/binprog="mysql-proxy"# Source networking configuration.
. /etc/sysconfig/network# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0# Set default mysql-proxy configuration.
#PROXY_OPTIONS="--daemon"PROXY_OPTIONS="--proxy-read-only-backend-addresses=192.168.95.12:3306 --proxy-backend-addresses=192.168.95.11:3306 --proxy-lua-script=/usr/local/mysql-proxy/rw-splitting.lua"PROXY_PID=/usr/local/mysql-proxy/run/mysql-proxy.pid# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
        . /etc/sysconfig/mysql-proxy
fiPATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH
# By default it's all good
RETVAL=0# See how we were called.
case "$1" in
  start)
        # Start daemon.
        echo -n $"Starting $prog: "
        $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=root --log-level=debug --log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
        RETVAL=$?
        echo
        if [ $RETVAL = 0 ]; then
                touch /var/lock/subsys/mysql-proxy]
                echo "ok"
        fi
       ;;
  stop)
        # 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)
        $0 stop
        sleep 3
        $0 start
       ;;
  condrestart)
       [ -e /var/lock/subsys/mysql-proxy ] && $0 restart
      ;;
  status)
        status mysql-proxy
        RETVAL=$?
       ;;
  *)
        echo "Usage: $0 {start|stop|restart|status|condrestart}"
        RETVAL=1
       ;;
esac
exit $RETVAL#---I put the mysql-proxy service management script in the /usr/local/mysql-proxy/init.d/ folder 
#--- give execute permission, create the corresponding directory 
# chmod +x /usr/local /mysql-proxy/init.d/mysql-proxy 
# mkdir /usr/local/mysql-proxy/run 
# mkdir /usr/local/mysql-proxy/log #cd /usr/local/mysql-proxy/init.d / 
#---Start mysql-proxy 
#./mysql-proxy start 
#---Stop mysql-proxy 
#./mysql-proxy stop 
#--- Restart mysql-proxy 
#./mysql-proxy restart

Some related parameters:

PROXY_PATH=/usr/local/mysql-proxy/bin //Define the mysql-proxy service binary path

— proxy-read-only-backend- addresses = 192.168.95.12: 3306 // define backend server address from the read-only
— proxy-backend-addresses = 192.168.95.11: 3306 // primary definition of backend server address
- Proxy-lua-script=/usr/local/mysql-proxy/rw-splitting.lua //Define the path to the lua read and write separation script

PROXY_PID=/usr/local/mysql-proxy/run/mysql-proxy.pid //Define the mysql-proxy PID file path

— daemon / / start to start in the daemon mode —
keepalive / / make the process automatically restored after the exception is closed [the above management script does not add this parameter ]
— user = root / / start the service as the root user
- -log-level=debug //Define the log log level, from high to low (error|warning|info|message|debug)
— log-file=/usr/local/mysql-proxy/log/mysql-proxy .log / / define the log log file path

(The above are some of my own insights and conclusions. If there are any shortcomings or mistakes, please point out)