{"id":1350,"date":"2015-09-06T17:12:46","date_gmt":"2015-09-06T09:12:46","guid":{"rendered":"http:\/\/nick.txtcc.com\/?p=1350"},"modified":"2020-04-22T13:59:44","modified_gmt":"2020-04-22T05:59:44","slug":"mysql-%e6%b7%bb%e5%8a%a0%e7%ac%acn%e4%b8%aa%e4%bb%8e%e5%ba%93","status":"publish","type":"post","link":"https:\/\/nick.txtcc.com\/index.php\/linux\/1350","title":{"rendered":"mysql \u6dfb\u52a0\u7b2cN\u4e2a\u4ece\u5e93"},"content":{"rendered":"<p><strong>\u5982\u679c\u4e3b\u670d\u52a1\u5668\u5df2\u7ecf\u5b58\u5728\u5e94\u7528\u6570\u636e\uff0c\u5219\u5728\u8fdb\u884c\u4e3b\u4ece\u590d\u5236\u65f6\uff0c\u505a\u4ee5\u4e0b\u5904\u7406\uff1a<\/strong><\/p>\n<p>\u6388\u6743\u7ed9\u4ece\u6570\u636e\u5e93\u670d\u52a1\u5668192.168.10.131\nmysql> GRANT REPLICATION SLAVE ON <em>.<\/em> to &#8216;rep1&#8217;@&#8217;192.168.10.131&#8217; identified by \u2018password\u2019;\n(1)\u4e3b\u6570\u636e\u5e93\u8fdb\u884c\u9501\u8868\u64cd\u4f5c\uff0c\u4e0d\u8ba9\u6570\u636e\u518d\u8fdb\u884c\u5199\u5165\u52a8\u4f5c\nmysql> FLUSH TABLES WITH READ LOCK;<\/p>\n<p>(2)\u67e5\u770b\u4e3b\u6570\u636e\u5e93\u72b6\u6001\nmysql> show master status;<\/p>\n<p>(3)\u8bb0\u5f55\u4e0b FILE \u53ca Position \u7684\u503c\u3002\n\u5c06\u4e3b\u670d\u52a1\u5668\u7684\u6570\u636e\u6587\u4ef6\uff08\u6574\u4e2a\/opt\/mysql\/data\u76ee\u5f55\uff09\u590d\u5236\u5230\u4ece\u670d\u52a1\u5668\uff0c\u5efa\u8bae\u901a\u8fc7tar\u5f52\u6863\u538b\u7f29\u540e\u518d\u4f20\u5230\u4ece\u670d\u52a1\u5668\u89e3\u538b\u3002\n\u6216\u8005\u7528mysqldump\u7684\u65b9\u5f0f\u5bfc\u51fasql\u6587\u4ef6<\/p>\n<p>(4)\u53d6\u6d88\u4e3b\u6570\u636e\u5e93\u9501\u5b9a\nmysql> UNLOCK TABLES;<\/p>\n<p>&nbsp;<\/p>\n<p>\u67d0\u9879\u76eemysql\u670d\u52a1\u5668 1\u4e3b 1\u4ece \uff0c\u73b0\u5728\u8981\u6dfb\u52a0\u4e00\u4e2amysql\u4ece\u670d\u52a1\u5668\uff0c\u8981\u6c42\u4e3b\u5e93\u4e0d\u80fd\u505c\u6b62\u670d\u52a1\uff0c\u4ee5\u524d\u7531\u4e8e\u4e0d\u662f\u7ebf\u4e0a\u7684\u670d\u52a1\u5668\uff0c\u53ef\u4ee5\u5728\u4e3b\u670d\u52a1\u5668\u4e0a \u6267\u884c flush tables with read lock \u8bed\u53e5(\u9501\u8868,\u53ea\u8bfb)\uff0c\u6240\u6709\u7684\u8868\u53ea\u80fd\u8bfb\u4e0d\u80fd\u5199\uff0c\u7136\u540e\u518d\u62f7\u8d1d\u4e3b\u5e93\u6570\u636e\u5230\u65b0\u7684\u4ece\u5e93\u670d\u52a1\u5668\u4e0a\uff0c\u5e76\u4fdd\u6301\u6570\u636e\u4e00\u81f4\u6027\uff0c\u73b0\u5728\u53ea\u80fd\u6362\u4e00\u79cd\u65b9\u6cd5\u4e86\uff0c\u601d\u8def \u65b0\u4ece\u5e932\u62f7\u8d1d\u8001\u7684\u4ece\u5e931\u7684\u6570\u636e\uff01<\/p>\n<p>&nbsp;<\/p>\n<p><strong>\u8001\u4ece\u5e931 \u76f8\u5173\u64cd\u4f5c:<\/strong><\/p>\n<p>#1 \u505c\u6b62 mysql\u4ece\u5e93,\u9501\u8868,\n\u8bb0\u4f4f Read_Master_Log_Pos: \u4e0e Relay_Master_Log_File:<\/p>\n<pre lang=\"php\" line=\"1\" escaped=\"true\">mysql>\u00a0stop\u00a0slave;\nmysql>\u00a0flush\u00a0tables\u00a0with\u00a0read\u00a0lock;\nmysql>\u00a0show\u00a0slave\u00a0status\\G;\n***************************\u00a01.\u00a0row\u00a0***************************\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_IO_State:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Host:\u00a0192.168.6.53\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_User:\u00a0dongnan\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Port:\u00a03306\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Connect_Retry:\u00a060\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Log_File:\u00a0mysql-bin.000097\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Read_Master_Log_Pos:\u00a019157395\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Log_File:\u00a0zabbix-slave-relay-bin.000185\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Log_Pos:\u00a011573578\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Master_Log_File:\u00a0mysql-bin.000097\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_IO_Running:\u00a0No\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_SQL_Running:\u00a0No\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Replicate_Do_DB:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Replicate_Ignore_DB:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Replicate_Do_Table:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Replicate_Ignore_Table:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Replicate_Wild_Do_Table:\n\u00a0\u00a0Replicate_Wild_Ignore_Table:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_Errno:\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_Error:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Skip_Counter:\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Exec_Master_Log_Pos:\u00a019157395\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Log_Space:\u00a019142103\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Until_Condition:\u00a0None\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Until_Log_File:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Until_Log_Pos:\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_Allowed:\u00a0No\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_CA_File:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_CA_Path:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_Cert:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_Cipher:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_Key:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Seconds_Behind_Master:\u00a0NULL\nMaster_SSL_Verify_Server_Cert:\u00a0No\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_IO_Errno:\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_IO_Error:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_SQL_Errno:\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_SQL_Error:\n1\u00a0row\u00a0in\u00a0set\u00a0(0.00\u00a0sec)\nERROR:\nNo\u00a0query\u00a0specified<\/pre>\n<p><strong>\u89e3\u91ca\uff1a<\/strong><\/p>\n<p>Master_Log_File\uff0cRead_Master_Log_Pos\u00a0\u8bb0\u5f55\u4e86IO\u00a0thread\u8bfb\u5230\u7684\u5f53\u524dmaster\u00a0binlog\u6587\u00a0\u4ef6\u548c\u4f4d\u7f6e\uff0c\u00a0\u5bf9\u5e94master\u7684binlog\u6587\u4ef6\u548c\u4f4d\u7f6e\u3002<\/p>\n<p>Relay_Log_File\uff0cRelay_Log_Pos\u8bb0\u5f55\u4e86SQL\u00a0thread\u6267\u884c\u5230relay\u00a0log\u7684\u90a3\u4e2a\u6587\u4ef6\u548c\u4f4d\u7f6e\uff0c\u5bf9\u5e94\u7684\u662fslave\u4e0a\u7684relay\u00a0log\u6587\u4ef6\u548c\u4f4d\u7f6e\u3002<\/p>\n<p>Relay_Master_Log_File\uff0cExec_Master_Log_Pos\u8bb0\u5f55\u7684\u662fSQL\u00a0thread\u6267\u884c\u5230master\u00a0binlog\u7684\u6587\u4ef6\u548c\u4f4d\u7f6e\uff0c\u5bf9\u5e94\u7684master\u4e0abinlog\u7684\u6587\u4ef6\u548c\u4f4d\u7f6e\u3002<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>#2 \u6253\u5305\u6570\u636e\u5e76\u53d1\u9001\u5230\u65b0\u4ece\u5e932\u670d\u52a1\u5668\u4e0a<\/p>\n<pre lang=\"php\" line=\"1\" escaped=\"true\">mysqldump -uroot -p --databases a b c d e f > data.sql\ntar\u00a0czvf\u00a0data.tar.gz\u00a0data.sql\nscp\u00a0zabbix_20110805.tar.gz\u00a0root@192.168.6.54:\/root\n\n<\/pre>\n<p><strong>\u65b0\u4ece\u5e932\u76f8\u5173\u64cd\u4f5c:<\/strong><\/p>\n<p>#1 \u66f4\u6539 server-id \u503c\u4e0d\u80fd\u4e3a1,\u56e0\u4e3amaster \u7684 server-id=1<\/p>\n<ol class=\"dp-xml\">\n    <li class=\"alt\">vim\u00a0\/etc\/my.cnf<\/li>\n    <li class=\"alt\"><span class=\"attribute\">server-id<\/span>\u00a0=\u00a0<span class=\"attribute-value\">3<\/span><\/li>\n<\/ol>\n<p>#2 \u5bfc\u5165mysql\u6570\u636e\u5e93\u4f7f\u7528\u00a0<span class=\"s1\">&#8211;init-command=&#8221;SET SQL_LOG_BIN = 0;&#8221; \u53c2\u6570\u53ef\u4ee5\u907f\u514d\u521d\u6b21\u5bfc\u6570\u636e\u65f6\u4ea7\u751f\u5de8\u5927binlog\u6587\u4ef6<\/span><\/p>\n<p>mysql\u00a0<span class=\"s1\">&#8211;init-command=&#8221;SET SQL_LOG_BIN = 0;&#8221;\u00a0<\/span>-u root -p < data.sql<\/p>\n<p>#3 \u542f\u52a8mysql\u6570\u636e\u5e93\u5e76change master<\/p>\n<p>Exec_Master_Log_Pos \u503c\u00a0 19157395<\/p>\n<p>Master_Log_File \u503c mysql-bin.000097<\/p>\n<p>&nbsp;<\/p>\n<pre lang=\"php\" line=\"1\" escaped=\"true\">mysql>\u00a0change\u00a0master\u00a0to\u00a0master_host='192.168.6.53',master_user='dongnan',master_password='password',master_log_file='mysql-bin.000097',master_log_pos=19157395;\nmysql>\u00a0start slave;\u00a0 #\u542f\u52a8slave\nmysql>\u00a0show\u00a0slave\u00a0status\\G;\u00a0 #\u663e\u793aslave \u72b6\u6001\n***************************\u00a01.\u00a0row\u00a0***************************\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_IO_State:\u00a0Waiting\u00a0for\u00a0master\u00a0to\u00a0send\u00a0event\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Host:\u00a0192.168.6.53\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_User:\u00a0dongnan\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Port:\u00a03306\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Connect_Retry:\u00a060\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Log_File:\u00a0mysql-bin.000097\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Read_Master_Log_Pos:\u00a021194359\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Log_File:\u00a0db1-relay-bin.000002\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Log_Pos:\u00a02037215\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Master_Log_File:\u00a0mysql-bin.000097\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_IO_Running:\u00a0Yes\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_SQL_Running:\u00a0Yes\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Replicate_Do_DB:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Replicate_Ignore_DB:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Replicate_Do_Table:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Replicate_Ignore_Table:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Replicate_Wild_Do_Table:\n\u00a0\u00a0Replicate_Wild_Ignore_Table:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_Errno:\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_Error:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Skip_Counter:\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Exec_Master_Log_Pos:\u00a021194359\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Log_Space:\u00a02037368\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Until_Condition:\u00a0None\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Until_Log_File:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Until_Log_Pos:\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_Allowed:\u00a0No\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_CA_File:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_CA_Path:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_Cert:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_Cipher:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_SSL_Key:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Seconds_Behind_Master:\u00a00\nMaster_SSL_Verify_Server_Cert:\u00a0No\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_IO_Errno:\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_IO_Error:\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_SQL_Errno:\u00a00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Last_SQL_Error:\n1\u00a0row\u00a0in\u00a0set\u00a0(0.00\u00a0sec)\nERROR:\nNo\u00a0query\u00a0specified<\/pre>\n<p>\u9a8c\u8bc1\u91cd\u5e93\u662f\u5426\u540c\u6b65:<\/p>\n<p>\u8001\u4ece\u5e931<\/p>\n<pre lang=\"php\" line=\"1\" escaped=\"true\">mysql> unlock tables;\nmysql>\u00a0start slave;\nmysql>\u00a0show\u00a0slave\u00a0status\\G;\n***************************\u00a01.\u00a0row\u00a0***************************\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_IO_State:\u00a0Waiting\u00a0for\u00a0master\u00a0to\u00a0send\u00a0event\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Host:\u00a0192.168.6.53\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_User:\u00a0dongnan\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Port:\u00a03306\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Connect_Retry:\u00a060\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Log_File:\u00a0mysql-bin.000097\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Read_Master_Log_Pos:\u00a021194359\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Log_File:\u00a0db1-relay-bin.000002\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Log_Pos:\u00a02037215\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Master_Log_File:\u00a0mysql-bin.000097\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_IO_Running:\u00a0Yes\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_SQL_Running:\u00a0Yes\n<\/pre>\n<p><span style=\"line-height: 1.5;\">\u65b0\u4ece\u5e932<\/span><\/p>\n<pre lang=\"php\" line=\"1\" escaped=\"true\">mysql>\u00a0show\u00a0slave\u00a0status\\G;\n***************************\u00a01.\u00a0row\u00a0***************************\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_IO_State:\u00a0Waiting\u00a0for\u00a0master\u00a0to\u00a0send\u00a0event\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Host:\u00a0192.168.6.53\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_User:\u00a0dongnan\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Port:\u00a03306\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Connect_Retry:\u00a060\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Master_Log_File:\u00a0mysql-bin.000097\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Read_Master_Log_Pos:\u00a021194359\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Log_File:\u00a0db1-relay-bin.000002\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Log_Pos:\u00a02037215\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Relay_Master_Log_File:\u00a0mysql-bin.000097\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_IO_Running:\u00a0Yes\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Slave_SQL_Running:\u00a0Yes<\/pre>\n<p>&nbsp;<\/p>\n<p>\u5982\u679c\u9047\u5230\u9519\u8bef\u5c1d\u8bd5\u505c\u6b62slave\u8bbe\u7f6e\u4ee5\u4e0b\u53c2\u6570\u540e\u518d\u542f\u52a8<\/p>\n<p>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;<\/p>\n<p>\u7ed3\u675f<\/p>\n<p>\u65e2\u7136\u4e3b\u5e93\u4e0d\u80fd\u52a8\uff0c\u90a3\u5c31\u53bb\u64cd\u4f5c\u4ece\u5e93\u5427\uff0c\u65b0\u4ece\u5e932\u62f7\u8d1d\u8001\u7684\u4ece\u5e931\u7684\u6570\u636e\uff01<\/p>\n<p>\u8865\u5145:\n\u4e3b\u5e93\u521b\u5efa\u540c\u6b65\u8d26\u53f7: grant replication slave on <em>.<\/em> to &#8216;repl&#8217;@&#8217;%&#8217; identified by &#8216;repl&#8217;;\n[mysqld]\nlog_slave_updates=1 #\u6b64\u914d\u7f6e\u5141\u8bb8\u94fe\u5f0f\u590d\u5236, \u5f53\u4f60\u9700\u8981\u94fe\u5f0f\u590d\u5236\u7684\u65f6\u5019\uff0c\u5982A->B->C\uff0c\u4f60\u5c31\u5fc5\u987b\u5728B\u4e3b\u673a\u4e0a\u7684mysql\u4e2d\u6dfb\u52a0\u4e00\u6761\u914d\u7f6e\nlog_bin = \/home\/logs\/mysql\/mysql-bin\nexpire_logs_days = 7\nmax_binlog_size = 100M\nbinlog-ignore-db=mysql\nbinlog-ignore-db=information_schema\nbinlog_format=ROW<\/p>\n<p>#\u4ece\u5e93\u914d\u7f6e\nreplicate-ignore-db=mysql\nreplicate-ignore-db=information_schema\nslave-skip-errors=1064 1146 1062 1032<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>mysqldump -u root -pPassword &#8211;all-databases | ssh user@new_host.host.com &#8216;cat &#8211; | mysql -u root -pPassword&#8217;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h1 class=\"postTitle\">MySQL replicate-ignore-db\u8be6\u89e3<\/h1>\n<div class=\"clear\"><\/div>\n<div class=\"postBody\">\n<div id=\"cnblogs_post_body\" class=\"blogpost-body \">\n\n1\uff1a\u5b98\u65b9\u7684\u89e3\u91ca\u662f\uff1a\u5728\u4e3b\u4ece\u540c\u6b65\u7684\u73af\u5883\u4e2d\uff0creplicate-ignore-db\u7528\u6765\u8bbe\u7f6e\u4e0d\u9700\u8981\u540c\u6b65\u7684\u5e93\u3002\u89e3\u91ca\u7684\u592a\u7b80\u5355\u4e86\uff0c\u4f46\u662f\u91cc\u9762\u8fd8\u6709\u5f88\u591a\u5751\u5462\u3002\n\n\u751f\u4ea7\u5e93\u4e0a\u4e0d\u5efa\u8bae\u8bbe\u7f6e\u8fc7\u6ee4\u89c4\u5219\u3002\u5982\u679c\u975e\u8981\u8bbe\u7f6e\uff0c\u90a3\u5c31\u7528Replicate_Wild_Ignore_Table: mysql.%\u5427\u3002\u5b9e\u9a8c\u7684\u5f88\u7b80\u5355\uff0c\u5982\u4e0b\n<div class=\"cnblogs_Highlighter sh-gutter\">\n<div id=\"highlighter_335947\" class=\"syntaxhighlighter sql\">\n<div class=\"line number1 index0 alt2\">\n<pre lang=\"php\" line=\"1\" escaped=\"true\">\u7b2c\u4e00\u79cd\u60c5\u51b5\n\u4ece\u5e93\uff1a\nreplicate-ignore-db = mysql\n\u4e3b\u5e93\uff1a\nuse mysql\nCREATE\u00a0TABLE\u00a0test.testrepl1(\nid\u00a0int(5))ENGINE=INNODB\u00a0DEFAULT\u00a0CHARSET=UTF8;\n\u4ece\u5e93\u4e0d\u4f1a\u540c\u6b65\u3002\u5751\n\u00a0\n\u7b2c\u4e8c\u79cd\u60c5\u51b5\n\u4ece\u5e93\uff1a\nreplicate-ignore-db = mysql\n\u00a0\n\u4e3b\u5e93\uff1a\nuse test\nCREATE\u00a0TABLE\u00a0mysql.testrepl2(\nid\u00a0int(5))ENGINE=INNODB\u00a0DEFAULT\u00a0CHARSET=UTF8;\n\u4ece\u5e93\u4e0d\u4f1a\u540c\u6b65\u3002\n\u00a0\n\u00a0\n\u7b2c\u4e09\u79cd\u60c5\u51b5\nuse test\nupdate\u00a0mysql.user\u00a0set\u00a0user\u00a0=\u00a0'testuser5'\u00a0where\u00a0user\u00a0=\u00a0'testuser1';\n\u4ece\u5e93\u4f1a\u540c\u6b65\n\u00a0\n\u7b2c\u56db\u79cd\u60c5\u51b5\ngrant\u00a0all\u00a0on\u00a0*.*\u00a0to\u00a0testnowild@'%'\u00a0identified\u00a0by\u00a0'ge0513.hudie';\n\u4ece\u5e93\u4f1a\u540c\u6b65\n\u00a0\n\u7b2c\u4e8c\u5927\u7c7b\uff1a\nReplicate_Wild_Ignore_Table: mysql.%\n\u00a0\n\u7b2c\u4e94\u79cd\u60c5\u51b5\n\u4e3b\u5e93\uff1a\nuse test\nupdate\u00a0mysql.user\u00a0set\u00a0user\u00a0=\u00a0'testuser1'\u00a0where\u00a0user\u00a0=\u00a0'testuser5';\n\u4ece\u5e93\u6ca1\u6709\u540c\u6b65\u3002\n\u00a0\n\u7b2c\u516d\u79cd\u60c5\u51b5\n\u4e3b\u5e93\uff1a\ngrant\u00a0all\u00a0on\u00a0*.*\u00a0to\u00a0testwild@'%'\u00a0identified\u00a0by\u00a0'ge0513.hudie';\n\u4ece\u5e93\u6ca1\u6709\u540c\u6b65\u3002<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>\u5982\u679c\u4e3b\u670d\u52a1\u5668\u5df2\u7ecf\u5b58\u5728\u5e94\u7528\u6570\u636e\uff0c\u5219\u5728\u8fdb\u884c\u4e3b\u4ece\u590d\u5236\u65f6\uff0c\u505a\u4ee5\u4e0b\u5904\u7406\uff1a \u6388\u6743\u7ed9\u4ece\u6570\u636e\u5e93\u670d\u52a1\u5668192.168.10.131 mysql> GRANT REPLICATION SLAVE ON . to &#038;#82&#46;&#46;&#46;<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,101],"tags":[429,100,428],"class_list":["post-1350","post","type-post","status-publish","format-standard","hentry","category-linux","category-db","tag-master","tag-mysql","tag-slave"],"_links":{"self":[{"href":"https:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/posts\/1350","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/comments?post=1350"}],"version-history":[{"count":15,"href":"https:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/posts\/1350\/revisions"}],"predecessor-version":[{"id":2088,"href":"https:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/posts\/1350\/revisions\/2088"}],"wp:attachment":[{"href":"https:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/media?parent=1350"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/categories?post=1350"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nick.txtcc.com\/index.php\/wp-json\/wp\/v2\/tags?post=1350"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}