搜索
首页数据库mysql教程MySQL日志维护策略汇总_MySQL

这几天要折腾mysql服务器,所以在网上搜罗了一些维护策略,然后自己总结实验,下面是我的总结经验和别人的一些建议。

日志类型:

MySQL有几个不同的日志文件,可以帮助你找出mysqld内部发生的事情:

 日志文件:记入文件中的信息类型
 错误日志:记录启动、运行或停止时出现的问题
 查询日志:记录建立的客户端连接和执行的语句
二进制日志:记录所有更改数据的语句。主要用于复制和即时点恢复
慢日志:记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
事务日志:记录InnoDB等支持事务的存储引擎执行事务时产生的日志

1.启动慢查询日志:

MySQL 如果启用了slow_query_log=ON选项,就会记录执行时间超过long_query_time(默认10s)的查询(初使表锁定的时间不算作 执行 时间)。日志记录文件为slow_query_log_file[=file_name],如果没有给出file_name值, 默认为主机名,后缀为-slow.log。如果给出了文件名,但不是绝对路径名,文件则写入数据目录。

【这个可以在调试mysql性能的时候启用,可以找出是哪个sql指令最浪费时间。生产环境中建议关闭】

2.生产环境中关闭通用查询日志:

由 于打开通用查询日志是记录用户的所有操作,在生产环境中这个日志的量是非常大的,所以一般情况下都是不打开的,myslq默认的该日志功能也是关闭的,在 特殊情况下才进行打开【一般只有在开发测试环境中,为了定位某些功能具体使用了哪些SQL语句的时候,才会在短时间段内打开该日志来做相应的分析。】

mysql> set global general_log = 1; #1:启动通用查询日志,0:关闭通用查询日志

mysql> show global variables like '%general_log%';

+------------------+----------------------------+ 
 
| Variable_name | Value | 
 
+------------------+----------------------------+ 
 
| general_log | ON | #是否启用了通用查询日志 
 
| general_log_file | /var/run/mysqld/mysqld.log | #日志路径 
 
+------------------+----------------------------+ 

2 rows in set (0.00 sec)

3.定期备份二进制日志和sql数据:【本地一份,远程日志主机一份,存储主机一份】

在 my.cnf中log-bin = [filename]是启用二进制日志,默认以[filename].000001往上记录的,从启用log-bin之后【此时最好用mysqldump 保存当前的mysql某个库的数据,因为二进制日志只是记录了从现在起到最近一次mysql当机重启中的所有sql语句】,mysql就会开始记录每一个 sql语句,一旦mysql因各种原因需要重启,则会产生新的二进制日志,000001的后缀名会不断往上自加。若是在mysql当机期间mysql的数 据遭到了破坏(如磁盘损坏),之前的数据全部都被破坏了,这时候这个备份策略就可以帮你挽回损失。你可以从二进制日志中恢复从开始到最近一次mysql重 启这段时间的数据。【二进制日志中记录的是每一个sql语句,可以用mysqlbinlog [filename]查看日志内容】

4.sync_binlog全局变量的取值一定要合适:

默 认情况下,并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能二进制日志中最后的语句丢失了。 要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使二进制日志在每N次二进制日志写入后与硬盘同步。对非 事务表的更新执行完毕后立即保存到二进制日志中。

下面解释下sync_binlog:

“sync_binlog”:这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下:

sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。

sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在 MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系 统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为 1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。从以往经验和相关测试来看,对于 高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

5.如果数据库有很多的事务型操作,则建议把二进制日志的回滚上限设置大一些:

对于事务表,例如BDB或InnoDB表,所有更改表的更新(UPDATE、DELETE或INSERT)被缓存起来,直到服务器接收到 COMMIT语句。在该点,执行完COMMIT之前,mysqld将整个事务写入二进制日志。当处理事务的线程启动时,它为 缓冲查询分配binlog_cache_size大小的内存。如果语句大于该值,线程则打开临时文件来保存事务【所以如果 bunlog_cache_size足够大,就避免了过多的磁盘的I/O操作,可以把数据全部缓存在内存中】。线程结束后临时文件被删除。 【“max_binlog_cache_size”:和"binlog_cache_size"相对应,但是所代表的是binlog能够使用的最大 cache内存大小。当我们执行多语句事务的时候,max_binlog_cache_size如果不够大的话,系统可能会报出“Multi- statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage” 的错误。所以最好也把max_binlog_cache_size也调大些(具体多大看你的服务器了)】

6.尽量把max_binlog_size设置大些

Binlog日志最大值,一般来说设置为512M或者1G,但不能超过1G。该大小并不能非常严格控制Binlog大小,尤其是当到达Binlog 比较靠近尾部而又遇到一个较大事务的时候,系统为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进入当前日志,直到该事务 结束。

7.下面是mysql环境的情况:

 mysql> show variables like '%binlog%';

+--------------------------------+------------+ | Variable_name | Value | +--------------------------------+------------+ 
 
| binlog_cache_size | 1048576 | 
 
| innodb_locks_unsafe_for_binlog | OFF | 
 
| max_binlog_cache_size| 4294967295 | 
 
| max_binlog_size| 1073741824 | 
 
| sync_binlog| 0| 
 
+--------------------------------+------------+ 

以上就是汇总的MySQL日志维护策略,希望对大家维护MySQL日志有所帮助。

声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
如何识别和优化MySQL中的慢速查询? (慢查询日志,performance_schema)如何识别和优化MySQL中的慢速查询? (慢查询日志,performance_schema)Apr 10, 2025 am 09:36 AM

要优化MySQL慢查询,需使用slowquerylog和performance_schema:1.启用slowquerylog并设置阈值,记录慢查询;2.利用performance_schema分析查询执行细节,找出性能瓶颈并优化。

MySQL和SQL:开发人员的基本技能MySQL和SQL:开发人员的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统,SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎,SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询,如按条件过滤和排序。4.常见错误包括语法错误和性能问题,可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。

描述MySQL异步主奴隶复制过程。描述MySQL异步主奴隶复制过程。Apr 10, 2025 am 09:30 AM

MySQL异步主从复制通过binlog实现数据同步,提升读性能和高可用性。1)主服务器记录变更到binlog;2)从服务器通过I/O线程读取binlog;3)从服务器的SQL线程应用binlog同步数据。

mysql:简单的概念,用于轻松学习mysql:简单的概念,用于轻松学习Apr 10, 2025 am 09:29 AM

MySQL是一个开源的关系型数据库管理系统。1)创建数据库和表:使用CREATEDATABASE和CREATETABLE命令。2)基本操作:INSERT、UPDATE、DELETE和SELECT。3)高级操作:JOIN、子查询和事务处理。4)调试技巧:检查语法、数据类型和权限。5)优化建议:使用索引、避免SELECT*和使用事务。

MySQL:数据库的用户友好介绍MySQL:数据库的用户友好介绍Apr 10, 2025 am 09:27 AM

MySQL的安装和基本操作包括:1.下载并安装MySQL,设置根用户密码;2.使用SQL命令创建数据库和表,如CREATEDATABASE和CREATETABLE;3.执行CRUD操作,使用INSERT,SELECT,UPDATE,DELETE命令;4.创建索引和存储过程以优化性能和实现复杂逻辑。通过这些步骤,你可以从零开始构建和管理MySQL数据库。

InnoDB缓冲池如何工作,为什么对性能至关重要?InnoDB缓冲池如何工作,为什么对性能至关重要?Apr 09, 2025 am 12:12 AM

InnoDBBufferPool通过将数据和索引页加载到内存中来提升MySQL数据库的性能。1)数据页加载到BufferPool中,减少磁盘I/O。2)脏页被标记并定期刷新到磁盘。3)LRU算法管理数据页淘汰。4)预读机制提前加载可能需要的数据页。

MySQL:初学者的数据管理易用性MySQL:初学者的数据管理易用性Apr 09, 2025 am 12:07 AM

MySQL适合初学者使用,因为它安装简单、功能强大且易于管理数据。1.安装和配置简单,适用于多种操作系统。2.支持基本操作如创建数据库和表、插入、查询、更新和删除数据。3.提供高级功能如JOIN操作和子查询。4.可以通过索引、查询优化和分表分区来提升性能。5.支持备份、恢复和安全措施,确保数据的安全和一致性。

与MySQL中使用索引相比,全表扫描何时可以更快?与MySQL中使用索引相比,全表扫描何时可以更快?Apr 09, 2025 am 12:05 AM

全表扫描在MySQL中可能比使用索引更快,具体情况包括:1)数据量较小时;2)查询返回大量数据时;3)索引列不具备高选择性时;4)复杂查询时。通过分析查询计划、优化索引、避免过度索引和定期维护表,可以在实际应用中做出最优选择。

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
3 周前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
3 周前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您听不到任何人,如何修复音频
3 周前By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25:如何解锁Myrise中的所有内容
3 周前By尊渡假赌尊渡假赌尊渡假赌

热工具

WebStorm Mac版

WebStorm Mac版

好用的JavaScript开发工具

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

SublimeText3 英文版

SublimeText3 英文版

推荐:为Win版本,支持代码提示!

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中