Procedure 5: Start/Stop Replication

5.3.1 Procedure 5: Start/Stop Replication

Table 5-3 Procedure 5: Start/Stop Replication

S T E P # This procedure is used to start/stop replication from OCLSMS to Query Server.Check off ( ✓ ) each step as it is completed. Boxes have been provided for this purpose under each step number. SHOULD THIS PROCEDURE FAIL, CONTACT MY ORACLE SUPPORT AND ASK FOR ASSISTANCE.

S T E P #	This procedure is used to start/stop replication from OCLSMS to Query Server.Check off ( ✓ ) each step as it is completed. Boxes have been provided for this purpose under each step number. SHOULD THIS PROCEDURE FAIL, CONTACT MY ORACLE SUPPORT AND ASK FOR ASSISTANCE.
1. ☐	Linux server: Stop MySQL replication	Log into Query Server as root. Go to directory /usr/bin `# cd /usr/bin` Check if mysql process is running: `# ps -eaf \| grep mysql` If it is not running, directly go to step 2 of this procedure. If it is running, stop MySQL replication by stopping slave: `# ./mysql -u root -p Enter password:<Query server's MySQL root user password> mysql> stop replica;` Verify that MySQL replication is no longer running using the SHOW REPLICA STATUS command (ensure the Replica_IO_Running and Replica_SQL_Running column values are set to No). `mysql> show replica status\G; Empty set , 1 warning (0.00 sec)` Exit the MySQL command-line utility: `mysql> exit;`
2.	Linux server: Create Regional Databases	Databases and tables should be created before restoring data on MySQL 8. Run following command for all regions whose DB needs to be replicated on QS Create Database `# ./mysql -uroot -p -e "create database dbname"` dbname is the name of regional database. For example: For Canada following command will be run `# ./mysql -uroot -p -e "create database CanadaDB"` Create Table Schema `# ./mysql -uroot -p dbname < /usr/regiondb.sql` dbname is the name of regional database. For example: For Canada following command will be run `# ./mysql -uroot -p CanadaDB < /usr/regiondb.sql` Note: Repeat these steps for all regional databases
3.	Linux Server: Create supDB and noreplDB	Databases and tables should be created before restoring data on MySQL 8. Run following command for supDB, noreplDB and resyncDB before restoring data Create supDB and noreplDB Databases `# ./mysql -uroot -p -e "create database supDB" # ./mysql -uroot -p -e "create database noreplDB" # ./mysql -uroot -p -e "create database ResyncDB"` Create Table Schema for supDB and noreplDB `# ./mysql -uroot -p supDB < /usr/supdb.sql # ./mysql -uroot -p noreplDB < /usr/noreplDB.sql # ./mysql -uroot -p ResyncDB < /usr/resyncdb.sql`
4. ☐	Linux server: Stop MySQL	Stop MySQL. `# cd /usr/bin # ./mysqladmin shutdown -p Enter password:<Query server's MySQL root user password>` If unable to shutdown mysql using above command, use the following command: `# kill <pid of mysqld_safe> <pid of mysqld>`
5. ☐	OCLSMS server: Create query server user on OCLSMS	`# lsmsdb –c addrepluser –h <IP/Hostname of QS> –p <mysqlpwd>` Note: If using hostname to configure QS on LSMS Associate the names of the query server hosts with their Internet Protocol (IP) addresses: To do this, add an IP address and hostname pair for each query server to the /etc/hosts file on both the primary and secondary LSMS servers. The hostname of the query server will be used to identify each query server when reporting on its status.
6. ☐	OCLSMS server: Create and copy the snapshots from the OCLSMS server.	Please refer to [3]OCLSMS 14.1 Alarms and Maintenance Guide, Current Version, Oracle, Appendix E (Query Server Maintenance Procedures), section “Reload a Query Server Database from the OCLSMS" in the section 1.2.1.
7. ☐	Linux server: Extract the snapshot data from the archive tar files copied from OCLSMS.	`# cd /usr/mysql/mysql1 # mkdir backup_temp # cd backup_temp # mv /usr/mysql/mysql1/mysql-snapshot-<regionDB>.tar.gz . # tar –xvzf mysql-snapshot-<regionDB>.tar.gz` In the above commands, replace <regionDB> with the regional database name (for example, CanadaDB). Copy .MYI and .MYD files from backup directory to DB directory `# scp <regionalDB Directory>/MY ../ <regionalDB Directory>` For example for CanadaDB `# scp CanadaDB/MY ../CanadaDB` Execute the same commands for supDB and noreplDB snapshot files.
8. ☐	Linux server: Verify ownership of database files and directories.	`# ls -ltr` If any database directories have ownership other than mysql:mysql, change them using this command: `# chown -R mysql:mysql <DB NAME>` `where <DB NAME> is supDB, noreplDB, or <region>DB,` where <region> is the name of an NPAC region. Also change the ownership of snapinfo.sql to mysql:mysql by executing the following command: `# chown mysql:mysql snapinfo.sql`
9. ☐	Linux server: Open the snapinfo.sql file	`# vi snapinfo.sql` Refer to Appendix A.1 to modify the snapinfo.sql file.
10. ☐	Linux server: Verify MySQL tables if following the upgrade procedure, otherwise skip it.	Change directory to /usr/bin `# cd /usr/bin` Restart MySQL `# su mysql # /usr/sbin/mysqld --basedir=/usr --skip-replica-start &` Start MySQL session: `# ./mysql -u root -p Enter password:<Query server's MySQL root user password>` Verify the tables present in the MySQL database: `mysql> use mysql; mysql> show tables;` Exit from the MySQL command line utility and run the below commands in case above query doesn’t return same output, otherwise continue to the next step. mysql> exit; `Run from root user: # cd /usr/bin # ./mysql_upgrade –u root –p Enter password:<Query server's MySQL root user password>` Note: Please ignore if there is any error in the output of above command and again verify MySQL tables by using step 8 of this procedure. If the output still differs then contact the Oracle Customer Care Center for assistance, otherwise continue to the next step.
11. ☐	Linux server: Create replication user	Log into Query Server as root. Change to directory /usr/bin `# cd /usr/bin` Restart MySQL `# su mysql # /usr/sbin/mysqld --basedir=/usr --skip-replica-start &` Start MySQL session: `# ./mysql -u root –p Enter password:<Query server's MySQL root user password> mysql> create user 'lsmsslave'@'localhost' identified by 'mysql123'; mysql> create user 'lsmsslave'@'%' identified by 'mysql123'; mysql> grant super,replication client on . to 'lsmsslave'@'%'; Mysql > CHANGE REPLICATION SOURCE TO GET_SOURCE_PUBLIC_KEY = 1;`
12. ☐	Linux server: Reset configuration information	`mysql> RESET BINARY LOGS AND GTIDS; mysql> reset replica;`
13. ☐	Linux server: Start replication from the correct position on the master	`mysql> source <absolute path of the snapinfo.sql file>`
14. ☐	Linux server: Start mysql slave	`mysql> start replica;`
15. ☐	Linux server: Check slave status	`mysql> show replica status\G` In the output of above command, ensure that values corresponding to columns Slave_IO_Running and Slave_SQL_Running are set to Yes.
16. ☐	Linux server: If the column value of both Slave_IO_Running and Slave_SQL_Runnin g are other than Yes, the status is not good and the error will need to be investigated.	`# vi /usr/mysql/mysql1/*.err` Look at last few lines of error log and record the errors below. Record error here: Contact the Oracle Customer Care Center and ask for assistance. Continue from step 13 of this procedure after error resolution.
17. ☐	OCLSMS server: Login to the OCLSMS Primary server and verify that Query Server is Connected.	login as: lsmsadm lsmsadm@IP's password:<Enter Password> `$ lsmsdb -c queryservers` Example: [lsmsadm@lsmspri ~]$ lsmsdb -c queryservers pc9091802.labs.nc.tekelec.com (10.75.136.183) Connected You have now completed this procedure. Query Server has started replicating data from OCLSMS.
End of Procedure

☐

Linux server: Stop MySQL replication

Log into Query Server as root. Go to directory /usr/bin

# cd /usr/bin

Check if mysql process is running:

# ps -eaf | grep mysql

If it is not running, directly go to step 2 of this procedure. If it is running, stop MySQL replication by stopping slave:
```
# ./mysql -u root -p
Enter password:<Query server's MySQL root user password>
mysql> stop replica;
```

Verify that MySQL replication is no longer running using the SHOW REPLICA STATUS command (ensure the Replica_IO_Running and Replica_SQL_Running column values are set to No).
```
mysql> show replica status\G;
Empty set , 1 warning (0.00 sec)
```

Exit the MySQL command-line utility:

mysql> exit;

Linux server: Create Regional Databases

Databases and tables should be created before restoring data on MySQL 8. Run following command for all regions whose DB needs to be replicated on QS

Create Database

# ./mysql -uroot -p -e "create database dbname"

dbname is the name of regional database.

For example: For Canada following command will be run

# ./mysql -uroot -p -e "create database CanadaDB"

Create Table Schema

# ./mysql -uroot -p dbname < /usr/regiondb.sql

dbname is the name of regional database.

For example: For Canada following command will be run

# ./mysql -uroot -p CanadaDB < /usr/regiondb.sql

Note: Repeat these steps for all regional databases

Linux Server: Create supDB and noreplDB

Databases and tables should be created before restoring data on MySQL 8. Run following command for supDB, noreplDB and resyncDB before restoring data

Create supDB and noreplDB Databases

# ./mysql -uroot -p -e "create database supDB"
# ./mysql -uroot -p -e "create database noreplDB"
# ./mysql -uroot -p -e "create database ResyncDB"

Create Table Schema for supDB and noreplDB

# ./mysql -uroot -p supDB < /usr/supdb.sql
# ./mysql -uroot -p noreplDB < /usr/noreplDB.sql
# ./mysql -uroot -p ResyncDB < /usr/resyncdb.sql

☐

Linux server: Stop MySQL

Stop MySQL.

# cd /usr/bin
# ./mysqladmin shutdown -p
Enter password:<Query server's MySQL root user password>

If unable to shutdown mysql using above command, use the following command:

# kill <pid of mysqld_safe> <pid of mysqld>

☐

OCLSMS server: Create query server user on OCLSMS

# lsmsdb –c addrepluser –h <IP/Hostname of QS> –p
<mysqlpwd>

Note: If using hostname to configure QS on LSMS

Associate the names of the query server hosts with their Internet Protocol (IP) addresses:

To do this, add an IP address and hostname pair for each query server to the /etc/hosts file on both the primary and secondary LSMS servers. The hostname of the query server will be used to identify each query server when reporting on its status.

☐

OCLSMS server: Create and copy the snapshots from the OCLSMS server.

Please refer to [3]OCLSMS 14.1 Alarms and Maintenance Guide, Current Version, Oracle, Appendix E (Query Server Maintenance Procedures), section “Reload a Query Server Database from the OCLSMS" in the section 1.2.1.

☐

Linux server: Extract the snapshot data from the archive tar files copied from OCLSMS.

# cd /usr/mysql/mysql1
# mkdir backup_temp
# cd backup_temp
# mv /usr/mysql/mysql1/mysql-snapshot-<regionDB>.tar.gz .
# tar –xvzf mysql-snapshot-<regionDB>.tar.gz

In the above commands, replace <regionDB> with the regional database name (for example, CanadaDB).

Copy *.MYI and *.MYD files from backup directory to DB directory

# scp <regionalDB Directory>/*MY* ../ <regionalDB Directory>

For example for CanadaDB

# scp CanadaDB/*MY* ../CanadaDB

Execute the same commands for supDB and noreplDB snapshot files.

☐

Linux server: Verify ownership of database files and directories.

# ls -ltr

If any database directories have ownership other than mysql:mysql, change them using this command:

# chown -R mysql:mysql <DB NAME>

where <DB NAME> is supDB, noreplDB, or <region>DB,

where <region> is the name of an NPAC region.

Also change the ownership of snapinfo.sql to mysql:mysql by executing the following command:

# chown mysql:mysql snapinfo.sql

☐

Linux server: Open the snapinfo.sql file

# vi snapinfo.sql

Refer to Appendix A.1 to modify the snapinfo.sql file.

10.

☐

Linux server: Verify MySQL tables if following the upgrade procedure, otherwise skip it.

Change directory to /usr/bin

# cd /usr/bin

Restart MySQL

# su mysql
# /usr/sbin/mysqld --basedir=/usr --skip-replica-start &

Start MySQL session:

# ./mysql -u root -p
Enter password:<Query server's MySQL root user password>

Verify the tables present in the MySQL database:
```
mysql> use mysql;
mysql> show tables;
```
Exit from the MySQL command line utility and run the below commands in case above query doesn’t return same output, otherwise continue to the next step. mysql> exit;

Run from root user:
# cd /usr/bin
# ./mysql_upgrade –u root –p
Enter password:<Query server's MySQL root user password>

Note: Please ignore if there is any error in the output of above command and again verify MySQL tables by using step 8 of this procedure. If the output still differs then contact the Oracle Customer Care Center for assistance, otherwise continue to the next step.

11.

☐

Linux server: Create replication user

Log into Query Server as root. Change to directory /usr/bin

# cd /usr/bin

Restart MySQL

# su mysql
# /usr/sbin/mysqld --basedir=/usr --skip-replica-start &

Start MySQL session:

# ./mysql -u root –p
Enter password:<Query server's MySQL root user password>
mysql> create user 'lsmsslave'@'localhost' identified by 'mysql123';
mysql> create user 'lsmsslave'@'%' identified by 'mysql123';
mysql> grant super,replication client on *.* to 'lsmsslave'@'%';
Mysql > CHANGE REPLICATION SOURCE TO GET_SOURCE_PUBLIC_KEY = 1;

12.

☐

Linux server: Reset configuration information

mysql> RESET BINARY LOGS AND GTIDS;
mysql> reset replica;

13.

☐

Linux server: Start replication from the

correct position on the master

mysql> source <absolute path of the snapinfo.sql file>

14.

☐

Linux server: Start mysql slave

mysql> start replica;

15.

☐

Linux server:

Check slave status

mysql> show replica status\G

In the output of above command, ensure that values corresponding to columns Slave_IO_Running and Slave_SQL_Running are set to Yes.

16.

☐

Linux server: If the column value of both Slave_IO_Running and Slave_SQL_Runnin g are other than Yes, the status is not good and the error will need to be investigated.

# vi /usr/mysql/mysql1/*.err

Look at last few lines of error log and record the errors below. Record error here:

Contact the Oracle Customer Care Center and ask for assistance. Continue from step 13 of this procedure after error resolution.

17.

☐

OCLSMS server: Login to the OCLSMS Primary server and verify that Query Server is Connected.

lsmsadm@IP's password:<Enter Password>

$ lsmsdb -c queryservers

Example:

[lsmsadm@lsmspri ~]$ lsmsdb -c queryservers pc9091802.labs.nc.tekelec.com (10.75.136.183) Connected

You have now completed this procedure. Query Server has started replicating data from OCLSMS.

End of Procedure