编程知识 cdmana.com

Glacier, can you tell me how to realize the read-write separation of MySQL with MYCAT?

In the high concurrency scenario, the author has developed it , Simple to offer 、 Stable 、 Extensible delayed message queuing framework , It has precise timing task and delay queue processing function . Since the open source for more than half a year , It has successfully provided precise timing scheduling scheme for more than ten small and medium-sized enterprises , It has withstood the test of production environment . In order to benefit more children's shoes , Now give the open source framework address :

https://github.com/sunshinelyz/mykit-delay

PS: Welcome to Star Source code , It's fine too pr Your blazing code .

Write it at the front

With the continuous development of system business ,MySQL The amount of data in the database is also increasing , When the amount of data reaches a certain order of magnitude ,MySQL Master-slave replication of 、 Read / write separation 、 Sub database and sub table has become an inevitable trend of architecture . see , A little friend asked me if I could talk about Mycat How to achieve MySQL Separation of reading and writing . So I arranged this article . at present , I also develop and maintain in my spare time Mycat Source code , Partners are learning and using Mycat when , You can also add me wechat when you encounter problems 【sun_shine_lyz】 Communicate with each other ! notes : The article has included : GitHub:https://github.com/sunshinelyz/technology-binghe Gitee:https://gitee.com/binghe001/technology-binghe

Environmental statement

What we need to pay attention to here is MySQL The master-slave replication of is dependent on MySQL Master slave replication mechanism ,Mycat Not responsible for MySQL Master-slave replication of , of MySQL Master slave replication configuration for , You can refer to the blog post 《MySQL And —— Configuration of master-slave replication 》, Okay , Let's move on to today's topic .

Set up the environment

operating system :CentOS-6.5-x86_64-bin-DVD1.iso

JDK edition :jdk1.8

Mycat edition :Mycat-server-1.6.1-RELEASE-20201208215510-linux.tar.gz ( Download source code self compiled )

Mycat node IP:192.168.209.133 Host name :liuyazhuang133 Host configuration :4 nucleus CPU、4G Memory

MySQL edition :mysql-5.6.32.tar.gz

Master node IP:192.168.209.131 Host name :liuyazhuang131 Host configuration :4 nucleus CPU、4G Memory

From the node IP:192.168.209.132 Host name :liuyazhuang132 Host configuration :4 nucleus CPU、4G Memory

Mycat Deployment architecture

Mycat The separation of read and write is based on the back end MySQL It is realized by the master-slave synchronization of cluster , and Mycat Provides the distribution function of statements .MyCat1.4 Start supporting MySQL The read-write separation mechanism of master-slave replication state binding , Make reading safer .

Let's see Mycat The overall deployment architecture of .

Mycat Installation

Set host name and IP Mapping to host name

# vim /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=liuyazhuang133
# vim /etc/hosts
127.0.0.1 liuyazhuang133
192.168.209.131 liuyazhuang131
192.168.209.132 liuyazhuang132
192.168.209.133 liuyazhuang133

To configure Java environment variable

because Mycat Yes, it is Java Developed , therefore Mycat Operation requires installation JDK( Exactly JRE That's enough ), And need JDK1.7 Or above

# vim /etc/profile
## java env
export JAVA_HOME=/usr/local/java/jdk1.8
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib/rt.jar
export PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
# source /etc/profile
# java -version

Upload Mycat Installation package

Upload Mycat-server-1.6.1-RELEASE-20201208215510-linux.tar.gz To MyCat In the server /home/mycat Catalog , And unzip and move to /usr/local/mycat Catalog

$ tar -zxvf Mycat-server-1.6.1-RELEASE-20201208215510-linux.tar.gz
# mv /home/mycat/mycat  /usr/local/
# cd /usr/local/mycat/
# ll

Set up Mycat Environment variables of

# vim /etc/profile
## mycat env
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin
# source /etc/profile

To configure Mycat

confirm MySQL Master slave replication is normal

In the configuration Mycat front , Please make sure the MySQL The master-slave replication installation configuration for is complete and running normally .MySQL The synchronization of master-slave data is in MySQL Middle configuration ,Mycat Not responsible for data synchronization .

Add :

  • MySQL Master slave replication configuration , If synchronous replication of functions or stored procedures is involved , Need to be in /etc/my.cnf Medium [mysqld] Add configuration in section log_bin_trust_function_creators=true Or set it in the client set global log_bin_trust_function_creators = 1;
  • If you want to do the master-slave switch under read-write separation , Then the slave node may also become a write node , Therefore, the slave node cannot be set to read-only read_only=1
  • Linux Version of MySQL, I need to set to MySQL Case insensitive , Otherwise, the table may not be found . Can be found in /etc/my.cnf Of [mysqld] Add lower_case_table_names=1 .

To configure schema.xml

schema.xml yes Mycat One of the most important profiles , Used for setting up Mycat The logical library of 、 surface 、 Data nodes 、dataHost The content such as ,

[[email protected] conf]$ cd /usr/local/root/conf/
[[email protected] conf]$ vi schema.xml

The contents of the document are as follows .

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
      <schema name="lyz_schema1" checkSQLschema="false" sqlMaxLimit="100" dataNode="lyz_dn1"></schema>
      <schema name="lyz_schema2" checkSQLschema="false" sqlMaxLimit="100" dataNode="lyz_dn2"></schema>
      <dataNode name="lyz_dn1" dataHost="dtHost2" database="lyzdb1" />
      <dataNode name="lyz_dn2" dataHost="dtHost2" database="lyzdb2" />
      <dataHost name="dtHost2" maxCon="500" minCon="20" balance="1"
           writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
           <heartbeat>show slave status</heartbeat>
           <!-- can have multi write hosts -->
           <writeHost host="hostM2" url="192.168.209.131:3306" user="root" password="root" />
           <writeHost host="hostS2" url="192.168.209.132:3306" user="root" password="root" />
      </dataHost>
</mycat:schema>

Mycat1.4 Start supporting MySQL The read-write separation mechanism of master-slave replication state binding , Make reading safer , The configuration is as follows :

Mycat The heartbeat check statement is configured to show slave status ,dataHost Two new attributes are defined on :switchType="2" And slaveThreshold="100", This means turning on MySQL The read-write separation and switch mechanism of master-slave replication state binding ,MyCat The heartbeat mechanism is detected by show slave status Medium "Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running" Three fields to determine the current master-slave synchronization status and Seconds_Behind_Master Master slave replication delay , When Seconds_Behind_Master Greater than slaveThreshold when , The read-write separation filter will filter out this Slave machine , Prevent reading old data long ago , When the primary node goes down , The switching logic checks Slave Upper Seconds_Behind_Master Is it 0, by 0 When, it means master-slave synchronization , It's safe to switch , Otherwise, it will not switch .

To configure server.xml

server.xml It is mainly used to set system variables 、 Manage users 、 Set user permissions, etc .

[[email protected] conf]$ vim server.xml

The contents of the document are as follows .

<?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>
  <property name="charset">utf8mb4</property>
    </system>
    <user name="lyz">
      <property name="password">lyz.123</property>
      <property name="schemas">lyz_schema1,lyz_schema2</property>
    </user>
    <user name="userlyz">
      <property name="password">lyz.123</property>
      <property name="schemas">lyz_schema1,lyz_schema2</property>
      <property name="readOnly">true</property>
    </user>
</mycat:server>

Open in firewall 8066 and 9066 port

Mycat The default data port for is 8066,mycat Receive access requests from database clients through this port .

The management port is 9066, For reception Mycat Monitoring command 、 Inquire about Mycat Health 、 Reloading configuration files, etc .

[[email protected] mycat]# vim /etc/sysconfig/iptables

increase :

## MyCat
-A INPUT -m state --state NEW -m tcp -p tcp --dport 8066 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 9066 -j ACCEPT

service iptables restart :

[[email protected] mycat]# service iptables restart

modify log Log level is debug

modify log Log level is debug, In order to confirm through the log based on Mycat Of MySQL Data operation status of database cluster reading and writing separation ( It can be changed to... Before formal production info Level )

[[email protected] conf]$ vim /usr/local/mycat/conf/log4j.xml

start-up Mycat

[[email protected] bin]$ cd /usr/local/root/bin/

(1) Console launch , This boot mode is after the console is shut down ,Nycat Services will also be shut down , Suitable for debugging :

[[email protected] bin]$ ./mycat console

(2) You can use the following background startup mode :

[[email protected] bin]$ ./mycat start
Starting root-server...

Mycat Connect the test

(1) If the local Windows Installed with MySQL, You can use the existing mysql Client remote operation Mycat

(2) Nativecat Connect Mycat

Read-write separation test

(1) monitor Mycat journal

[[email protected] ~]$ cd /usr/local/mycat/logs/
[[email protected] logs]$ tail -f mycat.log

(2) Reading test

$ mysql -uuserlyz -plyz.123 -h192.168.209.233 -P8066
mysql> show databases;
mysql> use lyz_schema2;
mysql> show tables;
mysql> select * from lyz_user_02;

Execute the above query statement , At this time, the corresponding Mycat The log information is as follows :

Multiple execution select * from lyz_user_02 sentence ,Mycat The printed log information shows that all read requests are routed to Slave node (192.168.209.132)

Write tests

mysql> insert into lyz_user_02 (userName, pwd) values('liuyazhuang', 'lyz.123');

After executing the new insert statement above , At this time, the corresponding Mycat The log information is as follows :

Execute the above insert statement several times , We found that all the new data came from Master node (192.168.209.131) Inserted in , also Slave Node passing Binlog Synced Master Data in node .

Sum up , be based on Mycat The read / write separation cluster of is configured successfully . If the article helps you a little , Please give me a compliment , I'm looking at it and forwarding it ~~.

Okay , That's all for today , I'm glacier , See you next time ~~

This article is from WeChat official account. - Glacier Technology (hacker-binghe)

The source and reprint of the original text are detailed in the text , If there is any infringement , Please contact the [email protected] Delete .

Original publication time : 2020-12-08

Participation of this paper Tencent cloud media sharing plan , You are welcome to join us , share .

版权声明
本文为[glacier]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224160737107I.html

Scroll to Top