Installing Mysql-proxy for MySQL read-write separation

A. Environment Description

Operating system version:CentOS 7.2

Primary server:192.168.0.72 node2

from server:192.168.0.73 node3

Dispatch Server Mysql-proxy:192.168.0.71 node1

two. configuring mysql master-slave 2.1 Installing mysql

Both Node2 and node3 need to be installed

[email protected] ~]$ sudo yum-y install Mariadb-server

2.2 Modifying a configuration file

Master configuration file :

Skip_name_resolve

Innodb_file_per_table

server_id = 1

Log_bin =/var/lib/mysql/bin_log

Slave configuration file :

Skip_name_resolve

Innodb_file_per_table

server_id = 2

Log_bin =/var/lib/mysql/bin_log

Read_Only

Note: The master server must have the binary log turned on because master-slave replication relies on master. And the server_id of the master-slave server must be different.

2.3 Start service

Node2 and node3 are going to start the service .

[[email protected] ~]$ sudo systemctl start mariadb

2.4 Configuring the master-slave server

on the Master server

assign the slave host access Master permissions account:

[Email protected] ~]$ Mysql-uroot

MariaDB [(None)]> GRANT REPLICATION CLIENT, REPLICATION SLAVE on * * to ' repl_user ' @ ' 192.168.0.% ' identified by ' Repl_ Pass ';

View The binary log location on Master

MariaDB [(None)]> Show Master status;

+---------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+---------------+----------+--------------+------------------+

|      binlog.000001 |              425 |                  | |

+---------------+----------+--------------+------------------+

on the slave server

mysql> change MASTER to master_host= ' 192.168.0.72 ', master_user= ' repl_user ', master_password= ' Repl_pass ', master_ Log_file= ' binlog.000001 ', master_log_pos=425;

mysql> slave start; # turn on from sync

Mysql> show slave status \g; # See if synchronization is successful

three. Configure mysql-proxy 3.1 Download the installation package

~]# wget wget https://downloads.mysql.com/archives/get/file/ Mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

~]# sudo tar xf https://downloads.mysql.com/archives/get/file/ mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -c/usr/local

~]# mv/usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

3.2 configuration mysql-proxy

Create A LUA script directory

~]# Cd/usr/local/mysql-proxy

mysql-proxy]# mkdir Lua

Create log directory

mysql-proxy]# Mdkir Logs

Copy read-write detach profile and manage script to Lua script directory

mysql-proxy]# CP Share/doc/mysql-proxy/rw-splitting.lua./lua

mysql-proxy]# CP Share/doc/mysqp-proxy/admin-sql.lua./lua

Creating a configuration file

~]# vim/etc/mysql-proxy.conf

[Mysql-proxy]

user = root running the agent

Admin-username = mysql_proxy_user # mysql-proxy user connecting back-end mysql server

Admin-password = mysql_proxy_pass # mysql-proxy password to connect back-end mysql server

proxy-address = 192.168.0.71:3307 # mysql-proxy listening IP and port, port default 4040

proxy-read-only-backend-addresses = 192.168.0.73 # read-only service

proxy-backend-addresses = 192.168.0.72 # read-write server

Proxy-lua-script =/usr/local/mysql-proxy/lua/rw-splitting.lua # Indicates a read-write detach configuration file

Admin-lus-script =/usr/local/mysql-proxy/lua/admin-sql.lua # Manage Script path

Log-file =/usr/local/mysql-proxy/logs/mysql-proxy.log # log file path

log-level = Debug # Logging Level

Daemon = true # Whether the program starts at the back end

KeepAlive = true # Mysql-proxy crashes after attempting to restart

Modifying read-write configuration files

Vim/usr/local/mysql-proxy/lua/rw-splitting.lua

If not Proxy.global.config.rwsplit then

Proxy.global.config.rwsplit = {

Min_idle_connections = 1, # The default is more than 4 connections, the Read and write separation begins, instead of 1

Max_idle_connections = 1, # default 8, change to 1

Is_debug = False

}

Start Mysql-proxy

~]#/usr/local/mysql-proxy/bin/mysql-proxy--defaults-file=/etc/mysql-proxy.cnf

 

Create a mysql-proxy connected user on MySQL

Mysql> GRANT All on * * to ' mysql_proxy_user ' @ ' 192.168.0.% ' identified by ' mysql_proxy_pass ';

four. test read/write separation

Test Write

mysql-umysql_proxy_user-pmysql_proxy_pass-h192.168.0.71--port=3307

mysql> Create Databases Master;

Mysql> CREATE TABLE test (id int);

mysql> INSERT into Master.test value (1);

View from server:

You can see the synchronization from the server

Test Read

Number of primary server query statement lines

Query the number of statement bars from the server

Connect mysql-proxy execute a read statement

Number of primary server query statement lines

Query the number of statement bars from the server

We can see that the query statement is executed from the server.

Installing Mysql-proxy for MySQL read-write separation