Mysql Replication

I’ve been working on creating a new master-slave setup out of an existing monolithic database server. I’ve been following Digital Ocean’s guide to replicatoin, but have had to figure out some other steps and caveats.

Random notes:
* relay-log needs to be defined
* if the slave gets into a bad state, you can use ‘RESET SLAVE;’ to get it healthy.

Here are those steps:

  1. create the backup on the original server: mysqldump --all-databases -u root -p > ./full-backup-2015.02.03-bak
  2. import it on the new master: cat full-backup-2015.02.03-bak | mysql -p
  3. re-export it with the “master data”: mysqldump --all-databases --master-data -u root -p > full-backup-2015.02.03-bak
  4. modify the master data in the backup to include the user/password that will be doing the replication
  5. make sure ansible ACTUALLY adds the replication user with the correct host
  6. run START SLAVE on the slave
  7. if ansible user creation makes it so there are slaves users on both servers, do: stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; to skip it.

Leave a Reply

Your email address will not be published. Required fields are marked *