Reset PowerDNS MySQL Master Slave DNS Replication.

Although PowerDNS in native mode with a MySQL backend is usually very stable and hardly causes any problems, occasionally master slave replication between MySQL servers fails. This is usually a result of a failure in the network connection between the master and slave server, which in our case is usually through a SSH tunnel (autossh). Ensure that these issues are resolved first before restoring the replication.

The best solution to quickly restore replication is to reset the slave server using a MySQL dump file created on the master server.

Start by checking the status on the master node.

mysql -e "SHOW MASTER STATUS\G"

Which should give an output similar to this:

*************************** 1. row ***************************
            File: mysql-bin.000059
        Position: 124033
    Binlog_Do_DB: pdns
Binlog_Ignore_DB:

Note “File: mysql-bin.000059” and “Position: 124033” which we need in a next step.

Now flush and lock all the tables in the PowerDNS database in this case “pdns”.

mysql pdns -e "FLUSH TABLES WITH READ LOCK;"

create a MySQL dump file.

mysqldump pdns > /tmp/pdns.sql

Unlock the tables.

mysql -e "UNLOCK TABLES;"

Return to the slave node! and import the dump file.

Since we have already set up an SSH tunnel to the master on the slave node for MySQL replication, the MySQL dump file can be retrieved using those credentials.

scp -P <SSH_PORT> <SSH_USER>@<MASTER_NODE_IP_ADDRESS>:/tmp/pdns.sql /tmp

Of course, modify the “SSH_PORT” “SSH_USER” and “MASTER_NODE_IP_ADDRESS” with the correct credentials of your master node.

Stop the MySQL slave server. We assume in this example that the database used by PowerDNS on the slave node is also “pdns”.

mysql pdns -e "STOP SLAVE;"

Import the MySQL dump file in

mysql pdns < /tmp/pdns.sql

Reset the MySQL slave server.

mysql -e "RESET SLAVE;"

Update the MySQL master settings with the “File” and “Postion” information we noted previously from the master node (mysql-bin.000059″ and “Position: 124033”).

mysql -e "CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000059', MASTER_LOG_POS=124033;"

Start the MySQL slave server.

mysql -e "START SLAVE;"

Check the status of the MySQL slave to verify that the data is indeed correct and replication has been restored.

mysql -e "SHOW SLAVE STATUS\G"

Which should give an output similar to this:

*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 127.0.0.1
                   Master_User: pdns-slave
                   Master_Port: 33061
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000059
           Read_Master_Log_Pos: 194049
                Relay_Log_File: slave-relay-bin.000003
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000059
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: pdns
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 124033
               Relay_Log_Space: 864
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

If all is well, now all changes on the PowerDNS master server are immediately propagated to the slave node and the DNS servers have the same records.

To reduce the chance of synchronization failures between master and slave, it is important to add some parameters to the mysqld.cnf configuration file.

Open the configuration file on the master.

vi /etc/mysql/mysql.conf.d/mysqld.cnf

And check if the following parameters are set. If not add or change them and restart MySQL.

innodb_flush_log_at_trx_commit  = 1
sync_master_info                = 1
sync_binlog                     = 1
binlog_checksum                 = CRC32

If you made any changes, restart MySQL to implement them.

systemctl restart mysql

Open the configuration file on the slave.

vi /etc/mysql/mysql.conf.d/mysqld.cnf

And check if the following parameters are set. If not add or change them and restart MySQL.

slave_sql_verify_checksum       = ON

If you made any changes, restart MySQL to implement them.

systemctl restart mysql