MySQL read/write splitting technology, mysql read/write splitting

MySQL read/write splitting technology, mysql read/write splitting
1. Introduction

MySQL is widely used today. With the increase of users and the increase of data volume, high concurrency comes along with it. However, we have many ways to relieve the pressure on the database. Distributed databases, Server Load balancer, read/write splitting, and adding cache servers. Here we will use the read/write splitting technology to relieve the pressure on the database.

There are many ways to implement read/write splitting. Here we will use the mysql-proxy intermediate software. This software contains a lua file for read/write splitting. This is also a required file for implementing read/write splitting using mysql-proxy. It needs to be parsed by the lua parser. Therefore, we need to install a lua parser.

 

2. Basic Environment

Three linux Virtual Hosts

Linux CentOS6.6 and 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

For details, see mysql master-slave replication and master-master replication.

Http://www.cnblogs.com/phpstudy2015-6/p/6485819.html#_label2

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

Step 1:

Create a MySQL user that can log on to the host 192.168.95.12 on 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;

 

Step 2:

View the binary file name and location of the 192.168.95.11MySQL Server

mysql>SHOW MASTER STATUS;

 

Step 3:

Inform the binary file name and location

Run the following command 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;

Step 4:

In 192.168.95.12

Mysql> slave start; # enable replication

Mysql> show slave status \ G # Check whether master-SLAVE replication is configured successfully

Master-slave replication configuration successful!

(Note: The above Relicate_Do_DB: aa indicates that the master-slave replication is only for the database aa [THIS is what I set before and it has not been changed]. I will not talk about this here, if you want to learn about medical, you can refer to the article http://www.cnblogs.com/phpstudy2015-6/p/6485819.html#_label7)

 

4. MySQL read/write splitting Configuration

Baidu cloud download: Link: http://pan.baidu.com/s/1slTl18L password: 9j0m

4.1 Install lua

Download: http://www.lua.org/download.html

Lua is a small scripting language. Lua is compiled by standard C. The code is concise and elegant, and can be compiled and run on almost all operating systems and platforms.

A complete Lua interpreter is only 200 k. Currently, Lua is the fastest in all script engines. All of these determine that Lua is the best choice for embedded scripts.

1) Install luaMany software packages are required.

Run rpm-qa | grep name to check whether the following software is installed:

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

If the related software package is missing, you can install it online using yum-y install, or directly find it from the system installation disc and install it using rpm-ivh. (I usually find the direct rpm installation in the system CD software library. If some cannot be found, download it online and then pass it to linux through ftp before installation)

2). Compile and install lua after the dependent software is installed.

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

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 found it directly in the CD software library during installation, direct rpm installation)

4.2 Install mysql-proxy

　　1) check whether the linux version is 32-bit or 64-bit.

View Linux kernel version

# Cat/etc/issue

View the linux version

# Cat/proc/version

　　2) download files by system digits (64-bit files linked to Baidu cloud above)

　　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

Installed successfully

 

5. MySQL read/write Splitting Test 1) Modify rw-splitting.lua File

Modify the default connection and perform a quick test. If this parameter is not modified, enable read/write splitting only when the number of connections reaches 4.

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

# Vi rw-splitting.lua

2) , After modification, 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 &

 

3) Create a database connection user for read/write splitting

User name: 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 master-slave replication is enabled, this user is created in mysql on hosts 11 and 12]

4) , Test login account proxy1@192.168.95.13 Add data

You can use any ip Client to log on to this account.

Log on to 192.168.95.13:

#./Mysql-u proxy1-P4040-h192.168.95.13-p

View the results in two mysql instances: consistent

The result shows that the account is used

(Ps: id is self-increasing. The configuration file was changed when the master node was copied. If the configuration file has not been changed, it will be used first)

5) Disable 12 mysql Slave Replication

Mysql> stop slave;

6) Proof of write Separation

Use the proxy1@192.168.95.13 account to open multiple clients for data insertion

Two data entries are inserted on each client:

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

 

View:

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

Primary database:

Slave database:

The result shows that the inserted data exists in the master database but does not exist in the slave database. Therefore, it indicates that the write can be separated.

　　 7) Verify read Separation

Log onto mysql with a proxy1@192.168.95.13 account to view 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 that read separation.

 

6. Suggestions

You can create a mysql-proxy service management script to enable and manage mysql-proxy.

The following management script is only applicable to the above installation path

[This management script must be modified according to the installation path]

 1 #!/bin/sh 2  3 # 4 # mysql-proxy This script starts and stops the mysql-proxy daemon 5 # 6 # chkconfig: - 78 30 7 # processname: mysql-proxy 8 # description: mysql-proxy is a proxy daemon to mysql 9 10 # Source function library.11 . /etc/rc.d/init.d/functions12 13 #PROXY_PATH=/usr/local/bin14 PROXY_PATH=/usr/local/mysql-proxy/bin15 16 prog="mysql-proxy"17 18 # Source networking configuration.19 . /etc/sysconfig/network20 21 # Check that networking is up.22 [ ${NETWORKING} = "no" ] && exit 023 24 # Set default mysql-proxy configuration.25 #PROXY_OPTIONS="--daemon"26 27 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"28 29 PROXY_PID=/usr/local/mysql-proxy/run/mysql-proxy.pid30 31 # Source mysql-proxy configuration.32 if [ -f /etc/sysconfig/mysql-proxy ]; then33         . /etc/sysconfig/mysql-proxy34 fi35 36 PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH37 # By default it's all good38 RETVAL=039 40 # See how we were called.41 case "$1" in42   start)43         # Start daemon.44         echo -n $"Starting $prog: "45         $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.log46         RETVAL=$?47         echo48         if [ $RETVAL = 0 ]; then49                 touch /var/lock/subsys/mysql-proxy]50                 echo "ok"51         fi52        ;;53   stop)54         # Stop daemons.55         echo -n $"Stopping $prog: "56         killproc $prog57         RETVAL=$?58         echo59         if [ $RETVAL = 0 ]; then60                 rm -f /var/lock/subsys/mysql-proxy61                 rm -f $PROXY_PID62         fi63        ;;64   restart)65         $0 stop66         sleep 367         $0 start68        ;;69   condrestart)70        [ -e /var/lock/subsys/mysql-proxy ] && $0 restart71       ;;72   status)73         status mysql-proxy74         RETVAL=$?75        ;;76   *)77         echo "Usage: $0 {start|stop|restart|status|condrestart}"78         RETVAL=179        ;;80 esac81 exit $RETVAL

 

# Create a directory for the execution permission # chmod + x/usr/local/mysql-proxy/init. d/mysql-proxy # mkdir/usr/local/mysql-proxy/run # mkdir/usr/local/mysql-proxy/log

 

Some related parameters:

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

-- Proxy-read-only-backend-addresses = 192.168.95.12: 3306 // defines the backend read-only slave server address.
-- Proxy-backend-addresses = 192.168.95.11: 3306 // define the backend master server address
-- Proxy-lua-script =/usr/local/mysql-proxy/rw-splitting.lua // define lua read/write splitting script path

PROXY_PID =/usr/local/mysql-proxy/run/mysql-proxy.pid // define mysql-proxyPID file path

-- Daemon // defines to start in daemon mode
-- Keepalive // enables the process to automatically recover after an exception is disabled. [this parameter is not added to the management script above]
-- User = root // start the service as a root user
-- Log-level = debug // defines the log level from high to low (error | warning | info | message | debug)
-- Log-file =/usr/local/mysql-proxy/log/mysql-proxy.log // define the log file path

 

(The above are some of your own opinions and conclusions. If you have any shortcomings or errors, please point them out)

Author: The leaf goes with the wind

Statement: The above only represents the point of view or conclusion I have summarized at a certain time in my work and study. When reprinting, please provide the original article link clearly on the Article Page