从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件

开启 log-bin mysqlbinlog命令详解

发布:蔺要红08-27分类: MYSQL


开启log-bin

[mysqld]
log-bin = /data/3306/mysql-bin  # 表示开启binlog
binlog_format=mixed             #  binlog日志格式,mysql默认采用statement
expire_logs_days = 10           # binlog过期清理时间,默认值为0,表示“没有自动删除
max_binlog_size = 512M          # 定义binlog日志大小,默认1G
binlog_cache_size = 1M          # binlog缓存大小
max_binlog_cache_size = 1M      # 最大binlog缓存大小

--  修改
binlog_format="ROW"
binlog_format="STATEMENT"


Binlog_Do_DB         # 设定哪些数据库需要记录Binlog;
Binlog_Ignore_DB     # 设定哪些数据库不要记录Binlog;


查看binlog模式            https://www.linyaohong.com/blog/mysql/251.html(详细介绍)

​​​​​​​mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)


binlog_cache_size (默认32KB,调优以及状态查看)​​​​​
mysql> show global status like 'bin%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Binlog_cache_disk_use      | 35    | # 测试环境没调大,已经使用了35次disk
| Binlog_cache_use           | 32078 | # 默认32K 设置太大的话,会比较消耗内存资源;设置太小又会使用到临时文件即disk
| Binlog_stmt_cache_disk_use | 46    |
| Binlog_stmt_cache_use      | 163   |
+----------------------------+-------+
4 rows in set (0.00 sec)

binlog二进制转为SQL语句
方法一:如果binlog比较小,可以通过比较直观的命令来查看
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                               |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.62-log, Binlog ver: 4                                              |
| mysql-bin.000001 | 107 | Query       |         1 |         175 | BEGIN                                                                              |
| mysql-bin.000001 | 175 | Query       |         1 |         300 | use `hong`; UPDATE `hong`.`honghong` SET `name` = '123123' WHERE `id` = 33         |
| mysql-bin.000001 | 300 | Xid         |         1 |         327 | COMMIT /* xid=15 */                                                                |
| mysql-bin.000001 | 327 | Query       |         1 |         401 | BEGIN                                                                              |
| mysql-bin.000001 | 401 | Query       |         1 |         534 | use `linyaohong`; UPDATE `linyaohong`.`test` SET `num` = 77777 WHERE `id` = 134873 |
| mysql-bin.000001 | 534 | Xid         |         1 |         561 | COMMIT /* xid=18 */                                                                |
| mysql-bin.000001 | 561 | Stop        |         1 |         580 |                                                                                    |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
​​​​​​​
​​​​​​​方法二:mysqlbinlog命令
常用参数

--start-datetime:从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
--stop-datetime:从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
--start-position:从二进制日志中读取指定position 事件位置作为开始。
--stop-position:从二进制日志中读取指定position 事件位置作为事件截至​​​
mysqlbinlog -d crm mysqld-bin.000001 > crm-events.txt  # 制定库 导出binlog日志为原生sql语句

mysqlbinlog -d crm mysqld-bin.000001 mysqld-bin.000002 > crm-events.txt  # 可以接多个,或者mysqld-bin.000*匹配

# 按照位置导出(指定结尾不指定开头,则从文件开始到指定的位置。指定开始不指定结束,则从开始位置到文件结尾)
mysqlbinlog -d hong --start-position=2061 mysql-bin.000001  > 123.sql
mysqlbinlog mysql-bin.000009 --start-position 154 --stop-position 755 > 123.sql

# 按照时间导出
mysqlbinlog  -d cs457_shangtua_  --start-datetime="2019-05-28 09:00:00"  --stop-datetime="2019-05-28 12:00:00" mysql-bin.000019 >457.sql

mysqlbinlog mysql-bin.000002 >123.sql
mysql -uroot  -p111111 -S /data/3306/mysql.sock < 123.sql #把所有SQL语句导入mysql数据库中


# -d 还可以指定库(只导出test库的语句,恢复的时候指定test库,把语句恢复到test中,已做实验)
mysqlbinlog -d test mysql-bin.000002 >123.sql
mysql -uroot  -p111111 -S /data/3306/mysql.sock  test< 123.sql #把SQL语句导入test中

grep -E  "^UPDATE|INSERT" linyaohong.sql  # 可以筛选出UPDATE和INSERT开头的语句,

-d  导出指定库的语句注意事项
        必须使是使用 use linyaohong; insert into test(num) values(1)
        如果使用  insert into linyaohong.test(num) values(1) 则不能正常导出指定的库
-- 测试结果,从mysql的库底下,通过库名.表名 的方式插入, -d不能正常导出
mysql> use mysql;
Database changed
mysql> insert into hong.honghong(name) values("aadf");
Query OK, 1 row affected (0.00 sec)

-- mysqlbinlog -d mysql mysql-bin.000001 中存在刚才执行的语句
use `mysql`/*!*/;
SET TIMESTAMP=1567005226/*!*/;
insert into hong.honghong(name) values("aadf")

-- mysqlbinlog -d hong mysql-bin.000001 里面并没有刚才执行的语句

-- 有个小坑注意一下,一般情况下网站都是一个账号一个库,理论上不会出现上述情况


mysqlbinlog命令总结:
1. 把 binlog解析为sql语句(包含位置和时间点)
2. -d 参数根据指定库拆分 binlog(拆分单表binlog可通过SoL关键字过滤)
3. 通过位置参数截取部分 binlog:位置参数:--art-position-365 --stop-position=456,精确定位取内容
4. 通过时间截取部分binlog:时间参数: --start-datetime="2014-10-1617:1415 --stop-datetime="2014-10-1617:1415 模糊取内容
5、-r 文件名,相当于重定向 “>” 
6、解析ROW级别binlog日志的方法(如果binlog格式设置为ROW/不加下面的参数则不能正常输出)

   关于binlog格式参考连接: https://www.linyaohong.com/blog/mysql/251.html
-- 不使用 --base64-output=decode-rows -v 则不能正常输出
BINLOG '
9L1mXRMBAAAAMgAAAOEAAAAAACEAAAAAAAEABGhvbmcACGhvbmdob25nAAIDDwL9AgI=
9L1mXRgBAAAABQQAAOYEAAAAACEAAAAAAAAAAv///AEAAAADAGxpbvwBAAAACgBsaW55YW9ob25n
/AIAAAADAGxpbvwCAAAACgBsaW55YW9ob25n/AMAAAADAGxpbvwDAAAACgBsaW55YW9ob25n/AQA
...
.....
YW9ob25n/CQAAAADAGxpbvwkAAAACgBsaW55YW9ob25n/CUAAAADAGxpbvwlAAAACgBsaW55YW9o
b25n
9L1mXRgBAAAAOQAAAB8FAAAAACEAAAAAAAEAAv///CYAAAADAGxpbvwmAAAACgBsaW55YW9ob25n
'/*!*/;
# at 1311


mysqlbinlog --base64-output=decode-rows -v mysql-bin.000016
mysqlbinlog --base64-output=“decode-rows” --verbose mysql-bin.000016​​​​​​​

--  输出

# at 1254
#190829  1:46:28 server id 1  end_log_pos 225 	Table_map: `hong`.`honghong` mapped to number 33
#190829  1:46:28 server id 1  end_log_pos 1254 	Update_rows: table id 33
#190829  1:46:28 server id 1  end_log_pos 1311 	Update_rows: table id 33 flags: STMT_END_F
### UPDATE `hong`.`honghong`
### WHERE
###   @1=1
....
......
###   @2='lin'
### SET
###   @1=3
###   @2='linyaohong'
### UPDATE `hong`.`honghong`
 

 
 
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站