Database Replication Guide for

DNA-M
Document id: DNA-M-ReplGuide

Version 36.0, Rev A, 2021-04-26

Copyright
© Copyright 2020 Infinera Corporation. All rights reserved. This Manual is the property of Infinera Corporation and is
confidential. No part of this Manual may be reproduced for any purposes or transmitted in any form to any third party
without the express written consent of Infinera. Infinera makes no warranties or representations, expressed or implied,
of any kind relative to the information or any portion thereof contained in this Manual or its adaptation or use, and
assumes no responsibility or liability of any kind, including, but not limited to, indirect, special, consequential or
incidental damages, even if Infinera, its affiliates, or its suppliers have been advised of the possibility of such damages,
(1) for any errors or inaccuracies contained in the information or (2) arising from the adaptation or use of or the inability
to use the information or any portion thereof including any application of software referenced or utilized in the Manual.
The information in this Manual is subject to change without notice.
Trademarks
Infinera, Infinera Intelligent Transport Networks, I-PIC, IQ NOS, FlexILS, DTN-X, DTN, ATN, FastSMP and logos that
contain Infinera are trademarks or registered trademarks of Infinera Corporation in the United States and other
countries. All other trademarks in this Manual are the property of their respective owners.
Infinera DTN-X, DTN, FlexILS, Cloud Xpress, XT, and ATN Regulatory Compliance
FCC Class A
This device complies with Part 15 of the FCC rules. Operation is subject to the following two conditions: (1) this device
may not cause harmful interference, and (2) this device must accept any interference received, including interference
that may cause undesired operation. Modifying the equipment without Infinera’s written authorization may result in the
equipment no longer complying with FCC requirements for Class A digital devices. In that event, your right to use the
equipment may be limited by FCC regulations, and you may be required to correct any interference to radio or
television communications at your own expense.
DOC Class A
This digital apparatus does not exceed the Class A limits for radio noise emissions from digital apparatus as set out in
the interference-causing equipment standard titled “Digital Apparatus," ICES-003 of the Department of
Communications. Cet appareil numérique respecte les limites de bruits radioélectriques applicables aux appareils
numériques de Classe A prescrites dans la norme sur le matériel brouilleur: "Appareils Numériques," NMB-003 édictée
par le Ministère des Communications.
Class A ITE
This is a Class A product based on the standard of the VCCI Council. If this equipment is used in a domestic
environment, radio interference may occur, in which case, the user may be required to take corrective actions.

Warning
This is a class A product. In a domestic environment this product may cause radio interference in which case the user
may be required to take adequate measures.
FDA
This product complies with the DHHS Rules 21CFR 1040.10 and 1040.11, except for deviations pursuant to Laser
Notice No. 50, dated June 24, 2007.
Contents
1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2. System Recommendations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
3. Programs, Directories and other info used in this guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
4. Setting up Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
4.1. Disabling the DNA-M Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
4.2. Configuration of the Master Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
4.3. Configuration of the Slave Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
4.4. Synchronization of the Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
4.5. Starting the Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
5. Monitoring Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
5.1. Verifying the Connection between the Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
5.1.1. Troubleshooting tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
5.2. Monitoring the Status of the Slave Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
5.3. Comparing the Status of the Master and Slave Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.4. Process List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
6. Hands-on configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
6.1. Reconfiguring the Slave Server Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
6.2. Changing location of the Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
7. Recovering a MySQL Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
7.1. Reconnecting Replication Threads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
7.1.1. Skipping Duplicate Entries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
7.2. Slave Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
8. Restarting Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
8.1. Resetting the Master and Slave Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
9. DNA-M Server upgrade and Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
10. Backing up the Slave Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
10.1. Manual Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
10.2. Automated Backups. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
10.2.1. Scheduling backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
10.3. Restoring the Master DNA-M database from a backup. . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
11. Switching Master Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
11.1. Promoting the Slave Server to become a Master Server . . . . . . . . . . . . . . . . . . . . . . . . . . 25
11.2. Configuring the old Master Server as a Slave Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Database Replication Guide for DNA-M 1. Introduction

1. Introduction
This document describes how to set up and initiate database replication for the DNA-M, version
36.0.0 on both Linux and Windows.

The DNA-M Server stores information about the network and the nodes in a database. With
replication, the Slave database synchronizes with the Master database and has no direct contact
with the network nodes. The advantage of replication is that the Slave database continuously
synchronizes itself towards the Master database without affecting it. It is then safe to pause the
Slave database, to make backups of it for safe keeping, without affecting the operations of the
Master database.

Database replication is based on the Master Server keeping track of all changes to its database
(updates, deletes, etc) in the binary log. The Slave Server reads the queries that are saved in the
Master Server’s binary log and then executes the same queries on its own database, thereby
synchronizing it with the Master Server’s database.

 Database replication is not required for the use of DNA-M and is not related to the
use of a DNA-M backup server.

DNA-M-ReplGuide | 2021-04-26 1 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 2. System Recommendations

2. System Recommendations
It is recommended to place the replication master and slave machines on a local network (LAN) to
ensure replication stability. The bandwidth used during replication depends on transmission network
size and topology on the one hand and enabled DNA-M features on the other hand.

For large transmission networks (> 500 NEs), it is recommended to use a

 dedicated disk for the database data directory, refer to the DNA-M Server
Administration Guide.

The recommended database for use with DNA-M is MariaDb. For version information and
instructions on MySQL or MariaDB installation, refer to the DNA-M Installation Guide.

DNA-M-ReplGuide | 2021-04-26 2 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 3. Programs, Directories and other info used in this guide

3. Programs, Directories and other info used in this

guide
This guide can be used for setting up replication on both Linux and Windows. In principle, the
procedure is identical but database directory paths and tool locations will differ. Each section will
describe differences between OS if and when necessary. This section lists some of the basic
differences.

The database command line client is started by entering the following in a command prompt on
Linux:

# mysql -u root -p

On Windows_ the above command must be run from the console, which usually can be opened from
the start menu:

Start Menu > All Programs > Accessories > Command Prompt

Configuration settings of the database can be found in the following files:

On Linux the affected configuration file is /etc/mysql/[Link]

On Windows the configuration file is called [Link] and usually found together with the database
program in the installation directories.

The following example values will be used throughout this guide and should be substituted
accordingly for your setup.

Master server IP: [Link]

Slave server IP: [Link]

Replication account username: user

Replication account password: password

DNA-M-ReplGuide | 2021-04-26 3 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 4. Setting up Replication

4. Setting up Replication
You have a MySQL Master Server that is continuously receiving data from the DNA-M Server. This
chapter describes how to set up a database replication server, a Slave Server, that is synchronized
with the Master Server’s database and that can act as a backup if something was to happen to the
Master Server.

Figure 1. Replication

For each step in this guide make sure that you always distinguish between the Master Server and
the Slave Server. It is always stated on which Server changes are made.

4.1. Disabling the DNA-M Server

If a DNA-M server is installed on the slave server (reasons for this are described in the Section
Switching Master Server) make sure that the DNA-M server is not running. Also remember to disable
automatic start of the DNA-M server at boot time in the slave server.

On Linux, disabling the DNA-M server start at boot is done as follows:

# chkconfig tnmserver off

# chkconfig tnmserver --del

On Windows Server, this can be done from the Services Utility, by changing the startup type for the
DNA-M Server process.

Start → Control Panel → System and Security → Administrative Tools → Services

Change the startup type by right clicking on the service name TnmServer and select Properties. On
the next screen look for where it says Startup type, change it to Manual and then press OK.

4.2. Configuration of the Master Server

A user account for the replication Slave has to be created on the Master Server. This replication user
account is restricted to the local network for security reasons. Start the MySQL command line client
on the Master Server and enter the following commands:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'172.16.100.%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

DNA-M-ReplGuide | 2021-04-26 4 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 4. Setting up Replication

 Write down the values entered for user and password.

The file my_replication-[Link], which is available in the DNA-M scripts directory, contains
settings for the Master Server. Open the file my_replication-[Link], copy its contents and
paste it into the section [mysqld] in the relevant mysql config file for your OS.

Settings in my_replication-[Link]:

• server-id: Gives the Master Server a unique ID to distinguish it from the Slave Server.
• log-bin and log-bin-index: Tells MySQL to start writing a binary log on the Master Server and
where to write the log. Make sure this directory is empty of all replication logs, especially if
replication has been used before.
• expire_logs_days: Sets the number of days for automatic binary log removal. A value of 7 means
that the binary logs covering the last seven days will be kept on the disk. Make sure to have at
least 2GB of free disk space for each day you want to save binary logs.
• binlog-do-db: Sets the database to write the log for, i.e. the database to replicate.
• log-error: Tells MySQL where to write the MySQL error log. The error log contains information
about when MySQL was started and stopped and also about any critical errors that occurred
while the server was running.
• To specify a specific location for the files log-bin, log-bin-index, relay-log, relay-log-index,
relay-log-info-file, master-info-file and log-error, enter a full path with quotation marks. If
no specific path is specified the files will be created in the MySQL data directory.

Restart the MySQL Master Server from a command prompt for the changes to take effect.

On Linux:

# service mysqld restart

On Windows:

> net stop mysql

> net start mysql

4.3. Configuration of the Slave Server

A replication account has to be created on the Slave Server as well. Start the MySQL command line
client on the Slave Server and enter the following commands.

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'172.16.0.%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

 Use the same username user and password password entered for the replication
account that was created on the Master Server in the previous section.

The file my_replication-[Link], which is available in the DNA-M scripts directory, contains

DNA-M-ReplGuide | 2021-04-26 5 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 4. Setting up Replication

settings for the Master Server. Open the file my_replication-[Link], copy its contents and paste
it into the section [mysqld] in the relevant mysql config file for your OS.

Settings in my_replication-[Link]:

• server-id: Gives the Slave Server a unique ID to distinguish it from the Master Server.
• log-bin and log-bin-index tells MySQL to start writing a binary log on the Slave Server and
where to write the log. Make sure this directory is empty of all replication logs, especially if
replication has been used before.
• expire_logs_days: Sets the number of days for automatic binary log removal. A value of 7 means
that only the binary logs covering the last seven days will be kept on the disk. Make sure to have
at least 2GB free space on disk for each day you want to save binary logs.
• replicate-do-db: Defines which database the Slave Server is replicating to.
• relay-log, relay-log-index and relay-log-info-file: Sets the location and file names for the
relay-log files. If not specified MySQL will create the file names based on the host name.
Changing a replication Slave’s host name can cause replication to fail.
• master-info-file: Sets the location and file name of the file [Link] which contains the
parameters that the Slave Server uses to connect to and communicate with the Master Server.
• log-slave-updates: Makes the Slave Server put replicated events into its own binary log.
• read_only: Makes the Slave Server read-only. Only users with the SUPER privilege and the
replication Slave thread will be able to modify data on it. You can use this to ensure that no
applications will accidentally modify data on the Slave Server instead of the Master Server.
• skip-slave-start: Prevents the Slave Server from starting automatically after a crash. If the
Slave Server starts automatically after a crash, it might cause data corruption and demand a
fresh start of replication.
• log-error: Tells MySQL where to write the MySQL error log. The error log contains information
stating when MySQL was started and stopped and also any critical errors that occurred while the
server was running.
• To specify a location for the files log-bin, log-bin-index, relay-log, relay-log-index, relay-
log-info-file, master-info-file and log-error, enter a full path with quotation marks. If no
specific path is specified the files will be created in the MySQL data directory.

Restart the MySQL Slave Server from a command prompt for the changes to take effect.

On Linux:

# service mysqld restart

On Windows:

> net stop mysql

> net start mysql

The information needed for the Slave Server to connect to the Master Server has to be set. Start the
MySQL command line client on the Slave Server and enter the following command:

DNA-M-ReplGuide | 2021-04-26 6 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 4. Setting up Replication

mysql> CHANGE MASTER TO

MASTER_HOST='[Link]',
MASTER_PORT=3306,
MASTER_USER='user',
MASTER_PASSWORD='password',
MASTER_CONNECT_RETRY=60;

MASTER_HOST and MASTER_PORT sets the IP address and TCP-port needed to connect to the Master.
Replace [Link] with the IP-address of your MySQL Master Server. MySQL servers use the port
3306 by default.

MASTER_USER and MASTER_PASSWORD sets the username and password needed to connect to the
MySQL Master Server. Replace user and password with the username and password entered for the
replication account that was created during configuration of the Master Server.

MASTER_CONNECT_RETRY specifies how many seconds to wait between connect retries.

4.4. Synchronization of the Databases

We have to synchronize the databases on the Master and the Slave Server before replication is
started. We do this by making a dump of the database on the Master Server and transferring it to the
Slave Server. It is important that the database isn’t changing while the backup is created. To ensure
this, a read lock has to be set on the Master Server’s database. The Master database will be set in a
read-only state and will not allow writing of new data into the database. To set a read lock, enter the
following lines into a MySQL command line client on the Master Server.

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SET GLOBAL read_only = ON;

Leave the MySQL command line client from which you issued the FLUSH TABLES
 statement running so that the read lock remains in effect. If you exit the MySQL
command line client the lock will be released.

Create a dump of the Master Server database by entering the following command into a command
prompt on the Master Server.

# mysqldump -u root -p --single-transaction --quick --flush-logs --master-data=1 --databases tnmdb -r tnmdb_<some_identifier>.sql

Make sure you change <some_identifier> field to something relevant. There will now be a backup
file called tnmdb_<some_identifier>.sql which also includes the vital master information, the binary
log position and filename to be appended to the output. Make sure the backup file,
tnmdb_<some_identifier>.sql, is not empty.

Transfer the backup file from the Master Server to the Slave Server with your preferred file transfer
method.

Make sure that you do not already have a DNA-M database on the Slave Server by entering the
following command in the MySQL command line client on the Slave Server.

DNA-M-ReplGuide | 2021-04-26 7 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 4. Setting up Replication

mysql> SHOW DATABASES;

If tnmdb is listed, make sure you are not running a DNA-M server on the Slave
 Server before you continue. If there already exists a tnmdb database on the Slave
Server it needs to be removed before continuing.

Import the transferred backup, tnmdb_<some_identifier>.sql, on the Slave Server by entering the
following commands in the MySQL command line client on the Slave Server.

mysql> GRANT ALL PRIVILEGES ON *.* TO user@localhost IDENTIFIED BY 'password';

mysql> STOP SLAVE;
mysql> SOURCE tnmdb_<some_identifier>.sql
mysql> START SLAVE;

where the SOURCE command should take the full path to the file tnmdb_<some_identifier>.sql.

4.5. Starting the Replication

The Slave Server’s database is now ready to start replicating. Release the lock on the tables on the
Master Server by entering the following commands in the MySQL command line client on the Master
Server.

mysql> SET GLOBAL read_only = OFF;

mysql> UNLOCK TABLES;

The replication should now be started. Refer to the section on Monitoring Replication for information
on how to verify that replication is initiated and working as it should.

DNA-M-ReplGuide | 2021-04-26 8 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 5. Monitoring Replication

5. Monitoring Replication
5.1. Verifying the Connection between the Servers
Check the error-log [Link] on the Slave Server in your MySQL data directory to verify that the
Slave Server has been connected to the Master Server.

Open the file [Link] in an editor and scroll to the end of the log file.

An example: Slave Server is connected to the Master Server it should give you an output similar to
this:

090319 16:01:50 [Note] Slave SQL thread initialized, starting

replication in log 'mysql-bin.000001' at position 106, relay log
'/var/lib/mysql/mysql-relay-bin.000001' position: 4
090319 16:01:50 [Note] Slave I/O thread: connected to master
'repl@[Link]:3306',replication started in log 'mysql-bin.000001'
at position 106

If the Slave Server failed to connect to the Master Server it should give you an output similar to this:

090319 16:01:50 [ERROR] Slave I/O: error connecting to master

'repl@[Link]:3306' - retry-time: 60 retries: 86400,
Error_code: 1045

5.1.1. Troubleshooting tips

If the Slave Server fails to connect to the Master Server:

• Check that the values of username 'user' and password 'password' that were set on the Master
Server during configuration of the Master Server corresponds with the values entered on the
Slave Server during configuration of the Slave Server. Issue the command below to verify that
your credentials are valid:

# mysql -u 'user' -p -h [Link]

• Make sure that settings bind-address and skip-networking are commented in the configuration
file [Link] or [Link].
• Check that firewalls are not blocking the connection
• # telnet [Link] 3306
• Check the file [Link], which by default is located in the MySQL data directory, to see the
values that were set for the Slave Server to connect to the Master Server.

If some values have to be changed or the replication does not start, the section Hands-on
configuration describes how to manually update the Master connection information on the Slave
Server and to start the replication.

DNA-M-ReplGuide | 2021-04-26 9 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 5. Monitoring Replication

5.2. Monitoring the Status of the Slave Server

To monitor the status of the Slave Server database, enter the following command in the MySQL
command line client.

mysql> SHOW SLAVE STATUS\G

The result should be something like this:

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event
Master_Host: [Link]
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 356927964
Relay_Log_File: slave-relay-bin.000003
Relay_Log_Pos: 68367855
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: tnmdb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 356927964
Relay_Log_Space: 233559752
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

If the values of Slave_IO_Running and Slave_SQL_Running are set to Yes the I/O thread and SQL
thread are running and the Slave is successfully connected to the Master. If either of these are No
the replication is stopped and a recovery and restart of the replication is required. The fields
Last_IO_Error and Last_SQL_Error might give you a clue as to what went wrong.

DNA-M-ReplGuide | 2021-04-26 10 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 5. Monitoring Replication

 Refer to the section on "Recovering a MySQL Replication" and section "Restarting

Replication" for instructions on how to recover and restart the replication.

The value of Seconds_Behind_Master is an indication of how 'late' the Slave Server database is and
tells you how many seconds the Slave database is behind the Master database. When the Slave
Server has caught up to the Master Server and goes idle waiting for more events from the I/O thread
this field is zero.

If the value for Seconds_Behind_Master is NULL the Slave Servers SQL thread is not running or the
Slave Servers I/O thread is not running or not connected to Master Server.

Seconds_Behind_Master only tells you the difference between when a statement was put into the
relay log on the Slave Server and when it was executed. It does not account for the time it takes for
the statement to get from the Master Server to the Slave [Link] your Slave Server is reading
statements from the Master slowly but is executing the statements immediately, your Slave Server
will report that it is 0 seconds behind the Master when in fact it might be further behind.

The fields Master_Log_File and Read_Master_Log_Pos show the position in the binary log files on the
Master Server from which the Slave I/O thread is reading.

The fields Relay_Master_Log_File and Exec_Master_Log_Pos show the position in the binary log files
on the Master Server at which the Slave SQL thread is executing.

The fields Relay_Log_File and Relay_Log_Pos show the position in the relay log files on the Slave
Server at which the Slave SQL thread is executing.

The position Exec_Master_Log_Pos in the log files Relay_Master_Log_File on the Master Server
corresponds to the position Relay_Log_Pos in the log file Relay_Log_File on the Slave Server.

5.3. Comparing the Status of the Master and Slave Server

You can also confirm that the replication is working correctly by monitoring the status of the Master
Server and comparing some values between the Master Server and Slave Server. To monitor the
status of the Master Server enter the following command in the MySQL command line client on the
Master Server.

mysql> SHOW MASTER STATUS;

The result is something like this:

+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 356927964 | tnmdb | |
+------------------+-----------+--------------+------------------+

By repeating the command SHOW MASTER STATUS in the MySQL command line client on the Master
Server you will see the value of Position increase when updates are being made to the database on
the Master Server. To see if the Slave is keeping up with the Master compare the value of Position
shown in the status of the Master Server with the value of Exec_Master_Log_Pos shown in the status

DNA-M-ReplGuide | 2021-04-26 11 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 5. Monitoring Replication

of the Slave Server. These values should be equal if the databases are synchronized and the Slave
Server is 0 second behind the Master.

To verify that the Slave Server is replicating statements from the most recent binary log file on the
Master Server you can compare the value of File in the status shown on the Master Server with
value of Relay_Master_Log_File shown in the status of the Slave Server.

5.4. Process List

The command SHOW PROCESSLIST shows you which threads are running on a server. This statement
is useful to monitor the status of the I/O thread and SQL thread on the Slave Server. Enter the
following command in the MySQL command line client on the Slave Server.

mysql> SHOW PROCESSLIST\G

The result is something like this:

*************************** 1. row ***************************

Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 1021748
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 626
State: Has read all relay log; waiting for the slave I/O thread to
update it
Info: NULL

The rows with the user system user show the status of the I/O and SQL threads used on Slave
Server.

• On the first row, [1. row], the field State shows the status of the I/O thread.
• On the second row, [2. row], the field State shows the status of the SQL thread.

In this example the SQL thread on the Slave Server has read all the relay logs and the I/O thread is
waiting for the Master Server to send more events from the binary log.

DNA-M-ReplGuide | 2021-04-26 12 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 6. Hands-on configuration

6. Hands-on configuration
It is possible to manually make changes to the configuration of the Slave Server, to change the
information that is uses to connect to the Master Server and to change options for logging.

6.1. Reconfiguring the Slave Server Settings

It is important to set a read lock on the Master Server’s database so that no data is modified while
making configuration changes. To lock the tables on the Master Servers database enter the following
lines in the MySQL command line client on the Master Server.

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SET GLOBAL read_only = ON;

Leave the MySQL command line client from which you issued the FLUSH TABLES
 statement running so that the read lock remains in effect. If you exit the MySQL
command line client the lock is released.

To determine which position the Master Server is at in the logs enter the following command in the
MySQL command line client on the Master Server.

mysql> SHOW MASTER STATUS;

The output should be something like this:

+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 162114925 | tnmdb | |
+------------------+-----------+--------------+------------------+

 Keep this information on the screen! It will be needed when reconfiguring the
Slave Server in the next section.

To manually configure the Slave Server and to change the information it uses to connect to the
Master Server we first have to stop the Slave Server and then enter the new Master information.

Enter the following lines in the MySQL command line client on the Slave Server, replacing the values
according to the information obtained with the show master status command on the Master Server.

DNA-M-ReplGuide | 2021-04-26 13 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 6. Hands-on configuration

mysql> STOP SLAVE;

mysql> CHANGE MASTER TO
MASTER_HOST='[Link]',
MASTER_PORT=3306,
MASTER_USER='user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=162114925;
mysql> START SLAVE;

• MASTER_HOST changes the IP address that the Slave Server connects to when trying to connect to
the Master Server.
• MASTER_PORT should be set to the TCP-port that the MySQL Master Server uses. The de- fault
port is 3306.
• MASTER_USER and MASTER_PASSWORD change the username and password that the Slave Server
authenticates itself with towards the Master. Use the values set on the Master Server chosen
during configuration of the Master Server.
• MASTER_LOG_FILE and MASTER_LOG_POS tell the Slave Server exactly where to look in the Master
Servers binary log file. Use the values of File and Position shown in the printout of the SHOW
MASTER STATUS command that was just entered on the Master Server.

The Slave will now be waiting for the Master Server to release its tables from lock which is done by
entering the following commands in the MySQL command line client on the Master Server.

mysql> SET GLOBAL read_only = OFF;

mysql> UNLOCK TABLES;

6.2. Changing location of the Log Files

This section shows how to change the settings that are specified in [Link] or [Link], for example
changing the location where log-files are saved.

Stop the IO-thread on the Slave Server by entering the following command in the MySQL command
line client on the Slave Server.

mysql> STOP SLAVE IO_THREAD;

Check the state of the Slave I/O thread with the command:

mysql> SHOW PROCESSLIST\G

Make sure that the Slave Server has read all relay log and that the state is as follows.

State: Has read all relay log; waiting for the slave I/O thread to
update it

DNA-M-ReplGuide | 2021-04-26 14 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 6. Hands-on configuration

Stop the Slave Server by entering the following commands in the MySQL command line client.

mysql> STOP SLAVE;

Stop the MySQL Server with the following commands in a command prompt on the Slave Server. On
Linux:

# service mysqld stop

On Windows:

> net stop mysql

Now you can make the desired changes to the file [Link]/[Link] and they will be loaded when the
MySQL server starts again.

If the path to where the log files are saved is changed, the new path that you
specify must be to an existing directory. If the paths to log files are changed, new
empty log files will be created in the specified location. If the path to the file
 [Link] is changed, [Link] has to be moved to the new specified
location. [Link] contains information about what position in the Master
Server logs that the Slave Server is currently reading from and without this
information replication will fail when started again.

Start MySQL again:

On Linux:

# service mysqld start

On Windows:

> net start mysql

Start the Slave Server and the IO-thread again in the MySQL command line client.

mysql> START SLAVE;

mysql> START SLAVE IO_THREAD;

DNA-M-ReplGuide | 2021-04-26 15 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 7. Recovering a MySQL Replication

7. Recovering a MySQL Replication

If the replication is interrupted, i.e. the I/O thread or SQL thread has been disconnected from the
Master Server, the first thing to try is to reconnect the threads from the replication Slave to the
Master Server.

If this does not help, one can perform a quick recovery of the Slave Server by confirming the position
at which the Slave Server has been stopped and then simply resetting and restarting the Slave
Server at that point in the Master Servers binary logs.

7.1. Reconnecting Replication Threads

Enter the following command in the MySQL command line client on the Slave Server.

mysql> SHOW SLAVE STATUS;

Check the status of Slave_IO_Running and Slave_SQL_Running. If either of these is set to No check
the Last_IO_Error and Last_SQL_Error which might give you a clue as to what went wrong.

Case 1 - Slave_SQL_Running=No

If only the status of Slave_SQL_Running is No, the I/O thread is connected to the Master Server, but
the SQL-thread has stopped replicating from the relay logs into the database.

Try to start the SQL-thread by entering the following commands in the MySQL command line client
on the Slave Server and then check the status of the Slave Server again.

mysql> START SLAVE SQL_THREAD;

mysql> SHOW SLAVE STATUS\G

Case 2 - Slave_IO_Running=No

If the status for Slave_IO_Running is No, the I/O-thread is not running or running but not connected to
a Master Server.

Restart the threads, the I/O thread and SQL thread, by entering the following commands in the
MySQL command line client on the Slave Server and then check the status of the Slave Server
again.

mysql> STOP SLAVE;

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

If the statuses of the threads have been restored to Yes, the replication has been restored and no
more actions are needed.

7.1.1. Skipping Duplicate Entries

If the replication stops because it tried to replay a duplicate record from the log files, this will show up

DNA-M-ReplGuide | 2021-04-26 16 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 7. Recovering a MySQL Replication

as an error in the SHOW SLAVE STATUS printout, we need to tell MySQL to skip that record and
continue. The statement SET GLOBAL SQL_SLAVE_SKIP_COUNTER=<N> skips the next N events from the
Master Server.

Enter the following commands in the MySQL command line client on the Slave Server and check the
status of the Slave Server again.

mysql>STOP SLAVE;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>START SLAVE;
mysql>SHOW SLAVE STATUS\G

The statements above may be performed a few times until no more duplicate entries exist. If there is
more than one duplicate entry you can increase the value N to skip several events at once.

7.2. Slave Recovery

If the procedure in section "Reconnecting Replication Threads" did not work, try the following
instructions which are all commands entered in the MySQL command line client on the Slave Server.

Stop the Slave Server.

mysql> STOP SLAVE;

Display the status of the Slave Server to get the position at which it has been stopped.

mysql> SHOW SLAVE STATUS;

The information needed from the displayed status is:

Master Host: [Link]

Master_User: user
Master_Port: 3306
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 189628335

 Keep the result of the SHOW SLAVE STATUS command. Without this information you
will not be able to complete the procedure.

Reset the Slave Server.

mysql> RESET SLAVE;

Set the position at which the Slave Server was stopped.

DNA-M-ReplGuide | 2021-04-26 17 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 7. Recovering a MySQL Replication

mysql> CHANGE MASTER TO

MASTER_HOST='[Link]',
MASTER_USER='user',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=189628335;

Start the Slave Server again and check the status of it.

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

If needed, perform the statements described in the previous section "Skipping Duplicate Entries" to
skip duplicate entries.

If this procedure fails you will have to perform the more time-consuming full copy of the Master
Servers database and restart the replication like you did when you initially set it up, refer to the
section "Restarting Replication".

DNA-M-ReplGuide | 2021-04-26 18 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 8. Restarting Replication

8. Restarting Replication
If a clean start of the replication is necessary, we first have to clear out the old replication log files
and then perform a full copy of the database on the Master Server to the Slave Server. When
resetting a Master or Slave Server their query log files are deleted. A fresh replication server can
then be configured and started.

8.1. Resetting the Master and Slave Servers

First we have to stop the DNA-M Server that is running on the Master Server. Stop the DNA-M
Server in the DNA-M Server GUI or by entering the following command in a command prompt.

On Linux:

# service tnmserver stop

On Windows:

> net stop tnmserver

On the Slave server, stop and reset the Slave Server by entering the following commands in the
MySQL commandline client.

mysql> STOP SLAVE;

mysql> RESET SLAVE;
mysql> RESET MASTER;

Reset the Master Server from a MySQL command line client on the Master Server with the following
command.

mysql> RESET MASTER;

Start the DNA-M Server on the Master Server again in the DNA-M Server GUI or by entering the
following command in a command prompt. On Linux:

# service tnmserver start

On Windows:

> net start tnmserver

It is now possible to start the replication again by setting up a new replication server from scratch,
following the instructions in the section "Setting up Replication".

DNA-M-ReplGuide | 2021-04-26 19 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 9. DNA-M Server upgrade and Replication

9. DNA-M Server upgrade and Replication

When a newly installed DNA-M is started for the first time, migration scripts may be automatically run
and the data in the DNA-M database is transformed according to the requirements of the new
installation. Some of the migration scripts may contain stored procedures which do not have
permission to be performed on the replication server.

When upgrading the DNA-M server to a new version it is recommended to back up the current
database before upgrade, disable replication, re-synchronize the master and slave as described
earlier in this document, and then make a clean start the of the replication (see the section on
"Restarting Replication") after upgrade has been performed.

If, however it is required that replication be left running during the upgrade the following procedure
can be used:

Before installing the new DNA-M server on the master, permissions to perform stored procedures on
any slave database can be temporarily enabled as follows:

On the master only do:

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

The newly installed DNA-M server can be started and the automatic migration will be successfully
run on both master and slave databases. When the DNA-M server has been running for a while
(roughly 10 minutes or all the nodes are no longer grey), the global option can then be disabled:

mysql> SET GLOBAL log_bin_trust_function_creators = 0;

This method, of course, runs a very slight risk of allowing stored procedures that target only one of
the databases to be run, and therefore master and slaves will not be 100% identical. For further
information on why this may be the case see [Link]
[Link]

DNA-M-ReplGuide | 2021-04-26 20 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 10. Backing up the Slave Server

10. Backing up the Slave Server

For good safety it is wise to make backups of a database so that, if needed, it can be recovered from
that backup. The following instructions describe how to make a backup of the database on the
replication Slave Server. To make a backup of a database, it has to be in a controlled state where no
writing to the database is allowed, so that the database is not changing during the creation of the
backup. This means that the database has to be locked during the backup process. One of the big
advantages with having replication between a Master and a Slave Server is that the Slave Server
can be locked and backed up without affecting the operations of the Master Server. Backups of the
Slave Server are recommended to be performed on a daily basis.

10.1. Manual Backups

Backups should include the Slave position in the Master Server logs (Master_Log_File
/Read_Master_Log_Pos) and the binary log files created on the Slave Server for point-in-time
recovery. This information is shown when issuing the command SHOW SLAVE STATUS in the MySQL
command line client.

To manually perform a backup of the Slave Server, follow these directions.

Stop the Slave Server from a MySQL command line client on the Slave Server.

mysql> STOP SLAVE;

Perform a backup of the Slave Server’s database from a command prompt on the Slave Server.

# mysqldump -u root -p --single-transaction --quick --flush-logs --master-data=1 --databases tnmdb -r tnmdb_<some_identifier>.sql

This saves the data into the backup file tnmdb_<some_identifier>.sql, together with the Master
coordinates in the Slave Servers bin-log files. If the Slave Server should be switched to a Master
Server, these coordinates are stored in the backup file to make point-in-time recovery from another
Slave Server possible.

Check that the backup file (tnmdb_<some_identifier>.sql) is of a reasonable size (about the size of
the database).

Navigate to the MySQL data directory and backup all binary log files.

The binary log files are named mysql-bin with an index number as a suffix, e.g. mysql- bin.000001.
Also backup all .info and .index files in the MySQL data directory. Start the Slave Server again
from a MySQL command line client on the Slave Server.

mysql> START SLAVE;

The Slave Server is restored to normal operation again and starts to synchronize with the Master by
catching up with updates in the Master Server’s binary log that were made while the Slave Server
was stopped.

DNA-M-ReplGuide | 2021-04-26 21 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 10. Backing up the Slave Server

 Refer to the section on Monitoring Replication for information on how to verify that
the replication is started again.

10.2. Automated Backups

The process of making backups of the Slave Servers database is automated by the provided scripts
located in the subfolder scripts of the DNA-M install directory.

The script makes a database dump of the replicated database, with the mysqldump function, and also
backs up the .info- and .index-files as well as the master and slave status information that are
needed in a recovery process.

For Linux the script is called:

MySQL_slave_backup.sh

For Windows the script is called:

MySQL_slave_backup.bat

 The scripts should NEVER be executed on the Master Server.

Open the relevant script for your OS with a text editor and modify the section OPTIONS to specify the
following.

• The location of the MySQL data directory

• The location where you want the backups to be placed
• The username and the password of a MySQL account
• The name of the database that is being replicated and that should be backed up

Every time the script is executed, the previously backed up files will be replaced so that the required
disk space for the backups does not increase over time. Setting up the script to be executed every
24 hours ensures that you always have a backup of the database that never is more than 24 hours
old.

To manually run the backup script just enter in a command prompt on the Slave Server:

On Linux:

# ./<path_to_script>/MySQL_slave_backup.sh

On Windows:

> C:\<DNA-M_home>\scripts\MySQL_slave_backup.bat

Verify that the backup process went well by checking that the MySQL dump file, the .index and

DNA-M-ReplGuide | 2021-04-26 22 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 10. Backing up the Slave Server

.info-files and text files named master_status.txt and slave_status.txt are located in the
directory where you specified that the backups should be saved.

Log files containing the date and time of when backups have been made will be located in the
subfolder dumplogs of the directory where you have chosen to save the backups.

10.2.1. Scheduling backups

It is recommended to regularly make backups of the Slave Server.

On Linux

Schedule a cron job to run the script at certain intervals. To edit the crontab, enter the following
command in a command prompt.

# crontab -e

Add this line at the bottom of the file.

30 4 * * * /<path_to_script>/MySQL_slave_backup.sh > /<dir_of_your_choice>/logs/[Link]

With this example-line in the crontab, MySQL_slave_backup.sh will be executed every night at 04:30.
A log file for the backup, called [Link], will be created in /<dir_of_your_choice>/logs/. The
time, frequency and logging option can of course be changed as you wish.

On Windows

Create a scheduled task to execute the script at certain times. Go to Scheduled tasks in the
Windows control panel to add a scheduled task. When asked which program to schedule, use the
browse button to locate the script MySQL_slave_backup.bat and select to execute the task, for
example, every night at 04:30.

10.3. Restoring the Master DNA-M database from a backup

This section describes how to restore the database of the Master Server to an earlier state using a
backup made on the Slave Server.

 You need to transfer the backup file (tnmdb_<some_identifier>.sql) from the

Slave Server to the Master Server first.

First we have to stop the DNA-M Server that is running on the Master Server. Stop the DNA-M
Server in the DNA-M Server GUI or by entering the following command in a command prompt on the
Master Server.

On Linux:

# service tnmserver stop

On Windows:

DNA-M-ReplGuide | 2021-04-26 23 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 10. Backing up the Slave Server

> net stop tnmserver

Drop the DNA-M database from a MySQL command line client on the Master Server.

mysql> drop database tnmdb;

Source the backup file, which was created on the slave server, from a MySQL command line client
on the Master Server with the following command.

mysql> source tnmdb_<some_identifier>.sql

Now the Master DNA-M Server can be started again, in the DNA-M Server GUI or by entering the
following command in a command prompt on the Master Server.

On Linux:

# service tnmserver start

On Windows:

> net start tnmserver

DNA-M-ReplGuide | 2021-04-26 24 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 11. Switching Master Server

11. Switching Master Server

This section describes how to transform a Slave Server into a Master Server. This can be done as a
planned action when you for example want to make hardware changes on the Master Server or in
case the Master Server crashes or stops working correctly.

Figure 2. Switching master server

To be able to perform this transformation as fast as possible, make sure a DNA-M Server has been
installed on the Slave Server that is to become the new Master Server. For instructions on
installation of a DNA-M Server refer to DNA-M Installation Guide for Linux.

If you are making a planned change of Master Server, the DNA-M Server on the Master Server has
to be shut down before continuing.

 From this point on the database on the Master Server will not receive any new
events from the DNA-M Server.

11.1. Promoting the Slave Server to become a Master Server

This section describes how to transform the Slave Server into a Master Server. Disconnect the Slave
Server’s I/O thread by entering the following command in a MySQL command line client on the
Slave Server:

mysql> STOP SLAVE IO_THREAD;

Check the state of the Slave Server’s I/O thread.

mysql> SHOW PROCESSLIST\G

Make sure that the Slave Server has finished executing the relay logs it fetched from the Mas- ter
Server before it was stopped (or crashed).

DNA-M-ReplGuide | 2021-04-26 25 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 11. Switching Master Server

Id: 3171
User: system user
Host:
db: NULL
Command: Connect
Time: 50
State: Has read all relay log; waiting for the slave I/O thread to
update it
Info: NULL

Perform a backup of the database on the Slave Server, as described in section "Backing up the
Slave Server". If backups of the Slave Server are performed on a daily basis, this step can be
skipped to save some time.

It is not safe to perform backups of a replication Master Server. If backups are

 scheduled to be performed regularly on the Slave Server that is to be made into a

Master Server, this scheduled task has to be removed when the Slave Server is
promoted to become a Master Server.

Disconnect the Slave Server from the old Master Server and discard the connection information in its
[Link] file by entering the following lines into the MySQL command line client on the Slave
Server.

mysql> STOP SLAVE;

mysql> RESET SLAVE;
mysql> RESET SLAVE ALL;

We can now change the configuration of the Slave Server to transform it into a Master Server.

Open the file [Link] or [Link]. Find the section SLAVE SERVER SETTINGS in the [mysqld] section and
comment the Slave settings by putting # marks in front of the following lines.

#log-bin = mysql-bin
#log-bin-index = [Link]
#expire_logs_days = 7
#replicate-do-db = tnmdb
#relay-log = mysql-relay-bin
#relay-log-index = [Link]
#relay-log-info-file = [Link]
#master-info-file = [Link]
#log-slave-updates = 1
#read_only = 1
#skip-slave-start = 1
#log-error = [Link]

Uncomment the Master settings by removing the # marks in front of the following lines in the same
file.

DNA-M-ReplGuide | 2021-04-26 26 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 11. Switching Master Server

log-bin = mysql-bin
log-bin-index = [Link]
expire_logs_days = 7
binlog-do-db = tnmdb
log-error = [Link]

Restart the MySQL Server from a command prompt on the Slave Server.

 The MySQL Slave Server has now been transformed into a MySQL Master Server.

Make sure the DNA-M [Link] is correct according to the new computer configuration.

Start the DNA-M Server, on the new Master Server. Wait until the field at the bottom of the DNA-M
Server GUI becomes green and the DNA-M Server started message appears in the Server
Messages area.

If the DNA-M Server does not start, check for error messages in mainserver_stderr.txt. For more
information on DNA-M Server configuration, refer to DNA-M Server Administration Guide.

11.2. Configuring the old Master Server as a Slave Server

If the old Master Server crashed and an unplanned upgrade of the Slave Server to new Master
Server was performed, then the old Master Server can be configured as a replication Slave to the
new Master Server, when it has been restored. In that case the Master and Slave Servers will have
changed places. To instead configure a completely new replication Slave Server, refer to section
"Setting up Replication".

Figure 3. Changing Master and Slave servers

The MySQL Server that we now want to make into a Slave was previously a Master Server.
Therefore the configuration settings in the file [Link] or [Link] have to be changed.

Edit the file [Link] or [Link] on the old Master Server.

Find the section MASTER SERVER SETTINGS in the [mysqld] section and comment the Master settings
by putting # marks in front of the following lines.

DNA-M-ReplGuide | 2021-04-26 27 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 11. Switching Master Server

#log-bin = mysql-bin
#log-bin-index = [Link]
#expire_logs_days = 7
#binlog-do-db = tnmdb
#log-error = [Link]

Uncomment the Slave settings by removing the # marks in front of the following lines in the same
file.

log-bin = mysql-bin
log-bin-index = [Link]
expire_logs_days = 7
replicate-do-db = tnmdb
relay-log = mysql-relay-bin
relay-log-index = [Link]
relay-log-info-file = [Link]
master-info-file = [Link]
log-slave-updates = 1
read_only = 1
skip-slave-start = 1
log-error = [Link]

Restart the MySQL Server by entering the following command in a command prompt.

On Linux:

# service mysqld restart

On Windows:

> net stop mysql

> net start mysql

 The old MySQL Master Server has now been transformed into a MySQL Slave
Server but is not yet connected to the new Master Server.

Transfer the backup of the old Slave Server database created earlier.

List the databases on the new Slave Server by entering the following command in a MySQL
command line client.

mysql> SHOW DATABASES;

 Make sure you are working on the new Slave Server before continuing. The next
command will remove the tnmdb database.

If the database tnmdb is in the list of available databases it needs to be removed, use the command
below in the MySQL command line client to remove the tnmdb database.

DNA-M-ReplGuide | 2021-04-26 28 (29) © Infinera Corporation 2021

Database Replication Guide for DNA-M 11. Switching Master Server

mysql> DROP DATABASE tnmdb;

Create a new empty database and import the backup file that was just transferred from the old Slave
Server. Enter the following commands in the MySQL command line client on the new Slave Server,
using the path to the backup file from the old Slave Server.

mysql> CREATE DATABASE tnmdb;

mysql> USE tnmdb;
mysql> STOP SLAVE;
mysql> SOURCE [Link]

Now set the parameters needed for the new Slave Server to connect to the new Master Server by
entering the following command in the MySQL command line client on the new Slave Server.

mysql> CHANGE MASTER TO

MASTER_HOST='[Link]',
MASTER_PORT=3306,
MASTER_USER='user',
MASTER_PASSWORD='password',
MASTER_CONNECT_RETRY=60;
mysql> START SLAVE;

The replication between the new Master and Slave Servers should now be up and running.

The Slave Server starts to synchronize with the Master Server from the point where the backup file
was created. The Slave server will then reaches the point in the binary log files where the Master
Server is writing new statements.

This can be monitored with the variable Seconds behind master, which should decrease over time
as the Slave Server is closing in on the Master Server. Refer to the section "Monitoring Replication"
for information on how to verify that the replication is started and running correctly.

DNA-M-ReplGuide | 2021-04-26 29 (29) © Infinera Corporation 2021