August 16, 2008

MySQL quick reference

Posted in Software at 01:30 by Graham King

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;