{"id":395,"date":"2020-06-07T19:58:48","date_gmt":"2020-06-07T11:58:48","guid":{"rendered":"http:\/\/www.leohe.net.cn\/?p=395"},"modified":"2020-06-08T11:06:32","modified_gmt":"2020-06-08T03:06:32","slug":"centos7%e7%9a%84mysql-galera-cluster%e9%9b%86%e7%be%a4hoproxykeepalived","status":"publish","type":"post","link":"https:\/\/china-abc.leohe.net.cn\/?p=395","title":{"rendered":"centos7\u7684mysql Galera Cluster3\u96c6\u7fa4+hoproxy+keepalived"},"content":{"rendered":"<p>\u5b98\u65b9\u6587\u6863\u5730\u5740\uff1a<\/p>\n<p><strong><a href=\"https:\/\/galeracluster.com\/documentation-webpages\/training\/tutorials\/index.html\">https:\/\/galeracluster.com\/documentation-webpages\/training\/tutorials\/index.html<\/a><\/strong><\/p>\n<p>\u6574\u4f53\u6846\u67b6\u56fe\u5982\u4e0b\uff1a<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-full wp-image-396\" src=\"http:\/\/www.leohe.net.cn\/wp-content\/uploads\/2020\/06\/haproxy_ha.png\" alt=\"\" width=\"608\" height=\"547\" srcset=\"https:\/\/china-abc.leohe.net.cn\/wp-content\/uploads\/2020\/06\/haproxy_ha.png 608w, https:\/\/china-abc.leohe.net.cn\/wp-content\/uploads\/2020\/06\/haproxy_ha-300x270.png 300w\" sizes=\"(max-width: 608px) 100vw, 608px\" \/><\/p>\n<p>\u5176\u4e2dmysql Galera Cluster\u9700\u8981\u81f3\u5c11\u4e09\u53f0\u673a\u5668\uff0chaproxy\u4e24\u53f0\u4ee5\u8fdb\u884c\u8d1f\u8f7d\u5747\u8861\uff0ckeepalived\u662f\u540c\u65f6\u5efa\u7acb\u572848\u548c49\u4e0a\u7684\uff0c\u6765\u4f7f\u7528\u865a\u62dfIP\uff080.50\uff09\u6765\u5404\u5916\u90e8\u5f00\u653e\u63a5\u53e3\u8fdb\u884cmysql\u7edf\u4e00\u8bbf\u95ee\u5730\u5740\u3002\u5f53\u67d0\u4e2aha\u5b95\u673a\u540e\u53ef\u8fdb\u884cIP\u6f02\u79fb\uff0c\u4fdd\u8bc1\u96c6\u7fa4\u7684\u7edf\u4e00\u8bbf\u95ee\u5165\u53e3\u3002<\/p>\n<p><strong>1.\u642d\u5efamysql Galera Cluster\uff080.45\uff0c0.46\uff0c0.47\uff09<\/strong><\/p>\n<p>cd \/etc\/yum.repos.d\/<\/p>\n<p>vim galera.repo<\/p>\n<p>\u5176\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n<p>[galera]<br \/>\nname = Galera<br \/>\nbaseurl = https:\/\/releases.galeracluster.com\/galera-3.29\/centos\/7\/x86_64\/<br \/>\ngpgkey = https:\/\/releases.galeracluster.com\/GPG-KEY-galeracluster.com<br \/>\ngpgcheck = 1<\/p>\n<p>[mysql-wsrep]<br \/>\nname = MySQL-wsrep<br \/>\nbaseurl = https:\/\/releases.galeracluster.com\/mysql-wsrep-5.7.29-25.21\/centos\/7\/x86_64\/<br \/>\ngpgkey = https:\/\/releases.galeracluster.com\/GPG-KEY-galeracluster.com<br \/>\ngpgcheck = 1<\/p>\n<p>\u5982\u679c\u6709\u7248\u672c\u53d8\u66f4\uff0c\u5219\u53ef\u4ee5\u8bbf\u95ee<a href=\"https:\/\/galeracluster.com\/documentation-webpages\/documentation\/install-mysql.html\">https:\/\/galeracluster.com\/documentation-webpages\/documentation\/install-mysql.html<\/a>\u00a0\u8fdb\u884c\u76f8\u5e94\u8bbe\u7f6e<\/p>\n<p><strong>\u5b89\u88c5<\/strong><\/p>\n<p>yum install galera-3 mysql-wsrep-5.7<\/p>\n<p><strong>\u914d\u7f6e<\/strong><\/p>\n<p>vim \/etc\/my.cnf<\/p>\n<p>\u6ce8\u610f\u5168\u90e8\u5185\u5bb9\u5e76\u6dfb\u52a0\u4ee5\u4e0b\u4fe1\u606f\uff1a<\/p>\n<p>!includedir \/etc\/my.cnf.d\/<\/p>\n<p>cd \/etc\/my.cnf.d\/<\/p>\n<p>vim wsrep.cnf<\/p>\n<p>\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n<p>log_timestamps=SYSTEM<br \/>\ndatadir=\/var\/lib\/mysql<br \/>\nsocket=\/var\/lib\/mysql\/mysql.sock<br \/>\n<strong>server_id=11<\/strong><br \/>\n# Disabling symbolic-links is recommended to prevent assorted security risks<br \/>\nsymbolic-links=0<\/p>\n<p>log-error=\/var\/log\/mysqld.log<br \/>\npid-file=\/var\/run\/mysqld\/mysqld.pid<br \/>\ndefault_storage_engine=InnoDB<br \/>\ninnodb_autoinc_lock_mode=2<\/p>\n<p>wsrep_cluster_name='cs_cluster'<br \/>\nwsrep-provider=\/usr\/lib64\/galera-3\/libgalera_smm.so<br \/>\nbinlog_format=ROW<br \/>\nbind-address=0.0.0.0<br \/>\ninnodb_flush_log_at_trx_commit=0<br \/>\ninnodb_buffer_pool_size=122M<br \/>\nwsrep_provider_options=\"gcache.size=300M; gcache.page_size=300M\"<br \/>\n<strong>wsrep_cluster_address=\"gcomm:\/\/192.168.0.45,192.168.0.46,192.168.0.47\"<\/strong><br \/>\nwsrep_sst_method=rsync<br \/>\n<strong>wsrep_node_name=node1<\/strong><br \/>\n<strong>wsrep_node_address=\"192.168.0.45\"<\/strong><\/p>\n<p>\u4ee5\u4e0a\u5185\u5bb9\u4e09\u53f0\u673a\u5668\u4e2d\u52a0\u7c97\u5904\u8bbe\u7f6e\u4e0d\u540c\uff0c<strong>wsrep_cluster_address<\/strong>\u628a\u96c6\u7fa4\u7684\u5730\u5740\u8f93\u51fa\u5168\u5373\u53ef,<strong>server_id<\/strong>\u5728\u6bcf\u53f0\u8fdb\u884c\u533a\u522b<\/p>\n<p><strong>\u542f\u52a8\uff1a<\/strong><\/p>\n<pre><span class=\"go\">\/usr\/bin\/mysqld_bootstrap<\/span> --wsrep-new-cluster\uff08\u7b2c\u4e00\u53f0\u542f\u52a8\u65f6\u9700\u8981\u6dfb\u52a0<strong>--wsrep-new-cluster<\/strong>\u53c2\u6570\uff09<\/pre>\n<p>\u540e\u7eed\u5176\u5b83\u673a\u5668\u542f\u52a8\u53ea\u9700\u8981systemctl start mysql<\/p>\n<p>mysql\u6b63\u5e38\u542f\u52a8\u540e\u5373\u53ef\uff0c<\/p>\n<p><strong>\u767b\u5f55\u67e5\u770b<\/strong><\/p>\n<p>root\u9ed8\u8ba4\u5bc6\u7801\u53ef\u4ee5\u901a\u8fc7\u4ee5\u4e0b\u547d\u4ee4\u4ecemysqld.log\u4e2d\u67e5\u770b<\/p>\n<p>cat mysqld.log | grep 'temporary password'<\/p>\n<p>mysqld.log\u5730\u5740\u53ef\u4ee5\u67e5\u770bmysql\u914d\u7f6e\u6587\u4ef6\u5177\u4f53\u914d\u7f6e<\/p>\n<p>mysql -uroot -p<\/p>\n<pre><span class=\"k\">SHOW<\/span> <span class=\"n\">STATUS<\/span> <span class=\"k\">LIKE<\/span> <span class=\"s1\">'wsrep_cluster_size'<\/span><span class=\"p\">;<\/span><\/pre>\n<pre><span class=\"o\">+--------------------+-------+<\/span>\r\n<span class=\"o\">|<\/span> <span class=\"n\">Variable_name<\/span>      <span class=\"o\">|<\/span> <span class=\"n\">Value<\/span> <span class=\"o\">|<\/span>\r\n<span class=\"o\">+--------------------+-------+<\/span>\r\n<span class=\"o\">|<\/span> <span class=\"n\">wsrep_cluster_size<\/span> <span class=\"o\">|<\/span> 3 \u00a0 \u00a0\u00a0<span class=\"o\">|<\/span>\r\n<span class=\"o\">+--------------------+-------+\r\n\u8868\u793a\u5f53\u524d\u67093\u53f0\u673a\u5668 <\/span><\/pre>\n<p><strong>2.\u642d\u5efahaproxy\u8d1f\u8f7d\u5747\u8861\uff0ckeepalived\u76d1\u63a7\u865a\u62dfip<\/strong><\/p>\n<p><strong>\u5b89\u88c5haproxy,keepalived<\/strong><\/p>\n<p>yum -y install haproxy keepalived<\/p>\n<p><strong>\u4fee\u6539\u914d\u7f6ehaproxy\uff080.48\uff0c0.49)<\/strong><\/p>\n<p>cd \/etc\/haproxy\/<br \/>\n<span class=\"p\">cp haproxy.cfg{,.bak}<br \/>\nvim \/etc\/haproxy\/haproxy.cfg<br \/>\n\u542f\u7528web\u7ba1\u7406\uff1a<br \/>\nlisten admin_stats<br \/>\nstats enable<br \/>\nbind *:8080<br \/>\nmode http<br \/>\noption httplog<br \/>\nlog global<br \/>\nmaxconn 10<br \/>\nstats refresh 30s<br \/>\nstats uri \/admin<br \/>\nstats realm haproxy<br \/>\nstats auth admin:admin<br \/>\nstats hide-version<br \/>\nstats admin if TRUE<\/span><\/p>\n<p>\u76d1\u542cmysql\uff1a<br \/>\nlisten mysql_proxy 0.0.0.0:3306<br \/>\nmode tcp<br \/>\nbalance leastconn<br \/>\nserver mysql1 192.168.0.45:3306 weight 1 maxconn 151 check<br \/>\nserver mysql2 192.168.0.46:3306 weight 1 maxconn 151 check<br \/>\nserver mysql3 192.168.0.47:3306 weight 1 maxconn 151 check<br \/>\n<strong>\u6709check\u53c2\u6570\uff0cweb\u7ba1\u7406\u9875\u624d\u53ef\u8fdb\u884c\u72b6\u6001\u68c0\u6d4b<\/strong><br \/>\n\u542f\u52a8haproxy<br \/>\nsystemctl start haproxy<br \/>\nsystemctl enable haproxy.service #\u8bbe\u7f6e\u5f00\u673a\u81ea\u52a8\u542f\u52a8<\/p>\n<p><strong>\u914d\u7f6ekeepalived(0.48 \u4e3b)<br \/>\n<\/strong>cd \/etc\/keepalived\/<br \/>\nvim keepalived.conf<br \/>\n\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n<p>global_defs {<br \/>\nnotification_email {<br \/>\nroot@localhost<br \/>\n}<br \/>\nnotification_email_from root@localhost<br \/>\nsmtp_server localhost<br \/>\nsmtp_connect_timeout 30<br \/>\nrouter_id youxi1<br \/>\n}<br \/>\nvrrp_script chk_haproxy {\/\/\u8981\u68c0\u6d4b\u7684\u811a\u672c\u4fe1\u606f<br \/>\nscript \"\/etc\/keepalived\/check_haproxy.sh\"<br \/>\ninterval 5<br \/>\nweight -5<br \/>\n}<br \/>\nvrrp_instance VI_1 {<br \/>\nstate MASTER<br \/>\ninterface ens33<br \/>\nvirtual_router_id 51\/\/\u4e3b\u4ece\u91cc\u6b64\u503c\u8981\u8bbe\u7f6e\u76f8\u540c<br \/>\npriority 100<br \/>\nadvert_int 1<br \/>\nauthentication {<br \/>\nauth_type PASS<br \/>\nauth_pass 1111<br \/>\n}<br \/>\nvirtual_ipaddress {<br \/>\n192.168.0.50 \/\/\u8fd9\u91cc\u4e3a\u8981\u865a\u62df\u51fa\u6765\u7684ip<br \/>\n}<br \/>\ntrack_script {<br \/>\nchk_haproxy<br \/>\n}<br \/>\n}<\/p>\n<p>\/etc\/keepalived\/check_haproxy.sh<br \/>\n\u5185\u5bb9\u5982\u4e0b\uff1a<br \/>\nA=`ps -C haproxy --no-header | wc -l`<br \/>\nif [ $A -eq 0 ]; then<br \/>\nsystemctl restart haproxy<br \/>\necho \"Start haproxy\" &amp;&gt; \/dev\/null<br \/>\nsleep 3<br \/>\nif [ `ps -C haproxy --no-header | wc -l` -eq 0 ];then<br \/>\nsystemctl kill keepalived<br \/>\necho \"Stop keepalived\" &amp;&gt; \/dev\/null<br \/>\nfi<br \/>\nfi<\/p>\n<p>chmod +x check_haproxy.sh<br \/>\n<strong>\u542f\u52a8\u670d\u52a1\uff1a(0.48)<\/strong><br \/>\nsystemctl start keepalived<\/p>\n<p><strong>\u914d\u7f6ekeepalived(0.49\u00a0\u4ece)<\/strong><\/p>\n<p>cd \/etc\/keepalived\/<br \/>\nvim keepalived.conf<br \/>\n\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n<p>global_defs {<br \/>\nnotification_email {<br \/>\nroot@localhost<br \/>\n}<br \/>\nnotification_email_from root@localhost<br \/>\nsmtp_server localhost<br \/>\nsmtp_connect_timeout 30<br \/>\nrouter_id youxi2<br \/>\n}<br \/>\nvrrp_script chk_haproxy {<br \/>\nscript \"\/etc\/keepalived\/check_haproxy.sh\"<br \/>\ninterval 5<br \/>\nweight -5<br \/>\n}<br \/>\nvrrp_instance VI_1 {<br \/>\nstate BACKUP<br \/>\ninterface ens33<br \/>\nvirtual_router_id 51<br \/>\npriority 98<br \/>\nadvert_int 1<br \/>\nauthentication {<br \/>\nauth_type PASS<br \/>\nauth_pass 1111<br \/>\n}<br \/>\nvirtual_ipaddress {<br \/>\n192.168.0.50<br \/>\n}<br \/>\ntrack_script {<br \/>\nchk_haproxy<br \/>\n}<br \/>\n}<\/p>\n<p>\/etc\/keepalived\/check_haproxy.sh<br \/>\n\u5185\u5bb9\u5982\u4e0b\uff1a<br \/>\nA=`ps -C haproxy --no-header | wc -l`<br \/>\nif [ $A -eq 0 ]; then<br \/>\nsystemctl restart haproxy<br \/>\necho \"Start haproxy\" &amp;&gt; \/dev\/null<br \/>\nsleep 3<br \/>\nif [ `ps -C haproxy --no-header | wc -l` -eq 0 ];then<br \/>\nsystemctl kill keepalived<br \/>\necho \"Stop keepalived\" &amp;&gt; \/dev\/null<br \/>\nfi<br \/>\nfi<\/p>\n<p>chmod +x check_haproxy.sh<br \/>\n<strong>\u542f\u52a8\u670d\u52a1\uff1a(0.49)<\/strong><br \/>\nsystemctl start keepalived<\/p>\n<p>\u4ee5\u4e0a\u96c6\u7fa4\u7ec4\u4ef6\u5b8c\u6210\u3002<\/p>\n<p><strong>\u4f7f\u7528\uff1a<\/strong><\/p>\n<p>\u5e94\u7528\u53ef\u4ee5\u8fde\u63a5 192.168.0.50:3306\u7aef\u53e3\u8fdb\u884c\u6570\u636e\u5e93\u7684\u8bbf\u95ee\u3002<\/p>\n<p>\u547d\u4ee4ps aux | grep keepalived | grep -v grep | awk '{print $2}'| xargs kill\u53ef\u4ee5\u6740\u6389keepalived\u8fdb\u7a0b<\/p>\n<p>http:\/\/192.168.0.49:8080\/admin<\/p>\n<p>\u8f93\u5165\u8d26\u53f7\u548c\u5bc6\u7801admin\/admin\u53ef\u4ee5\u8fdb\u5165 haroxy\u7684web\u7ba1\u7406\u754c\u9762<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-full wp-image-400\" src=\"http:\/\/www.leohe.net.cn\/wp-content\/uploads\/2020\/06\/QQ\u56fe\u724720200608005908.png\" alt=\"\" width=\"3817\" height=\"1138\" srcset=\"https:\/\/china-abc.leohe.net.cn\/wp-content\/uploads\/2020\/06\/QQ\u56fe\u724720200608005908.png 3817w, https:\/\/china-abc.leohe.net.cn\/wp-content\/uploads\/2020\/06\/QQ\u56fe\u724720200608005908-300x89.png 300w, https:\/\/china-abc.leohe.net.cn\/wp-content\/uploads\/2020\/06\/QQ\u56fe\u724720200608005908-768x229.png 768w, https:\/\/china-abc.leohe.net.cn\/wp-content\/uploads\/2020\/06\/QQ\u56fe\u724720200608005908-1024x305.png 1024w\" sizes=\"(max-width: 3817px) 100vw, 3817px\" \/><\/p>\n<p><strong>\u4f7f\u7528\u95ee\u9898\uff1a<\/strong><br \/>\n1.<strong>\u91cd\u542f\u7f51\u7edc\u540evip\u6d88\u5931<\/strong>\uff0c\u4e00\u822c\u4e0d\u5efa\u8bae\u4f7f\u7528service network restart\u91cd\u542f\u7f51\u7edc\uff0c\u5982\u679c\u9700\u8981\u91cd\u542f\u7f51\u7edc\uff0c\u53ef\u4ee5\u5728\u5176\u540e\uff0c\u4f7f\u7528\u4ee5\u4e0b\u547d\u4ee4\u8fdb\u884c\u624b\u52a8\u7ed1\u5b9a\u3002<br \/>\nip addr add 192.168.0.50 dev ens33<\/p>\n<p>&nbsp;<\/p>\n<p>\u53c2\u8003\u6587\u4ef6\uff1a<\/p>\n<p>https:\/\/www.cnblogs.com\/robbinluobo\/p\/8294782.html<\/p>\n<p>https:\/\/blog.csdn.net\/wzy0623\/article\/details\/102522268<\/p>\n<p>https:\/\/www.cnblogs.com\/fawaikuangtu123\/p\/10915229.html<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5b98\u65b9\u6587\u6863\u5730\u5740\uff1a https:\/\/galeracluster.com\/documentation-webpage [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[3,9],"tags":[],"_links":{"self":[{"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/posts\/395"}],"collection":[{"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=395"}],"version-history":[{"count":6,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/posts\/395\/revisions"}],"predecessor-version":[{"id":403,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=\/wp\/v2\/posts\/395\/revisions\/403"}],"wp:attachment":[{"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=395"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=395"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/china-abc.leohe.net.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=395"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}