# Replicated AUTO\_INCREMENT field is out of sync (simple master -\> slave)
**URL:** https://forums.percona.com/t/replicated-auto-increment-field-is-out-of-sync-simple-master-slave/6239
**Category:** General Questions
**Created:** [March 8, 2018, 5:35pm UTC](https://forums.percona.com/t/replicated-auto-increment-field-is-out-of-sync-simple-master-slave/6239 "2018-03-08T17:35:13Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@Amna](https://forums.percona.com/u/Amna)
#### Post date: [March 8, 2018, 5:35pm UTC](https://forums.percona.com/t/replicated-auto-increment-field-is-out-of-sync-simple-master-slave/6239/1 "2018-03-08T17:35:13Z")
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
---
### Author:  [@scott.nemes](https://forums.percona.com/u/scott.nemes)
#### Post date: [March 8, 2018, 6:46pm UTC](https://forums.percona.com/t/replicated-auto-increment-field-is-out-of-sync-simple-master-slave/6239/2 "2018-03-08T18:46:15Z")
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]](https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html%5B/url%5D)
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.
---
### Author:  [@Amna](https://forums.percona.com/u/Amna)
#### Post date: [March 9, 2018, 4:07am UTC](https://forums.percona.com/t/replicated-auto-increment-field-is-out-of-sync-simple-master-slave/6239/3 "2018-03-09T04:07:23Z")
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.
---
### Author:  [@scott.nemes](https://forums.percona.com/u/scott.nemes)
#### Post date: [March 9, 2018, 12:44pm UTC](https://forums.percona.com/t/replicated-auto-increment-field-is-out-of-sync-simple-master-slave/6239/4 "2018-03-09T12:44:11Z")
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. =)
---
### Author:  [@Amna](https://forums.percona.com/u/Amna)
#### Post date: [March 17, 2018, 1:29am UTC](https://forums.percona.com/t/replicated-auto-increment-field-is-out-of-sync-simple-master-slave/6239/5 "2018-03-17T01:29:09Z")
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]](https://dev.mysql.com/doc/refman/5.6/en/replication-features-blackhole.html%5B/url%5D)) or row format is enough as it skip non replicate tables actions.
---
### Author:  [@scott.nemes](https://forums.percona.com/u/scott.nemes)
#### Post date: [March 17, 2018, 4:10pm UTC](https://forums.percona.com/t/replicated-auto-increment-field-is-out-of-sync-simple-master-slave/6239/6 "2018-03-17T16:10:14Z")
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. =)