参数文件
当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命令对动态参数进行修改
1
2
3
|
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’来定位该文件
1
2
3
4
5
6
|
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工具
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
[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
1
|
mysqldumpslow -s t -t 10 -a mysqlslow.log
|
如果慢查询日志文件比较大时，分析消耗较多资源，我们可以通过sed命令按时间范围把需要的从慢查询日志文件中拷贝到新的文件中，再对新文件进行分析
1
|
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命令查看二进制日志中的内容
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
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工具来解析
1
2
3
4
5
6
7
8
9
10
|
[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参数指定一个或多个数据文件
1
|
innodb_data_file_path = /data1/ibdata1:12M;/data2/ibdata2:12M:autoextend
|
系统表空间最大的问题在于当存放在系统表空间的数据表删除后，占用的空间并不会释放，容易造成空间浪费
单表表空间
单表表空间即每张表对应一个表空间，其数据文件名为表名.ibd，要想使用单表表空间需要开启参数innodb_filer_per_table，也可以在创建表时指定单表表空间。
1
|
create table t1 (id int primary key) tablespace innodb_file_per_table;
|
相对于系统表空间，单表表空间的优点如下：
- 可以为不同的表指定不同的磁盘目录
- 可以结合可传输表空间快速移植单表
- 可以使用表压缩等特性
- 更好的对表进行重建收缩空间
- 表删除后空间释放
使用单表表空间后，打开表所需的文件描述符也随之增大，增加了内存的消耗
通用表空间
通用表空间从MySQL5.7开始引入到InnoDB中，通用表空间和系统表空间一样，也是共享表空间，每个表空间可以包含多张表。通用表空间可以像单表表空间类似，将数据文件定义在其它目录下，并占用更少的文件描述符，但无法像单表表空间那样通过可传输表空间进行单表迁移。
1
|
mysql> create tablespace tb1 add datafile '/service/data/tb1.ibd' engine innodb;
|
三种表空间可以根据需求互相来回切换，但其执行性能代价较大，需注意时机
1
2
3
4
5
6
7
8
|
# 单表表空间
mysql> alter table t1 tablespace innodb_file_per_table;
# 系统表空间
mysql> alter table t1 tablespace innodb_system;
# 通用表空间
mysql> alter table t1 tablespace ts1;
|
要想删除通用表空间需要先确保表空间为空，如果其中存在对象则不允许删除，需要先将对象迁移或修改为其它表空间，我们可以通过下列语句查看表空间中的对象
1
|
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';
|