mysql+mycat read-write separation

  • Environmental Science mysql-master/mycat mysql-slave

  • Configure jdk
tar  -zxf  jdk-8u151-linux-i586.tar.gz
mv jdk1.8.0_151 /usr/java/

Configure environment variables and take effect

vim /etc/profile
export JAVA_HOME=/usr/java/
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH:$HOMR/bin
source /etc/profile

View the java version and content indicates that java was installed successfully

[root@leeclient java]# java -version
java version "1.8.0_151"
Java(TM) SE Runtime Environment (build 1.8.0_151-b12)
Java HotSpot(TM) Client VM (build 25.151-b12, mixed mode)
  • mycat deployment
tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
mv mycat /usr/local/
  • mycat parameter configuration

1) Set the following parameters under /usr/local/mycat/conf/server.xml

<user name="lee1">
                <property name="password">123456</property>
                <property name="schemas">testdb</property>
<user name="lee2">
                <property name="password">123456</property>
                <property name="schemas">testdb</property>
                <property name="readOnly">true</property>

Lee1 and lee2 set passwords to connect mycat, lee1 to read and write, lee2 to read-only, and testdb to logical Libraries

2) under / usr/local/mycat/conf/schema.xml (backup cp schema.xml schema.xml.bak first)

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="testdb" checkSQLschema="false" sqlMaxLimit="1000" dataNode="dn1">
<dataNode name="dn1" dataHost="localhost1" database="ultrax" />
<dataHost name="localhost1" maxCon="2000" minCon="1" balance="0" writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<heartbeat>select  user()</heartbeat>
<writeHost host="hostM1" url="" user="root"  password="123456">
<readHost host="hostS1" url="" user="root" password="123456" />

The schema name needs to match the database name testdb set by server.xml, set the data node dn1
dataHost is the name of the data node and database is the real database name, discuz's background database ultrax

0 means no read-write separation will be enabled and read-write operations will be performed on the master
1 means they all participate in the select ion
2 means that all read requests are randomly shared between readhost and writehost
3 means that all read requests correspond to readhost

0, all writes sent to the first writeHost configured
1, all write operations are randomly sent to the configured writeHost
2, do not write.

-1, do not switch automatically
1, default, auto-switch
2, decide whether to switch based on the status of MySQL master-slave synchronization
3, switch mechanism based on MySQL galary cluster (suitable for cluster) (1.4.1), with a heartbeat statement of show status like'wsrep%'

WteHost and readHost configure mysql master-slave real user passwords for the corresponding background, respectively

3) Authorize on mysql master-slave server

Master and slave: grant all on *. * to'root'@''identified by'123456';
Main: grant all on *. * to'root'@''identified by'123456';
 Master and slave: grant all on *. * to'root'@'192.168.2.%'identified by'123456';

mycat service startup and view startup (note database port mapping)

/usr/local/mycat/bin/mycat start
tail -f /usr/local/mycat/logs/wrapper.log

tail -f /usr/local/mycat/logs/mycat.log

[root@mysqlm logs]# netstat -tunl | grep -E "8066|9066"
tcp        0      0 :::8066                     :::*                        LISTEN      
tcp        0      0 :::9066                     :::*                        LISTEN

8066 is the management port, web connection mycat
9066 is the port to connect back-end db, default is 9066, log on to the port and use show @@datasource directly; statement can query the read and write status of master and slave Libraries
Ports are set in server.xml, if you want to modify, add a statement to change the port

<property name="serverPort">8066</property>
<property name="managerPort">9066</property>

Log in to mycat 8066, here is to view the database after mycat is connected to the web, etc.

mysql -h -P8066 -ulee1 -p123456
mysql> show databases;
| testdb   |
1 row in set (0.04 sec)


The testdb above is mycat's logical library, which corresponds to the ultrax Library in the real database mysql

  • Common mycat commands

Enter 9066 Connection Port

mysql -h -P9066 -ulee1 -p123456

1) View node status

show @@datanode;

Name denotes the datanode name, dn1,
datahost data node name
Active represents the number of active connections
Idle denotes the number of idle connections
size represents the total number of connections

2) Check the state of your heart beat

show @@heartbeat;

3) Check the status of the front-end connection, and kill the connection kill @@connection ID directly based on the obtained id,

show @@connection\G;

4) View backend connection status

show @@backend\G;

5) View database resources

show @@database;

Other related directives include

show @@help;
show @@version;
  • Test mycat

1) Set balance="0" in schema.xml above
A. Test Read
Log on to port 9066 of mycat to view database resources

mysql -h –P9066 -ulee1 -p123456
show @@datasource;

You can see that the read_load column is the master increasing
Login to port 8066 of mycat

mysql -h -P8066 -ulee1 -p123456

Then do a query

use testdb;
select * from pre_common_member;

After you log in to 9066, you can see the read_load column, master has been increasing, slave has been zero

That is, in the case of balance="0", all the reads are on the master

B. Test Writing
Next, log in to mycat on port 8066 and create a table under testdb

use testdb;
create table mycatlee (id int,name varchar(20));
insert into mycatlee values (1,'lee1');

Then log on to port 9066 to view

show @@datasource;

You can see that master is increasing under write_load
That is, in the case of balance="0", all writes are on master

2) Set balance="1" in schema.xml
A. Test Read
Log on to 9066 to view database resources

show @@datasource;

Read data after landing 8066

use testdb;
select * from pre_common_member;

You can see that all the operations are read on slave
That is, in the case of balance="1", the reads are all on slave

B. Test Writing
Log on to port 8066 to insert data

insert into mycatlee values(2,'lee2');
insert into mycatlee values(3,'lee3');

You can see the write operation on the master (Note: Even if a write operation fails due to a similar grammar problem, the write_load value here will always increase, such as insert, because it is a write operation itself, so it goes from 0 to 2 instead of 1 because the grammar error has been written once)
That is, in the case of balance="1", all writes are on master

3) When creating a table, if the table is not synchronized from the library, mycat query mycattest at 118 will prompt that it does not exist, then directly stop the mysql service from the library and re-query it to query the contents of the table just inserted

Through the above tests, the read-write separation function is implemented

1) When starting mycat, viewing wrapper.log may encounter problems with jvm that prevent MYCAT from starting. You need to lower the Xmx equivalent under / usr/local/mycat/conf/wrapper.conf
For example:

Keywords: mycat MySQL Database Java

Added by foreknowapparel on Sun, 19 May 2019 01:48:47 +0300