Sunday, December 22, 2013

MySQL User Management

User management is the process of controlling which users are allowed to connect to the MySQL server and what permissions they have on each database.

Start the mysql server

# /etc/init.d/mysql start      or  # service mysql start   or # mysqld_safe –user=”mysql” &

Login into mysql database

[root@maxisdb1 mysql]# mysql -proot -u root
mysql>\q
mysql> show engines;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql>use mysql;
mysql> show tables;
mysql> select user,host from user;
Mysql default port number is 3306
Mysql default storage engine is “MyIsam”(Up to 5.5)   “Innodb”(From 5.5)


SQL Commands:

Data Definition Language (DDL)
CREATE,ALTER,DROP and TRUNCATE
Data Manipulation Language (DML)
INSERT, UPDATE, and DELETE.
Data query Language (DML)
SELECT
Transaction Control Language (TCL)
COMMIT, ROLLBACK, and SAVEPOINT.
Data Control Language (DCL)
GRANT and REVOKE.

The GRANT statement grants privileges to MySQL user accounts.

Creating a User  with Full Privileges to all databases:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'drk'@'localhost' IDENTIFIED BY 'drk' WITH GRANT OPTION;

Creating a User  with Full Privileges to a single database:
mysql> GRANT ALL PRIVILEGES ON drkdb.* TO 'drk'@'localhost' IDENTIFIED BY 'drk' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'drk'@'%' IDENTIFIED BY 'drk' WITH GRANT OPTION;
Using the WITH GRANT OPTION allows the account to add user accounts to the specified databases.
Creating a User Account with Full Privileges on All DATABASES:

Creating Specific Privileges to a User on one database:
GRANT SELECT, INSERT, DELETE, UPDATE, EXECUTE ON test.* TO 'username'@'192.168.0.3';


To check the privileges for an account, use SHOW GRANTS:
mysql> SHOW GRANTS FOR 'drk'@'localhost';

Removing Users:
USE mysql;
DELETE FROM user WHERE user = 'username' AND host = 'hostname';

As an alternative to GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables:


An example of how to Create a User in MySQL using the INSERT statement


INSERT INTO user (Host,User,Password) VALUES('localhost','my_user',PASSWORD('my_pass'));
If you want to add some privileges with the same command the query will look like this:
An example of how to create a user with INSERT and SELECT privileges by editing the 'mysql.user' table


INSERT INTO user (Host,User,Password,Select_priv,Insert_priv)
VALUES('localhost','my_user',PASSWORD('my_pass'),'Y','Y');

mysql> INSERT INTO user VALUES('localhost','drk',PASSWORD('drk'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','drk',PASSWORD('drk'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin', Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;

The REVOKE statement enables system administrators to revoke privileges from MySQL accounts.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM drk@localhost;
REVOKE INSERT ON *.* FROM 'drk'@'183.82.96.216';

update the password
mysql>update user set passowrd=PASSWORD('drk') where user='drk';
#flush privilges

MySQL Instalaltion



Mysql having three types of instalaltion:

1) rpm                     2) generic binaries               3) source distribution


Mysql server is available in two options

MySQL Community Edition &  MySQL Enterprise Edition


1) rpm instalaltion


# rpm -ivh mysql-5.5.25-linux2.6-i686.rpm

i= install  v=verify/verbose information h=hash sysmbol

MySQL Installation Layout for Linux RPM
Directory
Contents of Directory
/usr/bin
Client programs and scripts
/usr/sbin
The mysqld server
/var/lib/mysql
Log files, databases
/usr/share/info
Manual in Info format
/usr/share/man
Unix manual pages
/usr/include/mysql
Include (header) files
/usr/lib/mysql
Libraries
/usr/share/mysql
Miscellaneous support files, including error messages, character set files, sample configuration files, SQL for database installation


[root@maxisdb1 mysql]# scp /usr/share/mysql/my-medium.cnf /etc/my.cnf

[root@maxisdb1 mysql]# /etc/init.d/mysql start/stop/restart
or
[root@maxisdb1 mysql]#  /usr/share/mysql/mysql.server start
/stop/restart

or
[root@maxisdb1 mysql]# /usr/bin/mysqld_safe --user=mysql &


or
[root@maxisdb1 ~]# service mysql start/stop/restart

If any problem occurs see process id's

[root@maxisdb1 ~]# ps -ef | grep "mysql"

then kill the process id's

[root@maxisdb1 ~]# kill -9 3524 3534 4482 4562 4576 4641 

#mysql -proot -uroot

to update the password using mysqladmin
#mysqladmin -u root -p “root”
or
mysql>update user set password=PASSWORD(“root”) where user=”root”;

to see the status

[root@maxisdb1 ~]# mysqladmin -proot -uroot status
Uptime: 256  Threads: 1  Questions: 6  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: %

to see the status variables
[root@maxisdb1 ~]# mysqladmin -proot -uroot status variables

2) Generic binaries installation

################################################################################
mysql-5.5.25-linux2.6-i686.tar.gz generic binary installation on redhat
################################################################################
step 1) Downlaod the above package from http://dev.mysql.com/downloads/mysql/
step 2) # groupadd mysql
          # useradd -r -g mysql mysql
          # scp mysql-5.5.25-linux2.6-i686.tar.gz /usr/local
           # cd /usr/local
           # tar zxvf mysql-5.5.25-linux2.6-i686.tar.gz
          # mv mysql-5.5.25-linux2.6-i686 mysql
          # cd mysql
          # chown -R mysql .
          # chgrp -R mysql .
          #  ./scripts/mysql_install_db (--user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data )
          # chown -R root .
          # chown -R mysql data
         
        Next command is optional
          # cp support-files/my-medium.cnf /etc/my.cnf
          # cp support-files/mysql.server /etc/init.d/mysql


          start the mysql server


          # bin/mysqld_safe --user=mysql &
                   
                      or    
          #/etc/init.d/mysql start
                   
                    or
          #support-files/mysql.server start
          #cd /usr/local/mysql/bin
          #mv * /usr/bin
          #cd ../
          #rm -fr /usr/local/mysql/bin
          #ln -s /usr/bin /usr/local/mysql/bin



before going to binary instalaltion we have to remove all pre installation directories and
#cd /usr/local
#rm -rf mysql*

#cd /usr/share
#rm -rf mysql

#cd /varlib/
#rm -rf mysql

#cd /etc/
#rm my.cnf
#cd init.d
#rm mysql/mysqld

MySQL Installation Layout for Generic Unix/Linux Binary Package
Directory
Contents of Directory
bin
Client programs and the mysqld server
data
Log files, databases
docs
Manual in Info format
man
Unix manual pages
include
Include (header) files
lib
Libraries
scripts
share
Miscellaneous support files, including error messages, sample configuration files, SQL for database installation
Supported-files
Configuration files and mysql start up scripts




2) source distribution  installation

mysql-5.5.25-linux2.6-i686.tar.gz source distribution installation on redhat


step 1) Downlaod the above package from http://dev.mysql.com/downloads/mysql/
step 2) # groupadd mysql
# useradd -r -g mysql mysql
# scp mysql-5.5.25-linux2.6-i686.tar.gz /usr/local
# cd /usr/local
# tar zxvf mysql-5.5.25-linux2.6-i686.tar.gz
# ln -s mysql-5.5.25-linux2.6-i686 mysql
# ./configure --prefix=/usr/local/mysql
# make
# make install
# cd /usr/local/mysql
#chown -R mysql .
# chgrp -R mysql .
# bin/mysql_install_db –user=mysql
or
#  ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
#chown -R root .
#chown -R mysql var
Next command is optional
          # cp support-files/my-medium.cnf /etc/my.cnf
          # cp support-files/mysql.server /etc/init.d/mysql


          start the mysql server


          # bin/mysqld_safe --user=mysql &
                   
                      or    
          #/etc/init.d/mysql start
                   
                    or
          #support-files/mysql.server start
          #cd /usr/local/mysql/bin
          #mv * /usr/bin
          #cd ../
          #rm -fr /usr/local/mysql/bin
          #ln -s /usr/bin /usr/local/mysql/bin



before going to souce distribution installation we have to remove all pre installation directories and
#cd /usr/local
#rm -rf mysql*

#cd /usr/share
#rm -rf mysql

#cd /varlib/
#rm -rf mysql

#cd /etc/
#rm my.cnf
#cd init.d
#rm mysql/mysqld

MySQL Layout for Installation from Source
Directory
Contents of Directory
bin
Client programs and scripts
include/mysql
Include (header) files
Docs
Manual in Info format
man
Unix manual pages
lib/mysql
Libraries
libexec
The mysqld server
share/mysql
Miscellaneous support files, including error messages, sample configuration files, SQL for database installation
sql-bench
Benchmarks
var
Log files, databases





******************************************************************************
mysql-5.1.77 source isntallation from mysql-5.1.77.tar.gz
******************************************************************************
Downlaod the above package from 
http://dev.mysql.com/downloads/mysql/
# yum install gcc gcc-c++
# yum install binutils
# groupadd mysql
# useradd -r -g mysql mysql
# scp mysql-5.1.77.tar.gz /usr/local
# cd /usr/local
# tar zxvf mysql-5.1.77.tar.gz
# ./configure --prefix=/usr/local/mysql (--enable-profiling --with-plugin=partition,federated,innodb_plugin,csv,blackhole)
# chown -R mysql .
# chgrp -R mysql .
# make
# make isntall
# scripts/mysql_install_db --user=mysql
# chown -R root .
# chown -R mysql data
# mysqld_safe --user=mysql &



mysql-5.5.25 source installation from mysql-5.5.25.tar.gz

http://www.hellotechblog.com/2012/02/building-mysql-5520-on-centos-57-from.html

Downlaod the above package from 
http://dev.mysql.com/downloads/mysql/
# yum install cmake
# yum install ncurses-devel
# yum install gcc-c++
# yum install binutils
# groupadd mysql
# useradd -r -g mysql mysql
# scp mysql-5.5.25.tar.gz /usr/local
# cd /usr/local
# chown -R mysql .
# chgrp -R mysql .
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql (-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DMYSQL_DATADIR=/usr/local/mysql/data/  -DINSTALL_LAYOUT=STANDALONE -DENABLED_PROFILING=ON -DMYSQL_MAINTAINER_MODE=OFF
-DWITH_DEBUG=OFF)
# make
# make install
# cd /usr/local/mysql
# scripts/mysql_install_db --user=mysql
# chown -R root .
# chown -R mysql data
# ./bin/mysqld_safe --user=mysql &
# ./bin/mysql_secure_installation