Monday, September 30, 2024

PowerDNS and MariaDB (MySQL) Replication - What do you do when things get out of whack?

I use PowerDNS to manage my fairly extensive home lab environment's DNS. Because DNS is needed for not only the lab, but also so that my wife and daughter can watch TV or to use any other 'net-based resource, keeping things up and running is critical. Because of that, I have 3 seperate DNS servers, linked via MariaDB replication. Generally, this has been very reliable, and the configuration is much simpler than my previous setup, which used PowerDNS replication.

However, a couple of times, now, things have gotten out of sync. And, because it happens very rarely, I always forget how to get things resynced. Lucky me, though, user David Espart posted an excellent step-by-step process for getting things going again, here.

I'll repeat the steps below, in case Stack Overflow ever goes away or loses the content:

This is the full step-by-step procedure to resync a master-slave replication from scratch:

At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;


And copy the values of the result of the last command somewhere.

Without closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump -u root -p --all-databases > /a/path/mysqldump.sql

Now you can release the lock, even if the dump hasn't ended yet. To do it, perform the following command in the MySQL client:

UNLOCK TABLES;


Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Open a connection to mysql and type:

STOP SLAVE;

Load master's data dump with this console command:

mysql -uroot -p < mysqldump.sql

Sync slave and master logs:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;


Where the values of the above fields are the ones you copied before.

Finally, type:

START SLAVE;

To check that everything is working again, after typing:

SHOW SLAVE STATUS;

you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes


That's it!

Hope this helps someone, some day, or just jogs my own failing memory.

No comments:

Post a Comment