MySQL bin-log analysis method, mysqlbin-log

MySQL bin-log analysis method, mysqlbin-log

• Author: Echo Chen (Chen Bin)
• Email: chenb19870707@gmail.com
• Blog: Blog.csdn.net/chen19870707
• Date: September 9th, 2014

Some time ago, when the game server was suspended, it was always slow. Fortunately, all the game databases opened 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 while, remove the file names of each row to obtain the SQL statement.

Reference

-

Echo Chen: Blog.csdn.net/chen19870707

-

Mysql how to export the mysql-bin000001 can be interpreted txt?

Use mysqlbinlog mysql-bin.000001> file1.txt in the bin_log directory

Mysql log_bin always shows off

1. Restart the service after modifying the configuration;
2. You can specify the log file name to check whether logs exist. For example:
Log-bin = bin. log