MySQL read/write splitting

1. What is read/write splitting?

 

One of the most powerful functions of MySQL Proxy is to implement Read/Write Splitting )". The basic principle is to allow the primary database to process transactional queries, but to process SELECT queries from the database. Database Replication is used to synchronize changes caused by transactional queries to the slave database in the cluster. Of course, the master server can also provide the query service. The biggest role of read/write splitting is the pressure on the environment server. You can see the figure below:

 

Benefits of binary read/write splitting

1. Increased Redundancy

2. Added machine processing capabilities

3. For read operations-oriented applications, using read/write splitting is the best scenario, because it ensures that the write server is under less pressure and the read server can accept latency at a certain point in time.

Why read/write splitting improves performance

1. Increase in physical servers and Load
2. The master and slave nodes are only responsible for writing and reading each other, which greatly mitigates the contention between the X lock and the S lock.
3. The myisam engine can be configured from the slave database to improve query performance and save system overhead.
4. there is still a difference between synchronizing data from the master database and directly writing data from the master database. The binlog sent from the master database restores data. However, the most important difference is that the binlog sent from the master database to the slave database is asynchronous, restoring data from the database is asynchronous.
5. read/write splitting is applicable to scenarios where reading is much greater than writing. If there is only one server, when there are many select statements, the update and delete statements will be blocked by the data in these select statements. Wait until the select statement ends, low concurrency performance. For applications with similar write and read ratios, dual-master replication should be deployed.

6. some parameters can be added to the slave database to improve its read performance, for example, -- skip-innodb, -- skip-bdb, -- low-priority-updates, and -- delay-key-write = ALL. Of course, these settings are also determined based on specific business needs and may not be used.

7. Shared read. For example, if we have one master and three slave databases, we will not consider the slave database's unilateral settings mentioned in the preceding 1. Suppose there are 10 writes and 150 reads in one minute. Therefore, the number of reads from a master database 3 is equivalent to a total of 40, but the total number of reads remains unchanged. Therefore, on average, each server undertakes 10 writes and 50 reads (the master database does not perform read operations ). Therefore, although the write speed remains unchanged, the read speed is greatly apportioned, improving the system performance. In addition, when the read is apportioned, The Write Performance is indirectly improved. Therefore, the overall performance has been improved. To put it bluntly, we can change the performance of machines and bandwidth. The official MySQL documentation contains related calculation formulas: for official documentation, see "when and how MySQL replication can improve system performance" in 6.9FAQ"

8. another major function of MySQL replication is to increase redundancy and improve availability. When a database server goes down, you can adjust another slave database to restore services as quickly as possible. Therefore, you cannot simply look at performance, that is to say, one master and one slave are also possible.

4. read/write splitting

 

5. read/write splitting Simulation

Experiment environment overview

Serv01: proxy server 192.168.1.11 serv01.host.com

Serv08: master server (mainly write data, readable and writable) 192.168.1.18 serv08.host.com

Serv09: slave server (primary read data) 192.168.1.19 serv09.host.com

Operating system version

RHEL Server6.1 64-bit System

Version of the software package used

Mysql-5.5.29-linux2.6-x86_64.tar.gz

Mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz

Step 1: Set up a MySQL server and clear logs. Note: MySQL does not need to be installed on the proxy server.

Step 2: unzip the mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz file and decompress the file

[Root @ larrywen 1005] # scp/opt/soft/ule-mysql/mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz 192.168.1.11: /opt [root @ serv01 opt] # tar-xvf mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz-C/usr/local/[root @ serv01 opt] # cd/usr/local/[root @ serv01 local] # music mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit/mysql-proxy [root @ serv01 local] # ll mysql-proxy/total 24drwxr-xr-x. 2 7157 wheel 4096 Aug 17 2011 bindrwxr-xr-x. 2 7157 wheel 4096 Aug 17 2011 includedrwxr-xr-x. 4 7157 wheel 4096 Aug 17 2011 libdrwxr-xr-x. 2 7157 wheel 4096 Aug 17 2011 libexecdrwxr-xr-x. 3 7157 wheel 4096 Aug 17 2011 licensesdrwxr-xr-x. 3 7157 wheel 4096 Aug 17 2011 share -- you can view the help [root @ serv01 bin] #. /mysql-proxy -- help-all

Step 3: create a user on the master server of serv08 and create a user on the server of serv09. Note that the user name and password are consistent.

serv08mysql> grant all on *.* to 'larry'@'192.168.1.%' identified by 'larry';Query OK, 0 rows affected (0.00 sec)serv09mysql> grant all on *.* to 'larry'@'192.168.1.%' identified by 'larry';Query OK, 0 rows affected (0.00 sec)

Step 4: Modify the settings from the server in serv09, enable slave, and view the slave status. Create a test database and insert Test Data

serv09mysql> change master to  master_host='192.168.1.18',  master_user='larry', master_password='larry', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=107;Query OK, 0 rows affected (0.01 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status \G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.18                  Master_User: larry                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 107               Relay_Log_File: serv09-relay-bin.000002                Relay_Log_Pos: 253        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 107              Relay_Log_Space: 410              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 21 row in set (0.00 sec)ERROR: No query specifiedmysql> select user,password,host from mysql.user;+------+-------------------------------------------+-----------------+| user | password                                  | host            |+------+-------------------------------------------+-----------------+| root |                                           | localhost       || root |                                           | serv08.host.com || root |                                           | 127.0.0.1       || root |                                           | ::1             ||      |                                           | localhost       ||      |                                           | serv08.host.com || rep  | *0CDC8D34246E22649D647DB04E7CCCACAB4368B6 | 192.168.1.%     |+------+-------------------------------------------+-----------------+7 rows in set (0.00 sec)mysql> create database larrydb;Query OK, 1 row affected (0.00 sec)mysql> use larrydb;Database changedmysql> create table user(id int, name varchar(30));Query OK, 0 rows affected (0.01 sec)mysql> insert into user values(1,'larrywen');Query OK, 1 row affected (0.01 sec)mysql> insert into user values(2,'wentasy');Query OK, 1 row affected (0.00 sec)mysql> select * from user;+------+----------+| id   | name     |+------+----------+|    1 | larrywen ||    2 | wentasy  |+------+----------+2 rows in set (0.00 sec)serv09mysql> select * from larrydb.user;+------+----------+| id   | name     |+------+----------+|    1 | larrywen ||    2 | wentasy  |+------+----------+2 rows in set (0.00 sec)

Step 5: To check the phenomenon, serv09 disable slave from the server

mysql> stop slave;Query OK, 0 rows affected (0.01 sec)

Step 6, serv 01 check whether there are MySQL users, modify the rw-splitting.lua file, modify the following parameters

[Root @ serv01 mysql-proxy] # id mysqluid = 500 (mysql) gid = 500 (mysql) groups = 500 (mysql) [root @ serv01 mysql-proxy] # vim rw-splitting.lua [root @ serv01 mysql-proxy] # cat rw-splitting.lua | grep-e min_idle_connections-e max_idle_connections-e is_debug min_idle_connections = 1, -- Minimum number of idle connections. For testing, set it to 1 max_idle_connections = 1, -- maximum number of idle connections. For testing, set it to 1 is_debug = true -- whether to enable Debug, to view debugging information, set this parameter to true.

Step 7: START mysql-proxy

[Root @ serv01 mysql-proxy] #/etc/init. d/mysql-proxy startStarting mysql-proxy: -- determine whether to connect to [root @ serv01 ~] # Mysql-ularry-plarry-h 192.168.1.18Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 6 Server version: 5.5.29-log Source distributionCopyright (c) 2000,201 2, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' f Or help. Type '\ C' to clear the current input statement. mysql> exitBye [root @ serv01 ~] # Mysql-ularry-plarry-h 192.168.1.19Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 8 Server version: 5.5.29-log Source distributionCopyright (c) 2000,201 2, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> exitBye

Step 8: view the symptom

[root@serv01 ~]# /etc/init.d/mysql-proxy startStarting mysql-proxy: [root@serv01 ~]# mysql -ularry -plarry -h 192.168.1.11[connect_server] 192.168.1.11:51054  [1].connected_clients = 0  [1].pool.cur_idle     = 0  [1].pool.max_idle     = 1  [1].pool.min_idle     = 1  [1].type = 1  [1].state = 0  [1] idle-conns below min-idleWelcome to the MySQL monitor.  Commands end with ; or \g.[read_query] 192.168.1.11:51054  current backend   = 0  client default db =   client username   = larry  query             = select @@version_comment limit 1  sending to backend : 192.168.1.19:3306    is_slave         : false    server default db:     server username  : larry    in_trans        : false    in_calc_found   : false    COM_QUERY       : trueYour MySQL connection id is 10Server version: 5.5.29-log Source distributionCopyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> mysql> use larrydb;[read_query] 192.168.1.11:51054  current backend   = 0  client default db =   client username   = larry  query             = SELECT DATABASE()  sending to backend : 192.168.1.19:3306    is_slave         : false    server default db:     server username  : larry    in_trans        : false    in_calc_found   : false    COM_QUERY       : true[read_query] 192.168.1.11:51054  current backend   = 0  client default db =   client username   = larry  sending to backend : 192.168.1.19:3306    is_slave         : false    server default db:     server username  : larry    in_trans        : false    in_calc_found   : false    COM_QUERY       : falseDatabase changedmysql> select * from user;[read_query] 192.168.1.11:51054  current backend   = 0  client default db = larrydb  client username   = larry  query             = select * from user  sending to backend : 192.168.1.19:3306    is_slave         : false    server default db: larrydb    server username  : larry    in_trans        : false    in_calc_found   : false    COM_QUERY       : true+------+----------+| id   | name     |+------+----------+|    1 | larrywen ||    2 | wentasy  |+------+----------+2 rows in set (0.00 sec)mysql> insert into user values(3,'jsutdb');[read_query] 192.168.1.11:51644  current backend   = 0  client default db = larrydb  client username   = larry  query             = insert into user values(3,'jsutdb')  sending to backend : 192.168.1.19:3306    is_slave         : false    server default db: larrydb    server username  : larry    in_trans        : false    in_calc_found   : false    COM_QUERY       : trueQuery OK, 1 row affected (0.00 sec)serv08mysql> select * from user;+------+----------+| id   | name     |+------+----------+|    1 | larrywen ||    2 | wentasy  |+------+----------+2 rows in set (0.00 sec)serv09mysql> select * from larrydb.user;+------+----------+| id   | name     |+------+----------+|    1 | larrywen ||    2 | wentasy  ||    3 | jsutdb   |+------+----------+3 rows in set (0.00 sec)

In step 9, although the above tests are effective, they are not as expected. Troubleshoot and reconfigure. An error occurred while configuring the parameters proxy-read-only-backend-addresses and proxy-backend-addresses. The proxy-read-only-backend-addresses should be configured as the slave server IP address, configure proxy-backend-addresses to the IP address of the master server.

[root@serv01 ~]# vim /etc/init.d/mysql-proxy [root@serv01 ~]# cat /etc/init.d/mysql-proxy#!/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/binPROXY_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.1.19:3306 --proxy-backend-addresses=192.168.1.18:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"#PROXY_PID=/usr/local/mysql-proxy/run/mysql-proxy.pidPROXY_PID=/var/run/mysql-proxy.pid# Source mysql-proxy configuration.if [ -f /etc/sysconfig/mysql-proxy ]; then        . /etc/sysconfig/mysql-proxyfiPATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH# By default it's all goodRETVAL=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=mysql --log-level=debug --log-file=/var/log/mysql-proxy.log --proxy-address=192.168.1.11:3306        RETVAL=$?        echo        if [ $RETVAL = 0 ]; then                touch /var/lock/subsys/mysql-proxy        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       ;;esacexit $RETVAL

Step 10: test. When you insert data, you can find that the connection is the master server, and the query is also the master server. It indicates that the master server and slave server have read functions.

[Root @ serv01 ~] # Mysql-ularry-plarry-h 192.168.1.11 [connect_server] 192.168.1.11: 57891 [1]. connected_clients = 0 [1]. pool. cur_idle = 0 [1]. pool. max_idle = 1 [1]. pool. min_idle = 1 [1]. type = 1 [1]. state = 1 [1] idle-conns below min-idle [read_query] 192.168.1.11: 57891 current backend = 0 client default db = client username = larry query = select @ version_comment limit 1 sending to backend: 192.168.1.18: 3306 is_slave: false server default db: server username: larry in_trans: false in_calc_found: false COM_QUERY: truemysql> insert into user values (5, 'test'); Query OK, 1 row affected (0.01 sec) [read_query] 192.168.1.11: 57893 current backend = 0 client default db = larrydb client username = larry query = insert into user values (5, 'test') sending to backend: 192.168.1.18: 3306 is_slave: false server default db: larrydb server username: larry in_trans: false in_calc_found: false COM_QUERY: truemysql> select * from user; + ------ + ---------- + | id | name | + ------ + ---------- + | 1 | larrywen | 2 | wentasy | 5 | test | + ------ + ---------- + 3 rows in set (0.00 sec) [read_query] 192.168.1.11: 57893 current backend = 0 client default db = larrydb client username = larry query = select * from user sending to backend: 192.168.1.18: 3306 is_slave: false server default db: larrydb server username: larry in_trans: false in_calc_found: false COM_QUERY: trueserv08 the master server can view data and query the data. It indicates that the master server can write mysql> select * from larrydb. user; + ------ + ---------- + | id | name | + ------ + ---------- + | 1 | larrywen | 2 | wentasy | 5 | test | + ------ + ---------- + 3 rows in set (0.00 sec) serv09 when querying data from the server and finding that the data cannot be queried, the slave server reads mysql> select * from larrydb. user; + ------ + ---------- + | id | name | + ------ + ---------- + | 1 | larrywen | 2 | wentasy | 3 | jsutdb | 4 | db | + ------ + ---------- + 4 rows in set (0.00 sec)

Step 2: Enable slave. Data Synchronization is successful.

mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> select * from larrydb.user;+------+----------+| id   | name     |+------+----------+|    1 | larrywen ||    2 | wentasy  ||    3 | jsutdb   ||    4 | db       ||    5 | test     |+------+----------+5 rows in set (0.00 sec)