MySQL bin-log分析方法，mysqlbin-log

MySQL bin-log分析方法，mysqlbin-log

• Author：Echo Chen（陳斌）
• Email：chenb19870707@gmail.com
• Blog：Blog.csdn.net/chen19870707
• Date：September 9th, 2014     

Explain     前段時間，遊戲伺服器停服的時候總是很慢，幸運的是遊戲資料庫都開了bin-log，於是可以通過bin-log來分析停服時執行SQL語句的數量和執行時間，下面整理了一些關鍵步驟。

• 找到對應時間的bin-log檔案

      如果沒有在/etc/my.cnf中配置bin-log位置，MySQL的bin-log預設檔案位置在/var/lib/mysql下：

cd /var/lib/mysqlll -t

      

       找到想要尋找的時間段的SQL檔案，如果時間在兩個個檔案內，兩個檔案都需要。例如：這裡要尋找的是8月21 15:30 ~16:00，需要的檔案就是mysql-bin.000006

• 把二進位的檔案轉換成文字檔

mysqlbinlog mysql-bin.000006 > mysql-bin.000006.txt

      這個需要等待一點時間，需要等待一會兒.

• 將文字檔壓縮拷貝到本地

tar jcvf binlog.tar.bz2 mysql-bin.000006.txtsz binlog.tar.bz2

• 用文本工具開啟檔案，截取需要的時間段

        先看一下文本格式 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/*!*/;

      

         這裡有每一條SQL的執行時間，根據自己的需要，將不需要的時間段內的SQL刪掉，這裡最好用UltraEdit,因為檔案比較大。

• 分析bin-log檔案-----執行次數分析

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為所有表的表名，執行以上指令碼將列印所有表的UPDATE次數。

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

       在這裡看到UserRuneScape這個表執行的次數很多。

• 分析bin-log檔案-----執行時間分析

        再看一下文本格式 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即為執行時間，執行

grep -w exec_time=1 -r . |wc -l

即可查出執行在1s時間的條數，此外greo的參數-b表示在取出前幾行，-a表示取出後幾行，我們這裡找出執行慢的SQL語句。

grep -a1b6 -w exec_time=1 -r . > ~/test/result.txt

將結果儲存在result.txt中，再grep UPDATE 即可得到執行慢的SQL.

cd ~/testgrep -w UPDATE -r .  > ~/Desktop/result.txt

在稍作處理，去除每一行的檔案名稱，即可得到SQL語句

Reference

-

Echo Chen：Blog.csdn.net/chen19870707

-

mysql 怎把 mysql-bin000001 匯出成可以解讀的txt？

在bin_log目錄下使用mysqlbinlog mysql-bin.000001 > file1.txt
 
mysql 的log_bin 總是顯示off

一修改配置後要重啟服務；
二可以指定記錄檔名，查看是否有日誌，例如：
log-bin=bin.log