{"id":813,"date":"2025-01-10T09:38:46","date_gmt":"2025-01-10T01:38:46","guid":{"rendered":"http:\/\/192.168.5.24\/?p=813"},"modified":"2025-06-06T17:25:23","modified_gmt":"2025-06-06T09:25:23","slug":"mysql%e4%b8%bb%e4%bb%8e%e5%a4%8d%e5%88%b6%e4%b8%8e%e8%af%bb%e5%86%99%e5%88%86%e7%a6%bb%e9%85%8d%e7%bd%ae-2","status":"publish","type":"post","link":"https:\/\/bigvip.dpdns.org\/?p=813","title":{"rendered":"Mysql\u4e3b\u4ece\u590d\u5236\u4e0e\u8bfb\u5199\u5206\u79bb\u914d\u7f6e"},"content":{"rendered":"\n

\u4e00\u3001\u670d\u52a1\u5668\u89c4\u5212<\/h2>\n\n\n\n
\u670d\u52a1\u5668\u8282\u70b9<\/td>IP<\/td><\/tr>
Mysql-master<\/td>192.168.168.105<\/td><\/tr>
Mysql-slave<\/td>192.168.168.106<\/td><\/tr>
Mycat<\/td>192.168.168.101<\/td><\/tr><\/tbody><\/table>
\u670d\u52a1\u5668\u89c4\u5212<\/figcaption><\/figure>\n\n\n\n

\u4e8c\u3001Mysql\u5b89\u88c5\u914d\u7f6e<\/h2>\n\n\n\n

\u4e24\u4e2a\u8282\u70b9(\u4e3b-\u4ece\u6a21\u5f0f)\u7684\u5b89\u88c5<\/p>\n\n\n\n

yum -y install mysql-community-server\n\u4e3b\u8282\u70b9\u914d\u7f6e\uff1a<\/strong>\n\uff081\uff09\u4fee\u6539\u4e3b\u914d\u7f6e\u6587\u4ef6\nvi \/etc\/my.cnf\n\u2026\u2026\nserver-id=123\nlog-bin=abc\n\u2026\u2026\n\uff082\uff09\u542f\u52a8mysql\u670d\u52a1\nsystemctl enable --now mysqld\n(3)\u4fee\u6539\u5bc6\u7801\u7b56\u7565\nmysql>set global validate_password.policy=LOW; \nmysql>set global validate_password.length=4; \n\uff083\uff09\u521b\u5efa\u540c\u6b65\u8d26\u53f7slave\uff1a\nmysql>create user 'slave'@'%' identified by '123456';\nmysql>grant  replication slave on *.* to 'slave'@'%';\nmysql>flush privileges;\n(4)\u67e5\u770bbinlog\u6587\u4ef6\u540d\u53ca\u4f4d\u7f6e\uff1a\nmysql> SHOW BINARY LOG STATUS;\n+------------+----------+--------------+------------------+-------------------+\n| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |\n+------------+----------+--------------+------------------+-------------------+\n| abc.000001 |     861 |              |                  |                   |\n+------------+----------+--------------+------------------+-------------------+\n1 row in set (0.00 sec)\n\n\u4ece\u8282\u70b9\u914d\u7f6e\uff1a<\/strong>\n\uff081\uff09\u4fee\u6539\u4ece\u914d\u7f6e\u6587\u4ef6\nvi \/etc\/my.cnf\n\u2026\u2026\nserver-id=456\n\u2026\u2026\n\uff082\uff09\u4fee\u6539\u6570\u636e\u5e93\u7684\u914d\u7f6e\nmysql -uroot -p\nstop replica;\n\nCHANGE REPLICATION SOURCE TO\nSOURCE_HOST = '192.168.168.105',\nSOURCE_USER = 'slave',\nSOURCE_PASSWORD = '123456',\nSOURCE_PORT = 3306,\nSOURCE_LOG_FILE = 'abc.000001',\nSOURCE_LOG_POS = 861,\nSOURCE_SSL = 1;\n\nstart replica;\n\n\uff083\uff09\u67e5\u770b\u540c\u6b65\u72b6\u6001\uff1a\nmysql> show replica status \\G;\n*************************** 1. row ***************************\n             Replica_IO_State: Waiting for source to send event\n                  Source_Host: 192.168.168.105\n                  Source_User: slave\n                  Source_Port: 3306\n                Connect_Retry: 60\n              Source_Log_File: abc.000006\n          Read_Source_Log_Pos: 7991\n               Relay_Log_File: localhost-relay-bin.000012\n                Relay_Log_Pos: 5986\n        Relay_Source_Log_File: abc.000006\n           Replica_IO_Running: Yes\n          Replica_SQL_Running: Yes<\/strong>\n<\/code><\/pre>\n\n\n\n
\u53cc\u4e3b\u6a21\u5f0f<\/p>\n\n\n\n
\u6bcf\u4e2a\u8282\u70b9\u4e0a<\/strong>\uff1a\n\uff081\uff09\u542f\u52a8mysql\u670d\u52a1systemctl enable --now mysqld\n (2)\u4fee\u6539\u5bc6\u7801\u7b56\u7565\nmysql>set global validate_password.policy=LOW; \nmysql>set global validate_password.length=4; \n\uff083\uff09\u521b\u5efa\u540c\u6b65\u8d26\u53f7slave\uff1a\nmysql>create user 'slave'@'%' identified by '123456';\nmysql>grant slave replication on *.* to 'slave'@'%';\nmysql>flush privaleges;\n\n\u8282\u70b91\u914d\u7f6e\uff1a<\/strong>\n\uff081\uff09\u4fee\u6539\u914d\u7f6e\u6587\u4ef6\nvi \/etc\/my.cnf\n\u2026\u2026\nserver-id=123\nlog-bin=abc\nauto_increment_offset=1  # \u81ea\u589e\u5b57\u6bb5\u8d77\u59cb\u503c\uff0c\u4e0e\u670d\u52a1\u5668A\u914d\u5408\u907f\u514d\u51b2\u7a81\nauto_increment_increment=2  # \u81ea\u589e\u5b57\u6bb5\u6b65\u957f\nsync_binlog=1  # \u6bcf\u63d0\u4ea4\u4e00\u6b21\u4e8b\u52a1\u540c\u6b65\u4e00\u6b21\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5230\u78c1\u76d8\ngtid_mode=ON  # \u5f00\u542f\u5168\u5c40\u4e8b\u52a1\u6807\u8bc6\u7b26\u6a21\u5f0f\nenforce_gtid_consistency=ON  # \u5f3a\u5236GTID\u4e00\u81f4\u6027\n\u2026\u2026\n\uff082\uff09\u4fee\u6539\u6570\u636e\u5e93\u7684\u914d\u7f6e\nmysql -uroot -p\nstop replica;\n\nCHANGE REPLICATION SOURCE TO\nSOURCE_HOST = '192.168.168.106',\nSOURCE_USER = 'slave',\nSOURCE_PASSWORD = '123456',\nSOURCE_PORT = 3306,\nSOURCE_AUTO_POSITION = 1,\nSOURCE_SSL = 1;\n\nstart replica;\n(3)\u67e5\u770b\u540c\u6b65\u72b6\u6001\uff1a\n\n\u8282\u70b92\u914d\u7f6e\uff1a<\/strong>\n\uff081\uff09\u4fee\u6539\u914d\u7f6e\u6587\u4ef6\nvi \/etc\/my.cnf\n\u2026\u2026\nserver-id=456\nlog-bin=cde\nauto_increment_offset=2  # \u81ea\u589e\u5b57\u6bb5\u8d77\u59cb\u503c\uff0c\u4e0e\u670d\u52a1\u5668A\u914d\u5408\u907f\u514d\u51b2\u7a81\nauto_increment_increment=2  # \u81ea\u589e\u5b57\u6bb5\u6b65\u957f\nsync_binlog=1  # \u6bcf\u63d0\u4ea4\u4e00\u6b21\u4e8b\u52a1\u540c\u6b65\u4e00\u6b21\u4e8c\u8fdb\u5236\u65e5\u5fd7\u5230\u78c1\u76d8\ngtid_mode=ON  # \u5f00\u542f\u5168\u5c40\u4e8b\u52a1\u6807\u8bc6\u7b26\u6a21\u5f0f\nenforce_gtid_consistency=ON  # \u5f3a\u5236GTID\u4e00\u81f4\u6027\n\u2026\u2026\n\uff082\uff09\u4fee\u6539\u6570\u636e\u5e93\u7684\u914d\u7f6e\nmysql -uroot -p\nstop replica;\n\nCHANGE REPLICATION SOURCE TO\nSOURCE_HOST = '192.168.168.105',\nSOURCE_USER = 'slave',\nSOURCE_PASSWORD = '123456',\nSOURCE_PORT = 3306,\nSOURCE_AUTO_POSITION = 1,\nSOURCE_SSL = 1;\n\nstart replica;\n\n\uff083\uff09\u67e5\u770b\u540c\u6b65\u72b6\u6001\uff1a<\/code><\/pre>\n\n\n\n
\u4e09\u3001Mycat\u5b89\u88c5\u914d\u7f6e<\/h2>\n\n\n\n
yum -y install jdk1.8\n\uff081\uff09\u89e3\u538bmycat\n\uff082\uff09\u5347\u7ea7jdbc\n\u628a\u65b0\u7248\u672c\u7684mysql-connector-j-8.4.0.jar\u62f7\u8d1d\u5230mycat\u76ee\u5f55\u4e0b\u7684lib\u4e2d\uff0c\u5e76\u628a\u539f\u7248\u672c\u7684mysql-connector\u5220\u9664\u3002\n\uff083\uff09\u4fee\u6539\u914d\u7f6e\u6587\u4ef6\uff1a\nvi conf\/schema.xml\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\uff09\u2014\u2014\n<?xml version=\"1.0\"?>\n<!DOCTYPE mycat:schema SYSTEM \"schema.dtd\">\n<mycat:schema xmlns:mycat=\"http:\/\/io.mycat\/\">\n    <schema name=\"TESTDB\" checkSQLschema=\"false\"  sqlMaxLimit=\"100\" dataNode=\"dn1\">\n    <\/schema>\n    <dataNode name=\"dn1\" dataHost=\"localhost1\" database=\"ruoyi\" \/>\n    <dataHost name=\"localhost1\" maxCon=\"1000\" minCon=\"10\" balance=\"2\"\n                      writeType=\"0\" dbType=\"mysql\" dbDriver=\"jdbc\" switchType=\"1\"  slaveThreshold=\"100\">\n            <heartbeat>select user()<\/heartbeat>\n            <writeHost host=\"hostM1\" url=\"jdbc:mysql:\/\/192.168.168.105:3306?allowPublicKeyRetrieval=true&amp;useSSL=false&amp;serverTimezone=Asia\/Shanghai&amp;characterEncoding=utf8\" user=\"slave\"\n                               password=\"123456\">\n                    <readHost host=\"hostS1\" url=\"jdbc:mysql:\/\/192.168.168.106:3306?allowPublicKeyRetrieval=true&amp;useSSL=false&amp;serverTimezone=Asia\/Shanghai&amp;characterEncoding=utf8\" user=\"slave\" password=\"123456\" \/>\n            <\/writeHost>\n    <\/dataHost>\n<\/mycat:schema>\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014end\u2014\nvi conf\/server.xml\n\u628aTESTDB\u4fee\u6539\u4e3a\u4f60\u7684\u6570\u636e\u5e93\u7684\u540d\u79f0ruoyi<\/code><\/pre>\n\n\n\n
\u56db\u3001\u6d4b\u8bd5<\/h2>\n\n\n\n
\u5728mycat\u4e0a\u6d4b\u8bd5\uff1a\nmysql -P9066 -uroot -p123456\nmysql> show @@heartbeat ;<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
\u4e00\u3001\u670d\u52a1\u5668\u89c4\u5212 \u670d\u52a1\u5668\u8282\u70b9 IP Mysql-master 192.168.168.105 Mysql-sla […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"aside","meta":{"footnotes":""},"categories":[20,36],"tags":[109,15],"class_list":["post-813","post","type-post","status-publish","format-aside","hentry","category-linux","category-36","tag-mycat","tag-mysql","post_format-post-format-aside"],"_links":{"self":[{"href":"https:\/\/bigvip.dpdns.org\/index.php?rest_route=\/wp\/v2\/posts\/813","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bigvip.dpdns.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bigvip.dpdns.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bigvip.dpdns.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bigvip.dpdns.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=813"}],"version-history":[{"count":23,"href":"https:\/\/bigvip.dpdns.org\/index.php?rest_route=\/wp\/v2\/posts\/813\/revisions"}],"predecessor-version":[{"id":1258,"href":"https:\/\/bigvip.dpdns.org\/index.php?rest_route=\/wp\/v2\/posts\/813\/revisions\/1258"}],"wp:attachment":[{"href":"https:\/\/bigvip.dpdns.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=813"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bigvip.dpdns.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=813"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bigvip.dpdns.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}