mysql

Mysql

These are the rpms need to install mysql

[root@localhost ~]# rpm -qa |grep -i mysql
mysql-5.0.45-4.fc8
php-mysql-5.2.4-3
mysql-libs-5.0.45-4.fc8
mysql-server-5.0.45-4.fc8
perl-DBD-MySQL-4.005-2.fc8.1

There are 2 default account in mysql

1: root -- permits full access to mysql database
2: anonymous -- permits only access to test database


Note: 1: all the commnad in mysql ends with semicolon(;)
2: In its default state connection can only be made from localhost
3: Mysql security is not contigent upon OS security


Some important commands

1: mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.05 sec)

2: mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

3: mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2008-07-29 13:21:28 |
+---------------------+
1 row in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.00 sec)

2 ways to set root password

1: mysqladmin -u root password 'r00t'
2: set password for 'root'@'localhost'=password('abc123')


Removing anonymous user

mysql> delete from user where user=' ';

mysql> flush privileges; (To re-read the grant tables)
Query OK, 0 rows affected (0.00 sec)

Grant Table

There are3 main grant table

1:mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| | db |
| host |
| user |
+---------------------------+


mysql> desc user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | | |
| x509_issuer | blob | NO | | | |
| x509_subject | blob | NO | | | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.01 sec)


mysql> select user,host,password from user;
+----------+-----------------------+------------------+
| user | host | password |
+----------+-----------------------+------------------+
| root | localhost | 39aa9eb5065367ed |
| root | localhost.localdomain | 4b5698aa4603595b |
| root | 127.0.0.1 | 4b5698aa4603595b |
| prashant | localhost | 4b5698aa4603595b |
| prashant | % | 4b5698aa4603595b |
+----------+-----------------------+------------------+
5 rows in set (0.00 sec)


How to create user in mysql

Step1: mysql> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '39aa9eb5065367ed' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Step2: mysql> GRANT ALL PRIVILEGES ON *.* TO 'pankaj'@'localhost' IDENTIFIED BY 'panku' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;
+----------+-----------------------+
| user | host |
+----------+-----------------------+
| prashant | % |
| root | 127.0.0.1 |
| pankaj | localhost |
| prashant | localhost |
| root | localhost |
| root | localhost.localdomain |
+----------+-----------------------+
6 rows in set (0.00 sec)