Tuesday, October 5, 2010

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

1 comment :

YourGeek Fellow said...

Where I have to write those commands ? the place where we run SQL Queries?

Post a Comment