[Pdns-users] Master slave replication

[Alex Pavlov]

Hi Donald!

 

This is how it works on my setup. I run MariaDB as Generic-MySQL mode
"gmysql" in PowerDNS.

In pdns.conf should add:

launch=gmysql

gmysql-host=localhost

gmysql-dbname=powerdns

gmysql-user=powerdns_user

gmysql-password=powerdns_password

gmysql-dnssec=yes

 

Now go to MySQL settings on MASTER 192.168.0.1 (where slave will be
192.168.0.2):

#Binlog should be in ROW or MIXED format.

# Login to MySQL and set in mysql line:

mysql -u root -p

mysql> SET GLOBAL binlog_format = 'ROW';

# OR

mysql> SET GLOBAL binlog_format = 'MIXED'    

mysql>quit

#

# Edit your  /etc/mysql/my.cnf  file

nano /etc/mysql/my.cnf

#

[mariadb]

log-bin = mysql-bin

bind-address=0.0.0.0      /this allows to listen from any IP, ETH ports and
Loopback

server_id=1

log-basename=master1

skip-networking=0

 

# at mysql prompt enter:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_host'
IDENTIFIED BY 'password'; 

FLUSH PRIVILEGES;

#

# or enter alternative commands

GRANT REPLICATION SLAVE ON *.* TO replication_user;

GRANT ALL PRIVILEGES ON *.* TO 'replication_user'@'192.168.0.2' IDENTIFIED
BY 'password' WITH GRANT OPTION;

#

#To check replication status:

SHOW SLAVE STATUS \G

#

### Now, allow 127.0.0.1, and slave SQL 192.168.0.2 ###

iptables -A INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT

iptables -A INPUT -p tcp -s 192.168.0.2 --dport 3306 -j ACCEPT

## Block all other connections to 3306 ##

iptables -A INPUT -p tcp --dport 3306 -j DROP

#Save iptables

sudo iptables-save > /etc/iptables/rules.v4

#

service mysql restart

 

###The Slave settings 192.168.0.2 where maters is 192.168.0.1

#

#Now we head over to the Slave. Open the /etc/mysql/my.cnf file and add the
following at the bottom:

[mariadb]

server-id = 2

bind-address=0.0.0.0

#issue sql restart

service mysql restart

#

# Login MySQL

mysql -u root -p

 

mysql > CHANGE MASTER TO master_host="192.168.0.1",
master_user="replication_user", master_password="password"
master_use_gtid=slave_pos;

#Start slave replication:

START SLAVE;

#To check replication status:

SHOW SLAVE STATUS;

 

# alternatively you can start replication with the following command from
client (192.168.0.2) to connect to 192.168.0.1:3306, enter:

>mysql -u replication_user -h 192.168.0.1 -P 3306 -p pasword

#

 

Regards

Alex.

From: Pdns-users [mailto:pdns-users-bounces at mailman.powerdns.com] On Behalf
Of Donald Jayawardena
Sent: Monday, December 18, 2017 8:52 AM
To: pdns-users at mailman.powerdns.com
Subject: [Pdns-users] Master slave replication

 

Hi,

 

I have an issue replicating data through powerdns on two mysql databases.

 

I could replicate data when I update data manually on the master. But data
does not get thorugh to slave if I use powerdns (using curl) to update slave
data records from the master.

 

The error says:

 

Could not execute Delete_rows event on table powerdns.records; Can't find
record in 'records', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000001, end_log_pos 1655

 

Please let me know if any solution for this.

 

Donald Jayawardena

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20171218/f14b516c/attachment.html>