Skip to content

MySQL Replication to Slaves

All the slaves need to contact the master to get the MySQL updates and keep in sync. Once it's setup you will achieve instant DNSupdates across your cluster.

Master Replication Setup

This part needs to be performed on the master DNS server only.

1. Edit /etc/my.cnf with the following settings:

server-id = 1
log-bin = mysql-bin
log-bin-index = mysql-bin.index
expire-logs-days = 10
max-binlog-size = 100M
binlog-do-db = powerdns

2. Restart MySQL:

service mysqld restart

MySQL Replication User

1. A new SQL user needs to be created on the master:

mysql -u root -p

2. After entering the SQL root password you get next information:

create user pdnsslave;
create user 'pdnsslave'@'*';
grant replication slave on *.* to pdnsslave identified by 'ANEWPASSOWRD';
flush privileges;

3. Next we need some information from the masters SQL:

show master status \G

4. You will see information below. Make a note of the File and Position values:

*************************** 1. row ***************************
File: mysql-bin.000001
Position: 99
Binlog_Do_DB: powerdns
Binlog_Ignore_DB:
1 row in set (0.00 sec)
 

Slave Replication Setup

This part needs to be performed on the slave dns server(s) only.

1. Edit /etc/my.cnf with the following settings:

server-id=2
master-connect-retry=60
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
replicate-do-db=powerdns


The server-id variable needs to be different on each of the slave dns servers. i.e server-id=2, server-id=3


2. Restart MySQL:

service mysqld restart

Request Replication Access from the Master

1. Login to MySQL on the dns slave:

mysql -u root -p

2. After entering the password you get next information:

change master to
master_host='DNS_MASTER_IP',
master_user='pdnsslave',
master_password='ANEWPASSOWRD',
master_log_file='mysql-bin.000001',
master_log_pos=99;
start slave;

3. You can see the status using the following command:

show slave status \G

Replication is setup on the slave. Just follow the instructions again for any remaining dns slaves.

This Tutorial can be used as reference on MySQL replication - http://tonkersten.com/2012/01/110-mysql-database-replication/

Back to top