mysql二进制日志配置以及相关操作

in 软件 with 0 comment

在[mysqld]中添加选项

log-bin=/usr/local/mysql/log/mysql-bin #日志文件名称,注意是具体到文件,不是目录,未指定位置,默认数据文件位置
  mysql> show binary logs;   或show master logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql_bin.000001 |       375 |
    +------------------+-----------+
    1 row in set (0.00 sec)
 mysql> show master status;

    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql_bin.000001 |      375 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set
 mysql> show binlog events;  #可以格式化输出 show binlog events\G; 或指定日志文件查看show binlog events in 'mysql-bin.000001';
    +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
    | Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                           |
    +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
    | mysql_bin.000001 |   4 | Format_desc |       488 |         120 | Server ver: 5.6.33-log, Binlog ver: 4                          |
    | mysql_bin.000001 | 120 | Query       |       488 |         213 | BEGIN                                                          |
    | mysql_bin.000001 | 213 | Query       |       488 |         344 | use `jupan_whois`; DELETE FROM `domain_whois` WHERE (`id`='1') |
    | mysql_bin.000001 | 344 | Xid         |       488 |         375 | COMMIT /* xid=17 */                                            |
    +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
    4 rows in set
Responses