mysql的主从同步和分库分表

mysql的主从同步：

mysql主服务器：10.128.199.232

mysql从服务器：10.128.199.233
数据库mycat_test


主服务器my.ini配置，在文件末尾添加：
server-id = 1
log-bin = mysql-bin
binlog-do-db = mycat_test
replicate-do-db = mycat_test


从服务器my.ini配置，在文件末尾添加：
server_id = 2
binlog-do-db = mycat_test
log_bin = mysql-bin   
log_slave_updates = 1  
read_only = 1


在主服务器执行以下命令：
grant replication slave, replication client on *.* to 'repl'@'10.128.199.233' identified by '123';
输入命令SHOW MASTER STATUS;
记录下file和position(mysql-bin.000027、2698)


在从服务器执行以下命令：
CHANGE MASTER TO MASTER_HOST='10.128.199.232',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_LOG_FILE='mysql-bin.000027',MASTER_LOG_POS=2698;
输入命令start slave开启复制
输入命令show slave status查看slave状态


在master、slave上建立结构相同的table，在主服务器上执行增删改查操作，从服务器也会进行相对应的操作，保持数据的一致性。

mycat的分库分表：

安装完mycat后，首先修改修改config目录下的server.xml文件

<?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:server SYSTEM "server.dtd"><mycat:server xmlns:mycat="http://org.opencloudb/"><system><property name="defaultSqlParser">druidparser</property><property name="sequnceHandlerType">0</property>      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议--><!-- <property name="processorBufferChunk">40960</property> --><!-- <property name="processors">1</property> <property name="processorExecutor">32</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="mutiNodeLimitType">1</property> 0：开启小数量级（默认） ；1：开启亿级数据排序    <property name="mutiNodePatchSize">100</property> 亿级数量排序批量<property name="processors">32</property> <property name="processorExecutor">32</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> --></system><user name="furonge">          <property name="password">12345678</property>          <property name="schemas">P2P</property>      </user> <!-- <cluster> <node name="cobar1"> <property name="host">127.0.0.1</property> <property name="weight">1</property> </node> </cluster> --><!-- <quarantine> <host name="1.2.3.4"> <property name="user">test</property> </host> </quarantine> --></mycat:server>

修改rule.xml文件，配置分库规则

<tableRule name="mod-long"><rule><columns>id</columns><algorithm>mod-long</algorithm></rule></tableRule><function name="mod-long" class="org.opencloudb.route.function.PartitionByMod"><!-- how many data nodes --><property name="count">2</property></function>

修改schema.xml文件，进行分库分表配置：

<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema><schema name="P2P" checkSQLschema="true" sqlMaxLimit="100">      <table name="fu_users" primaryKey="user_id" dataNode="dn1,dn2" rule="mod-long" />      <table name="fu_admin" primaryKey="admin_id" dataNode="dn1" />      <table name="fu_apply_recover" primaryKey="id" dataNode="dn1" />       <table name="fu_apply_repay" primaryKey="id" dataNode="dn1" />       <table name="fu_auth_code" primaryKey="id" dataNode="dn1" />      <table name="fu_loan_apply" primaryKey="id" dataNode="dn1" />        <table name="fu_loan_investment" primaryKey="id" dataNode="dn1" />       <table name="fu_loan_type" primaryKey="id" dataNode="dn1" />       <table name="fu_message" primaryKey="id" dataNode="dn1" />       <table name="fu_popularize" primaryKey="popularize_id" dataNode="dn1" />       <table name="fu_purview" primaryKey="purview_id" dataNode="dn1" />       <table name="fu_role" primaryKey="role_id" dataNode="dn1" />       <table name="fu_role_admin" primaryKey="id" dataNode="dn1" />       <table name="fu_role_purview" primaryKey="id" dataNode="dn1" />       <table name="fu_sidemenu" primaryKey="sidebar_id" dataNode="dn1" />       <table name="fu_site_account" primaryKey="id" dataNode="dn1" />       <table name="fu_site_account_log" primaryKey="id" dataNode="dn1" />       <table name="fu_site_fee" primaryKey="fee_id" dataNode="dn1" />      <table name="fu_site_fee_type" primaryKey="charge_type_id" dataNode="dn1" />      <table name="fu_token" primaryKey="token_id" dataNode="dn1" />      <table name="fu_user_account" primaryKey="id" dataNode="dn1" />      <table name="fu_user_account_log" primaryKey="id" dataNode="dn1" />      <table name="fu_user_info" primaryKey="id" dataNode="dn1" />      <table name="fu_user_job" primaryKey="id" dataNode="dn1" />      <table name="fu_user_recharge" primaryKey="recharge_id" dataNode="dn1" />      <table name="fu_user_withdrawal" primaryKey="withdrawal_id" dataNode="dn1" />      <table name="fu_users_award_log" primaryKey="id" dataNode="dn1" />      <table name="fu_users_award_type" primaryKey="id" dataNode="dn1" />    </schema><dataNode name="dn1" dataHost="dataHost1" database="furonge_test" />   <dataNode name="dn2" dataHost="dataHost2" database="furonge_test2" />      <dataHost name="dataHost1" maxCon="1000" minCon="10" balance="1"          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">          <heartbeat>select user()</heartbeat>          <writeHost host="hostM1" url="10.128.199.232:3306" user="furonge" password="12345678">          <readHost host="hostR1" url="10.128.199.234:3306" user="mycat-234" password="123"/>        </writeHost>         <writeHost host="hostS1" url="10.128.199.233:3306" user="mycat-233" password="123"></writeHost>    </dataHost>    <dataHost name="dataHost2" maxCon="1000" minCon="10" balance="1"          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">          <heartbeat>select user()</heartbeat>          <writeHost host="hostM1" url="10.128.199.232:3306" user="furonge" password="12345678">          <readHost host="hostR1" url="10.128.199.234:3306" user="mycat-234" password="123"/>        </writeHost>         <writeHost host="hostS1" url="10.128.199.233:3306" user="mycat-233" password="123"></writeHost>    </dataHost></mycat:schema>

附：mycat1.6文档