1. MySQL Read-Write Separation Concept
The principle of MYSQL read-write separation is to let Master database handle transactional add, delete, modify, update operations (create, insert, update, delete) and Slave database handle query operations (select). MySQL read-write separation is based on MySQL master-slave replication, which can guarantee data modification on Master. After Slave synchronization, WEB applications can read to Sla Slave.Data on the ve side.
1.1 Implementation of Read-Write Separation
MySQL read-write separation can be based on third-party plug-ins or by developing and modifying code. There are four common ways to achieve read-write separation:
- Amoeba read-write separation;
- MySQL-Proxy read-write separation;
- MyCAT read-write separation;
- Based on program read-write separation (high efficiency, difficult to implement, development and change code);
Amoeba: is Alibaba 2008 open source MySQL as the underlying data store, and provides MySQL protocol interface for WEB, APP applications Proxy.It centrally responds to requests from WEB applications and sends SQL requests to specific databases for execution according to user-set rules. Based on this, load balancing, read-write separation, high availability and other requirements can be achieved.
MySQL-Proxy: MySQL middleware service officially provided by MySQL, supports countless client connections, while the backend can connect to several MySQL-Server servers. MySQL-Proxy itself is based on MySQL protocol. Clients connecting to MySQL-Proxy do not need to modify any settings, is no different from MySQL Server, and does not need to modify program code.
MyCAT: A database middleware based on Alibaba's 12-year open source cobar. It is a component between the database and application tiers in the architecture and is transparent to the application tier, enabling read-write separation, database subtabulation.
2. Read-write separation based on MySQL-Proxy
Proxy: 192.168.48.182 Master: 192.168.48.183 Slave: 192.168.48.184 Slave: 192.168.48.185
2.1 Diagram of working principle
2.2 Configuration Proxy
Proxy can choose to be deployed on the same server as mysql or on a separate server.
# Download MySQL-Proxy: [root@node02 ~]# cd /usr/src/ [root@node02 src]# [root@node02 src]# wget http://mirrors.163.com/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz # Unzip: [root@node02 src]# tar xf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz [root@node02 src]# [root@node02 src]# mv mysql-proxy-0.8.4-linux-el6-x86-64bit /usr/local/mysql-proxy # Configure environment variables: [root@node02 src]# echo "export PATH=/usr/local/mysql-proxy/bin:$PATH" > /etc/profile.d/mysql-proxy.sh [root@node02 src]# [root@node02 src]# . /etc/profile.d/mysql-proxy.sh [root@node02 src]# echo $PATH /usr/local/mysql-proxy/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin [root@node02 src]# # Start the MYSQL-Proxy middleware: [root@node02 src]# useradd -r mysql-proxy [root@node02 src]# [root@node02 src]# mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.48.183:3306" --proxy-read-only-backend-addresses="192.168.48.184:3306" --proxy-read-only-backend-addresses="192.168.48.185:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua" [root@node02 src]# # View port/log: [root@node02 src]# netstat -ntlp |grep 404 tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 11803/mysql-proxy tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 11803/mysql-proxy [root@node02 src]#
2.3 Startup parameters
# The parameters related to Mysql-Proxy are detailed below: --help-all # Get all the help information; --proxy-address=host:port # The address and port that the proxy service listens on, defaulting to 4040; --admin-address=host:port # Address and port that the management module listens on, defaulting to 4041; --proxy-backend-addresses=host:port # The address and port of the backend mysql server; --proxy-read-only-backend-addresses=host:port # The address and port of the back-end read-only mysql server; --proxy-lua-script=file_name # Complete the Lua script for the mysql proxy function; --daemon # Start mysql-proxy in daemon mode; --keepalive # Try restarting mysql-proxy when it crashes; --log-file=/path/to/log_file_name # Log file name; --log-level=level # Log level; --log-use-syslog # Logging based on syslog; --plugins=plugin # Plug-ins loaded when mysql-proxy starts; --user=user_name # The user running the mysql-proxy process; --defaults-file=/path/to/conf_file_name # The default configuration file path, whose configuration segment is identified by [mysql-proxy]; --proxy-skip-profiling # Disable profile; --pid-file=/path/to/pid_file_name # Process file name;
2.4 Start master/slave
[root@node03 ~]# systemctl start mariadb [root@node04 ~]# systemctl start mariadb [root@node05 ~]# systemctl start mariadb
2.5 View Read-Write Separation Status
View the read-write separation status based on port 4041 MySQL-Proxy and log on to port 4041:
[root@node02 ~]# mysql -h 192.168.48.182 -uadmin -padmin -P4041 # Backend database information can be seen at this point, but the status is unknown, indicating that there is no client connection yet and can be passed through the 4040 proxy port //Activate operations such as querying data. MySQL [(none)]> select * from backends; +-------------+---------------------+---------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+---------------------+---------+------+------+-------------------+ | 1 | 192.168.48.183:3306 | unknown | rw | NULL | 0 | | 2 | 192.168.48.184:3306 | unknown | ro | NULL | 0 | | 3 | 192.168.48.185:3306 | unknown | ro | NULL | 0 | +-------------+---------------------+---------+------+------+-------------------+ 3 rows in set (0.01 sec) MySQL [(none)]>
2.5 Authorize proxy on master database
MariaDB [(none)]> grant all on *.* to "mysql-proxy"@"192.168.48.182" identified by "123456"; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]>
2.6 Create data through proxy
By inserting data through the 4040 proxy port, the sql statement takes master, and the master state can be activated:
[root@node02 ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "create database superman charset utf8;" [root@node02 ~]#
On the 4041 administrative port, check again:
[root@node02 ~]# mysql -h 192.168.48.182 -uadmin -padmin -P4041 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.99-agent-admin Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> MySQL [(none)]> select * from backends; +-------------+---------------------+---------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+---------------------+---------+------+------+-------------------+ | 1 | 192.168.48.183:3306 | up | rw | NULL | 0 | | 2 | 192.168.48.184:3306 | unknown | ro | NULL | 0 | | 3 | 192.168.48.185:3306 | unknown | ro | NULL | 0 | +-------------+---------------------+---------+------+------+-------------------+ 3 rows in set (0.00 sec) MySQL [(none)]>
2.7 Query data through proxy
# First select the superman database in the master library (because of the master-slave relationship, the superman created in the master library synchronizes to the slave), create tables, and insert data: MariaDB [(none)]> use superman; Database changed MariaDB [superman]> create table t1(id int,name varchar(20)); Query OK, 0 rows affected (0.00 sec) MariaDB [superman]> insert t1 values(1,"xiaoming"); Query OK, 1 row affected (0.00 sec) MariaDB [superman]>
By querying data through the 4040 proxy port, the sql statement walks the slave, and the slave state can be activated:
# Execute more times! [root@node02 ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "select* from superman.t1;" +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ [root@node02 ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "select* from superman.t1;" +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ [root@node02 ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "select* from superman.t1;" +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ [root@node02 ~]#
On the 4041 administrative port, check again:
[root@node02 ~]# mysql -h 192.168.48.182 -uadmin -padmin -P4041 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.99-agent-admin Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> select * from backends; +-------------+---------------------+-------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+---------------------+-------+------+------+-------------------+ | 1 | 192.168.48.183:3306 | up | rw | NULL | 0 | | 2 | 192.168.48.184:3306 | up | ro | NULL | 0 | | 3 | 192.168.48.185:3306 | up | ro | NULL | 0 | +-------------+---------------------+-------+------+------+-------------------+ 3 rows in set (0.00 sec) MySQL [(none)]>
3. Read-write separation based on Mycat
MyCAT is based on Ali's open source Cobar products. It is a large open source database cluster for enterprise application development. It is an enterprise database which can be regarded as MySQL cluster to replace the expensive Oracle cluster. MyCAT does not depend on any commercial company. It never charges a fee and never closes the source.
MyCAT: 192.168.48.182 Master: 192.168.48.183 Slave: 192.168.48.184 Slave: 192.168.48.185
3.1 Install MyCAT
# Download mycat: [root@node02 ~]# cd /usr/src/ [root@node02 src]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz [root@node02 src]# # Unzip Mycat in the / usr/local directory: [root@node02 src]# tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /usr/local/ [root@node02 src]# [root@node02 src]# ll /usr/local/ total 0 drwxr-xr-x. 2 root root 6 Apr 11 2018 bin drwxr-xr-x. 2 root root 6 Apr 11 2018 etc drwxr-xr-x. 2 root root 6 Apr 11 2018 games drwxr-xr-x. 2 root root 6 Apr 11 2018 include drwxr-xr-x. 2 root root 6 Apr 11 2018 lib drwxr-xr-x. 2 root root 6 Apr 11 2018 lib64 drwxr-xr-x. 2 root root 6 Apr 11 2018 libexec drwxr-xr-x 7 root root 85 Jun 8 04:10 mycat drwxr-xr-x 8 7161 wheel 87 Dec 24 2013 mysql-proxy drwxr-xr-x. 2 root root 6 Apr 11 2018 sbin drwxr-xr-x. 5 root root 49 May 26 00:11 share drwxr-xr-x. 2 root root 6 Apr 11 2018 src [root@node02 src]# # Install java-jdk: [root@node02 src]# yum install java-1.8.0-openjdk -y [root@node02 src]# [root@node02 src]# java -version openjdk version "1.8.0_252" OpenJDK Runtime Environment (build 1.8.0_252-b09) OpenJDK 64-Bit Server VM (build 25.252-b09, mixed mode) [root@node02 src]# # Configure mycat environment variables: [root@node02 ~]# echo "export PATH=/usr/local/mycat/bin:$PATH" > /etc/profile.d/mycat.sh [root@node02 ~]# [root@node02 ~]# . /etc/profile.d/mycat.sh [root@node02 ~]# [root@node02 ~]# echo $PATH /usr/local/mycat/bin:/usr/local/mysql-proxy/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin [root@node02 ~]#
3.2 Authorize mycat on master database
# Authorization information is executed in the primary library and synchronized automatically from the library: [root@node03 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 13 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> grant all on *.* to "mycat-proxy"@"192.168.48.182" identified by "123456"; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]>
3.3 Configuring MyCAT
# To configureServer.xml
[root@node02 ~]# cp /usr/local/mycat/conf/server.xml{,.bak}
[root@node02 ~]# vi /usr/local/mycat/conf/server.xml
# Default administrative user, readable and writable:
<user name="mycat" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">super</property> ---schemas For logical Libraries
...
</user>
# Read-only users:
<user name="user">
<property name="password">user</property>
<property name="schemas">super</property> ---schemas For logical Libraries
<property name="readOnly">true</property>
</user>
# To configureSchema.xml
[root@node02 ~]# cp /usr/local/mycat/conf/schema.xml{,.bak}
[root@node02 ~]# vi /usr/local/mycat/conf/schema.xml
# Set up logical libraries and database nodes
<schema name="super" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
# Configure the backend real database corresponding to the database node (specify master's database ip and database name)
<dataNode name="dn1" dataHost="localhost1" database="superman" />
# Configure Read-Write Libraries and Balance (dataHost name specifies mastet ip)
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.48.183:3306" user="mycat-proxy"
password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.48.184:3306" user="mycat-proxy" password="123456" />
<readHost host="hostS2" url="192.168.48.185:3306" user="mycat-proxy" password="123456" />
</writeHost>
<writeHost host="hostS3" url="192.168.48.184:3306" user="mycat-proxy"
password="123456" />
</dataHost>
</mycat:schema>
# Notice the following three properties of the dataHost node
balance， switchType， writeType
balance="0", Do not turn on read-write separation, all read operations will be sent to the currently available writeHost Up.
balance="1"，All-out readHost and stand by writeHost participate in select Statement load balanced, simply put, when the two-master-two-slave mode(M1->S1，M2->S2，also M1 and M2 Mutual Preparedness)，Normally, M2,S1,S2 Participate select Statement load balancing.
balance="2"，All read operations are random on writeHost,readhost Distribute on.
balance="3"，Random distribution of all read requests to writeHost Lower readhost Execute, writeHost No reading pressure
writeType Representation Write Mode
writeType="0"，All actions sent to the first configured writehost
writeType="1"，Randomly send to all configured writehost
writeType="2"，Do not write
switchType Refers to switching modes, and there are currently four values:
switchType='-1'?Indicates no automatic switch
switchType='1'?Default value for automatic switch
switchType='2'?Be based on MySQL The state of master-slave synchronization determines whether to switch,The heartbeat statement is show slave status
switchType='3'Be based on MySQL galary cluster Switching mechanism for clusters (1).4.1)，The heartbeat statement is show status like 'wsrep%'. 3.3 Start MyCAT
[root@node02 ~]# mycat start Starting Mycat-server... [root@node02 ~]# [root@node02 ~]# netstat -ntlp|grep 66 tcp6 0 0 :::9066 :::* LISTEN 36231/java tcp6 0 0 :::8066 :::* LISTEN 36231/java [root@node02 ~]#
3.4 Connection Test
[root@node02 ~]# mysql -umycat -p123456 -P8066 -h127.0.0.1 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.29-mycat-1.6.7.1-release-20190627191042 MyCat Server (OpenCloudDB) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +----------+ | DATABASE | +----------+ | super | +----------+ 1 row in set (0.01 sec) MySQL [(none)]> # You can create a table in the back-end main library to continue the query table test: [root@node03 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 24 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use superman; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [superman]> MariaDB [superman]> show tables; +--------------------+ | Tables_in_superman | +--------------------+ | t1 | +--------------------+ 1 row in set (0.00 sec) MariaDB [superman]> MariaDB [superman]> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ 1 row in set (0.00 sec) MariaDB [superman]> # After inserting data from Library 1, continue the query: [root@node04 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 20 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use superman; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [superman]> show tables; +--------------------+ | Tables_in_superman | +--------------------+ | t1 | +--------------------+ 1 row in set (0.00 sec) MariaDB [superman]> MariaDB [superman]> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ 1 row in set (0.00 sec) MariaDB [superman]> MariaDB [superman]> insert into t1 values(2,"xiaowang"); Query OK, 1 row affected (0.00 sec) MariaDB [superman]> MariaDB [superman]> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | | 2 | xiaowang | +------+----------+ 2 rows in set (0.00 sec) MariaDB [superman]> # Insert a piece of data from Library 2 and continue the query: [root@node05 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 19 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use superman Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [superman]> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ 1 row in set (0.00 sec) MariaDB [superman]> MariaDB [superman]> insert into t1 values(3,"xiaozhang"); Query OK, 1 row affected (0.07 sec) MariaDB [superman]> MariaDB [superman]> select * from t1; +------+-----------+ | id | name | +------+-----------+ | 1 | xiaoming | | 3 | xiaozhang | +------+-----------+ 2 rows in set (0.00 sec) MariaDB [superman]>
Data cannot be found in the primary library and can be found through the proxy to verify that the read-write separation is successful.
[root@node02 ~]# mysql -umycat -p123456 -P8066 -h192.168.48.182 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.29-mycat-1.6.7.1-release-20190627191042 MyCat Server (OpenCloudDB) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> MySQL [(none)]> show databases; +----------+ | DATABASE | +----------+ | super | +----------+ 1 row in set (0.00 sec) MySQL [(none)]> MySQL [(none)]> use super; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MySQL [super]> MySQL [super]> show tables; +--------------------+ | Tables_in_superman | +--------------------+ | t1 | +--------------------+ 1 row in set (0.00 sec) MySQL [super]> MySQL [super]> select * from t1; +------+-----------+ | id | name | +------+-----------+ | 1 | xiaoming | | 3 | xiaozhang | +------+-----------+ 2 rows in set (0.01 sec) MySQL [super]> MySQL [super]> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ 1 row in set (0.00 sec) MySQL [super]> MySQL [super]> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | | 2 | xiaowang | +------+----------+ 2 rows in set (0.00 sec) MySQL [super]> MySQL [super]> select * from t1; +------+-----------+ | id | name | +------+-----------+ | 1 | xiaoming | | 3 | xiaozhang | +------+-----------+ 2 rows in set (0.00 sec) MySQL [super]> MySQL [super]> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ 1 row in set (0.00 sec) MySQL [super]> MySQL [super]> select * from t1; +------+-----------+ | id | name | +------+-----------+ | 1 | xiaoming | | 3 | xiaozhang | +------+-----------+ 2 rows in set (0.00 sec) MySQL [super]> MySQL [super]> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ 1 row in set (0.00 sec) MySQL [super]> MySQL [super]> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | | 2 | xiaowang | +------+----------+ 2 rows in set (0.00 sec) MySQL [super]>
3.5 Error Resolution
MySQL [superman]> show tables; ERROR 1184 (HY000): Invalid DataSource:0 There may be a problem with the back-end node, or it may be that the proxy cannot connect to the back-end node: You can test the connection problem by logging into the back-end database directly with the authorized user name and password on the proxy side first: [root@node02 conf]# mysql -umycat-proxy -h192.168.48.134 -p123456 ERROR 1129 (HY000): Host 'node3' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' #You can see that the proxy server is locked due to multiple errors, so the above error will also occur: Execute the following commands in the backend main library: mysqladmin flush-hosts Test again and the general problem will be solved.