InnoDB: Unable to lock ./ibdata1, error: 11

 While trying to start MySQL server  (using /etc/init.d/mysql start), I got the following error,

141024 14:43:40 mysqld_safe Starting mysqld.bin daemon with databases from /opt/bitnami/mysql/data

141024 14:43:40 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.

141024 14:43:40 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.

141024 14:43:40 [Note] Plugin 'FEDERATED' is disabled.

141024 14:43:40 InnoDB: The InnoDB memory heap is disabled

141024 14:43:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins

141024 14:43:40 InnoDB: Compressed tables use zlib 1.2.3

141024 14:43:40 InnoDB: Using Linux native AIO

141024 14:43:40 InnoDB: Initializing buffer pool, size = 8.0G

141024 14:43:41 InnoDB: Completed initialization of buffer pool

InnoDB: Unable to lock ./ibdata1, error: 11

InnoDB: Check that you do not already have another mysqld process

InnoDB: using the same InnoDB data or log files.

141024 14:43:41  InnoDB: Retrying to lock the first data file

InnoDB: Unable to lock ./ibdata1, error: 11

InnoDB: Check that you do not already have another mysqld process

InnoDB: using the same InnoDB data or log files.

------

------

------

InnoDB: Unable to lock ./ibdata1, error: 11

InnoDB: Check that you do not already have another mysqld process

InnoDB: using the same InnoDB data or log files.

InnoDB: Unable to lock ./ibdata1, error: 11

InnoDB: Check that you do not already have another mysqld process

InnoDB: using the same InnoDB data or log files.

141024 14:45:21  InnoDB: Unable to open the first data file

InnoDB: Error in opening ./ibdata1

141024 14:45:21  InnoDB: Operating system error number 11 in a file operation.

InnoDB: Error number 11 means 'Resource temporarily unavailable'.

InnoDB: Some operating system error numbers are described at

InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html

141024 14:45:21 InnoDB: Could not open or create data files.

141024 14:45:21 InnoDB: If you tried to add new data files, and it failed here,

141024 14:45:21 InnoDB: you should now edit innodb_data_file_path in my.cnf back

141024 14:45:21 InnoDB: to what it was, and remove the new ibdata files InnoDB created

141024 14:45:21 InnoDB: in this failed attempt. InnoDB only wrote those files full of

141024 14:45:21 InnoDB: zeros, but did not yet use them in any way. But be careful: do not

141024 14:45:21 InnoDB: remove old data files which contain your precious data!

141024 14:45:21 [ERROR] Plugin 'InnoDB' init function returned error.

141024 14:45:21 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

141024 14:45:21 [ERROR] Unknown/unsupported storage engine: InnoDB

141024 14:45:21 [ERROR] Aborting

141024 14:45:21 [Note] /opt/bitnami/mysql/bin/mysqld.bin: Shutdown complete

From the error log it is found that already there is an MySQL running in the same server with same port.

You can check the current running MySQL by,

ps –ef | grep mysql

root@localhost:/opt/bitnami/mysql/data# ps -ef | grep mysql

root     25478 20976  0 14:25 pts/2    00:00:00 /opt/bitnami/mysql/bin/mysql.bin --defaults-file=/opt/bitnami/mysql/my.cnf -u dba –p

root     25555     1  0 14:26 ?        00:00:00 /bin/sh /opt/bitnami/mysql/scripts/ctl.sh start

root     25557 25555  0 14:26 ?        00:00:00 /bin/sh /opt/bitnami/mysql/bin/mysqld_safe --defaults-file=/opt/bitnami/mysql/my.cnf --port=3306 --socket=/opt/bitnami/mysql/tmp/mysql.sock --datadir=/opt/bitnami/mysql/data --log-error=/opt/bitnami/mysql/data/mysqld.log --pid-file=/opt/bitnami/mysql/data/mysqld.pid --lower-case-table-names=1

mysql    25929 25557  1 14:26 ?        00:00:00 /opt/bitnami/mysql/bin/mysqld.bin --defaults-file=/opt/bitnami/mysql/my.cnf --basedir=/opt/bitnami/mysql --datadir=/opt/bitnami/mysql/data --plugin-dir=/opt/bitnami/mysql/lib/plugin --user=mysql --lower-case-table-names=1 --log-error=/opt/bitnami/mysql/data/mysqld.log --pid-file=/opt/bitnami/mysql/data/mysqld.pid --socket=/opt/bitnami/mysql/tmp/mysql.sock --port=3306

root     25979 20976  0 14:27 pts/2    00:00:00 /bin/sh /etc/init.d/mysql restart

root     26025 25979  0 14:27 pts/2    00:00:00 /bin/sh /etc/init.d/mysql start

root     26038 26025  1 14:27 pts/2    00:00:00 /bin/sh /opt/bitnami/mysql/bin/mysqld_safe --datadir=/opt/bitnami/mysql/data --pid-file=/opt/bitnami/mysql/data/ip-172-31-59-38.pid

mysql    26345 26038 38 14:27 pts/2    00:00:00 /opt/bitnami/mysql/bin/mysqld.bin --basedir=/opt/bitnami/mysql --datadir=/opt/bitnami/mysql/data --plugin-dir=/opt/bitnami/mysql/lib/plugin --user=mysql --log-error=/opt/bitnami/mysql/data/ip-172-31-59-38.err --pid-file=/opt/bitnami/mysql/data/ip-172-31-59-38.pid --socket=/opt/bitnami/mysql/tmp/mysql.sock --port=3306

root     26349 22899  0 14:27 pts/0    00:00:00 grep --color=auto mysql

Here you could see there are two MySQL running’s with same base and data directories. But the error log path is mentioned different for both.

It was due to a script which made mysql to start automatically if MySQL is not running.

Solved by  killing all the running MySQL process and removed the script .Then made MySQL to start it started without error

Percona Xtrabackup Installation

Backup plays a important role in database world. If our server gets crashed we might lost the data, in order to recover the data, we need backups. Hence backups are to be taken regularly.

        There are many ways to take backup of our server, out of that we mostly use mysqldump to take backups. Its the best solution to take backup also. 
                           But it will take more time if data size is huge. We all know mysqldump will lock  tables while taking a backup. If data size is huge it will be bottleneck for server. In order to avoid table locking we have some third party backup tool (Percona Xtrabackup).This will take backups fast as compared to other methods.

 Percona Xtrabackup is the only world’s open source, free MySQL backup tool.It does not lock table for InnoDB and XtraDB databases while taking a backup.

Percona Xtrabackup can take backup for InnoDB, MyISAM, XtraDB tables in MySQL servers as well as XtraDB tables in Percona servers.

We can achieve the following from Percona Xtrabackup,

•         Backups that complete quickly and reliably

•         Uninterrupted transaction processing during backups

•         Savings on disk space and network bandwidth

•         Automatic backup verification

•         Higher uptime due to faster restore time

Percona provides repositories for yum (RPM packages for Red Hat Enterprise Linux and compatible distributions such as CentOS, Oracle Enterprise Linux, Amazon Linux AMI, and Fedora) and apt (.deb packages for Ubuntu and Debian) for software such as Percona Server, XtraDB, Percona XtraBackup, and Percona Toolkit.

Here, I have shown yum installation of xtrabackup for 32bit CentOS.

First we need to get the rpm file, which can be found from the Percona’s official website.

For 32 Bit, Click here  

For 64 Bit, Click here 

Once downloaded install it using rpm,

rpm -Uvh percona-release -0.0-1.i386.rpm

After installing check it by,

yum list | grep percona

Next is to install Xtrabackup, Run yum install xtrabackup 

    
This will install percona Xtrabackup in your server.

MySQL Indexes

Indexes are used to find rows with specific column values quickly. It can execute the performance of query within fewer seconds. You can find the difference of time taken for a query to execute with and without index.

With index index it performs quickly and without index it takes some time. This is because without index the query has to scan all the rows of table ie. from first row to last row. With index it will start the search from the row where it find its column.

MySQL uses various index like,

          Primary Key

          Unique Key

          Secondary Index

          Full Text Index

Why MySQL Uses Index?

·        To find the rows matching the WHERE clause quickly.

·        To retrieve rows from other tables when performing joins.

·        To sort or group a table if the sorting or grouping is done.

·        To find the MIN() or MAX() value for a specific indexed column key_col

Primary Key

It must contain a unique value for each row of data. It cannot contain null values

There can be only one primary key for a table. We can have primary key for one or more columns.

Primary Key can be referred as a foreign key.

*   a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table

A example for primary key,

 This example shows a table having with a primary key on two columns.

  

Unique Key

It is same as primary key but it can have null values. A table can have many unique keys. And also unique cannot be referred as a foreign key.

The below example shows a table having with a primary key and unique key.

 

Secondary Index

All indexes other than primary key are secondary indexes. Ie. We can have only one primary key in a table, if we want to create more than one index for a table we create it as secondary index mentioning index.

Syntax;

          Create index index_name on table_name (column_name);

If we don’t mention index_name mysql will automatically assign a index name based on the column.(eg : if column name is ‘student’ it will create the index as ‘student’)

We can also create secondary indexes for one or more columns.

The following example shows a table having secondary index for more than one column.

 

Full text Index

MySQL supports Full-text indexing and searching.

A full-text index in MySQL is an index of type FULLTEXT. It can be used only with MyISAM tables (until 5.5). From MySQL 5.6 and up, they can also be used with InnoDB tables.

 Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.

perl(DBI) >= 1.13 is needed by percona-toolkit

Percona tool kit is one of the user friendly toolkits for DBA’s which are available in the environment. It has reduced the workload for DBA’s. Percona toolkits have many tool which works for verifying MySQL replication, finding duplicate indexes, summarizing MySQL server and servers etc.

While we install Percona toolkit some of us may face the following issue,

It is due the missing of the mentioned files , in order to overcome the issue we need  to install those files before installing Percona tool kit.

I have given example for Cent-OS 32 bit system,

First we should get the perl(DBI) file, you can get it from here

Once downloaded install it

It is better to run a rpm file with yum command rather than rpm install, as yum will install all missing dependencies along with the file.

# rpm  -Uvh perl-DBI-1.609-4.el6.i686.rpm 

Similarly download the rest of dependencies files and install them using yum

perl(DBD::mysql)

For Cent-OS 32 bit

For others

perl(Time::HiRes) 

For Cent-OS 32 bit

For others

perl(IO::Socket::SSL) 

For Cent-OS 32 bit  

For others

The reason for installing with yum is that it will install the missing files as shown

From the image you can see that for perl(IO::Socket::SSL) if we run it with rpm command it will ask for perl-Net-LibIDN & perl-Net-SSLeay packages . After installing those files only we can install the socket package.But with yum it will install those files too.

Once all missing dependencies are installed now you can install Percona toolkit

After installation is finished you can check it by running pt (pt tab key) command ,