工业电商做网站怎么样,php网站制作费用,东莞官方网站,wordpress 文章图片自动添加图片原文链接#xff1a;https://zhuanlan.zhihu.com/p/697078870
目录#xff1a;
binlog 是什么binlog 配置和查看binlog 的类型binlog 如何恢复数据binlog 是逻辑日志还是物理日志binlog 作用
注意#xff1a;以下所有的操作都在 MySQL 8.0 版本实现。
1、binlog 是什么 …原文链接https://zhuanlan.zhihu.com/p/697078870
目录
binlog 是什么binlog 配置和查看binlog 的类型binlog 如何恢复数据binlog 是逻辑日志还是物理日志binlog 作用
注意以下所有的操作都在 MySQL 8.0 版本实现。
1、binlog 是什么
首先介绍一下对于一个 SQL 语句它常常被分为以下几种类型
DDLData Definition Language数据定义语言 用来操作数据库、表、列等比如 CREATE、ALTER、DROPDMLData Manipulation Language数据操作语言 用来操作表里的数据比如 INSERT、UPDATE、DELETEDCLData Control Language数据控制语言 用来操作访问权限等比如 GRANTDQLData Query Language数据查询语言 用来查询数据比如 SELECTTCLTransaction Control Language事务控制语言 用于数据库的事务管理比如 COMMITROLLBACK
而 binlog即 binary log是 MySQL 的二进制日志文件这个文件记录了我们所有的 DDLDMLTCL 等操作比如表的创建数据的插入、更新和删除等。
比如我们下面执行的创建数据库、表插入、更新数据在 binlog 配置开启的情况下都会被记录到 binlog 中
CREATE DATABASE db_test DEFAULT CHARACTER set utf8;create table users (id int not null auto_increment primary key,name varchar(20) not null,email varchar(100) default
);insert into users (name, email) values(张三, 12345qq.com), (李四, 345123qq.com);update users set email 4123qq.com where id1;
2、binlog 配置和查看
1) binlog 查看配置
我们可以通过下面的命令查看 binlog 日志是否打开以及存储的文件夹
show variables like %log_bin%;
显示的内容如下 其主要字段含义分别如下
log_bin 表示是否开启 binloglog_bin_basename 表示的是 binlog 所在的文件夹log_bin_index 表示的是 binlog 的索引文件我们可以在其中找到所有的 binlog 日志sql_log_bin 用于主从复制这个参数关闭后主库的改动不会记录到 binlog不会复制到从库
log_bin_basename 指向的是 binlog 存储的文件夹在后面我们查看 binlog 具体内容的时候会需要进入到这个文件夹进行查看。
2) binlog 查看
我们可以通过下面的命令查看全部的 binlog 文件在这里一个 binlog 版本就是一个文件
show binary logs;show master logs;上面这两条命令都可以显示出 binlog 文件内容显示如下 3、binlog 的类型
binlog 记录的类型有三种一种是 STATEMENT一种是 ROW一种是 MIXED。
在介绍其具体含义前我们先介绍一下如何查看和修改 binlog 类型以及查看 binlog 内容。
1. 查看 binlog 类型
我们可以通过下面的命令查看 binlog 保存的类型
SHOW VARIABLES LIKE binlog_format;输出内容如下 表示 binlog 保存的类型是 ROW
2. 修改 binlog 类型
修改 binlog 类型我们到 binlog 文件中修改这里我们修改 mysqld.cnf修改 binlog_format 的值没有这个配置项的话加一行如下
binlog_format STATEMENT保存之后重启 MySQL再次查询就可以看到这个参数有变化了 3. binlog 类型及具体含义
其含义和示例分别如下
1) STATEMENT
STATEMENT 类型记录的是执行 SQL 语句的内容比如 INSERT、UPDATE、DELETE 等语句本身。
比如
insert into users (name, email) values(张三, 12345qq.com), (李四, 345123qq.com);
update users set email 4123qq.com where id1;在 STATEMENT 类型下上面这两句 SQL 在 binlog 中记录的就是这几句 SQL 本身。
通过查询 binlog 日志比如我们下面执行的语句可以看到输出的 info 那一列有我们刚刚执行的 insert 和 update 语句
show binlog events in binlog.000017;我们也可以使用 mysqlbinlog 命令在 shell 对这个日志文件进行查看
mysqlbinlog binlog.000017就可以看到输出的信息里会包含我们前面执行的两条命令
2) ROW
ROW 类型记录的是执行 SQL 语句前后的变更会包含变更前后完整数据行的内容。
将 binlog_format 的配置改为 ROW 后重新执行几条插入或者更新数据的命令在 MySQL 客户端执行 show binlog events 命令就可以看到日志记录里有对应的记录但是这里输出的信息并不全我们还是可以使用 mysqlbinlog 来查看。
mysqlbinlog binlog.000018但是可以看到输出的内容里数据的变化都被处理过
BEGIN
/*!*/;
# at 323
#240415 1:03:05 server id 1 end_log_pos 386 CRC32 0x0e2e4dda Table_map: db_test.users mapped to number 93
# has_generated_invisible_primary_key0
# at 386
#240415 1:03:05 server id 1 end_log_pos 474 CRC32 0xcebf294e Update_rows: table id 93 flags: STMT_END_FBINLOG
SQwcZhMBAAAAPwAAAIIBAAAAAF0AAAAAAAEAB2RiX3Rlc3QABXVzZXJzAAMDDw8EPAAsAQQBAQAC
ASHaTS4O
SQwcZh8BAAAAWAAAANoBAAAAAF0AAAAAAAEAAgAD//8AAQAAAAblvKDkuIkLADQxMjNAcXEuY29t
AAEAAAAG5byg5LiJDQAxMTExMTNAcXEuY29tTim/zg
/*!*/;
# at 474
#240415 1:03:05 server id 1 end_log_pos 505 CRC32 0xd7ae1d48 Xid 55
COMMIT/*!*/;会看不到真实记录的信息我们直接使用 cat 或者 vim 命令查看文件内容可以从其中窥得一二可以看到一些我们修改或者新增的字段内容。
或者我们还是可以使用 mysqlbin但是加上 -vv 参数我们可以看到原始执行的 SQL 语句类似于下面的内容
BINLOG
2RAcZhMBAAAAPwAAANUCAAAAAF0AAAAAAAEAB2RiX3Rlc3QABXVzZXJzAAMDDw8EPAAsAQQBAQAC
ASGMO256
2RAcZh4BAAAAPwAAABQDAAAAAF0AAAAAAAEAAgAD/wADAAAABueOiS6lA4AMTIxMjM0NUBxcS5j
b22PdURn
/*!*/;
### INSERT INTO db_test.users
### SET
### 13 /* INT meta0 nullable0 is_null0 */
### 2王五 /* VARSTRING(60) meta60 nullable0 is_null0 */
### 31212345qq.com /* VARSTRING(300) meta300 nullable1 is_null0 */3) MIXED
MIXED 类型则是 STATEMENT 和 ROW 两种类型的综合它会根据具体的情况来选择 STATEMENT 或者是 ROW 其中一种类型来进行存储。
比如说涉及函数或者一些比较复杂和无法还原的操作时就会选择 ROW 格式来存储变更的数据行如果是一些比较简单的插入、更新或者删除操作那么则会选择 STATEMENT 格式来进行存储。
4. binlog 三种类型的优缺点
关于这三种 binlog 类型的优缺点总结如下
1) STATEMENT
STATEMENT 记录的 SQL 语句适用于简单 SQL 语句的场景比如单条数据的插入、更新、删除等。
优点是 binlog 日志文件相对较小而且可读性强因为可以直接查看 SQL 语句了解操作
但同时存在的问题是某些复杂操作可能会出现不一致的情况。
2) ROW
ROW 类型存储的是数据行的变更会包含变更前后的内容适用于需要精确记录数据行变更的场景。
优点是可以精确记录数据行的变更情况避免 STATEMENT 类型可能出现的不一致的问题。
缺点则是因为记录了完整的数据行内容所以可能导致文件较大而且可读性较差比如我们前面使用 -vv 参数才能了解其执行的 SQL 语句。
3) MIXED
MIXED 则会根据具体情况自动选择 STATEMENT 或 ROW 类型来记录数据更改。
优点是兼顾了 STATEMENT 和 ROW 两种类型的有点既可以节约空间也可以精确记录数据变更。
缺点的话因为是系统是自动选择类型来记录所以可能存在选择不够理想的记录方式从而导致一些不一致或者是性能问题。
4、binlog 如何恢复数据
对于 MySQL 中的操作我们有时候可能会误操作导致会意外的更新或者删除一些数据、或者是删除表、删除库等那么这些操作如何使用 binlog 来进行恢复呢以下做一个简单的介绍。
在介绍如何恢复数据前先介绍前面的 binlog 文件保存的机制。
对于 MySQL 而言一个 binlog 文件内容为从某一刻开始保存的日志记录当我们使用 sudo service mysql restart 重启操作时系统会自动为我们新建一个 binlog 日志文件重启之后的所有数据操作都会被放到新的 binlog 文件里。
除了 MySQL 的手动重启我们还可以使用 flush logs 操作来手动新建一个 binlog 日志。
下面正式开始介绍如何使用 binlog 恢复数据。
1. 恢复数据的前提
恢复数据的前提就是我们已经打开了 binlog 日志记录配置而且最好有一个备份库这个备份库的作用是用于在误操作数据后可以快速从最近的时间点进行恢复。
备份库的内容则是与当前生产库或者说是目标库落后一定时间间隔的数据来源可以是我们每天或者更小时间粒度对目标数据库的定时保存。
2. 恢复数据的步骤
有了 binlog 日志和备份数据库的基础之后比如我们误操作更新了大范围的数据或者删除了某张表下面便开始恢复数据的步骤。
1) 停止写入更新操作
如果可能的话应该尽量先停止往 MySQL 对应表的写入操作避免数据污染操作。
2) 执行 flush logs
执行 flush logs 操作重新创建一个 binlog将误操作确定在某个 binlog 中。
3) 导出 binlog 日志
确定备份库的时间点然后找到包含失误操作的 binlog导出从备份库的时间点开始一直到发生误操作的时间点前的 binlog。
将这部分 binlog 在备份库重新执行这样我们的备份库就可以恢复数据到发生误操作的前一个时间点。
如果我们没有第一步停止写入更新的操作就可以跳过误操作的 SQL将发生误操作后的 SQL 语句也导入到备份库这样备份库就拥有了不发生误操作情况下的全部数据。
4) 替换
将备份库的数据替换到目标库然后重新开启服务。
以上就是使用 binlog 进行恢复数据的大致操作具体图示见下图 尽管恢复数据在某些情况下是可行的但是我们仍然要注意尽量避免在生产中直接操作数据库而应该尽量通过业务代码来实现对数据库的操作。
5、binlog 是逻辑日志还是物理日志
介绍一下 MySQL 中关于逻辑日志与物理日志的区别。
所谓逻辑日志指的是记录数据库操作的日志比如 INSERT、UPDATE 等逻辑操作。
而物理日志则是指记录数据的各种存储细节比如 MySQL 具体某个数据页的写入和修改数据等。
所以binlog 则属于逻辑日志。
6、binlog 作用
binlog 作为 MySQL 日志的一种有很多作用比如前面介绍的数据备份与恢复还可以用于数据同步比如主从复制模式下将主库的更改操作操作就是通过 binlog 的方式来同步到从库的。
当然通过 binlog 本身我们可以还原所有的数据操作我们也可以针对这部分进行对应的数据统计和分析等。 7、开启binlog日志记录
修改MySQL配置文件linux中配置文件为my.confwindow下为my.ini下面以centos为例演示
编辑配置文件
# 在centos中mysql的配置文件一般都在/etc/mysql目录下如果不在可以通过 find / -name my.cnf 查找
vi /etc/mysql/my.cnf
添加配置放到[mysqld]下面
# 服务ID
server-id1
# binlog 配置 只要配置了log_bin地址 就会开启
log_bin /var/lib/mysql/mysql_bin
# 日志存储天数 默认0 永久保存
# 如果数据库会定期归档建议设置一个存储时间不需要一直存储binlog日志理论上只需要存储归档之后的日志
expire_logs_days 30
# binlog最大值
max_binlog_size 1024M
# 规定binlog的格式binlog有三种格式statement、row、mixad默认使用statement建议使用row格式
binlog_format ROW
# 在提交n次事务后进行binlog的落盘0为不进行强行的刷新操作而是由文件系统控制刷新日志文件如果是在线交易和账有关的数据建议设置成1如果是其他数据可以保持为0即可
sync_binlog 1
重启MySQL服务使配置生效
systemctl restart mysqld
登录MySQL查看配置是否生效
mysql SHOW VARIABLES LIKE %log_bin%; log_bin为ON代表MySQL已经开启binlog日志记录 log_bin_basename配置了binlog的文件路径及文件前缀名 log_bin_index配置了binlog索引文件的路径
查看日志列表
mysql SHOW MASTER LOGS; 根据log_bin_basename的路径查看binlog具体文件
ls -l /var/lib/mysql/mysql_bin.*
