How to use MyCat to implement MySQL master-slave read-write separation in Linux

Linux-Use MyCat to implement MySQL master-slave read-write separation

1. MySQL read-write separation

1. Overview of MySQL read-write separation

As the most widely used free database in the world, I believe that all engineers engaged in system operation and maintenance must have come into contact with MySQL. However, in the actual production environment, a single MySQL server as an independent database cannot meet the actual needs at all, whether in terms of security, high availability, high concurrency, etc.

Therefore, generally speaking, data is synchronized through主從復制（Master-Slave） , and then讀寫分離（MySQL-Proxy/Amoeba） is used to improve the concurrent load capacity of the database. This solution is deployed and implemented.

2. Working principle of read-write separation

The basic principle is to let the master database handle transactional add, modify, and delete operations (INSERT, UPDATE, DELETE), while the slave database handles SELECT query operations. Database replication is used to synchronize changes caused by transactional operations to slave databases in the cluster. Insert a picture here to describe the internal data exchange process:

3. Why do we need to separate reading and writing?

• Faced with increasing access pressure, the performance of a single server has become a bottleneck, and the load needs to be shared
• The master and slave are only responsible for their own writing and reading, which greatly alleviates the contention of X (write) lock and S (read) lock.
• The slave database can be configured with the MyISAM engine to improve query performance and save system overhead
• Increase redundancy and improve availability

3. How to achieve read-write separation

There are two ways to achieve this :

(1) Application layer implementation: The program implementation of the website. Application layer implementation refers to the implementation of read-write separation within the application and in the connector.

Advantages: Read-write separation is achieved within the application, and it can be used after installation; it reduces the difficulty of deployment to a certain extent; the access pressure is below a certain level and the performance is very good.
Disadvantages: Once the architecture is adjusted, the code must change accordingly; it is difficult to implement advanced applications, such as automatic database and table partitioning; it is not suitable for large-scale application scenarios.

(2) Middleware layer implementation: Middleware layer implementation refers to the implementation of read-write separation in external middleware programs.

4. Common middleware programs

(1) Cobar: A relational distributed system developed by Alibaba B2B that manages nearly 3,000 MySQL instances. It withstood the test in Alibaba, but later, due to the author's departure, no one maintained cobar, and Alibaba also developed tddl to replace cobar.
(2) MyCAT: Community enthusiasts conducted secondary development based on Alibaba cobar, solving some problems that cobar had at the time and adding many new features. Currently, the MyCAT community is very active and some companies are already using MyCAT. In general, the support is relatively high and will continue to be maintained.
(3) OneProxy: A database expert, Mr. Lou, the former leader of the Alipay database team, developed it based on the official MySQL proxy concept using C. OneProxy is a commercial middleware. Mr. Lou gave up some functional points and focused on performance and stability. Someone has tested it and said it is very stable under high concurrency.
(4) Vitess: This middleware is used in production by YouTube, but its architecture is very complex. Unlike previous middleware, using Vitess requires relatively large application changes. To use the API interface of the language it provides, we can refer to some of its design ideas.
(5) Kingshard: Kingshard was developed by Chen Fei, a former member of the 360Atlas middleware development team, in his spare time using the Go language. Currently, there are about three people involved in the development. At present, it is not a mature product that can be used and needs to be continuously improved.
(7) Atlas: The 360 ​​team rewrote Lua in C based on MySQL proxy. The original version supports sharding tables, and the sharding version has been released. I've seen some friends on the Internet say that it often crashes under high concurrency, so if you want to use it, you need to test it in advance.
MaxScale and MySQL Route: Both of these middleware are official. MaxScale was developed by mariadb (a version maintained by the original author of MySQL). The current version does not support sharding. MySQL Route is a middleware released by Oracle Corporation, the official MySQL company.
Advantages: The architecture design is more flexible. Some advanced controls can be implemented in the program, such as transparent horizontal splitting, failover, and monitoring. MySQL performance can be improved by technical means. The impact on business code is small and safe.
Disadvantages: Requires support from a certain development and operation team.

2. MyCAT Overview

1. What is MyCAT

A completely open source large database cluster for enterprise application development; an enhanced database that supports transactions and ACID and can replace MySQL; an enterprise-level database that can be regarded as a MySQL cluster to replace expensive Oracle clusters; a new SQL Server that integrates memory caching technology, NoSQL technology, and HDFS big data; a new generation of enterprise-level database products that combines traditional databases and new distributed data warehouses; a novel database middleware product.

2. MyCat service installation and configuration

MyCat provides compiled installation packages that support installation and running on Windows, Linux, Mac, Solaris and other systems. Official download homepage http://www.mycat.org.cn/

Architecture:

192.168.1.63 mycat
192.168.1.64 Master 192.168.1.65 Slave

Mycat requires JDK 1.7 or above:

Step 1: Download the jdk-8u191-linux-x64.tar.gz file

[root@xuegod63 local]# wget http://download.oracle.com/otn-pub/java/jdk/8u191-
b12/2787e4a523244c269598db4e85c51e0c/jdk-8u191-linux-x64.tar.gz 
#If there is a problem with the package downloaded on Linux, you need to copy the link to Windows, download it, and then upload it to Linux.

Step 2: Create a new /usr/java folder and unzip jdk-8u191-linux-x64.tar.gz into the directory

[root@xuegod63 local]# mkdir /usr/java
[root@xuegod63 local]# tar xf jdk-8u191-linux-x64.tar.gz -C /usr/java/

Step 3: Configure environment variables Add the following content to the bottom of /etc/profile:

[root@xuegod63 local]# vim /etc/profile.d/java.sh #Create the java.sh file in the /etc/profile.d/ directory and set the following content 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
[root@xuegod63 local]# source /etc/profile.d/java.sh #Make environment variables effective [root@xuegod63 local]# java -version #View java version 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)

Install mycat Under Linux, you can download Mycat-server-xxxxx.linux.tar.gz and unzip it in a directory. Note that there should be no spaces in the directory. Under Linux (Unix), it is recommended to put it in the usr/local/Mycat directory, as follows:

[root@xuegod63 ~]# tar -xf Mycat-server-1.5-RELEASE-0301083012-linux.tar.gz -C 
/usr/local/
[root@xuegod63 local]# ls /usr/local/mycat/

Modify the MyCAT user password below (for reference only):

[root@xuegod63 ~]# useradd mycat
[root@xuegod63 ~]# passwd mycat #Enter the password twice to succeed when modifying 

[root@xuegod63 ~]# chown -R mycat.mycat /usr/local/mycat #Modify permissions

The directory is explained as follows:

The bin program directory stores the window version and the Linux version. In addition to providing the version packaged as a service, it also provides the nowrap shell script command for easy selection and modification. Enter the bin directory:

Linux 下運行：./mycat console,首先要chmod +x *

Note: mycat supports the following commands: { console | start | stop | restart | status | dump }

Configuration files are stored in conf directory. server.xml is the configuration file for Mycat server parameter adjustment and user authorization. schema.xml is the configuration file for logical library definition, table and sharding definition. rule.xml is the configuration file for sharding rules. Some specific parameter information of sharding rules is stored as a separate file in this directory. If the configuration file is modified, Mycat needs to be restarted to take effect.

The lib directory mainly stores some jar files that mycat depends on.

The logs are stored in logs/mycat.log , one file per day. The log configuration is in conf/log4j.xml . According to your needs, you can adjust the output level to debug. At the debug level, more information will be output to facilitate troubleshooting.

3. MyCat service startup and startup settings

When MyCAT is deployed and started in Linux, you first need to configure MYCAT_HOME in the Linux system's environment variables. The operation is as follows:

[root@xuegod63 local]# vim /etc/profile.d/mycat.sh #Create the mycat.sh file in the /etc/profile.d directory and write the following.
MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH
[root@xuegod63 local]# source /etc/profile.d/mycat.sh #Make environment variables effective

If a MyCAT cluster is built on multiple Linux systems, you need to configure the mapping of other IP addresses and host names on the server where the MyCAT Server is located. The configuration is as follows:

vi /etc/hosts

For example: I have 3 machines with the following configuration: IP Hostname:

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

After editing, save the file.

The user account and authorization information of mycat are configured in the conf/server.xml file

[root@xuegod63 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 for application access account permissions-->
 Line 34 <user name="root"> #Define the administrator user, that is, the username for connecting to Mycat <property name="password">123456</property> #Password <property name="schemas">ha</property> #Define a logical library corresponding to the schema configuration file </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>

Edit the MyCAT configuration file schema.xml. The configuration information about dataHost is as follows :

[root@xuegod63 local]# mv /usr/local/mycat/conf/schema.xml 
/usr/local/mycat/conf/schema.xml.bak #Back up the original file.
[root@xuegod63 local]# vim /usr/local/mycat/conf/schema.xml Create <?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 >

Green is the name of the physically existing database. Note: The schema tag is used to define the logical library in the MyCat instance. Name: is followed by the logical library name. MyCat can have multiple logical libraries, and each logical library has its own related configuration. You can use schema tags to divide these different logical libraries.

The checkSQLschema attribute is false by default. The official document means whether to remove the database name in front of the table, "select * from db1.testtable". If set to true, db1 will be removed. However, if the name of db1 is not the name of the schema, it will not be removed, so the official recommendation is not to use this syntax. Also set to false by default.

sqlMaxLimit When the value is set to a certain number. For each SQL statement executed, if there is no limit statement, MyCat will automatically add the corresponding value. For example, if the value is set to 100 and "select * from test_table" is executed, the result will be "select * from test_table limit 100".

The dataNode tag defines the data nodes in MyCat, which is what we usually call data shards. A dataNode label is an independent data shard

.

Both writeHost and readHost tags specify the configuration of the backend database and are used to instantiate the backend connection pool. The only difference is that writeHost specifies the write instance and readHost specifies the read instance. Multiple writeHosts and readHosts can be defined in one dataHost. However, if the backend database specified by the writeHost goes down, all readHosts bound to this writeHost will be unavailable. On the other hand, since this writeHost is down, the system will automatically detect it and switch to the backup writeHost. The attributes of these two tags are the same

There are two parameters to note, balance and switchType. Among them, balance refers to the load balancing type, and currently there are 4 values:

(2) balance="0", the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost;
(2) balance="1", all readHost and stand by writeHost participate in the load balancing of the select statement. Simply put, in the dual-master and dual-slave mode (M1->S1, M2->S2, and M1 and M2 are mutually master and slave), under normal circumstances, M2, S1, and S2 all participate in the load balancing of the select statement;
(3) balance="2", all read operations are randomly distributed on writeHost and readhost;
(4) balance="3", all read requests are randomly distributed to the readhost corresponding to the writerHost for execution, and the writerHost does not bear the read pressure.

switchType refers to the switching mode, and currently there are 4 possible values:

(1) switchType='-1' means no automatic switching;
(2) switchType='1' default value, indicating automatic switching;
(3) switchType='2' determines whether to switch based on the status of MySQL master-slave synchronization. The heartbeat statement is show slave status;
(4) switchType='3' is based on the switching mechanism of MySQL galary cluster (suitable for cluster) (1.4.1), and the heartbeat statement is show status like 'wsrep%'.

After the above two steps are completed, you can go to the /usr/local/mycat/bin directory and execute ./mycat start to start the mycat service!

[root@xuegod63 local]# /usr/local/mycat/bin/mycat start
[root@xuegod63 ~]# cat /usr/local/mycat/logs/wrapper.log #View the log and start successfully 

4. Configure MySQL master-slave

Install mysql upload mysql-5.7.tar.gz to xuegod64

[root@xuegod64 ~]# systemctl start mysqld
[root@xuegod64 ~]# echo "validate-password=OFF">> /etc/my.cnf
[root@xuegod64 ~]# systemctl restart mysqld

Disable password strength audit plugin

[root@xuegod64 ~]# systemctl start mysqld
[root@xuegod64 ~]# echo "validate-password=OFF">> /etc/my.cnf
[root@xuegod64 ~]# systemctl restart mysqld

Change the root user password

[root@xuegod64 ~]# grep "password" /var/log/mysqld.log
[root@xuegod64 ~]# mysql -uroot -p'-NEjo1gbPllh'
mysql> set password for root@localhost = password('123456');
mysql> exit

Another machine

[root@xuegod65 ~]# tar xf mysql-5.7.tar.gz
[root@xuegod65 ~]# yum install -y ./mysql*.rpm

Disable password strength audit plugin

[root@xuegod65 ~]# systemctl start mysqld
[root@xuegod65 ~]# echo "validate-password=OFF">> /etc/my.cnf
[root@xuegod65 ~]# systemctl restart mysqld

Change the root user password

[root@xuegod65~]# grep "password" /var/log/mysqld.log
[root@xuegod65 ~]# mysql -uroot -p'%OrrfGwyM6tS'
mysql> set password for root@localhost = password('123456');
mysql> exit

Create a database and test data

[root@xuegod64 ~]# 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');
[root@xuegod64 ~]# mysqldump -uroot -p123456 -B ha >HA.sql #You can export the database

Transfer the exported database to the slave server

[root@xuegod64 ~]# scp HA.sql [email protected]:/root/

xuegod65 operates from the service: Import database and table for read operation:

[root@xuegod65 ~]# mysql -uroot -p123456<HA.sql
[root@xuegod64 ~]# vim /etc/my.cnf
log-bin=mysql-bin-master #Enable binary log server-id=1 #Local database ID mark binlog-do-db=ha #Repository that can be copied from the server, binary database name that needs to be synchronized binlog-ignore-db=mysql #Repository that cannot be copied from the server [root@xuegod64 ~]# systemctl restart mysqld
[root@xuegod64 ~]# mysql -uroot -p'123456'

Authorize mycat to log in to the database account and the master-slave replication account

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

Authorize mycat to log in to the database account and the master-slave replication account

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "123456";
mysql> grant replication slave on *.* to slave@"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 slave
mysql> show slave status\G #Check the status. There are two yeses. The master-slave synchronization is successful!
mysql> exit

Simulate slave failure: the slave server is down

[root@xuegod65 ~]# systemctl stop mysqld

Test read and write on the client

[root@xuegod63 ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066 
mysql> use ha;
mysql> select * from test;

This means that the read operation route has been switched to the master, and there is no external impact!

[root@xuegod65 ~]# systemctl start mysqld

Simulate master failure: the master server is down

[root@xuegod64 ~]# systemctl stop mysqld

Test read and write on the client

[root@xuegod63 ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066
mysql> use ha;
mysql> create table test1(id int);
ERROR 1184 (HY000): Connection refused #The master database is down, so write operations are not possible, but reading is not affected.
mysql> select * from test;

[root@xuegod64 ~]# systemctl start mysqld

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 |

5. Automatically switch to Slave node after actual node crashes

Configure the MM mode master-slave replication environment to enable 65 as the master of 64

[root@xuegod64 ~]# mysql -uroot -p'123456'
mysql> change master to 
master_host='192.168.1.65',master_user='slave',master_password='123456';
mysql> start slave; #Start slave
mysql> show slave status\G #Check the status. There are two yeses. The master-slave synchronization is successful!
mysql> exit

1. Optimize and adjust the mycat configuration file

[root@xuegod63 ~]# 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>

We previously configured switchType="-1" to turn off automatic switching, but we should avoid writing data to the slave node in a master-slave environment, otherwise it will cause inconsistency between the master and slave databases. If we use the MM mode, the master node will automatically switch to the backup node after a failure, which will not affect data writing.

[root@xuegod63 ~]# /usr/local/mycat/bin/mycat restart 
[root@xuegod64 ~]# systemctl stop mysqld
[root@xuegod63 ~]# 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 |

2. Stop the master node:

[root@xuegod64 ~]# systemctl stop mysqld
Mycat continues to insertmysql> insert into test values(5,'feng');

3. Restore the master node:

[root@xuegod64 ~]# systemctl start mysqld
mysql -uroot -p123456
select * from ha.test;

Regarding usage suggestions, in some CMS (content management system) systems, the underlying database code has been encapsulated. It is not advisable to use mycat at this time, because the encapsulated SQL statements will be incompatible with mycat. It is recommended that when developing a program, you should consider whether the way to connect to MySQL is compatible with mycat. Or choose other middleware such as mysql-proxy.

The above is the details of how Linux uses MyCat to achieve MySQL master-slave read-write separation. For more information about Linux MySQL, please pay attention to other related articles on 123WORDPRESS.COM!