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'@'220.127.116.11') 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.
- 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.
- suggestion: Spin up a second mysqld process on port 3307 (with the
--init-fileoption 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.
Beware of the dangers of using multiple mysqld servers with the same data directory. Use separate data directories, unless you know what you are doing. Starting multiple servers with the same data directory does not give you extra performance in a threaded system.
5.3.1. Setting Up Multiple Data Directories
Normally, you should never have two servers that update data in the same databases. Even when the preceding precautions are observed, this kind of setup works only with MyISAM and MERGE tables, and not with any of the other storage engines.
mysql> SHOW TABLE STATUS where Name = 'user'\G *************************** 1. row *************************** Name: user **Engine: MyISAM** Version: 10 Row_format: Dynamic Rows: 27 Avg_row_length: 91 Data_length: 2476 Max_data_length: 281474976710655 Index_length: 2048 Data_free: 0 Auto_increment: NULL Create_time: 2012-04-12 11:46:21 Update_time: 2013-01-22 00:16:00 Check_time: 2013-01-19 20:52:09 Collation: utf8_bin Checksum: NULL Create_options: Comment: Users and global privileges 1 row in set (0.00 sec)
So I spun up a 2nd mysqld on port 3307, with a masked datadir like so;
# mkdir /var/lib/mysql2/ # ln -s /var/lib/mysql/mysql /var/lib/mysql2/mysql # /usr/sbin/mysqld --basedir=/usr \ --datadir=/var/lib/mysql2 \ --plugin-dir=/usr/lib64/mysql/plugin \ --user=mysql \ --log-error=/var/lib/mysql2/server-64654.err \ --pid-file=/var/lib/mysql2/server-64654.pid \ --socket=/var/lib/mysql2/mysql.sock \ --port=3307 \ --init-file=/var/cache/chef/grants.sql \ --datadir=/var/lib/mysql2/
with a grants.sql;
GRANT ALL ON *.* TO 'nagios-user'@'127.0.0.1' IDENTIFIED BY 'xxx' WITH GRANT OPTION; FLUSH PRIVILEGES; mysql --port=3307 -h 127.0.0.1 -e 'select User from mysql.user' +-------------+ | User | +-------------+ | root | | nagios_user |
But the original mysql,doesn;t pick it up until it has been restarted! gah!
mysql --port=3306 -h 127.0.0.1 -e 'select User from mysql.user' +-------------+ | User | +-------------+ | root |