MySQLbin-log Analysis Method
Author: Echo Chen (Chen bin) Email: chenb19870707@gmail.comBlog: Blog.csdn.net/chen19870707Date:September 9th, some time ago, the game server is always very slow when the server is stopped, lucky is that the game database has opened the bin-log, therefore, you can use bin-log to analyze the number of SQL statements executed and the execution time when the service is stopped. The following describes some key steps. Find the bin-log file of the corresponding time
If the bin-log location is not configured in/etc/my. cnf, the default file location of MySQL is/var/lib/mysql:
cd /var/lib/mysqlll -t
Find the SQL file of the time range you want to search for. If the time is within two files, both files are required. For example, here we want to find August 21 15:30 ~ 16: 00, the file is the mysql-bin.000006
Convert a binary file to a text file
mysqlbinlog mysql-bin.000006 > mysql-bin.000006.txt
Wait for a while.
Compress and copy text files to a local device
tar jcvf binlog.tar.bz2 mysql-bin.000006.txtsz binlog.tar.bz2
Use a text tool to open a file and intercept the required time
Let's take a look at the text format of the bin-log record format:
# 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/*!*/;
The execution time of each SQL statement is as follows. You can delete the SQL statements that are not needed in a time period based on your needs. UltraEdit is recommended because the file size is large.
Analyze the bin-log File ----- execution count analysis
table_list=(Account_tbl Activity_tbl AwardMsg_tbl BBRankFightPos_tbl BloodBattleRank_tbl BloodBattle_tbl Card_tbl Checkin_tbl ClickMsg_tbl DuelRank_tbl DynamicRune_tbl EquipFragment_tbl Equipment_tbl FightingPos_tbl Friends_tbl Gemstone_tbl Ghost_tbl HeroAttribute_tbl HeroJuedi_table ItemMarket_tbl Item_tbl LadderData_tbl LadderPlayer_tbl LadderRankList_tbl Mission_tbl MysteryShop_tbl PlayerStatistics_tbl Player_tbl RuneScapeRecovery_tbl Skill_tbl SkyLadderFightingPosition_tbl TipsMsg_tbl Treasure_tbl UserRuneScape_tbl VipCard_tbl)for i in ${table_list[@]}; do echo ${i} grep -w ${i} . -r | grep -w UPDATE | wc -ldone
Table_list is the name of all tables. Executing the above script will print the number of UPDATE times 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
Here we can see that the table UserRuneScape is executed many times.
Analyze the bin-log File ----- execution time analysis
Let's take a look at the text format of the bin-log record format:
# 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.
grep -w exec_time=1 -r . |wc -l
You can check the number of items executed in 1 s. In addition, the greo parameter-B indicates the first few rows before the removal, and-a indicates the last few rows. Here we can find slow SQL statements.
grep -a1b6 -w exec_time=1 -r . > ~/test/result.txt
Save the result to result.txt, and then grep UPDATE to obtain the slow SQL statement.
cd ~/testgrep -w UPDATE -r . > ~/Desktop/result.txt
After a few operations, remove the file names of each row to obtain the SQL statement Referencehttp: // www.cnblogs.com/edwardlost/archive/2011/07/13/2105598.html
Echo Chen: Blog.csdn.net/chen19870707