MYSQL Installation and Queries Linux(Cent OS)

Initially Login to the shell  using username and password:

Installation of Mysql in centos:  Yum install mysql  -y              

Checking the Status of Mysql:   service mysqld status ---> It gives either running or stopped if service installed otherwise unknown service.

 Commands to start, stop and restart the service are given below.
  service mysqld start      ----->   Start service
  service mysqld stop       ----->   Stop service
  service mysqld restart   ----->   Start service

The directory paths of Mysql:  find / -name mysql ---> It gives the all directories.
 
 /var/lib/mysql --> location of mysql storage.
 /etc/rc.d/init.d/mysqld ---> Initialization script for mysql to auto load whenever server is up.
 /var/run/mysqld  ----> socket running directory
 /usr/bin/mysql ---> bin directory for mysql
/var/log/mysqld.log --> default error log file location

Connect to Mysql:   mysql -uusername -ppassword
 default username: root                                  password:  no password(password empty)

Root Password setting:
1.Set root password from linux console:   mysqladmin -uusername -p  password 'newpassword'
it prompts enter current password:

2.After Connecting to Mysql using root as username:

mysql -u root -p
Enter password:(just press 'enter')
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 150
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> select user,host,password from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | password                                  |
+---------------+-----------+-------------------------------------------+
| root          | localhost |                                           |
| root          | centos6.2 |                                           |
| root          | 127.0.0.1 |                                           |
|               | localhost |                                           |
|               | centos6.2 |                                           |
+---------------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> set password for root@'localhost'=password('password');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@'127.0.0.1'=password('password');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@'centos6.2'=password('password');
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | password                                  |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root          | centos6.2 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root          | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
|               | localhost |                                           |
|               | centos6.2 |                                           |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

Create New Users and Assign Permissions:

1. mysql> create user 'guru'@'localhost';
Query OK, 0 rows affected (0.03 sec)

  mysql> set password for 'guru'@'localhost'=password('saranam');
Query OK, 0 rows affected (0.01 sec)

Create a database and give permission to this  user:

mysql> create database   Data_Storage;
Query OK, 1 row affected (0.00 sec)

mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on Data_Storage.* to 'guru'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> exit;
Bye
Login with this user and check permissions to database. 

2. mysql> create user 'guru'@'localhost'IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.00 sec)

 mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on Data_Storage.* to 'guru'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
3.Avoid plain text password:

CREATE USER 'guru'@'localhost'
IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';

4.  Using Grant directly we can create user and assign permissions

 mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on Data_Storage.* to 'tester'@'localhost' identified by 'testerpassword'; 

Query OK, 0 rows affected (0.00 sec) 

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 To give all permissions using Grant see the below query.

mysql> grant all on Data_Storage.* to 'tester2'@'localhost' identified by 'testerpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


To give permission to all databases
mysql> grant all on *.* to 'tester2'@'localhost' identified by 'testerpassword';
Query OK, 0 rows affected (0.00 sec)