I don't use pt-table-sync because I don't agree with the approach it uses:

When synchronizing a server that is a replication slave with the –replicate or –sync-to-master methods, it always makes the changes on the replication master, never the replication slave directly. This is in general the only safe way to bring a replica back in sync with its master; changes to the replica are usually the source of the problems in the first place. However, the changes it makes on the master should be no-op changes that set the data to their current values, and actually affect only the replica. Please read the detailed documentation that follows to learn more about this.

^{— http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html}

My philosophy is "no, that's okay, you guys can go ahead and stay away from the master."

While it's true that a common cause of replication errors are related to things being changed directly on the slave, it's also true that if two servers have an identical set of data at an identical set of binlog coordinates, that's a valid place to begin replication. (I would also suggest that the other common cause of replication errors is people trying to replicate only some of their tables and not all, which is almost never a good idea, unless you really know what you're doing, in which case, you'll know better than to try it.)

If you do use it, and I'm sure a lot of people do, then I don't think you actually want to stop the slave. I think you need to keep the slave running to it can make those "no-op changes to the master" and watch them replicate over to fix the inconsistent tables. I don't know what it does with errors, perhaps it skips over them.

Here is one of the alternative approaches that I use.

You need three console windows for this. Do not disconnect your session from the master or you will lose the global lock. You need a console connection to the master and one to the slave, and you need to have your commands to do mysqldump set up and ready to go in a third window.

First, check the master's binlog coordinates and the slave's binlog coordinates to make sure the slave is not lagging.

master-mysql> SHOW MASTER STATUS;

+-----------------------+-----------+--------------+------------------+
| File                  | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+-----------+--------------+------------------+
| pri-master-bin.004421 | 142052212 |              |                  |
+-----------------------+-----------+--------------+------------------+

slave-mysql> SHOW SLAVE STATUS\G

    Relay_Master_Log_File: pri-master-bin.004421
      Exec_Master_Log_Pos: 142052212
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
    Seconds_Behind_Master: 0

There are more values included with SHOW SLAVE STATUS but these are the important ones. The Exec_Master_Log_Pos needs to be close to or identical to the master, and the log file the same.

Next, obtain a brief read lock on the master. This locks all of the tables for write allows the master to settle down long enough that we know the slave and master are at the same place in the binlog at this point in time.

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

master-mysql>

Note that this sometimes takes a few seconds before your prompt returns.

Do not disconnect this session. You need this session active because it is holding the lock on the master. You can use it for the following, but don't close it.

Now, repeat the SHOW MASTER STATUS; on the master and SHOW SLAVE STATUS\G on the slave. You should find that they have settled down to the same set of binlog coordinates. Wait until they do. If they don't settle down quickly, you might want to drop the read lock on the master to figure out why not.

Next:

slave-mysql> STOP SLAVE SQL_THREAD;

This will stop the slave from executing any more events from the master, which is still locked.

In the third window, start a backup of the tables from the master.

shell> mysqldump --verbose --single-transaction database_name table1 table2 table3 table4 > dump.sql

-- Connecting to *hostname*...
-- Starting transaction...
-- Retrieving table structure for table...

As soon as you see "Retrieving table structure..." you can release the global read lock on your master, so your application can start writing to it again.

mysql-master> unlock tables;
Query OK, 0 rows affected (0.01 sec)

If your tables are InnoDB, the --single-transaction option should get a consistent backup from the master at a point in time during the few seconds that you had the global read lock on the master. Since you also stopped the slave at that point, the slave should be positioned at precisely the appropriate point in time for applying that backup.

When the backup of those tables is complete, apply it to the slave:

shell> mysql --host=slave.host.name database_name < dump.sql

At this point, you have restored the tables onto the slave in a form that should be identical to the way they appeared on the master at the time when the slave was stopped, and replication should resume when you remove the configuration not to replicate those tables, and then:

slave-mysql> START SLAVE SQL_THREAD;

Be sure your backups are solid before you begin.

I hope I can answer this adequately because I hate temp tables that use InnoDB. Here is why

In this diagram, the data dictionary has that temp table as an entry. Once the temp table is done being used for ALTER TABLE, the data dictionary entry should disappear.

Here are your questions and my answer for them ...

1.. When alter has been successfully done then why this temp table still exist and not deleted.

Under normal circumstances, when a DB Connection terminates, all open temp tables are supposed to be dropped. The only reason a temp table will linger after an ALTER TABLE is done is if the DB Connection running the ALTER TABLE terminates before the temp table can be dropped. It is possible that the rollback segments (See Diagram) has the original info of the table before the ALTER TABLE was issued.

Since you mentioned this as happening on the Slave, the only suspect (or person of interest) for this problem would be the SQL thread for MySQL Replication. Why? It is the owner of the temp tables need to complete the SQL being executed from the relay logs. If you kill a SQL thread in a manner other than STOP SLAVE; while it is performing an ALTER TABLE, it may not drop temp tables in an orderly manner. Thus, you will have a lingering temp table regardless of the storage engine.

2.. Now how can I delete this temp table and what will be its impact.

You could go ahead and delete the .frm and .ibd. I only say this because it is temp table. The table should have disappeared. The impact ? The data dictionary will still have the temp table registered. Just a few bytes get wasted.

Don't worry about the temp table name being used again. Look at the name of the temp table

#sql-7a87_230c32.ibd

There are 10 hexadecimal digits. The chances of reusing that temp table name is 1 in ₁₆10 or ₂40 which 1,099,511,627,776. I think you will be OK in this.

Don't delete them just yet. Look at the your third question ...

3.. May I read this temp file or know by any command that for which table this temp table was created.

I understand the need for this question. Evidently, you want to know what the table was that was having the ALTER TABLE done. Unfortunately, you cannot read any temp table through standard SQL.

However, you could probably rename the .frm and use a utility to read the table structure.

Here is an old post of mine to do this with a .frm in the Windows environment : How can extract the table schema from just the .frm file?

EPILOGUE

Even though the temp table no longer has a useless purpose at this point, deleting the InnoDB temp table there is that 1 in trillion chance that InnoDB be behave erratically.

Your last resort when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow

UPDATE 2014-06-10 08:36 EDT

It should be evident from the file timestamps and the commentary in your second update that a shutdown must have taken place in the middle of an ALTER TABLE. All transactional information for MVCC regarding the InnoDB table was held in a state of suspended animation in one of the following places:

• Transaction Logs (a.k.a. Redo Logs, which are the files ib_logfile0 and ib_logfile1),
• Rollback Segments & Undo Space (located in the system tablespace file ibdata1)
• Double Write Buffer (located in the system tablespace file ibdata1)

When you started mysql back up, the MVCC for the table was replayed to bring the original table into a consistent state. That may result in either the table being rolled back to the original state before the ALTER TABLE or if everything passed through the Redo Logs, the table now existing in the new design. The fact that the .ibd file has a later timestamp show that MVCC must have been applied to it on the startup of mysql (which includes crash recovery).

Once the shutdown happened, the InnoDB table lost its identity as a temp table and became a regular table in the eyes of mysqld. Thus, you should be able to delete the temp files with impunity.