2.2.2 MySQL基本功能与参数文件管理
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了2.2.2 MySQL基本功能与参数文件管理，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6418字，纯文字阅读大概需要10分钟。
内容图文
MySQL数据库管理-实战案例5
需求：目前业务系统运行比较慢，怀疑是mysql数据库内存参数少低，需要修改 1.请查看当前 innodb_buffer_pool 参数大小。 2.请根据实际物理内存来临时设置全局参数 innodb_buffer_pool 3.再将 innodb_buffer_pool 参数设置永久生效，修改之前请备份当前参数文件。
mysql root@localhost:(none)> show variables like 'innodb_buffer_pool%'
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
10 rows in set
Time: 0.012s
mysql root@localhost:(none)> select @@innodb_buffer_pool_size
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 134217728 |
+---------------------------+
1 row in set
Time: 0.005s
mysql root@localhost:(none)> set global innodb_buffer_pool_size=805306368
Query OK, 0 rows affected
Time: 0.001s
在参数文件中设置参数 my.cnf
innodb_buffer_pool_size=768M
重启生效，再检查
MySQL数据库管理-实战案例6
MySQL 日常维护过程中，因系统出现问题，请收集各种日志文件、服务器各种状态信息并提交线二线工程师分析。错误日志（ error log）: show variables like '%log_error%'
二进制日志（ binlog）: show variables like '%log_bin%';
慢查询日志（ slow query log）:
show variables like 'slow_query_log';
show variables like 'long_query_time';
全局日志（log）: show variables like "%general%";
中继日志（relay_log）: show variables like '%relay%';1、查看数据库版本
show variables like 'version'
2、列出 MySQL Server上的数据库。
SHOW DATABASES
3、查看服务器状态
mysql> status
mysql> show status
4、査看数据库存储引擎
mysql> show engines
5、查看引擎插件
mysql> show plugins
6、查看引擎状态
mysql> Show engine innodb status
7、查看数据库进程信息
mysql> show processlist
有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态帮助识别出有问题的查询语句等
8、查看数据库参数
mysql> show variables
9、查看当前数据库的字符集
mysql> show collation like '%utf8%';
10、查看当前数据库的校对规则
mysql> show variables like 'collation%'
11、查看是否配置 Replication：
show master status
show slave statusmysql root@localhost:(none)> show variables like 'innodb_buffer%'
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
11 rows in set
Time: 0.019s
mysql root@localhost:(none)> select @@innodb_buffer_pool_size
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 134217728 |
+---------------------------+
1 row in set
Time: 0.008s
mysql root@localhost:(none)> set global innodb_buffer_pool_size=805306368
Query OK, 0 rows affected
Time: 0.006s
mysql root@localhost:(none)> select @@innodb_buffer_pool_size
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 805306368 |
+---------------------------+
1 row in set
Time: 0.008s
mysql root@localhost:(none)> show variables like 'log_error%'
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| log_error | /var/log/mysqld.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
+----------------------------+----------------------------------------+
4 rows in set
Time: 0.009s
mysql root@localhost:(none)> show variables like 'log_bin%'
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-----------------------------+
5 rows in set
Time: 0.009s
mysql root@localhost:(none)> show variables like 'slow_quer%'
+---------------------+-------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/dbhost-2020-slow.log |
+---------------------+-------------------------------------+
mysql root@localhost:(none)> show variables like 'general%'
+------------------+--------------------------------+
| Variable_name | Value |
+------------------+--------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/dbhost-2020.log |
+------------------+--------------------------------+内容总结
以上是互联网集市为您收集整理的2.2.2 MySQL基本功能与参数文件管理全部内容，希望文章能够帮你解决2.2.2 MySQL基本功能与参数文件管理所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】