5.2.3.3.1.23. Database replication issues
If you are facing errors with worker database replication, in the NextGen Portal, navigate to Configure > Farms > Cores.
5.2.3.3.1.23.1. Ansible playbook
Alternatively, there is an Ansible playbook that allows you to specifically address and resolve Database replication issues.
5.2.3.3.1.23.1.1. Configuration steps
Before executing the playbook, ensure to perform the Ansible configuration steps, following the steps described in the Node deployment section:
Built the inventory file with all the nodes of your environment.
5.2.3.3.1.23.1.2. Launch the playbooks
From the ON Principal node, execute the following command:
ansible-playbook all_config.yml --tags "replication"
The output, will look similar to the following one:
[root@server ~]# ansible-playbook all_config.yml --tags "replication"
PLAY [Initial Configuration for all the hosts] ****************************************************************************************************************
TASK [Gathering Facts] ****************************************************************************************************************************************
ok: [onworker]
ok: [onprincipal]
ok: [onanalytics]
ok: [onvpngw01]
ok: [onvpngw02]
PLAY [all] ****************************************************************************************************************************************************
TASK [Gathering Facts] ****************************************************************************************************************************************
ok: [onprincipal]
PLAY [all] ****************************************************************************************************************************************************
TASK [Gathering Facts] ****************************************************************************************************************************************
ok: [onprincipal]
ok: [onworker]
TASK [principal_config : Enable and restart MariaDB] **********************************************************************************************************
changed: [onprincipal]
TASK [principal_config : MYSQL -> GRANT REPLICATION SLAVE ON *.* TO 'on_worker'@'<worker_ip>' IDENTIFIED BY '<password>'] *************************************
changed: [onprincipal] => (item=onworker)
TASK [principal_config : MYSQL -> GRANT ALL PRIVILEGES ON opennac.* TO 'admin'@'<worker_ip>' identified by '<admin_password>'] ********************************
changed: [onprincipal] => (item=onworker)
TASK [principal_config : MYSQL -> GRANT ALL PRIVILEGES ON opennac.* TO 'nagios'@'<worker_ip>' identified by '<admin_password>'] *******************************
changed: [onprincipal] => (item=onworker)
TASK [principal_config : MYSQL -> flush privileges] ***********************************************************************************************************
changed: [onprincipal]
TASK [principal_config : Export data] *************************************************************************************************************************
changed: [onprincipal]
TASK [principal_config : Fetch the dump_db.gz file to later send it to the slaves] ****************************************************************************
changed: [onprincipal]
TASK [worker_config : Restart mariadb] ************************************************************************************************************************
changed: [onworker]
TASK [worker_config : Check if the database exists] ***********************************************************************************************************
ok: [onworker]
TASK [worker_config : Check db_check_result] ******************************************************************************************************************
ok: [onworker] => {
"msg": {
"changed": false,
"executed_queries": [
"SHOW DATABASES LIKE '********';"
],
"failed": false,
"query_result": [
[
{
"Database (opennac)": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER"
}
]
],
"rowcount": [
1
]
}
}
TASK [worker_config : Stop slave if the database exists] ******************************************************************************************************
ok: [onworker]
TASK [worker_config : Drop database opennac;] *****************************************************************************************************************
changed: [onworker]
TASK [worker_config : Create empty opennac database;] *********************************************************************************************************
changed: [onworker]
TASK [worker_config : Copy file to worker] ********************************************************************************************************************
changed: [onworker]
TASK [worker_config : Reset Slave] ****************************************************************************************************************************
ok: [onworker]
TASK [worker_config : Configure the replication] **************************************************************************************************************
changed: [onworker]
TASK [worker_config : Import the opennac.gz database file] ****************************************************************************************************
changed: [onworker]
TASK [worker_config : Start the slave -> start slave;] ********************************************************************************************************
changed: [onworker]
TASK [worker_config : Check errors -> show slave status\G] ****************************************************************************************************
changed: [onworker]
TASK [worker_config : Show status] ****************************************************************************************************************************
ok: [onworker] => {
"msg": [
"*************************** 1. row ***************************",
" Slave_IO_State: Waiting for master to send event",
" Master_Host: onprincipal",
" Master_User: wk-dev-qa",
" Master_Port: 3306",
" Connect_Retry: 60",
" Master_Log_File: mysql-bin.000382",
" Read_Master_Log_Pos: 380939",
" Relay_Log_File: mysql-relay-bin.000002",
" Relay_Log_Pos: 380012",
" Relay_Master_Log_File: mysql-bin.000382",
" Slave_IO_Running: Yes",
" Slave_SQL_Running: Yes",
" Replicate_Do_DB: ",
" Replicate_Ignore_DB: ",
" Replicate_Do_Table: ",
" Replicate_Ignore_Table: ",
" Replicate_Wild_Do_Table: ",
" Replicate_Wild_Ignore_Table: ",
" Last_Errno: 0",
" Last_Error: ",
" Skip_Counter: 0",
" Exec_Master_Log_Pos: 380939",
" Relay_Log_Space: 380321",
" Until_Condition: None",
" Until_Log_File: ",
" Until_Log_Pos: 0",
" Master_SSL_Allowed: No",
" Master_SSL_CA_File: ",
" Master_SSL_CA_Path: ",
" Master_SSL_Cert: ",
" Master_SSL_Cipher: ",
" Master_SSL_Key: ",
" Seconds_Behind_Master: 0",
" Master_SSL_Verify_Server_Cert: No",
" Last_IO_Errno: 0",
" Last_IO_Error: ",
" Last_SQL_Errno: 0",
" Last_SQL_Error: ",
" Replicate_Ignore_Server_Ids: ",
" Master_Server_Id: 1",
" Master_SSL_Crl: ",
" Master_SSL_Crlpath: ",
" Using_Gtid: No",
" Gtid_IO_Pos: ",
" Replicate_Do_Domain_Ids: ",
" Replicate_Ignore_Domain_Ids: ",
" Parallel_Mode: optimistic",
" SQL_Delay: 0",
" SQL_Remaining_Delay: NULL",
" Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates",
" Slave_DDL_Groups: 0",
"Slave_Non_Transactional_Groups: 0",
" Slave_Transactional_Groups: 13"
]
}
PLAY [all] ****************************************************************************************************************************************************
TASK [Gathering Facts] ****************************************************************************************************************************************
ok: [onanalytics]
PLAY [all] ****************************************************************************************************************************************************
TASK [Gathering Facts] ****************************************************************************************************************************************
ok: [onvpngw01]
ok: [onvpngw02]
PLAY [all] ****************************************************************************************************************************************************
PLAY [all] ****************************************************************************************************************************************************
PLAY RECAP ****************************************************************************************************************************************************
onanalytics : ok=2 changed=0 unreachable=0 failed=0 skipped=25 rescued=0 ignored=0
onprincipal : ok=10 changed=7 unreachable=0 failed=0 skipped=17 rescued=0 ignored=0
onvpngw01 : ok=2 changed=0 unreachable=0 failed=0 skipped=25 rescued=0 ignored=0
onvpngw02 : ok=2 changed=0 unreachable=0 failed=0 skipped=25 rescued=0 ignored=0
onworker : ok=15 changed=8 unreachable=0 failed=0 skipped=12 rescued=0 ignored=0