PlanetMysql.ru — информация о СУБД MySQL

If you're using MySQL replication, then you're probably counting on it for some fairly important need. Monitoring via Nagios is generally considered a best practice. This article assumes you've already got your Nagios server setup and your intention is to add a Ubuntu 10.04 NRPE client. This article also assumes the Ubuntu 10.04 NRPE client is your MySQL replication master, not the slave. The OS of the slave does not matter.

Getting the Nagios NRPE client setup on Ubuntu 10.04

At first it wasn't clear what packages would be appropriate packages to install. I was initially mislead by the naming of the nrpe package, but I found the correct packages to be:

sudo apt-get install nagios-nrpe-server nagios-plugins

The NRPE configuration is stored in /etc/nagios/nrpe.cfg, while the plugins are installed in /usr/lib/nagios/plugins/ (or lib64). The installation of this package will also create a user nagios which does not have login permissions. After the packages are installed the first step is to make sure that /etc/nagios/nrpe.cfg has some basic configuration.

Make sure you note the server port (defaults to 5666) and open it on any firewalls you have running. (I got hung up because I forgot I have both a software and hardware firewall running!) Also make sure the server_address directive is commented out; you wouldn't want to only listen locally in this situation. I recommend limiting incoming hosts by using your firewall of choice.

Choosing what NRPE commands you want to support

Further down in the configuration, you'll see lines like command[check_users]=/usr/lib/nagios/plugins/check_users -w 5 -c 10. These are the commands you plan to offer the Nagios server to monitor. Review the contents of /usr/lib/nagios/plugins/ to see what's available and feel free to add what you feel is appropriate. Well designed plugins should give you a usage if you execute them from the command line. Otherwise, you may need to open your favoriate editor and dig in!

After verifying you've got your NRPE configuration completed and made sure to open the appropriate ports on your firewall(s), let's restart the NRPE service:

service nagios-nrpe-server restart

This would also be an appropriate time to confirm that the nagios-nrpe-server service is configured to start on boot. I prefer the chkconfig package to help with this task, so if you don't already have it installed:

sudo apt-get install chkconfig
chkconfig | grep nrpe

# You should see...
nagios-nrpe-server     on

# If you don't...
chkconfig nagios-nrpe-server on

Pre flight check - running check_nrpe

Before going any further, log into your Nagios server and run check_nrpe and make sure you can execute at least one of the commands you chose to support in nrpe.cfg. This way, if there are any issues, it is obvious now, while we've not started modifying your Nagios server configuration. The location of your check_nrpe binary may vary, but the syntax is the same:

check_nrpe -H host_of_new_nrpe_client -c command_name

If your command output something useful and expected, your on the right track. A common error you might see: Connection refused by host. Here's a quick checklist:

• Did you start the nagios-nrpe-server service?
• Run netstat -aunt on the NRPE client to make sure the service is listening on the right address and ports.
• Did you open the appropriate ports on all your firewall(s)?
• Is there NAT translation which needs configuration?

Adding the check_mysql_replication plugin

There is a lot of noise out there on Google for Nagios plugins which offer MySQL replication monitoring. I wrote the following one using ideas pulled from several existing plugins. It is designed to run on the MySQL master server, check the master's log position and then compare it to the slave's log position. If there is a difference in position, the alert is considered Critical. Additionally, it checks the slave's reported status, and if it is not "Waiting for master to send event", the alert is also considered critical. You can find the source for the plugin at my Github account under the project check_mysql_replication. Pull that source down into your plugins directory (/usr/lib/nagios/plugins/ (or lib64)) and make sure the permissions match the other plugins.

With the plugin now in place, add a command to your nrpe.cfg.

command[check_mysql_replication]=sudo /usr/lib/nagios/plugins/check_mysql_replication.sh -H 

At this point you may be saying, WAIT! How will the user running this command (nagios) have login credentials to the MySQL server? Thankfully we can create a home directory for that nagios user, and add a .my.cnf configuration with the appropriate credentials.

usermod -d /home/nagios nagios #set home directory
mkdir /home/nagios
chmod 755 /home/nagios
chown nagios:nagios /home/nagios

# create /home/nagios/.my.cnf with your preferred editor with the following:
[client]
user=example_replication_username
password=replication_password

chmod 600 /home/nagios/.my.cnf
chown nagios:nagios /home/nagios/.my.cnf

This would again be an appropriate place to run a pre flight check and run the check_nrpe from your Nagios server to make sure this configuration works as expected. But first we need to add this command to the sudoer's file.

nagios ALL= NOPASSWD: /usr/lib/nagios/plugins/check_mysql_replication.sh

Wrapping Up

At this point, you should run another check_nrpe command from your server and see the replication monitoring report. If not, go back and check these steps carefully. There are lots of gotchas and permissions and file ownership are easily overlooked. With this in place, just add the NRPE client using the existing templates you have for your Nagios servers and make sure the monitoring is reporting as expected.

I found Dennis the Menace, he now has a job as system administrator for a hosting company. Scenario: client has a problem with a server becoming unavailable (cause unknown) and has it restarted. MySQL had some page corruption in the InnoDB tablespace.

The hosting provider, being really helpful, goes in as root and first deletes ib_logfile* then ib* in /var/lib/mysql. He later says “I am sorry if I deleted it. I thought I deleted the log only. Sorry again.”  Now this may appear nice, but people who know what they’re doing with MySQL will realise that deleting the iblogfiles actually destroys data also. MySQL of course screams loudly that while it has FRM files it can’t find the tables. No kidding!

Then, while he’s been told to not touch anything any more, and I’m trying to see if I can recover the deleted files on ext3 filesystem (yes there are tools for that), he goes in again and puts an ibdata1 file back. No, not the logfiles – but he had those somewhere else too. The files get restored and turn out to be two months old (no info on how they were made in the first place but that’s minor detail in this grand scheme). All the extra write activity on the partition would’ve also made potential deleted file recovery more difficult or impossible.

This story will still get a “happy” ending, using a recent mysqldump to load a new server at a different hosting provider. Really – some helpfulness is not what you want. Secondary lesson: pick your hosting provider with care. Feel free to ask us from recommendations, we know some good/excellent and have encountered plenty bad.

So I’m looking for virtual servers in Europe for a new Python+MySQL based application and I found a provider with some good prices (Hosteurope.de). So I select the VM that I desire and go to the order form, but there’s no option for United States of America. I find this odd, so I email their support team to see why I can’t pay for services if I live in the USA. Here is their response: “Unfortunately we cannot accept your order due to internal policies” – What exactly is the internal policy where a company turns down sales specifically because the user is from the USA – I have verifiable credit, a real address, real bank accounts, and a real business. Yet hosteurope.de refuses legitimate business from the USA.

Does anyone know why this anti-USA policy is? And since they clearly don’t want my business, does anyone know of a good (and inexpensive) provider for virtual servers that aren’t anti-america?

—-
Dear Mr Reid

> I would very much like to use your services, however there was no option for
> USA in the country drop down menu when ordering – so I was forced to chose
> Austria. Can you tell me how I can order a virtual server from your company
> while residing in the United States of America? I can pay for a year of
> services in advance. I’m starting a new project and this could turn into a
> large account for your sales team. It would seem odd to turn away perfectly
> good business.
>
> Please let me know as I need to provision a server in Germany as soon as
> possible for my application.

Unfortunately we cannot accept your order due to internal policies. We apologize for the inconvenience.

Kind regards
Mit freundlichen Grüßen
Dominik Antulov

–
Dominik Antulov
Auftragsmanagement
Abteilung Hosting

E-Mail: support@hosteurope.de
Telefon: 0800 467 8387
Fax: +49 180 5 66 3233 (*)

+++ Nützliche Links
Host Europe FAQ (häufig gestellte Fragen): http://faq.hosteurope.de
Forum “Kunden helfen Kunden”: https://kis.hosteurope.de/forum/

Building and installing the Ruby mysql gem on freshly-installed Red Hat based systems sometimes produces the frustratingly ambiguous error below:

# gem install mysql
/usr/bin/ruby extconf.rb
checking for mysql_ssl_set()... no
checking for rb_str_set_len()... no
checking for rb_thread_start_timer()... no
checking for mysql.h... no
checking for mysql/mysql.h... no
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers.  Check the mkmf.log file for more
details.  You may need configuration options.

Searching the web for info on this error yields two basic solutions:

1. Install the mysql-devel package (this provides the mysql.h file in /usr/include/mysql/).
2. Run gem install mysql -- --with-mysql-config=/usr/bin/mysql_config or some other additional options.

These are correct but not sufficient. Because this gem compiles a library to interface with MySQL's C API, the gcc and make packages are also required to create the build environment:

# yum install mysql-devel gcc make
# gem install mysql -- --with-mysql-config=/usr/bin/mysql_config

Alternatively, if you're using your distro's ruby (not a custom build like Ruby Enterprise Edition), you can install EPEL's ruby-mysql package along with their rubygem-rails and other packages.

So you're a small startup company, ready to go live with your product, which you intend to distribute under an Open Source License. Congratulations, you made a wise decision! Your developers have been hacking away frantically, getting the code in good shape for the initial launch. Now it's time to look into what else needs to be built and setup, so you're ready to welcome the first members of your new community and to ensure they are coming back!

Keep the following saying in mind, which especially holds true in the Open Source world: "You never get a second chance to make a first impression!". While the most important thing is of course to have a compelling and useful product, this blog post is an attempt to highlight some other aspects about community building and providing the adequate infrastructure. This insight is based on my own experiences and my observations from talking with many people involved in OSS startups and projects.

Of course it’s not quite that simple. I’ve just decomissioned an old Red Hat 7.1 box (hosted dedicated server) that had been in service since 2002, so about 7 years. Specs? Celeron 1.3GHz, 512M, 60GB HD. Not too bad in the RAM and disk realm. It did a good job but goodness am I glad to be rid of it!

Not having that box online is safer for the planet, although it (perhaps amazingly considering the age of some of the externally facing software components) has never been compromised – I consider that mostly luck, by the way, I’m not naive about that. But it’s not easy to move off old servers, it’s generally (and also has been in this case) a lot of work.

Of course hosting has moved on since 2002, places like Linode offer more for less money/month. Of course they virtualise (Xen based in this case) and that’s not been my favourite (particularly for DB servers but depending on the use it really comes down to how you set up the whole infra). It’s a different environment, so different “rules” apply for the optimal setup. The feature/pricing model of the hosting(/cloud) provider actually has more than a little bit to do with that. Distributing tasks like MX relaying, DNS, moderate MySQL tasks, web server, across different virtual machines, with added redundancy across different data-centers, works very well for many use cases. And the funniest thing… more servers, with distributed redundancy, the net cost per month is actually lower than that one single server!

There a many aspects to consider, and I’m intending to write more about that in future posts. I just found it an interesting experience, dealing with this (personal, not even business) server. We handle with these technical environments all the time in our work, but it’s not quite the same perspective. It’s not all technical/financial issues, there’ more to it.