-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathMySQL主从.html
191 lines (138 loc) · 14.3 KB
/
MySQL主从.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>商伟的技术博客</title>
<meta name="description" content="">
<meta name="author" content="商伟">
<!-- HTML5 shim, for IE6-8 support of HTML elements -->
<!--[if lt IE 9]>
<script src="/theme/html5.js"></script>
<![endif]-->
<!-- Styles -->
<link href="/theme/bootstrap.min.css" rel="stylesheet">
<link href="/theme/local.css" rel="stylesheet">
<link href="/theme/pygments.css" rel="stylesheet">
<!-- Feeds -->
</head>
<body>
<div class="topbar">
<div class="topbar-inner">
<div class="container-fluid">
<a class="brand" href="/">商伟的技术博客</a>
<ul class="nav">
<li ><a href="/category/django.html">Django</a></li>
<li ><a href="/category/docker.html">Docker</a></li>
<li ><a href="/category/git.html">GIT</a></li>
<li ><a href="/category/javascript.html">JavaScript</a></li>
<li ><a href="/category/mongodb.html">Mongodb</a></li>
<li class="active"><a href="/category/mysql.html">MySQL</a></li>
<li ><a href="/category/pa-chong.html">爬虫</a></li>
<li ><a href="/category/python.html">Python</a></li>
<li ><a href="/category/rabbitmq.html">RabbitMQ</a></li>
<li ><a href="/category/redis.html">redis</a></li>
<li ><a href="/category/shu-ju-jie-gou.html">数据结构</a></li>
<li ><a href="/category/sui-shou-bi-ji.html">随手笔记</a></li>
<li ><a href="/category/supervisor.html">Supervisor</a></li>
<li ><a href="/category/vue.html">VUE</a></li>
<li ><a href="/category/wang-luo.html">网络</a></li>
<li ><a href="/category/web.html">web</a></li>
<li ><a href="/category/xiao-cheng-xu.html">小程序</a></li>
<li ><a href="/category/xu-ni-huan-jing.html">虚拟环境</a></li>
</ul>
<p class="pull-right"><a href="/archives.html">[archives]</a> <a href="/tags.html">[tags]</a></p>
</div>
</div>
</div>
<div class="container-fluid">
<div class="sidebar">
<div class="well">
<h3>Blogroll</h3>
<ul>
<li><a href="http://getpelican.com/">Pelican</a></li>
<li><a href="http://python.org/">Python.org</a></li>
<li><a href="http://jinja.pocoo.org/">Jinja2</a></li>
</ul>
<div class="social">
<h3>Social</h3>
<ul>
<li><a href="https://lienze.tech/">老渔夫吃虾米</a></li>
</ul>
</div>
</div>
</div>
<div class="content">
<div class='article'>
<div class="page-header"><h1>MySQL主从</h1></div>
<div class="well small">Permalink: <a class="more" href="/MySQL主从.html">2020-05-18 17:59:00+02:00</a>
by <a class="url fn" href="/author/shang-wei.html">商伟 </a>
in <a href="/category/mysql.html">MySQL</a>
tags: <a href="/tag/mysql.html">MySQL</a> </div>
<div><h3>1.1 mysql主从同步</h3>
<h4><em>1、mysql主从同步（复制）概念</em></h4>
<div class="highlight"><pre><span></span> <span class="mi">1</span><span class="p">.</span> <span class="err">将</span><span class="n">Mysql某一台主机数据复制到其它主机</span><span class="err">（</span><span class="n">slaves</span><span class="err">）上，并重新执行一遍来实现的。</span>
<span class="mi">2</span><span class="p">.</span> <span class="err">复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。</span>
<span class="mi">3</span><span class="p">.</span> <span class="err">主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。</span>
<span class="mi">4</span><span class="p">.</span> <span class="err">当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。</span>
<span class="mi">5</span><span class="p">.</span> <span class="err">从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。</span>
<span class="n">binlog</span><span class="err">：是二进制日志文件，用于记录</span><span class="n">mysql的数据更新或者潜在更新</span><span class="p">(</span><span class="err">比如</span><span class="n">DELETE语句执行删除而实际并没有符合条件的数据</span><span class="p">)</span>
</pre></div>
<h4><em>2、Mysql支持哪些复制</em></h4>
<div class="highlight"><pre><span></span> <span class="mi">1</span><span class="p">.</span> <span class="err">基于语句的复制</span><span class="p">:</span> <span class="err">在主服务器执行</span><span class="n">SQL语句</span><span class="err">，在从服务器执行同样语句。</span>
<span class="err">注：</span><span class="n">MySQL默认采用基于语句的复制</span><span class="err">，效率较高。一旦发现没法精确复制时</span><span class="p">,</span> <span class="err">会自动选基于行的复制。</span>
<span class="mi">2</span><span class="p">.</span> <span class="err">基于行的复制</span><span class="p">:</span> <span class="err">把改变的内容复制过去，而不是把命令在从服务器上执行一遍</span><span class="p">.</span> <span class="err">从</span><span class="n">mysql5</span><span class="p">.</span><span class="mi">0</span><span class="err">开始支持</span>
<span class="mi">3</span><span class="p">.</span> <span class="err">混合类型的复制</span><span class="p">:</span> <span class="err">默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。</span>
</pre></div>
<h4><em>3、Mysql主从复制原理</em></h4>
<div class="highlight"><pre><span></span> <span class="mi">1</span><span class="p">.</span> <span class="n">master服务器将数据的改变都记录到二进制binlog日志中</span><span class="err">，只要</span><span class="n">master上的数据发生改变</span><span class="err">，则将其改变写入二进制日志；</span>
<span class="mi">2</span><span class="p">.</span> <span class="n">salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变</span><span class="err">，如果发生改变，则开始一个</span><span class="n">I</span><span class="o">/</span><span class="n">O</span> <span class="n">Thread请求master二进制事件</span>
<span class="mi">3</span><span class="p">.</span> <span class="err">同时主节点为每个</span><span class="n">I</span><span class="o">/</span><span class="n">O线程启动一个dump线程</span><span class="err">，用于向其发送二进制事件，并保存至从节点本地的中继日志中</span>
<span class="mi">4</span><span class="p">.</span> <span class="err">从节点将启动</span><span class="n">SQL线程从中继日志中读取二进制日志</span><span class="err">，在本地重放，使得其数据和主节点的保持一致</span>
<span class="mi">5</span><span class="p">.</span> <span class="err">最后</span><span class="n">I</span><span class="o">/</span><span class="n">O</span> <span class="n">Thread和SQL</span> <span class="n">Thread将进入睡眠状态</span><span class="err">，等待下一次被唤醒。</span>
<span class="err">需要理解：</span>
<span class="mi">1</span><span class="err">）从库会生成两个线程</span><span class="p">,</span><span class="err">一个</span><span class="n">I</span><span class="o">/</span><span class="n">O线程</span><span class="p">,</span><span class="err">一个</span><span class="n">SQL线程</span><span class="p">;</span>
<span class="mi">2</span><span class="err">）</span><span class="n">I</span><span class="o">/</span><span class="n">O线程会去请求主库的binlog</span><span class="p">,</span><span class="err">并将得到的</span><span class="n">binlog写到本地的relay</span><span class="o">-</span><span class="n">log</span><span class="p">(</span><span class="err">中继日志</span><span class="p">)</span><span class="err">文件中</span><span class="p">;</span>
<span class="mi">3</span><span class="err">）主库会生成一个</span><span class="n">log</span> <span class="n">dump线程</span><span class="p">,</span><span class="err">用来给从库</span><span class="n">I</span><span class="o">/</span><span class="n">O线程传binlog</span><span class="p">;</span>
<span class="mi">4</span><span class="err">）</span><span class="n">SQL线程</span><span class="p">,</span><span class="err">会读取</span><span class="n">relay</span> <span class="n">log文件中的日志</span><span class="p">,</span><span class="err">并解析成</span><span class="n">sql语句逐一执行</span><span class="p">;</span>
</pre></div>
<h4><em>4、Mysql复制流程图</em></h4>
<div class="highlight"><pre><span></span> <span class="mi">1</span><span class="p">.</span> <span class="n">master将操作语句记录到binlog日志中</span>
<span class="mi">2</span><span class="p">.</span> <span class="n">salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变</span><span class="err">，如果发生改变</span>
<span class="mi">3</span><span class="p">.</span> <span class="n">salave开启两个线程</span><span class="err">：</span><span class="n">IO线程和SQL线程</span>
<span class="mi">1</span><span class="err">）</span><span class="n">IO线程</span><span class="err">：负责读取</span><span class="n">master的binlog内容到中继日志relay</span> <span class="n">log里</span><span class="err">；</span>
<span class="mi">2</span><span class="err">）</span><span class="n">SQL线程</span><span class="err">：负责从</span><span class="n">relay</span> <span class="n">log日志里读出binlog内容</span><span class="err">，并更新到</span><span class="n">slave的数据库里</span><span class="err">（保证数据一致）</span>
</pre></div>
<p><img alt="img" src="http://m.qpic.cn/psc?/V11vDzPO1w5782/zfrllz9Q9AzvUwq**DIV07njt*rPSgGWsLSWZSKeEasoGrc0*ZLQqicYJN5MqhwbYNWAR4VyatJbQ9mTi6Gh0A!!/b&bo=AgJdAQICXQEDCSw"></p>
<h3>1.2 MySQL同步延迟问题</h3>
<h4><em>1、造成mysql同步延迟常见原因</em></h4>
<div class="highlight"><pre><span></span> <span class="mi">1</span>）网络：如主机或者从机的带宽打满、主从之间网络延迟很大，导致主上的<span class="nv">binlog</span>没有全量传输到从机，造成延迟。
<span class="mi">2</span>）机器性能：从机使用了烂机器？比如主机使用<span class="nv">SSD</span>而从机还是使用的<span class="nv">SATA</span>。
<span class="mi">3</span>）从机高负载：有很多业务会在从机上做统计，把从机服务器搞成高负载，从而造成从机延迟很大的情况
<span class="mi">4</span>）大事务：比如在<span class="nv">RBR</span>模式下，执行带有大量的<span class="nv">delete</span>操作，这种通过查看<span class="nv">processlist</span>相关信息以及使用<span class="nv">mysqlbinlog</span>查看<span class="nv">binlog</span>中的<span class="nv">SQL</span>就能快速进行确认
<span class="mi">5</span>）锁: 锁冲突问题也可能导致从机的<span class="nv">SQL</span>线程执行慢，比如从机上有一些<span class="nv">select</span> .... <span class="k">for</span> <span class="nv">update</span>的<span class="nv">SQL</span>，或者使用了<span class="nv">MyISAM</span>引擎等。
</pre></div>
<h4><em>2、硬件方面（优化）</em></h4>
<div class="highlight"><pre><span></span> <span class="mi">1</span><span class="p">.</span><span class="err">采用好服务器，比如</span><span class="mi">4</span><span class="n">u比2u性能明显好</span><span class="err">，</span><span class="mi">2</span><span class="n">u比1u性能明显好</span><span class="err">。</span>
<span class="mi">2</span><span class="p">.</span><span class="err">存储用</span><span class="n">ssd或者盘阵或者san</span><span class="err">，提升随机写的性能。</span>
<span class="mi">3</span><span class="p">.</span><span class="err">主从间保证处在同一个交换机下面，并且是万兆环境。</span>
<span class="err">总结：硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。</span>
</pre></div>
<h4><em>3、mysql主从同步加速</em></h4>
<div class="highlight"><pre><span></span> <span class="mi">1</span><span class="err">）</span><span class="n">sync_binlog在slave端设置为0</span>
<span class="err">当事务提交后，</span><span class="n">Mysql仅仅是将binlog_cache中的数据写入Binlog文件</span><span class="err">，但不执行</span><span class="n">fsync之类的磁盘</span> <span class="err">同步指令通知文件系统将缓存刷新到磁盘</span>
<span class="err">而让</span><span class="n">Filesystem自行决定什么时候来做同步</span><span class="err">，这个是性能最好的。</span>
<span class="mi">2</span><span class="err">）</span><span class="n">slave端</span> <span class="n">innodb_flush_log_at_trx_commit</span> <span class="o">=</span> <span class="mi">2</span>
<span class="err">每次事务提交时</span><span class="n">MySQL都会把log</span> <span class="n">buffer的数据写入log</span> <span class="n">file</span><span class="err">，但是</span><span class="n">flush</span><span class="p">(</span><span class="err">刷到磁盘</span><span class="p">)</span><span class="err">操作并不会同时进行。</span>
<span class="err">该模式下，</span><span class="n">MySQL会每秒执行一次</span> <span class="n">flush</span><span class="p">(</span><span class="err">刷到磁盘</span><span class="p">)</span><span class="err">操作。</span>
<span class="mi">3</span><span class="err">）–</span><span class="n">logs</span><span class="o">-</span><span class="n">slave</span><span class="o">-</span><span class="n">updates</span> <span class="err">从服务器从主服务器接收到的更新不记入它的二进制日志。</span>
<span class="mi">4</span><span class="err">）直接禁用</span><span class="n">slave端的binlog</span>
</pre></div></div>
</div>
<footer>
<p>Powered by <a href="http://getpelican.com/">Pelican</a>. Theme based on <a href="http://twitter.github.com/bootstrap/">Twitter Bootstrap</a>.</p>
<p>© 商伟</p>
</footer>
</div>
</div>
</body>
</html>