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

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


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;

Further Learning
MySQL Fundamentals by Pinal Dave
MySQL Fundamentals Part 2 by Pinal Dave
High Performance MySQL: Optimization, Backups, and Replication

If you like this article then you may like to see my next set of mysql commands tutorial

No comments :

Post a Comment