Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Practice of Mycat read-write separation configuration

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Work over the years, but also went to some places, have some experience, vaguely feel that many cultures and cats are inextricably linked. In the IT industry, for example, cats have a high profile, such as the famous tomcat, developed by James Duncan Davidson, a software architect at SUN, and contributed by Sun to the Apache Software Foundation. He wanted the animal to take care of itself and named it Tomcat, so the name stayed.

One of my favorite data dictionary attempts in Oracle is cat, which is a typical cat culture with no other technical implications.

When I first heard of mycat, I thought it was a foreign open source project, but then I found that it turned out to be a domestic project, which made me a little excited, and the source code was based on Java, which I thought was too much to my taste.

The cat below was met one morning on Pippi Island, Thailand. It slept at the door and didn't bother to lift its eyelids.

Such a project as mycat is really popular in China, and there are also links on github, with more than 2000 stars. Is based on Ali open source Cobar products and development.

The github link is as follows:

Https://github.com/MyCATApache/Mycat-Server/wiki

Official links are as follows:

The current version of http://dl.mycat.io/ is relatively new is 1.6. it has witnessed the development of domestic database for so many years. It not only supports MySQL, but also supports Oracle,PG. At present, MySQL direction is the most popular.

Installation and deployment

The installation and deployment of Mycat is actually very simple, just download the corresponding installation package from the official, about 15m.

Download the latest installation package:

Wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

The content of the unzipped installation package is about 60m.

Installing Mycat is easy, as long as you make sure that Java is installed correctly, or if the system comes with it, the version is not low.

# java-version

Java version "1.7.045"

OpenJDK Runtime Environment (rhel-2.4.3.3.0.1.el6-x86_64 u45-b15)

The basic configuration of the corresponding Java of OpenJDK 64-Bit Server VM (build 24.45-b08, mixed mode) also needs to be adjusted in the profile file.

For example, modify the .bash _ profile file.

Export JAVA_HOME=/usr/lib/jvm/jre-1.7.0-openjdk.x86_64

Export PATH=$PATH:$JAVA_HOME/bin

Export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

Create system-level groups and users

Useradd mycat hardly needs to be adjusted for installation, just copy the extracted mycat directory to / usr/local, and then modify the permissions.

Mv mycat/ / usr/local/

Chown mycat:mycat / usr/local/mycat

Configure the database environment

The database environment we need to configure is assumed to be one master and three slaves, and the simulation can be built on one server. If you quickly build and deploy one master and three slaves, you can refer to a small script I wrote on github, and https://github.com/jeanron100/mysql_slaves, can do it in minutes.

Suppose the environment is as follows:

Master: Port 33091

Slave1: Port 33092

Slave2: Port 33093

Slave3: Port 33093

Mycat can achieve many functions, first to achieve a requirement, for example, there is a set of environment that reads more and writes less, and needs to provide a large amount of data connection access. We can create two user mycat_user, responsible for the DML,mycat_read responsible for the query. Create user mycat_user identified by 'mycat'

Create user mycat_read identified by 'mycat'

For example, if there are multiple databases, we will simulate the creation of 3 databases.

Create database db1

Create database db2

Create database db3

The section for assigning permissions is as follows:

Grant select on db1.* to mycat_read

Grant select,insert,delete,update on db1.* to mycat_user

Grant select on db2.* to mycat_read

Grant select,insert,delete,update on db2.* to mycat_user

Grant select on db3.* to mycat_read

Grant select,insert,delete,update on db3.* to mycat_user; gives you permission to query from the status of the library, which you need later.

Mysql > grant replication client on *. * to 'mycat_read'@'%'

Mysql > grant replication client on *. * to 'mycat_user'@'%'

Initialization data

To initialize the database, I choose a classic standard travelrecord and insert two rows of records. Created in 3 database db1,db2,db3.

Mysql > create table travelrecord

(id bigint not null primary key,user_id varchar, traveldate DATE, fee decimal,days int)

Mysql > insert into travelrecord (id,user_id,traveldate,fee,days) values

Mysql > insert into travelrecord (id,user_id,traveldate,fee,days) values (5000001)

Mycat configuration

After the above work is done, the work at the system level and database level is done. The command to start Mycat is simple, such as mycat start, stop Mycat's command mycat stop, and so on.

The command is very simple, the key lies in the configuration of Mycat, this is the point. There are two files to focus on in the mycat/conf directory, one is server.xml and the other is schema.xml

The key contents of the configuration of server.xml are as follows. We have configured two users, so in this configuration file, we will first follow the following configuration. Here we configure schema as pxc_schema.

Mycat

Pxc_schema

Mycat

Pxc_schema

True

The contents of schema.xml are as follows, and corresponding annotations are also made.

Show slave status

Check the connection of Mycat

After the configuration of Mycat is done, 80% of the task will be completed. In fact, the key lies in the configuration of Mycat files. Improper configuration still needs to be debugged repeatedly.

If we verify that Mycat is valid, we can use the default port 8066 to connect. If the word mycat appears in it, it proves that the Mycat setting is in effect.

[root@oel64 logs] # mysql-umycat_read-pmycat-P8066-h292.168.163.128

. . .

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

. . .

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Let's take a look at the database that the user mycat_read can access. It should be db1,db2,db3 in the database. Why did pxc_shema appear here? in fact, it can be understood that it is the effect of Mycat filtering in the middle. In fact, this is db1, and db2,db3 has not been reflected in the Mycat configuration file, so it has not taken effect yet.

Mysql > show databases

+-+

| | DATABASE |

+-+

| | pxc_schema |

+-+

1 row in set (0. 00 sec) We connect to this pxc_schema database.

Mysql > use pxc_schema

Database changed

You can see the tables under this database.

Mysql > show tables

+-+

| | Tables_in_db1 |

+-+

| | travelrecord |

+-+

1 row in set (0.01 sec)

What if we verify that the database we are connected to is Mycat-enabled read-write separation? We can look at the port.

Mysql > select @ @ port

+-+

| | @ @ port |

+-+

| | 33092 |

+-+

1 row in set (0.05sec)

From this we can see that it is connected to port 33092, that is, slave1.

You can switch over and over again to see if you are satisfied with this load balance.

Continue to expand the configuration of Mycat read-write separation

The above steps simply implement the read-write separation configuration, for example, I want to access multiple databases, not just pxc_schema, how to configure it.

We need to extend schema in server.xml. The values of schema are separated by commas, and the configuration details are mapped in schema.xml.

Mycat

Pxc_schema,db2,db3

If you configure multiple logical libraries in schema.xml, you can configure multiple schema keys.

The corresponding dataNode also needs to extend the mapping.

When the whole process is complete, restart Mycat.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report