MySQL CheatSheet

Here is the list of some commonly used MySQL commands. It comes in handy when creating databases and troubleshooting issues related to character set and collation.

# connect to database
mysql -u user -p -h host database

# check connections and processes
show variables like '%conn%';  
show status like '%conn%';  
show processlist;

# check character-set and collation
show variables like '%character%';  
show variables like '%collat%';  
show table status where name = 'MY_TABLE';  
show full columns from 'MY_TABLE';

# database and character sets
alter database MY_DATABASE character set utf8 collate utf8_unicode_ci;  
alter table MY_TABLE character set utf8 collate utf8_unicode_ci;  
create database MY_DATABASE default character set = 'utf8' default collate = 'utf8_unicode_ci';

# create new table
create table if not exists MY_TABLE (  
    ID bigint not null auto_increment,
    NAME varchar(100) not null,
    ORG_ID bigint not null,
    COMMENT longtext,
    CREATED_ON datetime,
    primary key (ID),
    unique (NAME, ORG_ID)
) engine = MyISAM|InnoDB , default character set = 'utf8' default collate = 'utf8_general_ci';

# create new user
create user 'dummy'@'localhost' identified by 'dummy';  
grant all privileges on MY_DATABASE.* to 'dummy'@'localhost' with grant option;  
create user 'dummy'@'%' identified by 'dummy';  
grant all privileges on MY_DATABASE.* to 'dummy'@'%' with grant option;

# backup and restore
mysqldump -u userA -p db_xyz > db_xyz.sql  
mysql -u userA -p db_xyz < db_xyz.sql  

Baldeep Hira

bay area programmer working on mobile/tablet/web apps and enterprise cloud apps; ui/ux, html5 and everything else for a prettier web and world

  • San Francisco Bay Area
comments powered by Disqus