网站开发要注意的漏洞,网站建设四步骤,江苏今天刚刚的最新新闻,自己做网站平台一、MySQL日志文件mysql日志文件及功能#xff1a;日志文件功能错误日志记录启动、停止、运行过程中mysqld时出现的问题通用日志记录建立客户端连接和执行的语句二进制日志记录更改数据的所有语句#xff0c;还用于复制慢查询日志记录执行时间超过long_query_time秒的所有查询…一、MySQL日志文件mysql日志文件及功能日志文件功能错误日志记录启动、停止、运行过程中mysqld时出现的问题通用日志记录建立客户端连接和执行的语句二进制日志记录更改数据的所有语句还用于复制慢查询日志记录执行时间超过long_query_time秒的所有查询默认情况下mysql所有日志均存储于mysql数据目录下。通过刷新日志可以时mysqld强制关闭和打开日志文件(或者在某些情况下切换到下一个日志)。刷新日志方法1)flush logs2)mysqladmin flush-logs3)mysqladmin refresh1、错误日志1)作用记录mysqld启动、停止以及mysql数据库在运行过程中发生的各种严重错误信息。当数据库发生任何故障导致无法重启时可以参考错误日志进行故障诊断。2)位置可以使用--log-error[file_name]参数选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name文件位置mysqld使用的错误日志名为host_name.err并默认保存在datadir指定的目录下。2、通用日志1)作用查询日志记录客户端的所有语句(所有连接和语句都记录到通用日志)而binlog不记录select语句。2)位置查询日志和慢查询日志都可以保存在文件或表中并使用参数--log-output[value]来进行控制value的值可以是tablefilenone的一个或多个的组合中间用逗号进行分割分别表示日志保存在表文件不保存在表或文件中这里的表指的是mysql库总的general_log(慢查询日志是slow_log)表。其中none的优先级最高比如--log-outputtable,file表示日志同时输出到表和文件中--log-outputtable,none表示日志不保存在表中。如果不显示使用此参数则表示日志输出到文件中。一般日志输出到表中要占用更多的系统资源。如果要启用查询日志可以通过参数--general_log[{0|1}]和--general_log_filefile_name来进行控制。前者控制是否启用查询日志(--general_log设置为1或者不带值都可以启用查询日志设置为0表示关闭查询日志不指定此参数表示不启动查询日志)后者控制日志文件的路径。也可以使用--log[file_name]或-l [file_name]选项启动它如果没有给出file_name那么默认值就是host_name.log。运行时可以通过set global general_log on打开通用日志。如果没有指定--general_log_filefile_name值且没有显示设置--log_output参数那么日志默认写入datadir目录下的默认文件名为host_name.log。这两个参数都是global类型可以在启动时或系统运行时动态修改。如果想在session级别控制日志是否被记录则通过在session中设置参数sql_log_off为on或off来进行控制。查询通用日志位置mysql show variables like gene%;--------------------------------------------------------| Variable_name | Value |--------------------------------------------------------| general_log | OFF || general_log_file | /usr/local/mysql/data/chavinking.log |--------------------------------------------------------2 rows in set (0.00 sec)3)日志内容的读取查询日志为文本文档格式一般直接读取就可。4)一般情况下不建议开启查询日志功能否则可能造成短时间内磁盘占用率猛增。3、慢查询日志1)概述当参数slow_query_log1时慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有sql语句的日志(获得表锁定的时间不能算作执行时间)。当没有给出慢查询日志名时默认为主机名后缀为-slow.log当给出了文件名但不是绝对路径慢查询日志会记录到mysql的数据目录下。执行完语句并且释放锁后即可记入慢查询日志记录顺序可以与执行顺序不同。慢查询日志可以查找执行时间长的语句用于优化它是MySQL数据中常用的性能优化工具。Long_query_time默认为10秒最小为0精度可以到微秒。一般两类语句不记录到慢查询日志中管理语句和不使用索引进行查询的语句。如果要监控这两类SQL语句可以分别通过参数--log-slow-admin-statements和log_queries_not_using_indexes进行控制。2)文件位置和格式慢查询日志默认是关闭的在mysql5.1.29前当用--log-slow-queries[file_name]选项启动mysqld时慢查询日志开始被记录。和前几种日志一样如果没有指定file_name的值日志将写入datadir路径默认文件名为host_name-slow.log。在mysql5.1.29后--log-slow-queries参数废弃采用两个新的参数进行替换--slow_query_log[{0|1}]显示指定慢查询日志状态如果不指定值或指定值为1都会打开慢查询使用slow_query_log_file[file_name]来指定慢查询日志路径另外还可以指定--log-output参数指定日志的输出格式默认输出到文件。需要注意的是如果选择输出到表则表中的记录的慢查询时间只能精确到秒而日志文件中可以精确到微秒。3)日志文件的读取慢查询日志文件是存文本方式存储可以直接读取。也可以通过mysqldumpslow工具(../bin/mysqldumpslow faspdev-slow.log)对慢查询日志进行汇总处理。慢查询日志通常用来定位mysql服务中sql问题默认建议打开慢查询日志并定期查看分析。4)慢查询日志在mysql性能优化中的作用详解4.1)慢查询日志格式mysql慢查询标准query_time查询耗时rows_examined检查多少条记录rows_sent返回多少行记录mysql使用以上几点大致衡量sql成本。其他信息包括如下time执行sql开始时间lock time等待table lock的时间注意innodb行锁等待不会反应在这里。userhost执行查询的用户和客户端ip以下是一个慢查询例子# Time: 170122 13:53:17# UserHost: root[root] localhost [] Id: 1# Query_time: 9.839503 Lock_time: 0.000159 Rows_sent: 0 Rows_examined: 524288SET timestamp1485064397;insert into column_charset select * from column_charset;一般执行时间最长的sql是需要优化的如果检查了大量数据而只返回少量数据则意味着质量不佳。需要注意的是慢查询日志里的慢查询不一定就是不良sql还可能受其他查询影响或者受到系统资源限制导致的。4.2)如何识别需要关注的sql确认已经开启了慢查询日志并且设置了合理的阈值。以下命令可以查看是否启用慢查询日志以及慢查询日志路径mysql show variables like %slow_query_log%;---------------------------------------------------------------- ------------------------| Variable_name | Value |-----------------------------------------------------------------------------------------| slow_query_log | OFF || slow_query_log_file | /usr/local/mysql5631/data/faspdev-slow.log |----------------------------------------------------------------------------------------以下命令可以查看全局变量long_query_time参数的值所有超过该值得sql都将记录到慢查询日志中mysql show variables like long_query_time;----------------------------| Variable_name | Value |----------------------------| long_query_time | 1.000000 |----------------------------1 row in set (0.00 sec)在mysql参数文件中开启慢查询日志设置如下[mysqld]slow_query_log1slow_query_log_file/usr/local/mysql/data/log/slowquery.loglong_query_time0.5mysql 5.0慢查询参数是不一样的且需要重启后才可以生效相关参数为log_slow_queries和slow_launch_time。参数log-queries-not-using-indexes用于指定如果没有使用到索引或虽然使用了索引但仍然遍历了所有记录就将其记录下来默认此参数是关闭的。4.3)使用工具分析慢查询日志如果慢查询日志内容比较多对于分析是比较麻烦的一般有以下两种办法可以参考u 调整阈值u 使用命令、脚本、工具进行分析如mysqldumpslow、pt-query-digest等。A. 使用操作系统命令进行分析可以使用shell命令进行统计分析如grep、awk、wc但是这些工具不容易实现高级功能。awk /^#Time:/{print $3,$4,c;c0}/^#User/{c} mysql-slow.log /mnt/slowquery.logB. 使用mysqldumpslow工具进行分析mysqldumpslow工具是官方自带的此工具可以获得日志中的查询摘要。以下是mysqldumpslow工具使用帮助$mysqldumpslow --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are--verbose verbose--debug debug--help write this text to standard output-v verbose-d debug-s ORDER what to sort by (t, at, l, al, r, ar etc), at is default-r reverse the sort order (largest last instead of first)-t NUM just show the top n queries-a dont abstract all numbers to N and strings to S-n NUM abstract numbers with at least n digits within names-g PATTERN grep: only consider stmts that include this string-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),default is *, i.e. match all-i NAME name of server instance (if using mysql.server startup script)-l dont subtract lock time from total time查询时间最长的10个sqlmysqldumpslow -t 10 mysql-slow.log查询访问次数最多的10个sql命令mysqldumpslow -s c -t 10 mysql-slow.log查询访问记录最多的10条sql命令mysqldumpslow -s r -t 10 mysql-slow.log4.4)以下是mysql运行状态下设置慢查询日志的例子mysql show variables like long_query_time;----------------------------| Variable_name | Value |----------------------------| long_query_time | 10.000000 |----------------------------1 row in set (0.00 sec)mysql set long_query_time2;Query OK, 0 rows affected (0.00 sec)mysql show variables like %slow_query_log%;-----------------------------------------------------------------| Variable_name | Value |-----------------------------------------------------------------| slow_query_log | OFF || slow_query_log_file | /usr/local/mysql5631/data/faspdev-slow.log |-----------------------------------------------------------------2 rows in set (0.00 sec)mysql set global slow_query_log1;Query OK, 0 rows affected (0.00 sec)4、binlog日志文件1)作用Binlog记录了数据库所有的ddl语句和dml语句但不包括select语句内容语句以事件的形式保存描述了数据的变更顺序binlog还包括了每个更新语句的执行时间信息binlog主要作用是用于恢复数据因此binlog对于灾难恢复和备份恢复来说至关重要。相当于oracle数据库中的archivelog文件。binlog还用于实现mysql主从复制。二进制日志文件记录了数据库的变更过程对于数据库的安全性和完整性起着至关重要的作用。2)位置和格式当用--log-bin[file_name]选项启动时mysqld开始将数据库变更情况写入日志文件。如果没有给出file_name值默认名为主机名-bin。如果给出了文件名但不包含路径则文件默认被写入datadir参数指定路径。mysqld将在每个binlog日志名后添加一个数字扩展名每次要启动服务器或刷新日志时该数字将会增加如果当前日志大小达到了max-binlog-size参数设置的值那么mysqld会自动创建新的二进制日志。mysqld还将创建一个binlog索引文件其中包含了所有binlog文件的文件名默认该索引文件与binlog文件名相同扩展名为.index。当mysqld运行时不可以手工编辑该索引文件这样可能导致mysqld异常。当rm删除binlog后你也许不得不手工编辑索引文件。3)binlog日志格式mysql有两种记录命令方式一种是语句级(binlog_formatstatement)一种是行级(binlog_formatrow)建议命令格式设置为混合模式(binlog_formatmixed)这在大部分模式下试用它在一般情况下试用语句级记录但是在一些特殊情况下试用行级记录这增加了复制的健壮性。Mysq5.5中二进制日志格式分为3种statementrow和mixed可以在启动数据库时通过参数--binlog_format进行设置这3种格式区别如下3.1)statementMysql5.1之前的版本都采用这种方式binlog日志中记录的都是语句(基于语句级的日志里包含了原始执行的sql语句还有其他信息如执行语句的线程id语句执行的时间戳执行所消耗时长)每一条对数据库造成修改的语句都会记录在日志中通过mysqlbinlog工具可以清晰的看到每条语句的文本。主从复制时从库的日志解析为原文本在从库中执行。优点是日志量小清晰易懂对io压力小缺点是某些情况下slave的日志复制会出差。3.2)rowMysql5.1.11之后出现的。它将每一行变更记录到日志中而不是记录sql语句(记录行的更改信息而不是语句)。优点是记录每一行数据变化的细节不会出现slave模式下某些情况无法复制的情况。缺点是日志量大对io影响较大。通过mysqlbinlog默认看到的都是经过base-64编码的信息mysqlbinlog加参数-verbose(或-v)将会生成带注释的语句如果连续两次使用这个参数(如-v -v)则生成字段类型、长度、是否为null等属性信息。一般而言row模式更为健壮而statement级别如果应用了mysql的一些额外特性比如存储过程、触发器则可能导致复制异常所以如果使用语句级记录那么需要保持mysql数据库应用的简单性即只用核心功能即可。3.3)mixed这是目前mysql的默认binlog日志格式即混合了statement和row两种日志。默认情况下采用statement但是在一些特殊情况下采用row方式记录日志。此种模式利用statement和row的优点而尽量避开他们的缺点。注意可以在global和session级别对binlog日志的格式进行修改。4)日志文件的读取由于binlog以二进制方式存储不能直接读取因此需要使用mysqlbinlog工具进行日志分析语法mysqlbinlog log-file。例4.1)以binlog方式启动数据库[rootfaspdev bin]# ./mysqld_safe --usermysql --log-binbinlog-test4.2)执行dml语句mysql delete from t1;Query OK, 2 rows affected (0.02 sec)4.3)mysqlbinlog查看binlog内容[rootfaspdev data]# ../bin/mysqlbinlog binlog-test.000001/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;/*!40019 SET session.max_insert_delayed_threads0*/;/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;DELIMITER /*!*/;# at 4#161111 17:45:52 server id 1 end_log_pos 120 CRC32 0x5bfb8e4f Start: binlog v 4, server v 5.6.31-log created 161111 17:45:52 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG UJMlWA8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABQkyVYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUO1s/*!*/;# at 120#161111 17:47:29 server id 1 end_log_pos 199 CRC32 0x3f0abe26 Query thread_id1 exec_time0 error_code0SET TIMESTAMP1478857649/*!*/;SET session.pseudo_thread_id1/*!*/;SET session.foreign_key_checks1, session.sql_auto_is_null0, session.unique_checks1, session.autocommit1/*!*/;SET session.sql_mode1075838976/*!*/;SET session.auto_increment_increment1, session.auto_increment_offset1/*!*/;/*!\C utf8 *//*!*/;SET session.character_set_client33,session.collation_connection33,session.collation_server33/*!*/;SET session.lc_time_names0/*!*/;SET session.collation_databaseDEFAULT/*!*/;BEGIN/*!*/;# at 199#161111 17:47:29 server id 1 end_log_pos 287 CRC32 0x78e57a7e Query thread_id1 exec_time0 error_code0use test/*!*/;SET TIMESTAMP1478857649/*!*/;delete from t1/*!*/;# at 287#161111 17:47:29 server id 1 end_log_pos 318 CRC32 0x0b2d7e98 Xid 9COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;/*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;如果日志格式是rowmysqlbinlog解析的文件是无法读懂的字符可以加-v或-v -v进行查看。5)默认情况下并不是每次写入时都将binlog与磁盘同步因此操作系统或机器发生故障那么binlog中最后的语句有可能会丢失。要防止这种情况发生可以设置sync_binlog全局变量为n(1为最安全的值同时也是最慢的)使binlog每n此写入就与磁盘做同步。6)日志文件的删除查看binlog方法mysql show binary logs;6.1)方法1reset master删除所有的binlog日志文件删除后的日志文件从1开始编号。mysql reset master;Query OK, 0 rows affected (0.00 sec)6.2)方法2使用purge binary logs命令删除部分binlog文件mysql purge binary logs to mysql-bin.000001;Query OK, 0 rows affected (0.03 sec)6.3)方法3purge master logs to ‘mysql-bin.******’命令该命令删除‘******’编号之前的所有日志文件。6.4)方法4purge master logs before ‘yyyy-mm-dd hh24:mi:ss’命令该命令删除‘yyyy-mm-dd hh24:mi:ss’日期之前的所有日志。6.5)设置参数--expire_logs_days#,此参数含义是设置日志过期天数 过了指定天数后的日志将会被自动删除这样有利于减少DBA管理日志的工作量。7)mysqlbinlog解析u # at 199u #161111 17:47:29 server id 1 end_log_pos 287u # Query thread_id1 exec_time0 error_code08)binlog管理相关参数8.1)--binlog-do-dbdb_name:该参数告诉主服务器如果当前的数据库是db_name应该将其记录到binlog中其他没有被指定的数据库将被忽略可多次使用对多个数据库进行定义。8.2)--binlog-ignore-dbdb_name:改参数告诉主服务器如果当前数据库为db_name则不记录binlog其他没有指定的数据库变化将记录binlog中可多次使用对多个数据库进行定义。8.3)--innodb-sage-binlog:该参数经常和--sync-binlogN(没写N次日志同步磁盘)一起配合使用使得事务在日志中的记录更加安全。8.4)Set sql_log_bin0具有super权限的客户端可以通过此语句禁止将自己的语句记录二进制文件这个选项在某些环境下是有用的比如主主切换或者mysql版本升级但是使用一定倍加小心免得造成slave环境下的数据不一致。5、日志文件维护很多生产环境日志文件可以占用大量的磁盘空间因此需要对日志文件进行定期清理操作mysql服务器当然也不例外。l 对于错误日志文件一般不会太大因此生产上注意一下就可以了。l 通用日志一般生产不开启因此也不存在清理一说。l 慢查询日志在慢查询很多的情况下可能变得很大可以手工清理或编写脚本管理。l binlog日志文件可以设置合适的过期策略如expire-logs-days10表示设置binlog过期时间为10天。expires-logs-days设置会在运行flush logs命令后触发删除过期日志注意不能使用rm删除binlog日志文件这可能导致你执行日志清理命令失败。mysql 5.6可以设置保留binlog日志文件大小避免磁盘对填满。二、innodb数据文件和日志文件1、以下简单介绍mysql数据目录下的文件1)db.opt数据库结构定义和设置2)*.frm数据表的结构定义3)*.MYDMyisam引擎表数据4)*.MYIMyisam引擎索引数据5)ibdata*Innodb表空间数据文件如果将innodb_file_per_table设置为1那么innodb数据表可以各自存储为一个文件这种模式成为独立表空间。如果将innodb_file_per_table设置为0那么innodb数据则可以统一存储在一个共享表空间里。6)ib_logfile*Innodb重做日志文件7)*.idbInnodb数据和索引。8)*.trg触发器2、innodb数据文件和日志文件如果没有再参数文件中指定innodb相关参数那么mysql将会在data目录下创建一个大小为10m的ibdata1文件和大小5m的两个ib_logfile*文件。这种设置对于生产而言太小一般建议设置logfile为256m数据文件初始大小1g~5g并且设置自动增长。参数配置样例Innodb_data_file_pathibdata1:1000m:autiextendInnodb_log_file_size256mInnodb_data_file_path的值可以设置为1个或多个列表。如果要命名一个以上的数据文件请用“;”分隔它们。其语法格式为Innodb_data_file_pathdatafile01:size[;datafile02:size;...]。例如Innodb_data_file_pathdatafile01:2000m;datafile02:2000m;datafile03:2000m:autoextend其中autoextend及其后属相只能用于Innodb_data_file_path列表中最后一个数据文件针对于最后一个数据文件如果innodb共享表空间耗尽后就会扩展最后一个数据文件默认扩展大小为8m。1)innodb独立表空间和共享表空间共享表空间使用简单方便维护但是也存在缺点使用贡献表空间明显的缺点是不能快速回收删除大表的空间io操作可能会消耗更多的资源等待。而独立表空间是大部分DBA推荐使用的方式它恰好在这一点弥补了共享表空间不足。Innodb下使用独立表空间每个表都有它自己的表空间。开启独立表空间方式在my.cnf文件中添加如下参数重启mysql实例即可生效[mysqld]innodb_file_per_table重启实例后innodb将会把新创建的表存储到数据目录下的文件tb1_name.ibd中这类似域myisam存储引擎所做的事但是myisam把表分成数据文件tb1_name.MYD和索引文件tb1_name.MYI。对于innodb数据和索引会放在一起都存放到.ibd文件中不过tb1_name.frm文件照旧会被创建。在my.cnf文件中删除了innodb_file_per_table行重启实例后innodb新创建的表会存储在共享表空间里就是说innodb_file_per_table这个参数仅影响表的创建位置。innodb使用独立表空间也仍然有一部分数据在共享表空间里。相对于myisam引擎管理的表我们不能随意移动.ibd文件这是因为表定义是被存储在innodb共享表空间里的而innodb必须保持事务id和事务日志顺序号的一致性。2)innodb增加数据文件手动增加一个innodb数据文件时需要重启实例。为innodb存储引擎管理表添加数据文件需要修改my.cnf参数文件把innodb_data_file_path参数指定添加的数据文件及其初始大小和相关参数。注意当你添加一个数据文件到innodb_data_file_path时需要确定它不存在当你重启实例时innodb会自动创建这个数据文件。添加innodb数据文件需要首先计算当前innodb_data_file_path中最后一个数据文件大小然后估计一个近似值给它再添加新的数据文件。Innodb数据文件只有最后一个可以设置为自动扩展。3)改变innodb重做日志大小不要试图通过直接更改配置文件来设置innodb事务日志大小这会导致不能启动数据库。如果想要改变innodb事务日志的数量和大小必须正常关闭mysql实例然后复制旧日志文件到一个安全的地方做备份然后从日志文件目录删除所有的旧日志文件之后更改my.cnf参数文件改变日志文件配置并再次启动mysql实例。Mysqld在启动时会发现没有重做日志文件然后告诉你它正在创建一个新的日志文件。更改innodb事务日志步骤l 干净关闭mysql实例l 使用mv命令转移innodb重做日志文件。l 修改my.cnf配置文件更改innodb_log_file_sizel 启动mysql实例注意旧版本mysql服务器重做日志总大小不能超过4g在mysql5.6以后限制扩展到521g。4)innodb的undo区域Undo区域也称为undo空间或undo表空间是innodb设计的一块特殊存储区域它保存了被活动事务更改的数据的副本(镜像)如果一个事务需要查看原来的数据(满足一致性读)那么可以从undo区域中获得未被更改的数据。默认情况下undo区域也是在innodb共享表空间内。Mysql5.6以后提供了选项可以把undo表空间独立到表空间这样就可以进行热点块的io优化提升性能。如果undo表空间暴涨出现这种情况可能是因为负载比较大或者存在长时间未提交的事务(长事务)。对于写操作比较频繁的应用innodb清理线程的速度可能会跟不上从而导致undo表空间越来越大可以通过设置innodb_max_purge_lag参数来避免undo空间过度增大。Innodb事务系统维持一个事务列表该列表记录被update或delete操作表示为删除的索引记录。这个列表的长度为purge_lag。当purge_lag超过了nnodb_max_purge_lag时每个dml语句都被延迟一定时间。Undo空间里保存了数据的前镜像这是满足一致性读的根本原因同时也是灾难恢复的重要角色(即回滚)。3、临时文件Mysql临时文件作用类似但不等同于oracle临时表空间。Mysql使用环境变量TMPDIR的值作为保存临时文件的目录。如果未设置则默认使用系统的默认值一般为/tmp/var/tmp/usr/tmp。可以使用--tmpdir参数在启动时指定mysql临时目录或者在my.cnf文件中指定tmpdir参数进行分配。如果mysql服务器正在作为复制服务器使用那么不能将mysql临时路径指向基于内存的文件系统目录或者主机重启会清空的目录否则可能造成复制失败。Mysql隐含创建所有的临时文件。进行排序操作时mysql会使用一个或多个临时文件。一定要保证临时目录空间够用。一些普通的数据库操作都有可能创建临时文件用于维护数据库内的数据结构alter table会在原表所在目录下创建临时表。4、mysql套接字文件服务器用来与本地客户端进行通信的linux套接字文件(也称为socket文件)默认位置是/tmp/mysql.sock。Scoket文件不建议放置在/tmp目录下可以将其单独指定存储位置我们可以通过my.cnf配置文件指定mysql socket文件存储路径下面是my.cnf中定义socket储存路径[mysqld]Socket/opt/mysql/mysql.socket[client]Socket/opt/mysql/mysql.socketMysql服务器一定要保留root的除了socket外的其他登录方式比如通过127.0.0.1的root登录账号以防止socket文件被删除导致无法登录mysql数据库的窘境。三、MySQL灾难恢复过程1、预写日志和undo表空间mysql靠预写日志(innodb事务日志)来保证数据持久性也就是说数据文件不会先被写入而是先写日志文件。innodb脏数据存在于innodb_buffer_pool里它会按照一定的机制批量写入磁盘这样可以提高吞吐率。mysql宕机后的实例恢复过程首先mysql找到事务日志中的某个点从该点开始重做redo中的事务在应用了所有redo日志后根据undo区域确定哪些事务需要回滚然后回滚哪些没有提交的事务简单理解mysql灾难恢复过程就是根据redo重做日志然后根据undo回退事务。innodb事务日志很大程度上决定了数据的安全性日志的持久性决定了灾难恢复后丢失多少数据mysql可以通过参数控制commit时写入事务日志的频率通常有以下3种情况1)innodb_flush_log_at_trx_commit1每次commit时都写入磁盘这样理论上发生故障时我们只丢失一个事务。2)innodb_flush_log_at_trx_commit2每次commit只写日志缓存到操作系统缓冲但不刷新磁盘innodb每秒刷新磁盘一次所以故障丢失的是最近1秒的数据。生产环境建议这样设置。3)innodb_flush_log_at_trx_commit0每秒把日志缓冲的内容写入到日志文件并且刷新到磁盘但commit时什么也不做。2、双写缓冲数据文件的写操作可能会将块写坏mysql设计了一个数据存储区域双写缓冲innodb使用双写缓冲来确保数据的安全避免块损坏。双写缓冲是innodb表空间的一个特殊区域主要用于写入页的备份并且是顺序写入的。当innodb刷新数据时首先写入双写缓冲然后写入数据文件。这样既可确保所有写操作的原子性和持久性。崩溃重启后innodb会检查每个块的校验和判断块是否损坏如果写入双写缓冲的是坏块那么显然没有写入实际数据文件那么用数据文件的块恢复双写缓冲如果写入了双写缓冲但是数据文件中的是坏块那么使用双写缓冲中的块恢复实际数据文件中的块。这样的机制提供了双层的安全保障但是缺点是增加了io。对于读取操作innodb通过页校验和来保证数据的存取每页在内存中都先算好一个校验值放在文件头部写入的时候先写校验值读的时候也会校验一下校验值。四、mysql参数文件及参数修改方法MySQL数据库初始化参数由参数文件来设置如果没有设置参数文件mysql就按照系统中参数的默认值来启动。在windows和linux上参数文件可以被放在多个位置数据库启动时按照不同的顺序来搜索如果多个位置都有参数文件则搜索顺序靠后的参数文件中的参数将覆盖前的参数。表linux下mysql参数文件读取顺序参数文件名目的/etc/my.cnf全局选项$MYSQL_HOME/my.cnf服务器相关选项Default-extra-file用--Default-extra-filepath选项指定的文件如果该文件存在的话~/.my.cnf用户相关选项Mysql安装上述顺序寻找参数文件如果多个文件同时存在那么文件中指定的后读取的选项要优先于先读取的选项所以数据目录或安装目录下的配置文件都有可能生效所以理论上在数据目录或安装目录下放置一个my.cnf文件即可。Mysql启动时可以指定--datadir用于指定数据路径但是此选项对于查询参数文件无效因为在读取--datadir之前mysql服务器就已经读取了配置文件了。多次指定一个选项后出现的将覆盖先出现的值因此命令行中通过set修改的选项优先级最高。通过如下命令可以列出mysqld读取参数文件的优先顺序该命令不能在生产环境随意运行$mysqld --verbose --help |grep my.cnf/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnfMysql参数可以在3个级别进行更改1)session级别set2)全局级别set global3)永久级别my.cnf附一份生产可用的mysql配置文件(my.cnf)示例(测试库mysql 5.6.24)[client]#客户端选项设置port 3306socket /opt/mysql-5.6.24/data/mysql.socket#设置客户端和连接字符集default_character_set utf8[mysqld]#服务器端选项设置# innodb设置default_storage_engine InnoDBinnodb_strict_mode 1innodb_buffer_pool_size 256M #mysql数据库服务器该值可设为物理内存的50%-80%之间innodb_stats_on_metadata 0innodb_file_format Barracudainnodb_data_file_pathibdata1:10m:autoextendinnodb_flush_method O_DIRECTinnodb_log_files_in_group 2innodb_log_file_size 16Minnodb_log_buffer_size 8Minnodb_file_per_table 1innodb_max_dirty_pages_pct 60innodb_io_capacity 200innodb_flush_log_at_trx_commit 2# 基本设置basedir /opt/mysql-5.6.24datadir /opt/mysql-5.6.24/dataport 3306server_id 19900315tmpdir /opt/mysql-5.6.24/tmpsocket /opt/mysql-5.6.24/tmp/mysql.socketPid-file /opt/mysql-5.6.24/data/mysql.pidskip-name-resolve 1skip-external-locking 1max_connect_errors 500max_connections 1000relay-log mysql-relay-binlog-slave-updates 1skip_slave_start 1read_only 0key_buffer_size 8Mtmp_table_size 8Mmax_heap_table_size 8Mquery_cache_type 0query_cache_size 0thread_cache_size 1024open_files_limit 65535table_open_cache 1024max_allowed_packet 16Mgtid-mode ONenforce-gtid-consistency 1lower_case_table_names1log-bin-trust-function-creatorsplugin-load rpl_semi_sync_mastersemisync_master.so;rpl_semi_sync_slavesemisync_slave.soreplicate-ignore-table mysql.ibbackup_binlog_markerslave-skip-errors ddl_exist_errorsrelay-log-info-repository TABLErelay_log_recovery 1master_info_repository TABLE# 服务器字符集设置character_set_server utf8# error log设置log_error /opt/mysql-5.6.24/data/mysql.err# slow log设置slow_query_log 1slow_query_log_file /opt/mysql-5.6.24/data/mysql-slow.loglong_query_time 0.5# binlog设置binlog_format mixedlog-bin /opt/mysql-5.6.24/logs/mysql-binsync_binlog 2max_binlog_size 16Mexpire_logs_days 10#others设置join_buffer_size 128Msort_buffer_size 2Mread_rnd_buffer_size 2Msql_modeNO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
