MYSQL物理结构

来源：互联网发布：怎样快速提升淘宝心编辑：程序博客网时间：2024/06/11 21:22

Mysql的物理结构

日志文件
1. error log:
  1. 记录mysql启动,关闭和运行时产生的重大的错误的信息;
  2. 如果mysqld警告一个表需要自动的检查或者修复,也会记录一个错误日志;
  3. 可以使用–log-error=file_name选项来指定错误日志文件,如果没有指定,则系统默认在data目录下生产一个hostname.err的文件;
  4. 也可以在配置文件中配置log_error变量;
  5. 查询log_error的位置:>show variables like ‘log_error’;查看主机名:>system hostname;
2. binary log:
  1. 记录所有对数据库更新和潜在的更新语句(一个delete语句,但是没有影响的行),语句以事件(event)的方式存储,同时也包含了更新语句执行的时间信息;
  2. 它不记录那些不修改任何数据的语句,如果想要记录所有的语句,可以使用query log;
  3. 它主要的目的是在做还原操作时尽可能全的更新数据库,因为它包含在一次备份后的所有更新操作.它同样被用在master replication server中作为一个记录发送给slave servers;
  4. 打开二进制日志会损失1%的性能,但是它带来的好处远远超过这些;
  5. 查看二进制日志是否打开,需要查看log_bin参数是否是ON:>show variables like ‘binlog_format’;
  6. 命令行参数
    1. –log-bin=filename:记录二进制日志文件的位置,尽量指定路径名,如果不指定的话则保存在数据目录;
    2. –log-bin-index=file:记录二进制日志文件索引的位置,保存了日志文件名;
    3. –max_binlog_size:单个文件最大多少;
    4. –binlog-do-db=db_name:哪个数据库使用,只有这个数据库使用;
    5. –binlog-ignore-db=db_name:哪个数据库不使用,只有这个数据库不使用;
  7. 系统变量
    1. log_bin:日志的位置;
    2. binlog_cache_size:二进制日志缓存大小,是每一个连接进来的线程分配的大小,不是整个服务器的大小;
    3. max_binlog_cache_size:最大缓存大小;
    4. max_binlog_size:单个文件最大大小,超过此大小则再分配一个文件,但是一个事务必须在一个文件中,所以可能会稍大点;
    5. binlog_cache_use:当前连接使用的binlog缓存的事务的数量,使用show status like ‘binlog_cache_use’查看(show status命令显示了所有连接到mysql服务器的状态值);
    6. binlog_cache_disk_use:如果binlog_cache_use不够用,则在磁盘上缓存,应该尽量避免;
    7. binlog_do_db:设置master-slave时使用;
    8. binlog-ignore-db:设置哪个数据库不记录日志;
    9. sync_binlog:缓存与硬盘的同步频率(commit多少下同步一次,0表示服务器自动控制);
  8. 查看当前二进制文件的名称和大小,show binary/master logs;
  9. 如果不指定二进制日志文件的位置,默认存放在data文件夹下,日志文件是:mysql-bin.xxxxxxx,索引文件是mysql-bin.index;
  10. 如果要切换日志的话,执行flush logs命令;
  11. 初始化二进制日志系统,从新生成:reset master命令;
  12. 删除某个日志文件:purge binary logs [before ‘datetime’ / to ‘log_name’] 删除指定日期之前的和删除指定文件之前的日志文件;
  13. 设置日志文件的失效期:参数为expire_logs_days,set global expire_log_days=n,N天前的日志自动删除;
  14. 二进制日志的格式
    1. 查看格式:show [global] variables like ‘binlog_format’;
    2. 设置日志格式:set [global] binlog_format = statement|row|mixed;
    3. 使用mysqlbinlog程序打开;
3. general query log:
  1. 一般查询日志记录服务器运行期间所有操作的日志.当客户端连接或者断开的时候服务器会记录信息到日志中,并记录所有从客户端接收到的sql语句,它对于在客户端中排错和查看是哪个客户端发送的命令很有帮助;
  2. mysqld是按照接收到命令的方式记录语句的,这可能跟它们执行的顺序不同(这与二进制日志是有区别的,二进制日志是执行后记录).
  3. 在mysql5.1.6中可以使用–log选项启动和使用–log-output选项指定日志输出的位置,也可以输出到”Server Log Tables”表中.在5.1.6之前,开启此功能是用–log=file_name or -l file_name选项(之后版本使用–general-log选项),如果没有指定file_name,默认是在data目录下生产一个hostname.log文件;
  4. 服务器重新启动和log flush不会产生一个新的文件;
  5. 默认此功能关闭,通过show variables like ‘log’/’general_log’,log与general_log意思相同;
  6. 设置打开一般查询日志:set global log=1;(两个变量会同时打开关闭,打开之后立即生效);
  7. 可以在启动的时候指定–general-file选项或者在配置文件中指定general_log=1,general_file_log=/path;
  8. 一半不打开此日志功能,数据量太大,如果打开可以放到单独的磁盘中.
  9. 备份切换一般日志文件:
    1. 先备份:>mv hostname.log hostname.log.bak
    2. 切换日志组:mysqladmin flush-logs;
4. slow query log:
  1. 调优时使用,记录超出指定时间的sql语句;
  2. 慢查询日志主要记录执行时间超过long_query_time变量指定时间的sql语句,这个时间不包括获得锁的时间,只包含执行时间,系统默认时间是10s(long_query_time=10.0);
  3. 在mysql5.1.6版本可以指定mysqld的–long-slow-queries=file_name选项启动,在之后的版本使用–slow-query-log=0|1选项和–slow-query-file-log=file_name选项指定;
  4. 命令行参数:
    1. –log-slow-queries=file_name;指定慢查询日志文件
  5. 系统变量:
    1. low-query-log:开启慢查询功能,set global low-query-log =0|1;
    2. slow-query-file-log:指定慢查询日志文件,系统默认是在data目录下的hostname-slow.log文件;
    3. long_query_time:指定查询的最大时间,set global long_query_time=n;
    4. long_queries_not_using_indexes:不用索引的慢查询,默认是功能关闭的;
      1. 查看某个表是否有索引:> show index from t;
      2. 打开此功能:>set global long_queries_not_using_indexes =1;
      3. 查看变量:>show variables like ‘long_queries_not_using_indexes’;
  6. 分析慢日志文件的工具:mysqldumpslow,查看具体的帮助是用mysqldumpslow –help;
5. innodb redo log;
  1. 与innodb数据引擎相关;
  2. 用来实现灾难恢复(crash recovery),突然断电会导致innodb表空间中的数据没有写到磁盘上,通过执行redo log能够重新执行这些操作来恢复数据;
  3. 提升innodb的i/o性能,innodb引擎把数据和索引都载入到内存中的缓冲池中,如果每次休息数据和索引都需要更新到磁盘,必定会增加i/o请求,而且因为每次更新的位置都是随机的,磁头需要频繁的定位导致效率很低,所以innodb每处理完一个事务后只添加一条日志log,另外有一个线程负责智能的读取日志文件并批量更新到磁盘上,实现最高效的磁盘写入;
  4. 系统变量:
    1. innodb_log_buffer_size:日志缓冲区的大小;
    2. innodb_log_file_size:日志文件的大小;
    3. innodb_log_files_in_group:一组日志中有几个文件:
      1. 文件名为ib_logfileX(X从0开始一次增加);
      2. 先关闭数据库服务:>mysqladmin shutdown(mysql.server stop);
      3. 把data目录下的ib_logfile*文件移动走:>mv ib_logfile* /tmp;
      4. 在配置文件中添加innodb_log_files_in_group=n的参数;
      5. 启动数据库服务:>mysqld –defaults-file=./my.cnf –user=mysql(mysql.server start);
      6. 可以查看error log文件观察启动过程;
    4. innodb_log_group_home_dir:日志存放的性对路径(相对于$MYSQL_HOME/mysql/data目录,即datadir目录);
      1. 关闭服务器;
      2. 在配置文件中添加此参数,并指定路径;
      3. 启动服务器;
    5. innodb_flush_log_at_trx_commit:根据不同的数据安全级别去设定.
      1. 0:日志缓冲每秒一次的被写入到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事务提交不做任何修改;
      2. 1:每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新;
      3. 2:每个事务提交后,日志缓冲被写到日志文件,但不对日志文件做到磁盘操作刷新,对日志文件每秒刷新一次;
      4. 查看此变量:>show variables like ‘innodb_flush_log_at_trx_commit’;
    6. innodb_os_log_written:写入到文件日志的数据量,使用show status查询;
    7. innodb_os_log_fsyncs:写入到磁盘的次数,使用show status查询;
数据文件:MySql服务器使用磁盘空间有几种方式,主要是在服务器数据目录(datadir)下的数据库目录和文件,主要有以下几点:
1. 数据库目录:每一个数据库对应一个数据目录(datadir)下的目录,不管你创建哪种类型的表.比如,一个数据库目录可以包含MYISAM引擎的表,INNODB引擎的表或者混合的表;
2. .frm文件(Table Format Files):包含了表结构的描述信息,每一个表都有一个.frm文件在对应的数据库目录下.它与表使用的哪种引擎没有关系;
3. .MYD/.MYI:由MYISAM存储引擎在适当的数据库目录下创建的数据库数据文件和索引文件.
4. INNODB存储引擎有它自己的表空间和日志文件,表空间包含所有使用InnoDB引擎表的数据和索引信息,同样也包括了事务回滚所必须的undo logs.日志文件记录了提交过的事务的信息,用来防止数据丢失.默认情况下,表空间和日志文件保存在数据目录,默认的表空间文件名叫ibdata1,默认的日志文件名叫ib_logfile0和ib_logfile1.(可以为每一个使用InnoDB引擎的表配置一个表空间,这种情况下,InnoDB给指定的表创建表在表数据库目录创建表空间)
  1. 查询表空间模式:>show variables like ‘innodb_file_per_table’ ON:单独表空间,OFF:共享表空间;
  2. 如果要修改这个值的话要关闭服务器,修改配置文件,启动服务这几步;
  3. 如果使用单独表空间模式的话会在相应的数据库目录创建tablename.frm(表结构文件)和tablename.ibd文件(数据和索引文件);
5. 服务器日志文件和状态文件:这些文件包含服务器上执行过的语句信息,日志被用于复制和数据恢复,获得优化查询性能的信息和误操作信息;
Relication相关文件（master info,relay info）
其他小文件

阅读全文

0 0