How to setup mysql on Linux

As mysql is widely used database since its open source ,most reliable & user friendly also. So that’s why I am included this topic about “How to setup mysql on Linux”.

1. Firstly to check whether mysql is installed or not on your system give command mysql. If the system responds with below error then mysql is not installed!!!!

[root@kalwa1 tmp]# mysql -bash: /usr/bin/mysql: No such file or directory
 [root@kalwa1 tmp]# 

If not proceed to step 2.

2. Now install mysql using yum. (Note you need to setup yum repository to work below command. )

[root@kalwa1 tmp]# yum install mysql*
Below is the snippet from the command output.
Running Transaction
Installing : mysql-libs-5.1.52-1.el6_0.1.x86_64 1/9
Installing : mysql-5.1.52-1.el6_0.1.x86_64 2/9
Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 3/9
Installing : mysql-server-5.1.52-1.el6_0.1.x86_64 4/9
Installing : mysql-test-5.1.52-1.el6_0.1.x86_64 5/9
Installing : mysql-bench-5.1.52-1.el6_0.1.x86_64 6/9
Installing : mysql-devel-5.1.52-1.el6_0.1.x86_64 7/9
Installing : mysql-connector-odbc-5.1.5r1144-7.el6.x86_64 8/9
Installing : 1:mysql-connector-java-5.1.12-2.el6.x86_64 9/9
Installed products updated.
Installed:
mysql.x86_64 0:5.1.52-1.el6_0.1
mysql-bench.x86_64 0:5.1.52-1.el6_0.1
mysql-connector-java.x86_64 1:5.1.12-2.el6 
mysql-connector-odbc.x86_64 0:5.1.5r1144-7.el6
mysql-devel.x86_64 0:5.1.52-1.el6_0.1 
mysql-libs.x86_64 0:5.1.52-1.el6_0.1 
mysql-server.x86_64 0:5.1.52-1.el6_0.1 
mysql-test.x86_64 0:5.1.52-1.el6_0.1
Dependency Installed:
 perl-DBD-MySQL.x86_64 0:4.013-3.el6
Complete!

3. Now check the version of mysql on system.

[root@kalwa1 tmp]# rpm -qa | grep -i mysql
perl-DBD-MySQL-4.013-3.el6.x86_64
mysql-devel-5.1.52-1.el6_0.1.x86_64
mysql-5.1.52-1.el6_0.1.x86_64
mysql-server-5.1.52-1.el6_0.1.x86_64
mysql-bench-5.1.52-1.el6_0.1.x86_64
mysql-connector-odbc-5.1.5r1144-7.el6.x86_64
mysql-libs-5.1.52-1.el6_0.1.x86_64
mysql-test-5.1.52-1.el6_0.1.x86_64
mysql-connector-java-5.1.12-2.el6.x86_64

4. Start the mysql services.

[root@kalwa1 tmp]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@kalwa1 tmp]#

5. To check port for mysql.

[root@kalwa1 tmp]# cat /etc/services | grep -i mysql |more
mysql 3306/tcp # MySQL
mysql 3306/udp # MySQL
mysql-cluster 1186/tcp # MySQL Cluster Manager
mysql-cluster 1186/udp # MySQL Cluster Manager
mysql-cm-agent 1862/tcp # MySQL Cluster Manager Agent
mysql-cm-agent 1862/udp # MySQL Cluster Manager Agent
mysql-im 2273/tcp # MySQL Instance Manager
mysql-im 2273/udp # MySQL Instance Manager
mysql-proxy 6446/tcp # MySQL Proxy
mysql-proxy 6446/udp # MySQL Proxy
sphinxql 9306/tcp # Sphinx search server (MySQL listener)
[root@kalwa1 tmp]#

6. Now go into mysql prompt for using mysql command

[root@kalwa1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.52 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

7. To check the current databases give below command in mysql prompt.

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql>

8. For creating new database give below command.

mysql> CREATE DATABASE uxtechno;
Query OK, 1 row affected (0.00 sec)

9. For verification once again fire SHOW DATABASES command.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| uxtechno           |
+--------------------+
4 rows in set (0.00 sec)
mysql>

10. To use a database:

mysql> USE uxtechno
Database changed
mysql>

11. To check if uxtechno database is selected:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| uxtechno   |
+------------+
1 row in set (0.00 sec)
mysql>

12. To create tables in uxtechno database:

mysql> CREATE TABLE student (name varchar(20), age int(3));
Query OK, 0 rows affected (0.00 sec)
mysql>

  mysql>DESCRIBE student;

14. To insert values into a table “eg: student “:

mysql> INSERT INTO student VALUES("Manmohan", "32" );
Query OK, 1 row affected (0.00 sec)
mysql>

mysql> SELECT * FROM student;

16. After adding few entries.

17. To select a field based on certain parameters:

mysql> SELECT Name FROM student WHERE age > 30;

18. To update a field in a table:

mysql> UPDATE student SET Name="Atul" where age=31;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>

In below entry for Atul name got updated.

19. To delete a certain field:

mysql> DELETE FROM student WHERE age=30;
Query OK, 1 row affected (0.00 sec)
mysql>

now check with select statement;

20. Now to quit the menu use command “\q” in mysql prompt.

mysql> \q
Bye
[root@kalwa1 ~]#