程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql_upgrade引起的master/slave replication中斷解決

mysql_upgrade引起的master/slave replication中斷解決

編輯:MySQL綜合教程

mysql_upgrade引起的master/slave replication中斷解決   在生產環境master服務器上處理完《1548-Cannot loadfrom mysql.proc. The table is probably corrupted》後,接到報警信息,slave服務器復制中斷查看slave 狀態   mysql>show slave status   發現如下語句執行錯誤   DROP DATABASEIF EXISTS performance_schema   performance_schema是mysql自帶的性能信息相關的庫,mysql怎麼會執行這個操作,看看錯誤日志吧   [root@db25522]# tail -n 500/data/my2/mysql/db25522.err   13051310:29:54 [Note] Error reading relay log event: slave SQL thread was killed   13051310:29:54 [ERROR] Error reading packet from server: Lost connection to MySQLserver during query ( server_errno=2013)   13051310:29:54 [Note] Slave I/O thread killed while reading event   13051310:29:54 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.002734',position 1017737307   13051310:29:57 [Note] Slave I/O thread: connected to master '[email protected]:3306',replicationstarted in log 'mysql-bin.002734' at position 1017737307   13051310:29:57 [Note] Slave SQL thread initialized, starting replication in log'mysql-bin.002734' at position 1017729051, relay log'/my/rlog/relay-bin.000764' position: 1017729197   13053015:00:53 [ERROR] Incorrect definition of table mysql.proc: expected column'comment' at position 15 to have type text, found type char(64).   13053015:00:53 [ERROR] Slave SQL: Query caused differenterrors on master and slave.    Error on master: message (format)='Cannot load from mysql.%s. The tableis probably corrupted' error code=1548 ; Error on slave: actual message='noerror', error code=0. Default database: 'performance_schema'. Query: 'DROP DATABASE IF EXISTS performance_schema',Error_code: 0   13053015:00:53 [Warning] Slave: Cannot load from mysql.proc. The table is probablycorrupted Error_code: 1548   13053015:00:53 [ERROR] Error running query, slave SQL thread aborted. Fix theproblem, and restart the slave SQL thread with "SLAVE START". We stoppedat log 'mysql-bin.002947' position 721651903   莫非是數據不一致導致的?發現master服務器有performance_schema這個庫,但是slave服務器沒有。在執行 mysql_upgrade -uroot 之前,主從復制在運行,判斷操作發生在mysql_upgrade-uroot之後,分析master上日志,在這個時間段內mysql進行了那些操作   [root@db25522]# mysqlbinlog  --no-defaults --start-date='2013-05-3015:00:00'  --end-date='2013-05-3015:03:00'   mysql-bin.002947  >/root/tmp.log   查詢日志發現   [root@db25522]#vi /root/tmp.log   /DROP   /*!*/;   # at721651876   #13053015:00:25 server id 13084  end_log_pos721651903     Xid = 435509540   COMMIT/*!*/;   # at 721651903   #13053015:00:53 server id 13084  end_log_pos721652022     Query   thread_id=418930    exec_time=0 error_code=1548   SETTIMESTAMP=1369897253/*!*/;   /*!\Cutf8 *//*!*/;   SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=28/*!*/;   DROP DATABASE IF EXISTS performance_schema   /*!*/;   # at721652022   #13053015:00:53 server id 13084  end_log_pos721652152     Query   thread_id=418930    exec_time=0 error_code=0   SETTIMESTAMP=1369897253/*!*/;   CREATEDATABASE performance_schema character set utf8   /*!*/;   # at721652152   #13053015:03:19 server id 13084  end_log_pos721652223     Query   thread_id=418956    exec_time=0 error_code=0   SETTIMESTAMP=1369897399/*!*/;   /*!\Cgbk *//*!*/;   SET@@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=28/*!*/;   BEGIN   /*!*/;   # at721652223   #13053015:03:19 server id 13084  end_log_pos721652336     Query   thread_id=418956    exec_time=0 error_code=0   usePriceDB/*!*/;   SETTIMESTAMP=1369897399/*!*/;   好吧,上面紅色部分,執行了這個操作,再看看slave錯誤日志   13053015:00:53 [ERROR] Incorrect definition of table mysql.proc: expected column'comment' at position 15 to have type text, found type char(64).   13053015:00:53 [ERROR] Slave SQL: Query caused different errors on master andslave.     Error on master: message(format)='Cannot load from mysql.%s. The table is probably corrupted' errorcode=1548 ; Error on slave: actual message='no error', error code=0. Defaultdatabase: 'performance_schema'. Query: 'DROP DATABASE IF EXISTSperformance_schema', Error_code: 0   13053015:00:53 [Warning] Slave: Cannot load from mysql.proc. The table is probablycorrupted Error_code: 1548   13053015:00:53 [ERROR] Error running query, slave SQL thread aborted. Fix the problem,and restart the slave SQL thread with "SLAVE START". We stopped atlog 'mysql-bin.002947' position 721651903   5.5的日志錯誤還是很人性化的,slave停止時讀取的binlog 日志文件,位置都很清楚。這為我們restart slave提供了方便。既然是DROP DATABASE IFEXISTS performance_schema 導致的錯誤,那麼跳過這條event。   Slave服務器:   mysql>show variables like '%skip%';   mysql>setglobal sql_slave_skip_counter =1;   mysql>slave start ;   復制正常   總結:復制雖然正常了。為什麼 mysql_upgrade 會做 DROP DATABASE IF EXISTSperformance_schema 這個操作?希望遇到類似問題的朋友,一起交流。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved