Replicated AUTO_INCREMENT field is out of sync (simple master -> slave)

General Questions
1

I have 2 MySQL (Percona 5.7) replicas on 2 Linux servers

Server1 contain masterA DB1& DB2 , slaveB DB3 and some tables of DB4

Server2 Contain masterB DB3 and DB4 , slaveA some tables of DB1/DB2

Replic A staus is running and data is syncing but for Replica B status is running but AUTO_INCREMENT field is out of sync

it’s so wired issue the replications look like are running and no errors but AUTO_INCREMENT field is out of sync, How can i solve this issue?

Those the my.cnf files for 2 servers

server1 my.cnf
server-id = 1 ##Master info## log-bin=mysql-bin binlog_format=ROW expire_logs_days = 3 max_binlog_size = 100M ##Slave info## relay_log=relay-log replicate-do-db=DB3 replicate-wild-do-table=DB4.tb1 replicate-wild-do-table=DB4.tb2 replicate-wild-do-table=DB4.tb3
Server2 my.cnf
server-id=3 # Master Info # log-bin=mysql-bin binlog_format=ROW expire_logs_days = 30 max_binlog_size = 100M ##Slave Info## relay_log=relay-log replicate-wild-do-table=DB1.tb1 replicate-wild-do-table=DB1.tb2 replicate-wild-do-table=DB1.tb3 replicate-wild-do-table=DB1.tb4 replicate-wild-do-table=DB2.tb1

photoid=51039
photoid=510391249×460 7.37 KB

2

Hi Amna;

Try replacing replicate-do-db=DB3 with replicate-wild-do-table=DB3.% and see if that gives you what you want.

Replication filters tend to be very messy, and generally do not end up doing what you think they are doing. I’d make sure to read through the manual on these settings a few times to make sure you understand every caveat, especially the parts about using STATEMENT vs ROW based replication.

[url]https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html[/url]

Another alternate option is to change the storage engine for any tables you do not want on the slave to BLACKHOLE, and avoid the replication filters altogether.

3

Hi Scott,
Thanks a lot for your reply, i will try your recommendation but let me ask question what should i do if i want to replicate some tables or some DBs not all ?
regarding STATEMENT / ROW based replication, i have used ROW based replication as master sent update/insert commands in binlog file as you know these commands unsafe statements so as far as i know Row is the best choose.

4

Hi Amna;

If you want to filter just database (i.e. db1, db2) then I would use replicate-do-db. If you want to filter by just tables or tables and databases at the same time, then I’d use replicate-wild-do-table (using the db1.% format for the databases to filter, and the db2.tbl1 format for the tables). Alternatively for filtering tables you could simply make the storage engine BLACKHOLE on the slave for the tables you do not want, which is a clean and filter-free way of doing it.

And yes using ROW based replication does simplify it some, but filters are still a pain. =)

5

Hi Scott,
Thanks a lot for your support. i have tried your suggestions and works fine. i have another question, what do you mean (Another alternate option is to change the storage engine for any tables you do not want on the slave to BLACKHOLE, and avoid the replication filters altogether.)? how to change the storage engine for slave to BLACKHOLE?
should i change bin format to statement as per this link ([url]https://dev.mysql.com/doc/refman/5.6/en/replication-features-blackhole.html[/url]) or row format is enough as it skip non replicate tables actions.

6

Hi Amna;

Glad you got it working! Since it is doing what you want now, I would not bother going the BLACKHOLE route. But for future reference, you can just the engine of a table on the slave by doing an ALTER TABLE … ENGINE=BLACKHOLE.

The link you referenced points out the fact that UPDATES and DELETES will not be logged to the binary log when the BLACKHOLE engine is used. This means that if you have a slave with BLACKHOLE tables, and are using row based replication, then the slave server will only record INSERTS to the binary log for those BLACKHOLE tables, and not for UPDATES or DELETES.

But again since you got it working the way you want, I would not mess with it at this point. =)