重庆网站建设吧,包装设计网上接单,sem专业培训公司,网站建设与开发学习【图书推荐】《MySQL 9从入门到性能优化#xff08;视频教学版#xff09;》-CSDN博客
《MySQL 9从入门到性能优化#xff08;视频教学版#xff09;#xff08;数据库技术丛书#xff09;》(王英英)【摘要 书评 试读】- 京东图书 (jd.com)
MySQL9数据库技术_夏天又到了…【图书推荐】《MySQL 9从入门到性能优化视频教学版》-CSDN博客
《MySQL 9从入门到性能优化视频教学版数据库技术丛书》(王英英)【摘要 书评 试读】- 京东图书 (jd.com)
MySQL9数据库技术_夏天又到了的博客-CSDN博客 二进制日志主要记录MySQL数据库的变化以一种有效的格式并且是事务安全的方式包含更新日志中可用的所有信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据例如没有匹配任何行的一个DELETE的语句。语句以“事件”的形式保存描述数据的更改。此外二进制日志还包含每个更新数据库的语句的执行时间信息。
二进制日志不包含没有修改任何数据的语句。如果想要记录所有语句例如为了识别有问题的查询需要使用通用查询日志。使用二进制日志的主要目的是最大可能地恢复数据库因为二进制日志包含备份后进行的所有更新。本节将介绍二进制日志的相关内容。
12.2.1 启动和设置二进制日志
默认情况下二进制日志是开启的可以通过修改MySQL的配置文件来启动和设置二进制日志。
在my.ini中配置文件中的[mysqld]组下面有关于二进制日志的设置
log-bin [path/ [filename] ]
log-bin定义开启二进制日志path表明日志文件所在的目录路径filename指定日志文件的名称如文件的全名为“filename.000001”“filename.000002”等。除此之外还有一个名称为“filename.index”的文件文件内容为所有日志的清单可以使用记事本打开该文件。
添加以下几个参数与参数值
[mysqld]
log-bin
expire_logs_days 10
max_binlog_size 100M
expire_logs_days定义MySQL清除过期日志的时间即二进制日志自动删除的天数。默认值为0表示不自动删除。当MySQL启动或刷新二进制日志时操作系统可能会删除过期的二进制日志文件。max_binlog_size定义单个文件的大小限制如果二进制日志写入的内容大小超出了给定值日志就会发生滚动关闭当前文件重新打开一个新的日志文件。不能将该变量设置为大于1GB或小于4096B默认值是1GB。
添加完毕之后关闭并重新启动MySQL服务进程即可打开二进制日志然后可以通过SHOW VARIABLES语句来查询日志设置。
【例12.1】使用SHOW VARIABLES语句查询日志设置执行的语句及结果如下
mysql SHOW VARIABLES LIKE log_% ;
-------------------------------------------------------------------------------
|Variable_name | Value |
-------------------------------------------------------------------------------
|log_bin | ON |
|log_bin_basename | D:\ProgramData\MySQL\MySQL Server 9.0\Data\X0NHUNO7YDZVSSI-bin |
|log_bin_index |D:\ProgramData\MySQL\MySQL Server 9.0\Data\X0NHUNO7YDZVSSI-bin.index |
|log_bin_trust_function_creators |OFF |
|log_bin_use_v1_row_events | OFF |
|log_error| .\X0NHUNO7YDZVSSI.err |
|log_error_services |log_filter_internal;log_sink_internal |
|log_error_suppression_list | |
|log_error_verbosity |2 |
|log_output | FILE |
|log_queries_not_using_indexes |OFF |
|log_slave_updates |ON |
|log_slow_admin_statements |OFF |
|log_slow_slave_statements |OFF |
|log_statements_unsafe_for_binlog |ON |
|log_throttle_queries_not_using_indexes|0 |
|log_timestamps | UTC |
-------------------------------------------------------------------------------通过上面的查询结果可以看出log_bin变量的值为ON表明二进制日志已经打开。MySQL重新启动之后读者可以在自己的计算机上的MySQL数据文件夹下面看到新生成的文件后缀为“.000001”和“.index”的两个文件文件名称为默认主机名称。例如在笔者的计算机上文件名称为“X0NHUNO7YDZVSSI-bin.000001”和“X0NHUNO7YDZVSSI-bin.index”。 数据库文件最好不要与日志文件放在同一个磁盘上这样当数据库文件所在的磁盘发生故障时可以使用日志文件恢复数据。 12.2.2 查看二进制日志
MySQL二进制日志是经常用到的它存储了所有的变更信息。当MySQL创建二进制日志文件时先创建一个以“filename”为名称、以“.index”为后缀的文件再创建一个以“filename”为名称、以“.000001”为后缀的文件。MySQL服务重新启动一次以“.000001”为后缀的文件会增加一个并且后缀名加1递增如果日志长度超过了max_binlog_size的上限默认是1GB就会创建一个新的日志文件。
SHOW BINARY LOGS语句可以查看当前的二进制日志文件的个数及其名称。MySQL二进制日志并不能直接查看如果要查看日志内容可以使用mysqlbinlog命令。
【例12.2】使用SHOW BINARY LOGS查看二进制日志文件的个数及其名称SQL语句 如下
mysql SHOW BINARY LOGS;
--------------------------------------- -----------
| Log_name | File_size | Encrypted
--------------------------------------- -----------
| X0NHUNO7YDZVSSI-bin.000001 | 178 | No |
--------------------------------------- -----------
1 row in set (0.00 sec)可以看到当前只有一个二进制日志文件。日志文件的个数与MySQL服务启动的次数相同。每启动一次MySQL服务就会产生一个新的日志文件。
【例12.3】使用mysqlbinlog查看二进制日志SQL语句如下
C:\ mysqlbinlog D:/mysql/log/binlog.000001
/*!40019 SET session.max_insert_delayed_threads0*/;
/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;
DELIMITER /*!*/;
# at 4
#190130 15:27:48 server id 1 end_log_pos 107 Start: binlog v 4, server v 9.0.1-log created 160330
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG
9JBcTg8BAAAAZwAAAGsAAAABAAQANS41LjEzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAA
AAAAAAAAAAAAAAAAAAD0kFxOEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA
/*!*/;
# at 107
#190330 15:34:17 server id 1 end_log_pos 175 Query thread_id2 exec_time0
error_code0
SET TIMESTAMP1314689657/*!*/;
SET session.pseudo_thread_id2/*!*/;
SET session.foreign_key_checks1, session.sql_auto_is_null0, session.unique_checks1,
session
SET session.sql_mode0/*!*/;
SET session.auto_increment_increment1, session.auto_increment_
offset1/*!*/;
/*!\C gb2312 *//*!*/;
SET session.character_set_client24,session.collation_connection24,
session.collation_server24/
SET session.lc_time_names0/*!*/;
SET session.collation_databaseDEFAULT/*!*/;
BEGIN
/*!*/;
# at 175
#190330 15:34:17 server id 1 end_log_pos 289 Query thread_id2 exec_time0
error_code0
use test/*!*/;
SET TIMESTAMP1314689657/*!*/;
UPDATE fruits set f_price 5.00 WHERE f_id a1
/*!*/;
# at 289
#190330 15:34:17 server id 1 end_log_pos 316 Xid 14
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;这是一个简单的日志文件记录了一些用户的操作。从文件内容中可以看到用户对表fruits进行了更新操作语句为“UPDATE fruits set f_price 5.00 WHERE f_id a1;”。
12.2.3 删除二进制日志
MySQL的二进制日志文件可以配置为自动删除但也提供了安全的手动删除二进制日志文件的方法RESET MASTER删除所有的二进制日志文件PURGE MASTER LOGS只删除部分二进制日志文件。本小节将介绍这两种删除二进制日志文件的方法。
1. 使用RESET MASTER语句删除所有二进制日志文件
RESTE MASTER语法如下
RESET MASTER;
执行完该语句后所有二进制日志文件将被删除MySQL会重新创建二进制日志文件新的日志文件扩展名将重新从000001开始编号。
2. 使用PURGE MASTER LOGS语句删除指定二进制日志文件
PURGE MASTER LOGS 语法如下
PURGE {MASTER | BINARY} LOGS TO log_name
PURGE {MASTER | BINARY} LOGS BEFORE date
第一种方法指定文件名执行该语句将删除文件编号比指定文件编号小的所有二进制日志文件。第二种方法指定日期执行该语句将删除指定日期以前的所有二进制日志文件。
【例12.4】使用PURGE MASTER LOGS删除创建时间比X0NHUNO7YDZVSSI-bin.000003早的所有二进制日志文件。
为了演示语句的操作过程准备多个日志文件读者可以对MySQL服务进行多次重新启动。例如这里有3个二进制日志文件
mysql SHOW BINARY LOGS;
--------------------------------------- -----------
| Log_name | File_size | Encrypted
---------------------------------------------------
| X0NHUNO7YDZVSSI-bin.000001 | 178 | No |
| X0NHUNO7YDZVSSI-bin.000002 | 641 | No |
| X0NHUNO7YDZVSSI-bin.000003 | 345 | No |
---------------------------------------------------
3 rows in set (0.00 sec)执行删除命令
mysql PURGE MASTER LOGS TO X0NHUNO7YDZVSSI-bin.000003;
Query OK, 0 rows affected (0.07 sec)
执行完成后使用SHOW binary logs语句查看二进制日志
mysql SHOW binary logs;
---------------------------------------
| Log_name | File_size |
---------------------------------------
| X0NHUNO7YDZVSSI-bin.000003 | 345 |
---------------------------------------
1 rows in set (0.00 sec)可以看到X0NHUNO7YDZVSSI-bin.000001、X0NHUNO7YDZVSSI-bin 000002两个二进制日志文件被删除了。
【例12.5】使用PURGE MASTER LOGS删除2024年7月30日前创建的所有二进制日志文件SQL语句及结果如下
mysql PURGE MASTER LOGS BEFORE 20240730;
Query OK, 0 rows affected (0.05 sec)
语句执行之后2024年7月30日之前创建的二进制日志文件都将被删除但2024年7月30日的日志会被保留读者可根据自己计算机中创建日志的时间修改命令参数。使用mysqlbinlog可以查看指定日志的创建时间如【例12.3】所示部分日志内容如下
/*!40019 SET session.max_insert_delayed_threads0*/;
/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;
DELIMITER /*!*/;
# at 4
#240730 15:27:48 server id 1 end_log_pos 107 Start: binlog v 4, server v 9.0.1-log created 160330
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG 其中240730为日志创建的时间即2024年7月30日。
12.2.4 使用二进制日志恢复数据库
如果MySQL服务器启用了二进制日志那么当数据库意外丢失数据时可以使用mysqlbinlog工具从指定的时间点开始例如最后一次备份直到现在或另一个指定的时间点的日志中恢复数据。
要想使用二进制日志恢复数据需要知道当前二进制日志文件的路径和文件名一般可以从配置文件my.cnf或者my.ini文件名取决于MySQL服务器的操作系统中找到路径。
mysqlbinlog恢复数据的语法如下
mysqlbinlog [option] filename |mysql –uuser -ppass
option是一些可选的选项filename是日志文件名。比较重要的两对option参数是--start-date与--stop-date、--start-position与--stop-position。--start-date与--stop-date可以指定恢复数据库的起始时间点和结束时间点。--start-position与--stop-position可以指定恢复数据库的开始位置和结束位置。
【例12.6】使用mysqlbinlog恢复MySQL数据库到2024年7月30日15:27:48时的状态执行命令及结果如下
mysqlbinlog --stop-date2024-07-30 15:27:48 D:\mysql\log\binlog\ X0NHUNO7YDZVSSI-bin.000003 | mysql –uuser –ppass
该命令执行成功后会根据X0NHUNO7YDZVSSI-bin.000003日志文件恢复2024年7月30日15:27:48以前的所有操作。这种方法对于因意外操作而导致的数据丢失非常有效比如因操作不当误删了数据表。
12.2.5 暂时停止二进制日志功能
如果在MySQL的配置文件中配置启动了二进制日志那么MySQL会一直记录二进制日志。如果想停止记录二进制日志可以修改配置文件但是需要重启MySQL数据库。为此MySQL提供了暂时停止记录二进制日志的功能通过SET SQL_LOG_BIN语句可以使MySQL暂停或者启动二进制日志。
SET SQL_LOG_BIN的语法如下
SET sql_log_bin {0|1}
执行如下语句将暂停记录二进制日志
mysql SET sql_log_bin 0;
Query OK, 0 rows affected (0.00 sec)
执行如下语句将恢复记录二进制日志
mysql SET sql_log_bin 1;
Query OK, 0 rows affected (0.00 sec)
