I am familiar with how to start MySQL server with the “–init-file” option, or the “–skip-grant-tables” options.
However I am using the percona nagios based mysql nrpe monitor, which needs to query a table, and therefore I need to configure a user (GRANT SELECT ON heartbeart to ‘nagios-monitor’ at ‘18.104.22.168’) for that operation with appropriate permissions granted to the nagios server host, or nrpe script host.
I’d like to have an automated installer script, to configure the table. Hence I would like to be able to carry out these operations automated, with little risk to the underlying application uptime.
It is not always clear what the mysql root user password is. (targets are arbitrarily used developer and test boxes, and they may change the password, or some other package may configure mysql-server)
I want to be able to add monitoring without causing any downtime, or reset of the connections.
- Other strategies to manage users/passwords are fine, but again, I still need to apply those grants without restarting the database.
If I install the mysql from scratch, (using chef/puppet/gradle etc) then the job adds a “dba” type user, in addition to the root user, with a complex password, which sidesteps the above problem. - this is not the use-case that i am considering here.
suggestion: Spin up a second mysqld process on port 3307 (with the --init-file option to create my nagios-monitor user), pointed at the same /var/lib/mysql, but masked to only open the “mysql” database
consequences?: probably corrupt everything - could pause the running mysqld;
sudo kill -STOP $(cat /var/run/mysqld/mysqld.pid)
and then start it again
sudo kill -CONT $(cat /var/run/mysqld/mysqld.pid)
But how to force the main mysqld to re-read the tables from disk?
I considered injecting some sort of scheduled task that is run by mysqld?
Is there anything appropriate that I could append my commands to there, as its not particularly urgent to run the grants.
are there any signals to cause a re-read of the
, presumably then I could add something useful… (though that is also not a preferred solution, because for similar reasons, I don’t want to have impact on the existing customized configuration)
(Also tracking this Q on dba stackexchange)