Wednesday, June 15, 2011

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;
# 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