{"id":1052,"date":"2022-03-30T19:39:36","date_gmt":"2022-03-30T11:39:36","guid":{"rendered":"https:\/\/www.buyao007.icu\/?p=1052"},"modified":"2022-03-30T20:36:54","modified_gmt":"2022-03-30T12:36:54","slug":"15-mysql-%e9%ab%98%e5%8f%af%e7%94%a8%e6%9e%b6%e6%9e%84","status":"publish","type":"post","link":"https:\/\/www.buyao007.icu\/?p=1052","title":{"rendered":"15.MySQL \u9ad8\u53ef\u7528\u67b6\u6784"},"content":{"rendered":"\n<p>\u5b59\u5bcc\u9633\uff0c \u6c5f\u6e56\u4eba\u79f0\u6ca1\u4eba\u79f0\u3002\u591a\u5e74\u4e92\u8054\u7f51\u8fd0\u7ef4\u5de5\u4f5c\u7ecf\u9a8c\uff0c\u66fe\u8d1f\u8d23\u8fc7\u5b59\u5e03\u65af\u5927\u89c4\u6a21\u96c6\u7fa4\u67b6\u6784\u81ea\u52a8\u5316\u8fd0\u7ef4\u7ba1\u7406\u5de5\u4f5c\u3002\u64c5\u957fWeb\u96c6\u7fa4\u67b6\u6784\u4e0e\u81ea\u52a8\u5316\u8fd0\u7ef4\uff0c\u66fe\u8d1f\u8d23\u56fd\u5185\u67d0\u5927\u578b\u535a\u5ba2\u7f51\u7ad9\u8fd0\u7ef4\u5de5\u4f5c\u3002<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">1.\u4ec0\u4e48\u662f\u9ad8\u53ef\u7528?<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4f01\u4e1a\u9ad8\u53ef\u7528\u6807\u51c6\uff1a\u5168\u5e74\u65e0\u6545\u969c\u65f6\u95f4\r\n\u65e0\u6545\u969c\u65f6\u95f4           \u6545\u969c\u65f6\u95f4      \r\n99.9%                0.1%     = 525.6  min        KA+\u53cc\u4e3b                 \uff1a\u4eba\u4e3a\u5e72\u9884 \r\n99.99%               0.01%    = 52.56  min        MHA+\u589e\u5f3a\u534a\u540c\u6b65+GTID     \uff1a\u534a\u81ea\u52a8\u5316\r\n99.999%              0.001%   = 5.256  min        PXC \u3001 MGR \u3001MGC        : \u591a\u6d3b\r\n99.9999%             0.0001%  = 0.5256 min        \u81ea\u52a8\u5316\u3001\u4e91\u5316\u3001\u5e73\u53f0\u5316    :\r<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">2.MHA\u9ad8\u53ef\u7528\u67b6\u6784\u4ecb\u7ecd\u53ca\u642d\u5efa\u8fc7\u7a0b<\/h2>\n\n\n\n<p><strong><em>1.\u89c4\u5212<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4e3b\u5e93:\r\n51       node \r\n\u4ece\u5e93: \r\n52       node\r\n53       node    manager\r<\/code><\/pre>\n\n\n\n<p><strong><em>2.\u51c6\u5907\u73af\u5883<\/em><\/strong><\/p>\n\n\n\n<p>\u7565\u30021\u4e3b2\u4eceGTID,\u53c2\u8003\u4e0a\u4e00\u7bc7\u6587\u7ae0\u642d\u5efa<\/p>\n\n\n\n<p><strong><em>3.\u914d\u7f6e\u5173\u952e\u7a0b\u5e8f\u8f6f\u8fde\u63a5<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ln -s \/usr\/local\/mysql\/bin\/mysqlbinlog          \/usr\/bin\/mysqlbinlog\r\nln -s \/usr\/local\/mysql\/bin\/mysql                \/usr\/bin\/mysql\r\nps: \u4e3a\u4ec0\u4e48\u8981\u521b\u5efa\u8f6f\u8fde\u63a5\uff0c\u8fd9\u662f\u56e0\u4e3amha\u8c03\u7528\u7684\u662f\u7edd\u5bf9\u8def\u5f84<\/code><\/pre>\n\n\n\n<p><strong><em>4.\u914d\u7f6e\u5404\u8282\u70b9\u4e92\u4fe1(\u5404\u8282\u70b9\u4e4b\u95f4\u65e0\u5bc6\u7801SSH)<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># db01\uff1a\r\nssh-keygen -t rsa\r\nssh-copy-id -i ~\/.ssh\/id_rsa.pub 10.0.0.51\r\nscp -rp .\/.ssh 10.0.0.52:\/root\/.ssh\r\nscp -rp .\/.ssh 10.0.0.53:\/root\/.ssh\r\n\u5404\u8282\u70b9\u9a8c\u8bc1\r\ndb01:\r\nssh 10.0.0.51 date\r\nssh 10.0.0.52 date\r\nssh 10.0.0.53 date\r\ndb02:\r\nssh 10.0.0.51 date\r\nssh 10.0.0.52 date\r\nssh 10.0.0.53 date\r\ndb03:\r\nssh 10.0.0.51 date\r\nssh 10.0.0.52 date\r\nssh 10.0.0.53 date\r<\/code><\/pre>\n\n\n\n<p><strong><em>5.\u5b89\u88c5\u8f6f\u4ef6<\/em><\/strong><\/p>\n\n\n\n<p>1.\u4e0b\u8f7dmha\u8f6f\u4ef6<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mha\u5b98\u7f51\uff1ahttps:\/\/code.google.com\/archive\/p\/mysql-master-ha\/\r\ngithub\u4e0b\u8f7d\u5730\u5740\uff1ahttps:\/\/github.com\/yoshinorim\/mha4mysql-manager\/wiki\/Downloads\r\n\u5bf9\u4e8emysql8.0\u4ee5\u4e0a\u7248\u672c\u8981\u75285.7\u4ee5\u4e0a\u7684MHA\u8f6f\u4ef6<\/code><\/pre>\n\n\n\n<p>2\uff0e\u6240\u6709\u8282\u70b9\u5b89\u88c5Node\u8f6f\u4ef6\u4f9d\u8d56\u5305<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>yum -y localinstall mha4mysql-node-0.58-0.el7.centos.noarch.rpm<\/code><\/pre>\n\n\n\n<p>3\uff0e\u5728db01\u4e3b\u5e93\u4e2d\u521b\u5efamha\u9700\u8981\u7684\u7528\u6237<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>create user mha@'10.0.0.%' identified with mysql_native_password by 'mha';\r\ngrant all privileges on *.* to mha@'10.0.0.%' ;\r<\/code><\/pre>\n\n\n\n<p>\uff14\uff0eManager\u8f6f\u4ef6\u5b89\u88c5\uff08db03\uff09<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes\r\nyum install -y  mha4mysql-manager*.rpm\r<\/code><\/pre>\n\n\n\n<p><strong><em>6\uff0e\u914d\u7f6e\u6587\u4ef6\u51c6\u5907(db03)<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong>1\u521b\u5efa\u914d\u7f6e\u6587\u4ef6\u76ee\u5f55<\/strong>\r\nmkdir -p \/etc\/mha\r\n<strong>2 \u521b\u5efa\u65e5\u5fd7\u76ee\u5f55<\/strong>\r\n mkdir -p \/var\/log\/mha\/app1\r\n<strong>3 \u7f16\u8f91mha\u914d\u7f6e\u6587\u4ef6<\/strong>\r\nvim \/etc\/mha\/app1.cnf\r\n&#91;server default]\r\nmanager_log=\/var\/log\/mha\/app1\/manager        \r\nmanager_workdir=\/var\/log\/mha\/app1            \r\nmaster_binlog_dir=\/data\/binlog\/   ###\u4e3b\u5e93binlog\u4f4d\u7f6e   \r\nuser=mha                                   ###\u6570\u636e\u5e93\u7528\u6237\r\npassword=mha                               ###\u6570\u636e\u5e93\u5bc6\u7801\r\nping_interval=2                             ###\u68c0\u6d4b\u5fc3\u8df3\u95f4\u9694\r\nrepl_password=123                       ###\u4e3b\u4ece\u590d\u5236\u7528\u6237\u7684\u5bc6\u7801\r\nrepl_user=repl                               ###\r\nssh_user=root                              ###ssh\u4e92\u4fe1\u7684\u7528\u6237\r\n\r\n&#91;server1]                                   \r\nhostname=10.0.0.51\r\nport=3306                                  \r\n&#91;server2]            \r\nhostname=10.0.0.52\r\ncandidate_master=1 ###\u5907\u9009\u4e3b\u5e93\r\nport=3306\r\n&#91;server3]\r\nhostname=10.0.0.53\r\nport=3306\r<\/code><\/pre>\n\n\n\n<p><strong><em>7\uff0e\u72b6\u6001\u68c0\u67e5<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>### \u4e92\u4fe1\u68c0\u67e5\r\nmasterha_check_ssh  --conf=\/etc\/mha\/app1.cnf \r\n### \u4e3b\u4ece\u72b6\u6001\u68c0\u67e5\r\nmasterha_check_repl --conf=\/etc\/mha\/app1.cnf \r<\/code><\/pre>\n\n\n\n<p><strong><em>8\uff0e\u5f00\u542fMHA(db03)<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>nohup masterha_manager --conf=\/etc\/mha\/app1.cnf --remove_dead_master_conf --ignore_last_failover  &lt; \/dev\/null> \/var\/log\/mha\/app1\/manager.log 2>&amp;1 &amp;<\/code><\/pre>\n\n\n\n<p><strong><em>\uff19\uff0e\u67e5\u770bMHA\u72b6\u6001<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 ~]# masterha_check_status --conf=\/etc\/mha\/app1.cnf<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">\uff13.MHA\u7684\u8f6f\u4ef6\u7ed3\u6784\u53ca\u5de5\u4f5c\u539f\u7406<\/h2>\n\n\n\n<p><strong><em>1.manager \u7ec4\u4ef6<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>masterha_manger             \u542f\u52a8MHA \r\nmasterha_check_ssh      \t\u68c0\u67e5MHA\u7684SSH\u914d\u7f6e\u72b6\u51b5 \r\nmasterha_check_repl         \u68c0\u67e5MySQL\u590d\u5236\u72b6\u51b5 \r\nmasterha_master_monitor     \u68c0\u6d4bmaster\u662f\u5426\u5b95\u673a \r\nmasterha_check_status       \u68c0\u6d4b\u5f53\u524dMHA\u8fd0\u884c\u72b6\u6001 \r\nmasterha_master_switch  \t\u63a7\u5236\u6545\u969c\u8f6c\u79fb\uff08\u81ea\u52a8\u6216\u8005\u624b\u52a8\uff09\r\nmasterha_conf_host      \t\u6dfb\u52a0\u6216\u5220\u9664\u914d\u7f6e\u7684server\u4fe1\u606f\r<\/code><\/pre>\n\n\n\n<p><strong><em>2\uff0enode \u7ec4\u4ef6<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>save_binary_logs            \u4fdd\u5b58\u548c\u590d\u5236master\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7 \r\napply_diff_relay_logs       \u8bc6\u522b\u5dee\u5f02\u7684\u4e2d\u7ee7\u65e5\u5fd7\u4e8b\u4ef6\u5e76\u5c06\u5176\u5dee\u5f02\u7684\u4e8b\u4ef6\u5e94\u7528\u4e8e\u5176\u4ed6\u7684\r\npurge_relay_logs            \u6e05\u9664\u4e2d\u7ee7\u65e5\u5fd7\uff08\u4e0d\u4f1a\u963b\u585eSQL\u7ebf\u7a0b\uff09\r<\/code><\/pre>\n\n\n\n<p><strong><em>3\uff0e\u539f\u7406<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>0.\u901a\u8fc7masterha_manger\u542f\u52a8MHA manager\u8fdb\u7a0b\u3002\r\n1.\u76d1\u63a7\r\nmanager\u81ea\u52a8\u8c03\u7528\uff0cmasterha_master_monitor\u811a\u672c\uff0c\u6bcf\u9694ping _interval\u79d2\u68c0\u6d4b\u4e3b\u5e93\u5fc3\u8df3\uff0c\u6700\u591a\u56db\u6b21\u673a\u4f1a\uff0c\u5982\u679c\u90fd\u6ca1\u6709\u5fc3\u8df3\uff0c\u4e3b\u5e93\u5b95\u673a.\r\n2.\u9009\u4e3b\r\nalive\u6570\u7ec4:\u6240\u6709\u5b58\u6d3b\u8282\u70b9\r\nlastest\u6570\u7ec4:\u6700\u63a5\u8fd1\u4e3b\u5e93\u65e5\u5fd7\u91cf\u7684\u8282\u70b9(pos\u6216\u8005gtid )\r\npref\u6570\u7ec4:\u8bbe\u7f6e\u4e86candidate_master>0\u8282\u70b9\r\nbad\u6570\u7ec4\ufe30\u4e0d\u9002\u5408\u505a\u4e3b\u5e93\u8282\u70b9\uff08\u6ca1\u5f00\u542fbinlog\u3001\u65e5\u5fd7\u5dee\u5f02\u5728100000000\u5b57\u8282\u4ee5\u4e0a\u7684\u3001\u8bbe\u5b9a\u4e86no_master )\r\na.\u6ee1\u8db3lastest\u5e76\u4e14\u6ee1\u8db3pref\u6570\u7ec4\uff0c\u5e76\u4e14\u4e0d\u5728bad\u6570\u7ec4\u4e2d\u7684\u8282\u70b9\uff0c\u4f18\u5148\u9009\u62e9\u4e3a\u65b0\u4e3b.\u5982\u679c\u5b58\u5728\u591a\u4e2a\uff0c\u5219\u6309\u7167\u8282\u70b9\u53f7\u7801\u987a\u5e8f\u9009\u62e9\r\nb.\u6ee1\u8db3lastest,\u5e76\u4e14\u4e0d\u5728bad\u6570\u7ec4\u4e2d\u7684\u8282\u70b9\uff0c\u9009\u62e9\u4e3a\u65b0\u4e3b\uff0c,\u5982\u679c\u5b58\u5728\u591a\u4e2a\uff0c\u5219\u6309\u7167\u8282\u70b9\u53f7\u7801\u987a\u5e8f\u9009\u62e9\r\nc.\u6ee1\u8db3pref\u6570\u7ec4,\u5e76\u4e14\u4e0d\u5728bad\u6570\u7ec4\u4e2d\u7684\u8282\u70b9,\u9009\u62e9\u4e3a\u65b0\u4e3b\uff0c.\u5982\u679c\u5b58\u5728\u591a\u4e2a\uff0c\u5219\u6309\u7167\u8282\u70b9\u53f7\u7801\u987a\u5e8f\u9009\u62e9\r\nd.\u6ee1\u8db3alive\u6570\u7ec4,\u5e76\u4e14\u4e0d\u5728bad\u6570\u7ec4\u4e2d\u7684\u8282\u70b9,\u9009\u62e9\u4e3a\u65b0\u4e3b\uff0c.\u5982\u679c\u5b58\u5728\u591a\u4e2a\uff0c\u5219\u6309\u7167\u8282\u70b9\u53f7\u7801\u987a\u5e8f\u9009\u62e9\r\ne.\u5982\u679c\u8fd8\u6ca1\u627e\u5230\uff0c\u5c31\u9009\u4e3b\u5931\u8d25\u3002\r\n3.\u6570\u636e\u8865\u507f\r\na.\u5982\u679cssh\u5982\u679c\u80fd\u8fde\u63a5\u5230\u4e3b\u5e93\uff0c\u8c03\u7528save binary_logs\u811a\u672c\uff0c\u7acb\u5373\u4fdd\u5b58\u7f3a\u5931\u90e8\u5206binlog , scp\u5230\u5404\u4e2a\u4ece\u5e93(\/var\/tmp\/o.x\u4e34\u65f6\u6587\u4ef6)\u8fdb\u884c\u6062\u590d\r\nb.\u5982\u679cssh\u8fde\u63a5\u4e0d\u4e0a\uff0c\u901a\u8fc7apply_diff_relay_logs\u811a\u672c\u8ba1\u7b97\u4ece\u5e93\u4e4b\u95f4\u7684\u65e5\u5fd7\u5dee\u5f02\uff0c\u5e76\u4e14\u6062\u590d\u3002\r\nc.\u5197\u9664\u65b9\u6848\uff0cMHA\u63d0\u4f9b\u4e86\u4e00\u4e2abinlogserver\u529f\u80fd\uff0c\u5b9e\u65f6\u62c9\u53d6\u4e3b\u5e93\u7684binlog\u65e5\u5fd7\u5230\u5907\u4efd\u8282\u70b9\u3002\u53ef\u4ee5\u8fdb\u884c\u6570\u636e\u8865\u507f\u3002\r\n\u6700\u9760\u8c31\u529e\u6cd5:5.7+GTID+\u589e\u5f3a\u534a\u540c\u6b65\r\n4.\u4e3b\u4ece\u8eab\u4efd\u5207\u6362(masterha master switch)\r\na.\u6240\u6709\u4ece\u5e93\u89e3\u9664\u8eab\u4efd\r\nb.\u6784\u5efa\u65b0\u7684\u4e3b\u4ece\u5173\u7cfb\r\n5.\u5e94\u7528\u900f\u660e\r\nip\u6f02\u79fb( master ip failover )\r\n6.\u544a\u8b66\r\nsend report\r\n7.\u81ea\u6108(\u5f85\u5f00\u53d1)\r<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">\uff14.\u6a21\u62df\u6545\u969c\u5e76\u6062\u590d<\/h2>\n\n\n\n<p><strong><em>0 \uff0e\u5de5\u4f5c\u72b6\u6001\u67e5\u770b<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 app1]# masterha_check_status --conf=\/etc\/mha\/app1.cnf \r\napp1 (pid:17501) is running(0:PING_OK), master:10.0.0.51\r<\/code><\/pre>\n\n\n\n<p><strong><em>1\uff0e\u5b95\u4e3b\u5e93\u6d4b\u8bd5<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db01 ~]# \/etc\/init.d\/mysqld stop\r\nShutting down MySQL............ SUCCESS! \r<\/code><\/pre>\n\n\n\n<p><em><strong>2\uff0e\u770b\u65e5\u5fd7<\/strong><\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 app1]# vim \/var\/log\/mha\/app1\/manager\r<\/code><\/pre>\n\n\n\n<p><strong><em>3\uff0e\u6062\u590d<\/em><\/strong><\/p>\n\n\n\n<p>1\uff0e\u4fee\u590d\u6545\u969c\u8282\u70b9<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db01 ~]# \/etc\/init.d\/mysqld start\r\nStarting MySQL.. SUCCESS! \r\n\u5982\u679c\u751f\u4ea7\u600e\u4e48\u529e\uff1f \r\n\u6309\u5b9e\u9645\u60c5\u51b5\u3002\r<\/code><\/pre>\n\n\n\n<p>2.\u6062\u590d\u4e3b\u4ece<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>db01\r\nchange master to \r\nmaster_host='10.0.0.52',\r\nmaster_user='repl',\r\nmaster_password='123' ,\r\nMASTER_AUTO_POSITION=1;\r\nstart slave;\r<\/code><\/pre>\n\n\n\n<p>3.\u4fee\u590d\u914d\u7f6e\u6587\u4ef6<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u65b9\u6cd5\u4e00\uff1a  \r\nvim \/etc\/mha\/app1.cnf\r\n&#91;server1]\r\nhostname=10.0.0.51\r\nport=3306\r\n\u65b9\u6cd5\u4e8c\uff1a \r\nmasterha_conf_host --command=add --conf=\/etc\/mha\/app1.cnf --hostname=10.0.0.51 --block=server10 --params=\"port=3306\"\r\nmasterha_conf_host --command=delete --conf=\/etc\/mha\/app1.cnf --block=server1\r<\/code><\/pre>\n\n\n\n<p>4.\u9884\u68c0\u6d4b\u811a\u672c<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 ~]# masterha_check_ssh  --conf=\/etc\/mha\/app1.cnf\r\n&#91;root@db03 ~]# masterha_check_repl  --conf=\/etc\/mha\/app1.cnf \r<\/code><\/pre>\n\n\n\n<p>5.\u542f\u52a8MHA<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>nohup masterha_manager --conf=\/etc\/mha\/app1.cnf --remove_dead_master_conf --ignore_last_failover  &lt; \/dev\/null> \/var\/log\/mha\/app1\/manager.log 2>&amp;1 &amp;\r\n\r\n&#91;root@db03 ~]# masterha_check_status --conf=\/etc\/mha\/app1.cnf \r\napp1 (pid:24316) is running(0:PING_OK), master:10.0.0.52\r<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">5.\u5e94\u7528\u900f\u660e--VIP<\/h2>\n\n\n\n<p>vip :\u00a0\u00a0 10.0.0.55\/24<\/p>\n\n\n\n<p><strong><em>1.vip \u6545\u969c\u8f6c\u79fb\u811a\u672c<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4e0a\u4f20\u811a\u672c\u6587\u4ef6\u5230\/usr\/local\/bin \u89e3\u538b\r\n&#91;root@db03 mha_script]# cp -a \/data\/mha_script\/*  \/usr\/local\/bin\n&#91;root@db03 bin]# chmod +x \/usr\/local\/bin\/*\n\r<\/code><\/pre>\n\n\n\n<p><strong><em>2.\u4fee\u6539\u5185\u5bb9<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# cp master_ip_failover master_ip_failover.bak\r\n\r\nmy $vip = '10.0.0.55\/24';\r\nmy $key = '1';\r\nmy $if  = 'ens33';\r\nmy $ssh_start_vip = \"\/sbin\/ifconfig $if:$key $vip\";\r\nmy $ssh_stop_vip = \"\/sbin\/ifconfig  $if:$key down\";\r\nmy $ssh_Bcast_arp= \"\/sbin\/arping -I $if -c 3 -A 10.0.0.55\";\r<\/code><\/pre>\n\n\n\n<p><strong><em>3.\u4fee\u6539Manager \u914d\u7f6e\u6587\u4ef6<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>vim \/etc\/mha\/app1.cnf \r\nmaster_ip_failover_script=\/usr\/local\/bin\/master_ip_failover\r<\/code><\/pre>\n\n\n\n<p><strong><em>4.\u91cd\u542fMHA<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# masterha_stop  --conf=\/etc\/mha\/app1.cnf \r\n&#91;root@db03 bin]# nohup masterha_manager --conf=\/etc\/mha\/app1.cnf --remove_dead_master_conf --ignore_last_failover  &lt; \/dev\/null> \/var\/log\/mha\/app1\/manager.log 2>&amp;1 &amp;\r<\/code><\/pre>\n\n\n\n<p><strong><em>5.\u624b\u5de5\u5728\u4e3b\u5e93\u6dfb\u52a0VIP<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# masterha_check_status --conf=\/etc\/mha\/app1.cnf\r\n&#91;root@db02 ~]# ifconfig eth0:1 10.0.0.55\/24\r<\/code><\/pre>\n\n\n\n<p><strong><em>6.\u6548\u679c\u6d4b\u8bd5<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u4f7f\u7528navicat \u8fde\u63a5\u6d4b\u8bd5MHA vip\u529f\u80fd<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">6.\u6545\u969c\u63d0\u9192\u529f\u80fd<\/h2>\n\n\n\n<p><strong><em>1.\u51c6\u5907\u811a\u672c<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# cp send_report send_report.bak1\r\n\r\nmy $smtp='smtp.qq.com';             # smtp\u670d\u52a1\u5668\r\nmy $mail_from='281@qq.com';    # \u53d1\u4ef6\u7bb1\r\nmy $mail_user='21';           # \u7528\u6237\u540d QQ\u53f7\r\nmy $mail_pass='gemghsvgkeyzcagh';   # \u6388\u6743\u7801\r\nmy $mail_to=&#91;'21@qq.com'];    # \u6536\u4ef6\u7bb1\r\n#my $mail_to=&#91;'to1@qq.com','to2@qq.com'];\r<\/code><\/pre>\n\n\n\n<p><strong><em>2.\u4fee\u6539\u914d\u7f6e\u6587\u4ef6<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>vim \/etc\/mha\/app1.cnf \r\n# \u6dfb\u52a0\u4e00\u884c\uff1a \r\nreport_script=\/usr\/local\/bin\/send_report\r<\/code><\/pre>\n\n\n\n<p><strong><em>3.\u91cd\u542fMHA<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# masterha_stop  --conf=\/etc\/mha\/app1.cnf \r\n&#91;root@db03 bin]# nohup masterha_manager --conf=\/etc\/mha\/app1.cnf --remove_dead_master_conf --ignore_last_failover  &lt; \/dev\/null> \/var\/log\/mha\/app1\/manager.log 2>&amp;1 &amp;\r<\/code><\/pre>\n\n\n\n<p><strong><em>4.\u6a21\u62df\u4e3b\u5e93\u5b95\u673a\u00a0<\/em><\/strong><\/p>\n\n\n\n<p>1.\u786e\u8ba4\u4e3b\u5e93<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# masterha_check_status  --conf=\/etc\/mha\/app1.cnf \r\napp1 (pid:27096) is running(0:PING_OK), master:10.0.0.52\r<\/code><\/pre>\n\n\n\n<p>2.\u5b95\u4e3b\u5e93<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db02 ~]# \/etc\/init.d\/mysqld stop \r\nShutting down MySQL............ SUCCESS! \r<\/code><\/pre>\n\n\n\n<p>3.\u89c2\u5bdfvip\u6f02\u79fb\u3001\u89c2\u5bdf\u90ae\u4ef6<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong><em>5.\u4fee\u590dMHA \u67b6\u67841\u4e3b2\u4ece<\/em><\/strong><\/h4>\n\n\n\n<p>\u7565<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">7.\u65e5\u5fd7\u8865\u507f\u7684\u5197\u4f59\u65b9\u6848--binlog_server<\/h2>\n\n\n\n<p><strong><em>1.\u521b\u5efa\u5fc5\u8981\u76ee\u5f55(db03)<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mkdir -p \/data\/binlog_server\/\r\nchown -R mysql.mysql \/data\/*\r\ncd  \/data\/binlog_server\/\r\n&#91;root@db03 ~]# mysql -e \"show slave status \\G\"|grep \"Master_Log\"\r\n              Master_Log_File: mysql-bin.000002\r\n          Read_Master_Log_Pos: 194\r\n        Relay_Master_Log_File: mysql-bin.000002\r\n          Exec_Master_Log_Pos: 194\r\n&#91;root@db03 ~]# \r\n&#91;root@db03 binlog_server]# masterha_check_status --conf=\/etc\/mha\/app1.cnf \r\napp1 (pid:7057) is running(0:PING_OK), master:10.0.0.51\r\n\r\nmysqlbinlog  -R --host=10.0.0.51 --user=mha --password=mha --raw  --stop-never mysql-bin.000002 &amp;\r\n\r\n\u6ce8\u610f\uff1a\r\n\u62c9\u53d6\u65e5\u5fd7\u7684\u8d77\u70b9,\u9700\u8981\u6309\u7167\u76ee\u524d\u4ece\u5e93\u7684\u5df2\u7ecf\u83b7\u53d6\u5230\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u70b9\u4e3a\u8d77\u70b9\r<\/code><\/pre>\n\n\n\n<p><strong><em>2.\u914d\u7f6e\u6587\u4ef6\u8bbe\u7f6e<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>vim \/etc\/mha\/app1.cnf \r\n&#91;binlog1]\r\nno_master=1\r\nhostname=10.0.0.53\r\nmaster_binlog_dir=\/data\/binlog_server\/\r<\/code><\/pre>\n\n\n\n<p><strong><em>3.\u91cd\u542fMHA<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# masterha_stop  --conf=\/etc\/mha\/app1.cnf \r\n&#91;root@db03 bin]# nohup masterha_manager --conf=\/etc\/mha\/app1.cnf --remove_dead_master_conf --ignore_last_failover  &lt; \/dev\/null> \/var\/log\/mha\/app1\/manager.log 2>&amp;1 &amp;\r<\/code><\/pre>\n\n\n\n<p><strong><em>4.\u6545\u969c\u6f14\u7ec3\u53ca\u4fee\u590d<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u989d\u5916\u4fee\u590dbinlog server \r\n&#91;root@db03 binlog_server]# cd \/data\/binlog_server\/\r\n&#91;root@db03 binlog_server]# \r\n&#91;root@db03 binlog_server]# ll\r\ntotal 4\r\n-rw-r----- 1 root root 485 Jul 15 12:09 mysql-bin.000004\r\n&#91;root@db03 binlog_server]# rm -rf *\r\n&#91;root@db03 binlog_server]#  mysql -e \"show slave status \\G\"|grep \"Master_Log\"\r\n              Master_Log_File: mysql-bin.000004\r\n          Read_Master_Log_Pos: 234\r\n        Relay_Master_Log_File: mysql-bin.000004\r\n          Exec_Master_Log_Pos: 234\r\n\r\n&#91;root@db03 binlog_server]# mysqlbinlog  -R --host=10.0.0.51 --user=mha --password=mha --raw  --stop-never mysql-bin.000006 &amp;\r\n\r\n\r\n&#91;root@db03 binlog_server]# nohup masterha_manager --conf=\/etc\/mha\/app1.cnf --remove_dead_master_conf --ignore_last_failover  &lt; \/dev\/null> \/var\/log\/mha\/app1\/manager.log 2>&amp;1 &amp;\r\n&#91;2] 8375\r\n&#91;root@db03 binlog_server]# masterha_check_status --conf=\/etc\/mha\/app1.cnf \r\napp1 (pid:8375) is running(0:PING_OK), master:10.0.0.51\r<\/code><\/pre>\n\n\n\n<p><strong><em>5.MHA \u6545\u969c\u4fee\u590d\u5927\u6cd5<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>a. \u68c0\u67e5\u4fee\u590d\u6545\u969c\u8282\u70b9(\u786c\u4ef6 OS  \u7f51\u7edc).\r\nb. \u68c0\u67e5\u4fee\u590d\u6570\u636e\u5e93\u5b9e\u4f8b \r\nc. \u68c0\u67e5\u548c\u4fee\u590d1\u4e3b2\u4ece.\r\ndb01  &lt;---->  db02 \r\nstop slave  ; \r\nreset slave;\r\ndb01  ---->  db02 \r\nd. \u68c0\u67e5\u548c\u4fee\u590d\u914d\u7f6e\u6587\u4ef6\r\ne. \u68c0\u67e5\u548c\u4fee\u590dVIP \r\nf. \u68c0\u67e5\u548c\u4fee\u590dbinlog server \r\ng. \u4f7f\u7528\u68c0\u67e5\u811a\u672c\u6700\u540e\u786e\u8ba4\r\nh.  \u542f\u52a8MHA\r<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"toc_0\">2.MHA\u7684\u7ef4\u62a4\u64cd\u4f5c - \u5728\u7ebf\u5207\u6362\u529f\u80fd<\/h2>\n\n\n\n<p><strong><em>1.\u53ea\u5207\u6362\u89d2\u8272(\u4e86\u89e3\u5373\u53ef)<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>masterha_master_switch  --conf=\/etc\/mha\/app1.cnf --master_state=alive --new_master_host=10.0.0.52 --orig_master_is_new_slave --running_updates_limit=10000\r\n\u6ce8\u610f\uff1a \r\nmaster_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes\/NO): yes\r\n1. \u6b64\u79cd\u65b9\u6cd5 \u5207\u6362\uff0c\u8981\u6ce8\u610f\u5c06\u539f\u4e3b\u5e93\uff0cFTWRL\uff0c\u5426\u5219\u4f1a\u9020\u6210\u4e3b\u4ece\u4e0d\u4e00\u81f4\u3002\r\n2. \u624b\u5de5\u5207\u6362vip\r<\/code><\/pre>\n\n\n\n<p><strong><em>2.master_ip_online_change_script\u529f\u80fd\u5b9e\u73b0<\/em><\/strong><\/p>\n\n\n\n<p>\u529f\u80fd: \u5728\u7ebf\u5207\u6362\u65f6\uff0c\u81ea\u52a8\u9501\u539f\u4e3b\u5e93\uff0cVIP\u81ea\u52a8\u5207\u6362<\/p>\n\n\n\n<p>1.\u51c6\u5907\u5207\u6362\u811a\u672c<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>vim \/usr\/local\/bin\/master_ip_online_change\r\nmy $vip = \"10.0.0.55\/24\";\r\nmy $key = \"1\";\r\nmy $ssh_start_vip = \"\/sbin\/ifconfig ens33:$key $vip\";\r\nmy $ssh_stop_vip = \"\/sbin\/ifconfig ens33:$key $vip down\";\r\nmy $ssh_Bcast_arp= \"\/sbin\/arping -I ens33 -c 3 -A 10.0.0.55\";\r<\/code><\/pre>\n\n\n\n<p>2.\u4fee\u6539MHA\u914d\u7f6e\u6587\u4ef6<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>vim \/etc\/mha\/app1.cnf\r\nmaster_ip_online_change_script=\/usr\/local\/bin\/master_ip_online_change\r<\/code><\/pre>\n\n\n\n<p>3.\u505c MHA<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# masterha_stop  --conf=\/etc\/mha\/app1.cnf <\/code><\/pre>\n\n\n\n<p>4.\u68c0\u67e5repl<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# masterha_check_repl   --conf=\/etc\/mha\/app1.cnf <\/code><\/pre>\n\n\n\n<p>5.\u5728\u7ebf\u5207\u6362<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>masterha_master_switch  --conf=\/etc\/mha\/app1.cnf --master_state=alive --new_master_host=10.0.0.51 --orig_master_is_new_slave --running_updates_limit=10000<\/code><\/pre>\n\n\n\n<p>6.\u91cd\u6784binlogserver<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# ps -ef |grep mysqlbinlog\r\nroot      28144  16272  0 17:50 pts\/1    00:00:00 mysqlbinlog -R --host=10.0.0.52 --user=mha --password=x x --raw --stop-never mysql-bin.000005\r\nroot      28529  16272  0 18:03 pts\/1    00:00:00 grep --color=auto mysqlbinlog\r\n&#91;root@db03 bin]# kill -9 28144\r\n&#91;root@db03 bin]# cd \/data\/binlog_server\/\r\n&#91;root@db03 binlog_server]# ll\r\ntotal 4\r\n-rw-r----- 1 root root 194 Apr  1 17:50 mysql-bin.000005\r\n&#91;root@db03 binlog_server]# rm -rf *\r\n&#91;root@db03 binlog_server]# mysqlbinlog  -R --host=10.0.0.51 --user=mha --password=mha --raw  --stop-never mysql-bin.000004 &amp;\r\n&#91;1] 28534\r<\/code><\/pre>\n\n\n\n<p>7.\u542f\u52a8MHA<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@db03 bin]# nohup masterha_manager --conf=\/etc\/mha\/app1.cnf --remove_dead_master_conf --ignore_last_failover  &lt; \/dev\/null> \/var\/log\/mha\/app1\/manager.log 2>&amp;1 &amp;\r\n\r\n&#91;root@db03 binlog_server]# masterha_check_status   --conf=\/etc\/mha\/app1.cnf \r\napp1 (pid:28535) is running(0:PING_OK), master:10.0.0.51\r<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5b59\u5bcc\u9633\uff0c \u6c5f\u6e56\u4eba\u79f0\u6ca1\u4eba\u79f0\u3002\u591a\u5e74\u4e92\u8054\u7f51\u8fd0\u7ef4\u5de5\u4f5c\u7ecf\u9a8c\uff0c\u66fe\u8d1f\u8d23\u8fc7\u5b59\u5e03\u65af\u5927\u89c4\u6a21\u96c6\u7fa4\u67b6\u6784\u81ea\u52a8\u5316\u8fd0\u7ef4\u7ba1\u7406\u5de5\u4f5c\u3002\u64c5\u957fWeb\u96c6 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":246,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[],"_links":{"self":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts\/1052"}],"collection":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1052"}],"version-history":[{"count":7,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts\/1052\/revisions"}],"predecessor-version":[{"id":1065,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts\/1052\/revisions\/1065"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/media\/246"}],"wp:attachment":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1052"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1052"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1052"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}