MySQL/mariadb知识点——日志记录(2)二进制日志
二进制日志
记录已提交事务导致数据改变或潜在导致数据改变的SQL语句,通过“重放”日志文件中的事件来生成数据副本,不依赖存储引擎类型。
开启二进制日志,默认是关闭的,二进制日志和数据分开存放
开启记录二进制文件的功能:
在my.cnf的[mysqld]下加入
log_bin[=/path/somefile]
默认二进制日志在数据库目录下,
mariadb-bin.000001
为二进制日志数据文件,
mariadb-bin.index
为二进制日志索引文件
记录二进制日志的三种方式:
- 基于语句的记录方式,每一条改变数据的语句记录为一条语句,节省空间,系统默认为此模式,但是不推荐使用,会有隐藏的风险
- 基于行的记录方式,每一行的改变都会将使其改变的语句记录为一条语句,日志量会很大,但是对数据的安全保护非常高
- 混合模式:mixed, 让系统自行判定该基于哪种方式进行
1 2 3 4 5 6 | MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format' ; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | -- 记录方式,系统默认基于语句方式 +---------------+-----------+ |
修改二进制日志记录方式
1 | SET binlog_format= 'ROW|STATEMENT|MIXED' ; |
二进制日志的相关变量
查看mariadb自行管理使用中的二进制日志文件列表
1 2 3 4 5 6 7 8 9 | MariaDB [(none)]> SHOW MASTER|BINARY LOGS; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000002 | 290 | | mariadb-bin.000003 | 264 | | mariadb-bin.000004 | 529038 | | mariadb-bin.000005 | 245 | +--------------------+-----------+ |
查看使用中的二进制日志文件
1 2 3 4 5 6 | MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000005 | 245 | | | +--------------------+----------+--------------+------------------+ |
查看二进制文件中的指定内容
1 | MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mariadb-bin.000004' FROM 1 LIMIT 2,3\G |
是否记录二进制日志,默认ON
1 | MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin' ; |
指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可
1 | MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin' |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | MariaDB [(none)]> SHOW VARIABLES LIKE 'max_binlog_size' ; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | max_binlog_size | 1073741824 | -- 单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G +-----------------+------------+ MariaDB [(none)]> SHOW VARIABLES LIKE 'sync_binlog' ; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 0 | -- 设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘 +---------------+-------+ MariaDB [(none)]> SHOW VARIABLES LIKE 'expire_logs_days' ; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 | -- 二进制日志可以自动删除的天数。 默认为0,即不自动删除 +------------------+-------+ |
mysqlbinlog命令
二进制日志的客户端命令工具
- --start-position=# 指定开始位置
- --stop-position=# 指定结束位置
- --start-datetime=(YYYY-MM-DD hh:mm:ss) 指定开始时间
- --stop-datetime=(YYYY-MM-DD hh:mm:ss) 指定结束时间
- --base64-output=decode-row
- -v |-vv |-vvv |-vvvv 显示详细信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [root@centos7 mysql] # mysqlbinlog --start-position=528864 --stop-position=529019 mariadb-bin.000004 --base64-output=decode-row -v # at 528864 #180611 20:59:46 server id 1 end_log_pos 528992 Query thread_id=29 exec_time=0 error_code=0 use `school`/*!*/; SET TIMESTAMP=1528721986/*!*/; INSERT students(StuID,Name,Age,Gender) VALUES (26, 'Tom' ,22, 'M' ) #改变数据的SQL语句 /*!*/; 事件发生的日期和时间:180611 20:59:46 事件发生的服务器标识:server id 1 事件的结束位置:end_log_pos 528992 事件的类型:Query 事件发生时所在服务器执行此事件的线程的ID:thread_id=29 语句的时间戳与将其写入二进制文件中的时间差:exec_time=0 错误代码:error_code=0 事件内容: GTID:Global Transaction ID,mysql5.6以mariadb10以上版本专属属性:GTID |
使用
mysqlbinlog mariadb-bin.000001 > file.sql
命令重定向到SQL文件中,再使用mysql -uroot -p < file.sql
命令可以直接导入数据,实现了备份还原的功能
二进制日志的管理
删除到02,注意:02不删除
1 | MariaDB [(none)]> PURGE BINARY LOGS TO 'mariadb.000002' ; |
删除2018-01-23之前的日志
1 2 | MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2018-01-23' ; MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30' ; |
删除所有二进制日志,index文件重新记数
1 | MariaDB [(none)]> RESET MASTER; |
手动触发日志滚动
1 | MariaDB [(none)]> FLUSH LOGS; |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步