{"id":390,"date":"2017-08-01T20:13:54","date_gmt":"2017-08-01T12:13:54","guid":{"rendered":"http:\/\/www.chenweikang.top\/?p=390"},"modified":"2017-09-19T11:36:25","modified_gmt":"2017-09-19T03:36:25","slug":"mycat%e5%ae%9e%e9%aa%8c%ef%bc%882%ef%bc%89-%e9%85%8d%e7%bd%aemysql%e4%b8%bb%e4%bb%8e%e5%90%8c%e6%ad%a5","status":"publish","type":"post","link":"https:\/\/www.chenweikang.top\/?p=390","title":{"rendered":"Mycat\u4e3b\u4ece\u540c\u6b65\u3001\u8bfb\u5199\u5206\u79bb\u3001\u5206\u5e93\u5206\u8868\uff082\uff09- \u914d\u7f6eMysql\u4e3b\u4ece\u540c\u6b65"},"content":{"rendered":"<blockquote><p>\u4e0a\u4e00\u7bc7\u6587\u7ae0\u6211\u4eec\u5728\u5355\u53f0\u670d\u52a1\u5668\u5b89\u88c5\u5e76\u542f\u52a8\u4e863\u4e2aMysql\u5b9e\u4f8b\uff0c\u7aef\u53e3\u5206\u522b\u4e3a\uff0c3306\u30013307\u30013308\uff0c\u672c\u6587\u6211\u4eec\u57fa\u4e8e\u4e0a\u7bc7\u7684\u73af\u5883\u914d\u7f6emysql\u4e3b\u4ece\u540c\u6b65\uff1a<\/p>\n<p>\u4e3b\uff08master\uff09\uff1a3306<\/p>\n<p>\u4ece\uff08slave\uff09\uff1a3307<\/p>\n<p>\u4ece\uff08slave\uff09\uff1a3308<\/p><\/blockquote>\n<h2>\u914d\u7f6e\u4e3b\u670d\u52a1\u5668\uff083306\uff09<\/h2>\n<h3>1.\u9996\u5148\u5206\u522b\u767b\u9646\u4e09\u4e2amysql\u5b9e\u4f8b \uff0c\u521b\u5efa\u4e09\u4e2a\u6570\u636e\u5e93\uff0c\u4ee5\u4f9b\u6d4b\u8bd5\u3002<\/h3>\n<pre class=\"prettyprint linenums\">create database test_db;\r\n<\/pre>\n<p>\u57283306\u6570\u636e\u5e93\u521b\u5efa\u4e00\u4e2a\u7528\u4e8e\u540c\u6b65\u6570\u636e\u7684\u7528\u6237\uff1a<\/p>\n<pre class=\"prettyprint linenums\">mysql&gt;grant replication slave on *.* to 'slaveuser'@'127.0.0.1' identified by '123456';\r\nmysql&gt;flush privileges;\r\n<\/pre>\n<h3>2.\u7f16\u8f91\/etc\/my3306.cnf\u6587\u4ef6<\/h3>\n<pre class=\"prettyprint linenums\">[client]\r\nport = 3306\r\nsocket = \/tmp\/mysql3306.sock\r\ndefault-character-set=UTF\r\n\r\n#\u4e3b\u6570\u636e\u5e93master\r\n\r\nbasedir = \/usr\/local\/mysql\r\n\r\ndatadir = \/www\/mysqldata3306\r\n\r\nport = 3306\r\n\r\npid-file = \/www\/mysqldata3306\/mysqld.pid\r\n\r\nsocket = \/tmp\/mysql3306.sock\r\n\r\ncharacter_set_server=utf8\r\n\r\n#\u670d\u52a1id\r\nserver-id=1\r\n\r\n#\u542f\u7528\u4e8c\u8fdb\u5236\u65e5\u5fd7-\u5fc5\u987b\u5f00\u542f\r\nlog-bin = mysql-bin\r\n\r\n#\u9700\u8981\u540c\u6b65\u7684\u6570\u636e\u5e93\r\nbinlog-do-db = test_db\r\n\r\n#\u4e0d\u9700\u540c\u6b65\u7684\u6570\u636e\u5e93\r\nbinlog-ignore-db=information_schema\r\nbinlog-ignore-db=mysql\r\n\r\n<\/pre>\n<h3>3.\u91cd\u542f3306\u7aef\u53e3\u7684mysql\u670d\u52a1<\/h3>\n<pre class=\"prettyprint linenums\">#\u4f7f\u7528\u6211\u4eec\u4e0a\u7bc7\u5199\u7684\u811a\u672c\u505c\u6b62\u670d\u52a1\uff0c\u4e5f\u53ef\u76f4\u63a5\u67e5\u770bpid\u7136\u540eKill\r\nsh mysqlStop.sh 3306\r\n#\u542f\u52a8\r\nmysqld_safe  --defaults-extra-file=\/etc\/my3306.cnf --datadir=\/www\/mysqldata3306 --user=mysql &amp;\r\n<\/pre>\n<p>\u91cd\u542f\u540e\u67e5\u770b\u65e5\u5fd7\u662f\u5426\u6b63\u5e38\u542f\u52a8\uff0c\u7136\u540e\u767b\u5f553306\uff0c\u67e5\u770b\u4e3b\u5e93master\u4fe1\u606f<\/p>\n<pre class=\"prettyprint linenums\">#\u767b\u5f55\u53d6mysql\r\nsh mysqlLogin.sh 3306\r\n#\u5728mysql\u547d\u4ee4\u884c\u754c\u9762\u6267\u884c\r\nshow master status\\G\r\n<\/pre>\n<p>\u5f97\u5230\u5982\u4e0b\u4fe1\u606f\uff0c\u8bb0\u4f4ffile\u548cposition\u7684\u503c\uff0c\u7b49\u4f1a\u914d\u7f6e\u4e1b\u5e93\u8981\u7528\uff1a<\/p>\n<pre class=\"prettyprint linenums\">*************************** 1. row ***************************\r\n            <span style=\"color: #ff0000;\"> File: save-bin-log.000004<\/span>\r\n         <span style=\"color: #ff0000;\">Position: 1958<\/span>\r\n     Binlog_Do_DB: \r\n Binlog_Ignore_DB: \r\nExecuted_Gtid_Set: \r\n1 row in set (0.00 sec)\r\n<\/pre>\n<h2>\u914d\u7f6e\u4ece\u670d\u52a1\uff083307\u30013308\uff09<\/h2>\n<h3>1.\u505c\u6b623307\u670d\u52a1\uff0c\u7f16\u8f91\/etc\/my3307.cnf<\/h3>\n<pre class=\"prettyprint linenums\">[client]\r\nport = 3307\r\n\r\nsocket = \/tmp\/mysql3307.sock\r\n\r\ndefault-character-set=UTF\r\n\r\n[mysqld]\r\n\r\nbasedir = \/usr\/local\/mysql\r\n\r\ndatadir = \/www\/mysqldata3307\r\n\r\nport = 3307\r\n\r\nsocket = \/tmp\/mysql3307.sock\r\n\r\ncharacter_set_server=utf8\r\n\r\npid-file = \/www\/mysqldata3307\/mysqld.pid\r\n\r\n####\u4ece\u5e93\u8bbe\u7f6e############\r\n\r\nserver-id = 2  \r\n\r\nlog-bin=\"\/www\/mysqldata3307\/salve-bin-log\"\r\n\r\n#\u4e0d\u9700\u8981\u5907\u4efd\u7684\u6570\u636e\u5e93\uff1b \r\nreplicate-ignore-db=information_schema\r\nreplicate-ignore-db=mysql\r\n\r\n#\u9700\u8981\u5907\u4efd\u7684\u6570\u636e\u5e93\r\nreplicate-do-db=test_db\r\nlog-slave-update=1\r\n\r\n#2\u5929\u81ea\u52a8\u6e05\u7406\u65e5\u5fd7\r\nexpire_logs_days=2\r\n\r\nsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES \r\n\r\n[mysqld_safe]\r\nlog-error=\/www\/mysqldata3307\/mysqld.log\r\n\r\n<\/pre>\n<h3>2.\u505c\u6b623308\u670d\u52a1\uff0c\u7f16\u8f91\/etc\/my3308.cnf<\/h3>\n<p><span style=\"color: #ff0000;\">\u914d\u7f6e\u540c\u4e0a\u3001\u5c06 server-id\u6539\u4e3a3 \uff0c\u6240\u67093307\u6539\u4e3a3308<\/span><\/p>\n<h3>3.\u542f\u52a83307 \uff0c\u5e76\u767b\u5f55\uff0c\u8bbe\u7f6e\u4e1b\u5e93\u76f8\u5173\u4fe1\u606f<\/h3>\n<p>\u9996\u5148\u505c\u6b62slave<\/p>\n<pre class=\"prettyprint linenums\">#\u5148\u505c\u6b62slave\r\nmysql&gt; stop slave;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n#\u8bbe\u7f6e\u76f8\u5173\u4fe1\u606f\r\nmysql&gt; change master to \r\n\u3000\u3000 -&gt; master_host='127.0.0.1',\r\n\u3000\u3000 -&gt; master_user='salveuser',  \/\/\u5728\u4e3b\u5e93\u8bbe\u7f6e\u7684slave\u7528\u6237\r\n\u3000\u3000 -&gt; master_password='123456', \/\/\u5728\u4e3b\u5e93\u8bbe\u7f6e\u7684\u5bc6\u7801\r\n\u3000\u3000 -&gt; <span style=\"color: #ff0000;\">master_log_file='mysql-bin.000004'<\/span>,   \/\/\u6ce8\u610f\u8fd9\u91cc\u5bf9\u5e94\u521a\u521a\u5728\u4e3b\u5e93\u67e5\u8be2\u5230\u7684master status\u4fe1\u606f\u4e2d\u7684 file\r\n\u3000\u3000 -&gt; <span style=\"color: #ff0000;\">master_log_pos=1958<\/span>;  \/\/\u6ce8\u610f\u8fd9\u91cc\u5bf9\u5e94\u521a\u521a\u5728\u4e3b\u5e93\u67e5\u8be2\u5230\u7684master status\u4fe1\u606f \u4e2d\u7684position\r\n#\u542f\u52a8slave\r\nmysql&gt; start slave;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\n#\u67e5\u770bslave \u72b6\u6001\r\nmysql&gt; show slave status\\G\r\n*************************** 1. row ***************************\r\n Slave_IO_State: Waiting for master to send event\r\n Master_Host: 127.0.0.1\r\n Master_User: saveuser\r\n Master_Port: 3306\r\n Connect_Retry: 60\r\n Master_Log_File: mater-bin-log.000004\r\n Read_Master_Log_Pos: 1958\r\n Relay_Log_File: mysqld-relay-bin.000008\r\n Relay_Log_Pos: 2125\r\n Relay_Master_Log_File: mater-bin-log.000004\r\n Slave_IO_Running: Yes\r\n Slave_SQL_Running: Yes\r\n Replicate_Do_DB: test_db\r\n Replicate_Ignore_DB: mysql\r\n Replicate_Do_Table: \r\n Replicate_Ignore_Table: \r\n Replicate_Wild_Do_Table: \r\n Replicate_Wild_Ignore_Table: \r\n Last_Errno: 0\r\n Last_Error: \r\n Skip_Counter: 0\r\n Exec_Master_Log_Pos: 1958\r\n Relay_Log_Space: 2350\r\n Until_Condition: None\r\n Until_Log_File: \r\n Until_Log_Pos: 0\r\n Master_SSL_Allowed: No\r\n Master_SSL_CA_File: \r\n Master_SSL_CA_Path: \r\n Master_SSL_Cert: \r\n Master_SSL_Cipher: \r\n Master_SSL_Key: \r\n Seconds_Behind_Master: 0\r\nMaster_SSL_Verify_Server_Cert: No\r\n Last_IO_Errno: 0\r\n Last_IO_Error: \r\n Last_SQL_Errno: 0\r\n Last_SQL_Error: \r\n Replicate_Ignore_Server_Ids: \r\n Master_Server_Id: 1\r\n Master_UUID: b019fc43-70e7-11e7-b86c-00163e063f95\r\n Master_Info_File: \/www\/mysqldata3307\/master.info\r\n SQL_Delay: 0\r\n SQL_Remaining_Delay: NULL\r\n Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I\/O thread to update it\r\n Master_Retry_Count: 86400\r\n Master_Bind: \r\n Last_IO_Error_Timestamp: \r\n Last_SQL_Error_Timestamp: \r\n Master_SSL_Crl: \r\n Master_SSL_Crlpath: \r\n Retrieved_Gtid_Set: \r\n Executed_Gtid_Set: \r\n Auto_Position: 0\r\n1 row in set (0.00 sec)<\/pre>\n<p>\u770b\u5230<\/p>\n<p>Slave_IO_Running: Yes<\/p>\n<p>Slave_SQL_Running: Yes<\/p>\n<p>\u5373\u4e3a\u914d\u7f6e\u6210\u529f<\/p>\n<h3>4.\u542f\u52a83308\u914d\u7f6e\u76f8\u5173\u4fe1\u606f<\/h3>\n<p><span style=\"color: #ff0000;\">\u5177\u4f53\u64cd\u4f5c\u540c\u7b2c3\u6b65<\/span><\/p>\n<h3>5.\u6d4b\u8bd5\u4e3b\u4ece\u540c\u6b65\u662f\u5426\u6b63\u5e38<\/h3>\n<p>\u5728\u4e3b\u5e93\u4e2d\u521b\u5efa\u4e00\u4e2a\u6d4b\u8bd5\u8868t_user\u5e76\u63d2\u5165\u4e00\u6761\u6570\u636e\uff1a<\/p>\n<pre class=\"prettyprint linenums\">mysql&gt; use test_db;\r\nDatabase changed\r\nmysql&gt; create table t_user (userid int primary key , username varchar(20) , birth date , address varchar(100));\r\nQuery OK, 0 rows affected (0.02 sec)\r\nmysql&gt; insert into t_user (userid , username , birth, address) values (1,'zhangsan','1997-01-01','sss');\r\nQuery OK, 0 rows affected (0.02 sec)\r\nmysql&gt; show tables;\r\n+-------------------+\r\n| Tables_in_test_db |\r\n+-------------------+\r\n| t_user            |\r\n+-------------------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\n<p>\u57283307\u548c3308\u5206\u522b\u67e5\u770b\u662f\u5426\u5df2\u7ecf\u6709t_user<\/p>\n<p><a href=\"http:\/\/www.chenweikang.top\/wp-content\/uploads\/2017\/08\/mysql-result.png\" class=\"gallery_colorbox\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-399\" src=\"http:\/\/www.chenweikang.top\/wp-content\/uploads\/2017\/08\/mysql-result.png\"  alt=\"\" width=\"1035\" height=\"288\" srcset=\"https:\/\/www.chenweikang.top\/wp-content\/uploads\/2017\/08\/mysql-result.png 1035w, https:\/\/www.chenweikang.top\/wp-content\/uploads\/2017\/08\/mysql-result-300x83.png 300w, https:\/\/www.chenweikang.top\/wp-content\/uploads\/2017\/08\/mysql-result-768x214.png 768w, https:\/\/www.chenweikang.top\/wp-content\/uploads\/2017\/08\/mysql-result-1024x285.png 1024w\" sizes=\"auto, (max-width: 1035px) 100vw, 1035px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2>\u5176\u4ed6\u5b9e\u9a8c<\/h2>\n<ul>\n<li><a href=\"http:\/\/www.chenweikang.top\/mycat%E5%AE%9E%E9%AA%8C%EF%BC%881%EF%BC%89-%E5%8D%95%E4%B8%AA%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2%E5%A4%9A%E4%B8%AAmysql\/\">mycat\u5b9e\u9a8c\uff081\uff09-\u5355\u4e2a\u670d\u52a1\u5668\u90e8\u7f72\u591a\u4e2amysql<\/a><\/li>\n<li><a href=\"http:\/\/www.chenweikang.top\/mycat%E5%AE%9E%E9%AA%8C%EF%BC%882%EF%BC%89-%E9%85%8D%E7%BD%AEmysql%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5\/\">mycat\u5b9e\u9a8c\uff082\uff09-\u914d\u7f6emysql\u4e3b\u4ece\u540c\u6b65<\/a><\/li>\n<li><a href=\"http:\/\/www.chenweikang.top\/mycat%E5%AE%9E%E9%AA%8C%EF%BC%883%EF%BC%89-%E7%AE%80%E5%8D%95%E9%85%8D%E7%BD%AEmycat%EF%BC%8C%E5%AE%9E%E7%8E%B0%E8%AF%BB%E5%86%99%E5%88%86%E7%A6%BB\/\">mycat\u5b9e\u9a8c\uff083\uff09-\u7b80\u5355\u914d\u7f6emycat\uff0c\u5b9e\u73b0\u8bfb\u5199\u5206\u79bb<\/a><\/li>\n<li><a href=\"http:\/\/www.chenweikang.top\/mycat%E5%AE%9E%E9%AA%8C%EF%BC%884%EF%BC%89-mycat%E6%95%B0%E6%8D%AE%E5%88%86%E7%89%87%E6%B5%8B%E8%AF%95\/\">mycat\u5b9e\u9a8c\uff084\uff09-mycat\u6570\u636e\u5206\u7247\u6d4b\u8bd5<\/a><\/li>\n<\/ul>\n<p>\u8f6c\u8f7d\u8bf7\u6ce8\u660e\uff1a<a href=\"https:\/\/www.chenweikang.top\">\u5de6\u624b\u4ee3\u7801\u53f3\u624b\u8bd7<\/a> &raquo; <a href=\"https:\/\/www.chenweikang.top\/?p=390\">Mycat\u4e3b\u4ece\u540c\u6b65\u3001\u8bfb\u5199\u5206\u79bb\u3001\u5206\u5e93\u5206\u8868\uff082\uff09- \u914d\u7f6eMysql\u4e3b\u4ece\u540c\u6b65<\/a><\/p><div class=\"__youshang\">\r\n            <div id=\"__youshang_popup\" class=\"wechat popup\" style=\"display: none;\">\r\n                <div class=\"head\">~\u8c22\u8c22\u6253\u8d4f~<\/div>\r\n                <div class=\"qrcode\"><div class=\"qrcode-li wechat\" ><img src=\"https:\/\/www.chenweikang.top\/wp-content\/uploads\/2019\/07\/wexin.png\" \/><\/div><div class=\"qrcode-li alipay\" style=\"display:none;\"><img src=\"https:\/\/www.chenweikang.top\/wp-content\/uploads\/2019\/07\/ali-pay.png\" \/><\/div><div class=\"qrcode-li hongbao\" style=\"display:none;\"><img src=\"https:\/\/www.chenweikang.top\/wp-content\/uploads\/2019\/07\/ali-hongbao.png\" \/><\/div><\/div>\r\n                <ul class=\"platform\"><li class=\"icon-wechat active\" data-bg-color=\"#05af4e\" data-thanks=\"~\u8c22\u8c22\u6253\u8d4f~\"><\/li><li class=\"icon-alipay \" data-bg-color=\"#00a2ea\" data-thanks=\"~\u8c22\u8c22\u6253\u8d4f~\"><\/li><li class=\"icon-hongbao \" data-bg-color=\"#dd5746\" data-thanks=\"<p>\u626b\u7801\u9886\u7ea2\u5305<\/p><p style='margin-top: 24px;'>\uff08\u4f59\u989d\u5b9d\u652f\u4ed8\u65f6\u53ef\u62b5\u73b0\uff09<\/p>\"><\/li><\/ul>\r\n            <\/div>\r\n            <a href=\"javascript:void(0);\" id=\"__youshang_btn\">\u8d4f<\/a>\r\n        <\/div>","protected":false},"excerpt":{"rendered":"<p>\u4e0a\u4e00\u7bc7\u6587\u7ae0\u6211\u4eec\u5728\u5355\u53f0\u670d\u52a1\u5668\u5b89\u88c5\u5e76\u542f\u52a8\u4e863\u4e2aMysql\u5b9e\u4f8b\uff0c\u7aef\u53e3\u5206\u522b\u4e3a\uff0c3306\u30013307\u30013308\uff0c\u672c\u6587\u6211\u4eec\u57fa [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":363,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[45],"class_list":["post-390","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","tag-45"],"_links":{"self":[{"href":"https:\/\/www.chenweikang.top\/index.php?rest_route=\/wp\/v2\/posts\/390","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chenweikang.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chenweikang.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chenweikang.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chenweikang.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=390"}],"version-history":[{"count":0,"href":"https:\/\/www.chenweikang.top\/index.php?rest_route=\/wp\/v2\/posts\/390\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.chenweikang.top\/index.php?rest_route=\/wp\/v2\/media\/363"}],"wp:attachment":[{"href":"https:\/\/www.chenweikang.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=390"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chenweikang.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=390"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chenweikang.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=390"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}