参数文件

当MySQL实例启动时,数据库会读取参数文件,查找参数文件的顺序为/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf,相同的参数会以最后读到的参数文件为准。当参数文件不存在时,NySQL所有的参数值就取决于编译MySQL时指定的值和源代码指定参数的默认值。参数由key-value类型的键值对组成,我们可以通过show variables或information_schema.GLOBAL_VARIABLES视图来查看。

参数类型分为动态参数和静态参数。动态参数即意味着我们可以在MySQL实例运行过程中修改,静态参数即无法在实例运行中修改。我们可以通过SET命令对动态参数进行修改

SET 
| [global | session] system_var_name = expr
| [@@global. | @@session. | @@] system_var_name = expr

global和session用于声明参数修改是基于全局的还是当前会话的,有的动态参数只能基于会话修改,例如autocommit。当然也可以使用SET @@global | @@session的形式来修改,效果是一样的。需要注意的是参数动态修改后,在重启时修改会失效恢复原始值,因此在动态修改参数后,我们应该持久化到参数文件中。

日志文件

日志文件记录了MySQL实例运行过程中产生的各种活动日志,常见的日志文件有:错误日志、二进制日志、慢查询日志、general log

错误日志

错误日志为你教案对MySQL启动、运行、关闭过程进行了记录,其中涵盖了错误信息、告警信息等,方便我们定位MySQL的日常问题。可以通过show variables like ‘log_error’来定位该文件

mysql> show variables like 'log_error';
+---------------+-----------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------+
| log_error | /service/mysql/db1/data/error.log |
+---------------+-----------------------------------+

慢查询日志

通过设置一个阈值,执行时间超过该阈值的SQL都将记录到慢查询日志,通过分析慢查询日志,可以帮忙定位数据库性能问题,找出问题SQL,并对其进行优化。将参数slow_query_log设置为ON表示开启慢查询日志,long_query_time参数用于设置慢查询阈值。我们可以通过show variables like ‘slow_query_log_file’定位慢查询日志文件,慢查询日志也可以存放在数据表中,由参数log_output控制。数据表为CSV引擎,查询性能并不高。

此外,我们可以设置参数log_queries_not_using_indexes=on来将未使用索引的SQL记录到慢查询中,即使它并没有超过慢查询阈值,未使用索引的语句往往带有一定的风险,随着数据量的不断增长,数据表扫描的代价会越来越多,成为性能瓶颈。未避免这类SQL重复被记录到慢查询日志中,导致文件大小快速增长,我们应该同时设置log_throttle_queries_not_using_indexes参数,限制每分钟记录到慢查询日志中未使用索引的SQL次数。

慢查询日志的内容随着时间不断增长,手动分析该日志就比较麻烦了,MySQL提供了mysqldumpslow工具用来分析慢查询日志,当然也可以使用第三方开源的pt-query工具

[root@t-luhx02-v-szzb ~]# mysqldumpslow --help
Usage: 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 (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't 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 don't subtract lock time from total time

查询时间最长的10条SQL

mysqldumpslow -s t -t 10 -a mysqlslow.log

如果慢查询日志文件比较大时,分析消耗较多资源,我们可以通过sed命令按时间范围把需要的从慢查询日志文件中拷贝到新的文件中,再对新文件进行分析

sed -n '/# Time: 200507 21:00:02/,/# Time: 200508  1:30:05/'p mysqlslow.log >slow_20200508.log

二进制日志

二进制日志(binary log)记录了所有MySQL的数据修改操作,SELECT或者SHOW之类的操作并不会记录,我们可以通过show binlog events命令查看二进制日志中的内容

mysql> show binlog events in 'mysql-bin.000007'\G
*************************** 1. row ***************************
Log_name: mysql-bin.000007
Pos: 4
Event_type: Format_desc
Server_id: 3307
End_log_pos: 123
Info: Server ver: 5.7.17-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000007
Pos: 123
Event_type: Previous_gtids
Server_id: 3307
End_log_pos: 194
Info: 5b47e1b8-6efb-11ea-8de1-005056abb91e:5-24
*************************** 3. row ***************************
Log_name: mysql-bin.000007
Pos: 194
Event_type: Gtid
Server_id: 3307
End_log_pos: 259
Info: SET @@SESSION.GTID_NEXT= '5b47e1b8-6efb-11ea-8de1-005056abb91e:25'
*************************** 4. row ***************************
Log_name: mysql-bin.000007
Pos: 259
Event_type: Query
Server_id: 3307
End_log_pos: 338
Info: BEGIN
*************************** 5. row ***************************
Log_name: mysql-bin.000007
Pos: 338
Event_type: Rows_query
Server_id: 3307
End_log_pos: 610
Info: # INSERT INTO `departments` VALUES
('d001','Marketing'),
('d002','Finance'),
('d003','Human Resources'),
('d004','Production'),
('d005','Development'),
('d006','Quality Management'),
('d007','Sales'),
('d008','Research'),
('d009','Customer Service')
*************************** 6. row ***************************
Log_name: mysql-bin.000007
Pos: 610
Event_type: Table_map
Server_id: 3307
End_log_pos: 674
Info: table_id: 225 (employees.departments)

binlog是追加写入的,写到一定大小会写入下一个文件中,并不覆盖之前的文件。由于binlog记录了所有逻辑操作,且文件不会覆盖,因此我们可以通过binlog将数据库恢复到binlog范围内的任意时间点,同时也是MySQL实现复制的基础。

二进制日志由参数log-bin控制,我们可以指定其文件路径及文件名格式,生成的index文件为二进制的索引文件,用来存放过往的二进制日志序号。二进制日志记录的信息和行为同时收到下列参数的影响:

  • max_binlog_size:指定了单个二进制日志文件的最大值,如果超过该值则生成新的二进制日志文件,序号+1并记录到index文件中
  • binlog_cache_size:系统给binlog分配了一块内存,由参数binlog_cache_size控制单个线程binlog cache的大小,如果超过限制则放到临时文件中,事务提交后则写入binlog并清空binlog cache。通过show global status查看binlog_cache_use和binlog_cache_disk_use的状态,可以判断binlog_cache_size是否设置合理。
  • sync_binlog:binlog写入时是先写到文件系统的page cache,并没有持久化到磁盘,等待fsync将数据持久化到磁盘上。这个机制是由参数sync_binlog控制的,为0时表示每次提交事务只写到page cache,不做fsync;为1时每次提交事务都会执行fsync;大于1时则表示每次提交事务写入到page cache,等累计N个事务再fsync,设置为大于1的值可能会在主机异常重启后丢失N个事务的binlog日志
  • log-slave-update:如果当前实例在复制中是slave角色,想要将master获取并执行的二进制日志写入自身的二进制日志文件中,需要开启log-slave-update,特别是在级联复制的场景下
  • binlog_format:binlog_format影响了记录二进制日志的格式,其有三个可选值:ROW、STATEMENT、MIXED。STATEMENT格式记录的是逻辑SQL语句;ROW格式记录表的行更改情况,导致二进制日志大小变大,RC隔离级别下必须设置为ROW;MIXED格式下,默认采用STATEMENT格式记录二进制日志,在部分情况下会使用ROW格式。

如果想要查询二进制日志文件的内容,我们可以利用mysqlbinlog工具来解析

[root@t-luhx02-v-szzb ~]# mysqlbinlog -vv --start-position=338 /service/mysql/db1/binlog/mysql-bin.000007
### INSERT INTO `employees`.`employees`
### SET
### @1=69350 /* INT meta=0 nullable=0 is_null=0 */
### @2='1956:08:28' /* DATE meta=0 nullable=0 is_null=0 */
### @3='Nagui' /* VARSTRING(42) meta=42 nullable=0 is_null=0 */
### @4='Kaltofen' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
### @5=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @6='1985:04:20' /* DATE meta=0 nullable=0 is_null=0 */
.....

general log

general log会将MySQL执行的所有SQL记录下来,开启后可能会产生大量日志,需谨慎使用,默认不开启。如果要开启可以将参数general_log设置ON,通过show variables general_log_file可以查看general log的文件路径。与慢查询日志相同,general log也受参数log_output影响

socket文件

在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式,这种方式需要一个socket文件,该文件由参数socket控制,默认在temp目录下。当我们利用本地mysql客户端连接数据库时,可以通过-S选项指定socket文件的方式连接。

pid文件

MySQL实例启动时,会将自己的进程ID写入到PID文件中,文件由参数pid_file控制,默认位于数据库目录下

表结构定义文件

MySQL的数据存储是根据表进行的,每个表都存在对应的文件,无论任何存储引擎,都有一个frm后缀名的文件,这个文件记录了对应表的表结构定义,frm还会用来存放视图的定义。

表空间文件

系统表空间

bdata1.ibd,主要用于保存double writer buffer,changer buffer,数据字典,数据表。我们可以通过innodb_data_file_path参数指定一个或多个数据文件

innodb_data_file_path = /data1/ibdata1:12M;/data2/ibdata2:12M:autoextend

系统表空间最大的问题在于当存放在系统表空间的数据表删除后,占用的空间并不会释放,容易造成空间浪费

单表表空间

单表表空间即每张表对应一个表空间,其数据文件名为表名.ibd,要想使用单表表空间需要开启参数innodb_filer_per_table,也可以在创建表时指定单表表空间。

create table t1 (id int primary key) tablespace innodb_file_per_table;

相对于系统表空间,单表表空间的优点如下:

  • 可以为不同的表指定不同的磁盘目录
  • 可以结合可传输表空间快速移植单表
  • 可以使用表压缩等特性
  • 更好的对表进行重建收缩空间
  • 表删除后空间释放

使用单表表空间后,打开表所需的文件描述符也随之增大,增加了内存的消耗

通用表空间

通用表空间从MySQL5.7开始引入到InnoDB中,通用表空间和系统表空间一样,也是共享表空间,每个表空间可以包含多张表。通用表空间可以像单表表空间类似,将数据文件定义在其它目录下,并占用更少的文件描述符,但无法像单表表空间那样通过可传输表空间进行单表迁移。

mysql> create tablespace tb1 add datafile '/service/data/tb1.ibd' engine innodb;

三种表空间可以根据需求互相来回切换,但其执行性能代价较大,需注意时机

# 单表表空间
mysql> alter table t1 tablespace innodb_file_per_table;

# 系统表空间
mysql> alter table t1 tablespace innodb_system;

# 通用表空间
mysql> alter table t1 tablespace ts1;

要想删除通用表空间需要先确保表空间为空,如果其中存在对象则不允许删除,需要先将对象迁移或修改为其它表空间,我们可以通过下列语句查看表空间中的对象

select regexp_replace(a.name,'/.+','') dbname,regexp_replace(a.name,'.+/','') tablename from innodb_tables a, innodb_tablespaces b where a.space = b.space and b.name= 'tb1';