Common But Important Mysql Commands And Queries

Here are some of the generally used queries. These are important and useful.


1. >mysql –user=root mysql

default user name is "root". And there is no password.


2. >grant all privileges on *.* to 'sat@localhost' identified by 'satpwd' with grant option;

'sat'@'localhost' -> sat is username and localhost is mysql host name.


3. >grant all privileges on *.* to 'sat'@'localhost' identified by 'satpwd' with grant option;

*.* means all DB.


4. >grant all privileges on *.* to 'sat'@'%' identified by 'satpwd' with grant option;

% is used for the user can access from anywhere and not only from 'localhost'


5.
1. set password=password=password('password');
2. set password for user = password('password');
3. set password= old_password('password'); (one of the method of solving this problem - "Client does not support authentication protocol requested" )
4. set password for 'sat'@'%.loc.gov'=password('newpasswd');

These queries will update the user table.
So, 4th is equivalent to


>update mysql.user set password = password('newpwd') where user='sat' and host='%.loc.gov';


6. >flush privileges

Flush privileges are necessary when doing update for user.
7. Server Access:1st is also for server access, but there you are only providing username for login, which is the case when mysql is just installed.


>mysql –h satyapra –u sat -p
and press enter. It will ask for password-
Enter password:…

-h means hostname
-p means password
-u means username
Password can be entered on the same line. For password do not give space between –p and 'password'. Drawback is, your password will be visible by others.

8. Few informative select queries to server:

>Select User();
>Quit;
>Select version();
>Select version(), current_date;
>Select version(), select current_date;
>Select now();

9. Remove mysql from service list (onwindow):

C:\>mysqld-nt --remove
C:\>mysqld-nt --remove service_name

Give service name if service name is different from the default service name on your window server list.

C:\>mysqld-nt --install

Install default service name.

10.
Take backup from mysql server for a particular database:

>mysqldump -h host -u sat -p --add-drop-table v2 > v2.sql

Where 'v2' is my database name, 'sat' is username. --add-drop-table will add Drop table option, so if table with the same name is there in v2 database at the time of restoring the database then that old table will be dropped. 'v2.sql' is the file where database will be dumped. The file can be on your C:\> path or on the home directory of the user logged in on Linux.
Use '-d' flag also, if you like to avoid rows info/data (no insert statement).