mysql开启binlog日志及恢复 windows mysql开启binlog日志

转载

mob6454cc6a68aa 2023-09-27 06:50:54

文章标签 mysql开启binlog日志及恢复 mysql 二进制日志日志文件 文章分类 MySQL 数据库

二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:
其一：MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
其二：自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。
二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

1、查看是否开启了binlog：show binary logs;

mysql开启binlog日志及恢复 windows mysql开启binlog日志_二进制日志

2、查看binlog日志开关是否打开:（log_bin=OFF表示关闭，ON表示打开）

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
3 rows in set (0.00 sec)

3、开启binlog步骤

step1:my.ini中添加log-bin配置

mysql5.7见mysql需要修改的my.ini见此

mysql开启binlog日志及恢复 windows mysql开启binlog日志_日志文件_02

[mysqld]   #mysqld节点添加
log-bin=D:\MySqlUse\MySql-binlog    #表示binlog的存储路径

在指定路径时要注意以下两点：
1 在目录的文件夹命名中不能有空格，比如“aa bb”文件夹是不允许的，这样，在访问日志时候会报错；
2 指定目录时候一定要以*.log结尾，即不能仅仅指定到文件夹的级别，如上面我写的logbin.log，这时候，日志文件的名称是logbin.000001 logbin.000002。。。不然不会有日志文件产生。

step2:重启mysql:services.exe调起服务，然后关闭mysql，再重启。

step3:查看log_bin开关状态

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

step4:查看binlog信息

mysql> show binary logs;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| MySql-binlog.000001 |       285 |
+---------------------+-----------+
1 row in set (0.00 sec)

step5：mysqlbinlog 查看binlog内容

为了方便查看日志内容可以导出到.sql文件
mysqlbinlog ..\log-bin\MySql-binlog.000001 ->a.sql

C:\Users\Administrator>mysqlbinlog MySql-binlog.000001;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'MySql-binlog.000001;' not found (Errcode: 2)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

C:\Users\Administrator>

4、binlog常用命令

mysql> show binlog events;   #只查看第一个binlog文件的内容
mysql> show binlog events in 'mysql-bin.000002';#查看指定binlog文件的内容
mysql> show binary logs;  #获取binlog文件列表
mysql> show master status； #查看当前正在写入的binlog文件

实际操练:

mysql> show binlog events in 'MySql-binlog.000008';
+---------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------+
| Log_name            | Pos  | Event_type  | Server_id | End_log_pos | Info
                                                                                            |
+---------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------+
| MySql-binlog.000008 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.40-log, Binlog ver: 4
                                                                                            |
| MySql-binlog.000008 |  107 | Query       |         1 |         176 | BEGIN
                                                                                            |
| MySql-binlog.000008 |  176 | Table_map   |         1 |         220 | table_id: 33 (learn.t1)
                                                                                            |
| MySql-binlog.000008 |  220 | Update_rows |         1 |         276 | table_id: 33 flags: STMT_END_F
                                                                                            |
| MySql-binlog.000008 |  276 | Xid         |         1 |         303 | COMMIT /* xid=26 */
                                                                                            |
| MySql-binlog.000008 |  303 | Query       |         1 |         372 | BEGIN
                                                                                            |
| MySql-binlog.000008 |  372 | Table_map   |         1 |         416 | table_id: 33 (learn.t1)
                                                                                            |
| MySql-binlog.000008 |  416 | Update_rows |         1 |         472 | table_id: 33 flags: STMT_END_F
                                                                                            |
| MySql-binlog.000008 |  472 | Xid         |         1 |         499 | COMMIT /* xid=31 */
                                                                                            |
| MySql-binlog.000008 |  499 | Query       |         1 |         770 | use `learn`; CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `t_modified` timestamp NOT NULL DEFAULT CURRENT_
TIMESTAMP, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `t_modified`(`t_modified`)) ENGINE=InnoDB |
| MySql-binlog.000008 |  770 | Query       |         1 |         847 | BEGIN
                                                                                            |
| MySql-binlog.000008 |  847 | Query       |         1 |         957 | use `learn`; insert into t values(1,1,'2018-11-13')
                                                                                            |
| MySql-binlog.000008 |  957 | Xid         |         1 |         984 | COMMIT /* xid=47 */
                                                                                            |
| MySql-binlog.000008 |  984 | Query       |         1 |        1061 | BEGIN
                                                                                            |
| MySql-binlog.000008 | 1061 | Query       |         1 |        1171 | use `learn`; insert into t values(2,2,'2018-11-12')
                                                                                            |
| MySql-binlog.000008 | 1171 | Xid         |         1 |        1198 | COMMIT /* xid=48 */
                                                                                            |
| MySql-binlog.000008 | 1198 | Query       |         1 |        1275 | BEGIN
                                                                                            |
| MySql-binlog.000008 | 1275 | Query       |         1 |        1385 | use `learn`; insert into t values(3,3,'2018-11-11')
                                                                                            |
| MySql-binlog.000008 | 1385 | Xid         |         1 |        1412 | COMMIT /* xid=49 */
                                                                                            |
| MySql-binlog.000008 | 1412 | Query       |         1 |        1489 | BEGIN
                                                                                            |
| MySql-binlog.000008 | 1489 | Query       |         1 |        1599 | use `learn`; insert into t values(4,4,'2018-11-10')
                                                                                            |
| MySql-binlog.000008 | 1599 | Xid         |         1 |        1626 | COMMIT /* xid=50 */
                                                                                            |
| MySql-binlog.000008 | 1626 | Query       |         1 |        1703 | BEGIN
                                                                                            |
| MySql-binlog.000008 | 1703 | Query       |         1 |        1813 | use `learn`; insert into t values(5,5,'2018-11-09')
                                                                                            |
| MySql-binlog.000008 | 1813 | Xid         |         1 |        1840 | COMMIT /* xid=51 */
                                                                                            |
| MySql-binlog.000008 | 1840 | Query       |         1 |        1917 | BEGIN
                                                                                            |
| MySql-binlog.000008 | 1917 | Query       |         1 |        2052 | use `learn`; delete from t   where a>=4 and t_modified<='2018-11-10' limit 1
                                                                                            |
| MySql-binlog.000008 | 2052 | Xid         |         1 |        2079 | COMMIT /* xid=54 */
                                                                                            |
| MySql-binlog.000008 | 2079 | Query       |         1 |        2156 | BEGIN
                                                                                            |
| MySql-binlog.000008 | 2156 | Query       |         1 |        2291 | use `learn`; delete from t   where a>=4 and t_modified<='2018-11-10' limit 1
                                                                                            |
| MySql-binlog.000008 | 2291 | Xid         |         1 |        2318 | COMMIT /* xid=56 */
                                                                                            |
+---------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------+
31 rows in set (0.02 sec)

5、binlong日志生成规则

1 ）当停止或重启服务器时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的日志文件，文件序号递增；

2）如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件（在这里需要注意的是，如果你正使用大的事务，二进制日志还会超过max_binlog_size，不会生成新的日志文件，事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性）

3 ）日志被刷新时，新生成一个日志文件。

flush实验:

mysql> show binary logs;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| MySql-binlog.000001 |       304 |
| MySql-binlog.000002 |       107 |
+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.31 sec)

mysql> show binary logs;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| MySql-binlog.000001 |       304 |
| MySql-binlog.000002 |       153 |
| MySql-binlog.000003 |       107 |
+---------------------+-----------+
3 rows in set (0.00 sec)

6、MySQ binlog三种模式及设置方法

1.1 Row Level 行模式

日志中会记录每一行数 据被修改的形式，然后在slave端再对相同的数据进行修改 

优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条被修改。

所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。

不会出现某些特定的情况下的存储过程或function，以及trigger的调用和触发无法被正确复制的问题

缺点：row level，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，会产生大量的日志内容。

1.2 Statement Level（默认）

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端
执行过的相同的sql来再次执行。
**优点：**statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减
少bin-log日志量，节约IO，提高性能，因为它只需要在Master上锁执行的语句的细节，以及执行语句的上下文的
信息。
缺点：由于只记录语句，所以，在statement level下 已经发现了有不少情况会造成MySQL的复制出现问题，主
要是修改数据的时候使用了某些定的函数或者功能的时候会出现。

1.3 Mixed 自动模式

在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式，也就是在Statement和
Row之间选择一种。如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

1.4 企业场景如何选择binlog模式
1、互联网公司，使用MySQL的功能相对少（存储过程、触发器、函数）

选择默认的语句模式，Statement Level（默认）
2、公司如果用到使用MySQL的特殊功能（存储过程、触发器、函数）
则选择Mixed模式

3、公司如果用到使用MySQL的特殊功能（存储过程、触发器、函数）又希望数据最大化一直，此时最好选择Row level模式

1.5 如何配置binlog

在数据库中查看binlog模式

mysql> show global variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> SET GLOBAL binlog_format = 'ROW';
Query OK, 0 rows affected (0.08 sec)

mysql> show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

但是要注意如果set global variables，那么show variables 还是老的binlog格式，需要从配置文件修改。然后重启mysql
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

配置文件参数如下：

[myslqd]
log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
#binlog_format="MIXED"

运行时在线修改临时：

mysql>SET GLOBAL binlog_format = 'ROW';
mysql>show global variables like '%binlog_format%';
其他模式命令为：
SET GLOBAL binlog_format = 'MIXED';
SET GLOBAL binlog_format = 'STATEMENT';

说明:

GLOBAL=全局会话
SESSION=当前会话
检验ROW模式下binlog日志记录效果

mysqlbinlog查看结果:

#记得binlog后不要加分号
D:\MySqlUse>mysqlbinlog --base64-output="decode-rows" --verbose MySql-binlog.000008
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200326  0:47:37 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.40-log created 200326  0:47:37
# Warning: this binlog is either in use or was not closed properly.
# at 107
#200326  0:47:40 server id 1  end_log_pos 176   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1585154860/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 176
# at 220
#200326  0:47:40 server id 1  end_log_pos 220   Table_map: `learn`.`t1` mapped to number 33
#200326  0:47:40 server id 1  end_log_pos 276   Update_rows: table id 33 flags: STMT_END_F
### UPDATE `learn`.`t1`
### WHERE
###   @1=1
###   @2=1
###   @3=19
### SET
###   @1=1
###   @2=1
###   @3=20
# at 276
#200326  0:47:40 server id 1  end_log_pos 303   Xid = 26
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

D:\MySqlUse>

二进制日志可以使用mysqlbinlog命令查看

mysqlbinlog [option] log-file1 log-file2...
以下是常用的几个选项：
-d,--database=name：只查看指定数据库的日志操作
-o,--offset=#：忽略掉日志中的前n个操作命令
-r,--result-file=name：将输出的日志信息输出到指定的文件中，使用重定向也一样可以。
-s,--short-form：显示简单格式的日志，只记录一些普通的语句，会省略掉一些额外的信息如位置信息和时间信
息以及基于行的日志。可以用来调试，生产环境千万不可使用
--set-charset=char_name：在输出日志信息到文件中时，在文件第一行加上set names char_name
--start-datetime,--stop-datetime：指定输出开始时间和结束时间内的所有日志信息
--start-position=#,--stop-position=#：指定输出开始位置和结束位置内的所有日志信息
-v,-vv：显示更详细信息，基于row的日志默认不会显示出来，此时使用-v或-vv可以查看

MySql日志分析:

日志分析

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：数据可视化UI框架数据可视化模块

下一篇：常见算法 python实现 python常见算法题

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯