Learn Useful MySQL Tricks

This was actually planned to be an article, but I got diverted while drafting it. It has become more like a cheatsheet now.

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;

Thank you!

Leave a Reply