SQL Command line
Question asked by as_nawin in #Coffee Room on Oct 16, 2009
as_nawin · Oct 16, 2009
Rank D2 - MASTER
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 Posted in: #Coffee Room
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 Posted in: #Coffee Room
