MySQL双主热备问题处理
1. Slave_IO_Running: No <code class="hljs lasso has-numbering">mysql<span class="hljs-subst">></span> show slave status<span class="hljs-subst">\</span>G <span class="hljs-subst">***************************</span> <span class="hljs-number">1.</span> row <span class="hljs-subst">***************************</span> Slave_IO_State: Master_Host: <span class="hljs-number">192.16</span><span class="hljs-number">.10</span><span class="hljs-number">.21</span> Master_User: cahms Master_Port: <span class="hljs-number">3306</span> Connect_Retry: <span class="hljs-number">60</span> Master_Log_File: mysql<span class="hljs-attribute">-bin</span><span class="hljs-number">.000003</span> Read_Master_Log_Pos: <span class="hljs-number">20398</span> Relay_Log_File: mysqld<span class="hljs-attribute">-relay</span><span class="hljs-attribute">-bin</span><span class="hljs-number">.000001</span> Relay_Log_Pos: <span class="hljs-number">4</span> Relay_Master_Log_File: mysql<span class="hljs-attribute">-bin</span><span class="hljs-number">.000003</span> Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: <span class="hljs-number">0</span> Last_Error: Skip_Counter: <span class="hljs-number">0</span> Exec_Master_Log_Pos: <span class="hljs-number">20398</span> Relay_Log_Space: <span class="hljs-number">106</span> Until_Condition: <span class="hljs-literal">None</span> Until_Log_File: Until_Log_Pos: <span class="hljs-number">0</span> Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: <span class="hljs-built_in">NULL</span> Master_SSL_Verify_Server_Cert: No Last_IO_Errno: <span class="hljs-number">1593</span> Last_IO_Error: Fatal error: The slave I/O <span class="hljs-keyword">thread</span> stops because master <span class="hljs-literal">and</span> slave have equal MySQL server ids; these ids must be different for replication <span class="hljs-keyword">to</span> work (<span class="hljs-literal">or</span> the <span class="hljs-subst">--</span>replicate<span class="hljs-attribute">-same</span><span class="hljs-attribute">-server</span><span class="hljs-attribute">-id</span> option must be used <span class="hljs-keyword">on</span> slave but this does <span class="hljs-literal">not</span> always make sense; please check the manual before using it)<span class="hljs-built_in">.</span> Last_SQL_Errno: <span class="hljs-number">0</span> Last_SQL_Error: <span class="hljs-number">1</span> row <span class="hljs-keyword">in</span> <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> sec)</code><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li><li>40</li><li>41</li><li>42</li><li>43</li><li>44</li><li>45</li></ul><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li><li>40</li><li>41</li><li>42</li><li>43</li><li>44</li><li>45</li></ul>
(1) master and slave have equal server ids
【解决办法】
修改/etc/my.cnf 下的server-id的值，确保master和slave的server-id不一样即可；
(2) Error reading packet from server:
Access denied; you need the REPLICATION SLAVE privilege for this operation ( server_errno=1227)
【解决办法】主服务器给的复制权限不够，重新赋予权限。 <code class="hljs oxygene has-numbering">> grant replication slave <span class="hljs-keyword">on</span> *.* <span class="hljs-keyword">to</span> <span class="hljs-string">'uname'</span>@<span class="hljs-string">'对端ip'</span> identified <span class="hljs-keyword">by</span> <span class="hljs-string">'password'</span>； > flush privileges；</code><ul style="" class="pre-numbering"><li>1</li><li>2</li></ul><ul style="" class="pre-numbering"><li>1</li><li>2</li></ul> <code class="hljs asciidoc has-numbering"><span class="hljs-header">== e.g ==</span> </code><ul style="" class="pre-numbering"><li>1</li><li>2</li></ul><ul style="" class="pre-numbering"><li>1</li><li>2</li></ul> <code class="hljs oxygene has-numbering">> grant replication slave <span class="hljs-keyword">on</span> *.* <span class="hljs-keyword">to</span> <span class="hljs-string">'root'</span>@<span class="hljs-string">'192.16.10.21'</span> identified <span class="hljs-keyword">by</span> <span class="hljs-string">'root'</span>; > grant replication slave <span class="hljs-keyword">on</span> *.* <span class="hljs-keyword">to</span> <span class="hljs-string">'root'</span>@<span class="hljs-string">'192.16.10.23'</span> identified <span class="hljs-keyword">by</span> <span class="hljs-string">'root'</span>;</code><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li></ul><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li></ul>
2. Slave_IO_State: <code class="hljs livecodeserver has-numbering">Waiting <span class="hljs-built_in">to</span> reconnect <span class="hljs-keyword">after</span> <span class="hljs-operator">a</span> failed master event <span class="hljs-built_in">read</span> mysql> show slave status\G *************************** <span class="hljs-number">1.</span> row *************************** Slave_IO_State: Waiting <span class="hljs-built_in">to</span> reconnect <span class="hljs-keyword">after</span> <span class="hljs-operator">a</span> failed master event <span class="hljs-built_in">read</span> Master_Host: <span class="hljs-number">192.16</span><span class="hljs-number">.10</span><span class="hljs-number">.231</span> Master_User: cahms Master_Port: <span class="hljs-number">3306</span> Connect_Retry: <span class="hljs-number">60</span> Master_Log_File: mysql-bin<span class="hljs-number">.000003</span> Read_Master_Log_Pos: <span class="hljs-number">106</span> Relay_Log_File: mysqld-relay-bin<span class="hljs-number">.000001</span> Relay_Log_Pos: <span class="hljs-number">4</span> Relay_Master_Log_File: mysql-bin<span class="hljs-number">.000003</span> Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: <span class="hljs-number">0</span> Last_Error: Skip_Counter: <span class="hljs-number">0</span> Exec_Master_Log_Pos: <span class="hljs-number">106</span> Relay_Log_Space: <span class="hljs-number">106</span> Until_Condition: None Until_Log_File: Until_Log_Pos: <span class="hljs-number">0</span> Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: <span class="hljs-constant">NULL</span> Master_SSL_Verify_Server_Cert: No Last_IO_Errno: <span class="hljs-number">0</span> Last_IO_Error: Last_SQL_Errno: <span class="hljs-number">0</span> Last_SQL_Error: <span class="hljs-number">1</span> row <span class="hljs-operator">in</span> <span class="hljs-built_in">set</span> (<span class="hljs-number">0.00</span> <span class="hljs-built_in">sec</span>)</code><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li><li>40</li><li>41</li><li>42</li></ul><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li><li>40</li><li>41</li><li>42</li></ul>
【解决办法】 <code class="hljs oxygene has-numbering"> > grant replication slave <span class="hljs-keyword">on</span> *.* <span class="hljs-keyword">to</span> <span class="hljs-string">'root'</span>@<span class="hljs-string">'192.16.10.228'</span> identified <span class="hljs-keyword">by</span> <span class="hljs-string">'root'</span>; > grant replication slave <span class="hljs-keyword">on</span> *.* <span class="hljs-keyword">to</span> <span class="hljs-string">'root'</span>@<span class="hljs-string">'192.16.10.231'</span> identified <span class="hljs-keyword">by</span> <span class="hljs-string">'root'</span>; > flush privileges;</code><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li></ul><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li></ul>
3.Another MySQL daemon already running with the same unix socket.
原因多个Mysql进程使用了同一个socket。 两个方法解决：
第一个是立即关机 使用命令 shutdown -h now 关机，关机后在启动，进程就停止了。
第二个直接把mysql.sock文件改名即可。也可以删除，推荐改名。
然后就可以启动mysql了。
下面是国外原文
To prevent the problem from occurring, you must perform a graceful
shutdown of the server from the command line rather than powering off
the server. <code class="hljs vala has-numbering"><span class="hljs-preprocessor"># shutdown -h now</span></code><ul style="" class="pre-numbering"><li>1</li></ul><ul style="" class="pre-numbering"><li>1</li></ul>
This will stop the running services before powering down the machine.
Based on Centos, an additional method for getting it back up again when you run into this problem is to move mysql.sock: <code class="hljs vala has-numbering"><span class="hljs-preprocessor"># mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak</span> <span class="hljs-preprocessor"># service mysqld start</span></code><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li></ul><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li></ul>
Restarting the service creates a new entry called mqsql.sock
