mysql

MySQL 5.7 使用PMM监视和管理数据库

Posted by Sunday on 2018-05-14

Server端安装

server端安装非常简单，我们采用docker的方式来部署。

1.拉取镜像

1
docker pull percona/pmm-server:latest

2.创建PMM数据容器

1
2
3
4
5
6
7
docker create \
   -v /opt/prometheus/data \
   -v /opt/consul-data \
   -v /var/lib/mysql \
   -v /var/lib/grafana \
   --name pmm-data \
   percona/pmm-server:latest /bin/true

这里Docker映射目录会导致一系列问题，如mysql无法启动。
解决请看 在PMM Docker部署上使用不同的挂载点

3.创建PMM服务器容器

1
2
3
4
5
6
7
8
9
docker run -d \
   -p 81:80 \
   --volumes-from pmm-data \
   -e SERVER_USER=pmm \
   -e SERVER_PASSWORD=sunday \
   -e ORCHESTRATOR_ENABLED=true \
   --name pmm-server \
   --restart always \
   percona/pmm-server:latest

Client安装

在每个需要性能统计的MySQL服务器上安装pmm-client

1
2
3
4
wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
apt-get install pmm-client

连接到服务器并向服务器上报数据

1
pmm-admin config --server 192.168.1.38:81 --server-user pmm --server-password sunday

在每个需要性能统计的MySQL服务器mysql授权

1
2
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@' localhost' IDENTIFIED BY 'sunday' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO 'pmm'@'localhost';

收集数据

1
pmm-admin add mysql --user pmm --password sunday --socket /tmp/mysql.sock

更好的性能监控
percona mysql设置：在线修改

1
SET GLOBAL <var_name>=<var_value>

percona mysql配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
log_output=file
slow_query_log=ON
long_query_time=0
log_slow_rate_limit=100
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
innodb_monitor_enable=all
userstat=1

#If you are running MySQL 5.6+ or MariaDB 10.0+, configure Configuring Performance Schema.
innodb_monitor_enable=all
performance_schema=ON

映射目录 pmm-server

创建pmm-data

1
2
3
4
5
6
7
docker create \
   -v /opt/prometheus/data \
   -v /opt/consul-data \
   -v /var/lib/mysql \
   -v /var/lib/grafana \
   --name pmm-data \
   percona/pmm-server:latest /bin/true

创建pmm-server 它会自动初始化

1
2
3
4
5
6
7
8
9
docker run -d \
   -p 81:80 \
   --volumes-from pmm-data \
   -e SERVER_USER=pmm \
   -e SERVER_PASSWORD=sunday \
   -e ORCHESTRATOR_ENABLED=true \
   --name pmm-server \
   --restart always \
   percona/pmm-server:latest

新建目录进行拷贝

1
2
mkdir -pv /data/pmm-data/opt/prometheus/
mkdir -pv /data/pmm-data/var/lib/

拷贝docker pmm-data 到 指定目录

1
2
3
4
docker cp pmm-data:/opt/prometheus/data /data/pmm-data/opt/prometheus/
docker cp pmm-data:/opt/consul-data /data/pmm-data/opt/consul-data
docker cp pmm-data:/var/lib/mysql /data/pmm-data/var/lib/mysql
docker cp pmm-data:/var/lib/grafana /data/pmm-data/var/lib/grafana

停止和删除pmm-server pmm-data

1
2
docker stop pmm-server pmm-data
docker rm pmm-server pmm-data

创建映射pmm-data

1
2
3
4
5
6
7
docker create \
   -v /data/pmm-data/opt/prometheus/data:/opt/prometheus/data \
   -v /data/pmm-data/opt/consul-data:/opt/consul-data \
   -v /data/pmm-data/var/lib/mysql:/var/lib/mysql \
   -v /data/pmm-data/var/lib/grafana:/var/lib/grafana \
   --name pmm-data \
   percona/pmm-server:latest /bin/true

修复权限

1
2
3
docker run --rm --volumes-from pmm-data -it percona/pmm-server:latest chown -R pmm:pmm /opt/prometheus/data /opt/consul-data
docker run --rm --volumes-from pmm-data -it percona/pmm-server:latest chown -R grafana:grafana /var/lib/grafana
docker run --rm --volumes-from pmm-data -it percona/pmm-server:latest chown -R mysql:mysql /var/lib/mysql

运行pmm-server

1
2
3
4
5
6
7
8
9
docker run -d \
   -p 81:80 \
   --volumes-from pmm-data \
   -e SERVER_USER=pmm \
   -e SERVER_PASSWORD=sunday \
   -e ORCHESTRATOR_ENABLED=true \
   --name pmm-server \
   --restart always \
   percona/pmm-server:latest

mysql、mariadb 更好的性能监控，请看这

https://www.percona.com/doc/percona-monitoring-and-management/deploying.server.virtual-appliance.html
https://www.percona.com/doc/percona-monitoring-and-management/conf-mysql.html
https://www.percona.com/doc/percona-monitoring-and-management/deploy/server/docker.setting-up.html#creating-and-launching-the-pmm-server-container
PMM Docker MySQL not starting
在PMM Docker部署上使用不同的挂载点

CATALOG
    FEATURED TAGS
    mysql
    FRIENDS