2-17-mysql read-Write separation-mysql-proxy

MySQL Read and write separation overview

As the most widely used free database in the world, MySQL believes that all engineers engaged in system operations must have contacted. However, in the actual production environment, the single MySQL as a separate database is completely unable to meet the actual needs, whether in security, high availability and high concurrency and other aspects.
Therefore, in general, it is through the master-slave Replication (Master-slave) to synchronize the data, and then through the read-write separation (Mysql-proxy/amoeba) to improve the database of the concurrency load of such a scenario for deployment and implementation.
How the Read and write separation works:
The basic principle is to have the primary database handle the transactional increment, change, delete (INSERT, UPDATE, delete), and the Select query operation from the database. Database replication is used to synchronize changes caused by transactional operations to the slave database in the cluster.

Data Internal Exchange process:why to read and write separation:faced with increasing access pressure, the performance of a single server becomes a bottleneck and requires load sharing1, master and slave only responsible for their own write and read, the maximum degree of relief x lock and S lock contention2. MyISAM engine can be configured from library, improve query performance and save system overhead3. Increase redundancy and increase availabilityHow to implement read-write separation:generally there are two ways to achieveApplication layer implementation, Web site program implementationapplication-tier implementations refer to the implementation of read-write separation within the application and in the connector

<ignore_js_op>
Advantages:
A: The application is internally read/write separated, the installation can use both
B: Reduce the difficulty of a certain deployment
C: Access pressure Below a certain level, the performance is very good
Disadvantages:
A: Once the architecture is adjusted, the code will change
B: Difficult to implement advanced applications, such as automatic sub-Library, sub-table
C: Not suitable for large-scale application scenarios
Middleware Layer Implementation
middleware layer implementation refers to the implementation of the external middleware program read and write separation
Common middleware Programs:
mysql-proxy Amoeba Atlas (Cobar) (Alibaba) tddl (Taobao)
Advantages:
A: More flexible architecture design
B: Some advanced controls can be implemented on the program, such as: transparent horizontal splitting, failover, monitoring
C: Can rely on some technical means to improve MySQL performance,
D: Small impact on business code, but also safe
Disadvantages:
requires the support of a certain DevOps team

Mysql-proxy Overview

MySQL Proxy is a simple program that is located between your client side and the MySQL server to monitor, analyze, or change their communications. It is flexible, unlimited, and common uses include: load balancing, failure, query analysis, query filtering and modification, and so on.
MySQL Proxy is such a middle-tier agent, simply said,MySQL proxy is a connection pool , responsible for the front-end application of connection requests forwarded to the background database, and through the use of Lua script, can achieve complex connection control and filtering For read-write separation and load balancing . For the application, theMySQL proxy is completely transparent , and the application only needs to be connected to the listening port of MySQL proxy. of course, this proxy machine may become a single point of failure, but can use multiple proxy machine as redundancy , in the Application Server connection pool configuration to multiple proxy connection parameters can be configured.

one of the more powerful features of MySQL Proxy is the realization of "read and write Separation", the basic principle is to let the primary database process transactional queries, let the library handle select queries. Database replication is used to synchronize changes caused by transactional queries to the slave libraries in the cluster

Lua Overview:
Lua is a small scripting language.
The speed of Lua is the fastest. It all determines that Lua is the best choice for embedded scripting.
LUA is written in standard C and can be compiled and run on almost all operating systems and platforms. LUA does not provide a powerful library, which is determined by its positioning. So Lua is not suitable as a language for developing standalone applications.
LUA website:http://www.lua.org/

Download: mysql-proxy
http://dev.mysql.com/downloads/mysql-proxy/

 
port :   Mysql-proxy default port: 4040



&NBSP;
experimental environment:
mysql-proxy server:         xuegod63               ip:192.168.1.63
Span style= "font-family: Microsoft JAS Black" >mysql Server (master, responsible for writing) service side:xuegod64    ip:192.168.1.64
mysql server (from, responsible for reading) client:xuegod65    ip:192.168.1.65
deployment Mysql-proxy Server-side XUEGOD62:
Pre-installation requires the system to support the LUA locale:

1. [[email protected] ~]# Yum install Lua

Copy Code

Install Mysql-proxy:
It is recommended to use a compiled binary version, because the latest version of the Mysql-proxy has high requirements for automake,glib and libevent versions when compiling with the source package, which are the basic packages of the system and are not recommended for forced updates.
And these compiled binaries are all in a unified directory after decompression, so it is recommended to select the following versions:

1. [Email protected] ~]# wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
2. [Email protected] ~]# TAR-XF mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz-c/usr/local/
3. [Email protected] local]# MV Mysql-proxy-0.8.5-linux-el6-x86-64bit/mysql-proxy

Copy Code

To modify a system environment variable:

1. [Email protected] local]# Vim/etc/profile
2. Export Path=/usr/local/mysql-proxy/bin/:/usr/local/mysql/bin: $PATH
3. [Email protected] local]# source!$

Copy Code

Mysql-proxy Script configuration file location:

1. [Email protected] ~]# ls/usr/local/mysql-proxy/share/doc/mysql-proxy/

Copy Code

Modify the configuration file for read-write separation:

1. [Email protected] ~]# Vim/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

Copy Code

Modify

1. Min_idle_connections = 4,
2. Max_idle_connections = 8,

Copy Code

For


#修改默认连接, for quick testing, the default minimum of 4 client connections will be read and write separated, the maximum number of links is 8.
Note: in order to verify the effect of the experiment, he was changed to 1. When there is a link, the function of read and write separation is realized. In order to clearly see the effect of the read and write separation, you need to temporarily turn off MySQL master copy function.

Create databases and tables on XUEGOD64 for write operations:

1. [Email protected] ~]# mysql-uroot-p123456
2. mysql> CREATE database db;
3. mysql> use DB;
4. Mysql> CREATE TABLE test (id int);
5. mysql> INSERT into test values (64);
6. Mysql> Grant all on db.* to [e-mail protected] '% ' identified by ' 123456 ';

Copy Code

Turn off password strength auditing, add Validate-password=off in MY.CNF
Create a database and table on XUEGOD65 to implement read operations:

1. [Email protected] ~]# mysql-uroot-p123456
2. mysql> CREATE database db;
3. mysql> use DB;
4. Mysql> CREATE TABLE test (id int);
5. mysql> INSERT into test values (65);
6. Mysql> Grant all on db.* to [e-mail protected] '% ' identified by ' 123456 ';

Copy Code

Start the service Mysql-proxy service

1. [Email protected] ~]# mysql-proxy--proxy-read-only-backend-addresses=10.10.10.65:3306--proxy-backend-addresses= 10.10.10.64:3306--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &

Copy Code

Parameter description:

--proxy-read-only-backend-addresses=192.168.1.64:3306 # Defining back-end read-only servers
--proxy-backend-addresses=192.168.1.63:3306 #定义后端mysql主服务器地址, specify the port of the MySQL write master server
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua & #指定lua脚本, here, The rw-splitting script is used for read-write separation

When there are multiple read-only servers, you can write more than one of the following parameters:

--proxy-read-only-backend-addresses=192.168.1.64:3306 # Defining back-end read-only servers
--proxy-read-only-backend-addresses=192.168.1.65:3306 # Defining back-end read-only servers
#--proxy-address=192.168.1.62:3307 Specifies the listener port for MySQL proxy, the default is: 4040

The full parameters can be viewed by running the following command:

1. [Email protected] ~]# Mysql-proxy--help-all

Copy Code

To see if the proxy is started:

1. [[email protected] ~]# lsof-i: 4040
2. COMMAND PID USER FD TYPE DEVICE size/off NODE NAME
3. Mysql-pro 3144 root 9u IPv4 19597 0t0 TCP *:yo-main (LISTEN)

Copy Code

Test read/write separation


To test the write operation:

1. [Email protected] ~]# mysql-uuser1-p123456-p4040-h192.168.1.62
2. mysql> use DB;
3. mysql> INSERT into test values (663); Enter, no error.

Copy Code

# #看不到64数据, you can see the data you just wrote, indicating that the write operation was successful. because it is the first client connection , the read-write separation is not started, so the select reads without seeing "6464" data, but instead sees the "6363" Data of the Lord.
Next, open a few more clients to test the read. Note: The first link must be to go backend the main MySQL server.
Test Read operation:

1. [Email protected] ~]# mysql-uuser1-p123456-p4040-h 192.168.1.63

Copy Code

1. [Email protected] ~]# mysql-uuser1-p123456-p4040-h 192.168.1.62

Copy Code

We'll use 62 to log in and see

1. [Email protected] ~]# mysql-uuser1-p123456-p4040-h 192.168.1.62

Copy Code

This indicates that the read-write separation test was successful. But the data hasn't been kept in sync yet. Keeping data synchronized can be done by MySQL master and slave.
Extensions: View Client connection Status:

1. Mysql> show Processlist;

Copy Code

Description of each column parameter:
first column, ID, an identity  
user column, showing the current user, if not root, this command only displays the SQL statements within the scope of your permission.  
host column that shows which IP port this statement was issued from. A user that can be used to track a problem statement.  
db column that shows which database the process is currently connected to.  
command column that shows the execution of the current connection commands, usually sleep (sleep), query, connection (connect).  
time column, this state lasts for the time, in seconds.  
state column, showing the state of the SQL statement using the current connection, the very important column, state is just one of the states in the statement execution, an SQL statement to query For example, you may need to go through copying to TMP table, sorting result, sending data, and so on.  
info column, the SQL statement is displayed because the length is limited, so the long SQL statement is not complete, but an important basis for judging the problem statement .

Deploy MySQL master-slave and read-write separation

Configure Master-Slave on a previous basis
Configure XUEGOD64 as Master

1. [Email protected] ~]# VIM/ETC/MY.CNF
2. Log-bin=mysql-bin-master #启用二进制日志
3. Server-id=1 #本机数据库ID Mark
4. Binlog-do-db=db #可以被从服务器复制的库. The name of the database in which the binary needs to be synchronized
5. Binlog-ignore-db=mysql #不可以被从服务器复制的库

Copy Code

Authorized:

1. mysql> grant replication Slave on * * to [e-mail protected] identified by "123456";

Copy Code

To restart MySQL:

1. Service mysqld Restart
2. [Email protected] ~]# mysql-uroot-p123456
3. mysql> use DB;
4. mysql> drop table test;
5. [Email protected] ~]# service mysqld restart

Copy Code

Configure XUEGOD65 to Slave

1. [Email protected] ~]# VIM/ETC/MY.CNF
2. Server-id = 2
3. [Email protected] ~]# service mysqld restart
4. [Email protected] ~]# mysql-uroot-p123456
5. mysql> Change Master to master_host= ' 10.10.10.64 ', master_user= ' slave ', master_password= ' 123456 ';
6. mysql> use DB;
7. mysql> drop table test;
8. mysql> start slave;
9. Mysql> Show Slave Status\g

Copy Code

Master-Slave synchronization test:
1:mysql The primary database xuegod64 insert data:

1. mysql> use DB;
2. Mysql> CREATE TABLE admin (id int);
3. mysql> INSERT INTO admin values (63);

Copy Code

2:mysql master-Slave database xuegod64\xuegod65 view synchronized data:


After synchronization, the test uses Mysql-proxy to read the same data.
Testing on other test machines

1. [Email protected]~]# mysql-uuser1-p123456-p4040-h 10.10.10.63-e ' select * from Db.admin; '

Copy Code

Stop from server, stop slave test insert data to see Read/write separation
#可以查看到admin中的63记录, the mysql+proxy+ master-slave read/write separation is successful.
Insert Data test:

1. mysql> use DB;
2. mysql> INSERT INTO admin values (64);

Copy Code

There are xuegod63,xuegod64 on the view.

How about when we slave hang out?
Set up a mysqlproxy+ master to view a situation that is down
Analog failure: hanging from the server

1. [[Email protected] ~]# service mysqld stop

Copy Code

Test read and write on the Mysql-proxy

1. mysql> INSERT INTO admin values (65);

Copy Code

Check the connection status on XUEGOD63, confirm that after slave is turned off, read and write are Access xuegod63

Summary: When the slave database is stopped, proxy query will be transferred to master, when the slave started, Proxy is still reading master, when there is a new link in the time will be re-read slave data. Sometimes you may need to restart the mysql-proxy.
If the primary database is hung:
Master and slave are gone, only the data can be viewed on mysql-proxy.

1. [Email protected] ~]# mysql-uuser1-p123456-p4040-h 10.10.10.63-e ' select * from Db.admin; '

Copy Code

2-17-mysql read-Write separation-mysql-proxy