From: Dominik Klein
Date: July 20 2006 11:00am
Subject: Re: Problems with synchronisation master -> slave
List-Archive: http://lists.mysql.com/mysql/200100
Message-Id: <44BF6262.1000904@in-telegence.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Thomas

> I try to configure replication from master to one slave for a database
> with various table types (InnoDB, MyISAM) without stopping the master.
> 
> Therefore I make a mysqldump on the slave from master with this options:
>     mysqldump
>         -h master-db \
>         -udummyuser \
>         -pdummypass \
>         -v \
>         --all-databases \
>         --disable-keys \
>         --quick \
>         --single-transaction \

Single-transaction only affects innodb tables.

As single-transaction sets --skip-lock-tables automatically, 
MyISAM-tables are not locked during the dump.
I think this is why you get the "row exists already" problem.

I'd suggest dumping innodb data and myisam data separatly. One mysqldump 
WITH single-transaction (for innodb) and one without.
This leaves the problem of data being inserted in the meantime, as 
master position will vary.

No idea at hand right now :(

>         --master-data \
>     > backup.file
> 
> an read the dump to the slave with:
>     mysql \
>         -u dummyuser \
>         -pdummypass \
>     < backup.file
> 
> When I take a look to the backup.file, I see a line like this:
>    CHANGE MASTER TO MASTER_LOG_FILE='webdb1-bin.000170',
> MASTER_LOG_POS=151635461;
> caused by the option "--master-data".
> 
> But when I start the slave, there are soon the error message, that the
> replication process will insert new row to a table, where this row exists
> already.
> 
> Next try was, to delete all data in slave and then start replication from
> the master from the beginning on (master-bin.000001), because we haven't
> deleted any binlogs on master. After some time there appears an error
> message, that there was an unsuccessful insert to an table, which doesn't
> exist. Also the schema/catalog for this new table doesn't exist.
> 
> The question is now, why the creation of the new schema/catalog and the
> table wasn't logged in the binlogs, so that they are not created via
> replication before some inserts/updates are processed on them. 

Maybe someone disabled log-bin temporarily when creating the db/table 
(for tests or whatever) and forgot about this when inserting data later.

> Are the
> binlogs not consistent? By the way, the "problem tables" are of type
> MyISAM.

"Problem tables" = binlogged, but non-existent tables?
or
"Problem tables" = "row exists problem"-tables?

Regards
Dominik