mysql体系结构及主要文件

目录

1.mysql体系结构

2.数据库与数据库实例 

3.物理存储结构​编辑

4.mysql主要文件 

4.1数据库配置文件

4.2错误日志

4.3表结构定义文件

4.4慢查询日志 

4.4.1慢查询相关参数 

4.4.2慢查询参数默认值

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

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 - GPLCopyright (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 -ADatabase 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 - GPLCopyright (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 -ADatabase 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;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/284732.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

官宣｜阿里巴巴捐赠的 Flink CDC 项目正式加入 Apache 基金会

官宣｜阿里巴巴捐赠的 Flink CDC 项目正式加入 Apache 基金会

摘要&#xff1a;本文整理自阿里云开源大数据平台徐榜江 (雪尽)&#xff0c;关于阿里巴巴捐赠的 Flink CDC 项目正式加入 Apache 基金会&#xff0c;内容主要分为以下四部分&#xff1a; 1、Flink CDC 新仓库&#xff0c;新流程 2、Flink CDC 新定位&#xff0c;新玩法 3、Flin…
阅读更多...
中文编程入门（Lua5.4.6中文版）第十一章 Lua 模块与包 参考星争际霸游戏

中文编程入门（Lua5.4.6中文版）第十一章 Lua 模块与包 参考星争际霸游戏

在遥远的星争际霸世界中&#xff0c;代码模块就如同星际基地中的高科技仓库&#xff0c;储存着各类经过封装优化的战术指令和战略资源。自Lua 5.1版本起&#xff0c;星际编程者们引入了标准化的模块管理系统&#xff0c;使得不同战舰之间能够共享和调用核心战斗算法&#xff0c…
阅读更多...
【】（综合练习）博客系统

【】（综合练习）博客系统

在之前的学些中&#xff0c;我们掌握了Spring框架和MyBatis的基本使用&#xff0c;接下来 我们就要结合之前我们所学的知识&#xff0c;做出一个项目出来 1.前期准备 当我们接触到一个项目时&#xff0c;我们需要对其作出准备&#xff0c;那么正规的准备是怎么样的呢 1.了解需求…
阅读更多...
覃超老师 算法面试通关40讲

覃超老师 算法面试通关40讲

教程介绍 无论是阿里巴巴、腾讯、百度这些国内一线互联网企业&#xff0c;还是 Google、Facebook、Airbnb 等硅谷知名互联网公司&#xff0c;在招聘工程师的过程中&#xff0c;对算法和数据结构能力的考察都是重中之重。本课程以帮助求职者在短时间内掌握面试中最常见的算法与…
阅读更多...
这个世界会好吗

这个世界会好吗

1918年11月7日&#xff0c;梁漱溟的父亲梁济正准备出门&#xff0c;遇到漱溟&#xff0c;二人谈起关于欧战的一则新闻。“世界会好吗&#xff1f;”父亲最后问道。儿子回答&#xff1a;“我相信世界是一天一天往好里去的。” “能好就好啊&#xff01;”父亲说罢就离开了家。 三…
阅读更多...
使用切片技术从点云中测量树木胸径DBH

使用切片技术从点云中测量树木胸径DBH

胸高直径（DbH）是树木库存调查的重要树木指标和元数据，它可以指示树木的相对年龄、大小和林业发展阶段。在MMS（移动测绘系统）LiDAR点云中，集合近年来一直在快速增长。在这项试点研究中，我们将通过对路边的目标树群进行切片，将这些收集到的点云用于城市林业和遥感。 具有…
阅读更多...
vector类详解及重要函数实现

vector类详解及重要函数实现

&#x1fa90;&#x1fa90;&#x1fa90;欢迎来到程序员餐厅&#x1f4ab;&#x1f4ab;&#x1f4ab; 今日主菜&#xff1a;vector类 主厨&#xff1a;邪王真眼 所属专栏&#xff1a;c专栏 主厨的主页&#xff1a;Chef‘s blog 坚持下去&#xff0c;成功不是目的&a…
阅读更多...
抖音IP属地怎么更改

抖音IP属地怎么更改

抖音是一个非常受欢迎的短视频平台&#xff0c;吸引了无数用户在上面分享自己的生活和才艺。然而&#xff0c;随着快手的火爆&#xff0c;一些用户开始担心自己的IP地址会被他人获取&#xff0c;引起个人隐私风险。那么&#xff0c;抖音用户又该如何更改到别的地方呢&#xff1…
阅读更多...
babyos 学习记录

babyos 学习记录

宏定义头文件 将一个宏定义取不同的数据到不同的数组中&#xff1b; 侵入式链表 struct list_head { struct list_head *next, *prev; }; // 添加&#xff08;list_add_tail/list_add&#xff09;、删除、查找 xx.h // 定义一个用于链表管理的结构体 typedef sturct{ xxx …
阅读更多...
2024-03-24 思考-MBTI-简要记录

2024-03-24 思考-MBTI-简要记录

摘要: 2024-03-24 思考-MBTI-简要记录 MBTI16型人格: MBTI16型人格在人格研究和评价中得到了广泛的应用。MBTI是一种基于瑞士心理学家荣格在理论基础上发展起来的人格分类工具。为了准确判断个人的心态偏好&#xff0c;将每个人分为16种不同的人格类型。这种分类方法不仅为我们…
阅读更多...
Websocket + Vue使用

Websocket + Vue使用

这里有一篇文档可以参考一下> 闪现 POM文件 <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-websocket</artifactId><version>2.7.0</version> </dependency> WebSocketConf…
阅读更多...
手撕算法-删除有序数组中的重复项

手撕算法-删除有序数组中的重复项

描述 很简单&#xff0c;就是&#xff0c;遇到重复的&#xff0c;只留一个&#xff0c;保存在数组的左半边。如&#xff1a;[0,0,1,1,1,2,2,3,3,4]变为[0,1,2,3,4] 分析 使用双指针。slow指针代表没重复的数应该放置的位置&#xff0c;fast表示遍历的不重复数字的位置&…
阅读更多...
【Linux更新驱动、cuda和cuda toolkit】

【Linux更新驱动、cuda和cuda toolkit】

目录 1. 更新显卡驱动1.1. 查看当前显卡驱动版本1.2. 删除原始显卡驱动1.3. 删除CUDA Toolkit1.4. 在NVIDIA官网找到2080Ti对应的最新驱动程序 2. 更新CUDA Toolkit2.1. 下载CUDA Toolkit2.2. 安装.run2.3. 添加环境变量2.4. 检查是否安装好了 最近需要更新服务器的显卡驱动和C…
阅读更多...
SpringBoot3使用响应Result类返回的响应状态码为406

SpringBoot3使用响应Result类返回的响应状态码为406

Resolved [org.springframework.web.HttpMediaTypeNotAcceptableException: No acceptable representation] 解决方法&#xff1a;Result类上加上Data注解
阅读更多...
文件包含一-WEB攻防-PHP应用文件包含LFIRFI伪协议编码算法无文件利用黑白盒

文件包含一-WEB攻防-PHP应用文件包含LFIRFI伪协议编码算法无文件利用黑白盒

演示案例&#xff1a; 文件包含-原理&分类&利用&修复黑盒利用-VULWEB-有无包含文件白盒利用-CTFSHOW-伪协议玩法 #文件包含-原理&分类&利用&修复 1、原理 程序开发人员通常会把可重复使用的函数写到单个文件中&#xff0c;在使用某些函数时&#xff0c…
阅读更多...
YOLOV5 改进：替换backbone为Swin Transformer

YOLOV5 改进：替换backbone为Swin Transformer

1、前言 本文会将YOLOV5 backbone更换成Swin Transformer 具体为什么这样实现参考上文：YOLOV5 改进：替换backbone（MobileNet为例）-CSDN博客 这里只贴加入的代码 训练结果如下： 2、common文件更改 在common文件中加入下面代码： 这里是swin transformer的实现，参考：…
阅读更多...
React函数组件Hook

React函数组件Hook

问题: 相对于类组件, 函数组件的编码更简单, 效率也更高, 但函数组件不能有state (旧版) 解决: React 16.8版本设计了一套新的语法来让函数组件也可以有state Hook 是 React 16.8 的新增特性。它可以让你在不编写 class 的情况下使用 state 以及其他的 React 特性 Hook也叫钩子…
阅读更多...
软件测试 -- Selenium常用API全面解答（java）

软件测试 -- Selenium常用API全面解答（java）

写在前面 // 如果文章有问题的地方, 欢迎评论区或者私信指正 目录 什么是Selenium 一个简单的用例 元素定位 id定位 xpath定位 name定位 tag name 定位和class name 定位 操作元素 click send_keys submit text getAttribute 添加等待 显示等待 隐式等待 显示等…
阅读更多...
【网络安全】CobaltStrike 使用

【网络安全】CobaltStrike 使用

本文章仅用于信息安全学习&#xff0c;请遵守相关法律法规&#xff0c;严禁用于非法途径。若读者因此作出任何危害网络安全的行为&#xff0c;后果自负&#xff0c;与作者无关。 Cobalt Strike是一款渗透测试神器&#xff0c;Cobalt Strike已经不再使用MSF而是作为单独的平台使…
阅读更多...
[NLP] 初窥000001

[NLP] 初窥000001

NL(natural language)–自然语言 人类的语言–中文&#xff0c;英语&#xff0c;法语 NLP(Natural Language Processing)–自认语言处理 计算机处理人类语言的技术&#xff0c;它包含翻译、智能问答、文本分类、情感分析等常见应用。 CV(Computational Vision) 感知NLP 认知…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023