mysql体系结构及主要文件

鱼跃龙门Smile

已于 2024-03-20 09:25:56 修改

阅读量1k

点赞数 11

文章标签： mysql 数据库

于 2024-03-19 16:23:20 首次发布

本文链接：https://blog.csdn.net/zj88189748/article/details/136829130

版权

4.4.4slow_query_log参数

4.4.5slow_query_log_file参数

4.4.6long_query_time参数

4.4.7在线修改慢查询参数

4.4.8slow.log日志过大处理

4.4.9min_examined_row_limit参数

4.4.10log_queries_not_using_indexes参数

4.4.11log_throttle_queries_not_using_indexes参数

4.4.12log_slow_admin_statements参数

4.4.13log_slow_slave_statements参数

4.4.14log_output参数

4.4.15log_timestamps参数

5.bing_address参数

6.通用日志

6.1general_log参数

6.2general_log_file参数

7.mysql8.0新特性：持久化修改参数

7.1设置持久化:set persist

7.2清空持久化变量：reset persist

8.存储引擎

1.mysql体系结构

2.数据库与数据库实例

创建数据库

(root@localhost) [(none)]> create database python_mysql;
Query OK, 1 row affected (0.03 sec)

删除数据库

drop database和drop schema是同义词，都可以用来删除数据库

(root@localhost) [(none)]> drop schema python;
Query OK, 0 rows affected (0.06 sec)

3.物理存储结构

4.mysql主要文件

4.1数据库配置文件

[mysqld-5.6]此参数下的配置在启动mysql5.6版本的才会读取（特定版本）

4.2错误日志

mysql有什么报错都会在这个日志文件中

4.3表结构定义文件

从数据库中查看表结构

(root@localhost) [mysql]> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `password_last_changed` timestamp NULL DEFAULT NULL,
  `password_lifetime` smallint(5) unsigned DEFAULT NULL,
  `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

安装mysql utilities工具

MySQL :: Download MySQL Utilities (Archived Versions)mysql utilities下载地址：MySQL :: Download MySQL Utilities (Archived Versions)

解压安装

[root@localhost soft]# tar -zxvf mysql-utilities-1.6.5.tar.gz
[root@localhost soft]# cd mysql-utilities-1.6.5/
[root@localhost mysql-utilities-1.6.5]# python setup.py install

mysqlfrm查看表结构

mysqlfrm --diagnostic user.frm #不能查看字符集，所以char字段长度比实际的乘以3了

mysqlfrm --diagnostic user.frm --server=root:123456@localhost #能看到字符集，实际的字段长度

[root@localhost mysql]# mysqlfrm --diagnostic user.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 user.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `user` (
  `Host` char(180) NOT NULL, 
  `User` char(96) NOT NULL, 
  `Select_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Insert_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Update_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Delete_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Create_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Drop_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Reload_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Shutdown_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Process_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `File_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Grant_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `References_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Index_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Alter_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Show_db_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Super_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Lock_tables_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Execute_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Repl_slave_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Repl_client_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Create_view_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Show_view_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Create_routine_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Alter_routine_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Create_user_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Event_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Trigger_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET <UNKNOWN> NOT NULL, 
  `ssl_type` enum('ANY','X509','SPECIFIED') CHARACTER SET <UNKNOWN> NOT NULL, 
  `ssl_cipher` blob CHARACTER SET <UNKNOWN>, 
  `x509_issuer` blob CHARACTER SET <UNKNOWN>, 
  `x509_subject` blob CHARACTER SET <UNKNOWN>, 
  `max_questions` int(11) unsigned NOT NULL, 
  `max_updates` int(11) unsigned NOT NULL, 
  `max_connections` int(11) unsigned NOT NULL, 
  `max_user_connections` int(11) unsigned NOT NULL, 
  `plugin` char(192) NOT NULL, 
  `authentication_string` text DEFAULT NULL, 
  `password_expired` enum('ANY','X509','SPECIFIED') CHARACTER SET <UNKNOWN> NOT NULL, 
  `password_last_changed` timestamp DEFAULT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `password_lifetime` smallint(5) unsigned DEFAULT NULL, 
  `account_locked` enum('ANY','X509','SPECIFIED') CHARACTER SET <UNKNOWN> NOT NULL, 
PRIMARY KEY `PRIMARY` (`Host`,`User`)
) ENGINE=MyISAM COMMENT 'Users and global privileges';

#...done.

[root@localhost mysql]# mysqlfrm --diagnostic user.frm --server=root:123456@localhost
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# 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 user.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `user` (
  `Host` char(60) COLLATE `utf8_bin` NOT NULL, 
  `User` char(32) COLLATE `utf8_bin` NOT NULL, 
  `Select_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Insert_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Update_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Delete_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Create_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Drop_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Reload_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Shutdown_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Process_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `File_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Grant_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `References_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Index_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Alter_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Show_db_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Super_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Create_tmp_table_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Lock_tables_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Execute_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Repl_slave_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Repl_client_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Create_view_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Show_view_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Create_routine_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Alter_routine_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Create_user_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Event_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Trigger_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `Create_tablespace_priv` enum('N','Y') COLLATE `utf8_general_ci` NOT NULL, 
  `ssl_type` enum('ANY','X509','SPECIFIED') COLLATE `utf8_general_ci` NOT NULL, 
  `ssl_cipher` blob COLLATE `binary`, 
  `x509_issuer` blob COLLATE `binary`, 
  `x509_subject` blob COLLATE `binary`, 
  `max_questions` int(11) unsigned NOT NULL, 
  `max_updates` int(11) unsigned NOT NULL, 
  `max_connections` int(11) unsigned NOT NULL, 
  `max_user_connections` int(11) unsigned NOT NULL, 
  `plugin` char(64) COLLATE `utf8_bin` NOT NULL, 
  `authentication_string` text COLLATE `utf8_bin` DEFAULT NULL, 
  `password_expired` enum('ANY','X509','SPECIFIED') COLLATE `utf8_general_ci` NOT NULL, 
  `password_last_changed` timestamp DEFAULT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `password_lifetime` smallint(5) unsigned DEFAULT NULL, 
  `account_locked` enum('ANY','X509','SPECIFIED') COLLATE `utf8_general_ci` NOT NULL, 
PRIMARY KEY `PRIMARY` (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8, COMMENT 'Users and global privileges';

#...done.

4.4慢查询日志

4.4.1慢查询相关参数

4.4.2慢查询参数默认值

(root@localhost) [mysql]> show variables like 'slow%'
    -> ;
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_launch_time    | 2                              |
| slow_query_log      | OFF                            |
| slow_query_log_file | /mysql_data/localhost-slow.log |
+---------------------+--------------------------------+
3 rows in set (0.00 sec)

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

(root@localhost) [(none)]> show variables like 'min_examined%'
    -> ;
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

(root@localhost) [mysql]> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

(root@localhost) [mysql]> show variables like 'log_throttle_queries_not_using_indexes';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
1 row in set (0.00 sec)

(root@localhost) [mysql]> show variables like 'log_slow_%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF   |
| log_slow_slave_statements | OFF   |
+---------------------------+-------+
2 rows in set (0.00 sec)

(root@localhost) [(none)]> show variables like 'log_out%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

4.4.3my.cnf中设置慢查询参数

[mysqld]
#slow log
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 2
min_examined_row_limit = 100
log_queries_not_using_indexes
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements
log_slow_slave_statements

重启mysql后再次查看慢查询参数

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

(root@localhost) [(none)]> show variables like 'slow%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| slow_launch_time    | 2        |
| slow_query_log      | ON       |
| slow_query_log_file | slow.log |
+---------------------+----------+
3 rows in set (0.00 sec)

(root@localhost) [(none)]> show variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 100   |
+------------------------+-------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.01 sec)

(root@localhost) [(none)]> show variables like 'log_throttle_queries_not_using_indexes';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_throttle_queries_not_using_indexes | 10    |
+----------------------------------------+-------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> show variables like 'log_slow%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| log_slow_admin_statements | ON    |
| log_slow_slave_statements | ON    |
+---------------------------+-------+
2 rows in set (0.01 sec)

4.4.4slow_query_log参数

打开慢查询日志：slow_query_log = 1，可在线修改

4.4.5slow_query_log_file参数

设置慢查询日志文件：slow_query_log_file = slow.log，可在线修改

4.4.6long_query_time参数

设置慢查询时间为2秒：long_query_time=2，可在线修改，执行超过2秒(>2s)的语句就会被记录在slow.log，执行时间是query_time-lock_time得到的值,但是如果设置了min_examined_row_limit=100，则结果少于100行的查询不会被记录在慢查询日志中

(root@localhost) [(none)]> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

查看slow.log

[root@localhost mysql_data]# tailf slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: (null)
Time                 Id Command    Argument
# Time: 2024-03-19T03:03:00.741964Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 2.003868  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1710817380;
select sleep(2);

4.4.7在线修改慢查询参数

在线修改slow_query_log、slow_query_log_file、long_query_time参数

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

(root@localhost) [(none)]> set global slow_query_log_file = 'slow_new.log';
Query OK, 0 rows affected (0.00 sec)

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

(root@localhost) [(none)]> show variables like 'slow%';
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| slow_launch_time    | 2            |
| slow_query_log      | OFF          |
| slow_query_log_file | slow_new.log |
+---------------------+--------------+
3 rows in set (0.00 sec)

(root@localhost) [(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

4.4.8slow.log日志过大处理

如果慢查询日志过大，在线重新设置慢查询日志

[root@localhost mysql_data]# mv slow.log slow.log.20240319
[root@localhost mysql_data]# ls
auto.cnf    client-cert.pem  ib_buffer_pool  ib_logfile1                mysql               public_key.pem   slow.log.20240319
ca-key.pem  client-key.pem   ibdata1         ibtmp1                     performance_schema  server-cert.pem  sys
ca.pem      error.log        ib_logfile0     localhost.localdomain.pid  private_key.pem     server-key.pem

(root@localhost) [(none)]> flush slow logs;
Query OK, 0 rows affected (0.00 sec)

[root@localhost mysql_data]# ls
auto.cnf    client-cert.pem  ib_buffer_pool  ib_logfile1                mysql               public_key.pem   slow.log
ca-key.pem  client-key.pem   ibdata1         ibtmp1                     performance_schema  server-cert.pem  slow.log.20240319
ca.pem      error.log        ib_logfile0     localhost.localdomain.pid  private_key.pem     server-key.pem   sys

4.4.9min_examined_row_limit参数

设置查询少于100行就不记录在slow.log中：min_examined_row_limit=100，可以在线修改，有session和global级别

(root@localhost) [(none)]> show variables like 'min_examined%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 100   |
+------------------------+-------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)

[root@localhost ~]# tailf /mysql_data/slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: (null)
Time                 Id Command    Argument

4.4.10log_queries_not_using_indexes参数

将没有使用索引的sql记录在慢查询日志中

4.4.11log_throttle_queries_not_using_indexes参数

限制每分钟记录没有使用索引sql语句的次数

设置每分钟有10次没使用索引就记录在慢查询日志：log_throttle_queries_not_using_indexes=10

4.4.12log_slow_admin_statements参数

开启后记录超时的管理操作到慢查询日志，如alter table

4.4.13log_slow_slave_statements参数

在从服务器上开启慢查询日志

4.4.14log_output参数

log_output慢查询日志输出位置，文件or表，默认是file

将慢查询日志设置成表：set global log_output = 'table';

(root@localhost) [(none)]> set global log_output = 'table';
Query OK, 0 rows affected (0.00 sec)

不建议记录到表，因为记录到表性能开销会更大，备份的时候也会把mysql.slow_log这张表备份下来，如果这张表过大会话更多的额外时间，但是用该表查询更方便

4.4.15log_timestamps参数

log_timestamps 这个参数主要是控制 error log、slow_log、genera log，等等记录日志的显示时间参数，但不会影响 general log 和 slow log 写到表 (mysql.general_log, mysql.slow_log) 中的显示时间。该参数全局有效，可以被设置的值有：UTC 和 SYSTEM，默认使用 UTC。log_timestamps 参数默认使用 UTC 时区，这样会使得日志中记录的时间比中国这边的慢了 8 个小时，导致查看日志不方便。修改为 SYSTEM 就能解决问题。

my.cnf中添加log_timestamps = system，重启mysql，或者set global log_timestamps = system;

5.bing_address参数

绑定mysql地址，将mysql绑定到该地址：bing_address = 192.168.26.101

6.通用日志

6.1general_log参数

开启通用日志：general_log=1

6.2general_log_file参数

将通用日志设置成文件general_log_flie=general.log,也可设置成表，日志数据存在mysql.general_log中，存在表中，数据库性能明显下降

7.mysql8.0新特性：持久化修改参数

在8之前的版本中，对于全局变量的修改，其只会影响其内存值，而不会持久化到配置文件中。数据库重启，又会恢复成修改前的值。从8.0开始，可通过SET PERSIST命令将全局变量的修改持久化到配置文件中。

7.1设置持久化:set persist

将慢查询时间设置为8持久化：set persist long_query_time = 8;

修改后持久化保存的位置在mysqld-auto.cnf,也可在performance.persisted_variables表中查看

[root@localhost ~]# mysql -S/tmp/mysql.sock80 -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost) [(none)]> set persist long_query_time=8;
Query OK, 0 rows affected (0.08 sec)

(root@localhost) [(none)]> use performance_schema;
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) [performance_schema]> select * from persisted_variables;
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| long_query_time | 8.000000       |
+-----------------+----------------+
1 row in set (0.00 sec)

(root@localhost) [performance_schema]> exit

[root@localhost ~] cd /mysql80_data
[root@localhost mysql80_data]# cat mysqld-auto.cnf 
{"Version": 2, "mysql_dynamic_variables": {"long_query_time": {"Value": "8.000000", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 171083027808196

在数据库启动时，会首先读取其它配置文件，最后才读取mysqld-auto.cnf文件。不建议手动修改该文件，其有可能导致数据库在启动过程中因解析错误而失败。如果出现这种情况，可手动删除mysqld-auto.cnf文件或将persisted_globals_load变量设置为off来避免该文件的加载。

7.2清空持久化变量：reset persist

[root@localhost mysql80_data]# mysql -S/tmp/mysql.sock80 -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost) [(none)]> reset persist;
Query OK, 0 rows affected (0.02 sec)

(root@localhost) [(none)]> select * from persisted_variables;
ERROR 1046 (3D000): No database selected
(root@localhost) [(none)]> use performance_schema;
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) [performance_schema]> select * from persisted_variables;
Empty set (0.00 sec)

(root@localhost) [performance_schema]> exit
Bye
[root@localhost mysql80_data]# cat mysqld-auto.cnf 
{"Version": 2}[root@localhost mysql80_data]#

8.存储引擎

除了innodb引擎，其他引擎不建议用，也就不建议学了，后面再来学习innodb引擎

show engines;查看存储引擎

(root@localhost) [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

在my.cnf配置文件中关闭存储引擎，注意是小写，在5.7版本中不能关闭myisam、csv、memory引擎，系统表中有用到这些引擎，在8.0版本中把大部分表的引擎改成innodb了

[mysqld]

skip-federated
skip-archive
skip-blackhole

修改后重启mysql，再次查看引擎

(root@localhost) [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | NO      | /dev/null storage engine (anything you write to it disappears) | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | NO      | Archive storage engine                                         | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

修改表的存储引擎

alter table a engine = innodb;

鱼跃龙门Smile

关注

11
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
mysql体系结构及主要文件

从数据库中查看表结构安装mysql utilities工具解压安装mysqlfrm查看表结构mysqlfrm --diagnostic user.frm #不能查看字符集，所以char字段长度比实际的乘以3了mysqlfrm --diagnostic user.frm --server=root:123456@localhost #能看到字符集，实际的字段长度#...done.#...done.
复制链接

扫一扫