3.5.3 Procedure 6 Backup LSMS DB

Table 3-11 Procedure 6 - Backup LSMS DB

S T E P # This procedure outlines the steps to backup the LSMS DB. Estimated time: 90 minutesNote: The estimated time may differ depending on the DB size.

☐1.

LSMS Active server:

Log in to the server as the user “root”

 
Login: root
Password: <root_password>

☐2.

LSMS Active server:

Record DB counts

 # lsmsdb –c counts

3.

LSMS Active and Standby Server: Disable LSMS backup on both A and B servers

Run the following command on both LSMS A and B to disable LSMS backup:

# sed -i '/^#/! {/lsmsbkp_wrapper/ s/^/#/}'
/etc/cron.d/lsmsbkp.cron

☐4..

LSMS Active server:

Remove existing DB snapshots

 
# rm –rf /var/TKLC/lsms/free/mysql-snapshot-* 
# rm –rf /var/TKLC/lsms/free/snapinfo.sql

☐5.

LSMS Active server:

Enable “QUERY_SERVER” and “RESYNCDB_QUERY_SERVER” Feature

Note: Even if Query Server is not connected this step is required to take backup

Run below command to verify “QUERY_SERVER” and “RESYNCDB_QUERY_SERVER” feature is enabled:

# lsmsdb -c features | grep –w QUERY_SERVER
# lsmsdb -c features | grep –w RESYNCDB_QUERY_SERVER

If these features are not enabled then execute the below commands to enable them:

# su – lsmsadm
$ dbcfginternal QUERY_SERVER Y

Provide the “Customer Service ID”

$ dbcfginternal RESYNCDB_QUERY_SERVER Y

Provide the “Customer Service ID”

$ exit

☐6.

LSMS Active server:

Backup the LSMS DB

 
# lsmsdb –c snapshot
WARNING: This command may cause a brief interruption in traffic being sent from the NPAC to connected network elements and local LSMS provisioning may be INTERRUPTED.

Do you want to continue? [Y/N]Y

Creating snapshot of the database partition, please wait... 
File descriptor 5 (socket:[34104267]) leaked on lvcreate invocation. Parent PID 28676: /usr/TKLC/lsms/tools/lsmsdb

Logical volume "dbbackup" created
The database is available to the application again. 
Disk snapshot created successfully.
Snapshot mounted successfully. 
Created snapinfo.sql file successfully 
MidAtlanticDB/ 
MidAtlanticDB/NumberPoolBlock.frm 
MidAtlanticDB/ServiceProvNetwork.MYD
.
.
.
Logical volume "dbbackup" successfully removed

Note: The execution time of the above command shall vary according to the DB size.

Verify that the following snapshot files are created at /var/TKLC/lsms/free directory:

  • mysql-snapshot-noreplDB.tar.gz
  • mysql-snapshot-supDB.tar.gz
  • mysql-snapshot-<regionDB>.tar.gz
  • snapinfo.sql

☐7.

LSMS Active server:

Verify the snapshot files for all existing NPAC regions

Run the following command and verify that the snapshot files are created for all the NPAC regions listed in the command output.

Note: The below command shows only the regions for which the DB exists. 

# lsmsdb -c dblist 
CanadaDB 
MidAtlanticDB 
MidwestDB 
NortheastDB 
ReplTestDB 
SoutheastDB 
SouthwestDB 
WestCoastDB 
WesternDB
logDB 
mysql 
noreplDB

performance_schema 
supDB

☐8.

LSMS Active server:

Take MySQL dump of supDB.

Note: Take a note of EMS configuration (IP, user, Group) and delete the same on LSMS.

Run the following command on LSMS Active server CLI to take MySQL dump of the supDB database.

# mysqldump -udbroot -p[dbroot_password] supDB >
/var/TKLC/lsms/free/supDBdump.sql

Note: Below warning message can be ignored if displayed: 

Warning: Using a password on the command line interface can be insecure.
Note: Do not run the following command when upgrading from
14.0.0.X to 14.0.0.Y. This command is only required while upgrading from 13.X release.

Edit supDBdump.sql to fix Authorization table 
# sed -i 's/function/functions/g' supDBdump.sql

☐9.

LSMS Active server:

Take MySQL dump of mysql.user.

Create MySQLUserGrants.sql file listing all the users and their privileges using the following shell script MySQLUser.sh in the root directory:

File: MySQLUser.sh

MYSQL_CONN="-udbroot -ppassword"
mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW CREATE USER ''',user,'''@''',host,''';') FROM
mysql.user WHERE user<>''" | sed '/mysql/d' | mysql
${MYSQL_CONN} --skip- column-names -A | sed 's/$/;/g' | sed "s/IDENTIFIED WITH 'mysql_native_password' AS/IDENTIFIED BY/g" | sed "s/IDENTIFIED WITH 'mysql_native_password'/IDENTIFIED BY/g" | sed "s/REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT
UNLOCK//g" > /var/TKLC/lsms/free/MySQLUserGrants.sql

mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM
mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip- column-names -A | sed 's/$/;/g' |sed 's/IDENTIFIED BY PASSWORD/IDENTIFIED BY/g' | sed '/mysql/d' >>
/var/TKLC/lsms/free/MySQLUserGrants.sql

Note: Edit MYSQL_CONN for the proper connection properties and this will generate the MySQLUserGrants.sql file with grant statements from the 5.7 machine that you can source into the 8.0 machine.

Run the MySQLUser.sh file to generate MySQLUserGrants.sql

# chmod +x MySQLUser.sh 
# ./MySQLUser.sh

Run command to edit MySQLUserGrants.sql

# sed -i 's/CREATE USER/CREATE USER IF NOT EXISTS/g'
MySQLUserGrants.sql

The content of the MySQLUserGrants.sql will be:

GRANT USAGE ON *.* TO 'lsmsadm'@'%' IDENTIFIED BY '57570851ac3cc01499';

Edit the .sql file and replace the password of all the users with plain text password to contain something like:

GRANT USAGE ON *.* TO 'lsmsadm'@'%' IDENTIFIED BY 'password- in-plain-text';

Run below commands to recover plain text password for default lsms users:

For dbroot user:

/usr/TKLC/lsms/tools/pass_fetch pass1

For materepl user:

/usr/TKLC/lsms/tools/pass_fetch pass3 -> materepl

For cmdline user:

/usr/TKLC/lsms/tools/pass_fetch pass4 For other default users:
/usr/TKLC/lsms/tools/pass_fetch pass2

Remove entry for `lsmsrepl`@`<lsmsqs IP>` from the MySQLUserGrants.sql. This will be added during LSMSQS configuration.

Note: There is no way to reverse the hash to recover the plain text. So the customer has to provide us the plain text passwords for users which were added by customer.

Edit the MySQLUserGrants.sql file and edit the second line: 

CREATE USER IF NOT EXISTS 'dbnopriv'@'%'IDENTIFIED BY;
Here ‘’ are missing after IDENTIFIED BY ;
Example: CREATE USER IF NOT EXISTS 'dbnopriv'@'%'
IDENTIFIED BY ‘’;
For migration from 14.0.0.X to 14.0.0.Y edit the file MySQLUserGrants.sql

Remove below two lines in MySQLUserGrants.sql:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER IF NOT EXISTS, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `dbroot`@`%` WITH GRANT OPTION;
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,A UTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG
_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_K EY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS
,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN, GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_RED O_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_A DMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_G ROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABL ES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMI N,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADM IN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_AD
MIN ON *.* TO `dbroot`@`%` WITH GRANT OPTION;

Add below line at the end of MySQLUserGrants.sql:

“GRANT ALL PRIVILEGES ON *.* TO 'dbroot'@'%' WITH GRANT OPTION;”

☐10.

LSMS Active server:

Log into the Active LSMS server GUI

Login to LSMS GUI as lsmsall user.

☐11.

LSMS Active server:

Record the configured MySQL Port

Go to “Admin -> MySQL Port -> View” and record the configured MySQL Port.

☐12.

LSMS Active server:

Record the configured ELAP Credentials

Go to “Configure -> LNP System -> EMS -> View” and record the configured ELAP Credentials.

☐13.

LSMS Active and Standby Server

Copy and Backup license file when upgrading from LSMS 14.0.0.X release

Login to LSMS CLI and take back up of the below 2 files from both Active and Standby server:

/usr/TKLC/osi/conf/license
/usr/local/netech/etc/license

These licenses will be required after full migration is complete

This procedure is complete!