MySQL变量查询与设置

本文主要是介绍MySQL变量查询与设置，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

变量查看与设置
mysql> show variables\G;
mysql> show variables like "变量名";
mysql> set global 变量名=值;
查看最大连接数
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
设置最大连接数
mysql> set global max_connections=1000;
刷新连接数
mysql> flush status;
查看连接超时
mysql> show variables like "connect_timeout";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| connect_timeout | 10 |
+-----------------+-------+
设置连接超时
mysql> set global connect_timeout=5;
查看线程状态
mysql> show status like "Threads%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 1 |
| Threads_connected | 1 |
| Threads_created | 2 |
| Threads_running | 1 |
+-------------------+-------+
查看连接进程
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 8 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
查看缓存线程数量
mysql> show variables like "thread_cache_size";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 9 |
+-------------------+-------+
查看线程缓冲打开表的数量
mysql> show variables like "table_open_cache";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 2000 |
+------------------+-------+
查看用于MyISAM引擎的关键索引缓存大小
mysql> show variables like "key_buffer_size";
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
查看查询缓存的一些参数
mysql> show variables like "%query_cache%";
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+

其他日志参数设置
错误日志
log_error[=文件名] //不指定为默认文件名
查询日志
general_log //启用日志
general_log-file=文件名 //不指定为默认文件名
慢查询日志
slow_query_log //启用日志
slow_query_log_file=文件名 //不指定为默认文件名
long-query_time=超时时间 //指定超时时间默认为10秒
log_queries_not_using_indexes=1 //记录未使用索引的sql查询

这篇关于MySQL变量查询与设置的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！