做矿产公司的网站,长沙 网页制作教程,网站页面架构怎么写,公司网站建设的目的4.6#xff09;日志管理
MySQL 支持丰富的日志类型#xff0c;如下#xff1a;
事务日志#xff1a;transaction log
事务日志的写入类型为 追加#xff0c;因此其操作为 顺序IO#xff1b;
通常也被称为#xff1a;预写式日志 write ahead…4.6日志管理
MySQL 支持丰富的日志类型如下
事务日志transaction log
事务日志的写入类型为 追加因此其操作为 顺序IO
通常也被称为预写式日志 write ahead logging
事务日志文件 ib_logfile0 ib_logfile1
错误日志error log
通用日志general log
慢查询日志slow query log
二进制日志binary log
中继日志reley log在主从复制架构中从服务器用于保存从主服务器的二进制日志中读取的事件 4.6.1事务日志
事务日志transaction log
redo log实现 WAL Write Ahead Log 写前日志 )数据更新前先记录 redo log。
undo log保存与执行的操作相反的操作用于实现 rollback 撤销操作。
事务型存储引擎自行管理和使用建议和数据文件分开存放
参考
什么是 WAL - 知乎
MySQL 日志undo log、redo log、binlog 有什么用 | 小林coding
WALWrite Ahead Log预写日志是数据库系统中常见的一种手段用于保证 数据操作的原子性和持久性。在计算机科学中「预写式日志」 Write-ahead logging缩写 WAL 是关系数据库系统中用于提供原子性和持久性ACID 属性中的两个的一系列技术。在使用 WAL 的系统中所有的修改在提交之前都要先写入 log 文件中。
redo log重做日志是 Innodb 存储引擎层生成的日志实现了事务中的 持久性主要用于掉电等故障恢复 记录即将执行的操作
undo log回滚日志是 Innodb 存储引擎层生成的日志实现了事务中的 原子性主要用于 ROLLBACK 事务回滚和 MVCC。 记录即将执行操作的相反操作用于实现事务回滚
Innodb 事务日志相关配置
// 列出与 Innodb 事务日志相关配置MariaDB [hellodb] show variables like %innodb_log%;innodb_log_file_size 50331648 // 每个事务日志文件的大小 ( 建议生产环境把该文件大小配置的更大一些 )innodb_log_files_in_group 2 // 事务日志文件的个数innodb_log_group_home_dir ./ // 事务日志文件的路径 ( 默认 MySQL 安装路径: /usr/lib/mysql ) 事务日志性能优化
生产案例mysql服务器io飚满百分之百的案例分析 - lmcc-老马吃草的博客
默认 MySQL 的刷盘策略 是 1最安全的但是安全的同时自然也就会带来一定的性能压力。
在写压力巨大的情况下根据具体的业务场景牺牲安全性的将其调为 0 或 2。
1此为默认值日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。这是完全遵守 ACID 特性。 每完成一次事务就会写入一次磁盘比较占用磁盘 IO
因此假如 1 秒完成了 100 个事务。就会有 100 次写硬盘的操作。 0提交时没有写磁盘的操作而是 每秒执行一次 将 MySQL 日志缓冲区 的提交事务写入刷新到磁盘。这样可提供更好的性能但 MySQL 应用或服务器崩溃可能丢失最后一秒的事务。 事务完成时没有写磁盘的操作而是每秒写入一次磁盘
因此假如 1 秒完成 100 个事务该值只会写入 1 次磁盘操作。 2每次提交后都会写入 OS 操作系统的缓冲区也是每秒才会进行一次刷新到磁盘文件中。性能比 0 略差一些只有操作系统或停电可能导致最后一秒的事务丢失。
效率和 0 几乎一样差别不大。因为都是每 1 秒将缓冲区的数据写入刷新到磁盘。 0 与 2 的区别
0先将事务日志缓存在 MySQL 的 Log_buffer 缓存区 中在一秒后将其写入磁盘。
2先将事务日志缓存在 OS 操作系统的缓存区 中在一秒后将其写入磁盘。
因此
2 的安全性胜过 0 因为 2 将缓存存放在 OS 操作系统的缓存区中
而 0 的性能胜过 2 而 0 将缓存存放在 MySQL 的缓冲区中
执行效率差别不大。
innodb_flush_log_at_trx_commit0|1|2// 默认 MySQL 的刷盘策略: 1MariaDB [(none)] select innodb_flush_log_at_trx_commit;----------------------------------| innodb_flush_log_at_trx_commit |----------------------------------| 1 |----------------------------------1 row in set (0.00 sec) 实例事务日志性能优化
注生产环境需结合需要进行优化。
这个案例展示了如何通过调整 InnoDB 的日志刷写设置来 巧妙地影响事务提交的速度。通过将 innodb_flush_log_at_trx_commit 设置为 2我们可以显著提升事务提交的速度但与此同时这也意味着增加了数据丢失的风险特别是在数据库遭遇崩溃的情况下。在部署和优化数据库时务必根据实际需求仔细权衡性能与数据安全性的关系以确保最佳的实践效果。
// 清空名为 testlog 的表MariaDB [hellodb] truncate table testlog;Query OK, 0 rows affected (0.00 sec)// 查询 testlog 表中的记录数MariaDB [hellodb] select count(*) from testlog;----------| count(*) |----------| 0 |----------1 row in set (0.00 sec)// 查询当前 InnoDB 存储引擎的日志刷写设置MariaDB [hellodb] select innodb_flush_log_at_trx_commit;----------------------------------| innodb_flush_log_at_trx_commit |----------------------------------| 1 |----------------------------------1 row in set (0.00 sec)// 将 InnoDB 的日志刷写设置为 2// 这意味着日志会在每个事务提交时写入到日志缓冲, 但不会立即刷新到磁盘// 这可以提高事务的提交速度MariaDB [hellodb] set global innodb_flush_log_at_trx_commit2;Query OK, 0 rows affected (0.00 sec)// 验证MariaDB [hellodb] select innodb_flush_log_at_trx_commit;----------------------------------| innodb_flush_log_at_trx_commit |----------------------------------| 2 |----------------------------------1 row in set (0.00 sec)// 调用存储过程 sp_testlog// 执行速度得到了大幅度提升// 这是因为之前设置的事务日志刷写策略提高了事务提交的速度MariaDB [hellodb] call sp_testlog;Query OK, 1 row affected (0.65 sec) // 速度大幅度提升// 再次查询 testlog 表中的记录数MariaDB [hellodb] select count(*) from testlog;----------| count(*) |----------| 100000 |----------1 row in set (0.01 sec) 高并发业务行业最佳实践是使用第三种折衷配置2 1. 配置为 2 和配置为 0, 性能差异并不大, 因为将数据从 Log Buffer 拷贝到 OS cache, 虽然跨越用户态与内核态, 但毕竟只是内存的数据拷贝, 速度很快. 2. 配置为 2 和配置为 0, 安全性差异巨大, 操作系统崩溃的概率相比 MySQL 应用程序崩溃的概率, 小很多, 设置为2, 只要操作系统不奔溃, 也绝对不会丢数据. 说明
设置为 1同时 sync_binlog 1 表示最高级别的容错
innodb_use_global_flush_log_at_trx_commit0 时将不能用 SET 语句重置此变量 MariaDB 10.2.6 后废弃 4.6.2错误日志
错误日志
mysqld 启动和关闭过程中输出的事件信息
mysqld 运行中产生的错误信息
event scheduler 运行一个 event 时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
// 查看错误日志tail /var/log/mariadb/mariadb.log 错误文件路径
SHOW GLOBAL VARIABLES LIKE log_error; 配置 错误日志 路径
vim /etc/my.cnflog-error/var/log/mariadb/mariadb.log 查看 错误日志
cat /var/log/mariadb/mariadb.log | grep -i ERR 记录哪些警告信息至错误日志文件
// CentOS7 mariadb 5.5 默认值为 1// CentOS8 mariadb 10.3 默认值为 2log_warnings0|1|2|3... // MySQL5.7 之前log_error_verbosity0|1|2|3... // MySQL8.0 范例
MariaDB [hellodb] SHOW GLOBAL VARIABLES LIKE log_warnings;----------------------| Variable_name | Value |----------------------| log_warnings | 2 |----------------------1 row in set (0.001 sec) 范例MySQL8.0 变量变化
mysql SHOW GLOBAL VARIABLES LIKE log_error_verbosity;----------------------------| Variable_name | Value |----------------------------| log_error_verbosity | 2 |----------------------------1 row in set (0.00 sec) 4.6.3通用日志
通用日志记录用户对数据库的通用操作包括错误的 SQL 语句
通用日志可以保存在file默认值或 table mysql.general_log 表
通用日志相关设置
general_logON|OFFgeneral_log_fileHOSTNAME.loglog_outputTABLE|FILE|NONE 通用日志 可以用来观察数据库发生的一些事件。
范例启用通用日志记录并记录日志在文件中
// 默认没有启用通用日志MariaDB [(none)] select general_log;---------------| general_log |---------------| 0 |---------------1 row in set (0.00 sec)// 默认没有启用通用日志 ( 等价于上一条命令 )MariaDB [(none)] show variables like general_log;----------------------| Variable_name | Value |----------------------| general_log | OFF |----------------------1 row in set (0.00 sec)// 启用通用日志功能MariaDB [(none)] set global general_log1;Query OK, 0 rows affected (0.00 sec)// 验证MariaDB [(none)] select general_log;---------------| general_log |---------------| 1 |---------------1 row in set (0.00 sec)// 默认通用日志存放在 FILE 文件中 ( 由 log_output 变量定义 )MariaDB [(none)] SHOW GLOBAL VARIABLES LIKE log_output;----------------------| Variable_name | Value |----------------------| log_output | FILE |----------------------1 row in set (0.00 sec)// 通用日志存放的文件路径 ( 开启通用日志功能后,自动生成如下文件 )MariaDB [(none)] select general_log_file;--------------------| general_log_file |--------------------| blog.log |--------------------1 row in set (0.00 sec)// 会记录用户对数据库的通用操作// 包括: 错误的 SQL 语句也会记录 ( 追溯用户执行的操作 )// 类似于: .bash_history 文件tail -f /var/lib/mysql/blog.log 范例通用日志记录到数据表中
注意我们如下案例中使用 set 修改变量值都仅是临时修改。
如果需要永久修改 需要写到 MySQL 配置文件 中。
// 永久修改vim /etc/my.cnflog_outputTABLE// 重启 Mariadb 服务systemctl restart mariadb.service// 修改通用日志// 将记录通用日志至 mysql.general_log 表中MariaDB [mysql] set global log_outputtable;MariaDB [mysql] SHOW GLOBAL VARIABLES LIKE log_output;----------------------| Variable_name | Value |----------------------| log_output | TABLE |----------------------1 row in set (0.00 sec)// 进入 mysql 数据库MariaDB [(none)] use mysql;MariaDB [mysql] show tables;---------------------------| Tables_in_mysql |---------------------------| columns_priv || db || event || func || general_log | // 会记录在该 general_log 表中| help_category || help_keyword || help_relation || help_topic || host || ndb_binlog_index || plugin || proc || procs_priv || proxies_priv || servers || slow_log || tables_priv || time_zone || time_zone_leap_second || time_zone_name || time_zone_transition || time_zone_transition_type || user |---------------------------24 rows in set (0.00 sec)// 查看数据表内容MariaDB [mysql] select * from general_log;Empty set (0.00 sec)// 数据表内容实则存放在该文件中[rootcentos8 ~] cat /var/lib/mysql/mysql/general_log.CSV 范例查找执行次数最多的前三条语句
MariaDB [mysql] select argument,count(argument) num from mysql.general_log group by argument order by num desc limit 3;--------------------------------------------| argument | num |--------------------------------------------| select * from teachers | 6 || select * from general_log | 4 || select * from students | 3 |--------------------------------------------3 rows in set (0.002 sec) 范例对访问的语句进行排序
[rootcentos8 ~] mysql -e select argument from mysql.general_log | awk {sql[$0]}END{for(i in sql){print sql[i],i}} | sort -nr[rootcentos8 ~] mysql -e select argument from mysql.general_log | sort | uniq -c | sort -nr// 对日志文件进行排序[rootcentos8 ~] cat /var/lib/mysql/blog.log | sort | uniq -c | sort -nr[rootcentos8 ~] cat /var/lib/mysql/blog.log | awk {sql[$0]}END{for(i in sql){print sql[i],i}} | sort -nr 4.6.4慢查询日志
慢查询日志记录执行查询时长超出指定时长的操作
慢查询日志 记录了执行时间较长的 SQL 语句我们可以通过分析慢查询日志找出执行时间较长的 SQL 语句和查询的问题所在从而优化数据库的性能。
慢查询相关变量
slow_query_logON|OFF // 开启或关闭慢查询 支持全局和会话, 只有全局设置才会生成慢查询文件long_query_timeN // 慢查询的阀值 单位秒, 默认为 10sslow_query_log_fileHOSTNAME-slow.log // 慢查询日志文件 // 慢查询相关行为log_slow_filter admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk // 上述查询类型且查询时长超过 long_query_time, 则记录日志// 记录没有使用索引的查询语句,即使你未到达指定阈值 ( 建议开启 )log_queries_not_using_indexesON // 不使用索引或使用全索引扫描, 不论是否达到慢查询阀值的语句是否记录日志, 默认 OFF, 即不记录 --- 了解 --log_slow_rate_limit 1 // 多少次查询才记录, mariadb 特有log_slow_verbosity Query_plan,explain // 记录内容log_slow_queries OFF // 同 slow_query_log, MariaDB 10.0/MySQL 5.6.1 版后已删除 范例 MySQL 小技巧慢查询日志
慢查询日志用于记录 SQL 执行时间超过某个临界值的 SQL 日志文件可用于快速定位慢查询为我们的 SQL优化 做参考。
// 我们可以这样进行永久配置vim /etc/my.cnf.d/mysql-server.cnfslow_query_logON // 开启慢查询功能long_query_time1 // 将慢查询的阀值设置为一秒log_queries_not_using_indexesON // 记录未使用索引的行为,即使你未到达指定阈值 ( 建议开启 )// 重启服务systemctl restart mysqld// 默认没有开启慢查询日志功能MariaDB [mysql] select slow_query_log;------------------| slow_query_log |------------------| 0 |------------------1 row in set (0.00 sec)// 开启慢查询日志功能MariaDB [mysql] set GLOBAL slow_query_log1;Query OK, 0 rows affected (0.00 sec)// 查看慢查询日志默认阀值 ( 生成环境建议设置更小一些 )// 当然,结合你实际环境进行配置// 运行时间超过如下阀值的 SQL 语句将会被记录MariaDB [mysql] select long_query_time;-------------------| long_query_time |-------------------| 10.000000 |-------------------1 row in set (0.00 sec)// 将超时阀值设置为 1 秒MariaDB [mysql] set long_query_time1;Query OK, 0 rows affected (0.00 sec)// 慢查询日志文件MariaDB [mysql] select slow_query_log_file;-----------------------| slow_query_log_file |-----------------------| blog-slow.log |-----------------------1 row in set (0.00 sec)// 测试 ( 超过阈值 )MariaDB [(none)] select sleep(1);----------| sleep(1) |----------| 0 |----------1 row in set (1.00 sec)// 经过如上配置// 目前该慢查询日志会记录运行时间超过规定阀值的记录tail -f /var/lib/mysql/centos8-slow.log // 测试 ( 记录没有使用索引的查询语句,即使你未到达指定阈值 )mysql select * from students where age20;------------------------------------------------------| StuID | Name | Age | Gender | ClassID | TeacherID |------------------------------------------------------| 9 | Ren Yingying | 20 | F | 6 | NULL || 22 | Xiao Qiao | 20 | F | 1 | NULL |------------------------------------------------------2 rows in set (0.00 sec)// 经过如上配置// 目前该慢查询日志会记录未使用索引的查询语句tail -f /var/lib/mysql/centos8-slow.log 范例慢查询分析工具 mysqldumpslow
[01:05:24 rootblog ~]# mysqldumpslow --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ][01:05:24 rootblog ~]# mysqldumpslow -s c -t 2 /var/lib/mysql/blog-slow.logReading mysql slow query log from /var/lib/mysql/blog-slow.logCount: 1 Time0.00s (0s) Lock0.00s (0s) Rows_sent0.0 (0), Rows_examined0.0 (0), 0users0hostsDied at /usr/bin/mysqldumpslow line 178, chunk 1. 4.6.5二进制日志 备份功能
二进制日志 主要作用
为了确保数据库能够最大程度地更新需要恢复并启用二进制日志。
这是因为二进制日志记录了备份后所进行的所有更新并且还用于在主复制服务器上记录所有将发送给从服务器的语句。 记录着 导致数据改变 或 潜在导致数据改变 的 SQL 语句 例如增删改
类似于家中的摄像头可以设置仅录制并储存有异动的画面。
这样可以有效地节约存储空间。
记录已提交的日志
不依赖于存储引擎类型
功能通过 重放 日志文件中的事件来生成数据副本 基于 Mariadb 演示
案例开启二进制日志功能 重要
( 并将 二进制日志文件 与 数据库文件 分开存放 )
// 创建二进制日志文件的存放目录mkdir /mysql/data/logbin -pvchown -R mysql:mysql /mysql/data/logbin/// 定义二进制日志文件路径 ( 服务器选项: log-bin )vim /etc/my.cnfvim /etc/my.cnf.d/mysql-server.cnf # MySQL 8.0[mysqld]log_bin # 开启二进制日志功能 ( 必须 )log-bin/mysql/data/logbin/mysql-bin # 定义二进制日志文件路径// 重启 mariadb 服务setenforce 0 # 关闭 SELinux ( 重要 )systemctl restart mariadb.service# 如重启报错 ( 可以尝试查看日志 tail -f /var/log/mysql/mysqld.log )# 将报错信息发送至 ChatGPT 为你提供解决思路// 验证ll /mysql/data/logbincat /mysql/data/logbin/mysql-bin.index ( 该文件记录当前已有的二进制日志文件列表 )// 验证select sql_log_bin;select log_bin; 二进制日志文件的构成
// 有两类文件
1. 日志文件: mysql|mariadb-bin.文件名后缀, 二进制格式, 如: on.000001, mariadb-bin.000002
2. 索引文件: mysql|mariadb-bin.index, 文本格式, 记录当前已有的二进制日志文件列表 格式配置
MariaDB [hellodb] show variables like binlog_format;----------------------| Variable_name | Value |----------------------| binlog_format | MIXED |----------------------1 row in set (0.001 sec)// MySQL 8.0 默认使用 ROW 方式mysql show variables like binlog_format;----------------------| Variable_name | Value |----------------------| binlog_format | ROW |----------------------1 row in set (0.07 sec)----// 修改二进制格式类型vim /etc/my.cnf.d/mysql-server.cnf[mysqld]binlog_formatSTATEMENT// 重启 MySQL 服务systemctl restart mysqld// 验证类型mysql select binlog_format;-----------------| binlog_format |-----------------| STATEMENT |-----------------1 row in set (0.00 sec) 二进制日志相关的 服务器变量
MySQL :: MySQL 8.0 Reference Manual :: 7.1.4 Server Option, System Variable, and Status Variable Reference
服务器选项全局级别可以写入配置文件永久修改。
// 服务器选项 ( 指定二进制日志文件存放路径 )vim /etc/my.cnf[mysqld]log_bin # 开启二进制日志功能log-bin/mysql/data/logbin/mysql-bin # 定义二进制日志文件路径 系统变量会话级别仅能临时在 MySQL 系统中修改。
// 系统变量 ( ON|OFF 开启或关闭 )set sql_log_binON;--- 验证 ---select sql_log_bin;select log_bin; 二进制日志相关的 服务器变量
sql_log_binON|OFF: // 是否记录二进制日志 默认 ON, 支持动态修改 ( 系统变量, 并非服务器选项 )
log_bin/PATH/BIN_LOG_FILE: // 指定文件位置; 默认 OFF, 表示不启用二进制日志功能, 需上述两项都开启才可以 ----
// 二进制日志记录的格式 老版本 mariadb5.5 默认 STATEMENT
binlog_formatSTATEMENT|ROW|MIXED: 1. STATEMENT: 语句型记录 ( 数据不全,在不同的时间执行结果不同 ) 2. ROW: 行型记录 ( 数据完全,在不同的时间执行结果相同 建议使用该方式,更安全 ) 3. MIXED: 混合型记录 ( 结合两者特性 不建议 )
---- max_binlog_size1073741824: // 单个二进制日志文件的最大体积 # 到达最大值会自动滚动, 默认为 1G 说明: 文件达到上限时的大小未必为指定的精确值
binlog_cache_size4m // 此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接)
max_binlog_cache_size512m // 限制用于缓存多事务查询的字节大小
sync_binlog1|0: // 设定是否启动二进制日志即时同步磁盘功能, 默认 0, 由操作系统负责同步日志到磁盘 ( 建议: 1 安全但效率更低 每次操作都会直接写磁盘日志 )
expire_logs_daysN: // 二进制日志可以自动删除的天数, 默认为 0, 即不自动删除 ( 建议: 30 , 保留最近一个月的二进制日志 )
二进制日志记录 三种格式
参考MySQL中的日志“binlog”的三种格式_binlog_format-CSDN博客
基于 语句 记录statement记录语句默认模式 MariaDB 10.2.3 版本以下 日志量较少。
基于 行 记录row记录数据日志量较大更加安全建议使用的格式MySQL8.0 默认格式。
混合 模式mixed让系统自行判定该基于哪种方式进行默认模式 MariaDB 10.2.4 及版本以上 二进制日志相关配置
查看 mariadb 自行管理使用中的二进制日志文件列表及大小
SHOW {BINARY | MASTER} LOGS // 举例 ( 二进制日志文件列表 )
show master logs; 查看正在使用中的二进制日志文件
在两种情况下会生成一个新的二进制日志文件
1当前使用的二进制日志文件达到了 max_binlog_size 变量所定义的最大体积
2重启数据库服务 SHOW MASTER STATUS // 举例 ( 查看目前正在使用的二进制日志文件 )
SHOW MASTER STATUS; // 插入一条数据
use hellpdb;
insert teachers values(10,a,30,M); // 验证
// 从 156 - 458 之间记录的应该就是我们上面这条插入数据的操作
SHOW MASTER STATUS; 在线查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN log_name] [FROM pos] [LIMIT [offset,] row_count] // 举例 ( 查看二进制文件中的指定内容 )
SHOW BINLOG EVENTS in binlog.000005; 范例
show binlog events in mysql-bin.000001 from 6516 limit 2,3 范例
MariaDB [hellodb] SHOW BINLOG EVENTS; MariaDB [hellodb] SHOW BINLOG EVENTS in mysql-bin.000002 from 614 limit 2,3\G MariaDB [hellodb] SHOW BINLOG EVENTS in mysql-bin.000002 from 614 limit 2,3\G mysqlbinlog二进制日志的客户端命令工具支持离线查看二进制日志
无需登录数据库 即可查看二进制日志
mysqlbinlog [OPTIONS] log_file… --start-position # 指定开始位置 --stop-position # --start-datetime # 时间格式: YYYY-MM-DD hh:mm:ss --stop-datetime --base64-output[name] -v -vvv 范例
// 指定起始位置 结束位置
// 注意: 填写正确的二进制文件名称和路径
mysqlbinlog --start-position156 --stop-position458 /var/lib/mysql/binlog.000005 -v // 指定时间段
mysqlbinlog --start-datetime2018-01-30 20:30:10 --stop-datetime2018-01-30 20:35:22 mariadb-bin.000003 -vvv 二进制日志事件的格式
# at 328
# 151105 16:31:40 server id 1 end_log_pos 431 Query thread_id1
exec_time0 error_code0
use mydb/*!*/;
SET TIMESTAMP1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30)) /*!*/;
事件发生的日期和时间151105 16:31:40
事件发生的服务器标识server id 1
事件的结束位置end_log_pos 431
事件的类型: Query
事件发生时所在服务器执行此事件的线程的ID: thread_id1
语句的时间戳与将其写入二进制文件中的时间差: exec_time0
错误代码: error_code0
事件内容:
GTIDGlobal Transaction ID, mysql5.6 以 mariadb10 以上版本专属属性: GTID 清除指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO log_name | BEFORE datetime_expr } // 示例
PURGE BINARY LOGS TO binlog.000003;
show master logs; 范例
PURGE BINARY LOGS TO mariadb-bin.000003; // 删除 mariadb-bin.000003 之前的日志
PURGE BINARY LOGS BEFORE 2017-01-23;
PURGE BINARY LOGS BEFORE 2017-03-22 09:25:30; 删除所有二进制日志 index 索引文件重新记数
RESET MASTER [TO #]; // 删除所有二进制日志文件, 并重新生成日志文件, 文件名从 # 开始记数, 默认从 1 开始, 一般是 master 主机第一次启动时执行, MariaDB 10.1.6 开始支持 TO // 举例
reset master;
show master logs;
show master status; 刷新日志文件
flush logs; # 触发二进制日志文件的重新生成,也就是生成一个新的二进制文件 范例切换二进制日志
// 基于 mysqladmin 刷新日志文件
[rootcentos8 ~] mysqladmin -uroot flush-binary-log
[rootcentos8 ~] mysqladmin -uroot flush-logs
[rootcentos8 ~] mysql
MariaDB [hellodb] flush logs; 举例语句型记录与行型记录的区别( MySQL 小技巧 )
更新学生表数据
查看二进制日志内容 语句型记录
二进制日志内容不详细但占用空间小 mysql hellodb mysql select binlog_format;
-----------------
| binlog_format |
-----------------
| STATEMENT |
-----------------
1 row in set (0.00 sec) mysql select * from students;
-------------------------------------------------------
| StuID | Name | Age | Gender | ClassID | TeacherID |
-------------------------------------------------------
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
-------------------------------------------------------
25 rows in set (0.00 sec) mysql update students set teacherid1 where stuid 6;
Query OK, 20 rows affected (0.01 sec)
Rows matched: 20 Changed: 20 Warnings: 0 mysql show master logs;
-------------------------------------
| Log_name | File_size | Encrypted |
-------------------------------------
| binlog.000001 | 179 | No |
| binlog.000002 | 7754 | No |
| binlog.000003 | 179 | No |
| binlog.000004 | 200 | No |
| binlog.000005 | 11890535 | No |
| binlog.000006 | 502 | No | # 占用 502 KB
-------------------------------------
6 rows in set (0.00 sec) 更新学生表数据库
查看二进制日志内容 行型记录
二进制日志内容详细但占用空间大
mysql hellodb mysql select binlog_format;
-----------------
| binlog_format |
-----------------
| ROW |
-----------------
1 row in set (0.00 sec) mysql update students set teacherid2 where stuid 6;
Query OK, 20 rows affected (0.01 sec)
Rows matched: 20 Changed: 20 Warnings: 0 mysql show master logs;
-------------------------------------
| Log_name | File_size | Encrypted |
-------------------------------------
| binlog.000001 | 179 | No |
| binlog.000002 | 7754 | No |
| binlog.000003 | 179 | No |
| binlog.000004 | 200 | No |
| binlog.000005 | 11890535 | No |
| binlog.000006 | 525 | No |
| binlog.000007 | 1476 | No | # 占用了 1476 KB
-------------------------------------
7 rows in set (0.00 sec) mysqlbinlog /var/lib/mysql/binlog.000006 -v # 语句型记录二进制日志文件
mysqlbinlog /var/lib/mysql/binlog.000007 -v # 行型记录二进制日志文件 举例
可以基于该小案例还原数据 存在备份效果
MySQL 小技巧MySQL 通过 binlog 二进制日志恢复数据
参考MySQL 通过binlog日志恢复数据_mysql 从库基于主库binlog恢复-CSDN博客
参考https://www.cnblogs.com/michael9/p/11923483.html
参考通过binlog文件恢复mysql数据的三种方式 - 掘金
刷新二进制日志文件
意味着将当前的二进制日志文件切换为新的日志文件
flush logs;
show master status; 在 teachers 表中添加一条记录
在名为 teachers 的数据库表中添加一条新的记录。
这一操作被记录在二进制日志中。
use hellodb;
insert teachers values(100,d,50,F); 破坏该数据表
由于误操作或其他原因导致 teachers 表的数据被损坏或丢失
delete from teachers where tid100; 基于 mysqlbinlog 查看二进制日志记录 可以看到上述操作过程
通过使用这个工具可以查看之前所有的数据库操作
包括添加新记录的操作。
mysqlbinlog /var/lib/mysql/binlog.000002 -v 当我们发现误操作了。 如何解决
在查看二进制日志后发现之前的一个操作导致了 “teachers” 表的损坏。
我们可以 基于 mysqlbinlog 截取误操作之前的操作记录 并生成一个 sql 文件
由于已经知道误操作导致的数据问题
可以截取误操作发生之前的所有操作记录。
这可以通过 mysqlbinlog 工具完成并生成一个 SQL 文件。
这个 SQL 文件包含了在误操作之前对 teachers 表所做的所有操作。
mysqlbinlog /var/lib/mysql/binlog.000002 --stop-position615 /root/1.sql 执行该 sql 文件 恢复数据
最后通过执行这个 SQL 文件
可以将 teachers 表恢复到误操作之前的状态。
这样数据丢失的问题就得到了解决。
mysql hellodb /root/1.sql
总结这个案例描述了一个常见的数据库恢复场景。通过使用二进制日志和
mysqlbinlog工具可以追踪和恢复数据库中的数据。 文章转载自： http://www.morning.tkcz.cn.gov.cn.tkcz.cn http://www.morning.bqxxq.cn.gov.cn.bqxxq.cn http://www.morning.gwhjy.cn.gov.cn.gwhjy.cn http://www.morning.wslr.cn.gov.cn.wslr.cn http://www.morning.hrrmb.cn.gov.cn.hrrmb.cn http://www.morning.fhxrb.cn.gov.cn.fhxrb.cn http://www.morning.lwdzt.cn.gov.cn.lwdzt.cn http://www.morning.pznnt.cn.gov.cn.pznnt.cn http://www.morning.ksggr.cn.gov.cn.ksggr.cn http://www.morning.gyylt.cn.gov.cn.gyylt.cn http://www.morning.mplld.cn.gov.cn.mplld.cn http://www.morning.fgsct.cn.gov.cn.fgsct.cn http://www.morning.tfrlj.cn.gov.cn.tfrlj.cn http://www.morning.rbhqz.cn.gov.cn.rbhqz.cn http://www.morning.jqsyp.cn.gov.cn.jqsyp.cn http://www.morning.xckqs.cn.gov.cn.xckqs.cn http://www.morning.rkzk.cn.gov.cn.rkzk.cn http://www.morning.fkgcd.cn.gov.cn.fkgcd.cn http://www.morning.mtgkq.cn.gov.cn.mtgkq.cn http://www.morning.wjqyt.cn.gov.cn.wjqyt.cn http://www.morning.lwzgn.cn.gov.cn.lwzgn.cn http://www.morning.rgksz.cn.gov.cn.rgksz.cn http://www.morning.qfrmy.cn.gov.cn.qfrmy.cn http://www.morning.wnjrf.cn.gov.cn.wnjrf.cn http://www.morning.qjghx.cn.gov.cn.qjghx.cn http://www.morning.rnnq.cn.gov.cn.rnnq.cn http://www.morning.hjlwt.cn.gov.cn.hjlwt.cn http://www.morning.wdlyt.cn.gov.cn.wdlyt.cn http://www.morning.clfct.cn.gov.cn.clfct.cn http://www.morning.rmkyb.cn.gov.cn.rmkyb.cn http://www.morning.klwxh.cn.gov.cn.klwxh.cn http://www.morning.yrbp.cn.gov.cn.yrbp.cn http://www.morning.joinyun.com.gov.cn.joinyun.com http://www.morning.mkrqh.cn.gov.cn.mkrqh.cn http://www.morning.rzcmn.cn.gov.cn.rzcmn.cn http://www.morning.nkddq.cn.gov.cn.nkddq.cn http://www.morning.fykrm.cn.gov.cn.fykrm.cn http://www.morning.bysey.com.gov.cn.bysey.com http://www.morning.qnrpj.cn.gov.cn.qnrpj.cn http://www.morning.mnqz.cn.gov.cn.mnqz.cn http://www.morning.ktqtf.cn.gov.cn.ktqtf.cn http://www.morning.fjkkx.cn.gov.cn.fjkkx.cn http://www.morning.dhyzr.cn.gov.cn.dhyzr.cn http://www.morning.bnbzd.cn.gov.cn.bnbzd.cn http://www.morning.feites.com.gov.cn.feites.com http://www.morning.rzjfn.cn.gov.cn.rzjfn.cn http://www.morning.stpkz.cn.gov.cn.stpkz.cn http://www.morning.jmnfh.cn.gov.cn.jmnfh.cn http://www.morning.gwdnl.cn.gov.cn.gwdnl.cn http://www.morning.ydmml.cn.gov.cn.ydmml.cn http://www.morning.rwnx.cn.gov.cn.rwnx.cn http://www.morning.ldfcb.cn.gov.cn.ldfcb.cn http://www.morning.thbkc.cn.gov.cn.thbkc.cn http://www.morning.qjxxc.cn.gov.cn.qjxxc.cn http://www.morning.rbhqz.cn.gov.cn.rbhqz.cn http://www.morning.dxqfh.cn.gov.cn.dxqfh.cn http://www.morning.xykst.cn.gov.cn.xykst.cn http://www.morning.jygsq.cn.gov.cn.jygsq.cn http://www.morning.yhwyh.cn.gov.cn.yhwyh.cn http://www.morning.yesidu.com.gov.cn.yesidu.com http://www.morning.zkpwk.cn.gov.cn.zkpwk.cn http://www.morning.seoqun.com.gov.cn.seoqun.com http://www.morning.xq3nk42mvv.cn.gov.cn.xq3nk42mvv.cn http://www.morning.mxhgy.cn.gov.cn.mxhgy.cn http://www.morning.ytrbq.cn.gov.cn.ytrbq.cn http://www.morning.zzjpy.cn.gov.cn.zzjpy.cn http://www.morning.qxljc.cn.gov.cn.qxljc.cn http://www.morning.qwmpn.cn.gov.cn.qwmpn.cn http://www.morning.fnfhs.cn.gov.cn.fnfhs.cn http://www.morning.xflzm.cn.gov.cn.xflzm.cn http://www.morning.fpbj.cn.gov.cn.fpbj.cn http://www.morning.hqpyt.cn.gov.cn.hqpyt.cn http://www.morning.nbsfb.cn.gov.cn.nbsfb.cn http://www.morning.rxpp.cn.gov.cn.rxpp.cn http://www.morning.ruyuaixuexi.com.gov.cn.ruyuaixuexi.com http://www.morning.gjlst.cn.gov.cn.gjlst.cn http://www.morning.qyfrd.cn.gov.cn.qyfrd.cn http://www.morning.mfsjn.cn.gov.cn.mfsjn.cn http://www.morning.egmux.cn.gov.cn.egmux.cn http://www.morning.yqwrj.cn.gov.cn.yqwrj.cn
