09更新)

Category: 電腦相關
Last Updated: Sunday, 10 August 2014 17:22
Published: Wednesday, 23 July 2014 22:27
Written by sam
Print

安裝、設定都不難

難在除錯…

剛好閒著來看一下Replication運行的如何

想不到一看就看到GG了…

[root@ip-172-31-12-172 html]# mysql -e 'show slave status\G'

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 
                  Master_User: 
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mylogbin.000001
          Read_Master_Log_Pos: 2193370
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 99060
        Relay_Master_Log_File: mylogbin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: joomla
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062

 Last_Error: Error 'Duplicate entry '217' for key 'PRIMARY'' on query. Default database: 'joomla'. Query: 'INSERT INTO `jos_content` (`id`,`title`,`alias`,`introtext`,`fulltext`,`state`,`sectionid`,`catid`,`created`,`created_by`,`created_by_alias`,`checked_out`,`checked_out_time`,`publish_up`,`publish_down`,`version`,`ordering`,`metakey`,`metadesc`,`access` ) VALUES ( '0','AUTO MySQL Master Slave Replication UPDATE','auto-mysql-master-slave-replication-update','\r\n
         Skip_Counter: 0
          Exec_Master_Log_Pos: 98915
              Relay_Log_Space: 2193672
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '217' for key 'PRIMARY'' on query. Default database: 'joomla'. Query: 'INSERT INTO `jos_content` ( `id`,`title`,`alias`,`introtext`,`fulltext`,`state`,`sectionid`,`catid`,`created`,`created_by`,`created_by_alias`,`checked_out`,`checked_out_time`,`publish_up`,`publish_down`,`version`,`ordering`,`metakey`,`metadesc`,`access` ) VALUES ( '0','AUTO MySQL Master Slave Replication UPDATE','auto-mysql-master-slave-replication-upda te','\r\n
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

未免來的太快了吧

Error Number 1062

照提示是主鍵無法更新進去

通常網路的解法就是兩種

1.SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

把錯誤加1，也就是跳過去…

2. slave_skip_errors = 1062

這個就更棒了…到my.cnf設定檔把這個錯誤1062代碼無視

以上都能解決問題(不看到就沒事…

先分別查看主、從的主機內容是否真的一致

[root@ip-172-31-12-172 html]# mysql -e 'select count(*) from joomla.jos_content where id=217'

+----------+

| count(*) |

+----------+

| 1 |

+----------+

上面的表名及條件請自行依錯誤提示修改

一定要主、從架構的主機都是真的相同

運氣好相同的話，就請執行

stop slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

start slave;

但通常都是不同的…所以報錯

而以上的兩種方法，則都是略過錯誤

正常來說的話…是應該要將該筆報錯的刪掉(但通常會有很多筆…

所以需要寫個SHELL了

#!/bin/bash
#Delete duplicate records primary key conflict ERROR NUM 1062
#By Sam 14/07/24

DB=`mysql -e 'show slave status\G' |grep Last_Error |awk '{print $13}' |awk -F "'" '{print $2}'`
Slave_SQL_Running=`mysql -e 'show slave status\G' |egrep 'Slave_SQL_Running' |awk '{print $2}'`
Last_SQL_Errno=`mysql -e 'show slave status\G' |egrep Last_Errno |awk '{print $2}'`
Duplicate=`mysql -e 'show slave status\G' |grep Last_Error |awk '/Duplicate entry/{print $5}' |awk -F "'" '{print $2}'`
TABLE=`mysql -e 'show slave status\G' |grep Last_Error |awk -F ":" '{print $4}' |awk '{print $3}' |sed 's/\''\/""/g'`
 
if [ "$Slave_SQL_Running" == No ] && [ "$Last_SQL_Errno" == 1062 ]; then
        PRIKEY=`mysql -e "desc $DB.$TABLE" |grep PRI |awk '{print $1}'`
        mysql -e "delete from $DB.$TABLE where $PRIKEY=$Duplicate"
        mysql -e 'start slave sql_thread'
else
echo "Normal and nothing to do"
fi

如果要一次解決…可以改用while true do (joomla裝新程式碼套件…試一下 Hs Highlighter

#!/bin/bash
#Delete duplicate records primary key conflict ERROR NUM 1062
#By Sam 14/08/05
echo `date +"%c"`

        Slave_SQL_Running=`mysql -e 'show slave status\G' |egrep 'Slave_SQL_Running' |awk '{print $2}'`
        Last_SQL_Errno=`mysql -e 'show slave status\G' |egrep Last_Errno |awk '{print $2}'`
        while [ "$Slave_SQL_Running" == No ] && [ "$Last_SQL_Errno" == 1062 ]; do
                        echo `mysql -e 'show slave status\G'`
      DB=`mysql -e 'show slave status\G' |grep Last_Error |awk '{print $13}' |awk -F "'" '{print $2}'`
      TABLE=`mysql -e 'show slave status\G' |grep Last_Error |sed 's/\`//g' |awk -F ":" '{print $4}' |awk '{print $3}'`
                        PRIKEY=`mysql -e "desc $DB.$TABLE" |grep PRI |awk '{print $1}'`
                        Duplicate=`mysql -e 'show slave status\G' |grep Last_Error |awk '/Duplicate entry/{print $5}' |awk -F "'" '{print $2}'`
                                mysql -e "delete from $DB.$TABLE where $PRIKEY=$Duplicate"
                                mysql -e "start slave sql_thread"
           Slave_SQL_Running=`mysql -e 'show slave status\G' |egrep 'Slave_SQL_Running' |awk '{print $2}'`
           Last_SQL_Errno=`mysql -e 'show slave status\G' |egrep Last_Errno |awk '{print $2}'`
  done

但我是用來作一次性的…或是懶的話…也可以弄進cron…又省事了些!!!

一切又正常了

[root@ip-172-31-12-172 html]# mysql -e 'show slave status\G'

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host:

Master_User:

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mylogbin.000001

Read_Master_Log_Pos: 2218077

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 2218222

Relay_Master_Log_File: mylogbin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: joomla

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 2218077

Relay_Log_Space: 2218379

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

###14/08/09### 更新一下

修正當出現不同的字串…會無法動作

[root@ip-172-31-12-172 ~]# mysql -e 'show slave status\G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 114.43.123.19
                  Master_User: 
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mylogbin.000006
          Read_Master_Log_Pos: 354520
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 13797802
        Relay_Master_Log_File: mylogbin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '2014-08-05 11:00:00-2-1' for key 'PRIMARY'' on query. Default database: 'joomla'. Query: 'INSERT INTO sam_banner_tracks
(`count`,`track_type`,`banner_id`,`track_date`) VALUES
(1, 2,1,'2014-08-05 11')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 13797657
              Relay_Log_Space: 29610586
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '2014-08-05 11:00:00-2-1' for key 'PRIMARY'' on query. Default database: 'joomla'. Query: 'INSERT INTO sam_banner_tracks
(`count`,`track_type`,`banner_id`,`track_date`) VALUES
(1, 2,1,'2014-08-05 11')'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

新的程式碼，針對新字串"日期"格式修正

#!/bin/bash
#Delete duplicate records primary key conflict ERROR NUM 1062
#By Sam 14/08/09
echo `date +"%c"`

        Slave_SQL_Running=`mysql -e 'show slave status\G' |egrep 'Slave_SQL_Running' |awk '{print $2}'`
        Last_SQL_Errno=`mysql -e 'show slave status\G' |egrep Last_Errno |awk '{print $2}'`
        while [ "$Slave_SQL_Running" == No ] && [ "$Last_SQL_Errno" == 1062 ]; do
                echo `mysql -e 'show slave status\G'`
        DB=`mysql -e 'show slave status\G' |grep Last_Error |awk -F"'" '{print $8}'`
       TABLE=`mysql -e 'show slave status\G' |grep Last_Error |awk -F "'" '{print $10}' |awk '{print $3}' |sed 's/\`//g'`
                PRIKEY=`mysql -e "desc $DB.$TABLE" |grep PRI |awk 'NR==1{print $1}'`
                mysql -e 'show slave status\G' |grep Last_Error |awk -F"'" '{print $3}' > $$.dup
          grep -q " " /root/$$.dup $1
         if [ $? == 0 ]; then
            Duplicate=`mysql -e 'show slave status\G' |grep Last_Error |awk -F"'" '{print $3}' |awk -F"-" '{print $1"-"$2"-"$3}'`
            mysql -e "delete from $DB.$TABLE where $PRIKEY='$Duplicate'"
                        mysql -e "start slave sql_thread"
         else
            Duplicate=`mysql -e 'show slave status\G' |grep Last_Error |awk -F"'" '{print $3}'`
            mysql -e "delete from $DB.$TABLE where $PRIKEY='$Duplicate'"
                        mysql -e "start slave sql_thread"
         fi
      Slave_SQL_Running=`mysql -e 'show slave status\G' |egrep 'Slave_SQL_Running' |awk '{print $2}'`
      Last_SQL_Errno=`mysql -e 'show slave status\G' |egrep Last_Errno |awk '{print $2}'`
      rm -rf /root/$$.dup
   done

MySQL Master Slave Replication Error 1062 (14/08/09更新)

Newest

MOST