大纲
- 环境
- 安装配置Mysql
- 安装
- 设置root密码
- 新增远程访问账户
- 修改绑定地址
- 重启
- 新增 MySQL Server Exporter 用户
- 安装启动mysqld_exporter
- 安装
- 启动
- 新增配置
- 启动
- 安装启动Prometheus
- 创建用户
- 下载并解压
- 修改配置
- 启动
- 安装启动grafana
- 安装
- 启动
- 测试
- 参考资料
抛开场景和数据,谈论性能优化,就是纸上谈兵。这个系列我们将通过相关数据来展现常见的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
安装启动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