初始化选项文件:
对于UNIX/Linux平台,MySQL程序默认会按照如下顺序扫描下列路径,并使用找到的第一个匹配的选项文件
/etc/my.cnf
/etc/mysql/my.cnf
SYSCONFDIR/my.cnf通过CMake源码编译时指定的SYSCONFDIR参数指定的路径
$MYSQL_HOME/my.cnf
~/.my.cnf
本例中为
/data/mysqldata/3306/my.cnf
错误日志文件:
不仅仅是记录错误信息,MySQL服务进程即mysqld启动或关闭的信息也会被记录进来。错误日志文件是在启用mysqld时,通过log-error选项指定错误日志的路径及文件名
错误日志中记录的信息分为3类:Note,Warning,Error
Note对应的是正常的MySQL服务启动或关闭信息
Error对应的是错误的信息
Warning对应的是警告信息
对于Linux/UNIX系统,错误日志可以被写入到系统日志syslog中,在执行mysqld_safe命令启动MySQL服务时,可以附加--syslog参数,使MySQL的日志信息输出到系统日志中
/data/mysqldata/3306/mysql-error.log
查询日志文件:
MySQL会自动将所有执行时间超过指定阈值的SQL语句都记录下来,保存在文件里,这个文件就是慢查询日志文件。
MySQL的查询日志有两种,一种是慢查询日志(Slow Query Log)。另一种是通用查询日志(General Query Log),不仅仅记录执行慢的查询,而是所有执行的查询语句都会被记录下来
MySQL的查询日志不仅能记录到文件,还能自动保存到MySQL数据库中的表对象里
慢查询日志:就是所有查询语句执行时间超过系统变量long_query_time(默认值为10秒)指定的参数值,并且访问的记录数超过系统变量min_examined_row_limit(默认值为0条)的数量的语句。默认情况下慢查询日志功能是被禁止的,通过MySQL系统参数进行控制。通过慢查询日志调优SQL语句是项非常有效的手段
(system@localhost) [mysql]> set global slow_query_log=on;
(system@localhost) [mysql]> show global variables like '%slow%';
+---------------------------+-------------------+
| Variable_name | Value |
+---------------------------+-------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysqldata/3306/slow_query.log |
+---------------------------+-------------------+
slow_query_log指定是否输出慢查询日志,1为输出,0不输出,默认值为0
slow_query_log_file指定日志文件存储路径和文件名,默认保存在MySQL的data目录下
long_query_time指定慢查询执行时间的阀值
log_short_format用来控制输出到慢查询日志文件的信息,指定后,会减少输出信息
log_slow_admin_statements用来控制是否将一些执行时间较长的管理类语句,如OPTIMIZE TABLE、ANALYZE TABLE、ALTER TABLE语句输出到慢查询日志中
log_queries_not_using_indexes用来控制是否将未使用索引的语句输出到慢查询日志文件
log_throttle_queries_not_using_indexes一般会与log_queries_not_using_indexes参数组合使用,是控制每分钟输出到慢查询日志的未使用索引的记录条数,默认是0,表示不限制
log_slow_slave_statements复制环境专用的参数,用来控制是否将复制的查询语句输出到慢查询日志
/data/mysqldata/3306/slow_query.log
通过慢查询日志调优SQL语句是非常有效的手段
慢查询日志中语句记录和顺序可能跟执行顺序不同
普通查询日志:能够记录mysqld进程所做的几乎所有操作,不仅仅是客户端发出的sql语句会被记录到普通查询日志中,对于数据库或对象的管理操作也会记录下来,甚至连客户端连接或断开连接,服务器都会向文件中写入相应信息。最大的功能是审计,通过浏览日志文件中的信息,可以了解客户端都做了什么。通过MySQL系统参数进行控制。DBA怀疑客户端执行的操作有问题,就可以通过普通查询日志确定客户端究竟执行的是什么
(system@localhost) [mysql]> show global variables like '%general%';
+------------------+----------------------+
| Variable_name | Value |
+------------------+----------------------+
| general_log | OFF |
| general_log_file | /data/mysqldata/3306/general_query.log |
+------------------+----------------------+
general_log是否开启,0表示禁用,1表示启用
general_log_file指定日志文件存储路径和文件名,默认情况下,保存在data目录下,默认为[host_name].log
可以在MySQL服务运行时动态进行修改,而不需要重启MySQL服务。
禁用或启用普通全局查询日志:
SET GLOBAL general_log=’OFF’;
SET GLOBAL general_log=’ON’;
禁用或启用某个会话产生的普通查询日志:
SET sql_log_off=’OFF’;
SET sql_log_off=’ON’;
不要长期启用普通查询日志
普通查询日志文件中出现的顺序,是按照mysqld接收的顺序记录的
配置查询日志:
MySQL服务启动时配置指定--log-output选项:TABLE,FILE,NONE
--log-output=TABLE,FILE --general_log
--log-output=TABLE --general_log --slow_query_log
仅启用普通查询日志,并记录到日志文件和日志表
--log-output=TABLE,FILE --general_log
启用普通查询日志和慢查询日志,日志记录到数据库中的日志表
--log-output=TABLE --general_log --slow_query_log
--log-output=FILE --slow_query_log
--log_output=FILE --slow_query_log --slow_query_log_file=/data/mysql/logs/slow.log
MySQL服务运行中实时修改
二进制日志文件:
二进制日志binary log:记录数据库中的修改事件
二进制日志文件binary log file:保存数据库中修改事件的文件
普通查询日志是文本格式文件,为用户实际执行的操作,而二进制日志文件,记录的是数据库实际执行的操作。
通过二进制日志,能够实现两个重要功能:
用户复制,将MySQL Master端的二进制日志发送至Slave端,Slave端即可根据二进制日志中的内容,在本地重做,以达到主从同步的目的
用于恢复,当使用了备份恢复了数据库后,通过应用二进制日志文件,能够实现将数据恢复到故障发生前的状态
默认是不启用二进制日志文件功能,需要在启动MySQL服务时附加--log-bin选项,用来控制MySQL服务端要将数据库的修改操作写入二进制日志文件
在my.cnf中为bin-log选项
[mysql@mysql1 binlog]$ pwd
/data/mysqldata/3306/binlog
[mysql@mysql1 binlog]$ ll
total 1300
-rw-rw----. 1 mysql mysql 67095 Jul 2 23:47 mysql-bin.000001
-rw-rw----. 1 mysql mysql 1216094 Jul 2 23:47 mysql-bin.000002
-rw-rw----. 1 mysql mysql 143 Jul 2 23:53 mysql-bin.000003
-rw-rw----. 1 mysql mysql 894 Jul 3 09:06 mysql-bin.000004
-rw-rw----. 1 mysql mysql 1066 Jul 3 14:49 mysql-bin.000005
-rw-rw----. 1 mysql mysql 436 Jul 3 14:53 mysql-bin.000006
-rw-rw----. 1 mysql mysql 143 Jul 3 17:03 mysql-bin.000007
-rw-rw----. 1 mysql mysql 2892 Jul 4 14:44 mysql-bin.000008
-rw-rw----. 1 mysql mysql 143 Jul 5 10:36 mysql-bin.000009
-rw-rw----. 1 mysql mysql 143 Jul 5 11:18 mysql-bin.000010
-rw-rw----. 1 mysql mysql 143 Jul 7 09:57 mysql-bin.000011
-rw-rw----. 1 mysql mysql 120 Jul 7 09:57 mysql-bin.000012
-rw-rw----. 1 mysql mysql 540 Jul 7 09:57 mysql-bin.index
mysqld进程会自动附加日志序列号到生成的二进制日志文件,最大空间是由系统变量max_binlog_size进行控制,日志文件有可能超过max_binlog_size参数指定的值。为了能够跟踪二进制文件的状态,MySQL服务会创建一个与二进制日志文件同名(扩展名为.index)的二进制日志索引文件,可以通过log-bin-index选项指定文件名和路径
RESET MASTER用来清空所有二进制日志文件,而PURGE BINARY LOGS语句可以用来删除指定的某个或某些日志文件。为安全起见,建议删除前先备份
对于非事务表来说,语句执行后就会立刻写入二进制日志中。对于事务表,则要等到当前没有任何锁定或未提交的信息才会写入二进制日志,以此来确保日志被记录的始终是其执行的顺序。对于暂未提交的事务,事务中的更新操作(update,delte,insert支持事务的表对象)会被缓存起来,直到收到COMMIT语句,而后,mysqld进程就会将整个事务在COMMIT执行前全部写到二进制日志。当线程开始处理事务时,它会按照binlog_cache_size系统变量指定的值分配内存空间,缓存SQL语句,如果语句所需要的空间比分配的缓存区要大,那么线程将打开一个临时文件保存这个事务,直到事务结束时再自动删除临时文件
二进制日志中记录事件的格式有3种:基于行格式记录(row-based logging),基于语句记录(statement-based logging),混合模式记录(mixed-based logging)
binlog_cache_use状态变量显示了使用binlog_cache_size系统变量的事务数
binlog_cache_disk_use状态变量显示了使用临时文件的事务数
这两个参数组合起来可用于binlog_cache_size系统变量设置的调整和优化,以尽可能避免使用磁盘临时文件
max_binlog_cache_size系统变量默认为4GB,用来限制事务能够使用的最大缓存区
系统变量一般都是MySQL数据库中的系统配置,状态变量则是MySQL服务运行过程中的一些状态信息
查看系统变量执行:show [global] variables;
查看状态变量执行:show [global] status;
两个语句都支持附加like子句做细粒度的过滤
默认情况下二进制日志不是实时同步到磁盘,可以设置二进制日志同步到磁盘的频率,MySQL系统变量sync_binlog,设置为1(秒)安全级别最高,同时也是最慢的设置。MySQL初始化参数--innodb_support_xa设置为1,启用分布式事务的支持,确保二进制日志与InnoDB数据文件的同步。MySQL应被配置为以事务为单位同步二进制日志和InnoDB日志到磁盘。InnoDB日志默认即是同步状态,sync_binlog=1可以同步二进制日志。
中继日志及复制状态文件:
在复制过程中,Slave节点会创建若干文件,有些用于保存从Master节点接收到的二进制日志,有些用于记录当前复制环境的状态,还有些用于记录日志事件处理进度等相关信息。
中继日志relay log文件,用于保存读取到的Master二进制日志,由Slave节点的I/O线程负责数据的维护。类似Oracle的Standby Redologs
Master信息日志master.info文件,用于保存复制环境中连接Master节点的配置信息,比如说Slaves节点连接Master使用的用户名、密码、IP、端口等均在其中,在5.6之前保存在data目录下,5.6之后可以选择保存在mysql.slave_master_info表对象里
中继日志信息日志relay-log.info文件,保存处理进度及中继日志文件的位置,在5.6之前保存在data目录下的replay-log.info,5.6之后可以选择保存在mysql.slave_master_info表对象里
表对象数据文件:
frm文件:表对象的结构定义文件,只要是表对象一定会拥有这个文件,不管什么引擎
ibd文件:InnoDB引擎专用数据文件(含索引)
MYD文件:MyISAM引擎专用数据文件
MYI文件:MyISAM引擎专用索引文件
CSV文件:CSV数据文件
ARZ文件:ARCHIVE专用数据文件
进程id文件:
当MySQL服务启动后,在数据库目录下发现一个mysql.pid文件,保存的是当前MySQL服务的进程号
套接字文件:
在Linux/UNIX环境下,可以使用域套接字。只有当MySQL客户度和MySQL服务在同一台机器上时才能使用
show variables like ‘socket’;
自动配置文件:
从MySQL5.6开始,每个MySQL实例会拥有一个唯一的UUID,保存在数据根目录下的auto.cnf文件中,是由MySQL自动生成,不要尝试修改