MySQL read-write separation

The principle of separation of MYSQL, speaking, reading and writing is actually let Master transactional database to add, DELETE, modify, UPDATE operations (CREATE, INSERT, UPDATE, DELETE), and let the Slave database processing the SELECT operation, separation premise is based on MYSQL master-slave replication MYSQL, speaking, reading and writing. In this way, data can be modified on the Master. After synchronization on the Slave, the WEB application can read data on the Slave.

1.1 Implementation of READ/write Separation:

MYSQL read/write separation can be achieved through third-party plug-ins or code development and modification. There are four common ways to achieve read/write separation:

Amoeba reading and writing separation; Mysql-proxy read/write separation;

Mycat read-write separation;

Based on program read and write separation (high efficiency, difficult implementation, development and change code);

Amoeba is a proxy that uses MySQL as the underlying data storage and provides MySQL protocol interface for WEB and APP applications. It centrally responds to WEB application requests and sends SQL requests to specific databases for execution according to preset rules. Based on this, load balancing, read/write separation, and high availability can be achieved.

Mysql-proxy is an official Mysql middleware service provided by Mysql, which supports numerous client connections. At the same time, the back-end can connect to several mysql-server servers. Mysql-proxy itself is based on the Mysql protocol, and the client connecting to mysql-proxy does not need to modify any Settings. It is the same as normal connection to MYSQL Server, no modification of the program code.

Mycat is a database middleware developed on the basis of alibaba’s 12-year-old open source Cobar. It is a component between the database and the application layer in the architecture system, and is transparent to the application layer. It can achieve read and write separation, separate libraries and separate tables.

2. Implement read/write separation based on mysql-proxy:

Proxy: 192.168.75.133 Master: 192.168.75.134 slave: 192.168.75.135Copy the code

2.1 Working Principle Diagram:

2.2 the proxy configuration:

Proxy can be deployed on the same server as mysql or deployed on another independent server.

Mysql-proxy: Wget http://mirrors.163.com/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux- el6 - x86-64 - bit. Tar. Gz # extract: Tar xf mysql-proxy-0.8.4- linux-el6-x86-644.tar. gz mv mysql-proxy-0.8.4- linux-el6-x86-644.tar. gz /usr/local/mysql-proxy # Configure environment variables:  [root@node3 src]# echo "export PATH=/usr/local/mysql-proxy/bin:$PATH" > /etc/profile.d/mysql-proxy.sh [root@node3 src]# /etc/profile.d/mysql-proxy.sh [root@node3 src]# useradd -r mysql-proxy [root@node3 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.75.134:3306" - proxy - read - only - backend - addresses = "192.168.75.135: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 @ node3 SRC] # netstat NTLP | grep TCP 0 0 0.0.0.0:40 0.0.0.0:4040 * 1348 / mysql - LISTEN proxy TCP 0 0 0.0.0.0:4041 1348 / mysql - proxy 0.0.0.0: * LISTENCopy the code

2.3 Startup Parameters:

--help-all: fetch all the help information; --proxy-address=host:port: indicates the address and port monitored by the proxy service. The default value is 4040. --admin-address=host:port: indicates the address and port monitored by the management module. The default value is 4041. --proxy-backend-addresses=host:port: indicates the address and port number of the back-end mysql server. --proxy-read-only-backend-addresses=host:port: addresses and ports of the back-end read-only mysql server; --proxy-lua-script=file_name: lua script to perform mysql proxy function; --daemon: start mysql-proxy in daemon mode; --keepalive: try to restart mysql-proxy when it crashes; --log-file=/path/to/log_file_name: log file name; --log-level=level: indicates the log level. --log-use-syslog: records logs based on syslog. Plugins = plugins: plugins loaded at mysql-proxy startup; --user=user_name: user running mysql-proxy; --defaults-file=/path/to/conf_file_name: indicates the default configuration file path. The configuration segment is identified by [mysql-proxy]. --proxy-skip-profiling: disabling profiles; --pid-file=/path/to/pid_file_name: specifies the process name.Copy the code

2.4 start the master/slave

systemctl start mariadb
Copy the code

2.5 Viewing read/Write Separation Status:

Based on port 4041 to view the status of read/write separation, log in to management port 4041

2.5 authorized proxy:

Grant all on *.* to "mysql-proxy"@"192.168.75.133" identified by "123456";Copy the code

2.6 Creating Data by Proxy:

Insert data through proxy port 4040, the SQL statement will go to master, and the master state can be activated:

Mysql -h192.168.75.133 -umysql -proxy-p123456 -p4040 -e "create database lutixia charset UTF8;"Copy the code

On the 4041 management port, look again:

2.7 Querying data by proxy:

Select lutixia from the slave database, create a table, and insert data into the slave database:  MariaDB [(none)]> use lutixia Database changed MariaDB [lutixia]> create table t1( id int, name varchar(20) ); Query OK, 0 rows affected (0.00 SEC) MariaDB [lutixia]> INSERT T1 values(1,"xiaoming"); Query OK, 1 row affected (0.00 SEC)Copy the code

Query data through proxy port 4040, the SQL statement will go to slave, so the slave state can be activated:

# Execute more times! [root@node4 ~]# mysql-h192.168.75.133-umysql-proxy-p123456-p4040-e "select * from lutixia.t1;" +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+Copy the code

On the 4041 management port, look again:

mysql> select * from backends; +-------------+---------------------+-------+------+------+-------------------+ | backend_ndx | address | state | type |  uuid | connected_clients | +-------------+---------------------+-------+------+------+-------------------+ | 1 | 192.168.75.134:3306 | up | rw | NULL | 0 | | 2 | 192.168.75.135:3306 | up | ro | NULL | | 0 + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

3. Achieve read and write separation based on Mycat:

Mycat is based on the Cobar product of Ali open source, a completely open source, large database cluster for enterprise application development, an enterprise database can be regarded as MySQL cluster, used to replace the expensive Oracle cluster, Mycat does not rely on any commercial company, never charge, never closed source!

Mycat: 192.168.75.133 master: 192.168.75.134 slave: 192.168.75.135Copy the code

3.1 installation mycat:

# download mycat: Wget http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042- Linux. Tar. Gz # extract: Tar xf mycat-server-1.6.7.1 -release-20190627191042-linux.tar.gz -c /usr/local/# # yum install java-1.8.0-openjdk -y # yum install java-1.8.0-openjdk -y #  echo "export PATH=/usr/local/mycat/bin:$PATH" > /etc/profile.d/mycat.sh . /etc/profile.d/mycat.shCopy the code

Authorized mycat 3.2:

Grant all on *.* to "mycat-proxy"@"192.168.75.133" identified by "123456";Copy the code

3.3 configuration mycat:

Server. XML:  <user name="mycat" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">lutixiadb</property> ... </user>  <user name="user"> <property name="password">user</property> <property name="schemas">lutixiadb</property> <property <schema name="lutixiadb" checkSQLschema="false" </property> </user> SqlMaxLimit ="100" dataNode="dn1"> </schema> <dataNode name="dn1" dataHost="localhost1" database="students"  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" 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.75.134:3306" user=" mycat-proxy" password="123456"> <! -- can have multi read hosts --> <readHost host="hostS1" URL ="192.168.75.135:3306" user="mycat-proxy" password="123456" /> </writeHost> </dataHost>Copy the code

3.3 start the mycat:

mycat	start
Copy the code

3.4 Connection Test:

Mysql -umycat -p123456 -p8066 -h127.0.0.1 mysql [(none)]> show databases; + -- -- -- -- -- -- -- -- -- -- -- + | DATABASE | + -- -- -- -- -- -- -- -- -- -- -- + | lutixiadb | + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC) # can create a table in the back-end main library, continue to query table test:  MySQL [(none)]> use lutixiadb; 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 [lutixiadb]> show tables; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Tables_in_students | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | t1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01) MySQL [lutixiadb]> select * from t1; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + | 1 | xiaowang | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 1 rows in the set (0.00 SEC)Copy the code

If no data can be found in the primary database, the proxy can be found to verify that the read/write separation is successful.

3.5 Error Resolution:

MySQL [lutixiadb]> show tables; ERROR 1184 (HY000): Invalid DataSource:0 Either the backend node is faulty or the proxy cannot connect to the backend node. You can log in to the backend database on the proxy using the authorized user name and password to test the connection problem. [root@node3 conf]# mysql-umycat-proxy-h192.168.75.134-p123456 ERROR 1129 (HY000): Host 'node3' is blocked because of many connection errors; Unblock with 'mysqladmin flush-hosts' # Mysqladmin flush-hosts tests again to resolve the general problem.Copy the code