MySQL Cheatsheet

I started an article on basic MySQL Tips and Tricks a little while ago, but never quite finished it. This documentation contains those tips as well as some additional techniques I've picked up

Tables

Clone Table Structure

CREATE TABLE new_tbl_name LIKE tbl_name;

 

Clone Table (including Data)

CREATE TABLE new_tbl_name SELECT * FROM tbl_name;

 

Rename a table

RENAME TABLE tbl_name TO new_tbl_name;

 

Change the DB Engine for a Table

ALTER TABLE tbl_name ENGINE=InnoDB;

 

Removing Tables based on their Prefix

For example, after upgrading a Joomla! site you may want to remove all tables who's name begins with jos_

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

 

 

Indexes

Create an Index

CREATE INDEX idx_name ON tbl_name(field_name);

 

Delete and Index

DROP INDEX idx_name ON tbl_name;

 

Index first n characters of field Note: Only works with certain field types

CREATE INDEX idx_name ON tbl_name(field_name(n));

 

View Index Cardinality

SHOW INDEXES FROM tbl_name;

 

 

Backups

Backup all databases

mysqldump --all-databases > backup.sql

 

Backup all databases except a certain one

In the example below, we'll exclude 'mysql','performance_schema' and 'information_schema'

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');" >` >backup.sql

 

Extracting a Single Database from a multiple database SQL Dump

sed -n '/^-- Current Database: `database_name`/,/^-- Current Database: `/p' fulldump.sql > foobar.sql
mysql < foobar.sql

 

Restoring InnoDB tables when there are foreign key constraints

When restoring a database backup that includes InnoDB tables, you may find the restoration fails because of foreign key constraints, we can adjust the sqldump to disable the foreign key checks, and then re-enable once restoration is complete

sed -i "`egrep -m 1 -n -e "USE (.)*;" test.txt | cut -d\: -f1`a\ SET FOREIGN_KEY_CHECKS=0;" dbdump.sql
echo "SET FOREIGN_KEY_CHECKS=1;" >> dbdump.sql
mysql < dbdump.sql

 

 

Logging and Profiling

Logging your SQL Session

It's possible to have your SQL session logged to an SQL file so that you can repeat the exact steps as though you were restoring a backup

mysql --tee=/home/$USER/mysql.log


# To adjust the file so that you can re-run the commands
grep "mysql> " /home/$USER/mysql.log | sed s/"mysql> "/""/g > commands.sql
mysql < commands.sql

 

Profiling MySQL Queries

To retrieve information such as execution time

SET profiling=1;
SELECT * FROM tbl_name;
SHOW PROFILES;

 

Calculating the Size of all Databases

SELECT count(*) tables,
  concat(round(sum(table_rows)/1000000,2),'M') rows,
  concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
  concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES;

 

Calculating the Size of Indexes and Data

SELECT engine,
        count(*) tables,
        concat(round(sum(table_rows)/1000000,2),'M') rows,
        concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
        concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
        concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
        round(sum(index_length)/sum(data_length),2) idxfrac
        FROM information_schema.TABLES
        GROUP BY engine
        ORDER BY sum(data_length+index_length) DESC LIMIT 10;

 

How to reset the MySQL Admin user's password