Activities

December 2012
M T W T F S S
« Nov   Jan »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

MySQL replication: How to view the problematic query from binary logs

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.

         Master_Log_File: mysql-bin.000474
          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.

 root@DB01 temp]# pwd
/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.

 [root@DB01 temp]# head -n 50  replication_error.txt  >> actual_log.txt

That’s it I got the exact sql statement which was returned error in slave from that file

..Enjoy

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>