How to install mysql in linux with practical

December 06, 2020 Add Comment Edit

Summary

In the previous tutorial we have learned about the introductionof mysql and advantages of mysql . In this article we will learn how to install mysql in linux operating system like centos ,oracle,redhat and ubuntu.

Prerequisite

To install mysql in linux system first we need to have a system with linux operating system. Now you can disable selinux as per your requirement and then configure yum repository to install mysql package from centralized yum repository. By default mysql runs on port no 3306. You can configure firewall on linux system as per your requirement.

Install mysql in linux

Now we have configured yum repository. First we need to verify that whether mysql server package is installed or not on the server using the below command.

#rpm –qa mysql*

If package is installed on the server you will get the output and you have nothing to do. If this package is not installed the server you need to install mysql package using you repository. Upto linux version 6.x mysql package is available in the yum repository . After 6.x or 7.x onward we can use mysql repository to install mysql server package.

To search mysql package it is available or not and which version will be available we can execute the below command.

#yum provides mysql*

Note: Mysql* means we can get all mysql related package from the yum repository.

To install mysql server in linux system(rhel,centos,oracle) we need to execute the below command.

#yum install mysql-server

How to install mysql in linux

Start mysql service in linux

After installation of mysql server we need to start the service and check the service status using the below commands.

[root@mysqldailytasks ~]# service mysqld start
Starting mysqld: [ OK ]
[root@mysqldailytasks ~]# service mysqld status
mysqld (pid 1547) is running...

To enable mysqld service at the startup mode i.e After reboot the physical server mysqld service will be automatically using the below command.

#chkconfig mysqld on

How to secure and set mysql root password

After completion above all steps we need to secure mysql root password using the below command.

[root@mysqldailytasks ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] n
... skipping.

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
... skipping.

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n
... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

How to connect mysql server from linux terminal

To connect mysql server from linux shell we need to execute the below command.

Mysql –u root –p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Check mysql version

To check mysql version we can execute the below query in mysql promt.

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.1.71 |

+-----------+

1 row in set (0.00 sec)

Conclusion

In this article we have learned how to install mysql in linux operating system like centos ,oracle,redhat and ubuntu,how to start the mysql service,how to secure root password for mysql,how to connect mysql from linux shell and finally how to check version of mysql server.

Introduction of Mysql and its features

December 05, 2020 Add Comment Edit

Summary

In this tutorial we will learn aboutintroduction of mysql. Also we will learn about what is database,type of database, what is mysql, advantages or we can sayfeatures of mysql.

What is database

Database is collection of datas which will stored in the organized manners in database and it will be easily accessed by database admin.

Lets take an example to explain database. Suppose in a school there will be many students and each student has name , age ,roll no so school authority collects each and every record from every student and store all datas into a database. Therefore we can access each and every students data in organized manner and if required we can view ,modify and delete the particular data from the database.

Type of database

Now we have learned what is database. Now we will learn type of database. There is two types of database one is relational database and other is no sql database.
Relational database will stored the data in structure manner and it will be easy to understand and easy to access. Relational database usesSQL (structured query languages). Example of the relational is oracle,mysql,postgresql.

Introduction of MySql

No SQL database will not stored data in structured manner . It will stored data in document format. It will no used SQL language.It can not be maintain any relation between table like relational database. Example of noSQL database is mongodb.

What is Mysql

MySQL is free and open source most popular and well known relational database management system all over the world. It is used for various web based software application.Mysql can be integrated with php,java and many others programming languages.Mysql has cross platform feature means we can use / install mysql server in windows,linux and mac operating system.

Advantage of Mysql

Mysql is a free and open source database . so that we can download mysql free.
Mysql is relational database management system because in one mysql database there will be many tables and each table has a relation with the other table.
Mysql supports cross platform because we can use mysql in the various operating system like Windows , linux and mac operating system.
Mysql stores data in the structured manner using sql language.
Mysql is very fast, reliable and scalable database . mysql can handle small site as well as large sites due to its scalability.
Mysql supports many programming languages like php,java and so on.
Mysql is very secure database.
Mysql database is used by many big companies like google,facebook etc.
Mysql supports roll back and commit operation.
Moreover mysql is easy to learn.

Conclusion

In this tutorial we have learned about introduction of mysql. Also we have learned about what is database,type of database, what is mysql, advantage or we can sayfeatures of mysql.

In next article we will learn how to install mysql in linux.

Grant all privileges on database in mysql

October 26, 2020 Add Comment Edit

Summary

In the previous article we have learned how to create a user in mysql and in this article we will learn how to grant all privileges on database in mysql.

Start to grant mysql privileges first we need to login our mysql server using the below command.

$mysql -u root -h localhost -p
Here -u switch indicates the user name and -h switch denotes the hostname and -p indicates the password.

grant all privileges on database in mysql

How to grant all privileges on database

We can restrict a user to operate by grant all privileges on database. Account privileges is very important task for a database admin to restrict a user to operate on database.Accidentally normal user can delete any records from the database. So a DBA should specify privileges to a normal user.

Case 1) suppose we have a normal database user techrideradmin and we want to give him full privileges by which this user can do any operational tasks on all databases. For this we will use grant all privileges statement.operational task means he or she can do any DML,DDL operation like insert,update ,delete any records from any database.
To set privileges on database we need to execute the below command.

mysql> grant all privileges on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

Query OK, 0 rows affected (0.00 sec)

In the above command first * denotes that List of all databaes and the second * denotes list of all table name for all databases . we are using * that means this user has full privileges (can access all databases and tables).

Localhost identified by password means we create mysql user in local server and we set password using identified by statement.

After executing above command we need to execute flush privileges command.

mysql>FLUSH PRIVILEGES;

Case 2) suppose we have one database that is techrideradmin and have one table on this database that is student . If we want to give permission for the particular database and table we need to execute the below command.

mysql> grant all privileges on techrideradmin.student to 'techrideradmin'@'localhost' identified by 'newpassword';

Query OK, 0 rows affected (0.01 sec)

Here we have grant all privileges on database techrideradmin on student table for techrideradmin user.

Case 3) If we want to allow this user for the remote host we can use the below command. Suppose my server ip address 192.168.137.5 and my workstation ip address 192.168.137.1 . we want to access the database using my workstation for do this we need to execute the below command.

mysql> grant all privileges on techrideradmin.student to 'techrideradmin'@'192.168.137.5' identified by 'newpassword';

Query OK, 0 rows affected (0.00 sec)

There are different type of privileges

1)ALL PRIVILEGES----Permit full permission to user.

mysql> grant all privileges on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant all privileges on database for all objects.

2)CREATE--------------Give permission to create database and table.

mysql> grant create on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant create privileges to create database and table of the existing database.

3)SELECT--------------Give permission to view the details of the table data.

mysql> grant select on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to create a readonly user in mysql and how to grant select privileges to view of all table data of any databases.

4)UPDATE--------------Give permission to update the data into the table

mysql> grant update on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant update privileges to update data into all tables of all databases.

5)INSERT---------------Give permission to insert data into table.

mysql> grant insert on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant insert privileges to insert data into all tables of all databases.

6)DELETE---------------Give permission to delete data from table.

mysql> grant delete on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant delete privileges to delete data into all tables of all databases.

7)DROP-----------------Give permission to drop table and database.

mysql> grant drop on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant drop privileges to drop any tables and databases.

Show grants for mysql user

Show grants command is used to find all provided grant information for a user.Here my username is techrideradmin and hostname is localhost so to view all grant info for that user using below command.

mysql> show grants for ‘techrideradmin’@’localhost’;

How to revoke privileges for MySQL user

If a user has leave from any organozation we need to revoke his or her privileges. To revoke the privileges for the mysql user we need to execute the below command.

mysql> revoke all privileges on *.* from 'techrideradmin'@'localhost' identified by 'newpassword';

Query OK, 0 rows affected (0.00 sec)

After executing the above command all privileges has been revoked from this user.

Conclusion

In this article we have learned how to grant all privileges on database in mysql.

How to use alter table in sql with query

October 18, 2020 Add Comment Edit

SUMMARY

In this tutorial we will learn about alter table in sql.

WHAT IS ALTER TABLE

Generally alter table is used to add column ,rename table ,drop column and modify data type of a column.Moreover any modification will be done after creating a table using alter table command.

PREREQUISITE

First we need to login our mysql server using the below command.

$mysql - u root -p

Now you need to create mysql database and table .Then check all database list and select our prefered database. To show database list we will use the below command.

mysql>show databases;

you select your database as per your requirement. In our case we select techrideradmin database . Then select the table list within the database. In our case we select department table. Now fetch all records form the table.

HOW TO USE ALTER TABLE WITH EXAMPLE

We have some queries about alter table in sql . These are given below.

1) Now we want to add city column into dept table ,we will use alter table statement .

mysql>alter table dept add city varchar (30);

Here it was an example of alter table add column .you can add multiple columns using alter table statement.

2) Now we want to drop city column uisng alter table statement .

mysql>alter table dept drop column city;

Here it was an example of drop column using alter table statement.

3) Now we want to rename dept table to department using alter table query.

mysql>alter table dept rename to department;

Using the above statement of alter table we can rename table name if it is required.

4) Now we can change datatype of SAL column from int to varchar using alter table query.

mysql>alter table dept modify column SAL varchar (10);

From the above statement of alter table we can modify datatype of particular column.

CONCLUSION

In this above article we have learned about alter table in sql.

How to use in operator in sql with example

October 17, 2020 Add Comment Edit

SUMMARY

In this article we will learn about in operator and not in operator in sql. Generally to filter data from any table we use where clause along with condition.When we are using more than one value to filter data from the database table then in operator plays role to solve this issue and the query has become simple.

PREREQUISITE

First we need to login our mysql server using the below command.

$mysql - u root -p

Now you need to create mysql database and table .Then check all database list and select our prefered database. To show database list we will use the below command.

mysql>show databases;

you select your database as per your requirement. In our case we select techrideradmin database . Then select the table list within the database. In our case we select dept table. Now fetch all records form the table.

WHAT IS IN OPERATOR

In case of in operator we will get those rows which we have provided within the in values along with where statement.

EXAMPLE OF IN OPERATOR

we will fetch those records which department nos belong to 1,3 and 5 using in operator.

mysql > select * from dept where deptno in (1,3,5);

WHAT IS NOT IN OPERATOR

In case of not in operator we will get those rows which is not provided within the not in operator.

EXAMPLE OF NOT IN OPERATOR

We will also fetch the records which dept nos are not belong to 1,3 and 5 using not in operator.

mysql > select * from dept where deptno not in (1,3,5);

Note: you can find the above video tutorial for reference.

CONCLUSION

we have learned about in operator in sql and not in operator in sql .

How to use Like operator in sql

October 17, 2020 Add Comment Edit

SUMMARY

In this video tutorial we will learn how to use Like operator in sql with wildcard character .

WHAT IS LIKE OPERATOR IN SQL

We use like operator for pattern search.There are two wildcard character " % " and " _ ".% is used to find from zero to any no of character._ is used to find for only one character.

First we need to login our mysql server . Then check all database list and select our preferred database.you select your database as per your requirement. In our case we select techrideradmin database . Then select the table list within the database. In our case we select department table. Now fetch all records form the table.

EXAMPLE OF LIKE OPERATOR IN SQL

We have solved some queries . You can see the video for reference.

1)Fetch the records whose first letter of ename is "A". For that purpose we use % wildcard .

mysql>Select * from department where ename like 'A%';

2)Fetch the records whose first two letters of ename is "AS".For that purpose we use % wildcard .

mysql>Select * from department where ename like 'AS%';

3)Fetch the records whose last letter of ename is "F".For that purpose we use % wildcard .

mysql>Select * from department where ename like '%F';

4)For any particular letter within the word we use _ character.

mysql > Select * from department where ename like 'AS_K';

Conclusion

In this video tutorial we have learned how to use Like operator in sql with wildcard character .

Display records if first character of name is same as last character in mysql

October 17, 2020 Add Comment Edit

Summary:

In this tutorial we will learn display records if first character of name is same as last character in mysql and also display records if first character of the first name is same as first character of the last name. To solve this we create one table in mysql and start our demonstration.

Prerequisite:

Now we are going to create one table named staff using the below query.

mysql> create table staff ( deptno int ,firstname varchar(20),lastname varchar(20),sal int);

Query OK, 0 rows affected (0.07 sec)

Now describe the staff table which we have just created.

Now we are going to insert some records into the staff table. To do this we need to execute the below query.

mysql> insert into staff (deptno,firstname,lastname,sal) values ( 1, 'ram','verma',2000);

Query OK, 1 row affected (0.00 sec)

mysql> insert into staff (deptno,firstname,lastname,sal) values ( 1, 'kartik','sharma',5000);

Query OK, 1 row affected (0.00 sec)

mysql> insert into staff (deptno,firstname,lastname,sal) values ( 1, 'kartik','khan',9000);

Query OK, 1 row affected (0.00 sec)

mysql> insert into staff (deptno,firstname,lastname,sal) values ( 1, 'aftab','ali',9000);

Query OK, 1 row affected (0.00 sec)

Display records if first character of name is same as last character in mysql

To display first any number of records we will use left() function and to display last any number of records we will use right () function.

Now we will fetch all records from our table using the below query.

mysql> select * from staff;

Now we want to display records if first character of name is same as last character in mysql using the below query.

mysql> select * from staff where left(firstname,1)=right(firstname,1);

Display records if first character of the first name is same as first character of the last name.

Now we are going to display records if first character of the first name is same as first character of the last name. To do this we need to execute the below query.

mysql> select * from staff where left(firstname,1)=left(lastname,1);

Conclusion

In this tutorial we have learneddisplay records if first character of name is same as last character in mysql and also display records if first character of the first name is same as first character of the last name.

How to select random values from a table and store it in another table in mysql

October 17, 2020 Add Comment Edit

Summary

In this tutorial we will learn how to select random values from a table and store it in another table in mysql.

To retrieve random values from a table we use rand() function in mysql. rand() function fetches random values from a table. Lets take an example.

How to select random values from a table

We have one table called department. Now we want to retrieve all records randomly we use rand() function.

mysql> select * from department order by rand() ;

Here order by clause is used to sort the randomly generated data.

Now we can place the limit to fetch particular no of rows like the below query;

mysql> select * from department order by rand() limit 3;

Here we place limit 3 to fetch 3 rows instead of 8 rows.

select random values from a table and store it in another table in mysql

Now we are going to store our randomly generated records to another table. we have department table from which we have fetched our random records. Now we will create another table named backupdept and in this table we are going to the fetched data.

Now to create table in mysql we need to execute the below query.

mysql> create table backupdept ( deptno int ,deptname varchar(20),ename varchar(20),sal int);

Query OK, 0 rows affected (0.03 sec)

To insert data from one table to another table we will use insert into select statement as below.

mysql> insert into backupdept (deptno,deptname,ename,sal) select * from department order by rand() ;

store data from one table to another table in mysql

Conclusion

In this tutorial we have learned how to select random values from a table and store it in another table in mysql.

How to use views in MySql with example

September 20, 2020 Add Comment Edit

In this tutorial we will learn what is views in mysql, what is the advantage of view, how to create view in mysql, how to update a view in mysql ,create view with check option and finally how to drop a view from mysql .

What is views in mysql

Views is an important task for a mysql database administrator. View is a logical table .We can perform insert ,update and delete operation using view. But when we execute one query on view ,the request goes to the physical table and requested operation will be perform there. Then the acknowledgement sent back to view to see the data of the table. Therefore performance will be degraded by using view in mysql.

What is the advantage of views in MySql

There are some advantages of view. These are following.

1) A complex query will become simple query using the view: When create a complex operation like join we can use view statement to do the query simple.

2) View provides extra layer of security of database: Suppose we have a table of department which contains many department name .We want to restrict one department data from other department we can do this using View. For this reason it provides extra layer of security.

3) We can restrict data for user: Suppose we have a table of department which contains many department name .We want to restrict one department data from other department we can do this using View

How to create view in MySql

Suppose we have one table named department and we want to restrict some data. We can create a view to do this task and the syntax is given below.

create view <view_name>

[select statement] [condition]

From the above syntax

1) create view is the keyword to create the view.

2) View_name is name of the view which to be created

3) We need to use “as” keyword

4) Then we need to enter our statement for which view will be created.

Now we want mechanical department can view only mechanical department data. We can execute the below query to do this task.

CREATE view view_mech as select * from dept where deptname=’MECH’;

Now we have created one view named view_mech. To see the view we can execute the below query. We can see only record of mechanical dept after execute the query.

Select * from view_mech;

How to update a view in MySql

If we need to update our view statement which is created the we need to use the “alter view” keyword. Syntax will be

alter view <view_name>

[update statement] [condition]

From the above syntax

1) alter view is the keyword to update the view.

2) View_name is name of the view which to be updated.

3) We need to use “as” keyword

4) Then we need to enter our statement for which view will be updated.

Create view in MySql with check option

We have created one view in the create view section to view,insert,update the record only mechanical department. If we insert one data for other department the data will be inserted.If we use “with check option” keyword then we will get an error. The syntax will be.

create view <view_name>

[select statement] [condition] with check option

We must enter “with check option” keyword to prevent the database. You can see demonstration at the video section .

Drop a view from MySql

We have created view in MySql . Now it is the time to drop the view from the MySql. Below is the syntax to drop the view.

Drop view <view_name>

From the above syntax

1) drop view is the keyword to drop the view.

2) View_name is name of the view which to be dropped.

Here we are going to drop view_mech using the below query.

drop view view_mech;

In this tutorial we have learned what is views in mysql, what is the advantage of view, how to create view in mysql, how to update a view in mysql ,create view with check option and finally how to drop a view from mysql .

How to use join query in MySql

September 19, 2020 Add Comment Edit

In this tutorial we will learn what is join , Join types in MySql, Join syntax in MySql,Join query in mysql ,Join tables in MySql.

What is join

Join is a query which can fetch or retrieve data from more than two tables. Join in MySql is very important task for a dba where we need details data from more than two tables. Then we can us join operation to fetch details data as per our requirement.

Join types in MySql

There are four types of join in MySql.

1.inner Join: Suppose we have two tables. Using the inner join we can retrieve data from these two tables which data are matched .

2.Left Join: Suppose we have two tables .Using Left join we can retrieve all data from the left table and matched data from the right table.

3.Right Join: Suppose we have two tables .Using Right join we can retrieve all data from the right table and matched data from the left table.

4.Cross Join: Suppose we have two tables ,one is employee table and other is department table. Department table has five records and employee table has three records. Using cross join Each row of the one table is join to every rows of the other table. After joining two tables we will get 15 records. Normally cross join is not used.

Join syntax in MySql

1) In case of inner join we can use the below syntax.

select * from <first table> inner join <second table> on first table.column name=second table.column name ;

2) In case of left join we can use the below syntax.

select * from <first table> left join <second table> on first table.column name=second table.column name ;

3) In case of right join we can use the below syntax.

select * from <first table> right join <second table> on first table.column name=second table.column name ;

4) In case of cross join we can use the below syntax.

select * from <first table> cross join <second table> ;

Join tables in MySql

Suppose we have two tables , ,one is employee table and other is department table. Department table has five records and employee table has three records. We are going to join two tables.

1) Incase of inner join query MySql we can execute the below join query to retrieve data from two tables.

select * from dept inner join emp on dept.deptno=emp.empdept ;

2) Incase of Left join query in MySql we can execute the below join query to retrieve data from two tables.

select * from dept left join emp on dept.deptno=emp.empdept ;

3) Incase of Right join query in MySql we can execute the below join query to retrieve data from two tables.

select * from dept right join emp on dept.deptno=emp.empdept ;

4) Incase of cross join query in MySql we can execute the below join query to retrieve data from two tables.

select * from dept cross join emp ;

In this tutorial we have learned what is join , Join types in MySql, Join syntax in MySql, Join tables in MySql.

Subscribe to: Posts ( Atom )