mysql 8 database replication software gateway 10.1
search cancel

mysql 8 database replication software gateway 10.1

book

Article ID: 248529

calendar_today

Updated On:

Products

CA API Gateway

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 |

+-------+-----------------+---------------------------------------+--------+