mysql数据库的参数以及性能的调配Mysql应用
|
《mysql数据库的参数以及性能的调配Mysql应用》要点： MySQL是一个关系型数据库管理系统,支持FreeBSD、Linux、MAC、Windows等多种操作系统与其他的大型数据库例如Oracle、DB2、SQL Server等相比功能稍弱一些MYSQL学习 2、支持常见的SQL语句规范MYSQL学习 4、良好的运行效率,有丰富信息的网络支持MYSQL学习 易用性比较MYSQL学习 ???从安装方面来说,MySQL安装包大小仅100MB左右,与那几大商业数据库相比完全不是一个数量级.它的安装也比Oracle等商业数据库容易很多,不论是通过已经编译好的二进制分发包,还是通过源码编译安装,都非常简单.MYSQL学习 MySQL一直以来奉行一个原则,那就是在保证足够稳定性的前提下,尽可能地提高自身的处理能力.也就是说,在性能和功能方面,MySQL第一考虑的要素主要还是性能,MySQL希望能够在满足客户99%的需求的前提下,将剩余的所有精力都用来努力提高系统性能,而不希望自己是一个比其他任何数据库的功能都要强大的产品.MYSQL学习 总体来说,MySQL数据库在发展过程中一直追求三项原则：简单、高效、可靠.MYSQL学习 二：mysql架构组成MYSQL学习 1)日志文件：主要包含{错误日志、查询日志、慢查询日志、事物日志、二进制日志}MYSQL学习 日志是mysql数据库的重要组成部分.记录mysql数据库运行期间发生的变化,如数据库的客户端连接状况、sql语句执行情况和错误信息.当数据库遭到损坏时,可以通过日志查看文件记录的出错的原因,并且可以通过日志文件进行数据恢复.MYSQL学习 错误日志：Error LogMYSQL学习 在mysql数据库中,错误日志功能是默认开启的.默认情况下,错误日志存储在mysql数据库的数据目录中.错误日志文件通常的名称为hostname.err.其中,hostname表示服务器主机名.MYSQL学习 错误日志所记录的信息是可以通过log-error和log-warnings来定义的,其中log-error是定义是否启用错误日志的功能和错误日志的存储位置,log-warnings是定义是否将警告信息也定义至错误日志中.记录的内容信息包括：服务器启动和关闭过程中的信息(未必是错误信息,如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等)、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息MYSQL学习 两组命令：分别查看系统设置和运行状态：MYSQL学习 1、查看系统设置：? 2、运行状态：? 那么接下来修改系统配置：在主配置文件中MYSQL学习 vi /etc/my.cnfMYSQL学习 查看mysql的版本MYSQL学习
或MYSQL学习
一般而言,日志级别的定义没有会话变量都只是在全局级别下进行定义MYSQL学习 错误日志的状态：MYSQL学习
log-error定义为错误日志文件路径MYSQL学习 log-error-verbosityMYSQL学习 The MySQL error log has received some attention in MySQL 5.7,with a new setting called?log_error_verbosity.MYSQL学习 更改错误日志位置可以使用log-error来设置如下：MYSQL学习 在主配置文件中：?vi /etc/my.cnfMYSQL学习 log-error = /usr/local/mysql/data/mysqld.errMYSQL学习 查看mysql的错误日志内容：MYSQL学习
在工作中有时候希望将错误日志做备份,并且重新记录,这时候可以使用mysql的flush logs刷新日志进行生成新的日志文件.备份文件为.beifen结尾MYSQL学习 删除错误日志：MYSQL学习 在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志,以保证mysql服务器上的硬盘空间.mysql数据库中,可以使用mysqladmin命令开启新的错误日志.mysqladmin命令的语法如下：mysqladmin –u root –pflush-logs也可以登录mysql数据库中使用FLUSHLOGS语句来开启新的错误日志.MYSQL学习 在mysql5.5.7之后：服务器将关闭此项功能.只能使用重命名原来的错误日志文件,手动冲洗日志创建一个新的：方式如下：MYSQL学习
更多信息请查阅官方文档：http://dev.mysql.com/doc/refman/5.5/en/error-log.htmlMYSQL学习 67二进制日志：Binary Log & Binary Log IndexMYSQL学习 二进制日志,俗称Binary Log,主要用于记录修改数据或有可能引起数据改变的mysql语句；并且记录语句的发生时间、执行时长、操作数据....一般情况下大小体积上限为1GMYSQL学习 当我们通过“log-bin=file_name”打开了记录的功能之后,MySQL?会将所有修改数据库数据的query?以二进制形式记录到日志文件中.当然,日志中并不仅限于query?语句这么简单,还包括每一条query?所执行的时间,所消耗的资源,以及相关的事务信息,所以binlog是事务安全的.MYSQL学习 主：如果log-bin日志不开启的话那么将无法做主从复制MYSQL学习 和错误日志一样,binlog记录功能同样需要“log-bin=file_name”参数的显式指定才能开启,如果未指定file_name,则会在数据目录下记录为mysql-bin.******(*代表0～9?之间的某一个数字,来表示该日志的序号).MYSQL学习 二进制的开启：MYSQL学习 当前是关闭状态MYSQL学习
可以通过直配置文件开启：MYSQL学习
之后重启mysql服务MYSQL学习 再次查看mysql服务已经启动：MYSQL学习
binlog还有其他一些附加选项参数：MYSQL学习 “max_binlog_size”设置binlog的最大存储上限,一般设置为512M或1G,一般不能超过1G当日志达到该上限时,MySQL?会重新创建一个日志开始继续记录.不过偶尔也有超出该设置的binlog产生,一般都是因为在即将达到上限时,产生了一个较大的事务,为了保证事务安全,MySQL?不会将同一个事务分开记录到两个binlog中.MYSQL学习 ?MYSQL学习 binlog-ignore-db=db_name”与“binlog-do-db=db_name”完全相反,它显式指定忽略某个(db_name)数据库的binlog记录,当指定了这个参数之后,MySQL?会记录指定数据库以外所有的数据库的binlog.MYSQL学习 mysql-bin.index文件(binary log index)的功能是记录所有Binary Log?的绝对路径,保证MySQL?各种线程能够顺利的根据它找到所有需要的Binary Log?文件.MYSQL学习 binlog_cache_size =32768???#默认值32768?binlog_cache_size：一个事务,在没有提交(uncommitted)的时候,产生的日志,记录到Cache中；等到事务提交(committed)需要提交的时候,则把日志持久化到磁盘.一般来说,如果我们的数据库中没有什么大事务,写入也不是特别频繁,2MB～4MB是一个合适的选择.但是如果我们的数据库大事务较多,写入量比较大,可与适当调高binlog_cache_size.MYSQL学习 binlog_cache_size?：一个事务,在没有提交(uncommitted)的时候,记录到Cache中；等到事务提交(committed)需要提交的时候,则把日志持久化到磁盘.?MYSQL学习 概念解释：MYSQL学习 事务表支持将批处理当做一个完整的任务统一提交或回滚,即对包含在事务中的多条语句要么全执行,要么全部不执行 ?MYSQL学习 log-bin = mysql-bin#指定binlog的位置,默认在数据目录下.MYSQL学习 概念解释：mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication,SBR),基于行的复制(row-based replication,RBR),混合模式复制(mixed-based replication,MBR).对应的,binlog的格式也有三种：STATEMENTROWMIXED.MYSQL学习 STATEMENT模式(SBR)MYSQL学习 每一条会修改数据的sql语句会记录到binlog中.优点是并不需要记录每一行的数据变化,减少了binlog日志量,节约IO,提高性能.MYSQL学习 缺点：MYSQL学习 某些情况下会导致master-slave中的数据不一致(如sleep()函数,last_insert_id(),以及user-defined functions(udf)等会出现问题)MYSQL学习 不记录每条sql语句的信息,仅需记录哪条数据被修改了,修改成什么样了.MYSQL学习 缺点：MYSQL学习 是会产生大量的日志,让日志暴涨.MYSQL学习 以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式.即交替使用行和语句、由mysql服务器自行判断.MYSQL学习 其中基于行的定义格式数据量会大一些但是可以保证数据的精确性MYSQL学习 注：在生产环境下多使用MBR模式,虽然I/O使用增大,但对数据安全性比较高MYSQL学习 ?MYSQL学习 通过编辑my.cnf中的log-bin选项可以开启二进制日志；形式如下：MYSQL学习 log-bin?= /usr路径MYSQL学习 其中,DIR参数指定二进制文件的存储路径；filename参数指定二级制文件的文件名,其形式为filename.number,number的形式为000001、000002等.每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件,这些日志文件的number会不断地递增.除了生成上述的文件外还会生成一个名为filename.index的文件.这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引MYSQL学习 ?MYSQL学习
查看二进制日志：MYSQL学习 二进制日志的定义方式为二进制格式；使用此格式可以存储更多的信息,并且可以使写入二进制日志的效率更高.但是不能直接使用查看命令打开并查看二进制日志.MYSQL学习
当前使用的二进制文件及所处位置MYSQL学习
查看当前二进制文件的信息：MYSQL学习 查看二进制日志信息的命令：MYSQL学习 语法格式：SHOW BINLOG EVENTS[IN?'log_name'] [FROM pos] [LIMIT [offset,] row_count]MYSQL学习 #查看所有的二进制信息MYSQL学习 mysql> show binlog eventsG;MYSQL学习 #查看指定日志的二进制信息MYSQL学习 #从指定的事件位置开始MYSQL学习 mysql> show binlog events?in 'log.000002' from 1215G;MYSQL学习 ?MYSQL学习
由于无法使用cat等方式直接打开并查看二进制日志；所以必须使用mysqlbinlog命令.但是当正在执行mysql读写操作时建议不要使用此打开正在使用的二进制日志文件；若非要打开可flush logs.mysqlbinlog命令的使用方式：MYSQL学习
删除二进制日志信息：MYSQL学习 二进制日志会记录大量的信息(其中包含一些无用的信息).如果很长时间不清理二进制日志,将会浪费很多的磁盘空间.但是,删除之后可能导致数据库崩溃时无法进行恢复,所以若要删除二进制日志首先将其和数据库备份一份,其中也只能删除备份前的二进制日志,新产生的日志信息不可删.也不可在关闭mysql服务器之后直接删除因为这样可能会给数据库带来错误的.若非要删除二进制日志需要做如下操作：导出备份数据库和二进制日志文件进行压缩归档存储.删除二进制文件的方法如下：MYSQL学习 方法1：根据文件或时间点来删除二进制日志：MYSQL学习 语法形式：MYSQL学习 mysql> PURGE { BINARY | MASTER } LOGS {TO?'log_name'?| BEFORE datetime_expr }MYSQL学习 其中TO'log_name'表示把这个文件之前的其他文件都删除掉,也可使用BEFORE?datetime_expr指定把哪个时间之前的二进制文件删除了.MYSQL学习 删除所有的二进制日志(慎用)：MYSQL学习 使用RESET MASTER语句可以删除所有的二进制日志.该语句的形式如下：MYSQL学习
事务日志(InnoDB特有的日志)可以帮助提高事务的效率.使用事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝,再把修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘.事务日志采用追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多.事务日志持久以后,内存中被修改的数据在后台可以慢慢的刷回到磁盘.目前大多数的存储引擎都是这样实现的.MYSQL学习 如果数据的修改已经记录到事务日志并持久化,但数据本身还没有写回磁盘,此时系统崩溃,存储引擎在重启时能够自动恢复这部分修改的数据.具有的恢复方式则视存储引擎而定.MYSQL学习 一般情况下,mysql会默认提供多种存储引擎,你可以通过下面的查看: 注：MYSQL学习 create table?库名.表名?engine = innodb; 查看事务日志的定义：MYSQL学习 mysql> show global variables like '%log%';MYSQL学习 显示结果：MYSQL学习 | innodb_flush_log_at_timeout| 1 ??|MYSQL学习 | innodb_flush_log_at_trx_commit ?| 1 ??#在事务提交时innodb是否同步日志从缓冲区到文件中,当这个值为1(默认值)之时,在每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新但这种方式最安全；如果设为2,每次提交事务都会写日志,但并不会执行刷的操作.每秒定时会刷到日志文件.要注意的是,并不能保证100%每秒一定都会刷到磁盘,这要取决于进程的调度.每次事务提交的时候将数据写入事务日志,而这里的写入仅是调用了文件系统的写入操作,而文件系统是有?缓存的,所以这个写入并不能保证数据已经写入到物理磁盘.设置为0MYSQL学习 注：刷写的概念MYSQL学习 所以,通常设为0和2的时候,在崩溃或断电的时候会丢失最后一秒的数据,因为这个时候数据只是存在于操作系统缓存.之所以说“通常1秒的数据的情况,比如说执行flush操作的时候阻塞了.MYSQL学习 总结MYSQL学习 ????????????|MYSQL学习 | innodb_locks_unsafe_for_binlog| OFF ?????????????????????????????????????|MYSQL学习 | innodb_log_buffer_size| 16777216 ????????????????????????????????|MYSQL学习 | innodb_log_checksums ???????????????????| ON ??????????????????????????????????????|MYSQL学习 | innodb_log_compressed_pages| ON ??????????????????????????????????????|MYSQL学习 | innodb_log_file_size| 50331648 ?#日志文件大小????????????????????????????|MYSQL学习 | innodb_log_files_in_group| 2?#?DB中设置几组事务日志,默认是2???????????????????????????????????|MYSQL学习 | innodb_log_group_home_dir| ./#定义事务日志组的位置,此位置设置默认为MySQLdatadir??????????????????????????????????????|MYSQL学习 每个事务日志都是大小为50兆的文件(不同版本的mysql有差异)：MYSQL学习 在mysql中默认以ib_logfile0,ib_logfile1名称存在MYSQL学习 4、慢查询日志：slow query logMYSQL学习 顾名思义,慢查询日志中记录的是执行时间较长的query,也就是我们常说的slowquery.MYSQL学习 慢查询日志采用的是简单的文本格式,可以通过各种文本编辑器查看其中的内容.其中MYSQL学习 记录了语句执行的时刻,执行所消耗的时间,执行用户,连接主机等相关信息.MYSQL学习 慢查询日志的作用：MYSQL学习 慢查询日志是用来记录执行时间超过指定时间的查询语句.通过慢查询日志,可以查找出哪些查询语句的执行效率很低,以便进行优化.一般建议开启,它对服务器性能的影响微乎其微,但是可以记录mysql服务器上执行了很长时间的查询语句.可以帮助我们定位性能问题的.MySQL?还提供了专门用来分析满查询日志的工具程序mysqldumpslow,用来帮助数据库管理人员解决可能存在的性能问题.MYSQL学习 查看慢查询日志的定义：MYSQL学习 启动和设置慢查询日志：MYSQL学习 方法1：通过配置文件my.cnf开启慢查询日志：MYSQL学习 注：在不同的mysql版本中,开启慢查询日志参数不太一样,不过都可以通过?show variables like "%slow%"?和show variables like "%long%"查看出来.MYSQL学习 其中：MYSQL学习 slow_query_log：?off关闭状态?(0) on开启状态(1) long_query_time选项来设置一个时间值,时间以秒为单位,可以精确到微秒.如果查询时间超过了这个时间值(默认为10秒),这个查询语句将被记录到慢查询日志中,0); font-family: Arial; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">设置为0的话表示记录所有的查询.MYSQL学习 注：如果不指定存储路径,慢查询日志默认存储到mysql数据库的数据文件下,如果不指定文件名,默认文件名为hostname-slow.logMYSQL学习 修改my.cnf文件：MYSQL学习
mysql>set?globalslow_query_log=1;? #开启慢查询日志MYSQL学习 Query OK,0 rowsaffected (0.35 sec)MYSQL学习 mysql>setsession long_query_time=0.0001; #更改时间(当前session中,退出则重置)MYSQL学习 mysql>set?globallong_query_time=0.0001; #更改时间(全局中,重启服务则重置)MYSQL学习 mysql> SHOWVARIABLES LIKE?'long%';? #查询定义时间MYSQL学习 查看慢查询日志MYSQL学习 查看文件内容命令如cat直接查看慢日志文件MYSQL学习 数据文据??(在这里主要介绍myisam和innodb的区别以及功能)MYSQL学习 在MySQL?中每一个数据库都会在定义好(或者默认)的数据目录下存在一个以数据库名字命名的文件夹,用来存放该数据库中各种表数据文件.不同的MySQL?存储引擎有各自不同的数据文件.如MyISAM用“.MYD”作为扩展名,Innodb用“.ibd”?MYSQL学习 如何查看你的mysql现在已提供什么存储引擎:
你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): 另外换可以在创建表的时候在表名的后面跟engine=innodb?可以改变表的引擎MYSQL学习 create table?库名.表名?engine = innodbMYSQL学习 可以在文件目录当中查看创建的文件格式MYSQL学习 查看mysql存储引擎命令,在mysql>提示符下搞入show engines;字段?Support为:Default表示默认存储引擎??MYSQL学习
1、“.frm”MYSQL学习 MyISAM数据库表文件：.MYD文件：表数据文件；.MYI文件：索引文件MYSQL学习 myisam专门存放存储引擎的专用文件MYSQL学习 ?MYSQL学习 .ibd文件：单表表空间文件,每个表使用一个表空间文件(file per table),存放用户数据库表数据和索引.MYSQL学习 .idb和ibdata的区别：MYSQL学习 共享表空间： 独立表空间：MYSQL学习 优点：
那么修改下主配置文件来开启共享表空间MYSQL学习 可以先?du -h ibdata1?查看下MYSQL学习
?MYSQL学习 登录mysql执行mysql> show variables like '%innodb_file_per_table%';MYSQL学习 这时新建的表就会使用共享表空间了.MYSQL学习 创建一个数据库testdb并新建一个表MYSQL学习 ?MYSQL学习 create ?procedure ?test() ========>?创建test文件MYSQL学习 declare i int；=========> ?通告i的类型MYSQL学习 while ?i < 100000 do =====> i的值如果小于100000MYSQL学习 set i = i +1 ========> ?每执行一次之后的值加1直到为99999MYSQL学习 end &&结束MYSQL学习
查看表在表空间占用情况：MYSQL学习 Replication相关文件：MYSQL学习 1)master.info?文件：MYSQL学习 master.info?文件存在于Slave?端的数据目录下,里面存放了该Slave?的Master?端的相关信息,包括Master?的主机地址,连接用户,连接密码,连接端口,当前日志位置,已经读取到的日志位置等信息.MYSQL学习 2)relay log?和relay log indexMYSQL学习 mysql-relay-bin.xxxxxn文件用于存放Slave?端的I/O?线程从Master?端所读取到的Binary Log?信息,然后由Slave?端的SQL?线程从该relay log?中读取并解析相应的日志信息,转化成Master?所执行的SQL?语句,然后在Slave?端应用.MYSQL学习 mysql-relay-bin.index文件的功能类似于mysql-bin.index,同样是记录日志的存放位置的绝对路径,只不过他所记录的不是Binary Log,而是Relay Log.MYSQL学习 3)relay-log.info?类似于master.info,它存放通过Slave?的I/O?线程写入到本地的relay log?的相关信MYSQL学习 息.供Slave?端的SQL?线程以及某些管理操作随时能够获取当前复制的相关信息.MYSQL学习 ?MYSQL学习 1)system config fileMYSQL学习 MySQL?的系统配置文件一般都是my.cnf,默认存放在"/etc"目录下,my.cnf文件中包含多种参数选项组(group),每一种参数组都通过中括号给定了固定的组名,如“[mysqld]”组中包括了mysqld服务启动时候的初始化参数,“[client]”组中包含着客户端工具程序可以读取的参数.MYSQL学习 2)pid fileMYSQL学习 pid file?是mysqld应用程序环境下的一个进程文件存放自己的pid号MYSQL学习 3)socket fileMYSQL学习 socket?文件也是在Unix/Linux?环境下才有的,用户在Unix/Linux?环境下客户端连接可以不通过TCP/IP?网络而直接使用Unix Socket?来连接MySQL.MYSQL学习 mysql有两种连接方式,常用的一般是tcp
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
