口诀：配置问题找my.cnf、错误问题找error.log、性能问题找slow.log

一、数据库配置文件

1.my.cnf文件

my.cnf是mysql启动时加载的配置文件，一般会放在mysql的安装目录中，用户也可以放在其他目录加载，常用放在/etc/my.cnf。

MySQL配置文件有多个文件，如下：

[root@k8s001 ~]# mysql --help |grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

MySQL实例读取配置文件的顺序是：/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

多个配置文件，遵循参数替换原则：即后面文件中的参数会替换掉前面文件的相同参数。

• my.cnf常用文件格式如下：

##mysql客户端设置
[client]
user=root    
password=Cmss@2021      ##免密登录设置
port=3306
socket=/tmp/mysql.sock 

##表示登录mysql后的设置
[mysql]
prompt=(\\u@\\h)[\\d]>\\_   ##显示登录用户名+ip+库

##mysql服务端设置（mysqld开头的都是服务端设置）
[mysqld]
server-id=1      
port=3306
user=mysql
datadir=/mdata/mysql_test_data
log_error=error.log
plugin-load=validate_password.so
default_password_lifetime=0
#skip-grant-tables

##表示特定mysql版本下生效
[mysqld-5.6]  
innodb_flush_neighbors=2

[mysqld-5.7]

[mysqld-8.0]

[mysqldump]
quick

[mysqladmin]

## 多实例配置
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe    #用来启动mysqld
mysqladmin=/usr/local/mysql/bin/mysqladmin #用来关闭mysqld，它需要用户名和密码。如不配置，则默认使用[client]的用户名和密码。如配置，请注意注意密码配置为pass（踩坑）
log=/usr/local/mysql/mysqld_multi.log
#user=root       #mysqladmin
#pass=Cmss@2021  #注意密码配置

##单机多实例1（版本5.7）
[mysqld1]
server-id=11
innodb_buffer_pool_size=32M
port=3307
datadir=/mdata/data1
socket=/tmp/mysql.sock1

##单机多实例2（版本5.7）
[mysqld2]
server-id=12
innodb_buffer_pool_size=32M
port=3308
datadir=/mdata/data2
socket=/tmp/mysql.sock2

##单机多实例3（版本5.6）
[mysqld56]
server-id=56
innodb_buffer_pool_size=32M
port=3356
basedir=/usr/local/mysql56    #声明mysql56安装包目录
datadir=/mdata/data56
socket=/tmp/mysql.sock3

##单机多实例4（版本8.0）
[mysqld80]
server-id=80
innodb_buffer_pool_size=32M
port=3380
basedir=/usr/local/mysql80
datadir=/mdata/data80
socket=/tmp/mysql.sock4

2.配置参数变量

• 从作用域上可以分为global（全局）和session（会话）

## 默认使用session变量
show [global|session] variables like '%变量%';
set [global|session] 变量='';

• 从类型上可以分为可修改参数和read-only（只读）参数

• 可修改参数可以在线修改

(root@localhost)[(none)] show session variables like '%long%query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

(root@localhost)[(none)] set long_query_time=20;
Query OK, 0 rows affected (0.00 sec)

(root@localhost)[(none)] show session variables like '%long%query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 20.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

• 只读参数只能通过配置文件修改，并重启才能起作用

(root@localhost)[(none)] show variables like '%datadir%';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| datadir       | /mdata/mysql_test_data/ |
+---------------+-------------------------+
1 row in set (0.00 sec)

(root@localhost)[(none)] set datadir='/data/';
ERROR 1238 (HY000): Variable 'datadir' is a read only variable

• 所有参数的修改不支持持久化（MySQL8.0的某些版本似乎可以支持）
• 通过global域修改参数不会影响当前session参数。（除非重连，重连就是另外一个session）

(root@localhost)[(none)] show session variables like '%long%query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

(root@localhost)[(none)] set global long_query_time=30;
Query OK, 0 rows affected (0.00 sec)

(root@localhost)[(none)] show session variables like '%long%query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

(root@localhost)[(none)] show global variables like '%long%query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 30.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

• 可以通过系统表performance_schema.variables_by_thread来查看每个session的变量值。

(root@localhost)[performance_schema] select * from variables_by_thread where variable_name='long_query_time';
+-----------+-----------------+----------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE |
+-----------+-----------------+----------------+
|        35 | long_query_time | 30.000000      |
|        36 | long_query_time | 10.000000      |
|        37 | long_query_time | 20.000000      |
+-----------+-----------------+----------------+
3 rows in set (0.00 sec)

二、表结构定义文件

MySQL物理存储结构图

说明：

mysqld表示一个数据库实例。

每个数据库实例有一个datadir，一个路径下有多个数据库，每个数据库对应一个文件夹。

每个数据库里有多张表，每张表对应一组表文件。即每个文件夹下有多组表文件。

• 1.数据文件路径由参数datadir设置

## 该参数配置在/etc/my.cnf，它是一个只读参数
(root@localhost)[(none)]> show variables like '%datadir%';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| datadir       | /mdata/mysql_test_data/ |
+---------------+-------------------------+
1 row in set (0.00 sec)
## 初始情况下四个数据库
root@localhost)[(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |  ## 数据库0 没有对应的文件夹
| mysql              |  ## 数据库1
| performance_schema |  ## 数据库2
| sys                |  ## 数据库3
+--------------------+
4 rows in set (0.00 sec)
## 来看一下mysql数据库中的表
(root@localhost)[performance_schema]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(root@localhost)[mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              | ## 对应一组文件（3个文件）
| db                        | ## 对应一组文件（4个文件）
| engine_cost               | ## 对应一组文件（2个文件）
...

• 2.一个数据库对应一个文件夹。

[root@k8s001 mysql_test_data]# pwd
/mdata/mysql_test_data
[root@k8s001 mysql_test_data]# ll
总用量 123024
-rw-r-----. 1 mysql mysql       56 6月   9 23:06 auto.cnf
-rw-------. 1 root  root      1679 6月   9 23:06 ca-key.pem
-rw-r--r--. 1 root  root      1074 6月   9 23:06 ca.pem
-rw-r--r--. 1 root  root      1078 6月   9 23:06 client-cert.pem
-rw-------. 1 root  root      1679 6月   9 23:06 client-key.pem
-rw-r-----. 1 mysql mysql    67273 6月  10 03:22 error.log
-rw-r-----. 1 mysql mysql      299 6月  10 03:21 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 6月  10 03:22 ibdata1
-rw-r-----. 1 mysql mysql 50331648 6月  10 03:22 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 6月   9 23:06 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 6月  10 03:22 ibtmp1
-rw-r-----. 1 mysql mysql        6 6月  10 03:22 k8s001.pid
drwxr-x---. 2 mysql mysql     4096 6月   9 23:06 mysql               ## 数据库1
-rw-rw----. 1 root  root         6 6月  10 03:22 mysqld_safe.pid
drwxr-x---. 2 mysql mysql     8192 6月   9 23:06 performance_schema  ## 数据库2
-rw-------. 1 root  root      1675 6月   9 23:06 private_key.pem
-rw-r--r--. 1 root  root       451 6月   9 23:06 public_key.pem
-rw-r--r--. 1 root  root      1078 6月   9 23:06 server-cert.pem
-rw-------. 1 root  root      1679 6月   9 23:06 server-key.pem
drwxr-x---. 2 mysql mysql     8192 6月   9 23:06 sys                 ## 数据库3

附注：为啥只有三个数据库文件夹？

因为information_schema 中的表实际上是视图，而不是基本表，因此，文件系统上没有与之相关的文件。

• 3.每张表对应一组文件

[root@k8s001 mysql_test_data]# cd mysql
[root@k8s001 mysql]# ll
总用量 11896
-rw-r-----. 1 mysql mysql    8820 6月   9 23:06 columns_priv.frm##表columns_priv对应3个文件
-rw-r-----. 1 mysql mysql       0 6月   9 23:06 columns_priv.MYD
-rw-r-----. 1 mysql mysql    4096 6月   9 23:06 columns_priv.MYI
-rw-r-----. 1 mysql mysql    9582 6月   9 23:06 db.frm          ##表db对应4个文件
-rw-r-----. 1 mysql mysql     488 6月   9 23:06 db.MYD
-rw-r-----. 1 mysql mysql    5120 6月   9 23:06 db.MYI
-rw-r-----. 1 mysql mysql      65 6月   9 23:06 db.opt
-rw-r-----. 1 mysql mysql    8780 6月   9 23:06 engine_cost.frm ##表engine_cost对应2个文件
-rw-r-----. 1 mysql mysql   98304 6月   9 23:06 engine_cost.ibd
...

附注：MySQL官网提供了优秀的mysql-utilites工具套件来辅助使用mysql。

安装见：https://blog.csdn.net/qq_41822345/article/details/117779815

## 可以使用mysqlfrm命令（先安装mysql-utilites工具套件）查看*.frm文件的结构
[root@k8s001 mysql]# mysqlfrm --diagnostic columns_priv.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for time_zone.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `Use_leap_seconds` enum('Y','N') NOT NULL, 
PRIMARY KEY `PRIMARY` (`Time_zone_id`)
) ENGINE=InnoDB COMMENT 'Time zones';

#...done.

三、错误日志文件

错误日志：记录启动、运行或停止mysqld时出现的问题。

错误日志，顾名思义，当然就是记录错误信息的日志，不过本小节中要提到的错误日志文件，并不仅仅是记录错误信息，MySQL服务进程启动/关闭的信息也会被记录进来，也不是说什么错误都会记录，只有服务进程运行过程中发生的关键(critical)错误会被记录，另外mysqld进程发现某些表需要自动检查或修复的话，也会抛出相关信息到该日志文件。

建议统一修改错误日志文件为一个固定的名称，比如：error.log。

初始安装mysql5.7和mysql8.0的初始化密码就在该文件中。

使用原则：mysqld实例一旦出错，就应该首先分析该文件。

它的位置如下：

(root@localhost)[(none)]> show variables like 'log_error';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| log_error     | /mdata/mysql_test_data/error.log |
+---------------+----------------------------------+
1 row in set (0.00 sec)

四、慢查询日志文件

慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
这里的执行时长不包括锁等待时间，所以实际上 sql执行时长=查询时长-锁等待时长

## 查看慢查询日志   默认文件名：机器名-slow.log   默认关闭
(root@localhost)[(none)]> show variables like '%slow%query%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log      | OFF                                     |
| slow_query_log_file | /mdata/mysql_test_data/k8s001-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.01 sec)
## 查看long_query_time参数（超过2s的日志都会被保存）
(root@localhost)[(none)]> show variables like 'long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
## 开启慢查询日志 慢查询日志是全局变量
(root@localhost)[(none)]> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
## 可以重新定义慢查询日志
(root@localhost)[(none)]> set global slow_query_log_file='slow.log';
Query OK, 0 rows affected (0.01 sec)
## 清除慢查询日志）（记得先备份）
(root@localhost)[(none)]> flush slow logs;
Query OK, 0 rows affected (0.00 sec)

使用举例：

## 休眠5s（mysql中执行）
(root@localhost)[(none)]> select sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 row in set (5.00 sec)
## 休眠2s呢？
(root@localhost)[(none)]> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

## 查看慢查询日志（机器中执行）
[root@k8s001 ~]# cat /mdata/mysql_test_data/k8s001-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.16 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2021-06-15T02:10:53.481247Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 5.000917  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1623723053;
select sleep(5);
# Time: 2021-06-15T02:14:47.427602Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 2.000813  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1623723287;
select sleep(2);

慢查询日志参数配置

[mysqld]
## slow log
slow_query_log=1
slow_query_log_file=/mdata/mysql_test_data/k8s001-slow.log
long_query_time=2
min_examined_row_limit=100     #扫描记录少于该值的sql不记录
log_queries_not_using_indexes  #记录没有使用索引的sql
log_throttle_queries_not_using_indexes=5 #限制每分钟的sql记录数
log_slow_admin_statements=1    #主机上开启慢查询日志
log_slow_slave_statements=0    #从机上关闭慢查询日志
log_timestamps=system          #时区信息 [UTC|system]
log_output=file                #指定慢查询日志文件形式 [FILE|TABLE|NONE]

五、通用日志文件

通用日志：记录建立的客户端连接和执行的语句。

它可以记录数据库的所有相关操作，开启后会导致性能下降明显。因此一般不开启。

## 查看通用日志     默认文件名： 机器名.log     默认关闭
(root@localhost)[(none)]> show variables like '%general%';
+------------------+-----------------------------------+
| Variable_name    | Value                             |
+------------------+-----------------------------------+
| general_log      | OFF                               |
| general_log_file | /mdata/mysql_test_data/k8s001.log |
+------------------+-----------------------------------+
2 rows in set (0.01 sec)
## 开启通用日志
(root@localhost)[(none)]> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)
## 清除通用日志
(root@localhost)[(none)]> flush general logs;
Query OK, 0 rows affected (0.00 sec)

使用场景：银行审计；用于测试了解原理等（比如想了解在线修改表schema的原理，就可以开启通用日志来分析数据库到底执行了那些操作）。

六、其它重要文件

• Binlog日志

Binlog日志，又叫二进制日志，记录所有更改数据的语句。也用于复制。它记录的都是二进制信息。

配置文件中开启binlog

## bin log 二进制日志
log-bin=mysql-bin

查看Binlog日志是否开启 和 日志参数sync_binlog

(root@localhost)[(none)]> show variables like '%log_bin%';
+---------------------------------+----------------------------------------+
| Variable_name                   | Value                                  |
+---------------------------------+----------------------------------------+
| log_bin                         | ON                                     |
| log_bin_basename                | /mdata/mysql_test_data/mysql-bin       |
| log_bin_index                   | /mdata/mysql_test_data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                    |
| log_bin_use_v1_row_events       | OFF                                    |
| sql_log_bin                     | ON                                     |
+---------------------------------+----------------------------------------+
6 rows in set (0.00 sec)

(root@localhost)[(none)]> show variables like "%sync_binlog%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)
## sync_binlog
#  0，默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。
#  1，事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。
#  N，每写N次操作系统缓冲就执行一次刷新操作。

• Redolog日志

Redo日志：当数据发生改变时 InnoDB会把记录到redolog中，并更新内存，InnoDB会在合适的时候记录到磁盘中。

Redo日志属于事务日志，它保证了持久性。

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

查看Redo日志参数 innodb_flush_log_at_trx_commit

(root@localhost)[(none)]> show variables like "%flush%trx%";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)
## innodb_flush_log_at_trx_commit
#  0，表示提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
#  1，表示提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。
#  2，表示提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

binlog和redolog的区别

1.二进制日志是在存储引擎的上层产生的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志。而redo log是innodb层产生的，只记录该存储引擎中表的修改。并且二进制日志先于redo log被记录。

2.二进制日志记录操作的方法是逻辑性的语句。而redolog是在物理格式上的日志，它记录的是数据库中每个页的修改，它支持并发修改。

3.二进制日志只在每次事务提交的时候一次性写入缓存中的日志"文件" （对于非事务表的操作，则是每次执行语句成功后就直接写入）。redo log在数据准备修改前写入缓存中的redo log中，然后才对缓存中的数据执行修改操作；而且保证在发出事务提交指令时，先向缓存中的redo log写入日志，写入完成后才执行提交动作。

4.事务日志记录的是物理页的情况，它具有幂等性，因此记录日志的方式极其简练。幂等性的意思是多次操作前后状态是一样的，例如新插入一行后又删除该行，前后状态没有变化。而二进制日志记录的是所有影响数据的操作，记录的内容较多。例如插入一行记录一次，删除该行又记录一次。

• Undolog日志

Undo日志：在操作数据之前会被备份到Undolog中 如果rollback语句就可以恢复到原先状态。

Undo日志属于事务日志，它保证了原子性。

Undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

比如：当delete一条记录时，Undolog 会记录insert语句
当insert一条记录时，Undolog会记录delete语句
当update一条记录时，Undolog会记录一条相反的语句

查看Undolog日志是否开启。

(root@localhost)[(none)]> show variables like "%undo%";
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set (0.01 sec)

• sync_binlog和innodb_flush_log_at_trx_commit

sync_binlog

0，默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。

1，事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。

N，每写N次操作系统缓冲就执行一次刷新操作。

innodb_flush_log_at_trx_commit

0，表示提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。

1，表示提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。

2，表示提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

对于线上环境，MySQL一般都是主备架构的。为了确保数据不会丢失以及主备之间获得最大的一致性，需要将这两个参数设为1，即使要牺牲掉一部分数据库的性能，安全最重要。

• 事务日志
  借一篇大佬的文章，介绍redolog和undolog事务日志（写的真好）：
  https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html