Reload a Query Server Database from Another Query Server

This procedure reloads a corrupted or backlevel query server's LNP database by copying another query server's LNP database. If the LSMS is configured with multiple query servers and at least one is currently synchronized, it is recommended to reload a query server from another query server (instead of from the LSMS) to prevent NPAC-to-network element traffic from being interrupted.

Note: Replication on the query server may be interrupted while a snapshot of the LNP database occurs. The time required to accomplish this procedure depends on the bandwidth of your network and the amount of data to be reloaded.

Note: 1 through 10 pertain to the query server that is directly connected to the LSMS. 11 through 14 pertain to the query server being reloaded.

Start the MySQL command-line utility on the query server that is directly connected to the LSMS:
# cd /opt/mysql/mysql/bin
# ./mysql -u root -p

Enter password:

<Query Server’s MySQL user root password>
Stop MySQL replication: (When replication is off, the query server data is not updated and is not kept in synchronization with the LSMS.)
mysql> stop slave;
Obtain a read lock and flush table cache information:
(The flush writes changes to tables on disk. The read lock prohibits changes to be made to tables but continues to allow other threads to read from them.)
mysql> FLUSH TABLES WITH READ LOCK;

Display the file name and current position of the binary log:

mysql> SHOW MASTER STATUS;

Output similar to the following displays:

+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+

Record the values in the File and Position columns, which display the file name and current position of the binary log, respectively.
In the example above, the file name is mysql-bin.003, and the current position is 73. These values are necessary to properly start the slave process on the query server that is being reloaded.
Exit the MySQL command-line utility:
mysql> exit;
Shutdown the MySQL server on the query server that is directly connected to the LSMS:
# ./mysqladmin -u root -p shutdown
Enter password:

<Query Server’s MySql root user password>
Remove all existing compressed snapshot files (if any):
# rm /usr/mysql1/mysql-snapshot*
Create a snapshot of the query server’s copy of all the LSMS data.
Create a compressed snapshot file for the Supplemental database:
Note: GNU tar (gtar) must be installed on the Query Server prior to any single region exceeding 60 million TNs.

# tar -cvf - /usr/mysql1/supDB/* | gzip > /usr/mysql1/mysql-snapshot-supDB.tar.gz
Create compressed snapshot files for each of the regional databases. Replace <regionDB> with the regional database name (for example, CanadaDB, MidwestDB, and so forth).
# tar -cvf - /usr/mysql1/<regionDB>/* | gzip > /usr/mysql1/mysql-snapshot-<regionDB>.tar.gz
At the query server that is directly connected to the LSMS, restart the MySQL daemon:
# cd /opt/mysql/mysql/bin
# ./mysqld_safe &
Shut down the MySQL server on the query server being reloaded:
# ./mysqladmin -u root -p shutdown
Enter password:

<Query Server’s MySQL user root password>
From the master query server, use the file transfer protocol (FTP) to move the snapshot data of the master server into the/usr/mysql1 directory on the query server being reloaded:
# cd /usr/mysql1
# ftp <IP address of the Query Server being reloaded>

ftp> cd /usr/mysql1

ftp> bin

ftp> prompt

ftp> mput mysql-snapshot*.tar.gz

ftp> bye
On the query server being reloaded, extract the snapshot data from the archive tar file of the directly connected query server's data.
Ensure that the privileges on the files and directories are correct. The user which MySQL runs as needs to be able to read and write to them, just as on the master. Perform the following commands:
# cd /usr/mysql1

# gunzip -c mysql-snapshot-supDB.tar.gz | tar -xvf -

# rm mysql-snapshot-supDB.tar.gz

Now, extract the data for the snapshot files for each of the LSMS regions starting with the largest regions first. Replace <regionDB> with the regional database name (for example, CanadaDB, MidwestDB, and so forth). Be sure to remove the compressed snapshot files after each database is extracted to guarantee that sufficient disk space is available for all databases:#
# gunzip -c mysql-snapshot-<regionDB>.tar.gz | tar -xvf -

# rm mysql-snapshot-<regionDB>.tar.gz
Start the MySQL daemon on the query server being loaded.

# cd /opt/mysql/mysql/bin

# ./mysqld_safe --skip-slave-start

Note: It is important to start the daemon with the --skip-slave-start option so that replication does not start automatically.
Start the mysql command-line utility on the query server that is being loaded:
# ./mysql -u root -p
Set the binary log position using information that you recorded in 5.
mysql> CHANGE MASTER TO
MASTER_LOG_FILE='<recorded_log_file_name>',

MASTER_LOG_POS=<recorded_log_position>;

For <recorded_log_file_name>, use the value you recorded for the file name in 5, and for , use the value you recorded for the binary position in 5. For example, using the values shown in the example in 4, enter the following command to set the binary log position:
mysql> CHANGE MASTER TO

MASTER_LOG_FILE='mysql-bin.003',

MASTER_LOG_POS=73;
Start replication on the query server that has been loaded:
mysql> start slave;
The query server should connect to the master server (LSMS or another query server) and catch up on any updates that occurred since the snapshot was taken.