Tag Archives: MySQL

Monitoring MySQL with Zabbix using the appaloosa-zabbix-templates

The standard MySQL Templates for Zabbix are a little basic and I admit I never really got them to work properly. Thankfully, the Appaloosa Zabbix Templates are available that are based on the excellent Percona Monitoring Plugins.

As always, this is how I got this to work. If you have any suggestions or improvements, get in touch.

Initial Work

On Ubuntu, some additional Perl modules are required:

$ apt-get install libxml-simple-perl libdatetime-perl

You also need to create some directories

$ mkdir /usr/local/zabbix
$ mkdir /usr/local/zabbix/plugins
$ mkdir /usr/local/zabbix/agent.d

Generating the Template

Download the source from Google Project Hosting

$ wget http://appaloosa-zabbix-templates.googlecode.com/files/appaloosa-zabbix-templates-0.0.1.tgz

Extract the source by running

$ tar -zxvf appaloosa-zabbix-templates-0.0.1.tgz

Open the extracted folder and generate the template XML

$ cd appaloosa-zabbix-templates-0.0.1
$ perl tools/gen_template.pl defs/mysql.pl mysql.xml

Copy the generated file, mysql.xml, to the /usr/local/zabbix/plugins directory

$ cp mysql.xml /usr/local/zabbix/plugins

Creating the Config File

Now create the agent’s config file by editing conf/mysql_agentd.conf using the sed command.

$ sed -e 's|$ZABBIX_AGENT_PATH|/usr/local/zabbix/plugins|' conf/mysql_agentd.conf > mysql.conf

Copy the new config file to the /usr/local/zabbix/config.d directory

$ cp mysql.conf /usr/local/zabbix/config.d

Open the Zabbix Agent configuration file

$ nano /usr/local/etc/zabbix_agentd.conf

Add the following line

Include=/usr/local/zabbix/agent.d

Restart the Zabbix Agent

$ service zabbix-agent restart

Creating a MySQL User

Connect to mysql using the command: (you will be prompted for the root’s password)

$ mysql --u root -p mysql

Create the new user

mysql> CREATE USER 'zabbixmonitor'@'localhost' IDENTIFIED BY 'password';

Grant privileges to the user

mysql> GRANT SELECT, SUPER, PROCESS ON *.* TO 'zabbixmonitor'@'localhost';

Reload all privileges:

mysql> FLUSH PRIVILEGES;

Exit mysql

mysql> quit

Alternatively, you can use a UI tool like Webmin if it is installed on your server.

Configuring the PHP Script

The template requires a PHP file from the original Cacti template. Download and extract the latest version of the Cacti Templates using

$ wget http://mysql-cacti-templates.googlecode.com/files/better-cacti-templates-1.1.8.tar.gz
$ tar -zxvf better-cacti-templates-1.1.8.tar.gz

Copy the file scripts/ss_get_mysql_stats.php to /usr/local/zabbix/plugins

$ cp better-cacti-templates-1.1.8/scripts/ss_get_mysql_stats.php /usr/local/zabbix/plugins

Open ss_get_mysql_stats.php for editing

$ nano /usr/local/zabbix/plugins/ss_get_mysql_stats.php

Change the values of $mysql_user and $mysql_pass to that of the mysql user created above

$mysql_user = 'zabbixmonitor';
$mysql_pass = '12345678';

Save and close the file

Import the Template

If you generated the mysql.xml on the Zabbix server, you need to download it to your client computer.

Once you have the file, log into Zabbix and go to Configuration > Templates

Click on the Import button in the top right

Click on Choose File to select mysql.xml and then click on Import

If successful, you should see the message Template Imported

Adding the template to a host

In Zabbix, go to Configuration > Hosts

Click on the host to add the template

Select the Templates tab

Click on Add and select template_mysql from the list

Click on Select and then Save

Troubleshooting

If you don’t get any data, check the Zabbix agent’s log. The logfile’s path is defined in the agent’s configuration file, but the default are:

Linux: /tmp/zabbix_agent.log
Windows: c:\zabbix\zabbix_agentd.log

Don’t forget, if you make any changes to the configuration file, you will need to restart the agent for them to take effect.

References

Appaloosa Installation Notes: http://code.google.com/p/appaloosa-zabbix-templates/wiki/Installation

Buxxnt: http://buzznt.blogspot.co.uk/2011/09/install-zabbix-mysql-plugin-imported.html

WebsitePanel and MySQL 5.5 Configuration Error. Again.

2 Replies

A couple of months ago I wrote about an error we encountered with WebsitePanel and MySQL 5.5. Essentially, this boiled down to WebsitePanel only recognising specific versions of the MySQL .Net Connector. The solution was actually very simple: tweak some entries in the web.config files.

Since then, we’ve upgraded WebsitePanel from 1.2.1 to version 2 (highly recommended) without any problems. A couple of weeks ago, we upgraded the MySQL .Net connector for a new project and promptly got the following error message:

System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---&gt; System.IO.FileNotFoundException: Could not load file or assembly 'MySql.Data, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The system cannot find the file specified.

As before, this problem is very easy to fix. Simply change the following entries in the web.config in both WebsitePanel’s server and portal components (the default paths are c:\websitepanel\server andc:\websitepanel\portal respectively). If these entries don’t exist, create them.

<runtime> 
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> 
        <dependentAssembly> 
            <assemblyIdentity name="MySql.Data" publicKeyToken="c5687fc88969c44d" /> 
            <bindingRedirect oldVersion="0.0.0.0-6.3.7.0" newVersion="6.5.4.0" /> 
        </dependentAssembly> 
    </assemblyBinding> 
</runtime>

<runtime> 
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> 
        <dependentAssembly> 
            <assemblyIdentity name="MySql.Data" publicKeyToken="c5687fc88969c44d" /> 
            <bindingRedirect oldVersion="0.0.0.0-6.5.4.0" newVersion="6.6.4.0" />
        </dependentAssembly> 
    </assemblyBinding> 
</runtime>

The only line that changes begins with bindingRedirect and you need to change the value of newVersion to that of the version of the MySQL .Net Connector you have installed. In this case, 6.6.4.0.

The mistake here – and it was my mistake – was assuming that a known issue with WebsitePanel had been addressed in the latest version. Needless to say, there are now specific notes regarding upgrading the MySQL .Net Connector.

On a side note: I’ve been developing .Net websites using MySQL for well over 8 years now. I’ve very rarely run into this problem and it nearly has always occurred when I have deployed a website that utilises a much newer version of the connector than is installed on the server. One thing that I never done is to explicitly define a version of the .Net Connector. Whilst this may be considered not to be best practice in some quarters, it does go some where to accommodating differing installed versions of the connector, especially on shared hosting providers (i.e. servers outside of my direct control).

WebsitePanel and MySQL 5.5 Configuration Error

MySQL Powershell Backup Script

2 Replies

This is easily one of my most commonly used Powershell scripts, and a variation on it runs on my servers at least once a day. It is essentially a very simple script: it contacts a specified MySQL server and then generates a .SQL backup files for each database found. It prefixes each backup filename with the day name (monday, tuesday etc.) so if you run it on daily basis, it gives you a seven day backup rotation for your databases.

As always, the use of this script and the accompanying files is done entirely at your own risk. No guarantee either direct or indirect is implied regarding the security, stability, reliability, impact or performance of this script.

The zip file includes copies of mysqldump.exe and mysql.data.dll that you may require. For details on how to configure and use the script, please see below.

Download the script (ZIP file)

Configuration

There are a few items you will need to configure before you can use the script in anger. All of the configuration options are presented at the top of the script, so you don’t need to delve into the main chunk of code.

Backup Store Folder

This is where the MySQL backup files will be placed. Please enter the path here, but do not include any trailing ‘\’

MySQL User Account

You will require a MySQL user account that has SHOW DATABASES, SELECT, LOCK TABLES and RELOAD permissions. It is also highly advised – especially if performing backups from a remote machine – that this user’s permissions are also limit to readonly.

mysqldump Path

mysqldump is the application that actually creates the backup file. It is typically installed with MySQL, but if you are running the script remotely or don’t know the file’s location, mysqldump.exe is included in the ZIP file.

MySQL .Net Connector

The script requires the MySQL .Net Connector driver. This can either be installed, or you can directly load the DLL file.

Using the connector

Download and install the connector from http://dev.mysql.com/downloads/connector/net/
Uncomment Line 38 of the script. This should say system.reflection.assembly]::LoadWithPartialName(“MySql.Data”)

Loading the library directly (the default method)

If you don’t wish to install the .Net driver, you can directly load the DLL file instead. The required DLL file is included as part of the ZIP file.

Uncomment line 43 of the script. This should say Add-Type -Path “C:\Data\Backups\MySQL\MySql.Data.dll”
Amend the path of line 43 to point to the location of the DLL file

Running the script

You can either run the script manually (through Powershell), or automate it through Task Scheduler. To make life a little easier I tend to create a batch file containing the Powershell command and then invoke the batch file through the task scheduler.

The general format of the command to include in the batch file is

%windir%\System32\WindowsPowerShell\v1.0\powershell.exe “c:\backups\Generic7DayBackup.ps1”

Lastly…

This is by design a very simple script. If you have any suggestions on how to improve it, please let me know by posting a comment.

Alexander John

Thoughts, notes & the occasional rant