MySQL主从复制与读写分离原理及用法详解

server-id=200
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index

server-id = 210
replicate-do-db =itmayiedu #需要同步数据库

stop slave;
change
master to master_host='172.27.185.1',master_user='root',master_password='root';
start slave;
show slave status;

CREATE DATABASE IF NOT EXISTS `weibo_simple`;
-- ------------------------------------
-- Table structure for `t_users` 用户表
-- ------------------------------------
DROP TABLE IF EXISTS `t_users`;
CREATE TABLE `t_users` (
 `user_id` varchar(64) NOT NULL COMMENT '注册用户ID',
 `user_email` varchar(64) NOT NULL COMMENT '注册用户邮箱',
 `user_password` varchar(64) NOT NULL COMMENT '注册用户密码',
 `user_nikename` varchar(64) NOT NULL COMMENT '注册用户昵称',
 `user_creatime` datetime NOT NULL COMMENT '注册时间',
 `user_status` tinyint(1) NOT NULL COMMENT '验证状态 1：已验证 0：未验证',
 `user_deleteflag` tinyint(1) NOT NULL COMMENT '删除标记 1：已删除 0：未删除',
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -------------------------------------
-- Table structure for `t_message`微博表
-- -------------------------------------
DROP TABLE IF EXISTS `t_message`;
CREATE TABLE `t_message` (
 `messages_id` varchar(64) NOT NULL COMMENT '微博ID',
 `user_id` varchar(64) NOT NULL COMMENT '发表用户',
 `messages_info` varchar(255) DEFAULT NULL COMMENT '微博内容',
 `messages_time` datetime DEFAULT NULL COMMENT '发布时间',
 `messages_commentnum` int(12) DEFAULT NULL COMMENT '评论次数',
 `message_deleteflag` tinyint(1) NOT NULL COMMENT '删除标记 1：已删除 0：未删除',
 `message_viewnum` int(12) DEFAULT NULL COMMENT '被浏览量',
 PRIMARY KEY (`messages_id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `t_message_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

	<!-- 添加user -->
  <user name="mycat">
  <property name="password">mycat</property>
  <property name="schemas">mycat</property>
  </user>
	
	<!-- 添加user -->
  <user name="mycat_red">
  <property name="password">mycat_red</property>
  <property name="schemas">mycat</property>
	<property name="readOnly">true</property>
  </user>

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
  <!-- 与server.xml中user的schemas名一致 -->
  <schema name="mycat" checkSQLschema="true" sqlMaxLimit="100">
    <table name="t_users" primaryKey="user_id" dataNode="dn1" rule="rule1"/>
   
    <table name="t_message" type="global" primaryKey="messages_id" dataNode="dn1" />
  </schema>
<dataNode name="dn1" dataHost="jdbchost" database="weibo_simple


" />
  
  <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="1"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"
        slaveThreshold="100">
     <heartbeat>select user()</heartbeat> 
    <writeHost host="hostMaster" url="172.27.185.1:3306" user="root" password="root">
    </writeHost>
    <writeHost host="hostSlave" url="172.27.185.2:3306" user="root" password="root"/>
  </dataHost>
  
</mycat:schema>

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- 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:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
	 <tableRule name="rule1">
    <rule>
      <columns>user_id</columns>
      <algorithm>func1</algorithm>
    </rule>
  </tableRule>
  <function name="func1" class="org.opencloudb.route.function.AutoPartitionByLong">
 	<property name="mapFile">autopartition-long.txt</property>
  </function>
</mycat:rule>