SQL Command line

MySQL command line administration-

1. Login to server-

Goto MySQL installed location through command prompt-

C:\MySQL\bin

or

C:\Program files\MySQL\bin

Specify MySQL user and password -

C:\MySQl\bin> mysql -u root -p

Above command is used to login to local MySQL server. If you want to login to remote MySQL server use this command

C:\MySQl\bin> mysql -h <specify-remote-mysql-server-name-or-ip> -u root -p

2. List all the databases in MySQL server-

mysql> show databases;

3. Create new database in MySQL server-

mysql> create database <specify-db-name-here>;

4. Create a table in a database-

create table `<specify-table-name-here>` (`field1` type , `field2` type);

For example- To create a table ‘fortest’ with 3 fields – testid, testname, testemail-

mysql> create table `fortest` (`testid` int( 11 ) not null auto_increment , `testname` varchar( 50 ) not null , `testemail` varchar( 100 ) not null , primary key ( `testid` ));

5. To access a particular database in MySQL server-

mysql> use <specify-db-name-here>;

for example-

mysql> use test;

6. To view all the tables in that particular database-

mysql> show tables;

7. To view that particular database’s table field format-

mysql> desc <specify-table-name-here>;

for example-

mysql> desc fortest;

8. To access all the contents in a table-

mysql> select * from <specify-table-name-here>;

for example-

mysql> select * from fortest;

9. To retrieve specific info from particular table-

mysql> select * from <specify-table-name-here> where <specify-field-name-here> = ‘any-info’;

for example – if you want to find the details of a person named ‘arun’ from the table ‘fortest’ -

mysql> select * from fortest where testname = ‘arun’;

10. To create user in MySQL server-

mysql> create user <specify-user-name-here> identified by ‘<specify-password-here>’;

for example-

mysql> create user ‘testinguser’@'%’ identified by ‘testingpassword’;

alternate method-

You can create through mysql database-

mysql> use mysql;
Database changed
mysql> insert into user (host,user,password) values (’%',’testinguser’,password(’testingpassword’));
Query OK, 1 row affected (0.19 sec)

mysql> flush privileges;

11. Change password for a user-

mysql> set password for ‘user-name’@'hostname’ = password(’specify-password-here’);

12. To assign database specific privileges-

All privileges to a particular database-

mysql> grant all privileges on `test` . * to ‘testinguser1′@’%’ with grant option ;

Specific privileges to a particular database-

mysql> grant select , insert , update , delete , create , drop , index , alter , on `fortest` . * to ‘testinguser’@'%’;

Alternate method-

mysql> insert into user (host,db,user,select_priv,insert_priv,update_priv, delete_priv,create_priv,drop_priv) values (’%',’specify-db-name-here’,’specify-user-name-here’,'y’,'y’,'y’,'y’,'y’,'y’);

mysql> use mysql;
Database changed
mysql> insert into user (host,db,user,select_priv,insert_priv,update_priv, delete_priv,create_priv,drop_priv) values (’%',’test’,'testinguser’,'y’,'y’,'y’,'y’,'y’,'n’) ;

13. To remove privileges for a particular user from a database-

mysql> revoke all privileges on `test` . * from ‘testinguser’@'%’;

14. To delete a particular table-

mysql> drop table `fortest` ;

15. To delete a particular database-

mysql> drop database `test` ;

16. Backup the database-

C:\MySQL\bin> mysqldump -u root -p <database-name> > C:\backup\test.sql

17. Backup particular table from the database-

C:\MySQL\bin> mysqldump -u root -p <database-name> <table-name> > C:\backup\fortest.sql

18. Restore a database from the backup-

C:\MySQL\bin> mysql -u root -p <database-name> < C:\backup\test.sql