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

MySQL & MariaDB Other MySQL® Questions
1

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 ‘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 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.

Notes

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.

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 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
/etc/my.cnf
, 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)

2

You do not have to restart MySQL to pickup new users / privileges. If you use the built-in commands like GRANT and CREATE USER, the grant tables are automatically reloaded into memory. If you edit the grant tables manually (i.e. using UPDATE or INSERT), then you just need to run FLUSH PRIVILEGES after to reload the grant tables into memory.

The one tricky part is knowing when your changes will take affect. From the MySQL manual:

  1. Table and column privilege changes take effect with the client’s next request.

  2. Database privilege changes take effect the next time the client executes a USE db_name statement.

  3. Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

https://dev.mysql.com/doc/refman/5.5/en/privilege-changes.ht ml

3

Hi,
Thanks for the reply.

use the built-in commands like GRANT and CREATE USER, the grant tables are automatically reloaded into memory.

So I am referring to a scenario in which I have root access to the underlying server, but I do not have any login to the mysql service.

That is to say, I am in this position;

[root@myserver ~]# id uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) context=user_u:system_r:unconfined_t[root@myserver ~]# #…

not this position;

mysql> SELECT USER(),CURRENT_USER()\G*************************** 1. row *************************** USER(): root@localhostCURRENT_USER(): root@localhost1 row in set (0.00 sec)