24 Nov

Using VLookup like Batch script to compare two excel / csv

Using Vlookup:

I have two csv files; File1 has Id and Value Columns and File2 has Id.

Problem: I need to compare both files and put respective values to File2 from File1.

Solution: VLookup in excel

Steps:

  • Open both files in Excel & arrange it vertically (Window >> Arrange; for ease)

vlookup-1

  • Click on cell where you want to put compared value: B2
  • Click on fx button and select VLOOKUP function.

vlookup-2

  • Under lookup_value, click first search field: A2.
  • Click back in Table_array text field, and click on button to select range. Select two column for comparison starting from second row, i.e. A2 to B9.
  • Put 2 in Col_index_num. If lookup succeeds in finding lookup_value in Table_array it will return 2nd value as result.
  • Put FALSE in Range_lookup to do exact match.
  • Click ok, and you will find B2 will be filled with respective value.
  • You may drag the command to other rows and values will get filled. Optionally you may copy and paste it from rows B3 to B9 to lookup and fill all values.

vlookup-3

  • You can observer in output here, if I change the value of ID in File2 which doesn’t exist, VLookup returns #N/A.

Descriptions in details for this function and each respected field are easily available in excel help.

Download Files: File1, File2

Batch script to compare and assign value-simulate vlookup:

Further I came up with a batch script to compare field value, simulate vlookup. After doinglittle testing, I managed to fix it.

for /f “tokens=1 delims=, skip=1″ %%i in (File2.csv) do @findstr  “%%i,” File1.csv >nul & If errorlevel 0 if not errorlevel 1 (for /f “tokens=1,2 delims=,” %%m in (‘findstr /i /L “%%i,” File1.csv’) do (@echo %%m,%%n>>output.csv echo %%i)) else (echo %%i,NA>>output.csv)

Please consider this batch script is written specifically for these csv and prove my understanding.
This script will compare file2 with file1, extract similar data from file1 and put it in output.csv.

Download batch file: vlookup-batch.bat

If you find any difficulty or don’t yield required results by following above process or using script, comment.

18 Nov

bat – batch file to create formatted date time (ddmmyyyy) directory

I required to make a batch script which needs file / directory to be created with current date / time stamps.
Following are the code snipts for formating date / time in a batch script:

FOR /F “TOKENS=1* DELIMS= ” %%A IN (‘DATE/T’) DO SET MYDATE=%%B
FOR /F “TOKENS=1,2 eol=/ DELIMS=/ ” %%A IN (‘DATE/T’) DO SET mm=%%B
FOR /F “TOKENS=1,2 DELIMS=/ eol=/” %%A IN (‘echo %MYDATE%’) DO SET dd=%%B
FOR /F “TOKENS=2,3 DELIMS=/” %%A IN (‘echo %MYDATE%’) DO SET yyyy=%%B
set DATED=%mm%%dd%%yyyy%
md %DATED: =%

or

FOR /F “tokens=*” %%A IN (‘DATE/T’) DO SET MYDATE=%%A
#Using regular expresions to remove / and DAY
SET MYDATE=%MYDATE:/=%
SET MYDATE=%MYDATE:* =%
md %MYDATE%

Other ways to achieve date string in a batch script variable:

@echo off
set yy=%date:~-4%
set mm=%date:~-7,2%
set dd=%date:~-10,2%
set MYDATE=%yy%%mm%%dd%

or

for /f “tokens=2-4 delims=/ ” %%g in (‘date /t’) do (
set mm=%%h
set dd=%%g
set yy=%%i
)
set MYDATE=%yy%%mm%%dd%

Similarly we can work with time:

for /f “tokens=1-2 delims=: ” %%j in (‘time /t’) do (
set hh=%%j
set mn=%%k
)
set MYTIME=%hh%%mn%

Further you can go ahead mixing time with date as well.

16 Nov

Calculate Mysql Memory Usage – Quick Stored Procedure

In this post we will look into the MySQL memory utilization estimation or calculation based on the global variables. Using a simple stored procedure call you can get the memory usage estimation for the present MySQL instance.

We have global buffers which are allocated irrespective of connections as and when mysql server is started. Along with that mysql server allocates memory to each thread to perform respective tasks.

So the formula goes:

Mysql Server Memory Usage = Sum of Global Buffers + (number of Connection * Per thread memory variables).
Continue Reading…

12 Nov

Scheduled Backup MySQL Administrator & Windows Scheduler – Odd

Creating scheduled backup using administrator is an easy task.

Follow the GUI and you’re done. But I observed something different!

MySQL Version: 5.0.83 Community

MySQL Administrator: 1.2.17

Machine: Intel P4, Windows XP, Enough RAM :)

Logs: General, Error & irrelevant here though Slow query.

Prologue: Administrator performs scheduled backup properly in normal scenario.

What I was watching: Administrator’s Auto-Backup behaviour with windows Scheduler service and tracing steps out.

Some facts:

MySQL Administrator stores backup project’s details in <PROJECT-NAME>.mbd files in xml format under directory

C:\Documents and Settings\<USER-NAME>\Application Data\MySQL

Along with it it also adds a scheduled task with name <PROJECT-NAME>.

“C:\Program Files\MySQL\MySQL Tools for 5.0\MySQLAdministrator.exe” “-UDC:\Documents and Settings\<USER-NAME>\Application Data\MySQL\” “-cbkupconn” “-bp1″ “-btC:\” “-bx1″

If you find trouble getting automated backup work, make sure your Scheduler [Task Scheduler] service is running.

Start >> Run >> services.msc

MySQL Administrator does not log these events unless specified explicitly.

To enable logging:

Menu: Tools >> Options
Category: Administrator
In Backup Section:
  • Tick checkbox “Write log file for scheduled backup” and spcify path.
  • Tick checkbox “Log Row Processing Progress” and specify number.

To view Scheduled task’s log you may:

Start  >>  program  >>  accessasory  >>  System Tools  >>  Scheduled Tasks
Click on menu: Advance >> View Logs
Or
Open Windows/SchedLgU.Txt

My observation:

Whenever I keep task scheduler Enabled & Started, I could see updates in logs of Scheduled task and also in Event Viewer. For backups you get logged in General Query Logs.

But with my surprise I’m not able to get the MySQL Administrator’s log file (with default setting).

Also if scheduler is Disabled & Stopped, Nothing is getting logged. Neigher in logs of Scheduled task nor in Event Viewer.

And again there is no MySQL Administrator’s log file getting generated. Also this even is not getting logged in to Error Log!

Later I read the bug: http://bugs.mysql.com/bug.php?id=28821

I tried to change the path from default, C:\ to D:\SOMEDIRECTORY. And with my surprise MySQL Administrator logged successfully executed scheduled tasks to log.

Next observation after disabling Scheduler(Task Scheduler) Windows Service I made was, backup had failed but Administrator haven’t logged it into C:\MySQLAdminBackupLog.txt !!

Even now I have “Log file path” as D:\ and I’m reading log updates in C:\MySQLAdminBackupLog.txt. There is nothing in D:\!

Is it the problem with way Administrator writes to .mdb file (bug?) or am I doing something wrong!

11 Nov

Monitor multiple mysql replication using php: Updated

Monitoring a replication is an important aspect. As replication includes multiple nodes, it is essential to track activity and status across all mysql servers involved in replication.

To monitor replication we know commands like:

Show slave status;

Show master status;

Refer: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-replication.html

But when it comes to non-gui interface, it becomes little tedious.

With some efforts, I managed to make a php script to monitor mysql servers.

It can:

  • Get per second status of slaves.
  • Start / Stop slave from any of the mysql servers in replication.
  • Display error number along with description in case of error in replication.
  • Shows detailed information of both the server’s global status variables.

Well there is no fancy UI, my bad, but it works for me. I’ll try to make it better, may be you can suggest.

Code:

[ Download here: monitor-replication.txt] ( ** Check Updated Monitoring Script. )

<?php

#Resets warning messages

error_reporting(E_ALL ^ E_NOTICE);

#Function to display slave status.

function slavestatus($host,$username,$password,$slaveport=3306)

{

$con=mysql_connect(“$host:$slaveport”,$username,$password)

or die(“can’t connect server”);

$result=mysql_query(“show slave status”);

$timeResult=mysql_query(“select now()”);

$time=mysql_result($timeResult,0,0);

while($status = mysql_fetch_array($result))

{

$file=$status[5];

$position=$status[6];

$sql_run=$status[10];

$io_run=$status[11];

$errorNum=$status[18];

$errorMeg=$status[19];

}

print “<tr align=center>

<td> $time </td>

<td> $host : $slaveport </td>

<td> $file </td>

<td> $position </td>

<td> $sql_run </td>

<td> $io_run </td>

<td> $errorNum </td>

<td> $errorMeg </td>

<td> <form name=’form’ action=’#’ method=’POST’>

<input type=’submit’ name=’stop’  id=’stop’  value=\”STOP $host:$slaveport\”>

<input type=’submit’ name=’start’  id=’start’  value=\”START $host:$slaveport\”>

</form> </td>

<td> <a href=’?detail=$host:$slaveport’> detailed </a></td>

</tr>”;

}

#Function for starting and stopping mysql server.

function start_stop($machine,$username,$password,$task=’no’)

{

if($task==’no’) exit;

$con=mysql_connect($machine,$username,$password) or die(“can’t connect server”);

$sql= $task . ” slave”;

$result=mysql_query($sql);

$_POST = array();

$task=”no”;

$VAR=$_SERVER[“REQUEST_URI”];

#Refreshes page, updates server status in table.

header(‘refresh: 1; url=’.$VAR);

}

#Function for showing detailed status

function get_status($machine,$username,$password)

{

$hostname = $_SERVER[‘SCRIPT_NAME’];

echo “<center><a href=\”$hostname\”>Reset</a></center>”;

if($task==’no’) exit;

$con=mysql_connect($machine,$username,$password) or die(“can’t connect server”);

$sql=”show global status”;

echo “<table border=1 align=center><tr><th>Variable</th><th>Value</th></tr>”;

$res=mysql_query($sql);

while($row = mysql_fetch_assoc($res))

{

echo ‘<tr align=center><td>’.$row[‘Variable_name’].

‘</td><td>’.$row[‘Value’].'</td></tr>';

}

echo “</table>”;

#$_POST = array();

$task=”no”;

}

echo ‘<html>';

if(!isset($_GET[‘detail’]))

{

#Auto refreshing page for continuous status

#content=”3;” page refreshes after 3 seconds

echo ‘<meta http-equiv=”refresh” content=”3;”>';

}

echo'<body>

<h1><font face=”verdana” size=10><small>

MySQL Replication Monitor</small></font></h1>';

#host-name & ports for replication servers

$slave = “localhost”;

$master = “localhost”;

$slaveport=3306;

$masterport=3307;

#considered a common username password for accessing both servers

$username=”root”;

$password=”kedar”;

print “<table border=1><tr>

<th> time </th>

<th> host : port </th>

<th> file </th>

<th> position </th>

<th> io run </th>

<th> sql run </th>

<th> errorNum </th>

<th> errorMeg </th>

<th> Stop / Start </th>

<th> Extra </th>

</tr>”;

slavestatus($slave,$username,$password,$slaveport);

slavestatus($master,$username,$password,$masterport);

print “</table>”;

if (isset($_POST[‘start’])) {

list($task,$machine)=split(” “,$_POST[‘start’]);

start_stop($machine,$username,$password,$task);

}

if (isset($_POST[‘stop’])) {

list($task,$machine)=split(” “,$_POST[‘stop’]);

start_stop($machine,$username,$password,$task);

}

if (isset($_GET[‘detail’])) {

echo “<br><center><b>Showing global status for: “.

$_GET[‘detail’] . “</b></center><br>”;

get_status($_GET[‘detail’],$username,$password);

}

echo ‘<div align=right><font face=”verdana” color=”gray” size=”2px”>

Kedar.</div></div></body>

</html>';

?>

Update: Above script with little changes for monitoring more mysql servers using php script with easiest possible configuration.
Download: MySQL-replication-monitoring-PHP-Script.  ( ** Check Updated Monitoring Script. )

The script give reads server configurations from a config file ‘replmon_conf.txt’ and thus facilitate to monitor simultaneously any number of mysql database servers.

You have to specify connection parameters in replmon_conf.txt as follows:
hostname,username,password,port

To monitor more than one mysql database servers you just need to add one more line of connection parameters.
Hope this helps.

Update 2:  Tookout some time and modified the script for monitoring mysql replication using php.

Download mysql monitor using php: mysql replication monitor php

-- Kedar Vaijanapurkar --