No data pmm MySQL Replication Summary

Percona Monitoring and Management (PMM)
, ,
1

add mysql service

pmm-admin add mysql mysql8 --query-source=perfschema --username=pmm --password=xxxxxxx --replication-set=192.168.17.89

[root@cellsoft-m1-v1-node1 ~]# pmm-admin list
Service type Service name Address and port Service ID
MySQL mysql8 127.0.0.1:3306 /service_id/d59fa380-5436-42fd-88ab-e7f105b664ce

Agent type Status Metrics Mode Agent ID Service ID
pmm_agent Connected /agent_id/65f92f63-3347-415f-be99-23525a82bdc3
node_exporter Running push /agent_id/b199aa81-82a5-4b97-8cdb-88b261ee31bb
mysqld_exporter Running push /agent_id/f35e4bbc-2fa7-47fc-ab12-a70b6b00e118 /service_id/d59fa380-5436-42fd-88ab-e7f105b664ce
mysql_perfschema_agent Running /agent_id/361e0d9b-27a4-484b-97c9-1644d3ccf95d /service_id/d59fa380-5436-42fd-88ab-e7f105b664ce
vmagent Running push /agent_id/cc4cf691-f525-469d-870a-aff678484c56

pmm-admin status

Agent ID: /agent_id/65f92f63-3347-415f-be99-23525a82bdc3
Node ID : /node_id/67dc9e17-7b7c-4b33-ad51-9d2e96e4a0c3

PMM Server:
URL : https://192.168.17.89:443/
Version: 2.23.0

PMM Client:
Connected : true
Time drift : 158.81204ms
Latency : 691.555”s
pmm-admin version: 2.23.0
pmm-agent version: 2.23.0
Agents:
/agent_id/361e0d9b-27a4-484b-97c9-1644d3ccf95d mysql_perfschema_agent Running
/agent_id/b199aa81-82a5-4b97-8cdb-88b261ee31bb node_exporter Running
/agent_id/cc4cf691-f525-469d-870a-aff678484c56 vmagent Running
/agent_id/f35e4bbc-2fa7-47fc-ab12-a70b6b00e118 mysqld_exporter Running

and that

219aaa9d4f47fa18a03d063f692c73d
219aaa9d4f47fa18a03d063f692c73d800×181 13.8 KB

how to do resolve it ?
thank you very much.

2

@KevinU
Please log in manually using the same pmm user/pass you used when adding the service. Can you execute ‘SHOW SLAVE STATUS’? If not, you have a permissions problem and you need to GRANT REPLICATION CLIENT to the PMM user.

1 Like
3

thank you reply
on server 88:

mysql -h192.168.17.88 -upmm -p

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34876
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

mysql> show slave status\G;
Empty set, 1 warning (0.01 sec)

ERROR:
No query specified

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 152794
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 776a7b31-646c-11eb-aacb-000c291af149:1-21307
1 row in set (0.01 sec)

ERROR:
No query specified’’

on server 89:

mysql -upmm -p

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19353
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.17.88
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 152794
Relay_Log_File: cellsoft-m1-v1-node2-relay-bin.000004
Relay_Log_Pos: 152669
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 152794
Relay_Log_Space: 153354
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 776a7b31-646c-11eb-aacb-000c291af149
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 776a7b31-646c-11eb-aacb-000c291af149:1-21503
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

1 Like
4

@KevinU,
First thing, stop doing this:

show slave status\G;

\G is a terminator. You don’t need both \G and ; Pick one or the other, not both.

Next, your PMM screenshot is very small, hard to read, but I think it is showing server .88? Well, then it is correct. In your connection example below, you connected to .88 and ran SHOW SLAVE STATUS which says ‘Empty set’, so I can only assume this is your master/source server. Thus, there will not be any replication information in PMM. All replication information comes from the replicas. Switch to this server in PMM and you’ll see the data.

1 Like
5

server 88 is master
server 89 is slave
\G for easy to read.

1 Like
6

Yes, I said all that above. Since 88 is the master there will never be any PMM replication data for this server.

Yes, \G for easy reading, but don’t do “\G;” You don’t need the semi-colon after \G.

1 Like
7

Why pmm reading slave status from master node?

1 Like
8

PMM reads the same data from all nodes.

1 Like
9

Hi I change master to slave;
on server 89:
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show master status;
±--------------±---------±-------------±-----------------±---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±--------------±---------±-------------±-----------------±---------------------------------------------+
| binlog.000014 | 151915 | | | 776a7b31-646c-11eb-aacb-000c291af149:1-21503 |
±--------------±---------±-------------±-----------------±---------------------------------------------+
1 row in set (0.00 sec)

on server 88:
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.17.89’,MASTER_USER=‘repl’,MASTER_PASSWORD=‘cellsoft@My888’,MASTER_LOG_FILE=‘binlog.000014’,MASTER_LOG_POS=151915;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
Level: Note
Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the ‘START SLAVE Syntax’ in the MySQL Manual for more information.
2 rows in set (0.01 sec)

ERROR:
No query specified

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.17.89
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000014
Read_Master_Log_Pos: 151915
Relay_Log_File: cellsoft-m1-v1-node1-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: binlog.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 151915
Relay_Log_Space: 545
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 776a7b31-646c-11eb-aacb-000c291af888
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 776a7b31-646c-11eb-aacb-000c291af149:1-21307
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
the pmm still empty?

image
image2442×1091 169 KB

please help to me? thank you very much.

10

I switch over master to slave;
on server 89:
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show master status;
±--------------±---------±-------------±-----------------±---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±--------------±---------±-------------±-----------------±---------------------------------------------+
| binlog.000014 | 151915 | | | 776a7b31-646c-11eb-aacb-000c291af149:1-21503 |
±--------------±---------±-------------±-----------------±---------------------------------------------+
1 row in set (0.00 sec)

on server 88:
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.17.89’,MASTER_USER=‘repl’,MASTER_PASSWORD=‘cellsoft@My888’,MASTER_LOG_FILE=‘binlog.000014’,MASTER_LOG_POS=151915;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
Level: Note
Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the ‘START SLAVE Syntax’ in the MySQL Manual for more information.
2 rows in set (0.01 sec)

ERROR:
No query specified

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.17.89
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000014
Read_Master_Log_Pos: 151915
Relay_Log_File: cellsoft-m1-v1-node1-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: binlog.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 151915
Relay_Log_Space: 545
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 776a7b31-646c-11eb-aacb-000c291af888
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 776a7b31-646c-11eb-aacb-000c291af149:1-21307
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)

ok.

image
image2493×527 50.4 KB

thank you.

1 Like