Mysql使用中的性能优化——搭建Mysql的监测服务

抛开场景和数据，谈论性能优化，就是纸上谈兵。这个系列我们将通过相关数据来展现常见的Mysql优化前后的性能差距。这样会给大家有个直观的认识。

环境

我是在Windows电脑上，使用Hyper-V构建了一台干净的虚拟机。并给该机器分配了2核4G。

操作系统是Ubuntu Server 24.04 LTS

cat /proc/version

Linux version 6.8.0-35-generic (buildd@lcy02-amd64-020) (x86_64-linux-gnu-gcc-13 (Ubuntu 13.2.0-23ubuntu4) 13.2.0, GNU ld (GNU Binutils for Ubuntu) 2.42) #35-Ubuntu SMP PREEMPT_DYNAMIC Mon May 20 15:51:52 UTC 2024

安装配置Mysql

安装

sudo apt install mysql-server

mysql --version

mysql Ver 8.0.36-2ubuntu3 for Linux on x86_64 ((Ubuntu))

设置root密码

sudo mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.36-2ubuntu3 (Ubuntu)
Copyright © 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

use mysql;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

ALTER USER 'root'@'localhost' IDENTIFIED BY '';
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;

将root用户的plugin从auth_socket改成mysql_native_password，并且设置密码为空，是为了让之后mysqld_exporter在build的过程中自检通过。

新增远程访问账户

这个账户主要是给Mysql client端使用。我们希望在其他机器上向这台机器发送请求以操作数据库，从而降低对Mysql所在机器的影响。

use mysql;
create user 'remoteuser' identified by 'fangliang';
ALTER USER 'remoteuser'@'%' IDENTIFIED BY 'fangliang';

修改绑定地址

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

修改

bind-address = 127.0.0.1

成为

bind-address = 0.0.0.0

重启

service mysql restart

==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
Authentication is required to restart ‘mysql.service’.
Authenticating as: fangliang
Password:
==== AUTHENTICATION COMPLETE ====

新增 MySQL Server Exporter 用户

该用户是用于mysqld_exporter采集信息，因为它安装在Mysql所在机器，所以只用配置本地访问即可。

sudo mysql -u root -p

use mysql;
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'fangliang' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

安装启动mysqld_exporter

安装

git clone https://github.com/prometheus/mysqld_exporter.git
cd mysqld_exporter
sudo apt install make
sudo apt install golang
make

启动

新增配置

cp test_exporter.cnf exporter.cnf
vim exporter.cnf

将原来内容

[client]
host=localhost
port=3306
socket=/var/run/mysqld/mysqld.sock
user=foo
password=bar
[client.server1]
user = bar
password = bar123

修改成

[client]
host=localhost
port=3306
socket=/var/run/mysqld/mysqld.sock
user=exporter
password=fangliang

启动

选择以下两种形式中任何一个都可以

直接启动

./mysqld_exporter --config.my-cnf=exporter.cnf

ts=2024-06-05T15:27:38.605Z caller=mysqld_exporter.go:240 level=info msg=“Starting mysqld_exporter” version=“(version=0.15.1, branch=main, revision=dd8afce2a46663a5112e53469f53ca56d50a63e2)”
ts=2024-06-05T15:27:38.605Z caller=mysqld_exporter.go:241 level=info msg=“Build context” build_context=“(go=go1.22.2, platform=linux/amd64, user=fangliang@fangliang, date=20240605-15:21:01, tags=unknown)”
ts=2024-06-05T15:27:38.605Z caller=mysqld_exporter.go:253 level=info msg=“Scraper enabled” scraper=global_status
ts=2024-06-05T15:27:38.605Z caller=mysqld_exporter.go:253 level=info msg=“Scraper enabled” scraper=global_variables
ts=2024-06-05T15:27:38.605Z caller=mysqld_exporter.go:253 level=info msg=“Scraper enabled” scraper=slave_status
ts=2024-06-05T15:27:38.605Z caller=mysqld_exporter.go:253 level=info msg=“Scraper enabled” scraper=info_schema.innodb_cmpmem
ts=2024-06-05T15:27:38.605Z caller=mysqld_exporter.go:253 level=info msg=“Scraper enabled” scraper=info_schema.query_response_time
ts=2024-06-05T15:27:38.605Z caller=mysqld_exporter.go:253 level=info msg=“Scraper enabled” scraper=info_schema.innodb_cmp
ts=2024-06-05T15:27:38.605Z caller=tls_config.go:313 level=info msg=“Listening on” address=[::]:9104
ts=2024-06-05T15:27:38.605Z caller=tls_config.go:316 level=info msg=“TLS is disabled.” http2=false address=[::]:9104

以Service形式启动

cd /etc/systemd/system
sudo vim mysqld_exporter.service 

填入以下内容，并保存。

[Unit]
Description=Mysqld Exporter Service

[Service]
ExecStart=/home/fangliang/mysqld_exporter/mysqld_exporter --config.my-cnf=/home/fangliang/mysqld_exporter/exporter.cnf
Restart=always

[Install]
WantedBy=multi-user.target

然后启动该服务

sudo systemctl enable mysqld_exporter.service 
sudo systemctl start mysqld_exporter.service 

安装启动Prometheus

我们新开一台机器，同样给2核4G配置，更更新好系统和apt库。

创建用户

sudo groupadd --system prometheus
sudo useradd -s /sbin/nologin --system -g prometheus prometheus

下载并解压

wget https://github.com/prometheus/prometheus/releases/download/v2.52.0/prometheus-2.52.0.linux-amd64.tar.gz
tar xvf prometheus-2.52.0.linux-amd64.tar.gz

修改配置

cd prometheus-2.52.0.linux-amd64
vim prometheus.yml

新增如下内容

- job_name: 'mysqld_exporter'
    static_configs:
    - targets: ['172.23.142.99:9104']
      labels:
        app: mysqld_exporter
        node: node_exporter
        role: mysqld_exporter

启动

mkdir tsdb 
./prometheus --config.file ./prometheus.yml --storage.tsdb.path=./tsdb --web.listen-address="0.0.0.0:9090"  

安装启动grafana

安装

cd ~
sudo apt-get install -y adduser libfontconfig1 musl
wget https://dl.grafana.com/enterprise/release/grafana-enterprise_11.0.0_amd64.deb
sudo dpkg -i grafana-enterprise_11.0.0_amd64.deb

启动

sudo /bin/systemctl start grafana-server

测试

登录http://172.23.131.19:3000/login，使用用户名和密码都是admin登录grafana。

数据源选择Prometheus，地址填入刚启动的Promethous服务地址。

保存后，新建看板

填入https://grafana.com/grafana/dashboards/14057-mysql/


最后我们看到图标的展现了。

参考资料

• https://github.com/prometheus/mysqld_exporter
• https://www.cherryservers.com/blog/install-prometheus-ubuntu
• https://grafana.com/grafana/download?pg=get&plcmt=selfmanaged-box1-cta1
• https://www.cnblogs.com/wangyongqiang/p/15823372.html