南康网站建设,徐州抖音推广公司,wordpress搜索结果模板,做米业的企业网站提示#xff1a;MySQL 中的日志比较重要的有 binlog#xff08;归档日志#xff09;、redo log#xff08;重做日志#xff09;以及 undo log#xff0c;那么跟我们本文相关的主要是 binlog#xff0c;另外两个日志松哥将来有空了再和大家详细介绍。 文章目录 1、二进制… 提示MySQL 中的日志比较重要的有 binlog归档日志、redo log重做日志以及 undo log那么跟我们本文相关的主要是 binlog另外两个日志松哥将来有空了再和大家详细介绍。 文章目录 1、二进制日志binlong的作用1、1 灾难时恢复数据1、2 主从复制 2、查看binlog日志文件2、binlong日志的三种格式2.1.1 Statement2.2.2 Row2.2.3 Mixed 3、配置binlong3、1MySQL配置文件my.cnf中配置binlong参数3、2 二进制日志binlog查看工具mysqlbinlog 4、测试mysqlbinlog查看二进制文件4、1 更新demp表数据4、2查看二进制文件记录更新 5、二进制日志binlog清理手动清理命令式5、1 reset master命令5、2 purage master logs to命令 自动设置清理 1、二进制日志binlong的作用
mysql中二进制日志记录了数据库的DDL定义操作和DDM操作语言
1、1 灾难时恢复数据
1、2 主从复制
2、查看binlog日志文件
命令 mysql show variables like ‘log_bin%’; ±--------------------------------±----------------------------------- | Variable_name | Value | ±--------------------------------±----------------------------------- | log_bin | ON | | log_bin_basename | /data/log-bin/mysql-bin-3306 | | log_bin_index | /data/log-bin/mysql-bin-3306.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | ±--------------------------------±----------------------------------- log_bin_basename 二进制日志所在位置 log_bin_index索引 查看binlong具体信息
[rootmysql8 ~]# cd /data/log-bin
[rootmysql8 log-bin]# ll
total 28
-rw-r--r-- 1 root root 0 Jul 29 03:53 1.sql
-rw-r----- 1 mysql mysql 179 Jul 28 21:09 mysql-bin-3306.000013
-rw-r----- 1 mysql mysql 179 Jul 29 00:26 mysql-bin-3306.000014
-rw-r----- 1 mysql mysql 179 Jul 29 00:46 mysql-bin-3306.000015
-rw-r----- 1 mysql mysql 179 Jul 29 00:46 mysql-bin-3306.000016
-rw-r----- 1 mysql mysql 179 Jul 29 00:46 mysql-bin-3306.000017
-rw-r----- 1 mysql mysql 1387 Jul 29 04:22 mysql-bin-3306.000018
-rw-r----- 1 mysql mysql 216 Jul 29 00:48 mysql-bin-3306.indexmysql-bin-3306为前缀的都是二进制文件记录着数据库操作。
2、binlong日志的三种格式
MySQL 中的日志比较重要的有 binlog归档日志、redo log重做日志以及 undo log
使用场景 MySQL 主从复制时在主机上开启 binlog主机将 binlog 同步给从机从机通 过 binlog 来同步数据进而实现主机和从机的数据同步。
MySQL 数据恢复通过使用 mysqlbinlog 工具再结合 binlog 文件可以将数据恢复到过去的某一时刻。
binlog 有三种格式
StatementStatement-Based Replication,SBR每一条会修改数据的 SQL 都会记录在 binlog 中。
RowRow-Based Replication,RBR不记录 SQL 语句上下文信息仅保存哪条记录被修改。
MixedMixed-Based Replication,MBRStatement 和 Row 的混合体。
2.1.1 Statement
Statement 模式只记录执行的 SQL不需要记录每一行数据的变化因此极大的减少了 binlog 的日志量避免了大量的 IO 操作提升了系统的性能。
但是正是由于 Statement 模式只记录 SQL而如果一些 SQL 中 包含了函数那么可能会出现执行结果不一致的情况。比如说 uuid() 函数每次执行的时候都会生成一个随机字符串在 master 中记录了 uuid当同步到 slave 之后再次执行就得到另外一个结果了。
所以使用 Statement 格式会出现一些数据一致性问题。
2.2.2 Row
从 MySQL5.1.5 版本开始binlog 引入了 Row 格式Row 格式不记录 SQL 语句上下文相关信息仅仅只需要记录某一条记录被修改成什么样子了。
Row 格式的日志内容会非常清楚地记录下每一行数据修改的细节这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。
不过 Row 格式也有一个很大的问题那就是日志量太大了特别是批量 update、整表 delete、alter 表等操作由于要记录每一行数据的变化此时会产生大量的日志大量的日志也会带来 IO 性能问题。
2.2.3 Mixed
从 MySQL5.1.8 版开始MySQL 又推出了 Mixed 格式这种格式实际上就是 Statement 与 Row 的结合。
在 Mixed 模式下系统会自动判断 该 用 Statement 还是 Row一般的语句修改使用 Statement 格式保存 binlog对于一些 Statement 无法准确完成主从复制的操作则采用 Row 格式保存 binlog。
Mixed 模式中MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式也就是在 Statement 和 Row 之间选择一种。
3、配置binlong
3、1MySQL配置文件my.cnf中配置binlong参数
# 这个参数表示启用 binlog 功能并指定 binlog 的存储目录
log-binmysqlbin
# 配置二进制文件存放的位置
log-bin /data/log-bin/mysql-bin-3306# 二进制日志格式的配置 STATEMENT Mixed row等配置binlog_formatSTATEMENT# 设置一个 binlog 文件的最大字节
# 设置最大 100MB
max_binlog_size104857600# 设置了 binlog 文件的有效期单位天
expire_logs_days 7# binlog 日志只记录指定库的更新配置主从复制的时候会用到
#binlog-do-dbjavaboy_db# binlog 日志不记录指定库的更新配置主从复制的时候会用到
#binlog-ignore-dbjavaboy_no_db# 写缓存多少次刷一次磁盘默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
# 1 表示每一条事务提交都会立即写磁盘n 则表示 n 个事务提交才会写磁盘
sync_binlog0# 为当前服务取一个唯一的 idMySQL5.7 之后需要配置
server-id1配置文件配置结束后重启mysql服务即可生效service mysqld start
3、2 二进制日志binlog查看工具mysqlbinlog
[rootmysql8 log-bin]# cd /data/log-bin/
[rootmysql8 log-bin]# ll
total 28
-rw-r--r-- 1 root root 0 Jul 29 03:53 1.sql
-rw-r----- 1 mysql mysql 179 Jul 28 21:09 mysql-bin-3306.000013
-rw-r----- 1 mysql mysql 179 Jul 29 00:26 mysql-bin-3306.000014
-rw-r----- 1 mysql mysql 179 Jul 29 00:46 mysql-bin-3306.000015
-rw-r----- 1 mysql mysql 179 Jul 29 00:46 mysql-bin-3306.000016
-rw-r----- 1 mysql mysql 179 Jul 29 00:46 mysql-bin-3306.000017
-rw-r----- 1 mysql mysql 1387 Jul 29 04:22 mysql-bin-3306.000018
-rw-r----- 1 mysql mysql 216 Jul 29 00:48 mysql-bin-3306.index
查看binlog二进制日志文件mysqlbinlog 日志文件
[rootmysql8 log-bin]# mysqlbinlog mysql-bin-3306.000014
mysqlbinlog: [ERROR] unknown variable default_character-setutf8mb4.
有报错建议修改配置文件中的字符集即可可有利用第二中方式轻松查看 用mysqlbinlog --no-defaults mysql-bin.3306.000018命令打开
4、测试mysqlbinlog查看二进制文件
4、1 更新demp表数据
更新demp表中所有id
mysql select * from demp;
--------------------------------------------------------------------------------------------------------------------------------------------------
| employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 163 | Danielle | Greene | DGREENE | 011.44.1346.229268 | 1999-03-19 00:00:00 | SA_REP | 9500 | 0.15 | 147 | 80 |
| 164 | Mattea | Marvins | MMARVINS | 011.44.1346.329268 | 2000-01-24 00:00:00 | SA_REP | 7200 | 0.10 | 147 | 80 |
| 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2000-03-24 00:00:00 | SA_REP | 6400 | 0.10 | 147 | 80 |
| 167 | Amit | Banda | ABANDA | 011.44.1346.729268 | 2000-04-21 00:00:00 | SA_REP | 6200 | 0.10 | 147 | 80 |
| 168 | Lisa | Ozer | LOZER | 011.44.1343.929268 | 1997-03-11 00:00:00 | SA_REP | 11500 | 0.25 | 148 | 80 |
| 169 | Harrison | Bloom | HBLOOM | 011.44.1343.829268 | 1998-03-23 00:00:00 | SA_REP | 10000 | 0.20 | 148 | 80 |
| 170 | Tayler | Fox | TFOX | 011.44.1343.729268 | 1998-01-24 00:00:00 | SA_REP | 9600 | 0.20 | 148 | 80 |
| 171 | William | Smith | WSMITH | 011.44.1343.629268 | 1999-02-23 00:00:00 | SA_REP | 7400 | 0.15 | 148 | 80 |
| 172 | Elizabeth | Bates | EBATES | 011.44.1343.529268 | 1999-03-24 00:00:00 | SA_REP | 7300 | 0.15 | 148 | 80 |
--------------------------------------------------------------------------------------------------------------------------------------------------
9 rows in set (0.00 sec)mysql update demp set employee_id100;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9 Changed: 9 Warnings: 0mysql commit;
Query OK, 0 rows affected (0.00 sec)mysql select * from demp;
--------------------------------------------------------------------------------------------------------------------------------------------------
| employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 100 | Danielle | Greene | DGREENE | 011.44.1346.229268 | 1999-03-19 00:00:00 | SA_REP | 9500 | 0.15 | 147 | 80 |
| 100 | Mattea | Marvins | MMARVINS | 011.44.1346.329268 | 2000-01-24 00:00:00 | SA_REP | 7200 | 0.10 | 147 | 80 |
| 100 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2000-03-24 00:00:00 | SA_REP | 6400 | 0.10 | 147 | 80 |
| 100 | Amit | Banda | ABANDA | 011.44.1346.729268 | 2000-04-21 00:00:00 | SA_REP | 6200 | 0.10 | 147 | 80 |
| 100 | Lisa | Ozer | LOZER | 011.44.1343.929268 | 1997-03-11 00:00:00 | SA_REP | 11500 | 0.25 | 148 | 80 |
| 100 | Harrison | Bloom | HBLOOM | 011.44.1343.829268 | 1998-03-23 00:00:00 | SA_REP | 10000 | 0.20 | 148 | 80 |
| 100 | Tayler | Fox | TFOX | 011.44.1343.729268 | 1998-01-24 00:00:00 | SA_REP | 9600 | 0.20 | 148 | 80 |
| 100 | William | Smith | WSMITH | 011.44.1343.629268 | 1999-02-23 00:00:00 | SA_REP | 7400 | 0.15 | 148 | 80 |
| 100 | Elizabeth | Bates | EBATES | 011.44.1343.529268 | 1999-03-24 00:00:00 | SA_REP | 7300 | 0.15 | 148 | 80 |
--------------------------------------------------------------------------------------------------------------------------------------------------
9 rows in set (0.00 sec)
4、2查看二进制文件记录更新
total 28 -rw-r–r-- 1 root root 0 Jul 29 03:53 1.sql -rw-r----- 1 mysql mysql 179 Jul 28 21:09 mysql-bin-3306.000013 -rw-r----- 1 mysql mysql 179 Jul 29 00:26 mysql-bin-3306.000014 -rw-r----- 1 mysql mysql 179 Jul 29 00:46 mysql-bin-3306.000015 -rw-r----- 1 mysql mysql 179 Jul 29 00:46 mysql-bin-3306.000016 -rw-r----- 1 mysql mysql 179 Jul 29 00:46 mysql-bin-3306.000017 -rw-r----- 1 mysql mysql 1697 Jul 30 07:29 mysql-bin-3306.000018 -rw-r----- 1 mysql mysql 216 Jul 29 00:48 mysql-bin-3306.index
根据时间显示和追加记录mysql-bin-3306.000018中记录了最新的数据库操作信息利用mysqlbinlog工具进行查看
[rootmysql8 log-bin]# mysqlbinlog --no-defaults mysql-bin-3306.000018
文件内容显示如下 [rootmysql8 log-bin]# mysqlbinlog --no-defaults mysql-bin-3306.000018
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;
/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;
DELIMITER /*!*/;
# at 4
#230729 0:48:15 server id 1 end_log_pos 125 CRC32 0x30ea3950 Start: binlog v 4, server v 8.0.24 created 230729 0:48:15 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG
T/HDZA8BAAAAeQAAAH0AAAABAAQAOC4wLjI0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABP8cNkEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBUDnqMA
/*!*/;
# at 125
#230729 0:48:15 server id 1 end_log_pos 156 CRC32 0xfc28efee Previous-GTIDs
# [empty]
# at 156
#230729 4:13:48 server id 1 end_log_pos 235 CRC32 0x52abd1d5 Anonymous_GTID last_committed0 sequence_number1 rbr_onlyno original_committed_timestamp1690575228721727 immediate_commit_timestamp1690575228721727 transaction_length299
# original_commit_timestamp1690575228721727 (2023-07-29 04:13:48.721727 CST)
# immediate_commit_timestamp1690575228721727 (2023-07-29 04:13:48.721727 CST)
/*!80001 SET session.original_commit_timestamp1690575228721727*//*!*/;
/*!80014 SET session.original_server_version80024*//*!*/;
/*!80014 SET session.immediate_server_version80024*//*!*/;
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 235
#230729 4:13:36 server id 1 end_log_pos 313 CRC32 0x5ce7bb97 Query thread_id9 exec_time0 error_code0
SET TIMESTAMP1690575216/*!*/;
SET session.pseudo_thread_id9/*!*/;
SET session.foreign_key_checks1, session.sql_auto_is_null0, session.unique_checks1, session.autocommit1/*!*/;
SET session.sql_mode1168113696/*!*/;
SET session.auto_increment_increment1, session.auto_increment_offset1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET session.character_set_client255,session.collation_connection255,session.collation_server45/*!*/;
SET session.lc_time_names0/*!*/;
SET session.collation_databaseDEFAULT/*!*/;
/*!80011 SET session.default_collation_for_utf8mb4255*//*!*/;
BEGIN
/*!*/;
# at 313
#230729 4:13:36 server id 1 end_log_pos 424 CRC32 0x4c694ced Query thread_id9 exec_time0 error_code0
use hr/*!*/;
SET TIMESTAMP1690575216/*!*/;
delete from demp where employee_id165
/*!*/;
# at 424
#230729 4:13:48 server id 1 end_log_pos 455 CRC32 0xbf81adbc Xid 6
COMMIT/*!*/;
# at 455
#230729 4:17:44 server id 1 end_log_pos 534 CRC32 0xde2c6e07 Anonymous_GTID last_committed1 sequence_number2 rbr_onlyno original_committed_timestamp1690575464562403 immediate_commit_timestamp1690575464562403 transaction_length631
# original_commit_timestamp1690575464562403 (2023-07-29 04:17:44.562403 CST)
# immediate_commit_timestamp1690575464562403 (2023-07-29 04:17:44.562403 CST)
/*!80001 SET session.original_commit_timestamp1690575464562403*//*!*/;
/*!80014 SET session.original_server_version80024*//*!*/;
/*!80014 SET session.immediate_server_version80024*//*!*/;
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 534
#230729 4:17:02 server id 1 end_log_pos 612 CRC32 0xb3c5f7ed Query thread_id9 exec_time0 error_code0
SET TIMESTAMP1690575422/*!*/;
BEGIN
/*!*/;
# at 612
#230729 4:17:02 server id 1 end_log_pos 722 CRC32 0x883aae6d Query thread_id9 exec_time0 error_code0
SET TIMESTAMP1690575422/*!*/;
delete from demp where employee_id18
/*!*/;
# at 722
#230729 4:17:08 server id 1 end_log_pos 833 CRC32 0x22d36194 Query thread_id9 exec_time0 error_code0
SET TIMESTAMP1690575428/*!*/;
delete from demp where employee_id180
/*!*/;
# at 833
#230729 4:17:17 server id 1 end_log_pos 944 CRC32 0xb75c1ace Query thread_id9 exec_time0 error_code0
SET TIMESTAMP1690575437/*!*/;
delete from demp where employee_id178
/*!*/;
# at 944
#230729 4:17:22 server id 1 end_log_pos 1055 CRC32 0x23c4fb31 Query thread_id9 exec_time0 error_code0
SET TIMESTAMP1690575442/*!*/;
delete from demp where employee_id177
/*!*/;
# at 1055
#230729 4:17:44 server id 1 end_log_pos 1086 CRC32 0x0d1db327 Xid 25
COMMIT/*!*/;
# at 1086
#230729 4:22:49 server id 1 end_log_pos 1165 CRC32 0xb420e324 Anonymous_GTID last_committed2 sequence_number3 rbr_onlyno original_committed_timestamp1690575769602803 immediate_commit_timestamp1690575769602803 transaction_length301
# original_commit_timestamp1690575769602803 (2023-07-29 04:22:49.602803 CST)
# immediate_commit_timestamp1690575769602803 (2023-07-29 04:22:49.602803 CST)
/*!80001 SET session.original_commit_timestamp1690575769602803*//*!*/;
/*!80014 SET session.original_server_version80024*//*!*/;
/*!80014 SET session.immediate_server_version80024*//*!*/;
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 1165
#230729 4:22:27 server id 1 end_log_pos 1243 CRC32 0x2f5d2059 Query thread_id9 exec_time0 error_code0
SET TIMESTAMP1690575747/*!*/;
BEGIN
/*!*/;
# at 1243
#230729 4:22:27 server id 1 end_log_pos 1356 CRC32 0x4a403e8b Query thread_id9 exec_time0 error_code0
SET TIMESTAMP1690575747/*!*/;
delete from tb_cs t where t.order_id5
/*!*/;
# at 1356
#230729 4:22:49 server id 1 end_log_pos 1387 CRC32 0x30b73bb0 Xid 35
COMMIT/*!*/;
# at 1387
#230730 7:29:44 server id 1 end_log_pos 1466 CRC32 0xe43139ec Anonymous_GTID last_committed3 sequence_number4 rbr_onlyno original_committed_timestamp1690673384343676 immediate_commit_timestamp1690673384343676 transaction_length310
# original_commit_timestamp1690673384343676 (2023-07-30 07:29:44.343676 CST)
# immediate_commit_timestamp1690673384343676 (2023-07-30 07:29:44.343676 CST)
/*!80001 SET session.original_commit_timestamp1690673384343676*//*!*/;
/*!80014 SET session.original_server_version80024*//*!*/;
/*!80014 SET session.immediate_server_version80024*//*!*/;
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 1466
#230730 7:29:39 server id 1 end_log_pos 1553 CRC32 0xfa85f183 Query thread_id11 exec_time0 error_code0
SET TIMESTAMP1690673379/*!*/;
BEGIN
/*!*/;
# at 1553
#230730 7:29:39 server id 1 end_log_pos 1666 CRC32 0xb0b99617 Query thread_id11 exec_time0 error_code0
SET TIMESTAMP1690673379/*!*/;
update demp set employee_id100
/*!*/;
# at 1666
#230730 7:29:44 server id 1 end_log_pos 1697 CRC32 0xe18478e3 Xid 47
COMMIT/*!*/;
SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;
里面具体描述了表的操作信息和具体时间以及sql语言二进制日志具体信息的获取要根据mysql配置文件中对二进制日志binlog格式的配置。
5、二进制日志binlog清理
手动清理命令式
5、1 reset master命令
reset master可以删除所有的日志文件重置二进制日志文件的索引文件为空然后产生新的二进制文件编号还是从000001开始以及索引文件
mysql reset master - ; Query OK, 0 rows affected (0.00 sec)
mysql commit; Query OK, 0 rows affected (0.00 sec)
5、2 purage master logs to命令
purage master logs to 可以删除指定二进制日志文件名之前的所有二进制日志文件
自动设置清理
在my.ini配置文件的【mysqlId】选项组中设置expire_logs_days参数 expire_logs_days参数定义了二进制日志文件的过期天数过期的二进制日志文件将被自动删除 expire_logs_days7
