This is the 9th day of my participation in the Novembermore Challenge

First, environmental preparation

(1) The local environment is three centos7
IP The host name The database name Install the software
192.168.77.128 master db1 mycat,mysql
192.168.77.129 slave1 db2 mysql
192.168.77.134 slave2 db3 mysql

(2) Create users and groups in Linux where mycat is installed

Create a new group

                groupadd mycat

2, create a new user and join the group

               useradd -g mycat mycat

3. Set a password for the new user

               passwd mycat

Install MySQL and create database

(1) Install MySQL reference my other two articles:

CentOS7 install MySQL5.7 using yum

Install mysql5.7 using CentOS7.4 RPM

(2) Create database

The database name varies from machine to machine

create database db1/db2/db3;
Copy the code

The result is as follows:

​​​

Download and install MyCat:

MyCat’s official website:

Mycat1.6

Download: github.com/MyCATApache…

You can also download my Baidu Cloud version:

Link: pan.baidu.com/s/1-J-Tb19y… Extraction code: B262

Step 1: Upload mycat-server-1.4-release-20151019230038-linux.tar. gz to the server

Step 2: Decompress the package. You are advised to save mycat in /usr/local/mycat.

Tar -xzvf mycat-server-1.4-release-20151019230038-linux.tar. gz mv Mycat /usr/localCopy the code

4. MyCat fragment configuration

/usr/local/mycat/conf = /usr/local/mycat/conf

1. Configure schema.xml

Schema. XML, as one of the important configuration files in MyCat, manages the logical library, logical tables, sharding rules, Datanodes, and DataSource of MyCat. Understanding these configurations is a prerequisite for using MyCat correctly. Here the file is parsed layer by layer.

The SCHEMA tag is used to define the logical libraries in MyCat instances

The Table label defines the logical Table rule in MyCat, which is used to specify sharding rules. The sharding rule of auto-sharding is based on the ID value range: 1-5000000 is the first piece 5000001-10000000 is the second piece…. .

The dataNode tag defines the data nodes in MyCat, which are commonly referred to as data fragments.

DataHost tag also exists as the lowest level tag in myCAT logical library, which directly defines the specific database instance, read/write separation configuration and heartbeat statement.

Create three databases on the server, db1 DB2 DB3

Modify schema. XML as follows:

<! DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://org.opencloudb/"> <schema name="youfanshop" checkSQLschema="false" sqlMaxLimit="100"> <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-intfile" /> </schema> <dataNode name="dn1" dataHost="master" database="db1" /> <dataNode name="dn2" dataHost="slave1" database="db2" /> <dataNode name="dn3" dataHost="slave2" database="db3" /> <dataHost name="master" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="master:3306" user="root" password="123456"> </writeHost> </dataHost> <dataHost name="slave1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <writeHost host="hostM2" url="slave1:3306" user="root" password="123456"> </writeHost> </dataHost> <dataHost name="slave2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <writeHost host="hostM3" url="slave2:3306" user="root" password="123456"> </writeHost> </dataHost> </mycat:schema>Copy the code

2. Configure server.xml

Server.xml holds almost all of the system configuration information required by MyCat. The most common is to configure the user name, password, and permissions here. Add UTF-8 character set Settings to system, otherwise there will be question marks for stored Chinese

utf8

/ / add a root user to server. XML. / / add a root user to server. XML. / / add a root user to server.

      <user name="root">
          <property name="password">123456</property>
          <property name="schemas">youfanshop</property>
      </user>
Copy the code

​

(3) Modify the partition-hash-int. TXT file in conf

Add 10020=2, the default is 10000 and 10010, now we need three category id, add one

​

Set mysql to ignore case

Add lower_case_table_names = 1 to vi /etc/my.cnf;

lower_case_table_names = 1
Copy the code

(5) Modify the hosts file

XML file uses slave1, slave2, and master without specifying IP addresses, so you need to map them to hosts:

192.168.77.128 master
192.168.77.129 slave1
192.168.77.134 slave2
Copy the code

​

Test MyCat

1. Start MyCat

Run the./ mycat start command in the bin directory of mycat

​

Check the logs:

tail -100 wrapper.log
Copy the code

It was a success to see the following results

​

2. Test horizontal partition of the mysql table

Install Navicat for MySQL on Windows outside the virtual machine, connect to three MySQL databases, execute the statement:

create table employee (id int not null primary key,name varchar(100),sharding_id int not null);
Copy the code

Use Navicat or sqlyog to connect to mycat. Mycat default port is 8066.

​

Mycat is connected to an empty employee table

Execute the following statement in the connected mycat

insert into employee(id,name,sharding_id) values(1, 'I am db1',10000);

insert into employee(id,name,sharding_id) values(2, 'I am db2',10010);

insert into employee(id,name,sharding_id) values(3, 'I am db3',10020);

insert into employee(id,name,sharding_id) values(4, 'I am db1',10000);

insert into employee(id,name,sharding_id) values(5, 'I am db2',10010);

insert into employee(id,name,sharding_id) values(6, 'I am db3',10020);
Copy the code

​

Refresh Navicat to see the library mycat connects to

​

db1

​

db2

​

db3

​

You can see that the six new records are allocated by MyCat to the three servers’ databases.

Sqlyog failed to connect to MyCat

MyCat’s host is not firewall disabled:

systemctl stop firewalld
Copy the code

After closing, the problem was resolved.

Or you don’t want to turn off the firewall, you can open port 8066:

1. Run the /sbin/iptables -i INPUT -p TCP –dport 8066 -j ACCEPT command to open ports

2. Save: / etc/rc. D/init. D/iptables save

3. Run the /etc/init.d/iptables restart command to restart the service

4. Run the /sbin/iptables -l -n command to check whether the port is open

You can also open a port like this:

firewall-cmd --add-port=8066/tcp --permanent
firewall-cmd --reload
Copy the code

PS: If you want to add multiple interfaces at once, you can repeat 1 several times, then 2, 3, and 4 at once.

\

​