我学的 网站开发 能进华为公司么,制作企业网站页面html,门户网站建设与管理,wordpress ajax登录插件目录
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.…
目录
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.数据库与数据库实例 创建数据库
(rootlocalhost) [(none)] create database python_mysql;
Query OK, 1 row affected (0.03 sec) 删除数据库
drop database和drop schema是同义词都可以用来删除数据库
(rootlocalhost) [(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表结构定义文件 从数据库中查看表结构
(rootlocalhost) [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)
) ENGINEMyISAM DEFAULT CHARSETutf8 COLLATEutf8_bin COMMENTUsers 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)
解压安装
[rootlocalhost soft]# tar -zxvf mysql-utilities-1.6.5.tar.gz
[rootlocalhost soft]# cd mysql-utilities-1.6.5/
[rootlocalhost mysql-utilities-1.6.5]# python setup.py install
mysqlfrm查看表结构
mysqlfrm --diagnostic user.frm #不能查看字符集所以char字段长度比实际的乘以3了
mysqlfrm --diagnostic user.frm --serverroot:123456localhost #能看到字符集实际的字段长度
[rootlocalhost 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)
) ENGINEMyISAM COMMENT Users and global privileges;#...done.[rootlocalhost mysql]# mysqlfrm --diagnostic user.frm --serverroot:123456localhost
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)
) ENGINEMyISAM DEFAULT CHARSETutf8, COMMENT Users and global privileges;#...done.4.4慢查询日志 4.4.1慢查询相关参数 4.4.2慢查询参数默认值
(rootlocalhost) [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)(rootlocalhost) [mysql] show variables like long%;
----------------------------
| Variable_name | Value |
----------------------------
| long_query_time | 10.000000 |
----------------------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] show variables like min_examined%- ;
-------------------------------
| Variable_name | Value |
-------------------------------
| min_examined_row_limit | 0 |
-------------------------------
1 row in set (0.01 sec)(rootlocalhost) [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)(rootlocalhost) [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)(rootlocalhost) [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)(rootlocalhost) [(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后再次查看慢查询参数
(rootlocalhost) [(none)] show variables like long%;
---------------------------
| Variable_name | Value |
---------------------------
| long_query_time | 2.000000 |
---------------------------
1 row in set (0.01 sec)(rootlocalhost) [(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)(rootlocalhost) [(none)] show variables like min_examined_row_limit;
-------------------------------
| Variable_name | Value |
-------------------------------
| min_examined_row_limit | 100 |
-------------------------------
1 row in set (0.00 sec)(rootlocalhost) [(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)(rootlocalhost) [(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)(rootlocalhost) [(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_time2可在线修改执行超过2秒(2s)的语句就会被记录在slow.log执行时间是query_time-lock_time得到的值,但是如果设置了min_examined_row_limit100则结果少于100行的查询不会被记录在慢查询日志中
(rootlocalhost) [(none)] select sleep(2);
----------
| sleep(2) |
----------
| 0 |
----------
1 row in set (2.00 sec)
查看slow.log
[rootlocalhost 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
# UserHost: root[root] localhost [] Id: 2
# Query_time: 2.003868 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp1710817380;
select sleep(2);
4.4.7在线修改慢查询参数
在线修改slow_query_log、slow_query_log_file、long_query_time参数
(rootlocalhost) [(none)] set global slow_query_log0;
Query OK, 0 rows affected (0.00 sec)(rootlocalhost) [(none)] set global slow_query_log_file slow_new.log;
Query OK, 0 rows affected (0.00 sec)(rootlocalhost) [(none)] set global long_query_time 3;
Query OK, 0 rows affected (0.00 sec)(rootlocalhost) [(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)(rootlocalhost) [(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日志过大处理
如果慢查询日志过大在线重新设置慢查询日志
[rootlocalhost mysql_data]# mv slow.log slow.log.20240319
[rootlocalhost 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(rootlocalhost) [(none)] flush slow logs;
Query OK, 0 rows affected (0.00 sec)[rootlocalhost 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_limit100可以在线修改有session和global级别
(rootlocalhost) [(none)] show variables like min_examined%;
-------------------------------
| Variable_name | Value |
-------------------------------
| min_examined_row_limit | 100 |
-------------------------------
1 row in set (0.00 sec)(rootlocalhost) [(none)] select sleep(4);
----------
| sleep(4) |
----------
| 0 |
----------
1 row in set (4.00 sec)[rootlocalhost ~]# 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 Argument4.4.10log_queries_not_using_indexes参数
将没有使用索引的sql记录在慢查询日志中
4.4.11log_throttle_queries_not_using_indexes参数
限制每分钟记录没有使用索引sql语句的次数
设置每分钟有10次没使用索引就记录在慢查询日志log_throttle_queries_not_using_indexes10
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;
(rootlocalhost) [(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_log1
6.2general_log_file参数
将通用日志设置成文件general_log_fliegeneral.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表中查看
[rootlocalhost ~]# 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.(rootlocalhost) [(none)] set persist long_query_time8;
Query OK, 0 rows affected (0.08 sec)(rootlocalhost) [(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
(rootlocalhost) [performance_schema] select * from persisted_variables;
---------------------------------
| VARIABLE_NAME | VARIABLE_VALUE |
---------------------------------
| long_query_time | 8.000000 |
---------------------------------
1 row in set (0.00 sec)(rootlocalhost) [performance_schema] exit[rootlocalhost ~] cd /mysql80_data
[rootlocalhost 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
[rootlocalhost 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.(rootlocalhost) [(none)] reset persist;
Query OK, 0 rows affected (0.02 sec)(rootlocalhost) [(none)] select * from persisted_variables;
ERROR 1046 (3D000): No database selected
(rootlocalhost) [(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
(rootlocalhost) [performance_schema] select * from persisted_variables;
Empty set (0.00 sec)(rootlocalhost) [performance_schema] exit
Bye
[rootlocalhost mysql80_data]# cat mysqld-auto.cnf
{Version: 2}[rootlocalhost mysql80_data]#
8.存储引擎
除了innodb引擎其他引擎不建议用也就不建议学了后面再来学习innodb引擎
show engines;查看存储引擎
(rootlocalhost) [(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再次查看引擎
(rootlocalhost) [(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;
