centos7下mysql5.6的主从复制详解

熠熠古客 發表於 2023-8-22 00:00:00

<p>
<span><strong>一、mysql主从复制介绍</strong></span></p>
<p>
mysql的主从复制并不是数据库磁盘上的文件直接拷贝，而是通过逻辑的binlog日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的sql语句，重新应用到mysql数据库中。</p>
<p>
mysql数据库支持单向，双向，链式级联，环状等不同业务场景的复制，一台服务器充当主服务器master，接收来自用户的更新，而一个或多个其他服务器充当从服务器slave，接收来自主服务器binlog文件的日志内容，解析出sql，更新到从服务器。</p>
<p>
一主一从 (A -> B, A为主,B为从)</p>
<p>
一主多从 (A -> B, A -> C, A为主,B与C为从)</p>
<p>
双主双向同步 (A -> B , B -> A , A与B同为主,互相备份)</p>
<p>
线性级联 (A -> B -> C , A与B主主互备，C为从)</p>
<p>
环状级联 (A -> B -> C -> A, A、B、C都为主,各节点都可写入数据)</p>
<p>
<span><strong>二、实现mysql主从读写分离的方案</strong></span></p>
<p>
1、通过程序实现读写分离(判断语句关键字，去连接主从数据库)</p>
<p>
2、通过开源的软件实现读写分离(mysql-proxy,amoeba,稳定性和功能一般,不建议生产使用)</p>
<p>
3、独立开发DAL层软件</p>
<p>
<span><strong>三、mysql主从复制原理介绍</strong></span></p>
<p>
mysql主从复制是一个异步的复制过程，将一个主库复制到一个从库，master与slave之间实现整个过程是由三个线程参与完成。其中sql线程与I/O线程在slave端，另一个I/O线程在master端。</p>
<p>
复制原理过程</p>
<p>
1、在slave上执行start slave命令，开启主从复制开关，开始进行主从复制。</p>
<p>
2、slave的I/O线程通过master上已经授权的复制用户请求master，请求指定binlog日志的指定位置。</p>
<p>
3、master接收slave的I/O线程的请求后，其自身负责复制的I/O线程会根据slave的请求信息分批读取指定binlog日志的指定位置之后的日志信息，然后返回给slave的I/O线程，返回信息除了binlog日志外，还有master的新binlog文件名称，以及新binlog中的下一个指定更新位置。</p>
<p>
4、slave获取来自master上I/O线程发送的binlog日志内容，日志文件及位置点后，会将binlog内容依次写到slave自身的relay log(中继日志)文件尾部，并将新的binlog文件名和位置记录到master-info文件中，以便下次从master读取新binlog日志时，能告诉master从新binlog的新位置读取。</p>
<p>
5、slave的sql线程会实时检测本地relay log中I/O线程新增加的日志内容，及时把relay log文件中的内容解析成sql语句，并按解析sql语句的位置顺序执行这些sql语句。relay-log.info中记录当前应用中继日志的文件名及位置点。</p>
<p>
<span><strong>四、mysql主从复制操作</strong></span></p>
<p>
我这里是mysql单机多实例，3306、3308、3309</p>
<p>
主库为3306，从库为3308,3309</p>
<p>
(1)、在master主库上</p>
<p>
1、设置server-id值并开启binlog功能</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_232413">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain">> </code><code class="bash functions">vi</code> <code class="bash plain">/etc/my</code><code class="bash plain">.cnf</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_192254">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain"></code>
</div>
<div class="line number2 index1 alt1">
<code class="bash spaces"> </code><code class="bash comments">#用于同步的每台机器server-id都不能相同</code>
</div>
<div class="line number3 index2 alt2">
</div>
<div class="line number4 index3 alt1">
<code class="bash plain">server-</code><code class="bash functions">id</code> <code class="bash plain">= 10</code>
</div>
<div class="line number5 index4 alt2">
</div>
<div class="line number6 index5 alt1">
<code class="bash plain">log-bin = </code><code class="bash plain">/data/mysql56/data/mysql-bin</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
2、重启主库</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_223407">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> service mysqld restart</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
3、登陆主库，查看server-id</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_458190">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> mysql -uroot -p</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">> show variables </code><code class="sql color1">like</code> <code class="sql string">'server_id'</code><code class="sql plain">; </code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
4、主库上建立用于从库复制的账号</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_422925">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> </code><code class="sql keyword">grant</code> <code class="sql plain">replication slave </code><code class="sql keyword">on</code> <code class="sql plain">*.* </code><code class="sql keyword">to</code> <code class="sql string">"rep"</code><code class="sql plain">@</code><code class="sql string">"%"</code> <code class="sql plain">identified </code><code class="sql keyword">by</code> <code class="sql string">"123456"</code><code class="sql plain">;</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">> flush </code><code class="sql keyword">privileges</code><code class="sql plain">;</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">> </code><code class="sql keyword">select</code> <code class="sql color2">user</code><code class="sql plain">,host </code><code class="sql keyword">from</code> <code class="sql plain">mysql.</code><code class="sql color2">user</code><code class="sql plain">;</code>
</div>
<div class="line number6 index5 alt1">
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">> show grants </code><code class="sql keyword">for</code> <code class="sql plain">rep@</code><code class="sql string">"%"</code><code class="sql plain">; </code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
5、对主库数据库锁表只读(当前窗口不要关掉)</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_578099">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> flush </code><code class="sql keyword">table</code> <code class="sql keyword">with</code> <code class="sql keyword">read</code> <code class="sql plain">lock;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
查看主库状态</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_350691">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> show master status;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
6、备份主库所有数据文件</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_691158">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> mysqldump -uroot -p -A -B | gzip > /data/mysql_bak.$(</code><code class="sql keyword">date</code> <code class="sql plain">+%F).sql.gz</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
7、备份完主库数据后，解锁</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_898755">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> unlock tables;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
8、把主库导出的数据迁移到从库</p>
<p>
(2)、在slave从库上</p>
<p>
1、设置server-id值并关闭binlog功能  </p>
<p>
①有两种情况下需打开binlog   </p>
<p>
②级联同步A->B->C中间的B，就要开启binlog   </p>
<p>
③在从库做数据库备份，必须要有全备和binlog日志才是完整备份。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_183077">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> vi /mysql-instance/3308/my.cnf </code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain"></code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">server-id = 11</code>
</div>
<div class="line number6 index5 alt1">
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">relay-log = /mysql-instance/3308/relay-bin</code>
</div>
<div class="line number8 index7 alt1">
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">relay-log-info-file = /mysql-instance/3308/relay-log.info </code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
2、重启从库</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_862614">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> /mysql-instance/3308/mysql restart</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
3、登陆从库检查参数</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_380741">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> mysql -uroot -p -S /mysql-instance/3308/mysql.sock</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">> show variables </code><code class="sql color1">like</code> <code class="sql string">'log_bin'</code><code class="sql plain">;</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">> show variables </code><code class="sql color1">like</code> <code class="sql string">'server_id'</code><code class="sql plain">; </code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
4、把主库mysqldump导出的数据恢复到从库</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_283025">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> gzip -d /data/mysql_bak.2017-01-15.sql.gz</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
把主库数据还原到从库</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_745876">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> mysql -uroot -p -S /mysql-instance/3308/mysql.sock < /data/mysql_bak.2017-01-15.sql</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
5、登录从库，配置复制参数</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_915419">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">CHANGE MASTER </code><code class="sql keyword">TO</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">MASTER_HOST=</code><code class="sql string">'127.0.0.1'</code><code class="sql plain">,</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">MASTER_PORT=3306,</code>
</div>
<div class="line number6 index5 alt1">
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">MASTER_USER=</code><code class="sql string">'rep'</code><code class="sql plain">,</code>
</div>
<div class="line number8 index7 alt1">
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">MASTER_PASSWORD=</code><code class="sql string">'123456'</code><code class="sql plain">,</code>
</div>
<div class="line number10 index9 alt1">
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">MASTER_LOG_FILE=</code><code class="sql string">'mysql-bin.000001'</code><code class="sql plain">,</code>
</div>
<div class="line number12 index11 alt1">
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">MASTER_LOG_POS=396; </code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
注意上面的MASTER_LOG_FILE和MASTER_LOG_POS是在主库中用show master status;查看的信息。</p>
<p>
查看master.info文件</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_412328">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> cat /mysql-instance/3308/data/master.info</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
6、启动从库同步开关，测试主从复制情况</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_759791">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e </code><code class="sql string">"start slave;"</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e </code><code class="sql string">"show slave status\G;"</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e </code><code class="sql string">"show slave status\G"</code> <code class="sql plain">| egrep </code><code class="sql string">"IO_Running|SQL_Running|_Behind_Master"</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
7、测试主从复制</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_228682">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> mysql -uroot -p -e </code><code class="sql string">"create database wohehe;"</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e </code><code class="sql string">"show databases;"</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<span><strong>五、mysql主从复制线程状态说明及用途</strong></span></p>
<p>
1、主库线程的同步状态</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_676105">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> show processlist\G; </code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">*************************** 1. row ***************************</code>
</div>
<div class="line number5 index4 alt2">
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces">   </code><code class="sql plain">Id: 5</code>
</div>
<div class="line number7 index6 alt2">
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces">  </code><code class="sql color2">User</code><code class="sql plain">: rep</code>
</div>
<div class="line number9 index8 alt2">
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces">  </code><code class="sql plain">Host: localhost:47605</code>
</div>
<div class="line number11 index10 alt2">
</div>
<div class="line number12 index11 alt1">
<code class="sql spaces">   </code><code class="sql plain">db: </code><code class="sql color1">NULL</code>
</div>
<div class="line number13 index12 alt2">
</div>
<div class="line number14 index13 alt1">
<code class="sql plain">Command: Binlog Dump</code>
</div>
<div class="line number15 index14 alt2">
</div>
<div class="line number16 index15 alt1">
<code class="sql spaces">  </code><code class="sql keyword">Time</code><code class="sql plain">: 4728</code>
</div>
<div class="line number17 index16 alt2">
</div>
<div class="line number18 index17 alt1">
<code class="sql spaces"> </code><code class="sql plain">State: Master has sent </code><code class="sql color1">all</code> <code class="sql plain">binlog </code><code class="sql keyword">to</code> <code class="sql plain">slave; waiting </code><code class="sql keyword">for</code> <code class="sql plain">binlog </code><code class="sql keyword">to</code> <code class="sql plain">be updated</code>
</div>
<div class="line number19 index18 alt2">
</div>
<div class="line number20 index19 alt1">
<code class="sql spaces">  </code><code class="sql plain">Info: </code><code class="sql color1">NULL</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
说明主库线程已从binlog读取更新，发送到了从库，线程处理空闲状态，等待binlog的事件更新。</p>
<p>
2、从库线程的同频状态</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_230729">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> show processlist\G; </code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">*************************** 2. row ***************************</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces">   </code><code class="sql plain">Id: 6</code>
</div>
<div class="line number6 index5 alt1">
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces">  </code><code class="sql color2">User</code><code class="sql plain">: system </code><code class="sql color2">user</code>
</div>
<div class="line number8 index7 alt1">
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces">  </code><code class="sql plain">Host:</code>
</div>
<div class="line number10 index9 alt1">
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces">   </code><code class="sql plain">db: </code><code class="sql color1">NULL</code>
</div>
<div class="line number12 index11 alt1">
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">Command: </code><code class="sql keyword">Connect</code>
</div>
<div class="line number14 index13 alt1">
</div>
<div class="line number15 index14 alt2">
<code class="sql spaces">  </code><code class="sql keyword">Time</code><code class="sql plain">: 5305</code>
</div>
<div class="line number16 index15 alt1">
</div>
<div class="line number17 index16 alt2">
<code class="sql spaces"> </code><code class="sql plain">State: Slave has </code><code class="sql keyword">read</code> <code class="sql color1">all</code> <code class="sql plain">relay log; waiting </code><code class="sql keyword">for</code> <code class="sql plain">the slave I/O thread </code><code class="sql keyword">to</code> <code class="sql keyword">update</code> <code class="sql plain">it</code>
</div>
<div class="line number18 index17 alt1">
</div>
<div class="line number19 index18 alt2">
<code class="sql spaces">  </code><code class="sql plain">Info: </code><code class="sql color1">NULL</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
说明从库已读取所有中继日志，等待从库I/O线程的更新。</p>
<p>
<span><strong>六、主从复制故障</strong></span></p>
<p>
如果我在从库上创建了一个库，然后去主库创建同名的库，那么这就会冲突了。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_906165">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> show slave status; </code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">Slave_IO_Running: Yes</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">Slave_SQL_Running: </code><code class="sql keyword">No</code>
</div>
<div class="line number6 index5 alt1">
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">Seconds_Behind_Master: </code><code class="sql color1">NULL</code>
</div>
<div class="line number8 index7 alt1">
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">Last_Error: Error </code><code class="sql string">'Can'</code><code class="sql plain">t </code><code class="sql keyword">create</code> <code class="sql keyword">database</code> <code class="sql string">'xxxxx'</code><code class="sql plain">; </code><code class="sql keyword">database</code> <code class="sql plain">exists</code><code class="sql string">' on query. Default database: '</code><code class="sql plain">xxxxx</code><code class="sql string">'. Query: '</code><code class="sql keyword">create</code> <code class="sql keyword">database</code> <code class="sql plain">xxxxx' </code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>对于该冲突解决方法</strong></p>
<p>
<strong>方法一</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_451823">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> stop slave;</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">#将同步指针移动下一个，如果多次不同步，可重复操作</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">> </code><code class="sql keyword">set</code> <code class="sql keyword">global</code> <code class="sql plain">sql_slave_skip_counter = 1;</code>
</div>
<div class="line number6 index5 alt1">
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">> start slave; </code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>方法二</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_897244">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">> vi /mysql-instance/3308/my.cnf </code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">#把可以忽略的错误号事先在配置文件中配置</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">slave-skip-errors = 1002,1007,1032 </code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。</p>
<p>
原文链接：http://www.cnblogs.com/jkko123/p/6360289.html</p>

頁: [1]

琼殿技术论坛's Archiver

centos7下mysql5.6的主从复制详解