To Backup only MYSQL database schema

CREATE DATABASE nplay_temp;

mysqldump --user xxxx --password=xxxx --databases xxxx --no-data > /tmp/xxxx_20101027.sql

Few site for Reference for MySQL DBA

http://hackmysql.com/

http://www.mysqlperformanceblog.com/

of course

http://dev.mysql.com/

Not exactly for this one, but you may refer the idea

http://dbaspot.com/

MYSQL - To Delete records from tableA that matching records from tableB

DELETE FROM t1 USING nplay_offline.sms_out_message AS t1 INNER JOIN nplay_delete.sms_out_message AS t2 WHERE t1.id=t2.id;

VI - to search and replace string in the file

:%s/sms_out_message_20101013/sms_out_message/g

UPDATE on INNODB status - INNODB_TABLE_LOCKS=1

Default value of INNODB_TABLE_LOCKS=ON, which means that Lock tables causes innodb to lock a table internally is autocommit=0;

MySQL InnoDB deadlock issue - lock mode AUTO-INC

AUTO-INC works as table level lock, which means other threads can't perform an insert in this table.

Hence, it means the insert statement would cause the threads to keep the lock to the end of the current insert SQL statements.

Suggested fix: you would have your own mechanism to generate the unique key to replace the "auto-increment" ID/fields. So that your insert statement may execute concurrently

Shell Scripts to run MySQL Query

 DB_NAME=SomeDB USER_NAME=someuser IP_ADDR=localhost PASSWORD=somepassword  somevar=`echo "select * from <table_name> where <condition>" | mysql -h $IP_ADDR -u$USER_NAME -p$PASSWD $DB_NAME `  echo $somevar