Backing Up the System Reporter Database

As I posted recently I had an issue with the corruption of one of the tables in MySQL, which caused System Reporter to go offline. To help to avoid a repeat of the 5 minutes I spent rolling around the floor muttering that all the data was lost, I have been looking into an automated way to backup the MySQL database used by System Reporter. I asked HP and they advised they don’t have any guidelines on backing up the System Reporter database or a preferred tool, so I jumped onto Google and had a look around at the various options.


The solution I have selected is a script published by By Matthew Moeller at Red Olive Design. The script is essentially a batch file which can easily be added as a scheduled task in Windows, which effectively means the backups can be automated. The other thing which made this particular script stand out is that it compresses the completed backup in a Zip file, in my case this resulted in the Zip being 4 times smaller than the actual DB. Another neat feature is that you can set how many days of backups you want to keep and it will automatically delete backups older than the specified date for you.


I have now had several successful runs with the script but as with any script use it at your own risk and make sure it is suitable for your environment. You can find the script which is free, and all the installation instructions here.

System Reporter Error

Recently I had some problems with System Reporter, the symptoms were that when I went to run a report it showed no data available for the period. When I checked the System Reporter server I saw that the 3Par system reporter sampler service had stopped. To see what the issue was I checked in the Sampleloop log located in the following location C:Program Files (x86)3PARSystem, towards the end of the log I saw the following error:

2014-07-21 08:51:33: MySQL DB error executing show full columns from statldrg_hourly_2: mysqlsel/db server: Table ‘.inservstatsstatldrg_hourly_2’ is marked as crashed and should be repaired

In order to repair the MySQL tables I took the following steps:


1.   Stop in this order the following services: System Reporter service, Apache service and MySQL Server service from services.msc

2.  Open the Task Manager and select show process from all users. If the following processes exist end them: inserv_perf.exe, Trimdb.exe, Compactdb.exe, inservsample.exe

3.     Before you start make a backup of the current database. The default location for the DB is C:ProgramDataMySQLMySQL Server 5.5datainservstats. You can back this up by just making a copy of the folder

4.     Open command prompt as an administrator and change directory to where MySQL database is located e.g. C:ProgramDataMySQLMySQL Server 5.5datainservstats

5.     The command below will repair and re index the tables just copy and paste it into your command prompt. You will need to replace the X with the version of MySql you are using eg 5.5

“C:Program FilesMySQLMySQL Server 5.Xbinmyisamchk.exe” -r –key_buffer_size=512M –sort_buffer_size=512M –read_buffer_size=8M –write_buffer_size=8M *.MYI

Wait for this command to complete, it can take some time, you will know it’s done when it goes back to the command prompt.

5.     Start the MySQL Server service, System Reporter service and Apache service.

In my case it took a good hour to run and then System Reporter was back from the dead, good times

Follow 3ParDude on Twitter here