基於mycat配置MySQL資料庫讀寫分離的詳細教程

架構圖

一、配置MySQL主從

二、安裝mycat

1、配置java環境

[root@mysql ~]# tar -xf jdk-8u77-linux-x64.gz

[root@mysql ~]#mkdir /usr/local/java

[root@mysql ~]#mv jdk1.8.0_77/ /usr/local/java/

[root@mysql ~]#vim /etc/profile

export JAVA_HOME=/usr/local/java/jdk1.8.0_77

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

[root@mysql ~]#source /etc/profile

[root@mysql ~]# java -version

java version "1.8.0_77"

Java(TM) SE Runtime Environment (build 1.8.0_77-b03)

Java HotSpot(TM) 64-Bit Server VM (build 25.77-b03, mixed mode)

說明此時Java環境已經部署成功

2、安裝mycat

下載mycat包並安裝(https://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz)

[root@mysql ~]# wget https://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[root@mysql ~]# tar -xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[root@mysql ~]# mv mycat/ /usr/local/

[root@mysql mycat]# cd /usr/local/mycat/

[root@mysql mycat]# vim /etc/profile

export MYCAT_HOME=/usr/local/mycat

export PATH=$PATH:$MYCAT_HOME/bin

[root@mysql mycat]# source /etc/profile

此時安裝完畢

3、配置mycat

[root@mysql mycat]# cd conf/

修改schema.xml文件

[root@mysql mycat]# vimschema.xml

一下備註部分為分表,因目前沒需要所以沒有對分表進行配置

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user();

修改server.xml[root@mysql conf]# vim server.xml

0

123456

TESTDB

456

TESTDB

true

4、啟動mycat,並查看是否啟動[root@mysql conf]# /usr/local/mycat/bin/mycat start

[root@mysql conf]# netstat -anptu |grep java

tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 3063/java

tcp6 0 0 :::42155 :::* LISTEN 3063/java

tcp6 0 0 :::34457 :::* LISTEN 3063/java

tcp6 0 0 :::1984 :::* LISTEN 3063/java

tcp6 0 0 :::8066 :::* LISTEN 3063/java

tcp6 0 0 :::9066 :::* LISTEN 3063/java

tcp6 0 0 192.168.1.76:36158 192.168.1.127:3306 ESTABLISHED 3063/java

tcp6 0 0 192.168.1.76:35258 192.168.1.128:3306 ESTABLISHED 3063/java

設置驗證環境並驗證

(1)、登陸mycat,建表並插入數據

[root@localhost ~]# mysql -uroot -h192.168.1.76 -P8066 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use TESTDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table company(id int not null primary key,name varchar(50),addr varchar(255));Query OK, 0 rows affected (0.01 sec)

mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(2000002, 'alex', '2018-06-08',500.0,3);

Query OK, 1 row affected (0.42 sec)

mysql> select * from travelrecord;

+———+———+————+——+——+

| id | user_id | traveldate | fee | days |

+———+———+————+——+——+

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+———+———+————+——+——+

3 rows in set (0.01 sec)

(2)、登陸主資料庫伺服器並驗證剛插入的數據

[root@localhost ~]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 42

Server version: 5.6.39-log Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -ADatabase changed mysql> select * from travelrecord;

+———+———+————+——+——+

| id | user_id | traveldate | fee | days |

+———+———+————+——+——+

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+———+———+————+——+——+

1 rows in set (0.00 sec)mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)同理登陸從伺服器並在從伺服器上插入數據以便驗證讀寫分離

[root@localhost ~]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 36

Server version: 5.6.39-log Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use liuys;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -ADatabase changed

mysql> select * from travelrecord;

+———+———+————+——+——+

| id | user_id | traveldate | fee | days |

+———+———+————+——+——+

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+———+———+————+——+——+

1 rows in set (0.00 sec)mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(2000001, 'alice', '2017-08-08',500.0,3);

Query OK, 1 row affected (0.42 sec)

mysql> select * from travelrecord;

+———+———+————+——+——+

| id | user_id | traveldate | fee | days |

+———+———+————+——+——+

| 2000001 | alice | 2017-08-08 | 500 | 3 |

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+———+———+————+——+——+

回到主伺服器查看時,肯定無法查到該條數據

mysql> select * from travelrecord;

+———+———+————+——+——+

| id | user_id | traveldate | fee | days |

+———+———+————+——+——+

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+———+———+————+——+——+

1 rows in set (0.00 sec)

當我們回到mycat伺服器時,又可以看到數據

[root@localhost ~]# mysql -uroot -h192.168.1.76 -P8066 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use TESTDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -ADatabase changed

mysql> select * from travelrecord;

+———+———+————+——+——+

| id | user_id | traveldate | fee | days |

+———+———+————+——+——+

| 2000001 | alice | 2017-08-08 | 500 | 3 |

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+———+———+————+——+——+

3 rows in set (0.01 sec)

至此mycat讀寫分離已部署完畢

發佈留言