binlog
MySQL的二进制日志,记录了所有增删改的日志,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。
开启
vi mysql/etc/my.cnf
在[mysqld] 区块
设置/添加 log-bin=mysql-bin 确认是打开状态(值 mysql-bin 是日志的基本名或前缀名);
重启mysqld服务使配置生效
# pkill mysqld
# mysql/bin/mysqld_safe --user=mysql &
查看
# mysql -uroot -p123456 ------登录
mysql> show variables like 'log_%';
+----------------------------------------+---------------------------------------+
| Variable_name | Value |
+----------------------------------------+---------------------------------------+
| log_bin | ON | ------> ON表示已经开启binlog日志
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /usr/local/mysql/data/martin.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
+----------------------------------------+---------------------------------------+
常用binlog日志操作命令
1.查看所有binlog日志列表
mysql> show master logs;
2.查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值
mysql> show master status;
3.刷新log日志,自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
注:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;
4.重置(清空)所有binlog日志
mysql> reset master;
查看log内容
mysql/bin/mysqlbinlog mysql/data/mysql-bin.000013 | grep 'xg_metatype(your table name)' ----查看你表的相关更改记录的sql语句
UPDATE `xg_metatype` SET `remark`='业务员业务员aaaaaaaaaaaaaa', `wechat_id`=1 WHERE `id`=253
UPDATE `xg_metatype` SET `remark`='aaaaaaaaaaaaaaaa', `wechat_id`=1 WHERE `id`=251
INSERT INTO `xg_metatype` (`name`, `type`, `remark`, `employee_template`, `is_independent_entity`, `wechat_id`, `create_employee_id`, `create_at`, `sort`, `is_del`) VALUES ('按摩师', 1, '按摩师按摩师按摩师按摩师按摩师按摩师', 2, 0, 1, 1, 1504081011, 39, 0)
或者机智的把输出写到一个log文件,有vim命令查找。
# mysql/bin/mysqlbinlog mysql/data/mysql-bin.000013 >> mysql.log
补充:
若出现问题:mysqlbinlog: unknown variable 'default-character-set=utf8mb4'
改成:mysqlbinlog --no-defaults mysql-bin.000123,即添加--no-defaults参数
网友评论