Restore database replication

Author: Jordi Bellido

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

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.
```
LEADER> SELECT MAX(timestamp) FROM kinton.event;
2022-08-03 06:20:48

LEADER> SELECT MAX(startTime) FROM kinton_accounting.accounting_event_detail;
2022-08-03 02:00:00
```
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.
```
REPLICA> SELECT MAX(startTime) FROM kinton_accounting.accounting_event_detail;
2022-06-28 23:00:00
```
In the leader database, find the binary log file that is currently in use.
Copy the name of the file.
```
LEADER> SHOW STATUS LIKE 'Binlog_snapshot_file';
master-bin.000015
```
Check if there is a binary log last that was last used in the replica database. This command will probably return an empty result.
```
REPLICA> SHOW STATUS LIKE 'Binlog_snapshot_file';
```
Stop the replication process in the replica database:
```
REPLICA> STOP SLAVE;
```

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

ABQ-SERVER# mysql --execute 'FLUSH TABLES WITH READ LOCK;'
ABQ-SERVER# mysqldump --routines --triggers kinton            > ~/kinton.sql-$(date +%Y%m%d_%H%M%S)
ABQ-SERVER# mysqldump --routines --triggers kinton_accounting > ~/kinton_accounting.sql-$(date +%Y%m%d_%H%M%S)
ABQ-SERVER# scp ~/kinton*sql* root@<reporting+billing-server>:~
ABQ-SERVER# mysql --execute 'UNLOCK TABLES;'

From the replica database, drop the affected databases:

REPLICA> DROP DATABASE kinton;
Query OK, 354 rows affected (11.133 sec)

REPLICA> DROP DATABASE kinton_accounting;
Query OK, 36 rows affected (11.831 sec)

Immediately, import the dumps from the leader database:

REPORTING+BILLING# mysql --execute 'CREATE DATABASE kinton; CREATE DATABASE kinton_accounting;'

REPORTING+BILLING# mysql kinton < /root/kinton.sql-yyyymmdd_hhmmss

REPORTING+BILLING# mysql kinton_accounting < /root/kinton_accounting.sql-yyyymmdd_hhmmss

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

REPORTING+BILLING# mysql --execute 'SELECT MAX(timestamp) FROM kinton.event;'
2022-08-03 06:20:48

REPORTING+BILLING# mysql --execute 'SELECT MAX(startTime) FROM kinton_accounting.accounting_event_detail;'
2022-08-03 02:00:00

In the replica database, start the replication again:

REPLICA> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

REPLICA> CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000015', MASTER_LOG_POS=145849731;

REPLICA> START SLAVE;

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.