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/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> ABQ-SERVER# mysql --execute 'SELECT MAX(timestamp) FROM kinton.event;' 2022-08-03 06:20:48 LEADER> 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.

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

   Code Block
   LEADER> ABQ-SERVER# mysql --execute "SHOW STATUS LIKE 'Binlog_snapshot_file%';" 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.

   Code Block
   REPLICA> REPORTING+BILLING# mysql --execute "SHOW STATUS LIKE 'Binlog_snapshot_file';"

5. Stop the replication process in the replica database:

   Code Block
   REPLICA> REPORTING+BILLING# mysql --execute 'STOP SLAVE;'

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

   Code Block

   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 mysqldump --routines --triggers --routines --triggersdatabases kinton kinton_accounting > ~/kinton_and_kinton_accounting.sql-$(date +%Y%m%d_%H%M%S).sql ABQ-SERVER# scp ~/kinton*sql* root@<reporting+billing-server>:~ ABQ-SERVER# mysql --execute 'UNLOCK TABLES;'

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

   Code Block
   REPLICA> REPORTING+BILLING# mysql --execute 'DROP DATABASE IF EXISTS kinton; QueryDROP OK,DATABASE 354 rows affected (11.133 sec) REPLICA> DROP DATABASE IF EXISTS kinton_accounting;' Query OK, 36 rows affected (11.831 sec)

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

   Code Block
   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/_and_kinton_accounting.sql-yyyymmdd_hhmmss.sql

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

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

10. In the leader database, get the log position

    Code Block
    ABQ-SERVER# mysql --execute show master status; +----------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------------------+----------+--------------+------------------+ | master-bin.000015 | 145849 | | | +----------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

    In the replica database, start the replication again:

    Code Block
    REPORTING+BILLING# mysql 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:

...