Mysql Tutorial And Commands Part 1

Here is first set of my basic 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

 

Filed: Tutorials
tags: Commands, MySQL, Part, Tutorial