{"id":398,"date":"2022-07-04T14:01:03","date_gmt":"2022-07-04T06:01:03","guid":{"rendered":"https:\/\/frogvps.com\/?p=398"},"modified":"2022-07-04T14:01:03","modified_gmt":"2022-07-04T06:01:03","slug":"mysql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%9a%84%e7%ba%a7%e8%81%94%e5%a4%8d%e5%88%b6%e9%85%8d%e7%bd%ae%e6%95%99%e7%a8%8b","status":"publish","type":"post","link":"https:\/\/frogvps.com\/?p=398","title":{"rendered":"MYSQL\u6570\u636e\u5e93\u7684\u7ea7\u8054\u590d\u5236\u914d\u7f6e\u6559\u7a0b"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

MYSQL\u5b9e\u73b0\u4e09\u53f0\u4e3b\u673a\u7684\u7ea7\u8054\u590d\u5236\uff0c\u6570\u636e\u4ecemaster \u590d\u5236\u5230slave1\uff0c\u518d\u7531slave1\u590d\u5236\u5230slave2\u3002<\/p>\n\n\n\n

\u914d\u7f6e\u65b9\u6cd5\u548c\u4e00\u822c\u7684\u4e3b\u4ece\u590d\u5236\u67b6\u6784\u4e00\u6837\u3002<\/p>\n\n\n\n

\u552f\u4e00\u7684\u533a\u522b\u5c31\u662f\u4e2d\u95f4\u8282\u70b9\u5fc5\u987b\u5f00\u542flog_slave_updates\u9009\u9879\u3002<\/p>\n\n\n\n

\u6b64\u9009\u9879\u5728MYSQL8.0\u4e2d\u4e3a\u9ed8\u8ba4\u503c\uff0c\u53ef\u4ee5\u4e0d\u7528\u4eba\u4e3a\u6dfb\u52a0\u3002<\/p>\n\n\n\n

\u5b9e\u73b0\u7ea7\u8054\u590d\u5236<\/h2>\n\n\n\n

\u51c6\u5907\u4e09\u53f0\u670d\u52a1\u5668\uff0c\u670d\u52a1\u5668\u79df\u7528\u7684\u6309\u65f6\u8ba1\u8d39\u7684vultr AMD<\/a><\/p>\n\n\n\n

master: 45.63.34.240<\/p>\n\n\n\n

slave1: 66.42.75.199<\/p>\n\n\n\n

slave2: 149.248.44.24<\/p>\n\n\n\n

\u64cd\u4f5c\u7cfb\u7edf\u73af\u5883\uff1a8 Stream x64<\/p>\n\n\n\n

\u6c38\u4e45\u5173\u95ed\u9632\u706b\u5899\uff08\u5982\u679c\u4f60\u7684\u4e91\u670d\u52a1\u5668\u6709\u7684\u8bdd\uff09<\/p>\n\n\n\n

systemctl disable --now firewalld<\/code><\/pre>\n\n\n\n
\u5b89\u88c5MYSQL<\/p>\n\n\n\n
yum -y install mysql-server<\/code><\/pre>\n\n\n\n
master\u914d\u7f6e<\/strong><\/p>\n\n\n\n
[mysqld]\nserver-id=240 #\u4e00\u822c\u7528ip\u6700\u540e\u4e00\u6bb5\u4f5c\u4e3a\u670d\u52a1\u5668id\nlog-bin<\/code><\/pre>\n\n\n\n
slave1\u914d\u7f6e\uff08\u4e2d\u95f4\u8282\u70b9\uff09<\/strong><\/p>\n\n\n\n
[mysqld]\nserver-id=199\nlog-bin\nread-only\nlog_slave_updates<\/code><\/pre>\n\n\n\n
slave2\u914d\u7f6e<\/strong><\/p>\n\n\n\n
[mysqld]\nserver-id=24\nlog-bin\nread-only<\/code><\/pre>\n\n\n\n
\u91cd\u542fMYSQL\u670d\u52a1<\/p>\n\n\n\n
systemctl restart mysqld<\/code><\/pre>\n\n\n\n
\u767b\u5f55master\u6570\u636e\u5e93<\/strong><\/p>\n\n\n\n
\u767b\u5f55master\u8282\u70b9\u89c2\u5bdf\u76ee\u524d\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u7684\u540d\u79f0\u548c\u4f4d\u7f6e\uff0c\u8bb0\u4f4f000002\uff0c156\u8fd9\u4e2a\u4f4d\u7f6e\u3002<\/p>\n\n\n\n
mysql> show master logs;\n+------------------+-----------+-----------+\n| Log_name         | File_size | Encrypted |\n+------------------+-----------+-----------+\n| vultr-bin.000001 |       179 | No        |\n| vultr-bin.000002 |       156 | No        |\n+------------------+-----------+-----------+\n2 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n
\u5728master\u8282\u70b9\u521b\u5efa\u590d\u5236\u8d26\u53f7\u5e76\u6388\u6743<\/p>\n\n\n\n
CREATE USER repluser@66.42.75.199 identified by '123456';\nGRANT replication slave on *.* TO repluser@66.42.75.199;<\/code><\/pre>\n\n\n\n
\u767b\u5f55slave1\u6570\u636e\u5e93<\/strong><\/p>\n\n\n\n
\u5728slave1\u8fde\u63a5\u4e3b\u8282\u70b9<\/p>\n\n\n\n
CHANGE REPLICATION SOURCE TO SOURCE_HOST='45.63.34.240',\nSOURCE_USER='repluser',\nSOURCE_PASSWORD='123456',\nSOURCE_LOG_FILE='vultr-bin.000002',\nSOURCE_LOG_POS=156;<\/code><\/pre>\n\n\n\n
\u5f00\u59cb\u590d\u5236<\/p>\n\n\n\n
START REPLICA;<\/code><\/pre>\n\n\n\n
\u67e5\u770b\u590d\u5236\u72b6\u6001<\/p>\n\n\n\n
SHOW REPLICA STATUS\\G<\/code><\/pre>\n\n\n\n
\"\"
IO\u548cSQL\u7ebf\u7a0b\u7684\u72b6\u6001\u662fYES\uff0c\u8bf4\u660e\u590d\u5236\u6ca1\u6709\u4ec0\u4e48\u9519\u8bef<\/figcaption><\/figure>\n\n\n\n
\u5728slave1\u8282\u70b9\u89c2\u5bdf\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4f4d\u7f6e<\/p>\n\n\n\n
mysql> SHOW MASTER LOGS;\n+------------------+-----------+-----------+\n| Log_name         | File_size | Encrypted |\n+------------------+-----------+-----------+\n| vultr-bin.000001 |       179 | No        |\n| vultr-bin.000002 |       156 | No        |\n+------------------+-----------+-----------+<\/code><\/pre>\n\n\n\n
\u5728slave1\u8282\u70b9\u521b\u5efa\u590d\u5236\u8d26\u53f7\u5e76\u6388\u6743<\/p>\n\n\n\n
CREATE USER repluser@149.248.44.24 identified by '123456';\nGRANT replication slave on *.* TO repluser@149.248.44.24;<\/code><\/pre>\n\n\n\n
\u767b\u5f55slave2\u6570\u636e\u5e93<\/strong><\/p>\n\n\n\n
\u5728slave2\u8282\u70b9\u8fde\u63a5slave1\u8282\u70b9<\/p>\n\n\n\n
CHANGE REPLICATION SOURCE TO SOURCE_HOST='66.42.75.199',\nSOURCE_USER='repluser',\nSOURCE_PASSWORD='123456',\nSOURCE_LOG_FILE='vultr-bin.000002',\nSOURCE_LOG_POS=156;<\/code><\/pre>\n\n\n\n
\u5f00\u59cb\u590d\u5236<\/p>\n\n\n\n
START REPLICA;<\/code><\/pre>\n\n\n\n
\u67e5\u770b\u590d\u5236\u72b6\u6001<\/p>\n\n\n\n
SHOW REPLICA STATUS\\G<\/code><\/pre>\n\n\n\n
\"\"
IO\u548cSQL\u7ebf\u7a0b\u7684\u72b6\u6001\u662fYES\uff0c\u8bf4\u660e\u590d\u5236\u6ca1\u6709\u4ec0\u4e48\u9519\u8bef<\/figcaption><\/figure>\n\n\n\n
\u4e09\u4e2a\u8282\u70b9\u5230\u8fd9\u91cc\u5c31\u5dee\u4e0d\u591a\u914d\u7f6e\u597d\u4e86\u3002<\/p>\n\n\n\n
\u73b0\u5728\u505a\u6d4b\u8bd5\uff0c\u5728master\u8282\u70b9\u521b\u5efa\u4e00\u4e2a\u6570\u636e\u5e93<\/p>\n\n\n\n
CREATE DATABASE frog1;<\/code><\/pre>\n\n\n\n
\u5728slave1\u8282\u70b9\u89c2\u5bdf\uff0c\u6570\u636e\u5df2\u7ecf\u540c\u6b65<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\u5728slave2\u8282\u70b9\u89c2\u5bdf\uff0c\u6570\u636e\u5df2\u7ecf\u540c\u6b65<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\u5728master\u8282\u70b9\u4fee\u6539\u7684\u6570\u636e\uff0c\u6210\u529f\u540c\u6b65\u5230\u4e86slave1\u548cslave2\uff0c\u8bf4\u660e\u7ea7\u8054\u590d\u5236\u642d\u5efa\u6210\u529f\u3002<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
MYSQL\u5b9e\u73b0\u4e09\u53f0\u4e3b\u673a\u7684\u7ea7\u8054\u590d\u5236\uff0c\u6570\u636e\u4ecemaster \u590d\u5236\u5230slave1\uff0c\u518d\u7531slave1\u590d\u5236\u5230slave2\u3002 \u914d\u7f6e\u65b9\u6cd5\u548c\u4e00\u822c\u7684\u4e3b\u4ece\u590d\u5236\u67b6\u6784\u4e00\u6837\u3002 \u552f\u4e00\u7684\u533a\u522b\u5c31\u662f\u4e2d\u95f4\u8282\u70b9\u5fc5\u987b\u5f00\u542flog_slave_updates\u9009\u9879\u3002 \u6b64\u9009\u9879\u5728MYSQL8…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[36],"topic":[],"class_list":["post-398","post","type-post","status-publish","format-standard","hentry","category-35","tag-mysql"],"_links":{"self":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts\/398","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=398"}],"version-history":[{"count":2,"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts\/398\/revisions"}],"predecessor-version":[{"id":405,"href":"https:\/\/frogvps.com\/index.php?rest_route=\/wp\/v2\/posts\/398\/revisions\/405"}],"wp:attachment":[{"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=398"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=398"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=398"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/frogvps.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftopic&post=398"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}