CentOS服务器环境下MySQL主从同步配置方法
<p>本文实例讲述了CentOS服务器环境下MySQL主从同步配置方法。分享给大家供大家参考，具体如下：</p><p><strong>一、环境</strong></p><p><strong>主机：</strong></p><p>
master操作系统：centos 5.3<br/>
IP：192.168.1.222<br/>
MySQL版本：5.0.77</p><p><strong>从机：</strong></p><p>
slave操作系统：centos 5.3<br/>
IP：192.168.1.220<br/>
MySQL版本：5.0.77</p><p><strong>二、创建数据库</strong></p><p>
分别登录master机和slave机的</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_977759"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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：mysql –u root –p</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
创建数据库：</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_194021"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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 keyword">create</code> <code class="sql keyword">database</code> <code class="sql plain">repl;</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p><strong>三、master机和slave机的相关配置</strong></p><p>
1、修改master机器中mysql配置文件my.cnf，该文件在/etc目录下</p><p>
在配置段添加如下字段</p><div class="jb51code"><div><div class="syntaxhighlighterbash" id="highlighter_482881"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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></td><td class="code"><div class="container"><div class="line number1 index0 alt2"><code class="bash plain">server-</code><code class="bash functions">id</code><code class="bash plain">=1</code></div><div class="line number2 index1 alt1"><code class="bash plain">log-bin=log</code></div><div class="line number3 index2 alt2"><code class="bash plain">binlog-</code><code class="bash keyword">do</code><code class="bash plain">-</code><code class="bash functions">db</code><code class="bash plain">=repl </code><code class="bash plain">//</code><code class="bash plain">需要同步的数据库，如果没有本行，即表示同步所有的数据库</code></div><div class="line number4 index3 alt1"><code class="bash plain">binlog-ignore-</code><code class="bash functions">db</code><code class="bash plain">=mysql </code><code class="bash plain">//</code><code class="bash plain">被忽略的数据库</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
在master机上为slave机添加一同步帐号</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_428414"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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 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">'repl'</code><code class="sql plain">@</code><code class="sql string">'192.1681.220'</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></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
重启master机的mysql服务：</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_97946"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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"></div></div></div><p>
用<code>show master status</code>命令看日志情况</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_554564"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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">mysql>show master status;</code></div><div class="line number2 index1 alt1"><code class="sql plain">+</code><code class="sql comments">-----------------+------------+-------------------+-----------------------+</code></div><div class="line number3 index2 alt2"><code class="sql plain">| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |</code></div><div class="line number4 index3 alt1"><code class="sql plain">+</code><code class="sql comments">-----------------+------------+-------------------+-----------------------+</code></div><div class="line number5 index4 alt2"><code class="sql plain">| log.000003 | 98 | repl | mysql |</code></div><div class="line number6 index5 alt1"><code class="sql plain">+</code><code class="sql comments">-----------------+------------+-------------------+-----------------------+</code></div><div class="line number7 index6 alt2"><code class="sql plain">1 row </code><code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
2、修改slave机中mysql配置文件</p><p>
同样在字段下添加如下内容</p><div class="jb51code"><div><div class="syntaxhighlighterbash" id="highlighter_462545"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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="bash plain">server-</code><code class="bash functions">id</code><code class="bash plain">=2</code></div><div class="line number2 index1 alt1"><code class="bash plain">master-host=192.168.1.222</code></div><div class="line number3 index2 alt2"><code class="bash plain">master-user=repl</code></div><div class="line number4 index3 alt1"><code class="bash plain">master-password=123456</code></div><div class="line number5 index4 alt2"><code class="bash plain">master-port=3306</code></div><div class="line number6 index5 alt1"><code class="bash plain">master-connect-retry=60</code></div><div class="line number7 index6 alt2"><code class="bash plain">replicate-</code><code class="bash keyword">do</code><code class="bash plain">-</code><code class="bash functions">db</code><code class="bash plain">=repl </code><code class="bash plain">//</code><code class="bash plain">同步的数据库，不写本行表示同步所有数据库</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
然后重启slave机的mysql</p><p>
在slave机中进入mysql</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_209765"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><td class="gutter"><div class="line number1 index0 alt2">
1</div><div class="line number2 index1 alt1">
2</div></td><td class="code"><div class="container"><div class="line number1 index0 alt2"><code class="sql plain">mysql>start slave;</code></div><div class="line number2 index1 alt1"><code class="sql plain">mysql>show slave status\G;</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
如果Slave_IO_Running、Slave_SQL_Running状态为Yes则表明设置成功。</p><p><strong>四、出现的问题</strong></p><p>
当我在执行start slave这条命令时，系统提示</p><p>ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO，</p><p>
执行<code>show slave status;</code>又提示Empty set (0.00 sec)，想不通问题在哪里</p><p>
后来发现，原来slave已经默认开启，要先关闭再开启</p><p>
执行</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_816531"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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">slave stop;</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
再执行</p><div class="codetitle"><span style="text-decoration:underline;">复制代码</span> 代码如下:</div><div class="codebody" id="code56937">
change master to master_host='192.168.1.222',master_user='repl',master_password='123456', master_log_file='log.000003' ,master_log_pos=98;</div><p>
</p><p>
然后执行</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_409585"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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">slave start;</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
这时再执行</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_748338"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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 slave status\G</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
显示如下：</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_987902"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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><div class="line number21 index20 alt2">
21</div><div class="line number22 index21 alt1">
22</div><div class="line number23 index22 alt2">
23</div><div class="line number24 index23 alt1">
24</div><div class="line number25 index24 alt2">
25</div><div class="line number26 index25 alt1">
26</div><div class="line number27 index26 alt2">
27</div><div class="line number28 index27 alt1">
28</div><div class="line number29 index28 alt2">
29</div><div class="line number30 index29 alt1">
30</div><div class="line number31 index30 alt2">
31</div><div class="line number32 index31 alt1">
32</div><div class="line number33 index32 alt2">
33</div><div class="line number34 index33 alt1">
34</div><div class="line number35 index34 alt2">
35</div></td><td class="code"><div class="container"><div class="line number1 index0 alt2"><code class="sql plain">*************************** 1. row ***************************</code></div><div class="line number2 index1 alt1"><code class="sql spaces"> </code><code class="sql plain">Slave_IO_State: Waiting </code><code class="sql keyword">for</code> <code class="sql plain">master </code><code class="sql keyword">to</code> <code class="sql plain">send event</code></div><div class="line number3 index2 alt2"><code class="sql spaces"> </code><code class="sql plain">Master_Host: 192.168.1.222</code></div><div class="line number4 index3 alt1"><code class="sql spaces"> </code><code class="sql plain">Master_User: repl</code></div><div class="line number5 index4 alt2"><code class="sql spaces"> </code><code class="sql plain">Master_Port: 3306</code></div><div class="line number6 index5 alt1"><code class="sql spaces"> </code><code class="sql plain">Connect_Retry: 60</code></div><div class="line number7 index6 alt2"><code class="sql spaces"> </code><code class="sql plain">Master_Log_File: log.000003</code></div><div class="line number8 index7 alt1"><code class="sql spaces"> </code><code class="sql plain">Read_Master_Log_Pos: 98</code></div><div class="line number9 index8 alt2"><code class="sql spaces"> </code><code class="sql plain">Relay_Log_File: mysqld-relay-bin.000002</code></div><div class="line number10 index9 alt1"><code class="sql spaces"> </code><code class="sql plain">Relay_Log_Pos: 229</code></div><div class="line number11 index10 alt2"><code class="sql spaces"> </code><code class="sql plain">Relay_Master_Log_File: log.000003</code></div><div class="line number12 index11 alt1"><code class="sql plain">Slave_IO_Running: Yes</code></div><div class="line number13 index12 alt2"><code class="sql spaces"> </code><code class="sql plain">Slave_SQL_Running: Yes</code></div><div class="line number14 index13 alt1"><code class="sql spaces"> </code><code class="sql plain">Replicate_Do_DB:</code></div><div class="line number15 index14 alt2"><code class="sql spaces"> </code><code class="sql plain">Replicate_Ignore_DB:</code></div><div class="line number16 index15 alt1"><code class="sql spaces"> </code><code class="sql plain">Replicate_Do_Table:</code></div><div class="line number17 index16 alt2"><code class="sql spaces"> </code><code class="sql plain">Replicate_Ignore_Table:</code></div><div class="line number18 index17 alt1"><code class="sql spaces"> </code><code class="sql plain">Replicate_Wild_Do_Table:</code></div><div class="line number19 index18 alt2"><code class="sql plain">Replicate_Wild_Ignore_Table:</code></div><div class="line number20 index19 alt1"><code class="sql spaces"> </code><code class="sql plain">Last_Errno: 0</code></div><div class="line number21 index20 alt2"><code class="sql spaces"> </code><code class="sql plain">Last_Error:</code></div><div class="line number22 index21 alt1"><code class="sql spaces"> </code><code class="sql plain">Skip_Counter: 0</code></div><div class="line number23 index22 alt2"><code class="sql spaces"> </code><code class="sql plain">Exec_Master_Log_Pos: 98</code></div><div class="line number24 index23 alt1"><code class="sql spaces"> </code><code class="sql plain">Relay_Log_Space: 229</code></div><div class="line number25 index24 alt2"><code class="sql spaces"> </code><code class="sql plain">Until_Condition: None</code></div><div class="line number26 index25 alt1"><code class="sql spaces"> </code><code class="sql plain">Until_Log_File:</code></div><div class="line number27 index26 alt2"><code class="sql spaces"> </code><code class="sql plain">Until_Log_Pos: 0</code></div><div class="line number28 index27 alt1"><code class="sql spaces"> </code><code class="sql plain">Master_SSL_Allowed: </code><code class="sql keyword">No</code></div><div class="line number29 index28 alt2"><code class="sql spaces"> </code><code class="sql plain">Master_SSL_CA_File:</code></div><div class="line number30 index29 alt1"><code class="sql spaces"> </code><code class="sql plain">Master_SSL_CA_Path:</code></div><div class="line number31 index30 alt2"><code class="sql spaces"> </code><code class="sql plain">Master_SSL_Cert:</code></div><div class="line number32 index31 alt1"><code class="sql spaces"> </code><code class="sql plain">Master_SSL_Cipher:</code></div><div class="line number33 index32 alt2"><code class="sql spaces"> </code><code class="sql plain">Master_SSL_Key:</code></div><div class="line number34 index33 alt1"><code class="sql spaces"> </code><code class="sql plain">Seconds_Behind_Master: 0</code></div><div class="line number35 index34 alt2"><code class="sql plain">1 row </code><code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
至此，说明配置成功。</p><p><strong>五、测试主从服务器是否能同步</strong></p><p>
在主服务器上面新建一个表，必须在repl数据下</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_325156"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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></td><td class="code"><div class="container"><div class="line number1 index0 alt2"><code class="sql plain">mysql> use repl</code></div><div class="line number2 index1 alt1"><code class="sql keyword">Database</code> <code class="sql plain">changed</code></div><div class="line number3 index2 alt2"><code class="sql plain">mysql> </code><code class="sql keyword">create</code> <code class="sql keyword">table</code> <code class="sql plain">test(id </code><code class="sql keyword">int</code><code class="sql plain">,</code><code class="sql keyword">name</code> <code class="sql keyword">char</code><code class="sql plain">(10));</code></div><div class="line number4 index3 alt1"><code class="sql plain">Query OK, 0 </code><code class="sql keyword">rows</code> <code class="sql plain">affected (0.00 sec)</code></div><div class="line number5 index4 alt2"><code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">test </code><code class="sql keyword">values</code><code class="sql plain">(1,</code><code class="sql string">'zaq'</code><code class="sql plain">);</code></div><div class="line number6 index5 alt1"><code class="sql plain">Query OK, 1 row affected (0.00 sec)</code></div><div class="line number7 index6 alt2"><code class="sql plain">mysql> </code><code class="sql keyword">insert</code> <code class="sql keyword">into</code> <code class="sql plain">test </code><code class="sql keyword">values</code><code class="sql plain">(1,</code><code class="sql string">'xsw'</code><code class="sql plain">);</code></div><div class="line number8 index7 alt1"><code class="sql plain">Query OK, 1 row affected (0.00 sec)</code></div><div class="line number9 index8 alt2"><code class="sql plain">mysql> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">test;</code></div><div class="line number10 index9 alt1"><code class="sql plain">+</code><code class="sql comments">------+------+</code></div><div class="line number11 index10 alt2"><code class="sql plain">| id | </code><code class="sql keyword">name</code> <code class="sql plain">|</code></div><div class="line number12 index11 alt1"><code class="sql plain">+</code><code class="sql comments">-------+------+</code></div><div class="line number13 index12 alt2"><code class="sql plain">| 1 | zaq |</code></div><div class="line number14 index13 alt1"><code class="sql plain">| 1 | xsw |</code></div><div class="line number15 index14 alt2"><code class="sql plain">+</code><code class="sql comments">-------+------+</code></div><div class="line number16 index15 alt1"><code class="sql plain">2 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
在从服务器查看是否同步过来</p><div class="jb51code"><div><div class="syntaxhighlightersql" id="highlighter_793967"><div class="toolbar">?</div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr class="firstRow"><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></td><td class="code"><div class="container"><div class="line number1 index0 alt2"><code class="sql plain">mysql> use repl;</code></div><div class="line number2 index1 alt1"><code class="sql keyword">Database</code> <code class="sql plain">changed</code></div><div class="line number3 index2 alt2"><code class="sql plain">mysql> </code><code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">test;</code></div><div class="line number4 index3 alt1"><code class="sql plain">+</code><code class="sql comments">------+------+</code></div><div class="line number5 index4 alt2"><code class="sql plain">| id | </code><code class="sql keyword">name</code> <code class="sql plain">|</code></div><div class="line number6 index5 alt1"><code class="sql plain">+</code><code class="sql comments">------+------+</code></div><div class="line number7 index6 alt2"><code class="sql plain">| 1 | zaq |</code></div><div class="line number8 index7 alt1"><code class="sql plain">| 1 | xsw |</code></div><div class="line number9 index8 alt2"><code class="sql plain">+</code><code class="sql comments">------+------+</code></div><div class="line number10 index9 alt1"><code class="sql plain">2 </code><code class="sql keyword">rows</code> <code class="sql color1">in</code> <code class="sql keyword">set</code> <code class="sql plain">(0.00 sec)</code></div></div></td></tr></tbody></table></div></div><div class="codetool" id="codetool"><div class="code_n"></div></div></div><p>
说明已经配置成功。</p><p>
希望本文所述对大家CentOS服务器维护有所帮助。</p><p>
原文链接：http://www.cnblogs.com/wanghuaijun/p/6845030.html</p>
頁:
[1]