Update MySQL replication server position

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?


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.