需求:
目前业务系统运行比较慢,怀疑是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 日常维护过程中,因系统出现问题,请收集各种日志文件、服务器各种状态信息并提交线二线工程师分析。
错误日志( 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.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 | +------------------+--------------------------------+