Mysql – Diffrences between master and slave replication in thesql

data integritymysql-5.5partitioningreplication

In a database named asterisk, there is a table named cdr which was partitioned on the time field.

mysql> show create table cdr;
Create Table: CREATE TABLE `cdr` (
`gid` bigint(20) NOT NULL AUTO_INCREMENT,
`id` bigint(20) NOT NULL,
`start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`clid` varchar(80) NOT NULL DEFAULT '',
`src` varchar(80) NOT NULL DEFAULT '',
`channel` varchar(80) NOT NULL DEFAULT '',
`duration` int(11) NOT NULL DEFAULT '0',
`billsec` int(11) NOT NULL DEFAULT '0',
`disposition` varchar(45) NOT NULL DEFAULT '',
`uniqueid` varchar(32) NOT NULL DEFAULT '',
`dnid` varchar(20) NOT NULL DEFAULT '',
`line` varchar(20) NOT NULL DEFAULT '',
`prefix` varchar(20) NOT NULL DEFAULT '',
`service` varchar(100) NOT NULL DEFAULT '',
`cost` int(11) NOT NULL DEFAULT '0',
`visit` varchar(500) NOT NULL DEFAULT '',
`pressed` varchar(200) NOT NULL DEFAULT '',
PRIMARY KEY (`gid`,`start`),
UNIQUE KEY `id` (`id`,`prefix`,`start`),
KEY `start` (`start`),
KEY `clid` (`clid`),
KEY `service` (`service`)
) ENGINE=InnoDB AUTO_INCREMENT=40086530 DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(`start`)
(PARTITION p0 VALUES LESS THAN ('2013-11-16') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN ('2014-01-20') ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN ('2014-02-20') ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN ('2014-04-01') ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN ('2014-06-01') ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN ('2015-01-01') ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

I make a slave replication from it to another server successfully.

Since, I check the database files on file system, I saw something incredible. File size of partitioned table differ on master and slave.

Master Server

root@mdb1-msh:# ls -lh /var/lib/mysql/asterisk_archive 
total 12G
-rw-rw---- 1 mysql mysql 1.7G May  8 22:35 cdr#P#p0.ibd
-rw-rw---- 1 mysql mysql 1.7G May  8 22:35 cdr#P#p1.ibd
-rw-rw---- 1 mysql mysql 1.7G May  8 22:35 cdr#P#p2.ibd
-rw-rw---- 1 mysql mysql 1.8G May  8 22:35 cdr#P#p3.ibd
-rw-rw---- 1 mysql mysql 1.9G May  8 22:35 cdr#P#p4.ibd
-rw-rw---- 1 mysql mysql 1.6G May  8 22:35 cdr#P#p5.ibd
-rw-rw---- 1 mysql mysql 916M Aug 18 06:33 cdr#P#p6.ibd
-rw-rw---- 1 mysql mysql 8.9K May  8 21:15 cdr.frm
-rw-rw---- 1 mysql mysql   48 May  8 21:15 cdr.par
-rw-rw---- 1 mysql mysql   61 May  9 10:16 db.opt

Slave server

root@db-ast1:~# ls -lh /var/lib/mysql/asterisk
total 11G
-rw-rw---- 1 mysql mysql 1.7G Aug 17 09:08 cdr#P#p0.ibd
-rw-rw---- 1 mysql mysql 1.6G Aug 17 09:08 cdr#P#p1.ibd
-rw-rw---- 1 mysql mysql 1.7G Aug 17 13:24 cdr#P#p2.ibd
-rw-rw---- 1 mysql mysql 1.7G Aug 17 13:24 cdr#P#p3.ibd
-rw-rw---- 1 mysql mysql 1.9G Aug 17 15:49 cdr#P#p4.ibd
-rw-rw---- 1 mysql mysql 1.6G Aug 17 17:07 cdr#P#p5.ibd
-rw-rw---- 1 mysql mysql 868M Aug 18 00:09 cdr#P#p6.ibd
-rw-rw---- 1 mysql mysql 8.9K Aug 17 05:28 cdr.frm
-rw-rw---- 1 mysql mysql   48 Aug 17 05:28 cdr.par
-rw-rw---- 1 mysql mysql   65 Aug 17 04:47 db.opt

I need to know what's wrong? what are differences? how to do this operations?
How to check data-integrity?

UPDATE1

Master

mysql> select count(gid) from cdr;
+------------+
| count(gid) |
+------------+
|   28170822 |
+------------+

Slave

mysql> select count(gid) from cdr;
+------------+
| count(gid) |
+------------+
|   27531422 |
+------------+

and for percona checksum:

user@db:~$ pt-table-checksum h=slave-host,u=root,p=pass --databases=asterisk --tables=cdr
Checksumming asterisk_archive.cdr:   7% 06:19 remain
Checksumming asterisk_archive.cdr:  20% 03:52 remain
Checksumming asterisk_archive.cdr:  33% 02:56 remain
Checksumming asterisk_archive.cdr:  46% 02:18 remain
Checksumming asterisk_archive.cdr:  59% 01:42 remain
Checksumming asterisk_archive.cdr:  72% 01:07 remain
Checksumming asterisk_archive.cdr:  90% 00:23 remain
        TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
08-18T10:41:34      0      0 27531422     180       0 228.538 asterisk.cdr

UPDATE2

I checked the mysql.err and there were no errors and the below is the replication status:

mysql> show slave status \G
*************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.34.20
              Master_User: slave_user
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000103
      Read_Master_Log_Pos: 1036136
           Relay_Log_File: mysql-relay-bin.000034
            Relay_Log_Pos: 1036282
    Relay_Master_Log_File: mysql-bin.000103
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB: 
      Replicate_Ignore_DB: 
       Replicate_Do_Table: 
   Replicate_Ignore_Table: 
  Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
               Last_Errno: 0
               Last_Error: 
             Skip_Counter: 0
      Exec_Master_Log_Pos: 1036136
          Relay_Log_Space: 1036481
          Until_Condition: None
           Until_Log_File: 
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File: 
       Master_SSL_CA_Path: 
          Master_SSL_Cert: 
        Master_SSL_Cipher: 
           Master_SSL_Key: 
    Seconds_Behind_Master: 0
  Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error: 
           Last_SQL_Errno: 0
           Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
         Master_Server_Id: 513
1 row in set (0.00 sec)

Best Answer

MySQL doesn't reduce/reclaim the size of previously deleted data. If the previous .ibd file grew significantly but the actual data only accounted for a portion of its size, running OPTIMIZE TABLE can reclaim the unused space.

Source: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

To verify MySQL replication integrity, you may want to check out Percona's pt-table-checksum.

pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master. The optional DSN specifies the master host. The tool’s “EXIT STATUS” is non-zero if any differences are found, or if any warnings or errors occur.

Source: https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html

Update 1:

What about printing out the differences using pt-table-sync - "test-sync" --print ?

pt-table-sync --h=localhost,u=root,p=pass --print --no-check-slave --databases=asterisk --tables=cdr <master-host> <slave-host>

pt-table-sync --h=localhost,u=root,p=pass --print --no-check-slave --databases=asterisk --tables=cdr <slave-host> <master-host>

Source: https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html

I have very bad news for you.

You should not have deleted the ibdata1 file. Here is why:

ibdata1 contains four type of information:

table metadata
MVCC data
data pages (with innodb_file_per_table enabled)
index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.

I also have very good news for you along with some bad news.

It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).

Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.

To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.

I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.

UPDATE 2011-10-17 06:19 EDT

Here is your original innodb configuration from your question:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

Please notice that innodb_log_file_size is there twice. Look carefully...

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;. Mystery solved !!!

UPDATE 2011-10-17 11:07 EDT

The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).

For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.

MySQL Replication – Temp Table Still Exists on Slave

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

InnoDB Architecture

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.