How to log each MySQL query

To log each and every query executed by MySQL, simply specify “-l” when starting mysqld_safe. For instance:

/usr/bin/mysqld_safe -l ...

All queries will then be logged to <hostname>.log in the data directory (typically /var/lib/mysql).

LAMP Virtual Appliance

This is a LAMP VM that’s certified by VMWare: http://www.virtualappliances.net/products/lamp.php

Default username/password is root/root.

Alter MySQL table engine type

Alter table t1 TYPE = InnoDB;

MySQL 5 data types

Explanation of the various data types in MySQL 5: http://dev.mysql.com/doc/refman/5.0/en/data-types.html

Reset MySQL auto_increment

ALTER TABLE mytable AUTO_INCREMENT = 8;

This sets the auto_increment ID to start from either 8 or its current value, whichever is higher.

Template for PHP MySQL queries

<?
$link = mysql_connect(’localhost’, ‘root’, ‘password’);
if (!$link) {
die(’Could not connect: ‘ . mysql_error());
}

$db_selected = mysql_select_db(’jsfbasic’, $link);

$result = mysql_query(’SELECT * FROM recipe’, $link);

if (!$result) {
die(’Invalid query: ‘ . mysql_error());
}

while ($row = mysql_fetch_array($result)) {
echo $row[0] . ‘<br />’;
}

mysql_close($link);
?>

Last inserted ID in MySQL

To retrieve the last inserted ID of the auto_increment column:

mysql> select last_insert_id();

The PHP equivalent is:

mysql_insert_id

The above must be invoked from the same thread that performed the insertion.