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 MySQL Database


List databases

mysql> show databases;

Add database

mysql> create database testdb

Drop database

mysql> drop database testdb

List, Add, Drop, Change and Grant User


mysql> use mysql
mysql> create user facar;
mysql> drop user facar;
mysql> update user set password=PASSWORD(“newpassword”) where user=’facar’;
mysql> grant all privileges on testdb.* to facar;
mysql> grant all privileges on *.* to facar;
mysql> revoke all privileges on testdb.* from facar;
mysql> revoke all privileges on *.* from facar;
mysql> flush privileges;
mysql> grant usage on testdb.* to facar identified by ‘password’;
mysql> grant usage on *.* to facar identified by ‘password’;
mysql> revoke usage on testdb.* from facar
mysql> revoke usage on *.* from facar
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