事务日志的写入类型为 "追加",因此其操作为 "顺序IO";
通常也被称为:预写式日志 write ahead logging
事务日志文件: ib_logfile0, ib_logfile1
中继日志:reley log,在主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取的事件
redo log:实现 WAL( Write Ahead Log 写前日志 ),数据更新前先记录 redo log。
undo log:保存与执行的操作相反的操作,用于实现 rollback 撤销操作。
MySQL 日志:undo log、redo log、binlog 有什么用? | 小林coding
WAL(Write Ahead Log)预写日志,是数据库系统中常见的一种手段,用于保证 数据操作的原子性和持久性。在计算机科学中,「预写式日志」( Write-ahead logging,缩写 WAL )是关系数据库系统中用于提供原子性和持久性(ACID 属性中的两个)的一系列技术。在使用 WAL 的系统中,所有的修改在提交之前都要先写入 log 文件中。
redo log(重做日志):是 Innodb 存储引擎层生成的日志,实现了事务中的 持久性,主要用于掉电等故障恢复;( 记录即将执行的操作 )
undo log(回滚日志):是 Innodb 存储引擎层生成的日志,实现了事务中的 原子性,主要用于 ROLLBACK 事务回滚和 MVCC。( 记录即将执行操作的相反操作,用于实现事务回滚 )
// 列出与 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:先将事务日志缓存在 MySQL 的 Log_buffer 缓存区 中,在一秒后将其写入磁盘。
2:先将事务日志缓存在 OS 操作系统的缓存区 中,在一秒后将其写入磁盘。
2 的安全性胜过 0( 因为 2 将缓存存放在 OS 操作系统的缓存区中 )
而 0 的性能胜过 2( 而 0 将缓存存放在 MySQL 的缓冲区中 )
innodb_flush_log_at_trx_commit=0|1|2
// 默认 MySQL 的刷盘策略: 1
MariaDB [(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_commit=2;
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)
1. 配置为 2 和配置为 0, 性能差异并不大, 因为将数据从 Log Buffer 拷贝到 OS cache, 虽然跨越用户态与内核态, 但毕竟只是内存的数据拷贝, 速度很快.
2. 配置为 2 和配置为 0, 安全性差异巨大, 操作系统崩溃的概率相比 MySQL 应用程序崩溃的概率, 小很多, 设置为2, 只要操作系统不奔溃, 也绝对不会丢数据.
设置为 1,同时 sync_binlog = 1 表示最高级别的容错
innodb_use_global_flush_log_at_trx_commit=0 时,将不能用 SET 语句重置此变量( MariaDB 10.2.6 后废弃)
event scheduler 运行一个 event 时产生的日志信息
// 查看错误日志
tail /var/log/mariadb/mariadb.log
SHOW GLOBAL VARIABLES LIKE 'log_error';
vim /etc/my.cnf
log-error=/var/log/mariadb/mariadb.log
cat /var/log/mariadb/mariadb.log | grep -i ERR
// CentOS7 mariadb 5.5 默认值为 1
// CentOS8 mariadb 10.3 默认值为 2
log_warnings=0|1|2|3... // MySQL5.7 之前
log_error_verbosity=0|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)
mysql> SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| log_error_verbosity | 2 |
+---------------------+-------+
1 row in set (0.00 sec)
通用日志:记录用户对数据库的通用操作,包括:错误的 SQL 语句
通用日志可以保存在:file(默认值)或 table( mysql.general_log 表 )
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|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_log=1;
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 修改变量值都仅是临时修改。
// 永久修改
vim /etc/my.cnf
log_output=TABLE
// 重启 Mariadb 服务
systemctl restart mariadb.service
// "修改通用日志"
// 将记录通用日志至 mysql.general_log 表中
MariaDB [mysql]> set global log_output="table";
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)
// 数据表内容实则存放在该文件中
[root@centos8 ~] 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)
[root@centos8 ~] mysql -e 'select argument from mysql.general_log' | awk '{sql[$0]++}END{for(i in sql){print sql[i],i}}' | sort -nr
[root@centos8 ~] mysql -e 'select argument from mysql.general_log' | sort | uniq -c | sort -nr
// 对日志文件进行排序
[root@centos8 ~] cat /var/lib/mysql/blog.log | sort | uniq -c | sort -nr
[root@centos8 ~] cat /var/lib/mysql/blog.log | awk '{sql[$0]++}END{for(i in sql){print sql[i],i}}' | sort -nr
慢查询日志 记录了执行时间较长的 SQL 语句,我们可以通过分析慢查询日志,找出执行时间较长的 SQL 语句和查询的问题所在,从而优化数据库的性能。
slow_query_log=ON|OFF // "开启或关闭慢查询" 支持全局和会话, 只有全局设置才会生成慢查询文件
long_query_time=N // "慢查询的阀值" 单位秒, 默认为 10s
slow_query_log_file=HOSTNAME-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_indexes=ON // 不使用索引或使用全索引扫描, 不论是否达到慢查询阀值的语句是否记录日志, 默认 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 版后已删除
慢查询日志:用于记录 SQL 执行时间超过某个临界值的 SQL 日志文件,可用于快速定位慢查询,为我们的 SQL优化 做参考。
// 我们可以这样进行永久配置
vim /etc/my.cnf.d/mysql-server.cnf
slow_query_log=ON // "开启慢查询功能"
long_query_time=1 // "将慢查询的阀值设置为一秒"
log_queries_not_using_indexes=ON // 记录未使用索引的行为,即使你未到达指定阈值 ( "建议开启" )
// 重启服务
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_log=1;
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_time=1;
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 age=20;
+-------+--------------+-----+--------+---------+-----------+
| 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
[01:05:24 root@blog ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
[01:05:24 root@blog ~]# mysqldumpslow -s c -t 2 /var/lib/mysql/blog-slow.log
Reading mysql slow query log from /var/lib/mysql/blog-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=0.0 (0), 0users@0hosts
Died at /usr/bin/mysqldumpslow line 178, <> chunk 1.
为了确保数据库能够最大程度地更新,需要恢复并启用二进制日志。
这是因为二进制日志记录了备份后所进行的所有更新,并且还用于在主复制服务器上记录所有将发送给从服务器的语句。
记录着 导致数据改变 或 潜在导致数据改变 的 SQL 语句( 例如:增删改 )
// 创建二进制日志文件的存放目录
mkdir /mysql/data/logbin -pv
chown -R mysql:mysql /mysql/data/logbin/
// 定义二进制日志文件路径 ( 服务器选项: log-bin )
vim /etc/my.cnf
vim /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/logbin
cat /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_format=STATEMENT
// 重启 MySQL 服务
systemctl restart mysqld
// 验证类型
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
1 row in set (0.00 sec)
// 服务器选项 ( 指定二进制日志文件存放路径 )
vim /etc/my.cnf
[mysqld]
log_bin # 开启二进制日志功能
log-bin=/mysql/data/logbin/mysql-bin # 定义二进制日志文件路径
// 系统变量 ( ON|OFF 开启或关闭 )
set sql_log_bin=ON;
--- 验证 ---
select @@sql_log_bin;
select @@log_bin;
sql_log_bin=ON|OFF: // "是否记录二进制日志" 默认 ON, 支持动态修改 ( 系统变量, 并非服务器选项 )
log_bin=/PATH/BIN_LOG_FILE: // "指定文件位置"; 默认 OFF, 表示不启用二进制日志功能, "需上述两项都开启才可以"
----
// "二进制日志记录的格式" 老版本 mariadb5.5 默认 STATEMENT
binlog_format=STATEMENT|ROW|MIXED:
1. STATEMENT: 语句型记录 ( 数据不全,在不同的时间执行结果不同 )
2. ROW: 行型记录 ( 数据完全,在不同的时间执行结果相同 "建议使用该方式,更安全" )
3. MIXED: 混合型记录 ( 结合两者特性 不建议 )
----
max_binlog_size=1073741824: // "单个二进制日志文件的最大体积" # 到达最大值会自动滚动, 默认为 1G 说明: 文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m // 此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接)
max_binlog_cache_size=512m // 限制用于缓存多事务查询的字节大小
sync_binlog=1|0: // 设定是否启动二进制日志即时同步磁盘功能, 默认 0, 由操作系统负责同步日志到磁盘 ( 建议: 1 安全但效率更低 每次操作都会直接写磁盘日志 )
expire_logs_days=N: // "二进制日志可以自动删除的天数", 默认为 0, 即不自动删除 ( 建议: 30 , 保留最近一个月的二进制日志 )
参考:MySQL中的日志“binlog”的三种格式_binlog_format-CSDN博客
基于 "语句" 记录:statement,记录语句,默认模式( MariaDB 10.2.3 版本以下 ),日志量较少。
基于 "行" 记录:row,记录数据,日志量较大,更加安全,建议使用的格式,MySQL8.0 默认格式。
混合 模式:mixed,让系统自行判定该基于哪种方式进行,默认模式( MariaDB 10.2.4 及版本以上)
查看 mariadb 自行管理使用中的二进制日志文件列表,及大小
// 举例 ( 二进制日志文件列表 )
show master logs;
1)当前使用的二进制日志文件达到了 max_binlog_size 变量所定义的最大体积
// 举例 ( 查看目前正在使用的二进制日志文件 )
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-position=156 --stop-position=458 /var/lib/mysql/binlog.000005 -v
// 指定时间段
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv
# 151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1
exec_time=0 error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;
事件发生的日期和时间:151105 16:31:40
事件发生的服务器标识:server id 1
事件的结束位置:end_log_pos 431
事件的类型: Query
事件发生时所在服务器执行此事件的线程的ID: thread_id=1
语句的时间戳与将其写入二进制文件中的时间差: exec_time=0
错误代码: error_code=0
事件内容:
GTID:Global 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';
RESET MASTER [TO #]; // 删除所有二进制日志文件, 并重新生成日志文件, 文件名从 # 开始记数, 默认从 1
开始, 一般是 master 主机第一次启动时执行, MariaDB 10.1.6 开始支持 TO
// 举例
reset master;
show master logs;
show master status;
flush logs; # 触发二进制日志文件的重新生成,也就是生成一个新的二进制文件
[root@centos8 ~] mysqladmin -uroot flush-binary-log
[root@centos8 ~] mysqladmin -uroot flush-logs
[root@centos8 ~] mysql
MariaDB [hellodb]> flush logs;
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 teacherid=1 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> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
mysql> update students set teacherid=2 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数据的三种方式 - 掘金
show master status;
insert teachers values(100,'d',50,'F');
由于误操作或其他原因,导致 teachers 表的数据被损坏或丢失
delete from teachers where tid>=100;
基于 mysqlbinlog 查看二进制日志记录( 可以看到上述操作过程 )
mysqlbinlog /var/lib/mysql/binlog.000002 -v
在查看二进制日志后,发现之前的一个操作导致了 “teachers” 表的损坏。
我们可以 基于 mysqlbinlog 截取误操作之前的操作记录( 并生成一个 sql 文件)
这可以通过 mysqlbinlog 工具完成,并生成一个 SQL 文件。
这个 SQL 文件包含了在误操作之前对 teachers 表所做的所有操作。
mysqlbinlog /var/lib/mysql/binlog.000002 --stop-position=615 > /root/1.sql
总结:这个案例描述了一个常见的数据库恢复场景。通过使用二进制日志和
mysqlbinlog工具,可以追踪和恢复数据库中的数据。