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

Xtrabackup Installation and Restore For MYSQL + INNODB

Installation

a) Using Percona Software Repositories

Run below command. This will create the Percona YUM repository

sudo rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Testing the Repository: This will display the repository list

sudo yum list | grep percona

Check the Xtrabackup install or not

rpm -qi xtrabackup

If it is not installed then install using below command and verify again using above command

sudo yum install xtrabackup

b) Using Generic .tar.gz binary packages

If you don’t want to install using Percona YUM repository use the below link to download ready to use Xtrabackup tool

http://www.percona.com/downloads/XtraBackup/XtraBackup-1.5/Linux/binary/

Note: - For Innodb database backup you must have innodb_file_per_table set in my.cnf.

Creating Backup

sudo ./innobackupex-1.5.1 --user=username --password=password /path/to/save/backup --databases=”databaseName”

Note: - the user should have the read/write access to the both data and target folder, also check .frm file copied or not in database backup folder

Backup Completes with following statements:

120209 01:57:22 innobackupex-1.5.1: completed OK!

Restore Backup

Prepare using binary package

Sudo ./innobackupex-1.5.1 --apply-log /path/to/save/backup/timestamp_folder

Copy Data directory

You could simply use cp to copy the files, also make sure the data directory and files are owned by the mysql user.

Stop the server and then run the below copy command

cp -R /path/to/save/backup/timestamp_folder/* /var/lib/mysql/

And run the below command

sudo chown –R mysql:mysql /var/lib/mysql

Start the server and check the database

Reference:

http://www.percona.com/doc/percona-xtrabackup/howtos/recipes_xbk_restore.html

http://agiletesting.blogspot.com/2010/09/mysql-innodb-hot-backups-and-restores.html

http://www.ovaistariq.net/590/on-hot-backups-and-restore-using-xtrabackup/

INNODB PLUG-IN INSTALLATION & SET UP

INNODB PLUG-IN INSTALLATION & SET UP

Step 1)

If you already have the MYSQL installed but don’t have innodb plugin on machine please stop it and remove it by

a) Executing command #rpm -qa | grep -i mysql (will list all package)

Ex.

mysql-libs-5.0.45-6.fc8

mysql-server-5.0.45-6.fc8

mysql-5.0.45-6.fc8

perl-DBD-MySQL-4.005-2.fc8.1

b) Remove this by executing #rpm –e package_name

Ex. #rpm –e mysql-libs-5.0.45-6.fc8

c) If you got “specifies multiple packages error then use for those package” error then execute below command

rpm -aq | grep mysql | xargs rpm -e --nodeps –allmatches

d) Execute command #find / -name mysql this will show you all remaining

Installed folder. Remove this using command #rm -fr mysql

Step 2)

a) Download the below packages from

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

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

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

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

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

b) Install it using

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

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

rpm -ivh MySQL-shared-compat-5.1.54-1.rhel5.x86_64.rpm

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

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

c) Open file /etc/my.cnf. If it exists with some other name like

/etc/my.cnf.rpmsave then rename it with my.cnf

Ex. #mv /etc/my.cnf.rpmsave /etc/my.cnf

d) Add the below settings into my.cnf

ignore_builtin_innodb

default-storage-engine=InnoDB

plugin- load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;

innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plug in.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_p lugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha _innodb_plugin.s

e) Restart the server # /etc/init.d/mysql restart

f) Change password /usr/bin/mysqladmin -u root password '****'

Step 3) execute below commands

INSTALL PLUGIN INNODB_TRX SONAME 'ha_innodb_plugin.so';

INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb_plugin.so';

INSTALL PLUGIN INNODB_LOCK_WAITS SONAME 'ha_innodb_plugin.so';

INSTALL PLUGIN INNODB_CMP SONAME 'ha_innodb_plugin.so';

INSTALL PLUGIN INNODB_CMP_RESET SONAME 'ha_innodb_plugin.so';

INSTALL PLUGIN INNODB_CMPMEM SONAME 'ha_innodb_plugin.so';

INSTALL PLUGIN INNODB_CMPMEM_RESET SONAME 'ha_innodb_plugin.so';

Step 4) Test the plug-in install or not by

a) execute mysql> show plugins \G. This will display all the install plugins

How to set default engine to INNODB

Step 1) this can be done by

a) Setting default-storage-engine=InnoDB in my.cnf under under [mysqld]

Or

b) Executing SET storage_engine=InnoDB;

c) To test it, execute the command mysql> show engines \G

This will display below output for InnoDB.

Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

XA: YES

Savepoints: YES

How to Un-install the INNODB plug-in

Step 1) execute the below Mysql commands

SET GLOBAL innodb_fast_shutdown=0;

UNINSTALL PLUGIN INNODB;

UNINSTALL PLUGIN INNODB_CMP;

UNINSTALL PLUGIN INNODB_CMP_RESET;

UNINSTALL PLUGIN INNODB_CMPMEM;

UNINSTALL PLUGIN INNODB_CMPMEM_RESET;

UNINSTALL PLUGIN INNODB_TRX;

UNINSTALL PLUGIN INNODB_LOCKS;

UNINSTALL PLUGIN INNODB_LOCK_WAITS;

And remove the entry default-storage-engine=InnoDB from my.cnf