MySQL Overview of read write separation

MySQL As the most widely used free database in the world , I believe that all engineers engaged in system operation and maintenance must have contacted . But in the actual production environment , By a single MySQL As an independent database, it can not meet the actual needs , Whether it's in security , High availability and high concurrency .

therefore , Generally speaking, it is through master-slave replication （Master-Slave） To synchronize data , Read and write again （MySQL-Proxy/Amoeba） To improve the concurrent load capacity of the database , Such a scheme for deployment and implementation .

Working principle of read-write separation

The basic principle is to make the primary database deal with transaction 、 Change 、 Delete operation （INSERT、UPDATE、DELETE）, And processing from the database SELECT Query operation . Database replication is used to synchronize changes caused by transactional operations to the slave database in the cluster . Internal data exchange process ：

Why to separate reading from writing

1. In the face of increasing access pressure , The performance of a single server becomes a bottleneck , Need to share the load
2. The master and the slave are only responsible for their own writing and reading , A great deal of relief X（ Write ） Lock and S（ read ） Lock contention
3. Configurable from library myisam engine , Improve query performance and save system overhead
4. Add redundancy , Improve availability

The way to separate read and write

There are generally two ways to achieve

1. Application layer implementation , The program implementation of the website

Application layer implementation refers to the separation of read and write within the application and in the connector advantage ： Read write separation is implemented inside the application , Installation can use ; Reduce the difficulty of deployment ; Access pressure is below a certain level , Performance is very good . shortcoming ： Once the architecture is adjusted , The code will change ; Difficult to implement advanced applications , Such as automatic warehouse distribution , table ; Not applicable to large application scenarios .

2. Middleware layer implementation ：

The implementation of middleware layer refers to the separation of reading and writing in external middleware programs

Common middleware programs

1. Cobar：

Alibaba B2B Developed relational distributed system , Management is close to 3000 individual MySQL example . Ali stood the test , Later, due to the author's walking away cobar No one maintains 了 , Ali also developed tddl replace cobar.

2. MyCAT：

Community enthusiasts in Ali cobar On the basis of the second development , It's solved cobar Some problems existed at that time , And a lot of new features have been added to it . at present MyCAT The community is very active , Some companies are already using MyCAT. Overall, support is better than Higher , It will be maintained all the time .

3. OneProxy：

Database giant , Former Alipay database team leader building development , be based on mysql official Of proxy The use of ideas c developable ,OneProxy It's a middleware for commercial charging , The general manager of the building has omitted some function points , Focus on performance and stability . Some people have tested that it is very stable at high and high .

4. Vitess：

This middleware is Youtube Produce in use , But the architecture is complex . Different from previous middleware , Use Vitess Application changes are relatively large , Use the language he provides API Interface , We can learn from some of his design ideas .

5. Kingshard：

Kingshard Before 360Atlas Chen Fei of the middleware development team uses her spare time use go Language development , At present, there are 3 about , At present, it is not a mature product , It needs to be constantly improved .

6. Atlas：

360 The team is based on mysql proxy hold lua use C rewrite . The original version supports split tables , At present, the database and table versions have been released . I see some friends on the Internet who often say that they will often hang up when they are high and send them , If you want to use it, you need to test it in advance .

7. MaxScale And MySQL Route：

Both middleware are official ,MaxScale yes mariadb (MySQL A version maintained by the original author ) R & D , The current version does not support sub database and sub table .MySQL Route It's now MySQL official Oracle A middleware released by the company .

advantage ： More flexible architecture design Some advanced control can be realized in the program , Such as ： Transparent horizontal split ,failover, monitor Can rely on technical means to improve mysql performance Little impact on business code , It's also safe

shortcoming ： It needs the support of a certain development and operation team .

What is? MyCAT

A completely open source , Large database cluster for enterprise application development ; Support transactions 、ACID、 Can replace MySQL Enhanced database of ; One can be regarded as MySQL Enterprise database of cluster , To replace the expensive Oracle colony ; A fusion of memory cache technology 、NoSQL technology 、HDFS New big data SQL Server; A new generation of enterprise database products combining traditional database and new distributed data warehouse ; A new database middleware product .

MyCat Service installation and configuration

MyCat A compiled installation package is provided , Support Windows、Linux、Mac、Solaris And so on . Official download home page www.mycat.org.cn/

framework

192.168.1.63 mycat
192.168.1.64 Lord
192.168.1.65 from
 Copy code 

Mycat Need to install JDK 1.7 Or above ： First step ： download jdk-8u191-linux-x64.tar.gz file

[[email protected] local]# wget http://download.oracle.com/otn-pub/java/jdk/8u191-
b12/2787e4a523244c269598db4e85c51e0c/jdk-8u191-linux-x64.tar.gz
# If in Linux There is a problem with the package downloaded from , You need to copy the link to Windows Download and upload to Linux.
 Copy code 

The second step ： newly build /usr/java Folder , take jdk-8u191-linux-x64.tar.gz Unzip into this directory

[[email protected] local]# mkdir /usr/java
[[email protected] local]# tar xf jdk-8u191-linux-x64.tar.gz -C /usr/java/
 Copy code 

The third step ： Configure environment variables stay /etc/profile Add the following at the bottom ：

[[email protected] local]# vim /etc/profile.d/java.sh #/etc/profile.d/ Create under directory 
java.sh File and specify the following contents
JAVA_HOME=/usr/java/jdk1.8.0_191
PATH=$JAVA_HOME/bin:$PATH
CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
export PATH JAVA_HOME CLASSPATH
[[email protected] local]# source /etc/profile.d/java.sh # Enable environment variables 
[[email protected] local]# java -version# see java edition 
java version "1.8.0_191"
Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)
 Copy code 

install mycat Linux You can download Mycat-server-xxxxx.linux.tar.gz Unzip in a directory , Note that the directory cannot have spaces , stay Linux(Unix) Next , Advice on usr/local/Mycat Under the table of contents , as follows ：

[[email protected] ~]# tar -xf Mycat-server-1.5-RELEASE-0301083012-linux.tar.gz -C 
/usr/local/
[[email protected] local]# ls /usr/local/mycat/
 Copy code 

The following changes MyCAT User password ( For reference only )：

[[email protected] ~]# useradd mycat
[[email protected] ~]# passwd mycat # Enter the password twice when modifying success 
 Copy code 

[[email protected] ~]# chown -R mycat.mycat /usr/local/mycat # Modify the permissions

The table of contents explains as follows ： bin Program directory , Deposited window Version and linux edition , In addition to providing versions packaged as services , Also provided nowrap Of shell Script command , Convenient for you to choose and modify , Enter into bin Catalog ：

Linux Run under ：./mycat console, First of all chmod +x *

notes ：mycat Supported commands { console | start | stop | restart | status | dump }

conf Store the configuration file in the directory ,server.xml yes Mycat Configuration files for server parameter adjustment and user authorization ,schema.xml It is the configuration file of logical library definition and table and fragment definition ,rule.xml It is the configuration file of fragmentation rules , The specific parameter information of the fragmentation rule is stored separately as a file , It's also in this catalog , Configuration file modification , Need to restart Mycat take effect .

lib It is mainly stored in the directory mycat Depending on jar file .

The log is stored in logs/mycat.log in , One file a day , Log configuration is in conf/log4j.xml in , According to your own needs , You can adjust the output level to debug, stay debug Below grade , It will output more information , It's easy to troubleshoot .

MyCat Service startup and startup settings

MyCAT stay Linux When the deployment starts in , The first thing you need to do is Linux In the environment variable of the system MYCAT_HOME, The operation mode is as follows ：

[[email protected] local]# vim /etc/profile.d/mycat.sh # stay /etc/profile.d Create under directory 
mycat.sh file , And write it as follows .
MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH
[[email protected] local]# source /etc/profile.d/mycat.sh # Enable environment variables 
 Copy code 

If it's on multiple stations Linux Built in the system MyCAT colony , That needs to be in MyCAT Server On the server where it is configured for other IP And host name mapping , The configuration is as follows ：

vi /etc/hosts
 Copy code 

for example ： I have a 3 Taiwan machine , The configuration is as follows ： IP Host name ：

192.168.1.63 xuegod63.cn
192.168.1.64 xuegod64.cn
192.168.1.65 xuegod65.cn
 Copy code 

After editing , Save the file .

mycat The user account and authorization information of is in conf/server.xml Configuration in file

[[email protected] local]# vim /usr/local/mycat/conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser</property>
</system>
<!-- The following settings are the application access account permissions -->
34 That's ok <user name="root"> # Define the administrator user , That is, connection Mycat Username
<property name="password">123456</property> # password
<property name="schemas">ha</property> # Define a logical library , And schema The configuration file corresponds to
</user>
<!-- The following settings apply read-only account permissions -->
<user name="user">
<property name="password">user</property>
<property name="schemas">ha</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
 Copy code 

edit MyCAT Configuration file for schema.xml, About dataHost The configuration information of is as follows ：

[[email protected] local]# mv /usr/local/mycat/conf/schema.xml 
/usr/local/mycat/conf/schema.xml.bak # Back up the original file .
[[email protected] local]# vim /usr/local/mycat/conf/schema.xml newly build 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="ha" checkSQLschema="false" sqlMaxLimit="100"
dataNode='dn1'>
</schema>
<dataNode name="dn1" dataHost="dthost" database="ha"/>
<dataHost name="dthost" maxCon="500" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="-1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="xuegod64.cn" url="192.168.1.64:3306" user="mycat"
password="123456">
</writeHost>
<writeHost host="xuegod65.cn" url="192.168.1.65:3306" user="mycat"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
 Copy code 

green ha Is the name of the physically existing database annotation ： schema Labels are used to define MyCat The logical library in the instance ,name: Then the logical library name MyCat There can be multiple logical Libraries , Each logical library has its own configuration . have access to schema Tags to divide these different logical Libraries .

checkSQLschema This attribute defaults to false, The official document means whether to remove the name of the database in front of the table ,”select * from db1.testtable” , Set to true It will be removed db1. But if db1 Its name is not schema The name of , Then it won't be removed , Therefore, it is officially recommended not to use this grammar . At the same time, the default setting is false.

sqlMaxLimit When the value is set to a value . Each executed SQL sentence , If you don't add limit sentence ,MyCat It will also automatically add the corresponding value . For example, set the value to 100, perform ”select * from test_table”, The effect is “selelct * from test_table limit 100”.

dataNode The label defines MyCat Data nodes in , That's what we usually call data fragmentation . One dataNode A tag is a separate piece of data .

attribute	explain
Name	Define the name of the data node , The name needs to be unique
dataHost	This attribute is used to define which database instance the partition belongs to
Database	This attribute is used to define which specific database instance of the partition attribute is a specific database

dataHost This tag defines the specific database instance 、 Read write separation configuration and heartbeat statement

label	explain
name	Unique identification dataHost label , For the upper label
maxCon	Specifies the maximum connection for each read-write instance connection pool .
minCon	Specifies the minimum connection for each read-write instance connection pool , Initialize connection pool size .
dbType	Specify the database type of the back-end connection , Binary is currently supported mysql agreement , There are other uses JDBC Connected database . for example ：mongodb、oracle、spark etc. .
dbDriver	Specify the connection to the back-end database Driver, The values currently available are native and JDBC. Use native Words , Because the value is binary mysql agreement , So you can use mysql and maridb. Other types of databases need to use JDBC Drive to support .
switchType	“-1” Does not automatically switch ; “1” The default value is , Automatic switch ; “2” be based on MySQL The state of master-slave synchronization determines whether to switch The heartbeat statement is show slave status “3” be based on MySQL galary cluster Switching mechanism of （ Suitable for clustering ）（1.4.1） The heartbeat statement is show status like ‘wsrep%’.

heartbeat The tag indicates the statement used for heartbeat checking with the back-end database .

writeHost /readHost Both tags specify the configuration of the backend database , Used to instantiate the backend connection pool . The only difference is ,writeHost Specify write instance 、readHost Specify a read instance . In a dataHost You can define more than one writeHost and readHost. however , If writeHost The specified backend database is down , So this writeHost All of the binding readHost Will not be available . On the other hand , Because of this writeHost Downtime , The system will automatically detect , And switch to the standby writeHost Up . These two tags have the same properties

attribute	explain
Host	Used to identify different instances , commonly writeHost We use M1,readHost We use it S1.
url	Back end instance connection address .Native： Address ： port JDBC：jdbc Of url
password	The password required by the backend storage instance
user	The user name required by the backend storage instance
weight	The weight Configure in readhost As the weight of the read node

There are two parameters to note ,balance and switchType. among ,balance Refers to the type of load balancing , The current value Yes 4 Kind of ：

1. balance="0", Do not turn on the read-write separation mechanism , All read operations are sent to the currently available writeHost On ;
2. balance="1", All of the readHost And stand by writeHost Participate in select Statement load balancing , ordinary

say , When dual master dual slave mode (M1->S1,M2->S2, also M1 And M2 Prepare for each other ), Under normal circumstances ,M2、S1、S2 all Participate in select Statement load balancing ; 3. balance="2", All the reading operations are random in writeHost、readhost To distribute ; 4. balance="3", All read requests are randomly distributed to wiriterHost Corresponding readhost perform ,writerHost No Bear the pressure of reading .

switchType Refers to the mode of switching , At present, there are also 4 Kind of ：

1. switchType='-1' Does not automatically switch ;
2. switchType='1' The default value is , Indicates automatic switching ;
3. switchType='2' be based on MySQL The state of master-slave synchronization determines whether to switch , The heartbeat statement is show slave status;
4. switchType='3' be based on MySQL galary cluster Switching mechanism of （ Suitable for clustering ）（1.4.1）, The heartbeat statement is show status like 'wsrep%'.

After the above two steps of configuration , You can go there. /usr/local/mycat/bin Execute under directory ./mycat start, You can start mycat service ！

[[email protected] local]# /usr/local/mycat/bin/mycat start
[[email protected] ~]# cat /usr/local/mycat/logs/wrapper.log # Check the log , Successful launch 
 Copy code 

To configure MySQL Master-slave

install mysql Upload mysql-5.7.tar.gz To xuegod64

[[email protected] ~]# scp mysql-5.7.tar.gz [email protected]:/root/
[[email protected] ~]# tar xf mysql-5.7.tar.gz
[[email protected] ~]# yum install -y ./mysql*.rpm
 Copy code 

Close the password strength audit plug-in

[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# echo "validate-password=OFF">> /etc/my.cnf
[[email protected] ~]# systemctl restart mysqld
 Copy code 

modify root User password

[[email protected] ~]# grep "password" /var/log/mysqld.log
[[email protected] ~]# mysql -uroot -p'-NEjo1gbPllh'
mysql> set password for [email protected] = password('123456');
mysql> exit
 Copy code 

Another machine

[[email protected] ~]# tar xf mysql-5.7.tar.gz
[[email protected] ~]# yum install -y ./mysql*.rpm
 Copy code 

Close the password strength audit plug-in

[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# echo "validate-password=OFF">> /etc/my.cnf
[[email protected] ~]# systemctl restart mysqld
 Copy code 

modify root User password

[[email protected]~]# grep "password" /var/log/mysqld.log
[[email protected] ~]# mysql -uroot -p'%OrrfGwyM6tS'
mysql> set password for [email protected] = password('123456');
mysql> exit
 Copy code 

Create database and test data

[[email protected] ~]# mysql -uroot -p123456
mysql> create database ha;
mysql> use ha;
mysql> create table test(id int,name varchar(20));
mysql> insert into test values(1,'man');
[[email protected] ~]# mysqldump -uroot -p123456 -B ha >HA.sql # You can export the database 
 Copy code 

Transfer the exported database to the slave server

[[email protected] ~]# scp HA.sql [email protected]:/root/
 Copy code 

xuegod65 Operate from the service ： Import databases and tables , Used to implement read operations ：

[[email protected] ~]# mysql -uroot -p123456<HA.sql
[[email protected] ~]# vim /etc/my.cnf
log-bin=mysql-bin-master # Enable binary logging
server-id=1 # Native database ID Mark
binlog-do-db=ha # Libraries that can be copied from the server , Binary database name that needs to be synchronized
binlog-ignore-db=mysql # Libraries that cannot be copied from the server
[[email protected] ~]# systemctl restart mysqld
[[email protected] ~]# mysql -uroot -p'123456'
 Copy code 

Authorized to mycat The account used to log in to the database and the master-slave replication account

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "123456";
mysql> grant replication slave on *.* to [email protected]"192.168.1.%" identified by "123456";
mysql> exit
[[email protected] ~]# vim /etc/my.cnf
log-bin=mysql-bin-slave # Enable binary logging
server-id=2 # Native database ID Mark
binlog-do-db=ha # Libraries that can be copied from the server , Binary database name that needs to be synchronized
binlog-ignore-db=mysql # Libraries that cannot be copied from the server
[[email protected] ~]# systemctl restart mysqld
[[email protected] ~]# mysql -uroot -p'123456'
 Copy code 

Authorized to mycat The account used to log in to the database and the master-slave replication account

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "123456";
mysql> grant replication slave on *.* to [email protected]"192.168.1.%" identified by "123456";
mysql> change master to
master_host='192.168.1.64',master_user='slave',master_password='123456';
mysql> start slave; # start-up slave
mysql> show slave status\G # Check the status , There are two yes Master slave synchronization succeeded ！
mysql> exit
 Copy code 

simulation slave fault ： Hung up from the server

[[email protected] ~]# systemctl stop mysqld
 Copy code 

Test read and write on the client

[[email protected] ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066 
mysql> use ha;
mysql> select * from test;
 Copy code 

Indicates that the route of the read operation is switched to master Yes , There is no external impact ！

[[email protected] ~]# systemctl start mysqld
 Copy code 

simulation master fault ： The main server is down

[[email protected] ~]# systemctl stop mysqld
 Copy code 

Test read and write on the client

[[email protected] ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066
mysql> use ha;
mysql> create table test1(id int);
ERROR 1184 (HY000): Connection refused # The main database stopped , You can't write , But it doesn't affect reading .
mysql> select * from test;
 Copy code 

[[email protected] ~]# systemctl start mysqld
 Copy code 

Read only user login test

mysql -u user -puser -h 192.168.1.201 -P8066
mysql> insert into test values(5,'feng');
ERROR 1495 (HY000): User readonly
mysql> select * from ha.test;
+------+--------+
| id | name |
+------+--------+
| 1 | cd |
 Copy code 

Automatic switching after actual node downtime Slave node

To configure MM Mode master-slave replication environment Enable 65 by 64 The Lord of

[[email protected] ~]# mysql -uroot -p'123456'
mysql> change master to
master_host='192.168.1.65',master_user='slave',master_password='123456';
mysql> start slave; # start-up slave
mysql> show slave status\G # Check the status , There are two yes Master slave synchronization succeeded ！
mysql> exit
 Copy code 

mycat Profile optimization and adjustment .

[[email protected] ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="ha" checkSQLschema="false" sqlMaxLimit="100"
dataNode='dn1'>
</schema>
<dataNode name="dn1" dataHost="dthost" database="ha"/>
<dataHost name="dthost" maxCon="500" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="xuegod64.cn" url="192.168.1.64:3306" user="mycat"
password="123456">
</writeHost>
<writeHost host="xuegod65.cn" url="192.168.1.65:3306" user="mycat"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
 Copy code 

Let's configure switchType="-1" Auto switch off , However, in the master-slave environment, we should avoid writing data to the slave node , Otherwise, the data in the master-slave database will be inconsistent , If we use MM Mode, the primary node will automatically switch to the standby node after failure , Data writing is not affected at this time .

[[email protected] ~]# /usr/local/mycat/bin/mycat restart
[[email protected] ~]# systemctl stop mysqld
[[email protected] ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066
MySQL [(none)]> insert into ha.test values(666,'mk');
MySQL [(none)]> select * from ha.test;
+------+---------+
| id | name |
+------+---------+
| 1 | man |
| 666 | mk |
 Copy code 

Stop the master node ：

[[email protected] ~]# systemctl stop mysqld
Mycat Continue to insert
mysql> insert into test values(5,'feng');
 Copy code 

Restore master

[[email protected] ~]# systemctl start mysqld
mysql -uroot -p123456
select * from ha.test;
 Copy code 

Suggestions for use , In some CMS（ Content management system ） The underlying database code in the system has been encapsulated , At this time to use mycat Is not desirable , Because after packaging sql The statement will and mycat Are not compatible , The suggestion is that connection should be considered in program development mysql And mycat Is compatible with each other . Or choose other middleware, such as mysql-proxy.