We have simple replication set up with 1 master and 1 slave. However, setting this up (after restart for instance) is always a difficult task. The moment between taking my database dump, noting the position of the binlog on the master and starting the slave back up, it seems data has already moved on (new inserts, updates, ...) and my slave is not able to start up.
The database has a lot of constraints between tables and this often is the reason data is not able to sync: a log for a specific consumers doesn't get synced because the consumer doesn't exist on the slave.
I can, sometimes, work around this by executing:
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; SELECT SLEEP(2); SHOW SLAVE STATUS;
But it feels very hack-y and a more a game of luck than anything else.
How should I go about this? I'm not really willing to shut down the master database in this process. How can I copy the data and start the slave at the exact position as the copied data?