August 16, 2008

MySQL quick reference

Posted in Software at 01:30 by graham

A concise reference / cheat-sheet / crib-sheet to MySQL’s commands.

Query

List databases

show databases;

List tables in db

show tables;

List columns in table:

desc mytable;

List indexes on a table

show index from mytable;

List foreign keys:

show create table mytable;

Next auto_increment key:

select auto_increment from information_schema.tables where table_name = ‘mytable’ and table_schema=’mydbname’;

To reset the auto_increment key: alter table "table_name" auto_increment=1

Show encoding (client, database, etc)

show variables like ‘character_set%’;

Input / Output

These need to be run from the command line:

Dump database:

$ mysqldump –add-drop-table -h HOST -u USER -p DATABASE > database.sql

Load database:

$ mysql -h HOST -u USER -p DATABASE < database.sql

Export single table:

select * from mytable into outfile ‘myfile.dat’;

Your user needs permissions to create the disc file, so you maybe need to connect to MySQL as root

Import single table:

load data infile ‘myfile.dat’ into table mytable;

DDL

Create database:

create database mydatabase character set utf8 collate utf8_general_ci;

If you don’t use UTF-8 I will find your application and fill it with non-ascii characters. You have been warned.

Create / grant user:

grant all on mydatabase.* to myuser@localhost identified by ‘mypassword’;

Add column:

alter table mytable add mycol datatype null;

Change column:

alter table mytable change myoldcol mynewcol datatype null;

The same command will rename a column, change it’s datatype, or change it’s null / not null status.

Drop column:

alter table mytable drop column myoldcol;

Rename table:

rename table myoldtable to mynewtable;

Create index:

create index myindex on mytable (mycol);

Drop index:

drop index myindex on mytable;

Create foreign key:

alter table mytable add foreign key myforeignkey (mycol) references mytable2 (mycol2);

Drop foreign key:

alter table mytable drop foreign key myforeignkey;

Replication

You need to be root for these commands.

Master status:

show master status;

Slave status:

show slave status;

5 Comments »

  1. Recent Links Tagged With “schema” – JabberTags said,

    January 30, 2009 at 10:04

    […] NHibernate 2.0 gold Released – Must Wait for ‘Linq to… Saved by jacthebear on Mon 19-1-2009 Graham King: MySQL quick reference Saved by hypershadic on Sun 18-1-2009 Schema Evolution Saved by murken on Tue 13-1-2009 An […]

  2. Recent Faves Tagged With “variables” : MyNetFaves said,

    January 25, 2009 at 20:20

    […] public links >> variables Graham King: MySQL quick reference First saved by supergimp | 13 days ago Why adding variables to CSS is a good thing First saved […]

  3. Boris Mohr said,

    October 12, 2008 at 15:12

    Nice little online reference to MySQL DB/table creation and management essentials. Thanks!

    Actually, I landed here while seraching for something different – a MySQL administrator’s quick reference. Here is one of the better ones I have found thus far

    http://www.explainth.at/en/qr/mysqlqr.shtml

  4. eugenio said,

    September 5, 2008 at 12:05

    You may want to add show processlist for load estimation and occasional troubleshooting…

    (how’s life?)

    [graham] Life’s good thanks! I never use ‘show processlist’, because all it shows me is my ‘show processlist’ command running. I guess my apps aren’t database intensive.

  5. Rich Bryant said,

    September 1, 2008 at 03:18

    Error on mysql help page … “show index for” should “show index from”. (Very nice reference page, though – thanks for it).

    [graham] Thanks Rich, I’ve fixed that.

Leave a Comment

Note: Your comment will only appear on the site once I approve it manually. This can take a day or two. Thanks for taking the time to comment.