Useful MySQL Tricks

This was originally going to be an article, but I got sidetracked whilst drafting it, so haven't really got much further than scrawling a couple of notes down! It may be of use to some though;

It's also now been superseded by a (slightly) more complete MySQL Cheatsheet

Removing Tables based on their Prefix

When migrating a Joomla site with JUpgrade, you may want to remove all the Joomla 1.5 tables

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';') AS statement FROM 
information_schema.tables WHERE table_schema = 'Btaskercouk' AND table_name LIKE 'jos_%';

Excluding Databases from a dump

To exclude a database from MySQLDump

mysqldump --databases `mysql --skip-column-names -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') 
FROM information_schema.schemata WHERE schema_name 
NOT IN ('mysql','performance_schema','information_schema');" >` >/mysql/backup/rds2.sql

Logging your SQL Session

Logging your commands (useful for recording exactly what you've run)

mysql -u username -p --tee=/home/$USER/mysql.log

Every command you run will be saved to ~/mysql.log, including the output. To extract just the commands you ran

cat /home/$USER/mysql.log | grep "mysql> "  | sed s/"mysql> "/""/g > commands.sql

This will give you a pure sql file that you can pipe straight into mysql

mysql -u username -p < commands.sql

Cloning a Table

Creating a clone of a table (excluding data)

create table bens_new_table LIKE the_old_table;

Creating a clone of a table (with data) - doesn't copy keys or indexes though

create table bens_new_table SELECT * FROM the_old_table;

Renaming Tables

Rename a Table

RENAME TABLE bens_old_table TO bens_new_table;