[root@java1 ~]# cat $MYCAT_HOME/conf/server.xml
<?xml version=”1.0″ encoding=”UTF-8″?>
<!– – – Licensed under the Apache License, Version 2.0 (the
“License”);
# vim schema.xml 修改schema配置读写分离
启动起来:mycat start
登录:
[root@java1 ~]# mysql -h192.168.6.168 -uroot -p**** -P 9066
mycat中的数据和读库数据一致:
<!– 全局SQL防火墙设置 –>
<!–
<firewall>
<whitehost>
<host host=”127.0.0.1″ user=”mycat”/>
<host host=”127.0.0.2″ user=”mycat”/>
</whitehost>
<blacklist check=”false”>
</blacklist>
</firewall>
–>
mycat插入一条数据:
slave:vi /etc/opt/rh/rh-mysql57/my.cnf.d/rh-mysql57-mysql-server.cnf
至此通过mycat实现mysql的读写分离配置成功。
2, mycat(主从)+ mysql (多主N从) 分库分表。
<schema name=”information_schema” checkSQLschema=”false”
sqlMaxLimit=”100″ dataNode=”dn1″>
</schema>
<schema name=”infosys” checkSQLschema=”false” sqlMaxLimit=”100″
dataNode=”dn2″>
</schema>
<schema name=”mysql” checkSQLschema=”false” sqlMaxLimit=”100″
dataNode=”dn3″>
</schema>
<schema name=”performance_schema” checkSQLschema=”false”
sqlMaxLimit=”100″ dataNode=”dn4″>
</schema>
<schema name=”sys” checkSQLschema=”false” sqlMaxLimit=”100″
dataNode=”dn5″>
</schema>
<dataNode name=”dn1″ dataHost=”192.168.6.163″ database=”infosys”
/>
<dataNode name=”dn2″ dataHost=”192.168.6.163″
database=”information_schema” />
<dataNode name=”dn3″ dataHost=”192.168.6.163″ database=”mysql”
/>
<dataNode name=”dn4″ dataHost=”192.168.6.163″
database=”performance_schema” />
<dataNode name=”dn5″ dataHost=”192.168.6.163″ database=”sys” />
<dataHost name=”192.168.6.163″ maxCon=”1000″ minCon=”10″
balance=”2″
writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″
slaveThreshold=”100″>
<heartbeat>select user()</heartbeat>
<!– can have multi write hosts –>
<writeHost host=”192.168.6.163″ url=”192.168.6.167:3306″
user=”root”
password=”******”>
<!– can have multi read hosts –>
<!– <readHost host=”hostS2″ url=”192.168.6.163:3306″ user=”root”
password=”*****” /> –>
<readHost host=”192.168.6.167″ url=”192.168.6.167:3306″ user=”root”
password=”******”>
</readHost>
</writeHost>
</dataHost>
</mycat:schema>
mysql的主从配置沿用上一篇博客的配置:https://www.cnblogs.com/MasterSword/p/9434169.html
首先配置jdk:
ps.mycat最大的功能在于对数据库分片的处理,如果有机会,以后会进行相关的试验。
<!–
off heap for merge/order/group/limit 1开启 0关闭
–>
<property name=”useOffHeapForMerge”>1</property>
写库中存在插入的数据:
vim /home/mycat/.bash_profile
写库:
* grant replication slave on *.* to replica@’%’ identified by
‘password’; *
试验版本:Mycat-server-1.6-release
mysqldump -u root -p –all-databases –lock-all-tables –events >
mysql_dump.sql
下面验证读写分离
slave:
读库:
<!–是否采用zookeeper协调切换 –>
<property name=”useZKSwitch”>true</property>
<!–
单位为m
–>
<property name=”systemReserveMemorySize”>384m</property>
停止读库的服务:
groupadd dba
useradd -g dba mycat
passwd mycat
mkdri /home/mycat/app
mv mycat /home/mycat/app/mycat
mycat下载地址:http://www.mycat.io/
- you may not use this file except in compliance with the License. –
You
may obtain a copy of the License at – –
http://www.apache.org/licenses/LICENSE-2.0 -
- Unless required by applicable law or agreed to in writing, software
- distributed under the License is distributed on an “AS IS” BASIS, –
WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. – See
the
License for the specific language governing permissions and –
limitations
under the License. –>
<!DOCTYPE mycat:server SYSTEM “server.dtd”>
<mycat:server xmlns:mycat=”http://io.mycat/">
<system>
<property name=”useSqlStat”>0</property> <!–
1为开启实时统计、0为关闭 –>
<property name=”useGlobleTableCheck”>0</property> <!–
1为开启全加班一致性检测、0为关闭 –>
在客户端新建一个链接到mycat的数据库连接,用户名密码为server.xml中配置的用户
1.首先安装MySQL ab:
# cd ../bin
# ./mycat start 启动mycat
# ps -aux | grep mycat 查看mycat是否启动成功
exit
修改读库的数据:
<!–
单位为m
–>
<property name=”memoryPageSize”>1m</property>
# cd /usr/local
# mv ~/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz /usr/local
# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
# cd /usr/local/mycat/conf/
# vim server.xml 查看mycat用户的默认配置
6, mycat+web 监控
master: vi /etc/opt/rh/rh-mysql57/my.cnf.d/rh-mysql57-mysql-server.cnf
<!– mycat的默认用户配置,保持不变即可 –>
<user name=”root”>
<property name=”password”>123456</property>
<property name=”schemas”>TESTDB</property>
</user>
flush tables with read lock;
连接中库为TESTDB,也是在server.xm中配置的
解压:Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
mycat:
log-bin=mysql-bin
<?xml version=”1.0″?>
<!DOCTYPE mycat:schema SYSTEM “schema.dtd”>
<mycat:schema xmlns:mycat=”http://io.mycat/">
<schema name=”TESTDB” checkSQLschema=”false”
sqlMaxLimit=”100″>
<!– 设置物理表 –>
<table name=”student” dataNode=”dn1″ />
</schema>
<!– 设置物理库 –>
<dataNode name=”dn1″ dataHost=”localhost1″ database=”test”
/>
<!– balance=1设置为读写分离 –>
<dataHost name=”localhost1″ maxCon=”1000″ minCon=”10″
balance=”1″ writeType=”0″ dbType=”mysql” dbDriver=”native”
switchType=”1″ slaveThreshold=”100″>
<heartbeat>select user()</heartbeat>
<!– 读库配置 –>
<writeHost host=”hostM1″ url=”xxx.xxx.xxx.xxx:3306″
user=”root” password=”xxxxxx”>
<!– 读库配置,可配置多个读库 –>
<readHost host=”hostS2″ url=”xxx.xxx.xxx.xxx:3316″
user=”root” password=”xxxxxx” />
</writeHost>
</dataHost>
</mycat:schema>
1, mycat (主从) + mysql (一主多从) 读写分离。
2.安装mycat
master:
flush privileges;
192.168.6.167 slave
log-bin=mysql-bin
server-id=102
read_only=1
report-host=node01.srv.world
[root@java1 logs]# cat ../conf/schema.xml
<?xml version=”1.0″?>
<!DOCTYPE mycat:schema SYSTEM “schema.dtd”>
<mycat:schema xmlns:mycat=”http://io.mycat/">
3, haproxy + mycat + mysql
show master status;
<!– 表级 DML 权限设置 –>
<!–
<privileges check=”false”>
<schema name=”TESTDB” dml=”0110″ >
<table name=”tb01″ dml=”0000″></table>
<table name=”tb02″ dml=”1111″></table>
</schema>
</privileges>
–>
</user>
</mycat:server>
<!–
单位为k
–>
<property name=”spillsFileBufferSize”>1k</property>
后期 学习下面的,路漫漫啊:
scp mysql_dump.sql node01.srv.world:/tmp/
<property name=”useStreamOutput”>0</property>
<property name=”sequnceHandlerType”>2</property>
<!– <property name=”useCompression”>1</property>–>
<!–1为开启mysql压缩协议–>
<!– <property
name=”fakeMySQLVersion”>5.6.20</property>–>
<!–设置模拟的MySQL版本号–>
<!– <property
name=”processorBufferChunk”>40960</property> –>
<!–
<property name=”processors”>1</property>
<property name=”processorExecutor”>32</property>
–>
<!–默认为type 0: DirectByteBufferPool | type 1
ByteBufferArena–>
<property name=”processorBufferPoolType”>0</property>
<!–默认是65535 64K 用于sql解析时最大文本长度 –>
<!–<property
name=”maxStringLiteralLength”>65535</property>–>
<!–<property
name=”sequnceHandlerType”>0</property>–>
<!–<property
name=”backSocketNoDelay”>1</property>–>
<!–<property
name=”frontSocketNoDelay”>1</property>–>
<!–<property
name=”processorExecutor”>16</property>–>
<!–
<property name=”serverPort”>8066</property> <property
name=”managerPort”>9066</property>
<property name=”idleTimeout”>300000</property> <property
name=”bindIp”>0.0.0.0</property>
<property name=”frontWriteQueueSize”>4096</property>
<property name=”processors”>32</property> –>
<!–分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志–>
<property
name=”handleDistributedTransactions”>0</property>
安装成功;
export MYCAT_HOME=/home/mycat/app/mycat
PATH=$PATH:$MYCAT_HOME/bin
source .bash_profile
vim /etc/my.cnf
lower_case_table_names = 1
我的server.xml,schema.xml
change master to
master_host=’192.168.6.163′,
master_user=’replica’,
master_log_file=’mysql-bin.000001′,\040
master_log_pos=732;
start slave;
server-id=101
192.168.6.163 master
unlock tables;
好了,现在再研究它怎么使用和要出现的问题怎么处理。
mysql -u root -p < /tmp/mysql_dump.sql
5, mycat + oracle (dg) 读写分离。
systemctl restart rh-mysql57-mysqld
* show slave status\G*
<user name=”root”>
<property name=”password”>*****</property>
<property
name=”schemas”>information_schema,infosys,mysql,performance_schema,sys</property>
jdk-8u73-linux-x64.rpm 这个直接yum -y install
4, haproxy +keepalive+mycat + mysql galera cluster + mysql
</system>