Recently I had got a replication error which causes a INSERT statement contains primary key violation and hence the replication stopped. I can see only partial SQL error statement on screen and did not view the full line of query. So I need to get FULL sql statement which is having errors.
Read_Master_Log_Pos: 520388449
Relay_Log_File: slave-relay.001034
Relay_Log_Pos: 768954312
Relay_Master_Log_File: mysql-bin.000469
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: db1,db2,test
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 '125623' for key 'PRIMARY'' on query. Default database: 'db1'. Query: 'INSERT INTO Plan ( ClitId, ProrId, FeerkGroupId, PlanGroupId, PlanPrrkGroupId, PlanName, PlanNo, PlanType, In, Repype, Report, Repeme, stus, BictId, Bises, Reee, TrFee, Prepar500, TPAId, etc etc...
Skip_Counter: 0
Exec_Master_Log_Pos: 768954166
Pls note down the Relay_Master_Log_File and Exec_Master_Log_Pos value which is required for later analysis.
Now I’m login to MySQL master server and take a copy of Relay_Master_Log_File to some other location to process. I uses “mysqlbinlog” utility to generate the human readable output from binary log file.
/sqldata/temp
[root@DB01 temp]# mysqlbinlog mysql-bin.000469 --start-position=768954166 >> replication_error.txt
Pls note that –start-position=768954166 variable is using the value of Exec_Master_Log_Pos from the SLAVE server.
I’ve set binary log file size is 1GB and so this log file will be huge sometimes. So I need only first few lines from that file.
That’s it I got the exact sql statement which was returned error in slave from that file
..Enjoy
Leave a Reply