mysql binary log 操作

今天药忘吃喽~ 2021-06-24 13:59 382阅读 0赞

1、查看log：  
mysql> show binary logs;  
\+------------------+-----------+  
| Log\_name         | File\_size |  
\+------------------+-----------+  
| mysql-bin.000001 |     29653 |  
| mysql-bin.000002 |   1035665 |  
| mysql-bin.000014 |       107 |  
\+------------------+-----------+  
  
  
2、查看当前的log：  
mysql> show master status;  
\+------------------+----------+--------------+------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |  
\+------------------+----------+--------------+------------------+  
| mysql-bin.000014 |      107 |              |                  |  
\+------------------+----------+--------------+------------------+  
1 row in set (0.00 sec)  
  
  
3、切换binary log：  
mysql> flush logs;  
Query OK, 0 rows affected (0.01 sec)  
  
  
4、清除所有binary log：  
mysql> reset master;  
mysql> show binary logs;  
\+------------------+-----------+  
| Log\_name         | File\_size |  
\+------------------+-----------+  
| mysql-bin.000001 |    0 |  
\+------------------+-----------+  
  
  
5、查看log里的events：（bin-log因为是二进制文件，不能通过记事本等编辑器直接打开查看，mysql提供两种方式查看方式）  
方法一：  
 1）查看当前日志中的events  
mysql> show binlog  events in "mysql-bin.000013";  
\+------------------+-----+-------------+-----------+-------------+------------------------------------------------+  
| Log\_name         | Pos | Event\_type  | Server\_id | End\_log\_pos | Info                                           |  
\+------------------+-----+-------------+-----------+-------------+------------------------------------------------+  
| mysql-bin.000013 |   4 | Format\_desc |         1 |         107 | Server ver: 5.5.25a-log, Binlog ver: 4         |  
| mysql-bin.000013 | 107 | Query       |         1 |         204 | use \`test\`; create table tb1(name varchar(50)) |  
| mysql-bin.000013 | 204 | Query       |         1 |         272 | BEGIN                                          |  
| mysql-bin.000013 | 272 | Table\_map   |         1 |         316 | table\_id: 33 (test.tb1)                        |  
| mysql-bin.000013 | 316 | Write\_rows  |         1 |         350 | table\_id: 33 flags: STMT\_END\_F                 |  
| mysql-bin.000013 | 350 | Xid         |         1 |         377 | COMMIT /\* xid=22 \*/                            |  
| mysql-bin.000013 | 377 | Rotate      |         1 |         420 | mysql-bin.000014;pos=4                         |  
\+------------------+-----+-------------+-----------+-------------+------------------------------------------------+  
7 rows in set (0.00 sec)  
  
  
 2）从某一点上来看：  
mysql> show binlog    events in "mysql-bin.000013"  from 204;  
\+------------------+-----+------------+-----------+-------------+--------------------------------+  
| Log\_name         | Pos | Event\_type | Server\_id | End\_log\_pos | Info                           |  
\+------------------+-----+------------+-----------+-------------+--------------------------------+  
| mysql-bin.000013 | 204 | Query      |         1 |         272 | BEGIN                          |  
| mysql-bin.000013 | 272 | Table\_map  |         1 |         316 | table\_id: 33 (test.tb1)        |  
| mysql-bin.000013 | 316 | Write\_rows |         1 |         350 | table\_id: 33 flags: STMT\_END\_F |  
| mysql-bin.000013 | 350 | Xid        |         1 |         377 | COMMIT /\* xid=22 \*/            |  
| mysql-bin.000013 | 377 | Rotate     |         1 |         420 | mysql-bin.000014;pos=4         |  
\+------------------+-----+------------+-----------+-------------+--------------------------------+  
5 rows in set (0.00 sec)  
  
  
 3）查看所有的：  
mysql> show binlog    events;  
  
  
注：为了排序美观，可以在结尾加\\G使结果横变纵，此时结尾无需加；语句结束符。  
      eg:   
mysql> show binlog events in 'mysql\_bin.000001'\\G   
...............省略...............   
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*   
   Log\_name: mysql\_bin.000001   
        Pos: 174   
Event\_type: Intvar   
  Server\_id: 1   
End\_log\_pos: 202   
      Info: INSERT\_ID=2   
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*   
   Log\_name: mysql\_bin.000001   
        Pos: 202   
Event\_type: Query   
  Server\_id: 1   
End\_log\_pos: 304   
       Info: use \`test\`; insert into bin(name) values ('orange')   
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 5. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*   
...............省略...............   
Log\_name:此条log存在那个文件中，从上面可以看出这2条log皆存在与mysql\_bin.000001文件中。   
Pos:log在bin-log中的开始位置   
Event\_type:log的类型信息   
Server\_id:可以查看配置中的server\_id,表示log是那个服务器产生   
End\_log\_pos：log在bin-log中的结束位置   
Info:log的一些备注信息，可以直观的看出进行了什么操作   
  
  
方法二：用mysql自带的工具mysqlbinlog,这是我们就需要知道bin-log存在硬盘的什么位置。可以在mysql控制台上使用：show variables like '%dir%'; 来查看文件位置：  
C:\\ProgramData\\MySQL\\MySQL Server 5.1\\data>mysqlbinlog mysql\_bin.000001   
/\*!40019 SET @@session.max\_insert\_delayed\_threads=0\*/;   
/\*!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;   
DELIMITER /\*!\*/;   
\# at 4   
\#140215 16:35:56 server id 1  end\_log\_pos 106   Start: binlog v 4, server v 5.1.51-community-log created 140215 16:35:56 at startup  
ROLLBACK/\*!\*/;   
BINLOG '   
7Mp7UA8BAAAAZgAAAGoAAAAAAAQANS4xLjUxLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA   
AAAAAAAAAAAAAAAAAADsyntQEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC   
'/\*!\*/;   
\# at 106   
\#140215 16:36:51 server id 1  end\_log\_pos 174   Query   thread\_id=2     exec\_time=0     error\_code=0  
SET TIMESTAMP=1350290211/\*!\*/;   
SET @@session.pseudo\_thread\_id=2/\*!\*/;   
SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=1, @@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;  
SET @@session.sql\_mode=1344274432/\*!\*/;   
SET @@session.auto\_increment\_increment=1, @@session.auto\_increment\_offset=1/\*!\*/;  
/\*!\\C utf8 \*//\*!\*/;   
SET @@session.character\_set\_client=33,@@session.collation\_connection=33,@@session.collation\_server=33/\*!\*/;  
SET @@session.lc\_time\_names=0/\*!\*/;   
SET @@session.collation\_database=DEFAULT/\*!\*/;   
BEGIN   
/\*!\*/;   
\# at 174   
\#140215 16:36:51 server id 1  end\_log\_pos 202   Intvar   
SET INSERT\_ID=3/\*!\*/;   
\# at 202   
\#140215 16:36:51 server id 1  end\_log\_pos 309   Query   thread\_id=2     exec\_time=0     error\_code=0  
use test/\*!\*/;   
SET TIMESTAMP=1350290211/\*!\*/;   
insert into bin(name) values('xishizhaohua')   
/\*!\*/;   
\# at 309   
\#140215 16:36:51 server id 1  end\_log\_pos 336   Xid = 28   
COMMIT/\*!\*/;   
\# at 336   
\#140215 16:37:25 server id 1  end\_log\_pos 379   Rotate to mysql\_bin.000002  pos: 4  
DELIMITER ;   
\# End of log file   
ROLLBACK /\* added by mysqlbinlog \*/;   
/\*!50003 SET COMPLETION\_TYPE=@OLD\_COMPLETION\_TYPE\*/;   
可以使用重定向命令输出到文件。