Install the MYSQL along with the InnoDB plugin

Introduction

This blog represent the procedure to install the MYSQL along with the InnoDB plugin.

InnoDB can be install from SQL INSTALL command or by configuring my.cnf, here we are using my.cnf to load and configure InnoDB plugin.

NOTE: If you already have MYSQL install and just want to install the InnoDB plugin please don't follow these steps.

Download MYSQL

Download the MYSQL packages using below linux command

Note : User must have download rights to download these packages

MySQL-client-community :- The standard MySQL client programs. You probably always want to install this package.

wget http://downloads.skysql.com/archives/mysql-5.1/MySQL-client-community-5.1.54-1.rhel5.x86_64.rpm

MySQL-server-community :- The MySQL server. You need this unless you only want to connect to a MySQL server running on another machine.

wget http://downloads.skysql.com/archives/mysql-5.1/MySQL-server-community-5.1.54-1.rhel5.x86_64.rpm

Create or Modify before mysqlserver is installed /etc/my.cnf

Below is the standard MYSQL my.cnf configuration referred from QA database server

[mysqld] configuration

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

#Default to using old password format for compatibility with mysql 3.x

#clients (those using the mysqlclient10 compatibility package).

#you can/should enable old passwords by setting old_passwords=1 else comment it

old_passwords=1

#Max no of clients to connect

max_connections=750

#unique id for the server

server-id=1

#Innodb configuration

innodb_flush_method=O_DIRECT

#set these value as per memory available

innodb_buffer_pool_size=6G

innodb_flush_log_at_trx_commit=1

#set these value as per memory available

innodb_log_file_size=64M

innodb_log_files_in_group=2

ignore_builtin_innodb

#Be careful while copying.There should be no space

plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

default-storage-engine=InnoDB

[mysql.server] configuration

[mysql.server]

user=mysql

[mysqld_safe] configuration

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

Installation

Install it using below command

rpm -ivh MySQL-client-community-5.1.54-1.rhel5.x86_64.rpm

rpm -ivh MySQL-server-community-5.1.54-1.rhel5.x86_64.rpm

[edit]

Set The Password

Once you have install all the package's please remember to set password for the MySQL root USER ! To do so, issue the below linux commands:

/usr/bin/mysqladmin -u root password 'new-password'

Note: above mysqladmin command will execute only when mysql server is up, here installation of MySQL-server-community-5.1.54-1.rhel5.x86_64.rpm package will start the server. if its not running then start the server using command

/etc/init.d/mysql start

Create User

execute below commands on Mysql

Mysql>create user 'userName'@'hostName' identified by 'password';

Mysql>GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW ON databaseName.* TO 'userName'@'hostName'

Note: hostName here is the remote(app server) machine's host name