MySQL tutorial and commands Part 1

Here is first set of my basic mysql commands which I have used in my day to day life while working with mysql database.



For Checking Wheather MySQL Server is running or not

-------------------------------------------------------------

ps -auxwww | grep mysql





Starting mysql

-----------------------

go to mysql installation directory and execute below command





./bin/mysqld_safe &





Stopping mysql

----------------



cd mysql/bin

./mysqladmin -u root shutdown

./mysqladmin --host=localhost --port=3305 -u root shutdown //for second instance listening on port 3305







Viewing mysql processlist and kiling offending mysql process

----------------------------------------------------------------------

This is extremely useful to see which query is running on which host , from which location query has fired , which query has locked which table etc.



cd mysql/bin

./mysqladmin -u root processlist





Kill processes



cd mysql/bin

./mysqladmin -u root kill ProcessID







How to see mysql help

---------------------------

you can see the mysql help from command prompt itself like following, you just need to understand it.



mysql> help alter;







Repair the table

--------------------

REPAIR TABLE TableNAme





copying data from one table to another

---------------------------------------------

This is very useful when you are altering table and you would like to take backup of data.



insert into ORDERS_TMP select * from ORDERS









Dropping columns from the table

-------------------------------------



ALTER TABLE `database`.`ORDERS` DROP COLUMN `BRAND`;







Adding Keys(Indexes) to a table

-------------------------------------



alter table ORDERS add KEY `BY_CLIENT` (`CLIENT_ID`) (here CLIENT_ID is a column in ORDers table)







modifying a column

-----------------------

This is useful in case you want to modify datatype or size of a particular column



alter table ORDERS modify column BRAND varchar(15) default NULL







Rename Table

------------------

This is again a useful method for creating backup of table before playing with it.

Renaming the new table to the original name:



mysql> ALTER TABLE new_table RENAME old_table;

to read further please see next set of mysql commands tutorial

MySQL tutorial and commands part 2

MySQL tutorial and commands part 3

MySql Tutorial : mysqldump utility in mysql

Difference between truncate and delete in SQL

Basic and Advanced SELECT command example

How to manage transaction in MySQL

Source:http://javarevisited.blogspot.com/2010/10/frequently-used-mysql-commands-part-1.html