Restore database replication

Author: Jordi Bellido

This page describes how to restore database replication from the main DB server to the replica server.

1. From the leader database (hosted on the Abiquo API or datanode server), check if the max timestamp is up to date with the time now, from the tables kinton.event and/or kinton_accounting.accounting_event_detail. Please note that one of these two databases might not exist in your environment in the replica database.

   ABQ-SERVER# mysql --execute 'SELECT MAX(timestamp) FROM kinton.event;' 2022-08-03 06:20:48 ABQ-SERVER# mysql --execute 'SELECT MAX(startTime) FROM kinton_accounting.accounting_event_detail;' 2022-08-03 02:00:00

2. From the replica database (typically hosted on the reporting+billing server), check if the max timestamp from the table kinton.event from the replica database is up to date. In this case, it is not.

   REPORTING+BILLING# mysql --execute 'SELECT MAX(startTime) FROM kinton_accounting.accounting_event_detail;' 2022-06-28 23:00:00

3. In the leader database, find the binary log file that is currently in use and the MySQL binary log position. Copy the name of the file and the position value:

   ABQ-SERVER# mysql --execute "SHOW STATUS LIKE 'Binlog_snapshot_%';" master-bin.000015 145849731

4. Check if there is a binary log last that was last used in the replica database. This command will probably return an empty result.

5. Stop the replication process in the replica database:

6. From the leader database, create two SQL dump files and copy them to the server reporting+billing:

7. From the replica database, drop the affected databases:

8. Immediately, import the dumps from the leader database:

9. Check that the replica database is now up to date with the main database:

10. In the replica database, start the replication again:

11. Monitor the status of the replication by executing this SQL command: SHOW SLAVE STATUS\G:

• The variables Slave_IO_Running and Slave_SQL_Running will both be set to Yes, because they now depend on data incoming from the main DB server. This is expected.

• The variable Seconds_Behind_Master will be set from NULL to 0, because is in sync with the main DB server again.

Now the MySQL replication is working again, you can retrieve and process any missing billing data.