Alexander John

Thoughts, notes & the occasional opinion.

Page 2 of 6

Monitoring MySQL with Zabbix using the appaloosa-zabbix-templates

1 February 2013 / / 4 Comments

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.

30 January 2013 / / 2 Comments

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

To

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

The return of LeafURL

30 January 2013 / / 0 Comments

Nearly a year ago I wrote a URL Shortening service called LeafURL. This was part intellectual exercise and part fun – part of me just wanted to build something other than a “standard” website. As with most spur of the moment ideas, it took longer than expected to complete. I did include some extra bells and whistles including graphical reports using the Microsoft Chart Controls.

A few weeks ago, I decided to give LeafURL an annual service and to improve its’ overall integration with the Calzada Media website. A few scratchings of the head later punctuated with quite a few what on earth did I do here? and version 2 of LeafURL has appeared. With version 2, LeafURL is now powered by the UrlShortener module for Camino CMS, Calzada Media’s own content management system. Outwardly, there are few visual changes, but by using the latest version of Camino, LeafURL is now more stable and robust than before.

The newly refurbished LeafURL is now available and is free to use by anyone.

WebsitePanel and MySQL 5.5 Configuration Error

20 September 2012 / / 0 Comments

If you try and configure the MySQL 5 service in WebsitePanel 1.2.1 you may get 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. ---> System.IO.FileNotFoundException: Could not load file or assembly 'MySql.Data, Version=6.3.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The system cannot find the file specified.

This error is down to WebsitePanel looking for a specific version (6.3.7.0) of the MySQL .Net Connector. In all likelihood, you will have installed either a later version (at the time of writing it is 6.5.4).

Thankfully, this is error is easy to fix and only requires modifying a couple of configuration files. Add the following code snippet at the end of the web.config files before the closing </configuration> tag for both WebsitePanel’s server and portal components (the default paths are c:\websitepanel\server and c:\websitepanel\portal respectively):

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

If you are using a version of the .Net Connector later than 6.5.4.0, then do amend the bindingRedirect newVersion property.

Detecting mobile browser in ASP.Net

18 September 2012 / / 0 Comments

Detecting mobile browsers can be a bit of pain. Whilst it is relatively easy to get browser information through the User Agent string, it is not such a simple task to identify the client device type. There is no defined property within the user agent field that says I’m a mobile device. User Agent values differ widely between devices and browsers, so it is necessary to use some less than elegant detection code.

I recently added this detection functionality to the Camino CMS Framework, and Calzada’s preferred method is using regular expressions. After some playing, we came up with the following regex:

(?i)(android(?:.+mobile)|applewebkit.+(?:\(KHTML, like Gecko){1}|avantgo|blackberry|blazer|docomo|elaine|fennec|htc|kindle|lge|linux.+armv|mmp|mms(?:.+midp){1}|mobile|mot(?:.+midp){1}|nintendo.+(?:3ds|wii)|nokia|nook|opera(?:.+mini|.+mobi){1}|palm|polaris|phone|ppc|psp|samsung|sec|sony|symbian|tablet|up\.browser|vodafone|wap|webos|windows(?:.+ce|.+mobile){1}|xda|xiino

A simplified ASP.Net detection code would look something like this:

Function IsMobileBrowser() As Boolean
Dim regex As New Text.RegularExpressions.Regex("(?i)(android(?:.+mobile)|applewebkit.+(?:\(KHTML, like Gecko){1}|" & _
"avantgo|blackberry|blazer|docomo|elaine|fennec|htc|kindle|lge|linux.+armv|mmp|mms(?:.+midp){1}|mobile|mot(?:.+midp){1}|" & _
"nintendo.+(?:3ds|wii)|nokia|nook|opera(?:.+mini|.+mobi){1}|palm|polaris|phone|ppc|psp|samsung|sec|sony|symbian|tablet|" & _
"up\.browser|vodafone|wap|webos|windows(?:.+ce|.+mobile){1}|xda|xiino", Text.RegularExpressions.RegexOptions.IgnoreCase)
Return regex.IsMatch(HttpContext.Current.Request.ServerVariables("http_user_agent").ToString)
End Function

There are a couple of key points to bear in mind with this method:

  1. It is not perfect. No matter how you tailor and tweak the regular expression, you are inevitably going to get some false positives and false negatives.
  2. This is not a write and forget function. As the number of mobile devices and manufacturers proliferate, the accuracy of the regular expression will diminish. To compensate, it is necessary to regularly review, test and update where required. To ensure that this is done, this process has been added this to the Camino development lifecycle.
  3. Not all mobiles or mobile users may wish to use mobile variant of a website. Do bear in mind that some tablets have a sufficiently large screen to view the normal version of a website.
I am aware that this is not an elegant nor wholly accurate solution, and there are plenty of programmers out there that will view it as a kludge, but in many ways it is a product of its’ ingredients. Until such time when all browsers will provide a property that simply states I’m running on a mobile device, then this will have to do.

Starting off with WebsitePanel

19 July 2012 / / 0 Comments

There are a whole gamut of hosting control panels available. I have used quite a few of them, and to be fair, none have really satisfied the criteria of being easy to use, powerful and responsive. The only one that I have vaguely liked to date is Helm 3. The less that said about Helm 4, the better.

Now, I will accept that I am being a little unfair with the last criteria. A substantial proportion of a control panel’s response time is taken up by calls on other services, often based on separate hardware. When you take this into consideration, Hosting Control Panels (HCPs) do succeed in corralling together and presenting management capabilities for multiple services (often from differing providers) in a single interface. What very few have done is present an interface that is easy to use both from an administrative and end-user perspective.

And this is the critical point. HCPs are primarily for use by end-users, not administrators, but this is something that is still regrettably not addressed by developers. End-users often have a poor level of technical literacy, and very few are prepared to wade through tiered menu levels following obscure technobabble. They just want to create an e-mail account. Why can’t they just log in and get presented with an Add E-mail Account link?

At Calzada, we are determined to make life simpler for our hosting customers, and admittedly, ourselves. A good HCP will not only make life easier for our customers, but potentially reduce the number of control panel related support queries. Therefore, the choice of hosting control panel was critical. I also have to admit that price is also a controlling factor. We are a small company, and cannot realistically justify some of the prices being charged for the likes of Plesk and cPanel/WHM.

Unlike in *nix world, open source Windows HCPs are a rarity. The only real choices are zPanel or WebsitePanel. After some testing, we opted for the latter as WebsitePanel supported a wider variety of applications than zPanel, and did appear to be more mature.

WebsitePanel is not perfect and there is undoubtedly room for improvement in some areas. We have experienced a number of glitches that have required workarounds – more posts to come on these – but the overall experience has been fairly good. The installation program worked like a dream and was noticeably quicker than some of its’ commercial competitors. I have to admit to warming to WebsitePanel; it does what it is expected to do and it is fairly easy to use for both administrative and end-user purposes.

 

 

 

 

 

IIS FastCGI Fatal Error: Allocated memory size exhausted

19 July 2012 / / 0 Comments

Traditionally IIS and PHP have not mixed particularly well. Things have gotten a lot better since the arrival of FastCGI and the Web Platform Installer, but there are still occasions when something occurs which flummoxes an ASP.Net guy like myself.

I recently moved this blog onto a Windows 2008 R2 server running IIS 7.5. I’ll admit that I wasn’t really expecting any problems as I had already moved a MyBB forum onto the same server without any problems. My normal level of IT paranoia had taken a leave of absence as I made the assumption that MyBB would be a far more demanding PHP application than WordPress, so there shouldn’t be any issues.

You know what they say about assumptions? After all the data and files had been migrated I fired up my browser and initially got a blank page in return. By default, FastCGI PHP does not return error messages – it is after all running on a production server. So I dropped the following code into the top of the wp-config.php to manually enable errors.

ini_set('display_errors',true);

This duly returned the following error message:

Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 261904 bytes) in C:\inetpub\HostingSpaces\alexanderjohn\alexanderjohn.co.uk\wwwroot\wp-includes\functions.php on line 2109

Now, any quick Google search on this will immediately return a series of results all recommending the solution being to increase the memory_limit value in the php.ini from 128MB as the likely source is probably memory leaks in PHP. This did seem a little counter intuitive – does WordPress really need that much memory? I duly increase it several times, and each time I got a variation on the above error – all that changed was the Allowed memory size value increased to match the memory_limit value.

My next step was to check the various application pool settings. They were all fine, and the site still refused to work with a new, dedicated pool.

Realistically, this only left WordPress as the culprit. I duly downloaded the latest version and performed a manual upgrade. Still no luck.

I then stripped out all the additional plugins and themes that were installed, and Hey Presto!, there was life.

I don’t know which plugin caused the problem as I have taken this opportunity to re-evaluate every plugin I was using. This is something that I hadn’t done in a while, and to be honest, was well overdue.

As for the root core cause. I would suspect that this down to some form of infinite loop. If it is so, I am amazed that some developers are still adding iterations without some form of safety net. At Calzada, it is actually part of our coding practice that any loop that has the potential of going infinite has some form of safety net. At the end of the day, it is very hard to look professional to a customer when a product fails because of a very simple coding error.

MySQL Powershell Backup Script

29 May 2012 / / 2 Comments

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

  1. Download and install the connector from http://dev.mysql.com/downloads/connector/net/
  2. 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.
  1. Uncomment line 43 of the script. This should say Add-Type -Path “C:\Data\Backups\MySQL\MySql.Data.dll”
  2. 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.

 

Setting up a forum – not as easy as you might think

24 April 2012 / / 1 Comment

Several of years ago, when working for a now defunct company, a customer wanted to add a forum to his website. That’s easy the boss said, and a colleague duly installed one of the many free to use forums. As we weren’t managing the forum beyond the installation of the software, we paid little or no heed to the initial configuration of the forum. As a casual forum visitor, I guess I assumed that starting up a forum would just as easy as using it.

I was wrong. In the last couple of weeks, Calzada Media has been working towards the launch of a new online forum for the local cricket community called LegStump. LegStump is all about building online cricket communities between the grass roots supporters and players: the local village and community teams who play cricket for simple enjoyment.

It would have been easy to upload any forum software and then configure it on the hoof. Such an approach doesn’t mean you’re doing anything especially wrong but you are lining yourself for some potentially big problems in the future.

So, we took the other road and took our time applying the normal Calzada project management processes. Only when we were happy with a version of the forum running on our development servers, did we even consider moving it into the live environment.

With our experience fresh in the memory, here are our recommendations for those, like us, starting our with their first forum:

Choose your forum software wisely

This will be your biggest and most important choice. Once you have deployed a forum, migrating to a different software package is a challenging prospect, not only for you, but also for your members.

Based on our experience, we advise:

  • Be aware of your limitations and knowledge – For example, don’t use a Perl based forum if you have never used Perl.
  • Taken into consideration the hosting platform upon which you will run your forum. If you already have web hosting, then bear in mind your package’s functionality and capabilities. If you don’t have hosting, you do need to consider potential hosting costs.
  • Look for a consistent and high quality user experience for all grades of members.
  • Can the forum software be extended? Are there a good range of modifications, plugins and themes available?
  • How easy and capable are the forum’s management capabilities?

Whatever software you choose, be sure to evaluate the software prior to deployment. With LegStump, there were a number of forums we liked but it was through testing and evaluation we narrowed down our choice to MyBB.

Prepare and publish your rules, guidelines and legal notices

This is an area that often overlooked. Without stated rules and guidelines, you run the very real risk of losing control of the discussions and content on your forum. You should not only clearly define your forum’s rules and guidelines, but also place them at a location where everyone including non-members can read them.

At LegStump, we currently operate under four principal legal documents:

It is also worth noting that these rules and guidelines apply to all users irrespective of their roles or positions within the forum.

Design

The wrong design or theme will drive members away as quickly as the improper choice of forum software. You chosen design should:

  • Reflect the tone of your forum’s content
  • Be easy on the eye – no big clashes of colour
  • Not unduly affect the performance of the software

Enhance the capabilities of your forum with plugins

Do check out the range of modifications or plugins that are available for your chosen forum software. You may be surprised just what are available and how they will enhance your forum.

With LegStump, we have utilised nearly 10 mods to improve both the user experience and also management capabilities.

 

Happy New Year

2 January 2012 / / 0 Comments

A day late, but Happy New Year and best wishes for the year ahead.

2011 was a year of great change across the globe and anxiety for many (especially for us folks in Europe!). Some commentators have suggested that it is a year to forget. I disagree. In many ways we should be thankful that 2011 is over, but we should not forget the events that defined it.

The Tsunami in Japan whose effect will be felt for future generations. The Arab Spring where successive totalitarian governments were toppled like Dominoes. The killing of Osama Bin Laden. The continuing Euro crisis that will almost certainly never be resolved fully – I smell a Heath Robinson half-baked euro-bodge-solution in the coming months. These are just some of the events that have profoundly reshaped the world we live in. I suspect that in the future, 2011 will be viewed as the turning point where the world truly started to become less West-centric. The economic emergence of the BRIC nations at the time of the decline of the European block has all but ensured that this scenario will come true.

Every year has defining events. 2011 stands out due to the sheer and almost incessant number of events, spread out across the world affecting a significant proportion of the global population.

Here’s to hoping that 2012 will be a great year.

« Older posts Newer posts »

© 2025 Alexander John

Theme by Anders Norén — Up ↑