MySQL Frequently Using Commands

MySQL is one of the most using database system. Almost all web sites are using MySQL database. I wrote most using commands of MySQL database.I will share various commands using examples for MySQL database .

Connect MySQL

[root@testdb ~]# mysql
[root@testdb ~]# mysql -h hostip
[root@testdb ~]# mysql -h hostip -u username
[root@testdb ~]# mysql dbname -u username

[root@testdb ~]# mysql dbname -u username -P portnumber

List, Add, Drop, Change and Grant User

mysql> use mysql
mysql> create user Ajay;
mysql> drop user Ajay;
mysql> update user set password=PASSWORD(“newpassword”) where user=’Ajay’;
mysql> grant all privileges on testdb.* to Ajay;
mysql> grant all privileges on *.* to Ajay;
mysql> revoke all privileges on testdb.* from Ajay;
mysql> revoke all privileges on *.* from Ajay;
mysql> flush privileges;
mysql> grant usage on testdb.* to Ajay identified by ‘password’;
mysql> grant usage on *.* to Ajay identified by ‘password’;
mysql> revoke usage on testdb.* from Ajay
mysql> revoke usage on *.* from Ajay

mysql> flush privileges;

Information Queries of MySQL Database

mysql> help;

mysql> use testdb;
mysql> show tables;
mysql> desc tablename;

mysql> connect mysql;
mysql> select user();

mysql> show variables;
mysql> show variables where variable_name = ‘Port’;

[root@testdb ~]# mysqladmin –help
[root@testdb ~]# mysqladmin –version
[root@testdb ~]# mysqladmin ping

[root@testdb ~]# mysqladmin variables