1

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)
Improve this question
3
  • which replication format are you using? Have you tried to check if all the tables are being replicated on slave by checking number of records in tables in your database. SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{your_db}';
    – MySQL DBA
    Aug 18, 2015 at 5:20
  • @MySQLDBA, the result of the query is as the above. there are differences between the master and slave. The replication format is ROW and this database just contain one table as I mentioned above.
    – shgnInc
    Aug 25, 2015 at 4:04
  • Does slave received any errors post setting up Replication? Also, Have you skipped any errors on slave?
    – MySQL DBA
    Aug 25, 2015 at 5:24

1 Answer 1

Reset to default
1

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

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.