Page Comparison

...

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 SELECT MAX(timestamp) FROM 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.

   Code Block
   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

2. From the replica database (typically hosted on the Reporting-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.

   Code Block
   REPLICA> 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.
   Copy the name of the file.

   Code Block
   LEADER> SHOW STATUS LIKE 'Binlog_snapshot_file'; master-bin.000015

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.

   Code Block
   REPLICA> SHOW STATUS LIKE 'Binlog_snapshot_file';

5. Stop the replication process in the replica database:

   Code Block
   REPLICA> STOP SLAVE;

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

   Code Block

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

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

   Code Block
   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)

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

   Code Block
   Reporting-billing#REPORTING+BILLING# mysql --execute 'CREATE DATABASE kinton; CREATE DATABASE kinton_accounting;' Reporting-billing#REPORTING+BILLING# mysql kinton < /root/ToBeDeleted/kinton.sql-20220803yyyymmdd_030550hhmmss Reporting-billing#REPORTING+BILLING# mysql kinton_accounting < /root/ToBeDeleted/kinton_accounting.sql-20220803yyyymmdd_030429hhmmss

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

   Code Block
   Reporting-billing#REPORTING+BILLING# mysql --execute 'SELECT MAX(timestamp) FROM kinton.event;' 2022-08-03 06:20:48 Reporting-billing#REPORTING+BILLING# mysql --execute 'SELECT MAX(startTime) FROM kinton_accounting.accounting_event_detail;' 2022-08-03 02:00:00

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

    Code Block
    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;

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 has been 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.