Home / server / Questions / 411995
In Process
ledy
Asked: 2012-07-28 14:33:50 +0800 CST

maakit: mk-table-sync issue with index

  • 772

Trying to re-sync a slave, I run into troubles with some of my tables which do not have a auto-increment primary index, but a primary unique ID (not auto-increment).

I run the re-sync with

mk-table-sync --execute --sync-to-master localhost --databases gman --table "list,of,my,problem,tables" --wait 60

This works for all tables with auto-inc primary ID, and for the others it says:

Can't make changes on the master because no unique index exists at /usr/bin/mk-table-sync line 7733. while doing db123.problem on localhost

How to fix this in another way? I do only want the slave to be synced with the proper data from master. If possible, i'd prefer doing this with mk-table-sync instead of LOCK, dump, insert the dump on the slave which is really old-fashioned and not very useful use-case.

Thanks for tips on maakit.

mysql

1 Answers

  1. If the indexed column in question has an index but is not defined as a UNIQUE INDEX, mk-table-sync will not know it is UNIQUE and will regard the index as a secondary index. You would just have to redefine the index to be UNIQUE and run mk-table-sync again.

    SUGGESTION : Please run mk-table-sync on each individual table but not with --execute

    mk-table-sync --print --sync-to-master localhost --databases gman --table tblname --wait 60 > tblname.sql

    That way you can look the SQL before excuting it on the Slave.

    If the Slave is also a Master, block the Slave from Replicating

    echo "SET SQL_LOG_BIN=0;" > tblname.sql

    mk-table-sync --print --sync-to-master localhost --databases gman --table tblname --wait 60 >> tblname.sql

    • 0