Opsview - Rebuild database replication

Rebuilding database replication is typically a system administration task and may not be considered within the scope of regular Opsview support. If you encounter any errors or unexpected output while following this article, you should consult your database administrators.

Prerequisites

• Your Opsview environment is either High Availability or Disaster Recovery

Conventions

The following terms are used as shorthand for this article:

Process

You may find it easier to open two terminal sessions: one for your Primary DB host and the other for your Secondary DB host.

1. Log in to your deployment host as root.
2. Obtain the database root password from your user_secrets.yml file:

grep opsview_database_root_password /opt/opsview/deploy/etc/user_secrets.yml

Make a note of this password, you will need it later. Once you have noted the database root password, you can log out of the deployment host.

3. Log in to the Primary DB host as a root user.

4. Ensure there is enough disk space for a full database dump on the Primary DB host:

df -h

5. Create a named screen session on the Primary DB host for dumping the database:

screen -S database_dump

screen -r database_dump

6. Export the Primary DB database using mysqldump inside the screen session on the Primary DB host:

(echo "SET foreign_key_checks = 0;"; mysqldump -u root -p --default-character-set=utf8mb4 --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A) | gzip -9 > /var/tmp/opsviewdump.sql.gz

Enter the database root password when prompted. This export may take a few hours depending on the size of the database.

7. Copy the database dump (/var/tmp/opsviewdump.sql.gz) to the Secondary DB host from the Primary DB host:

scp /var/tmp/opsviewdump.sql.gz root@<secondary_db_host>:/var/tmp/

Replace <secondary_db_host> with the hostname of your Secondary DB host.

9. Log in to the MySQL console as root on the Primary DB:

mysql -u root -p

Enter the database root password when prompted.

10. Stop the replication slave on the Primary DB:

STOP SLAVE;

11. Confirm that replication has stopped on the Primary DB:

SHOW SLAVE STATUS\G

If replication has stopped, you should see the following in the command output:

Slave_IO_Running: No
Slave_SQL_Running: No

12. Log in to the Secondary DB host as a root user.
13. Log in to the MySQL console as root on the Secondary DB host:

mysql -u root -p

Enter the database root password when prompted.

14. Stop the replication slave on the Secondary DB:

STOP SLAVE;

15. Confirm that replication has stopped on the Secondary DB:

SHOW SLAVE STATUS\G

If replication has stopped, you should see the following in the command output:

Slave_IO_Running: No
Slave_SQL_Running: No

16. Drop the Opsview databases on the Secondary DB and then log out of the MySQL console:

DROP DATABASE opsview;
DROP DATABASE runtime;
DROP DATABASE odw;
DROP DATABASE dashboard;
exit

17. Create a named screen session on the Secondary DB host for importing the database:

screen -S database_import

18. Import the database dump using mysql inside the screen session on the Secondary DB host:

( echo "SET FOREIGN_KEY_CHECKS=0;"; zcat /var/tmp/opsviewdump.sql.gz ) | mysql -u root -p --max_allowed_packet=32M

Enter the database root password when prompted.

19. After the database import completes, check the current log file position for the database replication. You can check this on either the Primary DB or the Secondary DB host as both should be in the same position.

zgrep -A 5 "Position to start replication" /var/tmp/opsviewdump.sql.gz

You should see output similar to the following:

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='<log file>',MASTER_LOG_POS=<log position>

Note the values in the place of <log file> and <log position>, these values are needed later.

20. Log in to the MySQL console as root on the Secondary DB host:

mysql -u root

Enter the database root password when prompted.

21. Set the Primary DB as the master for replication and set the replication log file and position on the Secondary DB:

CHANGE MASTER TO MASTER_HOST='<Primary DB IP>', MASTER_PORT=13307, MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='<log file>', MASTER_LOG_POS=<log position>;

Replace the variables in angle brackets (<>) according to the following table:

22. Start the replication slave on the Secondary DB.

START SLAVE;

23. Confirm that replication has started on the Secondary DB:

SHOW SLAVE STATUS\G

If replication has started, you should see the following in the command output:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

24. Check the master bin log file and position on the Secondary DB:

SHOW MASTER STATUS\G

If the master bin log file and position are set correctly, you should see output like this:

Master_Log_File: <log file>
Read_Master_Log_Pos: <log position>

Where <log file> and <log position> match the values from step 19 above.

25. Log in to the MySQL console as root on the Primary DB host:

mysql -u root -p

Enter the database root password when prompted.

26. Set the replication log file and position on the Primary DB:

CHANGE MASTER TO MASTER_LOG_FILE='<log file>', MASTER_LOG_POS=<log position>;

Where <log file> and <log position> match the values from step 19 above.

27. Start the replication slave on the Primary DB:

START SLAVE;

28. Confirm that replication has started on the Primary DB:

SHOW SLAVE STATUS\G

If replication has started, you should see the following in the command output:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes