Versions Compared

Old Version 2

changes.mady.by.user Jordi Bellido

Saved on

compared with

New Version 3

changes.mady.by.user MS

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  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> 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+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+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 --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;'

  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# 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

  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)

  11. 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;

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

...