MySQL MYCAT installation configuration

explain

Linux MYCAT installation configuration to realize MySQL master-slave replication + read-write separation.
In this case, you have installed MySQL master-slave replication by default. Please refer to my related articles for details.
Mycat has a single server and MySQL has a master server and a slave server. There are three servers in total.
- MySQL master server: 192.168.3.201
- MySQL slave server: 192.168.3.202
- Mycat server: 192.168.3.205

Operation steps

>Environmental preparation

One master-slave MySQL server configured
Another server is used to install Mycat. Mycat is a Java project. It is required that this server must install JDK. For details, please refer to my related articles.

>Install Mycat

Download: https://github.com/MyCATApache/Mycat-Server/releases/

Downloaded installation package:

[root@192 Mycat]# ll
total 21512
-rw-r--r--. 1 501 games 21760812 Jan 28 11:52 Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

Unzip:

[root@192 Mycat]# tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@192 Mycat]# mv mycat /usr/local/
[root@192 Mycat]# cd /usr/local/mycat/
[root@192 mycat]# ll
total 12
drwxr-xr-x. 2 501 games  190 Jan 29 00:36 bin
drwxr-xr-x. 2 501 games    6 Oct 22  2019 catlet
drwxr-xr-x. 4 501 games 4096 Jan 29 00:36 conf
drwxr-xr-x. 2 501 games 4096 Jan 29 00:36 lib
drwxr-xr-x. 2 501 games    6 Jan  5  2020 logs
-rwxr-xr-x. 1 501 games  227 Jan  5  2020 version.txt

>Configure Mycat

The configuration file is located in the: conf directory

Modify the configuration file server xml:

[root@192 conf]# vi server.xml
...
<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">master_slave_001</property>
    <property name="defaultSchema">master_slave_001</property>
    <!--No MyCAT Database selected Try to use this before making an error schema As schema，Otherwise null,report errors -->
    <!-- Table level DML Permission setting -->
    <!--
    <privileges check="false">
        <schema name="TESTDB" dml="0110" >
            <table name="tb01" dml="0000"></table>
            <table name="tb02" dml="1111"></table>
        </schema>
    </privileges>
	-->
</user>
...

Configuration Description:
- root: the user name of connecting Mycat, which is equivalent to the user name of MySQL
- Password: the password to connect to Mycat, which is equivalent to the password of MySQL
- schemas and defaultSchema: the virtual library name of Mycat, corresponding to the real library name of MySQL.
  - The two sides should be consistent, otherwise some MySQL clients (such as Navicat) will report an error when connecting: table 'testdb tb001' doesn't exist.

Modify the configuration file schema XML (this file has changed a lot, so back it up first):

[root@192 conf]# cp schema.xml schema.xml.default

[root@192 conf]# vi schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- 
	schema:           Mapping between virtual library and real library 
	-name:           The name of the virtual library, corresponding to server.xml Set in schemas
	-sqlMaxLimit:    Maximum number of query records allowed
	-checkSQLschema: Check whether to automatically delete "virtual library name"
	-dataNode:       Virtual library corresponding real database，corresponding<dataNode>Tagged name
	-->
	<schema name="master_slave_001" checkSQLschema="false" sqlMaxLimit="100" dataNode="dataNode001">
        <!-- 
		table:      Virtual table configuration
		-name:     Table name
		-dataNode: The partition corresponding to the table. If slicing is done, configure multiple, separated by commas; Or use db$0-99，representative db0 reach db99 of database
		-rule:     Sharding rule. If not, delete it
		-->
        <!-- 
		<table name="tb_item" dataNode="dn1" rule="sharding-by-mod2"/> 
		-->
	</schema>
	<!-- 
	dataNode:   Database fragmentation, each dataNode It is a database partition
	-name:     Slice name
    -dataHost: Host information of real library, corresponding to<dataHost>Tagged name
	-database: real MySQL Real physical database name in
	-->
	<dataNode name="dataNode001" dataHost="dataHost001" database="master_slave_001" />
	<!-- 
	dataHost:     Host information of real library
	-name:       host name
	-maxCon:     Maximum connection
	-minCon:     Minimum connection
	-balance:    Load balancing mode, 0 do not enable read / write separation. one~3 All open. See the details below
	-writeType:  Write load balancing. Always set 0
	-dbDriver:   Drive type, recommended native，Optional jdbc
	-switchType: Automatic switching between master and slave, 1=Automatic switching,-1=No automatic switching, 2=be based on MySQL The status of master-slave synchronization determines whether to switch. The default is 1
	-->
	<dataHost name="dataHost001" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
		<writeHost host="writeHost001" url="192.168.3.201:3306" user="root" password="rootroot">
			<readHost host="readHost001" url="192.168.3.202:3306" user="root" password="rootroot" />
		</writeHost>
	</dataHost>
</mycat:schema>

schema.name (Virtual Library name):
- Scheme 1: the virtual database name is consistent with the real database name in MySQL, so you can access all tables in the real database without defining accessible tables.
- Scheme 2: the virtual database name is different from the real database name in MySQL, and then define the accessible table in the schema tag.
- If the second scheme is selected, when the accessible table is not defined, the query will show table 'testdb Tb001 'doesn't exist error.
dataHost. Detailed description of balance:
- balance = "0": the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost.
- balance = "1": all readhosts and stand by writeHost participate in the load balancing of the select statement. In short, it is applicable to dual master and dual slave mode (M1 - > S1, M2 - > S2, and M1 and M2 are primary and standby to each other). Under normal circumstances, M2, S1 and S2 all participate in the load balancing of select statements.
- balance = "2": all read operations are randomly distributed on writeHost and readhost.
- balance = "3": all read requests are randomly distributed to readhost for execution. writerHost does not bear the reading pressure. Corresponding to master order and slave order.

>Start Mycat

Console startup:

[root@192 mycat]# ./bin/mycat console
Running Mycat-server...
wrapper  | --> Wrapper Started as Console
wrapper  | Launching a JVM...
jvm 1    | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1    |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
jvm 1    |
jvm 1    | MyCAT Server startup successfully. see logs in logs/mycat.log

View log file location:

[root@192 logs]# pwd
/usr/local/mycat/logs

[root@192 logs]# ll
total 28
-rw-r--r--. 1 root root 20067 Jan 29 10:55 mycat.log
-rw-r--r--. 1 root root     6 Jan 29 10:49 mycat.pid
-rw-r--r--. 1 root root  2568 Jan 29 10:49 wrapper.log

Background start:

[root@192 mycat]# ./bin/mycat start
[root@192 mycat]# ./bin/mycat stop
[root@192 mycat]# ./bin/mycat restart
[root@192 mycat]# ./bin/mycat status

>Remote connection test

Mycat default port: 8066
Here I use Navicat client to create a new MySQL connection method. Configuration parameters:
```
Host:192.168.3.205
Port:8066
UserName:root
Password:123456
```
- Parameter Description:
  - Host: the IP address of the server where Mycat is located
  - Port: the external access port when Mycat is used as MySQL springboard (mapped to 3306 of MySQL)
  - UserName: Mycat configuration file server "root" in the user element defined in XML“
  - Password: Mycat configuration file server "Password" in the user element defined in XML
After the connection is successful, you will see the database named "master_slave_001", which is the table in MySQL. Try to add, delete, modify, check and view the data of the master-slave database. Everything is normal.
So far, Mycat+MySQL master-slave replication + read-write separation has been realized.

>Dual master and dual slave (dual machine hot standby)

The core operation is to configure another master-slave copy, and then the two master services copy each other. Please pay attention to my [manual dog head] for specific operations~

Keywords: Linux Database MySQL mycat

Added by PhillNeedsHelp on Wed, 23 Feb 2022 04:53:32 +0200

Programming VIP