0032-每特学院蚂蚁课堂mysql优化之分表分库与读写分离

1、Myql主与读写分主概-B-A的slavebinlog过来的，并不relay logMyql主与读写分主概-B-A的slavebinlog过来的，并不relay log，-relaylogwindowsip 分别为172.27.185.1(主)、172.27.185.2(从GRANTREPLICATIONSLAVEON*.*windowsip 分别为172.27.185.1(主)、172.27.185.2(从GRANTREPLICATIONSLAVEON*.*TOroot172.27.185.2IDENTIFIEDBY 4. my.ini 5. 360WiFi start slave;show

2、slaveserver-id=replicate-do-db=itmayieduInsert 、delete、update、createScale-up Scale-out ScaleUp Application 可以在垂直方向上扩展。一般对单台机器而言，Scale Up Insert 、delete、update、createScale-up Scale-out ScaleUp Application 可以在垂直方向上扩展。一般对单台机器而言，Scale Up 某个计算节点（机器）CPU Mycat读写分离适用于读远比写的场景，如果有一台服务器，当select很多时，update和 dele

3、te会被这些select只负责各自的写和读，极大程度的缓解X锁和S锁争用；有1主3从，不考虑上述1中提到的从库单方面设置，假设现在1分钟内有10条写入，150条 下来每台服务器承担了10条写入和50条 Mycat安创建CREATEDATABASEMycat安创建CREATEDATABASEIFNOTEXISTS-Tablestructurefort_users-DROPTABLEIFEXISTSCREATETABLEt_usersuser_idvarchar(64NOTNULLCOMMENT用户varchar(64NOTNULLCOMMENT用户邮箱user_passwordvarchar(6

4、4)NOTNULLCOMMENT用user_nikenamevarchar(64NOTNULLCOMMENT用户昵称user_creatimedatetimeNOTNULLCOMMENT时间us(1NOTNULLCOMMENT验证状态 1：已验证 0：未验证user_deleteflag tiny(1NOTNULLCOMMENT删除标记 1：已删除0：未删除, PRIMARY KEY (user_id)ENGINE=InnoDBDEFAULT Mycat中间件的原理是对数据进行分片处理，从原有的一个库，被切分为多个分片数据RAID0.-Tablestructurefor-DROPTABLEIF

5、EXISTSCREATETABLEt_messagemessages_idvarchar(64)NOTNULLCOMMENTuser_idvarchar(64NOTNULLCOMMENT用户messages_infovarchar(255DEFAULTNULLCOMMENT内容messages_timedatetimeDEFAULTNULLCOMMENT(12DEFAULTNULLCOMMENT评论次数message_deleteflag(1NOTNULLCOMMENT删除标记10：未删除(12DEFAULTNULLCOMMENT被浏览量, PRIMARY KEY (messages_id),

6、KEYuser_idt_message_ibfk_1 FOREIGN KEY (user_id) REFEREN)ENGINE=InnoDBDEFAULT?xml?xml!DOCTYPEmycat:schemaSYSTEM !- server.xml user schemas schemaname=mycatcheckSQLschema=truedataNodename=dn1dataHost=jdbchost dataHostname=jdbchostmaxCon=1000minCon=10writeType=0 dbType=select!- useruserpropertypropert

7、y!- useruserpropertypropertypropertyrule.xml文为了更好地定位错误，rule.xml文为了更好地定位错误，修改 levelvalue=debug 常见问SHOWMASTERUS my.inigrantallprivilegeson*.*toroot172.27.185.1identifiedby?xml !-LicensedundertheApacheLicense,2.0(theyoumaynotusethisfileexceptincompliancewiththeLicense.-You may obtain a copy of the License at - -Unlessrequiredbyapplicablelaworagreedtoinwriting,softwaredistributedundertheLicenseisdistributedonanASISBASIS, -WARRANTIES OR CONDITIONS OF ANY KIND, either express or impd.-Seethe License