Portal Developer migrating from one mysql instance to another mysql instance
Article ID: 233062
Updated On:
Products
Issue/Introduction
We need to move the databases from our Portal Developer to another mysql server
Old server: mysql1 IP 192.yyy.z.1
New server: mysql2 IP 192.yyy.z.2
Environment
API Portal 5.0 CR01
Resolution
Migrating mysql 5.7 / 8.0 to a new instances of mysql 5.7 / 8.0 - steps
Steps 1 shutdown portal
# docker stack rm portal
Step 2 dump all databases - MUST include routines to include PROCEDURES and FUNCTIONS
mysql 5.7 and 8.0
# mysqldump -u root -p --routines --all-databases > portal.all-db-backup-1.sql
Step 3 Copy portal.all-db-backup.sql to new mysql instance
Step 4 Create user with grants (below is for mysql 5.7 different for mysql 8 - see portal docs)
Mysql 5.7
CREATE USER '<username>'@'%' IDENTIFIED BY '<password>'; GRANT ALL PRIVILEGES ON *.* TO '<username>'@'%' IDENTIFIED BY '<password>'; FLUSH PRIVILEGES;
Mysql 8.0
CREATE USER '<username>'@'%' IDENTIFIED BY '<password>'; GRANT ALL PRIVILEGES ON *.* TO '<username>'@'%';
Step 5 restore all DB from older mysql
# mysql -u root -p < portal.all-db-backup.sql
Step 6 Verify mysql my.cnf file has all the proper settings
mysql 5.7
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
innodb_log_buffer_size=32M
innodb_log_file_size=80M
max_allowed_packet=8M
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
group_concat_max_len=512000
mysql 8.0
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8mb4
innodb_log_buffer_size=32M
innodb_log_file_size=80M
max_allowed_packet=8M
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
group_concat_max_len=512000
Step 7 restart mysql
# service mysqld restart
Step 8 Change portal.conf to point to new mysql instance
# vi conf/portal.conf
PORTAL_DOMAIN=<portal_domain>
PORTAL_LICENSE_FILENAME=/home/centos/Portal_EE_2021.xml
PORTAL_ENROLL_NOTIFICATION_EMAIL=root@<snmp_address>
PORTAL_ENABLE_ANALYTICS=y
PORTAL_DATABASE_TYPE=mysql
PORTAL_DATABASE_HOST=<mysql_hostname_OR_IP>
PORTAL_DATABASE_PORT=3306
PORTAL_DATABASE_USERNAME=<username>
PORTAL_DATABASE_PASSWORD=<password>
Step 9 restart portal.sh
# ./portal.sh
Watch for service to start
# watch docker service ls
Check the size of databases, they won’t be exact but show all contain data
Old mysql instance: <old_mysql_portalDB>
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) |
+----------------------+------------+
| analytics | 0.12500000 |
| apim_otk_db | 0.35937500 |
| druid | 0.45312500 |
| information_schema | 0.15625000 |
| integration_core | 0.23437500 |
| integration_runscope | 0.06250000 |
| mysql | 2.71057415 |
| performance_schema | 0.00000000 |
| portal | 3.18750000 |
| rbac | 0.23437500 |
| sys | 0.01562500 |
| tenant_provisioning | 0.42187500 |
+----------------------+------------+
12 rows in set (0.04 sec)
New mysql instance: <new_mysql_db>
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) |
+----------------------+------------+
| analytics | 0.12500000 |
| apim_otk_db | 0.35937500 |
| druid | 0.45312500 |
| information_schema | 0.15625000 |
| integration_core | 0.23437500 |
| integration_runscope | 0.06250000 |
| mysql | 2.34436703 |
| performance_schema | 0.00000000 |
| portal | 3.15625000 |
| rbac | 0.26562500 |
| sys | 0.01562500 |
| tenant_provisioning | 0.42187500 |
+----------------------+------------+
12 rows in set, 48 warnings (0.05 sec)
Feedback
