Posts Tagged ‘mysql’

Centreon and batch-adding hosts

Monday, April 27th, 2009

Centreon is a nice GUI wrapper for Nagios. It is using MySQL as its configuration engine, and it functions quite well. One thing Cacti can do but Centreon can’t is mass automatic addition of servers. I have had a new site with an installed Centreon, and I wanted to add about 40 servers to be monitored. This is a tedious work, and I was searching for some semi-automatic method of doing it.
This is not perfect, but it worked for me.
In this case I do not replicate service-group relationship, but only add a mass of servers.

First – create a text file containing a list of servers and IPs. It should look like this:

I have placed in in /tmp/machines

Second – find the last host entry. In my case the DB name is Centreon, so I run the following command:

mysql -u root -p centreon -e’select host_id from host’

This should return a colum with numbers. Find the largest one and increment it by one. In my example the last one was 19, so my initial host_id will be 20.

You should now find the host_template_model_html_id you are to use. There are few methods for that, but the easiest way is to find another host information which matches to some level your desired information. In my case it was called “DB1”, so this looks like this:

mysql -u root -p centreon -e”select host_template_model_htm_id from host where host_name=’DB1′”

Please note that my blog formatting might change the quote character. You might not want to copy/paste it, but type it yourselves.

The result of the above query should give us a template ID. In my case it was “2”, which is fine by me.

If you want a better reference for the values entered, you can do a whole select for a single host to verify your values match mine:

mysql -u root -p centreon -e”select * from host where host_name=’DB1’G”

This should give you long listing and information of the host, as a reference.

My script goes like this, based on the assumptions made above:

for i in `cat /tmp/machines`
   NAME=`echo $i | cut -f1 -d:`
   IP=`echo $2 | cut -f2 -d:`
   echo "insert into host values ('$HOST',2,NULL,NULL,1,NULL,NULL,NULL,NULL,'$NAME','$NAME','$IP',NULL,NULL,'2','2','2','2','2',NULL,'2',NULL,NULL,'2','2','2','2',NULL,NULL,'2',NULL,NULL,'0',NULL,'1','1');" >> /tmp/insert_sql.sql
   echo "insert into extended_host_information values('',$HOST,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);" >> /tmp/insert_sql.sql
   let HOST++

This should create a file called /tmp/insert_sql.sql which then should be first reviewed, and then inserted into your database.

Needless to say – back up your database first, just in case:

mysqldump -u root -p –opt -B centreon > /tmp/centreon_backup.sql

and then insert the newly created data:

mysql -u root -p centreon < /tmp/insert_sql.sql

Notice – at this point, no service relationship is created. I think it is quite a chore only to create the nodes. Adding the service relationships complicates things a bit, and I did not want to go there at this specific stage. However, for few tenths of monitored hosts, this is quite a lifesaver.

Notice that this is only Centreon configuration, and you will be required to apply it (through the GUI) to Nagios.

WordPress Mu 2.7.1 and Multi-Site Manager

Friday, April 24th, 2009

I have neglected this blog due to routine work lately. I have done some things, but not much to write home about, and being swamped with work, I just lacked the initiative to add new stuff, or to dwell into new technologies and problems. Routine is routine, and upgrade to WordPress Mu sometimes introduces new issues.

A very useful plugin called “Multi-Site Manager” is available for, well, managing easily multi-site configuration of a single WPMU from the GUI.

I have been using it for a while, and I was happy with it, however, after the latest Mu upgrade (2.7.1), a single problem appeared – you cannot manage sites from a newly created domain. Already existing domains functionality was not changed, but only new ones.

I through myself knee deep into its MySQL data, and got to the following workaround. This should be incorporated into the code, but me being such a lame PHP coder, maybe it’s not the best idea that I do it…


You have created a new site, but from within its admin you cannot view the site admin panel. Manually entering the URL results in a “Permission Denied” error message.


Login to your MySQL DB.

Use the MU database.

Find your new site ID:

select * from wp_site;

Write down the site number. Also, write down another site number – a small site would be best.

Get the correct entry for site_admins from some other blog

Select * from wp_sitemeta where meta_key=’site_admins’;

Response should look something like this:

| meta_id | site_id | meta_key | meta_value |
| 8 | 1 | site_admins | a:1:{i:0;s:5:”adm”;} |
| 467 | 21 | site_admins | a:1:{i:0;s:5:”adm”;} |
| 245 | 13 | site_admins | a:1:{i:0;s:5:”adm”;} |
| 45 | 3 | site_admins | a:1:{i:0;s:5:”adm”;} |
| 28 | 2 | site_admins | a:1:{i:0;s:5:”adm”;} |
| 62 | 5 | site_admins | a:1:{i:0;s:5:”adm”;} |
| 126 | 8 | site_admins | a:1:{i:0;s:5:”adm”;} |

Create a site_admin entry for your new site:

insert into wp_sitemeta (site_id,meta_key,meta_value) values (’22’,’site_admins’,’a:1:{i:0;s:5:”adm”;}’);

Im my example, 22 is the site ID, obtained from the first query.

Following that, refresh your dashboard GUI and you should be able to find your “Site Admin” panel there. From here you can define your desired settings for the site as you see fit.

MySQL permissions for LVM Snapshots

Thursday, October 23rd, 2008

aking LVM snapshots as a mean of backing up MySQL is rather simple, as can be described here. However, if you are into security, you would strive to grant minimal permissions for the action to the MySQL user. Per MySQL Documentation, the required privileges is “RELOAD”. That should be enough, granted on *.*, of course.

LVM Snapshots with MySQL

Saturday, December 2nd, 2006

Nowadays, when LVM2 is common and is actually the default in installation of RedHat based distributions, using its snapshot capabilities can save lots of grief when files are deleted or when you need to revert to a day in the past – both for your files and for your MySQL DB.

I have created a script which is based on the following assumptions:

1. Inside /etc/samba/smb.conf there is a directive such as: include /etc/samba/smb.conf

2. There is a single LV containing all the system’s data. It doesn’t occupy all the physical disk (or, for the matter, the entire VG space). Free space is 10-20% of disk size

3. Specific share directives are located inside /etc/samba/smb.conf.snapshot.full. An empty file /etc/samba/smb.conf.snapshot.empty exists.

4. I do not trust all places to hold a password for their MySQL (although it is advised!). This script assumes such password doesn’t always exist

5. The script mounts the snapshot read-only just after creating an empty file with the date of the snapshot inside its root.

The script is attached here. take-snapshot.txt