We can check how to install mysql db in a linux machine
All files of mysql including DB are stored in /var/lib/mysql/
All configuration files are in /etc/my.cnf
Install the package using yum
# yum install mysql mysql-server
And then start the mysql server
# service mysqld start
Here no root password set for the DB so any one can access the DB command
# mysql
For securing it set a root password for mysql
# mysqladmin -u root password ‘password’
this will set a root password for mysql. If you want to update the mysql root password the do the following
# mysqladmin -u root -p’oldpassword’ password ‘newpassword’
Then to enter into mysql database run
# mysql -u root -p
enter password:
or we can specify password after -p option but it will be visible to all
Now your mysql data base is ready for operation with a default port of 3306.
Some basic mysql queries are
To list all databases on the mysql sql server
mysql> show databases;
Create a database on the sql server
mysql> create database [databasename];
To use a database
mysql> use dbname;
To create a table in the database
mysql> create table tablename(name varchar(20),dob int(20));
To see all the tables in the db
mysql> show tables;
To drop a particular table
mysql> drop table tablename;
To see the structure of a particular table
mysql> describe employee;
mysql> show columns from table-name;
Show all data in a table
mysql> SELECT * FROM [table name];
Granting Database permission to a user, * will give all default permissions
mysql> grant usage on *.* to ‘ctechz’@’localhost’ identified by ‘ctechz123’;
Granting particular permission to users,
mysql> grant select,insert,update,delete,create,drop on TestDb.* to ‘ctechz’@’localhost’ identified by ‘ctechz123’;
Creating a user to access db
mysql> create user ctechz identified by ‘ctechz123’;
To change the password for a user
mysql> set password for ‘ctechz’@’localhost’=password(‘ctechz123’);
mysql> flush privileges;
Granting permission from any host
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’%’ WITH GRANT OPTION;
% means for all host
We can get the mysql history file from
# vim .mysql_history
from the above file we can get the commands that we run the mysql bd.
If you want to connect to a remote mysql server from your machine
# mysql -uroot -p -h 192.168.1.245
Granting permission to access the database from a particular Ip
# grant all privileges on *.* to ‘root’@’192.168.1.67’ identified by ‘mysql1’;
# flush privileges;
Recent Comments