Score:1

Update MySQL replication server position

gh flag

I have been trying to set up a replication of a large DB (90GB)

I have created a backup using mysqldump --single-transaction

I have then restored that on the replication server. I have then enabled replication, but I have accidentally clicked Reset slave in phpMyAdmin and its set the position back to basically 0.

So if my understanding is correct, it's trying to rebuild the DB on the replication server from the beginning.

Because it kept erroring out on duplicates that already existed in the db (because I restored the backup first), I have temporality added:

slave-skip-errors=1062
skip-slave-start

to the my.ini file to skip the duplication. But even after this, it's about 4 months behind the master (9983704 seconds).

Is there a way I can move the position on the slave up so it only rebuilds from the last few days?

Score:1
us flag

You should use mysqldump --single-transaction --master-data to dump the database from master. The --master-data tells mysqldump to include log position in the resulting file.

After you restore the correct dump to slave, you can START SLAVE and replication will proceed normally.

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.