Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use Mycat to achieve the separation of MySQL reading and writing. many people may not understand it very well. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

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

JDK version: jdk1.8

Mycat version: Mycat-server-1.6.1-RELEASE-20201208215510-linux.tar.gz (download source code compiled by yourself)

Mycat node IP:192.168.209.133 hostname: liuyazhuang133 host configuration: 4-core CPU, 4G memory

MySQL version: mysql-5.6.32.tar.gz

Host node IP:192.168.209.131 hostname: liuyazhuang131 host configuration: 4-core CPU, 4G memory

Slave node IP:192.168.209.132 hostname: liuyazhuang132 host configuration: 4-core CPU, 4G memory

Mycat deployment

The read-write separation of the architecture Mycat is based on the master-slave synchronization of the back-end MySQL cluster, while Mycat provides the function of distributing statements. MyCat1.4 began to support the read-write separation mechanism of MySQL master-slave replication state binding, making reads more secure and reliable.

Let's take a look at the overall deployment architecture diagram of Mycat.

Installation settings of Mycat hostname and mapping of IP to hostname

# 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

Configure Java environment variables

Because Mycat is developed in Java, you need to install JDK (JRE to be exact) and JDK1.7 or above to run Mycat

# 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 the / home/mycat directory in the MyCat server, extract and move to the / usr/local/mycat directory

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

Set the environment variable for Mycat

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

Configure Mycat

Confirm that MySQL master-slave replication is normal

Before configuring Mycat, verify that the master-slave replication installation configuration of MySQL is complete and functioning properly. The synchronization of MySQL master and slave data is configured in MySQL, and Mycat is not responsible for data synchronization.

Add:

In MySQL master-slave replication configuration, if synchronous replication of functions or stored procedures is involved, you need to add configuration log_bin_trust_function_creators=true in the [mysqld] section in / etc/my.cnf or set set global log_bin_trust_function_creators= 1 in the client.

If you want to do master-slave switching under read-write separation, the slave node may also become a write node, so the slave node cannot be set to read-only read_only=1.

The Linux version of MySQL needs to be set to MySQL case-insensitive, otherwise the table may not be found. You can add lower_case_table_names=1 to the [mysqld] paragraph of / etc/my.cnf.

Configure schema.xml

Schema.xml is one of the most important configuration files of Mycat, which is used to set up Mycat's logical libraries, tables, data nodes, dataHost, etc.

[root@liuyazhuang133 conf] $cd / usr/local/root/conf/ [root@liuyazhuang133 conf] $vi schema.xml

The contents of the file are shown below.

Show slave status

Mycat1.4 starts to support the read-write separation mechanism of MySQL master-slave replication state binding, which makes the read more secure and reliable. The configuration is as follows:

The Mycat heartbeat check statement is configured as show slave status, and two new attributes are defined on dataHost: switchType= "2" and slaveThreshold= "100", which means that the read-write separation and switching mechanism of MySQL master-slave replication state binding is enabled. The MyCat heartbeat mechanism determines the current master-slave synchronization status and the Seconds_Behind_Master master-slave replication delay by detecting the "Seconds_Behind_Master", "Slave_IO_Running" and "Slave_SQL_Running" fields in the show slave status. When the Seconds_Behind_Master is greater than the slaveThreshold, the read-write separation filter filters out the Slave machine to prevent reading the old data from a long time ago, and when the master node goes down. The switching logic checks whether the Seconds_Behind_Master on the Slave is 0, which indicates master-slave synchronization and can be safely switched, otherwise it will not be switched.

Configure server.xml

Server.xml is mainly used to set system variables, manage users, set user permissions, and so on.

[root@liuyazhuang133 conf] $vim server.xml

The contents of the file are shown below.

Druidparser utf8mb4 lyz.123 lyz_schema1,lyz_schema2 lyz.123 lyz_schema1,lyz_schema2 true

Open ports 8066 and 9066 in the firewall

The default data port for Mycat is 8066 MagneCat to receive access requests from the database client through this port.

The management port is 9066, which is used to receive Mycat monitoring commands, query Mycat health, reload configuration files, and so on.

[root@liuyazhuang133 mycat] # vim / etc/sysconfig/iptables

Add:

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

Restart the firewall:

[root@liuyazhuang133 mycat] # service iptables restart

Modify the log log level to debug

Modify the log log level to debug to confirm the data operation status of read-write separation of Mycat-based MySQL database clusters through logs (which can be changed to info level before formal production)

[root@liuyazhuang133 conf] $vim / usr/local/mycat/conf/log4j.xml

Start Mycat

[root@liuyazhuang133 bin] $cd / usr/local/root/bin/

(1) console startup. After the console is closed, the Nycat service will also be shut down, which is suitable for debugging:

[root@liuyazhuang133 bin] $. / mycat console

(2) the following backend startup methods can be used:

[root@liuyazhuang133 bin] $. / mycat start Starting root-server...

Mycat connection test (1) if the local Windows has MySQL installed, you can use the existing mysql client to remotely operate Mycat

(2) Nativecat connects to Mycat

Read-write separation test

(1) listen to Mycat logs

[root@liuyazhuang133 ~] $cd / usr/local/mycat/logs/ [root@liuyazhuang133 logs] $tail-f mycat.log

(2) Reading test

$mysql-uuserlyz-plyz.123-h292.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, and the corresponding Mycat log information is as follows:

When the select * from lyz_user_02 statement is executed multiple times, the log information printed by Mycat shows that the read requests are routed to the Slave node (192.168.209.132)

Write test

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

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

After executing the above insert statement many times, it is found that the new data is inserted from the Master node (192.168.209.131), and the Slave node synchronizes the data in the Master node through Binlog.

After reading the above, do you have any further understanding of how to use Mycat to achieve MySQL read-write separation? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.