{"id":2194,"date":"2020-07-30T00:28:25","date_gmt":"2020-07-29T16:28:25","guid":{"rendered":"http:\/\/39.97.231.10\/?p=2194"},"modified":"2020-07-30T00:28:25","modified_gmt":"2020-07-29T16:28:25","slug":"mysql%e4%b8%bb%e4%bb%8e%e5%a4%8d%e5%88%b6%e4%bb%8e%e5%ba%93sql%e7%ba%bf%e7%a8%8b%e6%ba%90%e7%a0%81%e5%88%86%e6%9e%90","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=2194","title":{"rendered":"MySQL\u4e3b\u4ece\u590d\u5236\u4ece\u5e93SQL\u7ebf\u7a0b\u6e90\u7801\u5206\u6790"},"content":{"rendered":"<div>\n<div>\n<div>\nMySQL\u4e3b\u4ece\u590d\u5236\u901a\u8fc7Binlog\u8fdb\u884c\u6570\u636e\u4f20\u8f93\uff0c\u4e3b\u5e93\u5199\u5165\u6570\u636e\uff0c\u751f\u6210Binlog\uff0c\u901a\u8fc7dump\u7ebf\u7a0b\u5c06Binlog\u53d1\u9001\u7ed9\u4ece\u5e93\uff0c\u4ece\u5e93IO\u7ebf\u7a0b\u63a5\u6536Binlog\u5e76\u5199\u5165\u81ea\u5df1\u7684relay log\uff0cSQL\u7ebf\u7a0b\u5e94\u7528relay log\u91cc\u7684\u4e8b\u52a1\u3002\u672c\u6587\u7ed3\u5408MySQL\u6e90\u7801\uff0c\u5206\u6790SQL\u7ebf\u7a0b\u7684\u4e3b\u8981\u5904\u7406\u8fc7\u7a0b\u3002<br \/>\nMySQL\u6e90\u7801\u7248\u672c\uff1a5.7.19<\/p>\n<h5>1. SQL\u7ebf\u7a0b\u542f\u52a8<\/h5>\n<p>MySQL\u4ece\u5e93\u5728\u6267\u884cstart slave\u547d\u4ee4\u65f6\uff0c\u5728MySQL\u5185\u90e8\u4f9d\u6b21\u8c03\u7528\u4e0b\u9762\u7684\u51fd\u6570\u6765\u542f\u52a8IO\u7ebf\u7a0b\u548cSQL\u7ebf\u7a0b\u3002<\/p>\n<ul>\n<li>mysql_execute_command()\uff0csql\/sql_parse.cc<\/li>\n<li>start_slave_cmd()\uff0csql\/rpl_slave.cc<\/li>\n<li>start_slave()\uff0csql\/rpl_slave.cc<\/li>\n<li>start_slave_threads()\uff0csql\/rpl_slave.cc<\/li>\n<li>start_slave_thread()\uff0csql\/rpl_slave.cc<\/li>\n<\/ul>\n<p>\u5176\u4e2dSQL\u7ebf\u7a0b\u5728 start_slave_thread() \u51fd\u6570\u4e2d\u88ab\u521b\u5efa\u3002<br \/>\nstart_slave_threads \u51fd\u6570\u5728\u53e6\u5916\u4e00\u4e2a\u5730\u65b9\u4e5f\u4f1a\u88ab\u8c03\u7528\uff0cinit_slave()\u51fd\u6570\uff0c\u8fd9\u4e2a\u51fd\u6570\u662f\u5728MySQL\u542f\u52a8\u65f6\u8c03\u7528\uff0c\u5982\u679c\u6ca1\u6709\u6307\u5b9askip-slave-start\uff0c\u4e3b\u4ece\u590d\u5236\u968f\u7740MySQL\u542f\u52a8\u800c\u81ea\u52a8\u542f\u52a8\u3002<\/p>\n<h5>2. SQL\u7ebf\u7a0b\u51fd\u6570<\/h5>\n<p>MySQL\u4e3b\u4ece\u590d\u5236SQL\u7ebf\u7a0b\u7684\u7ebf\u7a0b\u51fd\u6570\u4f4d\u4e8esql\/rpl_slave.cc\u6587\u4ef6\u4e2d\uff0c\u5b9a\u4e49\u5982\u4e0b\uff1a<br \/>\nextern \\&#8221;C\\&#8221; void *handle_slave_sql(void *arg)<br \/>\n\uff081\uff09\u521b\u5efa\u5de5\u4f5c\u7ebf\u7a0b<br \/>\n\u5728MySQL 5.7 \u7248\u672c\uff0c\u5f00\u542f\u903b\u8f91\u5e76\u884c\u590d\u5236\uff0cSQL\u7ebf\u7a0b\u4f1a\u521b\u5efa\u591a\u4e2a\u5de5\u4f5c\u7ebf\u7a0b\u5e76\u53d1\u8fdb\u884crelay log\u65e5\u5fd7\u7684\u5e94\u7528\uff0c\u6e90\u7801\u4e2d\u521b\u5efa\u5de5\u4f5c\u7ebf\u7a0b\u7684\u8c03\u7528\u6808\u5982\u4e0b\uff1a<\/p>\n<ul>\n<li>handle_slave_sql()<\/li>\n<li>slave_start_workers()<\/li>\n<li>slave_start_single_worker()<\/li>\n<li>mysql_thread_create()<\/li>\n<\/ul>\n<p>\uff082\uff09\u8fdb\u5165\u5faa\u73af<br \/>\nSQL\u7ebf\u7a0b\u521b\u5efa\u5b8c\u5de5\u4f5c\u7ebf\u7a0b\u4e4b\u540e\uff0c\u4f1a\u8fdb\u5165while\u5faa\u73af\uff0c\u76f4\u5230\u505c\u6b62\u590d\u5236\u6216\u8005SQL\u7ebf\u7a0b\u88abkill\u3002<br \/>\n\u5728\u5faa\u73af\u4e2d\u7684\u4e3b\u8981\u51fd\u6570\u8c03\u7528\u5173\u7cfb\u5982\u4e0b\uff1a<\/p>\n<ul>\n<li>handle_slave_sql()\uff0cSQL\u7ebf\u7a0b\u4e3b\u51fd\u6570<\/li>\n<li>exec_relay_log_event()<\/li>\n<li>apply_event_and_update_pos()<\/li>\n<li>ev-&gt;apply_event(rli);<\/li>\n<li>ev-&gt;do_apply_event()<\/li>\n<\/ul>\n<p>exec_relay_log_event \u51fd\u6570\u8bfb\u53d6relay log\u4e2d\u7684event\u3002<br \/>\napply_event_and_update_pos\u51fd\u6570\u53bb\u5e94\u7528relay log event\u3002\u5728\u8fd9\u4e2a\u51fd\u6570\u91cc\uff0c\u4f1a\u5c06thd\u7684server_id\u8bbe\u7f6e\u6210event\u7684server_id\uff0c\u4fdd\u8bc1event\u88ab\u5e94\u7528\u540e\uff0c\u751f\u6210\u81ea\u5df1\u7684binlog\u65f6\uff0cserver_id\u4ecd\u7136\u662f\u539f\u59cb\u503c\u3002<br \/>\napply_event_and_update_pos\u51fd\u6570\u4e2d\u4f1a\u8c03\u7528\u51fd\u6570sql_delay_event()\uff0c\u7528\u4e8e\u5904\u7406\u5ef6\u8fdf\u590d\u5236\uff0c\u6bd4\u5982\u4f7f\u7528\u4e86CHANGE MASTER TO MASTER_DELAY = X \u8fd9\u6837\u7684\u8bed\u6cd5\u3002<br \/>\nev-&gt;apply_event()\u51fd\u6570\u662fevent\u81ea\u5df1\u6210\u5458\u51fd\u6570\uff0c\u8fd9\u4e2a\u51fd\u6570\u91cc\u9762\u4f1a\u53bb\u5224\u65adevent\u662f\u5426\u53ef\u4ee5\u5e76\u884c\u5e94\u7528\uff0c\u5982\u679c\u4e0d\u53ef\u4ee5\uff0c\u5c31\u5728\u5f53\u524d\u7ebf\u7a0b(SQL\u7ebf\u7a0b)\u53bb\u5904\u7406\u8fd9\u4e2aevent\uff0c\u5982\u679c\u53ef\u4ee5\u5e76\u884c\u5e94\u7528\uff0c\u5219\u4f1a\u8fd4\u56de\u4e0a\u4e00\u5c42\u51fd\u6570apply_event_and_update_pos\uff0c\u5c06event\u4e22\u8fdb\u5165\u4e00\u4e2a\u961f\u5217\uff0c\u540e\u7eedworker\u7ebf\u7a0b\u4f1a\u53bb\u8fd9\u4e2a\u961f\u5217\u4e2d\u53d6\u51faevent\u5904\u7406\u3002<\/p>\n<h5>3. \u5de5\u4f5c\u7ebf\u7a0b\u4e3b\u8981\u903b\u8f91<\/h5>\n<p>\u5de5\u4f5c\u7ebf\u7a0b\u7684\u7ebf\u7a0b\u51fd\u6570\u4e3a\uff1a<br \/>\nextern \\&#8221;C\\&#8221; void *handle_slave_worker(void *arg)<br \/>\n\u4e3b\u8981\u8c03\u7528\u5173\u7cfb\u5982\u4e0b\uff1a<\/p>\n<ul>\n<li>handle_slave_worker()\uff0c\u5de5\u4f5c\u7ebf\u7a0b\u4e3b\u51fd\u6570<\/li>\n<li>slave_worker_exec_job_group()<\/li>\n<li>slave_worker_exec_event()<\/li>\n<li>ev-&gt;do_apply_event_worker(this);<\/li>\n<li>ev-&gt;do_apply_event()<\/li>\n<li>mysql_parse()<\/li>\n<\/ul>\n<p>\u5de5\u4f5c\u7ebf\u7a0b\u5185\u90e8\u4e3b\u8981\u662f\u4e00\u4e2awhile\u5faa\u73af\uff0c\u8c03\u7528slave_worker_exec_job_group()\u51fd\u6570\u6267\u884c\u5206\u914d\u7ed9\u81ea\u5df1\u7684\u4efb\u52a1\u3002<br \/>\n\u5728slave_worker_exec_job_group\u51fd\u6570\u4e2d\uff0c\u62ff\u5230event\u540e\uff0c\u8c03\u7528\u4e0b\u9762\u8fd9\u4e2a\u51fd\u6570\u5e94\u7528event\uff1a<br \/>\nerror= worker-&gt;slave_worker_exec_event(ev);<br \/>\n\u5728slave_worker_exec_event\u51fd\u6570\u4e2d\u53c8\u4f1a\u8c03\u7528event\u81ea\u5df1\u7684\u6210\u5458\u51fd\u6570\u6765\u5e94\u7528event\uff1a<br \/>\nret= ev-&gt;do_apply_event_worker(this);<br \/>\n\u5728do_apply_event_worker\u51fd\u6570\u4e2d\uff0c\u8c03\u7528\u4e0d\u540c\u7c7b\u578bevent\u7684do_apply_event()\u6210\u5458\u51fd\u6570\u3002\u6bd4\u5982Query_log_event\u8fd9\u79cd\u7c7b\u578b\u7684event\u3002<br \/>\nQuery_log_event::do_apply_event()<br \/>\ndo_apply_event\u51fd\u6570\u4e2d\u6700\u7ec8\u8c03\u7528mysql_parse\u51fd\u6570\u6267\u884cSQL\u8bed\u53e5\u3002<\/p>\n<h5>4. \u603b\u7ed3<\/h5>\n<p>\u672c\u6587\u7b80\u5355\u5206\u6790\u4e86MySQL\u4e3b\u4ece\u590d\u5236SQL\u7ebf\u7a0b\u7684\u4e3b\u8981\u5904\u7406\u903b\u8f91\uff0cMySQL\u4e3b\u4ece\u590d\u5236\u662f\u903b\u8f91\u590d\u5236\uff0c\u4ece\u4e0a\u9762\u8fc7\u7a0b\u6765\u770b\uff0cSQL\u7ebf\u7a0b\uff08\u5305\u62ec\u5de5\u4f5c\u7ebf\u7a0b\uff09\u4ecerelay log\u4e2d\u62ff\u5230event\uff0c\u7136\u540e\u50cf\u6267\u884c\u4e00\u4e2a\u539f\u59cbSQL\u4e00\u6837\u5728\u4ece\u5e93\u4e0a\u91cd\u65b0\u6267\u884c\u4e00\u6b21\uff0c\u76f8\u5bf9\u4e8e\u7269\u7406\u590d\u5236\uff0c\u8fd9\u79cd\u65b9\u5f0f\u6548\u7387\u5e76\u4e0d\u9ad8\uff0c\u8017\u8d39\u8d44\u6e90\uff0c\u5e76\u4e14\u5bb9\u6613\u4ea7\u751f\u590d\u5236\u5ef6\u8fdf\u3002\n<\/p><\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>MySQL\u4e3b\u4ece\u590d\u5236\u901a\u8fc7Binlog\u8fdb\u884c\u6570\u636e\u4f20\u8f93\uff0c\u4e3b\u5e93\u5199\u5165\u6570\u636e\uff0c\u751f\u6210Binlog\uff0c\u901a\u8fc7dump\u7ebf\u7a0b\u5c06Binlog &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"http:\/\/www.weijing.co\/?p=2194\"> <span class=\"screen-reader-text\">MySQL\u4e3b\u4ece\u590d\u5236\u4ece\u5e93SQL\u7ebf\u7a0b\u6e90\u7801\u5206\u6790<\/span> \u67e5\u770b\u5168\u6587 &raquo;<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[90,88],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"mailpoet_newsletter_max":false,"woocommerce_thumbnail":false,"woocommerce_single":false,"woocommerce_gallery_thumbnail":false,"shop_catalog":false,"shop_single":false,"shop_thumbnail":false},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":2,"uagb_excerpt":"MySQL\u4e3b\u4ece\u590d\u5236\u901a\u8fc7Binlog\u8fdb\u884c\u6570\u636e\u4f20\u8f93\uff0c\u4e3b\u5e93\u5199\u5165\u6570\u636e\uff0c\u751f\u6210Binlog\uff0c\u901a\u8fc7dump\u7ebf\u7a0b\u5c06Binlog&hellip;","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/2194"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2194"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/2194\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2194"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}