Read-Write Separation of Mysql Master-Slave Configuration

I. Several schemes for master-slave replication of MySQL

Database read-write separation is an indispensable and important function for large-scale systems or high-volume Internet applications. From the database point of view, for most applications, from centralization to distribution, the most basic requirement is not the bottleneck of data storage, but the bottleneck of computing, that is, the bottleneck of SQL query. We know that under normal circumstances, Insert SQL is written in tens of milliseconds, while most Select SQL in the system takes several seconds to several minutes to get results. Complex SQL, which consumes the server CPU, is no less powerful than the power of the dead cycle. On systems without read-write separation, it is likely that some complex SQL queries in peak time will lead to database server CPU burst tables, system paralysis, and in serious cases may lead to database crash. Therefore, from the perspective of database protection, we should try to avoid single-node database without master-slave replication mechanism.
For MySQL, the standard read-write separation is the master-slave mode. A write node Master is followed by multiple read nodes. The number of read nodes depends on the pressure of the system. Usually, the configuration of 1-3 read nodes is as follows:

2. Master-slave duplication + read-write separation

Large-scale e-commerce systems generally use a Master database, the combination of multiple Slave databases to achieve read-write separation technology. Master library is responsible for data updating and real-time data query, Slave library is responsible for non-real-time data query. Because in practical applications, the database is read more and write less (high frequency of reading data, relatively less frequency of updating data), while reading data usually takes a long time, occupies more CPU s of database server, thus affecting user experience. Our usual practice is to extract queries from the main library, adopt multiple slave libraries, use load balancing, and reduce the query pressure of each slave library.

The goal of using read-write separation technology is to effectively relieve the pressure of Master library and distribute user query data requests to different Slave libraries, so as to ensure the robustness of the system. Let's look at the process of separating reading from writing.
　　

Client writes database through master, slave reads database, and has master-slave replication, that is, slave backs up master's data. In this way, read in slave and write in master, realize the separation of read and write, reduce the pressure of master and improve the concurrent load.

3. mysql-proxy Realizes Read-Write Separation

1. Environmental description:

  • Operating System: Red Hat Enterprise Linux Server release 6.5 (Santiago)
  • Master: 172.25.20.2
  • Slave slave server: 172.25.20.3
  • Scheduling server MySQL-Proxy: 172.25.20.4
  • Test client: 172.25.20.5 (mysql)
  • mysql master-slave replication
    This is omitted, please refer to my other blog MySQL 5.7.19 master-slave replication implementation and tuning.
    Portal: http://t.cn/R0WnDhC

2. Install mysql-proxy

Install on the Scheduling Server (server4)

The implementation of read-write separation is implemented by lua script. Now mysql-proxy is integrated and no installation is required.

Official website download address: https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

Find the version you need from: http://dev.mysql.com/downloads/mysql-proxy/

[root@server4 ~]# wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server4 ~]# tar -zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
[root@server4 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

3. Configure mysql-proxy to create the main configuration file

[root@server4 ~]# cd /usr/local/mysql-proxy/
[root@server4 mysql-proxy]# mkdir lua
[root@server4 mysql-proxy]# mkdir logs
[root@server4 mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./lua/
[root@server4 mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua ./lua

[root@server4 mysql-proxy]# vim /etc/mysql-proxy.cnf

[mysql-proxy]
user=root
admin-username=proxy
admin-password=123.com
proxy-address=172.25.20.4:4000
proxy-read-only-backend-addresses=172.25.20.3
proxy-backend-addresses=172.25.20.2
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=info
daemon=true
keepalive=true

[root@server4 mysql-proxy]# chmod 660 /etc/mysql-proxy.cnf

4. Modify Read-Write Separation Profile

[root@server4 mysql-proxy]# vim /usr/local/mysql-proxy/lua/rw-splitting.lua

if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
        --      min_idle_connections = 4,
        --      max_idle_connections = 8,
                min_idle_connections = 1,
                max_idle_connections = 1,

                is_debug = false
        }
end

5. Start mysql-proxy

[root@server4 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
[root@server4 mysql-proxy]# netstat -tupln | grep 4000
tcp        0      0 172.25.20.4:4000            0.0.0.0:*                   LISTEN      1091/mysql-proxy 

##Close mysql-proxy using: kill all-9 mysql-proxy

6. Test Read-Write Separation

  • Create proxy users on master (server 2) for mysql-proxy usage and synchronize this operation from the server
mysql> grant all on *.* to 'myproxy'@'172.25.20.4' identified by '1234+asDF';
  • Connecting mysql-proxy with client (server 5)
[root@server5 ~]# mysql -u myproxy -h 172.25.20.4 -P 4000 -p1234+asDF
  • Create a database and tables, then the data is written to the main mysql, and then synchronized from slave, you can first slave off, see if you can write, here I will not test, the next test read the data!
##First look at the client and master and slave database content, should be the same

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test.usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.02 sec)

Next, write data test

##Client
mysql> CREATE DATABASE myuser;
Query OK, 1 row affected (0.00 sec)

mysql> USE myuser;
Database changed
mysql> CREATE TABLE user (number INT(10),name VARCHAR(255));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test values(01,'user1');
ERROR 1146 (42S02): Table 'myuser.test' doesn't exist
mysql> insert into user values(01,'user1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(02,'user2');
Query OK, 1 row affected (0.01 sec)
  • The lander checks the newly written data from mysq as follows.
mysql> CREATE DATABASE myuser;
mysql> USE myuser;
mysql> CREATE TABLE user (number INT(10),name VARCHAR(255));
mysql> insert into user values(01,'user1');
mysql> insert into user values(02,'user2');

View on master and slave respectively:

##master
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| myuser             |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM myuser.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)


##slave
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| myuser             |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM myuser.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)




As if the data were not synchronized to slave, check the master's status

mysql> show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |     1100 | test         | mysql            | 42989d0d-a446-11e7-8d8d-525400140b3d:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

The problem is clear. We only synchronize the contents of the test library. It seems a little troublesome for us to modify the configuration. Let's do experiments in the test library.

##Client (server 5)
mysql> USE test;
mysql> CREATE TABLE user (number INT(10),name VARCHAR(255));
mysql> insert into user values(01,'user1');
mysql> insert into user values(02,'user2');

##On master (server 2)

mysql> SELECT * FROM test.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)

##On slave (server 3)
mysql>  SELECT * FROM test.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)

Successful data synchronization, by the way, on the client query the data we just wrote

mysql>  SELECT * FROM test.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)


By the way, look at the status of the scheduling server (server4):

We can also do a test, because the database myuser and the contents of the library we just created are written to master, but not synchronized to slave. Then we query the contents of myuser database on the client side. If we can query it, it means that the query is also the master, then our read-write separation will not succeed. On the contrary, if the query is not done, we can query the content of myuser database on the client side. By then, you can say that the query is slave, while the write is master, and the read-write separation configuration is successful. Next we will witness the miracle together:

mysql> SELECT * FROM myuser.user;
ERROR 1146 (42S02): Table 'myuser.user' doesn't exist
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| myuser             |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

As expected, the query was not available, indicating that our read-write separation configuration was successful.

  • Change a client to login to mysql-proxy, query data, the results are the same, the contents of the test library can be queried normally, the contents of the myuser library can not be queried, indicating that we have successfully configured, and truly achieve the effect of separation of reading and writing!
[root@foundation20 ~]# mysql -u myproxy -h 172.25.20.4 -P 4000 -p1234+asDF
mysql> SELECT * FROM myuser.user;
ERROR 1146 (42S02): Table 'myuser.user' doesn't exist
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql>  SELECT * FROM test.user;
+--------+-------+
| number | name  |
+--------+-------+
|      1 | user1 |
|      2 | user2 |
+--------+-------+
2 rows in set (0.00 sec)

Keywords: MySQL Database SQL Linux

Added by cap2cap10 on Tue, 21 May 2019 03:18:58 +0300

Popular Keywords

©2024 Programming VIP