/ 2019-09-02
db01:
mysql -S /data/3307/mysql.sock
grant all on *.* to root@'10.0.0.%' identified by '123';
source /root/world.sql
mysql -S /data/3308/mysql.sock
grant all on *.* to root@'10.0.0.%' identified by '123';
source /root/world.sql
cd /application/mycat/conf
mv schema.xml schema.xml.bak
vim schema.xml
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
schema>
<dataNode name="dn1" dataHost="localhost1" database= "wordpress" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()heartbeat>
<writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
writeHost>
dataHost>
mycat:schema>
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
schema>
<dataNode name="dn1" dataHost="localhost1" database= "world" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()heartbeat>
<writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.52:3309" user="root" password="123" />
writeHost>
dataHost>
vim schema.xml
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
schema>
<dataNode name="sh1" dataHost="oldguo1" database= "world" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()heartbeat>
<writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
writeHost>
dataHost>
mycat:schema>
重启mycat
mycat restart
读写分离测试
mysql -uroot -p -h 127.0.0.1 -P8066
show variables like 'server_id';
begin;
show variables like 'server_id';
总结:
以上案例实现了1主1从的读写分离功能,写操作落到主库,读操作落到从库.如果主库宕机,从库不能在继续提供服务了。
(2)