陈建华的个人网站

  • 一、 安装jdk
  • 1、检查是否安装java
  • 2、安装jdk包
  • 3、修改环境变量文件,添加如下内容；
  • 二、mycat安装配置读写分离
  • 1、解压缩至目录/usr/local/
  • 2、查看配置文件
  • 3、先配置server.xml
  • 4、配置schema
  • 5、开启mycat
  • 6、查看端口
  • 7、登录mycat读写分离服务:
  • 8、登录mycat管理端:
  • 三、 mycat读写分离验证
  • 1、更改日志的输出模式
  • 2、创建表
  • 3、插入数据
  • 4、查询观察日志的变化

[PHP亿万流量架构之]使用Mycat做Mysql集群

  • Jet Chen
  • 2018-03-21
  • 0

mysql-master：172.16.200.43
Mycat：172.16.200.43
mysql-slave1：172.16.200.45
mysql-slave2：172.16.200.46
.......................................................................................
MariaDB5.5.51数据库
..............................................................................................

一、 安装jdk

1、检查是否安装java

<span class="hljs-meta"># java -version</span>

2、安装jdk包

网址：http://www.oracle.com/technet...
上传至/usr/local/java目录下

3、修改环境变量文件,添加如下内容；

添加完成后，source /etc/profile
vi /etc/profile

<span class="hljs-comment">#add java</span>
export JAVA_HOME=<span class="hljs-regexp">/usr/local</span><span class="hljs-regexp">/java/jdk</span>1.<span class="hljs-number">7.0_80</span>
export JAVA_BIN=<span class="hljs-regexp">/usr/local</span><span class="hljs-regexp">/java/jdk</span>1.<span class="hljs-number">7.0_80</span>/bin
export PATH=$<span class="hljs-symbol">PATH:</span>/usr/local/java/jdk1.<span class="hljs-number">7.0_80</span>/bin
export CLASSPATH=./:<span class="hljs-regexp">/usr/local</span><span class="hljs-regexp">/java/jdk</span>1.<span class="hljs-number">7.0_80</span>/<span class="hljs-class"><span class="hljs-keyword">lib</span>:/<span class="hljs-title">usr</span>/<span class="hljs-title">local</span>/<span class="hljs-title">java</span>/<span class="hljs-title">jdk1</span>.7.0<span class="hljs-title">_80</span>/<span class="hljs-title">jre</span>/<span class="hljs-title">lib</span></span>
MYCAT_HOME=<span class="hljs-regexp">/usr/local</span><span class="hljs-regexp">/mycat
export JAVA_HOME JAVA_BIN PATH CALSSPATH</span>

二、mycat安装配置读写分离

1、解压缩至目录/usr/local/

# <span class="hljs-selector-tag">tar</span> <span class="hljs-selector-tag">zxvf</span> <span class="hljs-selector-tag">Mycat-server-1</span><span class="hljs-selector-class">.6-RELEASE-20161028204710-linux</span><span class="hljs-selector-class">.tar</span><span class="hljs-selector-class">.gz</span>

2、查看配置文件

配置文件非常多.如果只是简单配置在不同的服务器上进行读写分离只需要配置两个文件 server.xml 和  schema.xml

3、先配置server.xml

    <user <span class="hljs-built_in">name</span>=<span class="hljs-string">"root"</span>>
        <<span class="hljs-keyword">property</span> <span class="hljs-built_in">name</span>=<span class="hljs-string">"password"</span>><span class="hljs-number">123456</span></<span class="hljs-keyword">property</span>>
        <<span class="hljs-keyword">property</span> <span class="hljs-built_in">name</span>=<span class="hljs-string">"schemas"</span>>test</<span class="hljs-keyword">property</span>>
    </user>
    <user <span class="hljs-built_in">name</span>=<span class="hljs-string">"user"</span>>
        <<span class="hljs-keyword">property</span> <span class="hljs-built_in">name</span>=<span class="hljs-string">"password"</span>><span class="hljs-number">123456</span></<span class="hljs-keyword">property</span>>
        <<span class="hljs-keyword">property</span> <span class="hljs-built_in">name</span>=<span class="hljs-string">"schemas"</span>>test</<span class="hljs-keyword">property</span>>
        <<span class="hljs-keyword">property</span> <span class="hljs-built_in">name</span>=<span class="hljs-string">"readOnly"</span>><span class="hljs-literal">true</span></<span class="hljs-keyword">property</span>>
    </user>

4、配置schema

<span class="hljs-meta"><!DOCTYPE mycat:schema SYSTEM "schema.dtd"></span>
<span class="hljs-tag"><<span class="hljs-name">mycat:schema</span> <span class="hljs-attr">xmlns:mycat</span>=<span class="hljs-string">"http://io.mycat/"</span>></span>
        <span class="hljs-tag"><<span class="hljs-name">schema</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"test"</span> <span class="hljs-attr">checkSQLschema</span>=<span class="hljs-string">"false"</span> <span class="hljs-attr">sqlMaxLimit</span>=<span class="hljs-string">"100"</span> <span class="hljs-attr">dataNode</span>=<span class="hljs-string">"dn_test"</span>></span> <span class="hljs-tag"></<span class="hljs-name">schema</span>></span>
        <span class="hljs-tag"><<span class="hljs-name">dataNode</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"dn_test"</span> <span class="hljs-attr">dataHost</span>=<span class="hljs-string">"dh_43"</span> <span class="hljs-attr">database</span>=<span class="hljs-string">"test"</span> /></span>
        <span class="hljs-tag"><<span class="hljs-name">dataHost</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"dh_43"</span> <span class="hljs-attr">maxCon</span>=<span class="hljs-string">"1000"</span> <span class="hljs-attr">minCon</span>=<span class="hljs-string">"10"</span> <span class="hljs-attr">balance</span>=<span class="hljs-string">"1"</span>
                          <span class="hljs-attr">writeType</span>=<span class="hljs-string">"0"</span> <span class="hljs-attr">dbType</span>=<span class="hljs-string">"mysql"</span> <span class="hljs-attr">dbDriver</span>=<span class="hljs-string">"native"</span> <span class="hljs-attr">switchType</span>=<span class="hljs-string">"1"</span>  <span class="hljs-attr">slaveThreshold</span>=<span class="hljs-string">"100"</span>></span>
                <span class="hljs-tag"><<span class="hljs-name">heartbeat</span>></span>select user()<span class="hljs-tag"></<span class="hljs-name">heartbeat</span>></span>
                <span class="hljs-comment"><!-- can have multi write hosts --></span>
                <span class="hljs-tag"><<span class="hljs-name">writeHost</span> <span class="hljs-attr">host</span>=<span class="hljs-string">"43_M"</span> <span class="hljs-attr">url</span>=<span class="hljs-string">"172.16.200.43:3306"</span> <span class="hljs-attr">user</span>=<span class="hljs-string">"root"</span>
                                   <span class="hljs-attr">password</span>=<span class="hljs-string">"000000"</span>></span>
                        <span class="hljs-comment"><!-- can have multi read hosts --></span>
                        <span class="hljs-tag"><<span class="hljs-name">readHost</span> <span class="hljs-attr">host</span>=<span class="hljs-string">"45_S1"</span> <span class="hljs-attr">url</span>=<span class="hljs-string">"172.16.200.45:3306"</span> <span class="hljs-attr">user</span>=<span class="hljs-string">"root"</span> <span class="hljs-attr">password</span>=<span class="hljs-string">"000000"</span> /></span>
                        <span class="hljs-tag"><<span class="hljs-name">readHost</span> <span class="hljs-attr">host</span>=<span class="hljs-string">"46_S2"</span> <span class="hljs-attr">url</span>=<span class="hljs-string">"172.16.200.46:3306"</span> <span class="hljs-attr">user</span>=<span class="hljs-string">"root"</span> <span class="hljs-attr">password</span>=<span class="hljs-string">"000000"</span> /></span>
                <span class="hljs-tag"></<span class="hljs-name">writeHost</span>></span>
                <span class="hljs-comment"><!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --></span>
        <span class="hljs-tag"></<span class="hljs-name">dataHost</span>></span>

5、开启mycat

./mycat start

6、查看端口

<span class="hljs-attr"># netstat -tnlp
TCP *:9066</span> <span class="hljs-comment">(LISTEN)</span> <span class="hljs-attr">#虚拟schema管理端口 
TCP *:8066</span> <span class="hljs-comment">(LISTEN)</span> <span class="hljs-attr">#虚拟schema登陆端口 </span>

能看到这个说明咱们的Mycat已经启动成功了

7、登录mycat读写分离服务:

# mysql -uroot -p123456 -h127<span class="hljs-number">.0</span><span class="hljs-number">.0</span><span class="hljs-number">.1</span> -P <span class="hljs-number">8066</span>

8、登录mycat管理端:

# <span class="hljs-selector-tag">mysql</span> <span class="hljs-selector-tag">-uroot</span> <span class="hljs-selector-tag">-p123456</span> <span class="hljs-selector-tag">-h127</span><span class="hljs-selector-class">.0</span><span class="hljs-selector-class">.0</span><span class="hljs-selector-class">.1</span> <span class="hljs-selector-tag">-P</span> 9066
<span class="hljs-selector-tag">Mysql</span>> <span class="hljs-selector-tag">show</span> @@<span class="hljs-keyword">heartbeat</span>;
RS_CODE为1表示心跳正常
Mysql> show @@datasource; --查看读写分离的机器配置情况

三、 mycat读写分离验证

1、更改日志的输出模式

vi log4j.xml
修改<asyncRoot level="info" includeLocation="true">
为 <asyncRoot level="debug" includeLocation="true">

2、创建表

<span class="hljs-meta"># mysql -uroot -p123456 -h127.0.0.1 -P 8066</span>
mysql> <span class="hljs-keyword">create</span> table test (id bigint <span class="hljs-keyword">not</span> <span class="hljs-built_in">null</span> primary <span class="hljs-built_in">key</span>,user_id varchar(<span class="hljs-number">100</span>),<span class="hljs-built_in">date</span> <span class="hljs-built_in">DATE</span>, fee decimal);

3、插入数据

<span class="hljs-selector-tag">mysql</span>> <span class="hljs-selector-tag">insert</span> <span class="hljs-selector-tag">into</span> <span class="hljs-selector-tag">test</span>(id,user_id,date,fee)  <span class="hljs-selector-tag">values</span>(<span class="hljs-number">1</span>,<span class="hljs-variable">@@hostname</span>,<span class="hljs-number">20161201</span>,<span class="hljs-number">100</span>);
<span class="hljs-selector-tag">mysql</span>> <span class="hljs-selector-tag">insert</span> <span class="hljs-selector-tag">into</span> <span class="hljs-selector-tag">test</span>(id,user_id,date,fee)  <span class="hljs-selector-tag">values</span>(<span class="hljs-number">5000001</span>,<span class="hljs-variable">@@hostname</span>,<span class="hljs-number">20161202</span>,<span class="hljs-number">100</span>);

4、查询观察日志的变化

mysql> select * from test
tail -f wrapper.log

© 2023 陈建华的个人网站
Theme by Wing
  • {{ item.name }}
  • {{ item.name }}