29 Mar

MySQL related file types and basic information

This post covers the basic information of files that MySQL Server uses / creates for various tasks.

my.cnf :

It is the main configuration file for MySQL. You may find it under base directory in windows or under /etc/.

.sock :

mysqld creates a socket for programs to connect to and notes in this file. It is named as mysql.sock.

Pid file :

MySQL server write its process ID in this file. You may specify it by –pid-file=file_name otherwise it defaults to hostname of the server.

.frm :

MySQL represents each table by an .frm table format file, stores table definition in the database directory. It is stored as
.frm under data directory.

.MYD :

This is the extension of the Data files for MyISAM tables. It is stored as
.MYD under data directory.

.MYI :

This is the extension of the Index files for MyISAM tables. It is stored as
.MYI under data directory.

[ad#ad-2-300×250]

.ibd :

If you specify innodb_file_per_table option to my.cnf, InnoDB stores each table in its own .ibd file in the same MySQL database directory where the .frm file is created. For InnoDB tables, the data and the indexes are stored together in the .ibd file.

ibdata1 :

Shared tablespace / data file for InnoDB tables when innodb_file_per_table option is not specified in my.cnf, all tables are created in this shared tablespace.

ib_logfile0 & ib_logfile1 :

InnoDB log files. The information is logged in circular fashion. InnoDB applies changes recorded in logfiles to tablespace. This is also important for performance and recovery.

.db :

This is data file for BDB (BerkeleyDB) storage engine which stores table data and indexes.

db.opt :

In CREATE DATABASE command, create_specification options specify database characteristics. Database characteristics are stored in the db.opt file in the database directory.

error log :

This log contains information about mysqld start and stop events, any critical errors that occur while the server is running etc. In my.cnf you can specify log-error[=file_name] option and error log will be created under data directory file_name.err. If no name is specified MySQL will create hostname.err file.

slow query log :

This log contains queries that runs slower than specified long_query_time variable. You can specify log-slow-queries[=file_name] option. If no file name specified, MySQL will log slow queries in host_name-slow.log file under data directory.

general query log :

This file logs all the SQL sent from client about MySQL server and mysqld start up/down details. You may enable it by log[=file_name]. If file_name is not specified MySQL will create hostname.log under data directory.

#sql-55c_6.MYD / #sql-55c_6.frm / #sql-55c_6.MYI :

Temporary files created when you normally issue ALTER TABLE, MySQL creates a copy of table, apply changes, delete original table and then rename temporary table to original. The names are like #sql-…/MYD/MYI/frm.

binary log files :

These files contains events that describe database changes such as table creation operations or changes to table data. You may specify their name by –log-bin[=base_name] option. Default name is value of pid-file option (defaults to hostname of server).

relay log files :

These files contains events read from the binary log of the master and written by the slave I/O thread. Events in the relay log are executed on the slave as part of the SQL thread.

master.info :

This file contains the status and current configuration information for the slave’s connectivity to the master.

relay-log.info :

This file holds the status information about the execution point within the slave’s relay log.

.index :

To keep track of which binary log files have been used, mysqld creates a binary log index file. It contains the names of all used binary log files. You may change it by –log-bin-index[=file_name] option otherwise basename will be same as that of binary log file with default extension “.index”.
In case of replication, relay logs uses .index files for same purpose. The default relay log index file name is host_name-relay-bin.index in the data directory. You may change the name by –relay-log-index option.

.TMD:

This is an intermediate data file for a table that needs to recreate its data file. You can find this files normally during REPAIR TABLE operations.

TRG & TRN Files:

Trigger definitions are stored in plain text files in the directory that contains the schema objects.
The file tablename.TRN is the TRIGGERNAME file stored under database directory.
The file tablename.TRG is the TRIGGERS file. It represents all the table triggers attached to a given table, so this file can contain triggers for multiple events (BEFORE/AFTER, INSERT/UPDATE/DELETE).

.ARZ, .ARM & .ARN files:

These are files related to Archieve Storage Engine.

.ARZ is data files holds data for table, .ARM holds metadata while during table optimization process a .ARN file may appear.

27 Mar

Earth Hour March 27 2010-India-Switch off lights for future

Earth Hour is a global event organized by WWF (World Wide Fund for Nature). Earth Hour held on the last Saturday of March annually, asking households and businesses to turn off their non-essential lights and other electrical appliances for one hour to raise awareness towards the need to take action on climate change.

In 2007, 2.2 million residents of Sydney participated by turning off all non-essential lights and in 2008 many other cities around the world adopted the event in 2008.

In India during Earth Hour 2009, almost five million Indians across 56 cities switched off their lights for an hour to contribute towards the action for climate change.
Earth hour saved almost 1000 MW of power! Celebrities and cricketers including Aamir Khan, Sachin Tendulkar and Anil Kumble have supported the cause with Abhishek Bachchan pledging his support as the brand ambassador.

Earth Hour 2010 will take place on March 27, 2010 from 8:30 p.m. to 9:30 p.m., local time.

Earth Hour 2010 is reportedly on track to become the largest Earth Hour yet, aiming to garner more than the one billion participant goal of 2009’s Earth Hour.

121 countries have signed up for Earth Hour 2010.

Show your support:

New economic modelling indicates the world has only 5 years to initiate a low carbon industrial revolution before runaway climate change becomes almost inevitable. But it can be done, and the long term benefits will be enormous.

So now’s the time to take a stand and give world leaders the mandate they need to make the right climate deal.

[ad#ad-2-300×250]

This year, India – IHG Hotels Group Support the Earth Hour Cause by encouraginh in-house guests to switch off lights in their rooms and join the “guestogether” parties being hosted by them between 8:30 and 9:30pm.

Lets start making difference-

Switch off all unnecessary lights @ 8.30 on 27th March

and make the Earth Hour a success towards our a better future.

20 Mar

Working with EditPlus Text Editor-Regular Expression How To

Editplus is a lot better than the regular text editor, Notepad.
From all it’s features I like RegExp Support the most, and than comes the block select feature.
Here are the quick lines to carry out regular tasks using regular expression in Editplus. It’s kinda downloadable cheetsheet list.

Remove all empty lines:

Find: “^\n” (Ignore double-quotes in all find/replace)
Replace: “”
Where,
^ – Beginning of the line
\n – New Line

Remove Multiple Spaces convert into single space:

Find: ” +”
Replace: ” “
Where,
+ – find one or more occurance of space character.

Comment multiple line of code:

Find: “^”
Replace: “#” or “//”
You may optionally use: Edit Menu > Format > Line Comment.
Generate Comma Separated List from new line delimited list:

Find: “\n”
Replace: “, “
This helps in even joining some of lines of code instead of replacing by comma you may replace it with “”.

Manipulate columns display order / punctuation:

Find: “([0-9]+)\t([a-zA-Z]+)”
Replace: “\2\t\1″
Where,
[0-9]+ – Finds one or more digits
[a-zA-Z]+ – Finds one or more characters
() – mark the block or capture the group
\2 – 2nd mark expression
Eg:
123 abc
345 cde
567 efg
Becomes:
abc 123
cde 345
efg 567
The Other Way:
- Press Alt+C
- Drag you mouse to select respective column and click
- Copy / Cut as required

[ad#ad-2-300×250]

Append / Add semicolon (any character) at the end of the line:

Find: “\n”
Replace: “;\n”

Enclose lines by quotes:

Find: “\n”
Replace: “‘\n'”

Delete all lines containing a given STRING:

Find: “^.*STRING.*$”
Replace: “”

Remove lines not containing a given STRING:

I don’t know how to do this!! :)

Convert tab separated file into insert statements:

TSV: abcd de4 iirn 34399
SQL: INSERT INTO TABLENAME VALUES (“abcd”, “de4″, “iirn”,”34399″);
Find: “(.*)\t(.*)\t(.*)\t(.*)”
Replace: “INSERT INTO TABLENAME VALUES (“\1″, “\2″, “\3″,”\4″);”

Format the telephone number:

Find: “([0-9][0-9][0-9])([0-9][0-9][0-9])([0-9].*)”
Replace: “\1-\2-\3″
Eg.:

Original: 1231231231
Formatted-1: 123-123-1231

Remove Brackets:

Find: “\(|\)”
Replace: “”
Where,
\( – Match (. \ is required to escape marking the expression.
| – or

Replace 1st occurrence of character:

Find: ” (.*)”
Replace: “-\1″
Where,
(.*) – matches everything and marks the block
** Make sure you ignore double-quotes(“) while writing in find / replace boxes.

EditPlus supports following regular expressions in Find, Replace and Find in Files command.

Expression – Description
  • \t – Tab character.
  • \n – New line.
  • . – Matches any character.
  • | – Either expression on its left and right side matches the target string.
  • [] – Any of the enclosed characters may match the target character.
  • [^] – None of the enclosed characters may match the target character.
  • * – Character to the left of asterisk in the expression should match 0 or more times.
  • + – Character to the left of plus sign in the expression should match 1 or more times.
  • ? – Character to the left of question mark in the expression should match 0 or 1 time.
  • ^ – Expression to the right of ^ matches only when it is at the beginning of line.
  • $ – Expression to the left of $ matches only when it is at the end of line.
  • () – Affects evaluation order of expression and also used for tagged expression.
  • \ – Escape character. If you want to use character “\” itself, you should use “\\”.

Notable Features of Editplus are :

  • Spell checking
  • Regex-based find & replace
  • Encoding conversion
  • Newline conversion
  • Syntax highlighting
  • Multiple undo/redo
  • Rectangular block selection
  • Auto indentation
  • Code folding (Text folding)

Download pdf: Editplus-RegExp.

18 Mar

batch script to add remove prefix zero pad bulk file rename

This post has two batch scripts:
1. Batch script to rename files with zero padded number series-prefix

2. Batch script to remove prefix of perticular length

1. Batch script to rename files with zero padded number series-prefix

This script will accept file-type to be searched and lenght of zero-padded prefix to be attached.

Usage: RenZeroPad.bat

Consider you have to rename / arrange a lot of mp3 files in a perticular sequence:

File names before execution:
fileX.mp3
fileY.mp3
fileZ.mp3

Command on dos prompt: RenZeroPad.bat mp3 4

File names with attached prefix:
0001 fileX.mp3
0002 fileY.mp3
0003 fileZ.mp3

[ad#ad-2-300×250]

The Batch Script to add zero pad digits prefix:

@echo off
setLocal EnableDelayedExpansion

set /a cnt=1
for %%i in (*.%1) do (
call :Set0Pad %2

set newName=!str! %%i
ren “%%i” “!newName!”
)

:Set0Pad
set padcntr=0000000000%cnt%
set str=%padcntr:~-%1%
set renstr=%str%
set /a cnt+=1

** Download available at the end of the page.

2. Batch script to remove prefix of perticular length

This script will accept file-type to be searched and lenght of prefix to be removed.

Usage: RemovePrefix.bat

Consider you have to rename / remove prefixes from a bunch of files:

0001_fileX.doc
0002_fileY.doc
0003_fileZ.doc

Command on dos prompt: RemovePrefix.bat mp3 5

Files will be renamed with removed prefixes as follows:
fileX.doc
fileY.doc
fileZ.doc

The Batch Script to remove prefix:

@echo off
setLocal EnableDelayedExpansion

set /a cnt=1
for %%i in (*.%1) do (
set str=%%i
set newstr=!str:~%2!
ren “%%i” “!newstr!”
)

Download: RemovePrefix.bat and RenZeroPad.bat

11 Mar

SQL syntax with /*! c-style comments in MySQLdump

In mysql we have — , /* and /*! comments.  This post is mainly about very basic c-style comments.

/*! : C-Style comments in MySQL

We normally see comments in MySQLdump as follows:

/*!40000 ALTER TABLE `a` DISABLE KEYS */;
Or
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */

These are actually C-Style comments which has embeded sql and treated specially by MySQL server, but ignored by other database engines.
This helps in writing portable comments, it’s treated as a part of surrounding SQL by MySQL while ignored by other databases.

But still you may wonder what are these numbers after /*! – /*!40000 or /*!50013 !!

There are two ways you can write the C-Style comments.
1. /*! SQL */

When SQL is embeded with comments [/*! and */] it’s executed by MySQL server along with surrounding SQL.

2. /*!version-number SQL */

When SQL is embeded with comment that begins with /*! follwed by a version number, the SQL becomes version specific.
MySQL executes SQL body only if it server’s version is atleast as recent as version-number.

So if your MySQL version is 5.x, following syntax will give error.

SHOW /*!60000 status */;

but following will work fine!

SHOW /*!50000 status */;

[ad#ad-2-300×250]

Similarly see below example:
mysql> show /*!50077 full*/ tables;
+—————-+————+
| Tables_in_test | Table_type |
+—————-+————+
| comments       | BASE TABLE |
+—————-+————+
1 row in set (0.00 sec)

mysql> show /*!50078 full*/ tables;
+—————-+
| Tables_in_test |
+—————-+
| comments       |
+—————-+
1 row in set (0.00 sec)

Above code works at first attempt as MySQL Version was 5.0.77  but for version specific comment /*!50078 it don’t.


Other comments in MySQL:

  • – : double slash and a space or # : Hash

This is spanned till end of the line.

  • /* :

This is a normal multi-line comment.

-- Kedar Vaijanapurkar --