Implementation of MySQL read/write splitting in RHEL6.5

Implementation of MySQL read/write splitting in RHEL6.5

I. Requirements

1. Configure two MySQL servers and one proxy server 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. The Master server allows SQL queries and writes, and the Slave server only allows SQL queries.

Ii. Solution

Use four RHEL6.5 virtual machines, as shown below. Among them, 192.168.4.10 and 192.168.4.20 serve as the MySQL master and slave servers respectively, and are the backend of the entire service. Another 192.168.4.30 serves as the MySQL proxy server, which is a front-end service directly oriented to the customer. The client 192.168.4.100 is used for access testing.

Compare the read/write splitting effect of the two methods --

1. MySQL Master-Slave replication: the client accesses the Master server to write the database, and the client accesses the Slave server to read the database. In this case, the client needs to identify where to write and where to read.
2. MySQL Master-Slave replication + Proxy: the client accesses the Proxy server and submits read and write requests to the Proxy for identification. If it is a database write operation, it is handed over to the Master. If it is a database read operation, it is handed over to Slave for processing, it is controlled by the allocation policy. In this case, the Proxy server does not need to distinguish between read and write targets, but rather reduces the complexity of the client program.

The following is based on mysql master-slave replication. For more information about mysql master-slave replication, see MySQL master-slave replication.

Iii. Implementation

1. Deploy the mysql-proxy Server

1) install the MySQL-proxy package officially provided by mysql

MySQL officially provides a free installation version of mysql-proxy, which can be used after decompression. Because msyql-proxy uses the LUA script language, you need to install the lua Software Package (included in the RHEL6 image) in advance to ensure its normal use:

[Root @ proxy ~] # Yum-y install lua

Then deploy the mysql-proxy package:

[Root @ proxy desktop] # tar zxf mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz-C/usr/local/

[Root @ proxy desktop] # cd/usr/local/

[Root @ proxy local] # mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit/mysql-proxy // shorten the file a bit for easy access

Switch to the deployed directory and check the related directories, executable programs, and scripts. Generally, use the mysql-proxy script in the bin directory to start the proxy service:

[Root @ proxy local] # cd mysql-proxy/

[Root @ proxy mysql-proxy] # ls

Bin include lib libexec licenses share

[Root @ proxy mysql-proxy] # ls bin/libexec/

Bin/: // service Script directory

Mysql-binlog-dump mysql-myisam-dump mysql-proxy

 

Libexec/: // executable program directory

Mysql-binlog-dump mysql-myisam-dump mysql-proxy

2) Prepare the LUA policy script for read/write splitting.

Copy the sample policy provided by mysql-proxy directly:

[Root @ proxy mysql-proxy] # cp share/doc/mysql-proxy/rw-splitting.lua ./

3) Start the mysql-proxy service

Main Command Options:

-P (uppercase): 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.

 

[Root @ proxy mysql-proxy] # pwd

/Usr/local/mysql-proxy

[Root @ proxy mysql-proxy] # bin/mysql-proxy-P 192.168.4.30: 3306 \

>-B 192.168.4.10: 3306 \

>-R 192.168.4.20: 3306 \

>-S rw-splitting.lua &

[Root @ proxy mysql-proxy] # netstat-anpt | grep mysql // confirm the listening status

Tcp 0 0 192.168.4.30: 3306 0.0.0.0: * LISTEN 16119/mysql-proxy

For the client, the proxy host 192.168.4.30 is equivalent to a readable and writable MySQL database server.

To start the mysql-proxy service at startup, you can write the above command to the/etc/rc. local configuration file:

[Root @ proxy ~] # Vim/etc/rc. local

....

/Usr/local/mysql-proxy/bin/mysql-proxy-P 192.168.4.30: 3306 \

-B 192.168.4.10: 3306 \

-R 192.168.4.20: 3306 \

-S rw-splitting.lua &

2. Test the read/write splitting through mysql-proxy.

1) authorize the user on the Master server to allow remote access from clients at 192.168.4.0/24.

Mysql> GRANT all ON *. * TO user02 @ '192. 192.% 'identified by 'pwd123 ';

Query OK, 0 rows affected (0.07 sec)

Because the master-SLAVE synchronization of the mysql database has been configured previously, user authorization on the SLAVE is automatically updated.

2) access the Mysql database from the client 192.168.4.100

[Root @ client ~] # Mysql-h192.168.4.30-uuser02-ppwd123

ERROR 2003 (HY000): Can't connect to MySQL server on '192. 168.4.30 '(192)

In this case, you can disable the proxy firewall or add a firewall rule that allows access to port 3306.

[Root @ proxy ~] # Iptables-I INPUT-s 192.168.4.0/24-p tcp -- dport 3306-j ACCEPT

Log On again:

[Root @ client ~] # Mysql-h192.168.4.30-uuser02-ppwd123

......

Mysql> // indicates that the logon is successful.

Mysql> create database student; // CREATE a DATABASE

Query OK, 1 row affected (0.10 sec)

Mysql> USE student; // switch to the new database

Database changed

Mysql> create table info (id int (4), name varchar (48); // CREATE a TABLE

Query OK, 0 rows affected (1.23 sec)

Mysql> insert into info VALUES (1, "hanmeimei"), (2, "lilei"); // INSERT two table records

Query OK, 2 rows affected (0.18 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql> SELECT * FROM info;

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

| Id | name |

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

| 1 | hanmeimei |

| 2 | lilei |

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

2 rows in set (0.01 sec)

Mysql> quit

Bye

3) confirm the table created on the client on the master and slave.

[Root @ client ~] # Mysql-h192.168.4.10-uuser02-ppwd123

Mysql> USE student;

Mysql> show tables;

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

| Tables_in_student |

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

| Info |

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

1 row in set (0.00 sec)

Mysql> SELECT * FROM info;

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

| Id | name |

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

| 1 | hanmeimei |

| 2 | lilei |

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

2 rows in set (0.00 sec)

 

[Root @ client ~] # Mysql-h192.168.4.20-uuser02-ppwd123

Mysql> SELECT * FROM student.info;

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

| Id | name |

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

| 1 | hanmeimei |

| 2 | lilei |

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

2 rows in set (0.00 sec)

4) observe the network connection accessed by the MySQL proxy

The Proxy agent can see the network connection with the MySQL read and write Server:

[Root @ proxy mysql-proxy] # netstat-anpt | grep mysql

Tcp 0 0 192.168.4.30: 3306 0.0.0.0: * LISTEN 16119/mysql-proxy

Tcp 0 0 192.168.4.30: 60975 192.168.4.10: 3306 ESTABLISHED 16119/mysql-proxy

Tcp 0 0 192.168.4.30: 60974 192.168.4.10: 3306 ESTABLISHED 16119/mysql-proxy

Tcp 0 0 192.168.4.30: 60977 192.168.4.10: 3306 ESTABLISHED 16119/mysql-proxy

Tcp 0 0 192.168.4.30: 60978 192.168.4.10: 3306 ESTABLISHED 16119/mysql-proxy

Tcp 0 0 192.168.4.30: 60976 192.168.4.10: 3306 ESTABLISHED 16119/mysql-proxy

On the Master node, you can see the network connection from the Slave and Proxy:

[Root @ master mysql] # netstat-anpt | grep mysql

Tcp 0 0: 3306: * LISTEN 3788/mysqld

Tcp 0 0: ffff: 192.168.4.10: 3306: ffff: 192.168.4.30: 60974 ESTABLISHED 3788/mysqld

Tcp 0 0: ffff: 192.168.4.10: 3306: ffff: 192.168.4.30: 60978 ESTABLISHED 3788/mysqld

Tcp 0 0: ffff: 192.168.4.10: 3306: ffff: 192.168.4.30: 60975 ESTABLISHED 3788/mysqld

Tcp 0 0: ffff: 192.168.4.10: 3306: ffff: 192.168.4.30: 60977 ESTABLISHED 3788/mysqld

Tcp 0 0: ffff: 192.168.4.10: 3306: ffff: 192.168.4.20: 33555 ESTABLISHED 3788/mysqld

Tcp 0 0: ffff: 192.168.4.10: 3306: ffff: 192.168.4.30: 60976 ESTABLISHED 3788/mysqld

The concurrent access volume does not exceed the specified threshold value. read/write operations are not differentiated. read/write operations are handed over to the server responsible for read operations. once distinguished, all requests are differentiated until the number of connections is 0.

Now, even if you access different servers, the client cannot find any changes to the server. If you want to observe them, you can store different data in the same table of the two servers, then, log on to the agent from multiple terminals on the client to observe the data changes,

Iv. Expansion

Integration of MySQL proxy and read (or write) Server

In many enterprise applications, the MySQL database basically reads and writes less. Therefore, you can install mysql-proxy with the Master or Slave to reduce the cost of one server. For example:

Master + Proxy: 192.168.4.10/24

Slave: 192.168.4.20/24

In this case, to avoid port conflicts between mysql-proxy and MySQL service programs, you can change the listening address and port of the related program if necessary.

To modify the listening address and port of the MySQL service program, you can use the following options:

-- Bind-address =: used to limit the listening address. For example, it can be set to 127.0.0.1.

-- Port =: used to change the port number. For example, you can change it to 33060.

Of course, the easiest way is to directly switch mysql-proxy to another port. For example, if you deploy mysql-proxy on the Master, you can monitor port 33060 of 192.168.4.10 when it is enabled:

[Root @ proxy ~] # Cd/usr/local/mysql-proxy/

[Root @ proxy mysql-proxy] # bin/mysql-proxy-P 192.168.4.10: 3333 \

-B 192.168.4.10: 3306 \

-R 192.168.4.20: 3306 \

-S rw-splitting.lua &

In this way, the client can read and write the database as long as it accesses port 3333 of 192.168.4.10. When the server uses a non-standard MySQL port, you must add the-P option for the mysql tool to specify the port number:

[Root @ client ~] # Mysql-h 192.168.4.10-uuser02-P 3333-ppwd123

Mysql>

MySQL5.6 master-slave Replication (read/write splitting) configuration full version

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL-5.6 + MySQL-Proxy build master-slave replication and read/write Separation

MySQL database read/write splitting

The Thinkphp framework supports MySQL read/write splitting.

This article permanently updates the link address: