mysql读写分离

2019-02-14  本文已影响0人  慕容晗曦

关闭防火墙、selinux,部署java,mycat
完成主从复制

mycat

[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce  0
[root@localhost ~]# cp /usr/local/mycat/conf/server.xml{,.back}
[root@localhost ~]# cp /usr/local/mycat/conf/schema.xml{,.back}
[root@localhost ~]# cp /usr/local/mycat/conf/log4j2.xml{,.back}

mycat定义逻辑库和相关配置的配置文件

[root@localhost ~]# vim /usr/local/mycat/conf/server.xml
<?xml version="2.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0,设置为1则需要指定默认账户-->
        <property name="useHandshakeV10">1</property>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

                <property name="sequnceHandlerType">2</property>
        <property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>
<property name="useOffHeapForMerge">1</property>
<property name="memoryPageSize">64k</property>
<property name="spillsFileBufferSize">1k</property>  
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">384m</property>
<property name="useZKSwitch">false</property>
<property name="strictTxIsolation">false</property>
<property name="useZKSwitch">true</property>
</system>
<user name="root" defaultAccount="true">
                <property name="password">123456</property> ============密码
                <property name="schemas">db</property>   ===================逻辑库名
</user>
</mycat:server>

定义用户以及系统相关变量,如端口等的配置文件

[root@localhost ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="db"                ================主库名
                checkSQLschema="false" 
                sqlMaxLimit="100" 
                dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="db" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.122.104:3306" user="root"         ============主库ip
                                   password="Www.2.com">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.122.91:3306" user="root" password="Www.2.com" /> =========从库ip
                </writeHost>
        </dataHost>
</mycat:schema>

定义逻辑库,表、分片节点等内容的配置文件

[root@localhost ~]# vim /usr/local/mycat/conf/log4j2.xml
<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d [%-5p][%t] %m %throwable{full} (%C:%F:%L) %n"/>
        </Console>

        <RollingFile name="RollingFile" fileName="${sys:MYCAT_HOME}/logs/mycat.log"
                     filePattern="${sys:MYCAT_HOME}/logs/$${date:yyyy-MM}/mycat-%d{MM-dd}-%i.log.gz">
        <PatternLayout>
                <Pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} %5p [%t] (%l) - %m%n</Pattern>
            </PatternLayout>
            <Policies>
                <OnStartupTriggeringPolicy/>
                <SizeBasedTriggeringPolicy size="250 MB"/>
                <TimeBasedTriggeringPolicy/>
            </Policies>
        </RollingFile>
    </Appenders>
    <Loggers>
        <!--<AsyncLogger name="io.mycat" level="info" includeLocation="true" additivity="false">-->
            <!--<AppenderRef ref="Console"/>-->
            <!--<AppenderRef ref="RollingFile"/>-->
        <!--</AsyncLogger>-->
        <asyncRoot level="debug" includeLocation="true"> ===========================设置日志级别为 debug
            <!--<AppenderRef ref="Console" />-->
            <AppenderRef ref="RollingFile"/>

        </asyncRoot>
    </Loggers>
</Configuration>

启动服务

[root@localhost ~]# /usr/local/mycat/bin/mycat  start 

查看端口

[root@localhost ~]# netstat -antp | grep -E "8066|9066"

主库

在真实的 master 数据库上给用户授权

测试\

测试是否能正常登录上 主服务器

[root@localhost ~]# mysql -uroot -p'Www.2.com' -h192.168.122.104

继续测试是否能登录上从服务器

[root@localhost ~]# mysql -uroot -p'Www.2.com' -h192.168.122.91

通过客户端进行测试是否能登录到 mycat 上

[root@localhost ~]#mysql -uroot -p123456 -P8066 -h192.168.122.68
上一篇 下一篇

猜你喜欢

热点阅读