Tuesday, January 25, 2011

MySQL - Restart - AutoInc resets to zero

I noticed fact recently that MySQL Table autoincrement Id value resets to zero, if table is empty.

solution to this is, you can reset the counter to

mysql> ALTER TABLE tbl AUTO_INCREMENT = 1000;

Wednesday, January 19, 2011

Crontab/Cron analyzed - not working issues

It has now become routine job to write shell scripts to schedule jobs on server. (Ubuntu)

Although its routine, still sometime we got stuck with same issues. so I thought lets write a small article
to type out common errors I made.

1) Path -
By default, in cron environment, no Path is defined, so you can not even run simplest of shell file with simple
command like 'date'

you need to define
PATH=/home/sandip/bin:/bin:/sbin:/usr/bin:/usr/sbin/$PATH

your script testCronFile.sh can reside in /home/user/bin. it should have 755 perms.
chmod 755 testCronFile.sh


additionally to make sure that Script is executing.

you can do it using following 2 ways..
1) sudo tail -f /var/log/syslog
You would see something like following..

Jan 19 10:08:01 localhost CRON[11593]: (root) CMD (testCronFile.sh)


2) In testCronFile.sh, You can include line like

echo "Testing Shell file" >> /home/sandip/cron.log

so after execution of script, cron.log file would get created with "Testing Shell file" written in it.

for accessing files under root permission from cron, use sudo
$ sudo crontab -e


You can add comments, if you have something else regarding cron files.

Tuesday, January 04, 2011

MySQL export large data from command promt to CSV file

If you can cope with table-at-a-time, and your data is not binary, use the -B option to the mysql command. With this option it'll generate TSV (tab separated) files which can import into Excel, etc, quite easily:

% echo 'SELECT * FROM table' | mysql -B -uxxx -pyyy database
Alternatively, if you've got direct access to the server's file system, use SELECT INTO OUTFILE which can generate real CSV files:

SELECT * INTO OUTFILE 'table.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table


Courtesy:
Stack Overflow

http://stackoverflow.com/questions/467452/dump-a-mysql-database-to-a-plaintext-csv-backup-from-the-command-line