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;