• Table of Contents
• Cheat Sheet
• Performance Best Practices
• References
• About

We love cheatsheets; when you need a quick access to a command or query and do not have time for reading an article or essay or digging through forum posts, cheatsheets are life savers. In such a spirit, we have decided to create a cheatsheet for commonly used MySQL commands here. It is a work in progress. Once completed, we will create a PDF download for this cheatsheet so that you can print it on a single page.

DATABASE MAINTENANCE

# Backup a database in a live environment without locking it:
$ mysqldump -u user -p -h host --opt --skip-lock-tables --single-transaction my_db > my_db.sql

# Backup a single table in a live environment without locking it:
$ mysqldump -u user -p -h host --opt --skip-lock-tables --single-transaction my_db my_table > my_table.sql

# Check status of a MYISAM (default engine) table
mysql> check table [table];

# Repair MYISAM table
mysql> repair table [table];

# Exporting data from a table into a CSV file in /tmp directory
mysql> SELECT [fields] FROM table [WHERE condition] INTO OUTFILE '/tmp/[table].txt' \
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

DATABASE STATUS

# What version of MySQL are you running?
mysql> SELECT VERSION();

# Check who is running what queries?
mysql> show full processlist;

# Get quick stats from server
mysql> status;

# Show variables like [string]
mysql> show variables like '%[string]%';

DATABASE ADMINISTRATION

# Create a duplicate (struct) table from an existing table
mysql> create [new table] like [existing table];

# Force auto increment to a certain [value]
mysql> ALTER TABLE [table] AUTO_INCREMENT = [value];

# Capture all commands into a log file
mysql>tee [filename]

# Optimize MYISAM tables after massive delete operations
mysql> optimize table [table];

PERFORMANCE

# Check query cache
mysql> show status like '%cache%';

# Show index for a table
mysql> show index from user;

DANGEROUS STUFF (NOT RECOMMENDED)

# Kill a MySQL thread
mysql> show full processlist; /* to show process list with Id numbers */
mysql> kill [process id]; /* good luck! I hope you know what you are doing :) */

# Excute a Linux command such as ls, pwd, du, df, etc. from within mysql prompt
mysql> system [linux command];

Copyright © 2010 EVOKNOW, Inc.