mysql> SHOW GLOBAL VARIABLES LIKE '%log%';
| slow_query_log     | OFF  #定義慢查詢?nèi)罩镜?
| slow_query_log_file  |/mydata/data/stu18-slow.log  #輸出方式為file（文件）時(shí)定義慢查詢?nèi)罩镜奈恢?

[root@stu18 data]# vim /etc/my.cnf
[mysqld]
slow_query_log=1
log-slow-queries [= DIR/[filename] ]

mysql> SHOW GLOBAL VARIABLES LIKE 'long%';

+-----------------+-----------+
| Variable_name  | Value   |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.04 sec)

mysql> set globalslow_query_log=1;    #開啟慢查詢?nèi)罩?

Query OK, 0 rowsaffected (0.35 sec)

mysql> setsession long_query_time=0.001;   #更改時(shí)間（當(dāng)前session中，退出則重置）

Query OK, 0 rowsaffected (0.00 sec)

mysql> set globallong_query_time=0.001;   #更改時(shí)間（全局中，重啟服務(wù)則重置）
mysql> SHOWVARIABLES LIKE 'long%';     #查詢定義時(shí)間

+-----------------+----------+
| Variable_name  | Value  |
+-----------------+----------+
| long_query_time |0.001000 |
+-----------------+----------+
1 row in set (0.00sec)
mysql> showglobal variables like "%slow%"; #查看慢查詢?nèi)罩鹃_啟狀態(tài)
+---------------------+-----------------------------+
| Variable_name    | Value            |
+---------------------+-----------------------------+
|log_slow_queries  | ON             |
|slow_launch_time  | 2              |
|slow_query_log   | ON             |
|slow_query_log_file | /mydata/data/stu18-slow.log |
+---------------------+-----------------------------+
4 rows in set (0.03sec)

mysql> use mysql
mysql> selectuser,host,password from user where user="root";

+------+------------------+----------+
| user | host       | password |
+------+------------------+----------+
| root |localhost    |     |
| root |stu18.magedu.com |     |
| root |127.0.0.1    |     |
| root | ::1       |     |
+------+------------------+----------+
4 rows in set (0.08sec)    #查詢時(shí)間為0.08
mysql> systemmore /mydata/data/stu18_slow.log     #查詢慢查詢?nèi)罩居涗浶畔?
/usr/local/mysql/bin/mysqld,Version: 5.5.33-log (Source distribution). started
with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time         Id Command  Argument
>>>>>>>>>>>>>>>>部分已省略>>>>>>>>>>>>>>
# Time: 13100723:46:33
# User@Host:root[root] @ localhost []
# Query_time:0.108459 Lock_time: 0.000216 Rows_sent:4 Rows_examined: 6
SETtimestamp=1381160793;
selectuser,host,password from user where user="root";

mysql> SHOWGLOBAL VARIABLES LIKE '%log%';

+-----------------------------------------+-----------------------------------+
| Variable_name              | Value               |
+-----------------------------------------+-----------------------------------+
| innodb_flush_log_at_trx_commit     | 1    #在事務(wù)提交時(shí)innodb是否同步日志從緩沖到文件中1表示事務(wù)以提交就同步不提交每隔一秒同步一次，性能會(huì)很差造成大量的磁盤I/O；定義為2表示只有在事務(wù)提交時(shí)才會(huì)同步但是可能會(huì)丟失整個(gè)事務(wù)   |
|innodb_locks_unsafe_for_binlog     |OFF                |
| innodb_log_buffer_size         | 8388608              |
|innodb_log_file_size          |5242880              |
| innodb_log_files_in_group        | 2   #至少有兩個(gè)        |
|innodb_log_group_home_dir        |./   #定義innodb事務(wù)日志組的位置    |
|innodb_mirrored_log_groups       |1  #表示對(duì)日志組做鏡像     |

[root@stu18 data]#ls -lh

-rw-rw---- 1 mysqlmysql 5.0M Oct 7 23:36 ib_logfile0
-rw-rw---- 1 mysqlmysql 5.0M Aug 12 01:06 ib_logfile1

mysql> showglobal variables like "%log_bin%";

+---------------------------------+-------+
| Variable_name          | Value |
+---------------------------------+-------+
| log_bin             | ON  | #已開啟
|log_bin_trust_function_creators | OFF  |
| sql_log_bin           | ON  |
+---------------------------------+-------+

mysql> showglobal variables like "%log%";

sql_log_bin ={ON|OFF}   #用于控制會(huì)話級(jí)別二進(jìn)制日志功能的開啟或關(guān)閉。默認(rèn)為ON，表示啟用記錄功能。用戶可以在會(huì)話級(jí)別修改此變量的值，但其必須具有SUPER權(quán)限。
binlog_cache_size =32768  #默認(rèn)值32768 Binlog Cache用于在打開了二進(jìn)制日志（binlog）記錄功能的環(huán)境，是MySQL 用來提高binlog的記錄效率而設(shè)計(jì)的一個(gè)用于短時(shí)間內(nèi)臨時(shí)緩存binlog數(shù)據(jù)的內(nèi)存區(qū)域。一般來說，如果我們的數(shù)據(jù)庫中沒有什么大事務(wù)，寫入也不是特別頻繁，2MB～4MB是一個(gè)合適的選擇。但是如果我們的數(shù)據(jù)庫大事務(wù)較多，寫入量比較大，可與適當(dāng)調(diào)高binlog_cache_size。同時(shí)，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設(shè)置的binlog_cache_size是否足夠，是否有大量的binlog_cache由于內(nèi)存大小不夠而使用臨時(shí)文件（binlog_cache_disk_use）來緩存了。
binlog_stmt_cache_size= 32768    #當(dāng)非事務(wù)語句使用二進(jìn)制日志緩存，但是超出binlog_stmt_cache_size時(shí)，使用一個(gè)臨時(shí)文件來存放這些語句。
log_bin = mysql-bin#指定binlog的位置，默認(rèn)在數(shù)據(jù)目錄下。
binlog-format= {ROW|STATEMENT|MIXED}   #指定二進(jìn)制日志的類型，默認(rèn)為MIXED。如果設(shè)定了二進(jìn)制日志的格式，卻沒有啟用二進(jìn)制日志，則MySQL啟動(dòng)時(shí)會(huì)產(chǎn)生警告日志信息并記錄于錯(cuò)誤日志中。
sync_binlog = 10#設(shè)定多久同步一次二進(jìn)制日志至磁盤文件中，0表示不同步，任何正數(shù)值都表示對(duì)二進(jìn)制每多少次寫操作之后同步一次。當(dāng)autocommit的值為1時(shí)，每條語句的執(zhí)行都會(huì)引起二進(jìn)制日志同步，否則，每個(gè)事務(wù)的提交會(huì)引起二進(jìn)制日志同步
max_binlog_cache_size= {4096 .. 18446744073709547520}   #二進(jìn)定日志緩存空間大小，5.5.9及以后的版本僅應(yīng)用于事務(wù)緩存，其上限由max_binlog_stmt_cache_size決定。
max_binlog_stmt_cache_size= {4096 .. 18446744073709547520}  #二進(jìn)定日志緩存空間大小，5.5.9及以后的版本僅應(yīng)用于事務(wù)緩存
expire_log_days ={0..99}  #設(shè)定二進(jìn)制日志的過期天數(shù)，超出此天數(shù)的二進(jìn)制日志文件將被自動(dòng)刪除。默認(rèn)為0，表示不啟用過期自動(dòng)刪除功能。如果啟用此功能，自動(dòng)刪除工作通常發(fā)生在MySQL啟動(dòng)時(shí)或FLUSH日志時(shí)。

[root@stu18 ~]#my.cnf
[mysqld]
log-bin [=DIR \ [filename]]

[root@stu18 ~]# cd /mydata/data/
[root@stu18 data]#ls -lh

-rw-rw---- 1 mysqlmysql 14K Aug 13 15:30 mysql-bin.000001
-rw-rw---- 1 mysqlmysql 150 Aug 13 17:05 mysql-bin.000002
-rw-rw---- 1 mysqlmysql 150 Aug 13 17:06 mysql-bin.000003
-rw-rw---- 1 mysqlmysql 150 Aug 13 17:07 mysql-bin.000004
-rw-rw---- 1 mysqlmysql 150 Aug 13 17:39 mysql-bin.000005
-rw-rw---- 1 mysqlmysql 126 Aug 13 19:03 mysql-bin.000006
-rw-rw---- 1 mysqlmysql 126 Aug 13 19:03 mysql-bin.000007
-rw-rw---- 1 mysqlmysql 126 Aug 13 19:05 mysql-bin.000008
-rw-rw---- 1 mysqlmysql 107 Aug 13 19:05 mysql-bin.000009
-rw-rw---- 1 mysqlmysql 353 Oct 7 23:40 mysql-bin.000010
-rw-rw---- 1 mysqlmysql 190 Oct 7 20:43 mysql-bin.index

[root@stu18 data]#cat mysql-bin.index

./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000010

mysql> showbinary logs;   #顯示當(dāng)前服務(wù)器使用的二進(jìn)制文件及大小

+------------------+-----------+
| Log_name     | File_size |
+------------------+-----------+
| mysql-bin.000001|   13814 |
| mysql-bin.000002|    150 |
| mysql-bin.000003|    150 |
| mysql-bin.000004|    150 |
| mysql-bin.000005|    150 |
| mysql-bin.000006|    126 |
| mysql-bin.000007|    126 |
| mysql-bin.000008|    126 |
| mysql-bin.000009|    107 |
| mysql-bin.000010|    353 |
+------------------+-----------+
10 rows in set (0.07sec)

mysql> showmaster logs;   #顯示主服務(wù)器使用的二進(jìn)制文件及大小

+------------------+-----------+
| Log_name     | File_size |
+------------------+-----------+
| mysql-bin.000001|   13814 |
| mysql-bin.000002|    150 |
| mysql-bin.000003|    150 |
| mysql-bin.000004|    150 |
| mysql-bin.000005|    150 |
| mysql-bin.000006|    126 |
| mysql-bin.000007|    126 |
| mysql-bin.000008|    126 |
| mysql-bin.000009|    107 |
| mysql-bin.000010|    353 |
+------------------+-----------+
10 rows in set (0.02sec)
mysql> showmaster status;  #當(dāng)前使用的二進(jìn)制文件及所處位置
+------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010|   353 |       |         |
+------------------+----------+--------------+------------------+
1 row in set (0.00sec)

mysql> flushlogs;

Query OK, 0 rowsaffected (0.23 sec)

mysql> showmaster status;

+------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011|   107 |       |         |
+------------------+----------+--------------+------------------+
1 row in set (0.00sec)

mysql> createdatabase yong;

Query OK, 1 rowaffected (0.12 sec)

mysql> createtable yong.tb1 (id int,name char(20));

Query OK, 0 rowsaffected (0.44 sec)

mysql> insertinto yong.tb1 values(1,'tom');

Query OK, 1 rowaffected (0.14 sec)

mysql> showmaster status;

+------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011|   479 |       |         |
+------------------+----------+--------------+------------------+
1 row in set (0.00sec)

SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

mysql> showbinlog events\G     #查看所有的二進(jìn)制信息

***************************87. row ***************************
  Log_name: mysql-bin.000001
    Pos: 13580
 Event_type: Query
 Server_id: 1
End_log_pos: 13688
    Info: use `hellodb`; /*!40000 ALTERTABLE `toc` DISABLE KEYS */
***************************88. row ***************************
  Log_name: mysql-bin.000001
    Pos: 13688
 Event_type: Query
 Server_id: 1
End_log_pos: 13795
    Info: use `hellodb`; /*!40000 ALTERTABLE `toc` ENABLE KEYS */
***************************89. row ***************************
  Log_name: mysql-bin.000001
    Pos: 13795
 Event_type: Stop
 Server_id: 1
End_log_pos: 13814
    Info:
89 rows in set (0.00sec)
mysql> showbinlog events in 'mysql-bin.000011';  #查看指定日志的二進(jìn)制信息
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
| Log_name     | Pos | Event_type | Server_id | End_log_pos | Info                     |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
| mysql-bin.000011|  4 | Format_desc |     1 |     107 | Server ver: 5.5.33-log, Binlogver: 4    |
| mysql-bin.000011 |107 | Query    |     1 |     190 | create database yong             |
| mysql-bin.000011 |190 | Query    |     1 |     293 | create table yong.tb1 (idint,name char(20)) |
| mysql-bin.000011 |293 | Query    |     1 |     357 | BEGIN                    |
| mysql-bin.000011 |357 | Query    |     1 |     452 | insert into yong.tb1values(1,'tom')     |
| mysql-bin.000011 |452 | Xid     |     1 |     479 | COMMIT /* xid=103 */             |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
6 rows in set (0.00sec)
mysql> showbinlog events in 'mysql-bin.000011' from 190; #從指定的事件位置開始
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
| Log_name     | Pos | Event_type | Server_id |End_log_pos | Info                     |
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
| mysql-bin.000011 |190 | Query   |     1 |     293 | create table yong.tb1 (idint,name char(20)) |
| mysql-bin.000011 |293 | Query   |     1 |     357 | BEGIN                    |
| mysql-bin.000011 |357 | Query   |     1 |     452 | insert into yong.tb1values(1,'tom')     |
| mysql-bin.000011 |452 | Xid    |     1 |     479 | COMMIT /* xid=103 */             |
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
4 rows in set (0.00sec)
mysql> showbinlog events in 'mysql-bin.000011' from 190 limit 3; #指定偏移量(不是語句，是事件)
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
| Log_name     | Pos | Event_type | Server_id |End_log_pos | Info                     |
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
| mysql-bin.000011 |190 | Query   |     1 |     293 | create table yong.tb1 (idint,name char(20)) |
| mysql-bin.000011 |293 | Query   |     1 |     357 | BEGIN                    |
| mysql-bin.000011 |357 | Query   |     1 |     452 | insert into yong.tb1values(1,'tom')     |
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
3 rows in set (0.00sec)

[root@stu18 data]#mysqlbinlog mysql-bin.000017    #必須在數(shù)據(jù)目錄下

*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET@@session.max_insert_delayed_threads=0*/;
/*!50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4    #事件開始處
#131009 0:25:59 server id 1 end_log_pos 107  Start: binlog v 4, server v 5.5.33-log created 131009 0:25:59 
# Warning: thisbinlog is either in use or was not closed properly.
BINLOG '
FzJUUg8BAAAAZwAAAGsAAAABAAQANS41LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#131009 0:26:36 server id 1 end_log_pos 192  Query  thread_id=12  exec_time=0 error_code=0   #131009 0:26:36年月日的簡(jiǎn)寫方式；end_log_pos事件結(jié)束處；thread_id=12 哪個(gè)會(huì)話線程創(chuàng)建的此語句；exec_time=0 執(zhí)行時(shí)長單位為秒；error_code=0 錯(cuò)誤代碼0表示沒有
SET TIMESTAMP=1381249596/*!*/;   #預(yù)設(shè)信息(環(huán)境設(shè)定)

[root@stu18 data]#mysqlbinlog mysql-bin.000017 > /tmp/a.sql

[root@stu18 data]#mysql < a.sql

mysql> resetmaster;  

Query OK, 0 rowsaffected (0.17 sec)
mysql> showbinary logs;
+------------------+-----------+
| Log_name     | File_size |
+------------------+-----------+
| mysql-bin.000001|    107 |
+------------------+-----------+
1 row in set (0.04sec)

mysql> PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }

mysql> PURGEBINARY LOGS TO 'mysql-bin.000007';

Query OK, 0 rowsaffected (0.11 sec)

mysql> showbinary logs;

+------------------+-----------+
| Log_name     | File_size |
+------------------+-----------+
| mysql-bin.000007|    150 |
| mysql-bin.000008|    150 |
| mysql-bin.000009|    150 |
| mysql-bin.000010|    150 |
| mysql-bin.000011|    150 |
| mysql-bin.000012|    150 |
| mysql-bin.000013|    150 |
| mysql-bin.000014|    150 |
| mysql-bin.000015|    150 |
| mysql-bin.000016|    150 |
| mysql-bin.000017|    483 |
+------------------+-----------+
11 rows in set (0.04sec)

[root@stu18 data]#cat mysql-bin.index

./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000010
./mysql-bin.000011
./mysql-bin.000012
./mysql-bin.000013
./mysql-bin.000014
./mysql-bin.000015
./mysql-bin.000016
./mysql-bin.000017

mysql> PURGEBINARY LOGS BEFORE '13-10-19 10:26:36'; #使用時(shí)間來刪除二進(jìn)制日志

Query OK, 0 rowsaffected (0.05 sec)