Set up a MySQL proxy server to implement read/write splitting + Master/slave synchronization _ MySQL

Build a MySQL proxy server to implement read/write splitting + Master/slave synchronization proxy server

Lab requirements:

1. configure two MySQL servers (192.168.100.2, 192.168.100.3) and one proxy server (192.168.100.1) to implement read/write splitting of the MySQL proxy.

2. you only need to access the MySQL proxy server. the actual SQL query and write operations are handed over to two MySQL servers in the background.

3.2 MySQL servers implement Master-Slave synchronization. the Master server allows SQL queries and writes, and the Slave server only allows SQL queries.

Ubuntu Nginx load implementation of high performance WEB Server 5 --- MySQL master synchronization http://www.linuxidc.com/Linux/2012-06/61687p5.htm

Production environment MySQL master synchronization primary key conflict processing http://www.linuxidc.com/Linux/2013-07/86890.htm

MySQL master/slave failure error Got fatal error 1236 http://www.linuxidc.com/Linux/2012-02/54729.htm

MySQL master-slave replication, http://www.linuxidc.com/Linux/2013-03/81913.htm on a single server

I. configuration of the MASTER database server (192.168.100.2)

1. install the software package (MySQL is used in this experiment)

# Rpm-qa | grep-I mysql

MySQL-shared-5.6.15-1.el6.x86_64

MySQL-devel-5.6.15-1.el6.x86_64

MySQL-embedded-5.6.15-1.el6.x86_64

MySQL-test-5.6.15-1.el6.x86_64

MySQL-server-5.6.15-1.el6.x86_64

MySQL-client-5.6.15-1.el6.x86_64

MySQL-shared-compat-5.6.15-1.el6.x86_64

2. start the service and log on to the database to authorize the proxy server.

# Service mysql start & chkconfig mysql on

Mysql> grant all on *. * to proxyuser @ "192.168.100.1" identified by "123456 ";

3. create a test database and table

Mysql> create database proxydb;

Mysql> create table proxydb. proxytab (id int );

Mysql> insert into proxydb. proxytab values (2 );

II. SLAVE Database Server (192.168.100.3) configuration

1. install the software package

2. start the service and log on to the database to authorize the proxy server.

# Service mysql start & chkconfig mysql on

Mysql> grant all on *. * to proxyuser @ "192.168.100.1" identified by "123456"; // perform the same authorization as the MASTER database server

3. create a test database and table

Mysql> create database proxydb; // create the same database and table

Mysql> create table proxydb. proxytab (id int );

Mysql> insert into proxydb. proxytab values (3); // to test and distinguish two different database servers, insert different data first

III. MySQL proxy server configuration

1. test whether an authorized user can log on to two database servers on the proxy server.

# Mysql-h192.168.100.2-uproxyuser-p123456 // log on to the MASTER server

Mysql> select * from proxydb. proxytab;

+ ------ +

| Id |

+ ------ +

| 2 |

+ ------ +

1 row in set (0.00 sec)

# Mysql-h192.168.100.3-uproxyuser-p123456 // log on to the SLAVE server

Mysql> select * from proxydb. proxytab;

+ ------ +

| Id |

+ ------ +

| 3 |

+ ------ +

1 row in set (0.00 sec)

2. release Port 3306

# Service mysqld stop

# Chkconfig mysqld off

# Netstat-tulnp | grep: 3306

3. install software packages that provide proxy servers

# Tar zxvf mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz

# Mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit/usr/local/mysql-proxy // This package is green without installation

# Yum-y install lua // This software uses the LUA script language, so you need to install the lua software package

When the proxy service starts, the database responsible for writing is set to read the database server.

Use scripts to differentiate user access types

Specify the port you want to listen

4. start the proxy service

Cd/usr/local/mproxy/bin/

Use the mysql-proxy script in the bin directory to start the proxy service.

#. /Mysql-proxy-P 192.168.100.1: 3306-r 192.168.100.3: 3306-B 192.168.100.2: 3306-s/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &

Main command options:

-P: specifies the IP address and port of the proxy listener.

-R: specifies the IP address and port of the read server.

-B: specify the IP address and port of the write server.

-S: specify the lua script file

-- Keepalive: if the service process crashes, restart the process.

5. check whether the agent process is running

# Jobs

# Netstat-tulnp | grep: 3306

Tcp 0 0 192.168.100.1: 3306 0.0.0.0: * LISTEN 3314/mysql-proxy

// The proxy process must be in the running state. The client can access the backend database server through the proxy service.

6. view the read/write splitting script

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

......

40 min_idle_connections = 4, // read/write splitting is implemented only when the default number of concurrent requests is greater than 4

41 max_idle_connections = 8,

For more details, refer to the highlights on the next page.: Http://www.linuxidc.com/Linux/2014-05/102265p2.htm