A MySQL Vacuum database tables script (auto_increment reset)

MySQL vacuum FAQ: How do I vacuum a MySQL database table, or an entire MySQL database?

Discussion (MySQL VACUUM == OPTIMIZE TABLES)

This may be an unusual situation, but as I'm creating a new application that uses a MySQL database, I've found that I want a "MySQL vacuum" operation; I want to reset all my MySQL auto_increment counters back to 1 without having to completely drop and then re-create my MySQL database.

Specifically what I'm doing is this:

1. Running approximately 10,000 inserts against some of my MySQL database tables.
2. Finding bugs in my programs and approach.
3. Dropping my MySQL database tables (without dropping the database).
4. Recreating my database tables (basically using "drop table if exists foo" followed by "create table foo").
5. Going back to Step 1.

As I do this, my MySQL auto_increment counters are constantly increasing; first they start at 1, then 10,001, then 20,001, etc. This is isn't a huge problem in development mode, but it is annoying. I thought there was a MySQL VACUUM operator, but there isn't. Instead there is a MySQL OPTIMIZE TABLES command.

A MySQL vacuum script (MySQL auto_increment reset script)

So, to reset the MySQL auto_increment counter back to 1, I finally stumbled on the following technique, which I just tested, and does indeed set all the auto_increment counters back to 1.

<?php

# NAME:       VacuumMysqlDatabase.php
# DEVELOPER:  Alvin Alexander, http://devdaily.com
# COPYRIGHT:  
# LICENSE:    Copyright 2010, Alvin Alexander.
#             This software is released under the terms of the
#             Creative Commons Share-Alike License. See the following
#             URL for more information:
#             http://creativecommons.org/licenses/by-sa/3.0/

# database parameters
$db_host   = 'localhost';
$db_user   = 'root';
$db_pass   = 'root';
$db_dbname = 'codeme';

# show all errors right now
error_reporting(E_ALL);
ini_set("display_errors", 'On'); 

# connect to database
$link = mysql_connect($db_host, $db_user, $db_pass);
if (!$link)
{
  die('Could not connect: ' . mysql_error());
}
mysql_select_db ($db_dbname);

# vacuum all the mysql tables
$alltables = mysql_query("SHOW TABLES"); 
while ($table = mysql_fetch_assoc($alltables)) 
{ 
  foreach ($table as $db => $tablename) 
  { 
    mysql_query("OPTIMIZE TABLE $tablename") or die(mysql_error()); 
  } 
} 

# need root access (or similar) to run this command
mysql_query("FLUSH TABLES") or die(mysql_error()); 

?>

My MySQL Vacuum tables script - Summary

I'm not going to explain this script much, other than to say that in my MySQL database, it does the "vacuum" stuff I mentioned, setting the MySQL auto_increment counter back to 1. So, I hope this MySQL vacuum script is helpful for you.