MySQL 数据库性能 一键调优工具 tuning-primer.sh
1 背景知识
2 操作系统准备
#root>
dnf install perl -y
3 调优工具安装
#root>
cd ~
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
4 调优工具使用
4.1 执行一键调优脚本
#root>
perl mysqltuner.pl
4.2 输入 MySQL 数据库用户与密码
>> MySQLTuner 2.6.1
* Jean-Marie Renouard <jmrenouard@gmail.com>
* Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering
ℹ Skipped version check for MySQLTuner script
ℹ Using mysql to check login
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
4.3 查看调优结果
-------- Recommendations -----------
General recommendations:
Check warning line(s) in /var/log/mysqld.log file
Check error line(s) in /var/log/mysqld.log file
1 CVE(s) found for your MySQL release. Consider upgrading your version !
MySQL was started within the last 24 hours: recommendations may be inaccurate
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
MyISAM engine is deprecated, consider migrating to InnoDB
Buffer Key MyISAM set to 0, no MyISAM table detected
Be careful, increasing innodb_redo_log_capacity means higher crash recovery mean time
Variables to adjust:
skip-name-resolve=ON
key_buffer_size=0
innodb_buffer_pool_size (>= 10.8G) if possible.
innodb_redo_log_capacity should be (=32M) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.
innodb_log_buffer_size (> 64M)
5 添加调优参数
根据建议的调优结果进行参数调优。
- 编辑
my.cnf文件。
vi /etc/my.cnf
- 填写参数内容。
skip-name-resolve=ON
key_buffer_size=0
innodb_buffer_pool_size=11G
innodb_redo_log_capacity=512M
innodb_log_buffer_size =512M
6 调优结果验证
systemctl restart mysqld.service
ksql -u root -p
show variables where variable_name in ('skip-name-resolve','key_buffer_size','innodb_buffer_pool_size','innodb_redo_log_capacity','innodb_log_buffer_size');
+--------------------------+-------------+
| Variable_name | Value |
+--------------------------+-------------+
| innodb_buffer_pool_size | 11811160064 |
| innodb_log_buffer_size | 536870912 |
| innodb_redo_log_capacity | 536870912 |
| key_buffer_size | 0 |
+--------------------------+-------------+
4 rows in set (0.00 sec)