Setup MySQL Master-Slave replication in Debian 8

This short howto should also fit for other distros, perhaps with some differences in the paths!

Master

  • In /etc/mysql/my.cnf change bind-address to 0.0.0.0
  • Set a unique server-id
  • log_bin=mysql-bin
  • log_error=mysql-bin.err
  • binlog_do_db=your-db
  • Login to mysql and create a replication user
  • mysql> grant replication slave on *.* to ‚replication’@slave-ip identified by ‚password‘;
  • service mysql restart
  • Dump your DB (if already exists) or create it as stated in binlog_do_db on both sides
  • Login and write down the result of show master status;

Slave 

  • In /etc/mysql/my.cnf change bind-address to 0.0.0.0
  • Set a unique server-id
  • replicate-do-db as above
  • log_bin=mysql-bin
  • log_error=mysql-bin.err
  • service mysql restart
  • Login to mysql and:
  • CHANGE MASTER TO MASTER_HOST=’master-ip‘,MASTER_USER=’replication‘, MASTER_PASSWORD=’password-from-master‘, MASTER_LOG_FILE=’mysql-bin.output-from-master‘, MASTER_LOG_POS=  output-from-master;
  • START SLAVE;
  • Check with SHOW SLAVE STATUS\G; (Slave_IO_Running: Yes and Slave_SQL_Running: Yes)

 

 

  • Go to Master, add a new table and check if it’s created on the Slave
  • Thats all! 🙂

 

References

https://support.rackspace.com/how-to/set-up-mysql-master-slave-replication/

https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

https://www.thomas-krenn.com/de/wiki/MySQL_Replikation