Basic MySQL Commands

To login (from unix shell) use -h only if needed.

#mysql -h hostname -u root -p

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database’s field formats.

mysql> describe [table name];

To delete a database.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Creating a new user.

# mysql -u root -p

mysql> use mysql;

mysql> INSERT  INTO user (Host,User,Password)  VALUES('%','username',PASSWORD('password'));

mysql> flush privileges;

Change a users password from unix shell.

#mysqladmin -u username -h hostname  -p password 'new-password'

Change a users password from MySQL prompt. 

# mysql -u root -p

mysql> SET PASSWORD FOR  'user'@'hostname' = PASSWORD('password');

mysql> flush privileges;

Recover a MySQL root password.

# /etc/init.d/mysql stop

# mysqld_safe --skip-grant-tables  &

# mysql -u root

mysql> use mysql;

mysql> update user set  password=PASSWORD("newrootpassword") where User='root';

mysql> flush  privileges;

mysql> quit

# /etc/init.d/mysql stop

# /etc/init.d/mysql  start

 

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user “user1” to connect to the server from localhost 

# mysql -u root -p

mysql> use mysql;

mysql> grant  usage on *.* to user1@localhost identified by 'password';

mysql> flush  privileges;

Give user privilages for a database.

mysql> grant all privileges on  databasename.* to username@localhost;

mysql> flush privileges;

          or

# mysql -u root -p

mysql> use mysql;

mysql> INSERT  INTO user  (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)  VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');

mysql>  flush privileges;.

Load a CSV file into a table.

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO  TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  (field1,field2,field3);

Dump all databases for backup.

#mysqldump -u root -ppassword --opt  >/tmp/alldatabases_backup.sql

Dump one database for backup.

#mysqldump -u username -ppassword --databases  databasename >/tmp/databasename.sql

Dump a table from a database.

mysqldump -c -u username -ppassword  databasename tablename > /tmp/tablename.sql

Restore database (or database table) from backup.

mysql -u username -ppassword databasename  < /tmp/databasename.sql