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 -u root -p
2. List all the databases in MySQL server-
mysql> show databases;
3. Create new database in MySQL server-
mysql> create database;
4. Create a table in a database-
create table `` (`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;
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;
for example-
mysql> desc fortest;
8. To access all the contents in a table-
mysql> select * from;
for example-
mysql> select * from fortest;
9. To retrieve specific info from particular table-
mysql> select * from where = ‘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 identified by ‘’;
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 > C:\backup\test.sql
17. Backup particular table from the database-
C:\MySQL\bin> mysqldump -u root -p > C:\backup\fortest.sql
18. Restore a database from the backup-
C:\MySQL\bin> mysql -u root -p < C:\backup\test.sql
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
2. List all the databases in MySQL server-
mysql> show databases;
3. Create new database in MySQL server-
mysql> create database
4. Create a table in a database-
create table `
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
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
for example-
mysql> desc fortest;
8. To access all the contents in a table-
mysql> select * from
for example-
mysql> select * from fortest;
9. To retrieve specific info from particular table-
mysql> select * from
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
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
17. Backup particular table from the database-
C:\MySQL\bin> mysqldump -u root -p
18. Restore a database from the backup-
C:\MySQL\bin> mysql -u root -p
Replies
You are reading an archived discussion.
Related Posts
Lost your MySQL root password? Resetting MySQL root password is simple. Please follow these steps-
# Login to your MySQL server.
# Locate the mysql.ini file.
-This should be something...
IF u think u cannot format c drive when windows is running so try this & u can test on other drives this is simple binary code
format c:\ /Q/X...
How to make our own shortcut key
Follow these steps
1)Select file or folder of which u want to make a shortcut key
2)Right click on it & Select Send...
To this what can be done is that the USB can be blocked and then stopping the use of USB drives. Here is a simple registry hack to do so,...
There are many new features in Windows Vista that are installed by default that you may not need. These extra and un-needed features can slow down your computer and take...