I'd set up a grafana server to collect some metrics from a few servers, and as part of that I wanted to get some Mysql data from a couple of them. Easy right? So on the first server I logged into mysql and set up a user for this:
CREATE USER 'telegraf-client'@'localhost' IDENTIFIED BY 'xxxxxxx'; GRANT REPLICATION CLIENT ON *.* TO 'telegraf-client'@'localhost' WITH MAX_USER_CONNECTIONS 5; GRANT PROCESS ON *.* TO 'telegraf-client'@'localhost'; GRANT SELECT ON performance_schema.* TO 'telegraf-client'@'localhost'; FLUSH PRIVILEGES;
And the in /etc/telegraf/telegraf.conf I added:
[[inputs.mysql]] servers = ["telegraf-client:xxxxxxxxxxx@tcp(127.0.0.1:3306)/"]
All good. Restart telegraf with systemctl and off we go: data flowed into grafana in a veritable torrent.
On the second server, I did EXACTLY THE SAME THING, but all I could see in the mysql error log was [Warning] Access denied for user ' telegraf-client'@'127.0.0.1' (using password: YES)
I tried everything I could think of over several days. I tried localhost instead of the IP. I tried different quotes, I tried spaces in the brackets, I tried specifying information_schema database. I trawled forums. I tried deleting the user and recreating it as something else (maybe it didn't like the hyphen? getting desperate here). Nope.
I tried using the root user and deb-sys-maint. Nope. From the command line it would connect with 'localhost' but not 127.0.0.1. I checked the hosts file, the mysql config. Many many hours wasted.
Here is what worked. I have no idea why. The servers and setups were identical. This is the answer to one of the Mysteries of Linux:
servers = ["telegraf-client:xxxxxxxxxx@unix(/var/run/mysqld/mysqld.sock)/"]
May it save you many hours.