Insight of MySQL Row based Replication

 

RBR provides a safe way of replicating between master and slave. It has several advantages over statement based replication (SBR), where the lack of non-deterministic operations jumps to mind rather quickly. However, there is one drawback that drives some users away from it. RBR ships the changes over to the slave, instead of the operations as it happens in SBR. This means that RBR may exhibit a large binary log footprint for operations that make a rather big number of changes or operate over sizable rows.

 

Below are some interesting enhancements in RBR in MySQL 5.6 Replication.

1. Optimized row image:

In RBR, one row event contains changes to one or more rows in a given table. A row change, in its turn, may consist of one or two full row copies of the row that is being changed. These are generally known as row images. The first one, known as before image (BI), contains data as it was before the row was modified. The second one, known as after image (AI), is what the row looks like after the changes were done. Each image has different purpose: the BI is used for locating, in the storage engine, the row to be updated/deleted, while the AI, is used for replaying the actual changes. Needless to say, not both images are needed for every operation. Deletes only need the BI, inserts, on the other hand, just need the AI, while updates need both.

Large data transfer between master and slave while using RBR is a thing of past. Why log complete rows on the master? Why not just the changed colums?  The MySQL 5.6 release enables a DBA to configure the master to log only the changed columns in the row.  The Before Image (BI) will contain the columns for identifying the rows and the After Image(A I) will just contain the column that has changed.

Before MySQL 5.6: 

As one can observe in the illustration that the row images were full, i.e. all the columns in the row were logged for both the Before Image(BI) and the After Image AI

In MySQL 5.6:

In MySQL 5.6 the DBA can configure if full or minimal or NOBLOB image of rows should be logged in the binary log in the following ways.

 

1. start the server with --binlog-row-image=MINIMAL|FULL|NOBLOB

2. SET SESSION|GLOBAL binlog_row_image= MINIMAL|FULL|NOBLOB

In minimal image if there is a Primary Key (PK) or any kind of Unique Index/Key (UK) that can identify a row uniquely on the slave then the BI contains just that column. In the After Image (AI) only the changed columns are logged 

 

In case of NOBLOB is used, the BLOB fields will be skipped from logging unless changed. In case  FULL is used the logging will continue as before.

As you can see the amount of data transfer will be reduced by a lot especially if you have used proper indexes/keys for the tables on the master and the slave.

2. Batch operations on table without PK or UK:

is one of the long awaited optimization when using RBR. This enables the DBA to configure what algorithm will be used on the slave to find the appropriate row for updating/deleting. We have introduced the concept of HASH_SCAN, in which an in-memory hash is being created on the slave, and provides a way to find and apply rows in a row event in O(n) time.  This enhancement provides a great scope for performance improvement on the slave for the events logged in row format.

3. Informational Log events: 

One of the difficulties DBA have faced till now, while using RBR is that, it is difficult to maintain. The Row images are binary in nature and is not suitable for human "consumption". This is where this enhancement comes handy. There are two ways of using this.

    1. Enabling switch --binlog-rows-query-log-events during the server startup

    2. Changing the corresponding session variable.

Once enabled this will cause the original query for the row event to be logged in the binary log as a special event which the DBA can check using one of the two  methods.

 

1. One can check these special events in the binary-log by using SHOW BINLOG EVENTS. The query corresponding the the row event will be logged as an event type: Row_query. below is one such example

 

mysql> SHOW BINLOG EVENTS;

+---------------+-----+-------------+-------+--------+-----------------------------------------------+

| Log_name      | Pos | Event_type  | S..id | End... | Info                                          |

+---------------+-----+-------------+-------+--------+-----------------------------------------------+

| master.000001 |   4 | Format_desc |     1 |    114 | Server ver: 5.6.7-m5-debug-log, Binlog ver: 4 |

| master.000001 | 114 | Query       |     1 |    200 | use `test`; CREATE TABLE NUM_INTS (a INT)     |

| master.000001 | 200 | Query       |     1 |    268 | BEGIN                                         |

| master.000001 | 268 | Rows_query  |     1 |    323 | # INSERT INTO NUM_INTS VALUES (1), (2), (3)   |

| master.000001 | 323 | Table_map   |     1 |    364 | table_id: 54 (test.NUM_INTS)                  |

| master.000001 | 364 | Write_rows  |     1 |    408 | table_id: 54 flags: STMT_END_F                |

| master.000001 | 408 | Query       |     1 |    477 | COMMIT                                        |

+---------------+-----+-------------+-------+--------+-----------------------------------------------+

 

In the above terminal output (snip-ed), one can see the event at pos= 268 is an informational event for the next Table_map and Write_rows events. 

 

 

2. Another way is to use the mysqlbinlog utility with  --vv switch (verbosity level 2 or more).

 

shell> mysqlbinlog -vv /master.000001

# at 268

#110401 14:24:29 server id 1  end_log_pos 323   Rows_query

# INSERT INTO t1 VALUES (1), (2), (3)

# at 323

#110401 14:24:29 server id 1  end_log_pos 364   Table_map: `test`.`NUM_INTS` mapped to number 54

# at 364

#110401 14:24:29 server id 1  end_log_pos 408   Write_rows: table id 54 flags:STMT_END_F

 

Reference:-

http://d2-systems.blogspot.in/2011/04/mysql-562-dm-optimized-row-based.html

http://binlogtorelaylog.blogspot.com/2012/10/sneak-peek-inside-mysql-56-RBR.html 

Foreign Key in MySQL

Foreign Keys are a way of implementing relationships/constraints between columns in different tables. For example, we want to make sure that the value of the county column in the towns table has an associated entry in the counties table. In this way, no-one can place a town in a non-existent county and similarly no one can remove a county and leave orphaned towns behind.

We refer to the towns table as the child and the counties table as the parent of world database.

There are different categories of constraints that influence how they’re enforced when a row is updated or deleted from the parent table:

• Cascade: If a row is deleted from the parent then any rows in the child table with a matching FK value will also be deleted. Similarly for changes to the value in the parent table.
• Restrict: A row cannot be deleted from the parent table if this would break a FK constraint in the child table. Similarly for changes to the value in the parent table.
• No Action: Very similar to “Restrict” except that any events/triggers on the parent table will be executed before the constraint is enforced – giving the application writer the option to resolve any FK constraint conflicts using a stored procedure.
• Set NULL: If NULL is a permitted value for the FK column in the child table then it will be set to NULL if the associated data in the parent table is updated or deleted.
• Set Default: If there is a default value for the FK column in the child table then it will be used if the associated data in the parent table is updated or deleted. Note that this is not implemented in this version.

Oracle Announces MySQL 5.6 Release Candidate

Hi Guys,
 
Finally MySQL 5.6 is here. With great features for DBA and Product.. Below is some of key features from Oracle Keynot.

Release Candidate Delivers a Wide Range of Innovations

MySQL 5.6 RC provides enhanced linear scalability, helping users to leverage modern hardware capabilities.

With this Release Candidate, users can experience simplified query development and execution, better transactional throughput and application availability, flexible NoSQL access, improved replication and enhanced instrumentation.

New features and benefits include:

ü  Better Query Execution Times and Diagnostics provided through an enhanced MySQL Optimizer that includes:

ü  Subquery Optimizations: simplify query development by optimizing subqueries prior to execution. New efficiencies in how result sets are selected, sorted and returned deliver substantial improvement in query execution times.

ü  The addition of Index Condition Pushdown (ICP) and Batch Key Access (BKA) can improve selected query throughput by up to 280x(1).

ü  Enhanced Optimizer Diagnostics: with EXPLAIN for INSERT, UPDATE, and DELETE operations. EXPLAIN plan output in JSON format delivers more precise optimizer metrics and better readability, and Optimizer Traces enables to track the optimizer decision-making process.

ü  Better Transactional Throughput and Application Availability with an improved InnoDB storage engine:

ü  Better Transactional and Read Only Throughput: InnoDB has been re-factored to minimize legacy threading, flushing, purge mutex contentions and bottlenecks, enabling better concurrency on heavily loaded OLTP systems, and resulting in significantly improved throughput for read only workloads(2).

ü  Enhanced Availability: Online DDL operations enable DBAs to add indexes and perform table alterations while the application remains available for updates.

ü  Full-Text Search with InnoDB: allows developers to build FULLTEXT indexes on InnoDB tables to represent text-based content and speed up application searches for words and phrases.

ü  Simple, Key Value Lookup: flexible NoSQL access to InnoDB provides simple, key-value lookup of InnoDB data via the familiar Memcached API. Users get the “best of both worlds,” combining key-value operations and complex SQL queries in the same database.

ü  Improved Scale-Out and High Availability: with new features in MySQL replication including:

ü  Self-Healing Replication Clusters: the addition of Global Transaction Identifiers and HA Utilities make it simple to automatically detect and recover from failures. Crash-Safe Replication enables the binary log and slaves to automatically recover correct positions in the replication stream in case of a crash, and resume replication without administrator intervention. Checksums maintain data integrity across the cluster by automatically detecting and alerting on errors.

ü  High Performance Replication Clusters: up to 5x faster replication through Multi-Threaded Slaves(3), Binlog Group Commit and Optimized Row-Based Replication enable users to maximize the performance and efficiency of replication as they scale-out their workloads across commodity systems.

ü  Time-delayed Replication: provides protection against operational errors made on the master, for example accidentally dropping tables.

ü  Enhanced PERFORMANCE_SCHEMA: new instrumentation enables users to better monitor most resource intensive queries, objects, users and applications. New summaries with aggregated statistics grouped by query, thread, user, host and object are also available. The enhancements allow for easier default configuration with less than five percent overhead.

The MySQL 5.6 RC includes additional enhancements and is a feature complete aggregation of the Development Milestones Releases Oracle previously delivered to the MySQL community.

 

 

rpm command cheat sheet for Linux

rpm is a powerful Package Manager for Red Hat, Suse and Fedora Linux. It can be used to build, install, query, verify, update, and remove/erase individual software packages. A Package consists of an archive of files, and package information, including name, version, and description:

Syntax	Description	Example(s)
rpm -ivh {rpm-file}	Install the package	rpm -ivh mozilla-mail-1.7.5-17.i586.rpm rpm -ivh --test mozilla-mail-1.7.5-17.i586.rpm
rpm -Uvh {rpm-file}	Upgrade package	rpm -Uvh mozilla-mail-1.7.6-12.i586.rpm rpm -Uvh --test mozilla-mail-1.7.6-12.i586.rpm
rpm -ev {package}	Erase/remove/ an installed package	rpm -ev mozilla-mail
rpm -ev --nodeps {package}	Erase/remove/ an installed package without checking for dependencies	rpm -ev --nodeps mozilla-mail
rpm -qa	Display list all installed packages	rpm -qa rpm -qa | less
rpm -qi {package}	Display installed information along with package version and short description	rpm -qi mozilla-mail
rpm -qf {/path/to/file}	Find out what package a file belongs to i.e. find what package owns the file	rpm -qf /etc/passwd rpm -qf /bin/bash
rpm -qc {pacakge-name}	Display list of configuration file(s) for a package	rpm -qc httpd
rpm -qcf {/path/to/file}	Display list of configuration files for a command	rpm -qcf /usr/X11R6/bin/xeyes
rpm -qa --last	Display list of all recently installed RPMs	rpm -qa --last rpm -qa --last | less
rpm -qpR {.rpm-file} rpm -qR {package}	Find out what dependencies a rpm file has	rpm -qpR mediawiki-1.4rc1-4.i586.rpm rpm -qR bash

{package} - Replace with actual package name

MySQL Prepared Statements

MySQL has many good features. Prepared statement is one of good feature which I like most. When I was a developer I was using this to complete many operations.  After A long time I was refreshing my concepts of development. Sharing so another can also use enjoy this features

Statement Handling
MySQL support the prepared statement syntax. For the better part, a prepared statement is much like a 'normal', immediate statement. The main difference is seen in the way the statement is processed by the server.

Immediate Statements

When an immediate statement is issued, it is processed directly. Processing comprises the following steps:

1. Parsing: lexical and syntactic analysis of the statement
2. Planning: optimizer devises a strategy to realise the required result or action, the execution plan or query plan
3. Execution: retrieval/seeking, writing and reading of data and, if applicable, the construction of a resultset

After these steps, the server responds to the request by sending the client a result set (if applicable), or an acknowledgement that the statement was executed. Of course, all these actions are performed in concert, and the client is not aware of these different steps taking place. This becomes clear when typing the following statement into the MySQL command line client tool:

mysql> select count(*) from information_schema.schemata;

+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.10 sec)

Immediately (well, almost) the request to select the number of schemata is responded to by returning the result set.

Prepared Statements
A prepared statement is initiated by the PREPARE statement. A preparation for a query equivalent to previous one could look like this:

mysql> prepare stmt from
    -> 'select count(*) from information_schema.schemata';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

This time, we didn't get a result set.
The PREPARE statement instructs the server to parse the query, and possibly, to devise the execution plan. PREPARE associates an identifier with the statement, stmt, which acts as a handle to refer to the statement and the corresponding execution plan.

Actual execution is postponed until called for by the EXECUTE statement, using the handle to identify the prepared statement to execute.

mysql> execute stmt;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

Which is the same result as we got when we issued the immediate statement.

This seems like an overly complex way to do what we could also do with just one statement. It is - until we execute it again:

mysql> execute stmt;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

....and again and again and again.

This actually touches upon the major purpose of the prepared statement concept: when a statement is to be repeatedly executed, a prepared statement is potentially more efficient. Because only the execution step needs to be repeated, there is less time wasted on parsing and building the query plan each time the result is required.
Increased efficiency is even more evident for statements that do not return a result set (such as INSERT, UPDATE and DELETE) because the actions needed to construct a result set are generally more time-consuming than parsing and creating an execution plan.

Now, we need to elaborate just a little on this efficiency argument. In the Reference manual, you will bump in to this phrase pretty quickly:

MySQL 5.0 provides support for server-side prepared statements. This...takes advantage of the efficient client/server binary protocol...provided that you use an appropriate client programming interface. Candidate interfaces include the MySQL C API client library (for C programs), MySQL Connector/J (for Java programs), and MySQL Connector/NET.

An alternative SQL interface to prepared statements is available. This interface is not as efficient as using...a prepared statement API...

A little further on, the manual explains that the prepared statement syntax is available from within the SQL language primarily for development purposes; NOT to gain efficiency.

(I did some tests that suggest that the SQL prepared statement syntax is slower than immediate statements, but I don't know if this has to do with the query cache. I used INSERT statements BTW)

Using Parameters
A very powerful feature of prepared statements is the possibility to bind parameters to it. Parameters are specified by writing special ? placeholders inside the sql statement that is to be prepared:

mysql> prepare stmt from
    -> 'select count(*) from information_schema.schemata where schema_name = ?'
;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

When EXECUTE-ing the statement, these placeholders must be bound to user variables with the USING syntax:

mysql> set @schema := 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @schema;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

So, even though the statement has been prepared, we can still enjoy the flexibility of controlling the query result.

One thing to keep in mind is that parameters are not implemented using simple string substitution. For example, the placeholder in the previous example is not quoted inside the statement. It merely provides a slot for a value, and the binding process takes care of transferring that value to the slot inside the statement. Quotes are merely syntactic methods to distinguish a string from the surrounding code. Because the binding process is way beyond the level of parsing, it does not make sense to use quotes.

You can't just put a placeholder wherever you like. Parameter placeholders can appear only in those places inside the statement where you would normally expect an expression. In particular, you cannot use parameter placeholders to parameterize identifiers or entire statement structures. So, the following attempt fails with a syntax error, because the parameter placeholder appears where you would normally put an identifier:

mysql> prepare stmt from 'create table ? (id int unsigned)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '? (id int unsigned)' at line 1

However, is is not impossible to paramterize identifiers using the prepared statement syntax. I'll show that in a bit. It's just that you can't do it using parameters, because parameters are just a special cases of expressions, like column references, literals, etc.

Multiple Parameters
We are not restricted to just one parameter, we can use several:

mysql> prepare stmt from
    -> 'select count(*)
    -> from information_schema.schemata
    -> where schema_name = ? or schema_name = ?'
;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute stmt
    -> using @schema1,@schema2
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

Parameter binding occurs in a positional manner.
Each ? placeholder must be matched bij exactly one user variable in the USING clause, where the first placeholder matches the first user variable, the second placeholder matches the second user variable and so on.

You really must match each placeholder by exactly one user variable, or else you will encounter an error:

1210 (HY000): Incorrect arguments to EXECUTE

Dynamic SQL
For no particular reason, PREPARE accepts either a string literal, or a user-defined variable to define the statement to prepare. It would've been just as conceivable to accept just a statement, like so:

mysql> prepare stmt from
    -> select count(*) from information_schema.schemata;

But no, this does not work. This just results in a syntax error.

Anyway, we already saw how PREPARE accepts a statement in the form of a string literal. It's pretty much the same for a global user variable:

mysql> set @sql_text := 'select count(*) from information_schema.schemata';

mysql> prepare stmt from
    -> @sql_text

Query OK, 0 rows affected (0.00 sec)
Statement prepared

Because we can freely assign whatever value we want to @sql_text user variable, we can use this as a device to employ dynamic SQL.

Now we know how to dynamically manipulate our identifiers too: we just manipulate our string before assigning it to the user variable, like so:

mysql> set @table_name := 'mytable';
Query OK, 0 rows affected (0.02 sec)

mysql> set @sql_text:=concat('create table ',@table_name,'(id int unsigned)');
Query OK, 0 rows affected (0.00 sec)

Cleaning up
There's one extra step in the process of using prepared statements that I did not yet mention. That's cleaning up (I guess that tells you something about me, right?). Once you've prepared a statement, the handle and the associated objects on the server's side will remain to exist until the client's session is over. This means that client will keep some of the server's resources occupied. Therefore, it's good practice to clean up afterwards. When you're sure you're done working with the statement, you should DEALLOCATE it:

mysql> deallocate prepare stmt;

This just tells the server to get rid of all the resources associated with the statement handle, and to forget about the statement handle as well.

How to check Index usage information in SQL Server

Today I got face once question  from one of my team-mate, How we will check index usage information in SQL Server?  After some R&D we got the way, to find out information J . Below is the query for same.
sys.dm_db_index_operational_stats
This function gives you information about insert, update and delete operations that occur on a particular index. In addition, this view also offers data about locking, latching and access methods. There are several columns that are returned from this view, but these are some of the more interesting columns:

leaf_insert_count - total count of leaf level inserts
leaf_delete_count - total count of leaf level inserts
leaf_update_count - total count of leaf level updates
Here is a sample query that provides some of the key columns that are helpful to determine insert, update and delete operations.

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
A.LEAF_INSERT_COUNT,
A.LEAF_UPDATE_COUNT,
A.LEAF_DELETE_COUNT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = A.[OBJECT_ID]
AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
sys.dm_db_index_usage_stats

This view gives you information about overall access methods to your indexes. There are several columns that are returned from this DMV, but here are some helpful columns about index usage:

user_seeks - number of index seeks
user_scans- number of index scans
user_lookups - number of index lookups
user_updates - number of insert, update or delete operations

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

Enjoy J

MySQL: How to recover Recovery deleted ibdata1

A interesting Article to read

Recently I had a case when a customer deleted the InnoDB main table space – ibdata1 – and redo logs – ib_logfile*.

MySQL keeps InnoDB files open all the time. The following recovery technique is based on this fact and it allowed to salvage the database.

Actually, the files were deleted long time ago – 6 months or so. As long as file is open physically it still exits in the file system and reachable to processes which have opened it.

Thus, from user perspective nothing has changed after the deletion. By the way, this is a good reason to monitor existence of these files!
But after the restart InnoDB will detect that there is neither system table space nor log files, so it will create empty ones. The InnoDB dictionary will be empty and InnoDB won’t be able to use a bunch of existing ibd files. This situation will be a job for ibdconnect, but as long as MySQL isn’t restarted it is possible to recover database fast. Let me illustrate how.

Let’s simulate the accident. For that I will delete /var/lib/mysql/ib* files, while sysbench generates read/write activity:

Screen0:

root@localhost:~# sysbench --num-threads=16 --max-requests=0 --test=oltp --oltp-table-size=1000000 --max-time=3600 --mysql-user=root run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!

Screen1:

root@localhost:/var/lib/mysql# rm ib*
root@localhost:/var/lib/mysql#

Now the files have gone, but MySQL is still running. They don’t exist in /var/lib/mysql, but can be reachable in /proc file system:

root@localhost:/var/lib/mysql# ls -la  /proc/14101/fd/ | grep -e ibdata -e ib_
lrwx------ 1 root  root  64 Aug  7 23:29 3 -> /var/lib/mysql/ibdata1 (deleted)
lrwx------ 1 root  root  64 Aug  7 23:29 8 -> /var/lib/mysql/ib_logfile0 (deleted)
lrwx------ 1 root  root  64 Aug  7 23:29 9 -> /var/lib/mysql/ib_logfile1 (deleted)

where 14101 is the PID of mysqld process.

However, we can’t copy them back because at any given point of time, there are modified pages in the buffer pool. Those pages are not written on disk and will be lost if the changes are not permanently written. This can lead to corruption and data loss.

For the same reason we can’t make MySQL backup by just copying the files.

So, we have to make sure all modifications are written to the disk.

For that we have to stop any further writes and wait until InnoDB flushes all pages.

To stop write activity we can either stop application or lock tables:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.37 sec)

Now let’s wait until all dirty pages are flushed on disk. For that we will monitor checkpoint age. The checkpoint age is a difference between current log sequence number and last checkpoint in “SHOW ENGINE INNODB STATUS” output. If checkpoint age is zero, all pages are flushed:

---
LOG
---
Log sequence number 363096003
Log flushed up to   363096003
Last checkpoint at  363096003
Max checkpoint age    7782360

To speed up flushing we can set dirty pages percentage to zero:

mysql> set global innodb_max_dirty_pages_pct=0;
Query OK, 0 rows affected (0.01 sec)

It is also important to ensure all other background processes have finished their work.
One of them is the insert buffer thread. Its size should be not more than 1(it’s never less than 1):

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 398, seg size 400,

Another thread writing in background is the purge thread.
It should purge all transactions up to very last

------------
TRANSACTIONS
------------
Trx id counter 0 16644
Purge done for trx's n:o < 0 16644 undo n:o < 0 0

But if the last transaction wasn’t something that requires a purge operation (SELECT for example) Trx id counter will be bigger.
In that case at least ensure InnoDB isn’t doing any writes:

FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
332 OS file reads, 47 OS file writes, 32 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

When all modified pages are flushed it is safe now to copy InnoDB files back:

root@localhost:/var/lib/mysql# cp /proc/14101/fd/3 /var/lib/mysql/ibdata1
root@localhost:/var/lib/mysql# cp /proc/14101/fd/8 /var/lib/mysql/ib_logfile0
root@localhost:/var/lib/mysql# cp /proc/14101/fd/9 /var/lib/mysql/ib_logfile1

Let’s fix the owner:

root@localhost:/var/lib/mysql# chown -R mysql ib*
root@localhost:/var/lib/mysql#

And restart MySQL:

root@localhost:/var/lib/mysql# /etc/init.d/mysql restart

After the restart all InnoDB tables are reachable:

mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.19 sec)

Conclusions

• Add to your monitoring system checks that InnoDB files ibdata and ib_logfile* do exist
• Do not restart MySQL until the further recovery strategy is clear

Reference:-
http://www.mysqlperformanceblog.com/2012/08/10/recovery-deleted-ibdata1/