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.005smysql 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 status 操作結果 mysql 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.019smysql root@localhost:(none)> select @@innodb_buffer_pool_size +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 134217728 | +---------------------------+ 1 row in set Time: 0.008smysql root@localhost:(none)> set global innodb_buffer_pool_size=805306368 Query OK, 0 rows affected Time: 0.006smysql root@localhost:(none)> select @@innodb_buffer_pool_size +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 805306368 | +---------------------------+ 1 row in set Time: 0.008smysql 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.009smysql 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.009smysql 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基本功能与参数文件管理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。