MySQL read and write separation
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 proxy1@192.168.95.13 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 -h192.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 proxy1@192.168.95.13 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 proxy1@192.168.95.13 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)