Some Useful MySQL Commands Used Day to Day

Today I thought of presenting you with something of a little different taste.Therefore I thought of publishing some useful and more common mysql commands that will be useful to developers in their day to day development tasks. As mysql is the most commonly used opens ource database in java EE based development I think this post content will be useful to a lot of novice fresh graduate developers who are entering the industry. I've used a small mysql comment line to explain briefly the purpose of each command mentioned below.

  -- Getting a MySQL Dump(From remote DB):  
  mysqldump -u root -ppassword -h server_name db_name > database_dump_name.sql 

   
  -- Restore a MySQL Dump(To remote DB):    
  mysql -u root -ppassword -h server_name db_name < database_dump_name.sql 

   
  -- Output Data To a File    
  SELECT * FROM table_name INTO OUTFILE 'c:/file_name.file_extention';   

 
  -- Optimize Data Table : Used to defragment the table data file    
  optimize table table_name;    

  -- Monitor/View MySQL Memoty Usage On Linux /shell    
  top -u mysql    

  -- Create an Index for an existing Table In MySQL    
  CREATE INDEX table_column ON table_name(table_column);    

  -- Create a New User and Assign him all privileges to an existing database   
   GRANT ALL ON cpsdb.* TO user@localhost IDENTIFIED BY 'password';