mysql 8 database replication software gateway 10.1
Article ID: 248529
Updated On:
Products
Issue/Introduction
We are looking for guidance and steps on how to create SSG DB replication for mysql 8 on a Software gateway 10.1
Environment
Release : 10.0, 10.1
Component :
Resolution
mysql 8.0 Replication (v8.0.28)
Master = <Master-1>
Slave = <Master-2>
MASTER-1 /etc/my.cnf
# Uncommment log-bin and log_bin_trust_function_creators
# if a clustered db server
log-bin=/var/lib/mysql/ssgbin-log
# disable_log_bin
log_bin_trust_function_creators=1
log_slave_updates=OFF
gtid_mode=ON
enforce-gtid-consistency=ON
# uncomment the next item on 1st db master server
server-id=1
# uncomment the next item on 2nd db master servers
#server-id=2
relay-log = /var/lib/mysql/ssgrelay-bin
relay-log-index = /var/lib/mysql/ssgrelay-bin.index
# Slave reliability items:
slave-skip-errors=126,1053,1105,1129,1158,1159,1160,1161
slave-net-timeout=30
slave_exec_mode=IDEMPOTENT
MASTER-2 /etc/my.cnf
# Uncommment log-bin and log_bin_trust_function_creators
# if a clustered db server
log-bin=/var/lib/mysql/ssgbin-log
# disable_log_bin
log_bin_trust_function_creators=1
log_slave_updates=OFF
gtid_mode=ON
enforce-gtid-consistency=ON
# uncomment the next item on 1st db master server
# server-id=1
# uncomment the next item on 2nd db master servers
server-id=2
relay-log = /var/lib/mysql/ssgrelay-bin
relay-log-index = /var/lib/mysql/ssgrelay-bin.index
# Slave reliability items:
slave-skip-errors=126,1053,1105,1129,1158,1159,1160,1161
slave-net-timeout=30
slave_exec_mode=IDEMPOTENT
Restart mysql
Create the two users and set privileges for replication
Created on Master-1
# mysql -u root -p -e "CREATE USER 'repluser'@'<Master-2>' IDENTIFIED BY '7layer'"
# mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'repluser'@'<Master-2>'"
Created on Master-2
# mysql -u root -p -e "CREATE USER 'repluser'@'<Master-1>' IDENTIFIED BY '7layer'"
# mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'repluser'@'<Master-1>'"
Check each server
# mysql -u root -p -e "select user, host, Insert_priv, Grant_priv from mysql.user"
Enter password:
+------------------+--------------------------------+-------------+------------+
| user | host | Insert_priv | Grant_priv |
+------------------+--------------------------------+-------------+------------+
| gateway | % | N | N |
| mysql.infoschema | localhost | N | N |
| mysql.session | localhost | N | N |
| mysql.sys | localhost | N | N |
| root | localhost | Y | Y |
| repluser | <Master-1> | Y | N |
+------------------+--------------------------------+-------------+------------+
Execute on both mysql servers these commands:
Stops replication:
# mysqladmin -u root -p stop-replica
Enter password:
Replication stopped
Reset the master configuration on both nodes:
# mysql -u root -p -e "reset master"
Reset the slave configuration on both nodes:
# mysql -u root -p -e "reset slave; reset slave all"
Stop the API Gateway 'ssg' service on both nodes: service ssg stop
# /opt/SecureSpan/Gateway/runtime/bin/gateway.sh stop
Prepare on Master-2 SSG DB
Is it exist DROP ssg
[<Master-2># mysql -u root -p -e "DROP database ssg"
CREATE ssg
[<Master-2># mysql -u root -p -e "CREATE database ssg"
Sync the two databases (dump into a file from Master-1 onto Master-2 file system, then import):
Dump the Master-1 SSG DB from the Master-2 to be imported into Master-2
[<Master-2>]#mysqldump -h <Master-1> -u repluser -p --get-server-public-key --set-gtid-purged=on --routines ssg > ssg.sql
Import SSG DB
[<Master-2>]# mysql -u root -p ssg < ssg.sql
RESET MASTER on Master-2 after DB import
[<Master-2>]# mysql -u root -p -e "reset master"
Need to get replication position on each mysql instance and set
Position on Master-1 from Master-2
[<Master-2>]# mysql -h <Master-1> -u repluser -p -e "SHOW MASTER STATUS\G"
Enter password:
*************************** 1. row ***************************
File: ssgbin-log.000001
Position: 25307
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: e32ebad7-7d4d-11ec-9e14-fa163ec4f93c:1-34
Set replication to proper position on Master-2 for Master-1:
[<Master-2>]# mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='<Master-1>',MASTER_USER='repluser',MASTER_PASSWORD='7layer',MASTER_LOG_FILE='ssgbin-log.000001',MASTER_LOG_POS=25307"
Position on Master-2 from Master-1
Get position:
[<Master-1>]# mysql -h <Master-2> -u repluser -p -e "SHOW MASTER STATUS\G"
Enter password:
*************************** 1. row ***************************
File: ssgbin-log.000001
Position: 517
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 7900f351-8a98-11ec-b693-fa163e728780
Set replication to proper position on Master-1 for Master-2:
[<Master-1>]]# mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='<Master-2>',MASTER_USER='repluser',MASTER_PASSWORD='7layer',MASTER_LOG_FILE='ssgbin-log.000001',MASTER_LOG_POS=517"
START REPLICATION ON BOTH Master’s
[<Master-1>]# mysqladmin -u root -p start-replica
Enter password:
Replication started
[<Master-2>]# mysqladmin -u root -p start-replica
Enter password:
Replication started
Start SSG
[<Master-1> #/opt/SecureSpan/Gateway/runtime/bin/gateway.sh start
Check Status
[<Master-2>]# mysql -u root -p -e "SHOW SLAVE STATUS\G"
Enter password:
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: <Master-1>
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ssgbin-log.000001
Read_Master_Log_Pos: 157
Relay_Log_File: ssgrelay-bin.000002
Relay_Log_Pos: 327
Relay_Master_Log_File: ssgbin-log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[<Master-1>]# mysql -u root -p -e "SHOW SLAVE STATUS\G"
Enter password:
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: <Master-2>
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ssgbin-log.000001
Read_Master_Log_Pos: 157
Relay_Log_File: ssgrelay-bin.000002
Relay_Log_Pos: 327
Relay_Master_Log_File: ssgbin-log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Useful queries:
Check tables sizes
mysql> SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+----------------------+--------------+
| Database name | Size (MB) |
+----------------------+--------------+
| mysql | 4.12500000 |
| information_schema | 0.00000000 |
| performance_schema | 0.00000000 |
| sys | 0.01562500 |
| ssg | 210.95312500 |
| portal | 2.87500000 |
| rbac | 0.23437500 |
| apim_otk_db | 0.21875000 |
| analytics | 0.09375000 |
| druid | 0.31250000 |
| tenant_provisioning | 0.42187500 |
| integration_core | 0.23437500 |
| integration_runscope | 0.06250000 |
| otk_db | 0.46875000 |
| ssgtmp | 4.06250000 |
| mag | 0.10937500 |
+----------------------+--------------+
16 rows in set (0.46 sec)
Check login users
[<Master-1> ~]# mysql -u root -p -e "select id, user,host, db from information_schema.processlist"
Enter password:
+-------+-----------------+---------------------------------------+--------+
| id | user | host | db |
+-------+-----------------+---------------------------------------+--------+
| 56480 | gateway | localhost:54818 | ssg |
| 51240 | system user | | NULL |
| 56472 | gateway | localhost:54784 | ssg |
| 56488 | root | localhost | NULL |
| 51241 | system user | | NULL |
| 56473 | gateway | localhost:54786 | ssg |
| 51242 | system user | | NULL |
| 56476 | gateway | localhost:54802 | ssg |
| 51244 | repluser | <Master-2>:44212 | NULL |
| 5 | event_scheduler | localhost | NULL |
| 51237 | system user | connecting host | NULL |
| 56477 | gateway | localhost:54804 | ssg |
| 56485 | gateway | localhost:54847 | ssg |
| 51238 | system user | | NULL |
| 56486 | otkuser | localhost:54856 | otk_db |
| 56478 | gateway | localhost:54814 | ssg |
| 51239 | system user | | NULL |
| 56487 | otkuser | localhost:54854 | otk_db |
+-------+-----------------+---------------------------------------+--------+
Feedback
