MySQL recipes

MySQL recipes

#login
mysql -u <user> -p

# list all databases
show databases;

# 'open' a database
use <database_name>;

# list all tables
show tables;

# list table schema
describe <table_name>;

# show table content (with human-friendly formatting)
select * from <table_name> \G

# ditto, but set a limit
select * from <table_name> limit 1 \G

# delete all tables
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

# export a dump file
mysqldump <database_name> > file.sql

# export a dump file from another db server
mysqldump -u <user> -p -h <server> <database_name> > file.sql

# import a dump file
mysql -u <user> -p <database_name> < file.sql