MySQL Bin-log Analysis Method

• Author:echo Chen (Chenbin)
• Email:[email protected]
• Blog:blog.csdn.net/chen19870707
• Date:september 9th,      

Explain time ago, the game server is always very slow to stop the service, fortunately, the game database is open Bin-log, so can be bin-log to analyze the number of execution SQL statement and execution time, the following collation of some key steps.

• Find the Bin-log file for the corresponding time

If the Bin-log location is not configured in/etc/my.cnf, the MySQL bin-log default file location is under/var/lib/mysql:

Cd/var/lib/mysqlll-t

Locate the SQL file for the time period you want to find, and if the time is within two files, two files are required. For example: here to find the August 21 15:30 ~16:00, the required file is mysql-bin.000006

• Convert a binary file to a text file

Mysqlbinlog mysql-bin.000006 > Mysql-bin.000006.txt

This needs to wait a little while and wait a while.

• Copy a text file to a local

Tar jcvf binlog.tar.bz2 Mysql-bin.000006.txtsz binlog.tar.bz2

• Open a file with a text tool to intercept the required time period

First look at the format of the text format Bin-log:

# at 7473#110630 11:56:05 server ID 1  end_log_pos 7612  Query   thread_id=6     exec_time=0     error_code= 0SET timestamp=1309406165/*!*/; UPDATE ssmatch.young_league_match_7 SET status= ' playing ' WHERE mid=699617/*!*/;

Here are the execution time of each SQL, according to their own needs, the unnecessary time period of the SQL deleted, it is best to use UltraEdit, because the file is relatively large.

• Analysis of-----execution times of bin-log files

table_list= (account_tbl activity_tbl awardmsg_tbl bbrankfightpos_tbl Bloo Dbattlerank_tbl bloodbattle_tbl card_tbl checkin_tbl clickmsg_tbl duelrank_ TBL Dynamicrune_tbl equipfragment_tbl equipment_tbl fightin Gpos_tbl friends_tbl gemstone_tbl ghost_tbl heroattribute_tbl Herojuedi_ta               ble itemmarket_tbl item_tbl ladderdata_tbl ladderplayer_tbl Ladderranklist_tbl mission_tbl mysteryshop_tbl playerstatistics_tbl player_tbl Run Escaperecovery_tbl skill_tbl skyladderfightingposition_tbl tipsmsg_tbl treasure_tbl UserRune Scape_tbl vipcard_tbl) for I in ${table_list[@]}; Do Echo ${i} grep-w ${i}. -r | Grep-w UPDATE | Wc-ldone

Table_list is the table name for all tables, executing the above script will print the update count for all tables.

Account_tbl0activity_tbl4281awardmsg_tbl0bbrankfightpos_tbl1527bloodbattlerank_tbl190bloodbattle_tbl4281card_ Tbl376checkin_tbl4273clickmsg_tbl0duelrank_tbl83dynamicrune_tbl4276equipfragment_tbl0equipment_ Tbl95fightingpos_tbl103friends_tbl34gemstone_tbl43ghost_tbl3heroattribute_tbl4271herojuedi_table0itemmarket_ Tbl0item_tbl486ladderdata_tbl0ladderplayer_tbl3616ladderranklist_tbl0mission_tbl4281mysteryshop_ Tbl4279playerstatistics_tbl0player_tbl4282runescaperecovery_tbl10skill_tbl15skyladderfightingposition_ Tbl3744tipsmsg_tbl0treasure_tbl4274<span style= "color: #ff0000;" >userrunescape_tbl15519</span>vipcard_tbl6

See here Userrunescape This table executes a lot of times.

• Analysis of-----Execution time of bin-log files

Then look at the format of the text format Bin-log:

# at 7473#110630 11:56:05 server ID 1  end_log_pos 7612  Query   thread_id=6     exec_time=0     error_code= 0SET timestamp=1309406165/*!*/; UPDATE ssmatch.young_league_match_7 SET status= ' playing ' WHERE mid=699617/*!*/;

Exec_time is the execution time, which executes

Grep-w Exec_time=1-r. |wc-l

You can find out the number of bars executed at 1s time, and the Greo parameter-B means that the first few lines are taken out,-a means the last few lines are taken out, and we find the SQL statement that executes slowly.

Grep-a1b6-w Exec_time=1-r. > ~/test/result.txt

Save the results in Result.txt, and then grep UPDATE to get a slow SQL execution.

CD ~/testgrep-w update-r.  > ~/desktop/result.txt

In a little bit of processing, remove the file name of each row, you can get the SQL statement

Reference

1. Http://www.cnblogs.com/edwardlost/archive/2011/07/13/2105598.html

-

Echo chen:blog.csdn.net/chen19870707

-

MySQL Bin-log Analysis Method