How to Move Gateway Database Tables from the System Tablespace to Its Own Tablespace after Upgrade from 9.2 to 9.3

1. Block MySQL traffic between appliances
2. Back up databases
3. Shrink the existing ibdata file and move to own tablespace
4. Unblock MySQL traffic
5. Reinitialize replication

Block MySQL traffic between appliances

1. Log onto the primary Gateway database node as the ssgconfig user.
2. Select Option 3: Use a privileged shell (root).
3. Close the port used for MySQL for all connections originating from the secondary Gateway database node.
   • NOTE: The value of "SECONDARY-IP" will need to be the IP address of the management interface of the secondary Gateway database node.
     
     iptables -I INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3306 -j REJECT
     iptables -I INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3307 -j REJECT

• Log onto the secondary Gateway database node as the ssgconfig user.
• Select Option 3: Use a privileged shell (root).
• Close the port used for MySQL for all connections originating from the primary Gateway database node.
  • NOTE: The value of "PRIMARY-IP" will need to be the IP address of the management interface of the primary Gateway database node.
    
    iptables -I INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3306 -j REJECT
    iptables -I INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3307 -j REJECT

Back up databases

1. Stop the Gateway service: service ssg stop
2. Back up the SSG database alone on the primary Gateway: mysqldump --databases ssg | gzip > /root/ssg-db-backup.sql.gz
3. Back up all databases on the primary Gateway as an extra precaution: mysqldump --all-databases ssg | gzip > /root/all-db-backup.sql.gz

Shrink the existing ibdata file and move to own tablespace

1. Stop the MySQL service on the primary Gateway: service mysql stop
2. Remove the existing ibdata files: rm -rf /var/lib/mysql/ib*
3. Remove the ssg folder: rm -rf /var/lib/mysql/ssg
4. Start the MySQL service on the primary Gateway: service mysql start
5. Extract the contents of the SSG database backup: gzip -d /root/ssg-db-backup.sql.gz
6. Restore the SSG database: mysql < /root/ssg-db-backup.sql
7. Start the Gateway service on the primary Gateway: service ssg start

Unblock MySQL traffic

1. Stop replication on each Gateway database node from the privileged shell: mysql -e "stop slave"
2. Reopen MySQL communication on the primary Gateway database node:
   • NOTE: The value of "SECONDARY-IP" will need to be the IP address of the management interface of the secondary Gateway database node.
     
     iptables -D INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3306 -j REJECT
     iptables -D INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3307 -j REJECT
3. Reopen MySQL communication on the secondary Gateway database node:
   • NOTE: The value of "PRIMARY-IP" will need to be the IP address of the management interface of the primary Gateway database node.
     
     iptables -D INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3306 -j REJECT
     iptables -D INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3307 -j REJECT

Reinitialize replication

1. Reinitialize replication on the secondary node: /opt/SecureSpan/Appliance/bin/create_slave.sh
   • ​​​​NOTE: Specify the FQDN, credentials, and port of the primary Gateway database node. When prompted, clone the database from the primary Gateway database node.
2. ​Reinitialize replication on the primary node: /opt/SecureSpan/Appliance/bin/create_slave.sh
   • NOTE: Specify the FQDN, credentials, and port of the secondary Gateway database node. When prompted, do not clone the database from the secondary Gateway database node.
3. Verify successful initialization of replication: mysql -e "show slave status\G" | grep ": Yes"

   • ​​NOTE: The console or terminal should return with the following information exactly as depicted:
     
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes