How to forcibly create/modify MySQL user grants, without restarting the database service

I am familiar with how to start MySQL server with the "--init-file" option, or the "--skip-grant-tables" options.

However I have a nagios based mysql monitor, which needs to query a table, and therefore I need to configure a user (GRANT SELECT ON heartbeart to 'nagios-monitor'@'123.123.123.123') 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 change the passwords)

• I want to be able to add monitoring without causing any downtime

• Other strategies to manage users/passwords are fine, but again, I still need to apply those grants without restarting the database.

Notes

• If I install the mysql from scratch, then I add a "dba" type user similar to 'debian-sys-maint' 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.

Possible Solutions

• 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

sudo kill -STOP $(cat /var/run/mysqld/mysqld.pid)

and then start -

sudo kill -CONT $(cat /var/run/mysqld/mysqld.pid)

How to force the main mysql to re-read the tables from disk?

-
some sort of scheduled task that is run by mysqld? could append my commands to that, as its not particularly urgent to run the grants.

Multiple mysqld

http://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html


Beware of the dangers of using multiple mysqld servers with the same
data directory.

You could copy just the the "mysql" database away to another location and start another daemon on it. Get the SHA1 or DES hash stored in the user table for a user with SUPER privs (usually root, but sometimes renamed for security through obscurity).

Then connect to the mysql using a modified version of the client library that makes mysql_real_connect() support using a pre-hashed password instead of having it take the password plaintext. This should be trivial.

You won't ever know the actual password, but with the hash and a modified client you'll be able to log in anyway.

You can then make any modifications to permissions, create necessary schema and tables and flush privileges.

I'll leave the security implications of such practices up to you.

StackExchange Database Administrators Q#33318, answer score: 2