MySQL Backup (From Version 4.4 & 5.0)

This section guides you with the procedure required for taking backup of Session Monitor's MySQL Data. This section is applicable only for taking MySQL backup from Session Monitor with version 4.4 and 5.0.

Installing MySQL shell utility

Download the latest mysql80-community-release rpm file for Oracle Linux 7 from https://dev.mysql.com/downloads/repo/yum/ and copy it to the current OCSM Server.

Note:
'mysql80-communityrelease-el7-7.noarch.rpm' has been used here.

Run these commands to install mysql80-community-release rpm:

sudo yum remove mysql-community-release
rpm -ivh <mysql80-community-release-xxxxx>.rpm

For example:

rpm -ivh mysql80-community-release-el7-7.noarch.rpm

Enable 'mysql80-community-source' from repo by running the following command:
```
sed -i '/mysql80-community-source/{ n; n; n; s/enabled=0/enabled=1/g }' /etc/yum.repos.d/mysql-community-source.repo
```

Verify yum search returns success by running the following command:

yum search mysql-shell

Sample Output:

.
.
Repository ol7_UEKR4_archive is listed more than once in the configuration
Repository ol7_UEKR5_archive is listed more than once in the configuration
Repository ol7_kvm_utils is listed more than once in the configuration
============================================================================================ N/S matched: mysql-shell ======================================================================================================
mysql-shell.x86_64 : Command line shell and scripting environment for MySQL
.
.

Run the following command to install mysql-shell:
```
yum install mysql-shell
```
mysql-shell is installed. Confirm by typing mysqlsh --no-defaults, and the mysql-shell console must open. By default it opens JS mode.
Output:
```
[root@localhost ~]# mysqlsh --no-defaults
MySQL Shell 8.0.32
 
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
MySQL  JS >
```
Note:
Type "\quit" to exit from the mysql shell.

Create a temporary directory structure in /root/ or any other location of the current OCSM Server where the space is available.
For example:
```
mkdir /root/mysqlBackup/
```
Copy the password from /root/.my.cnf and keep it handy.

Figure 3-1 copy password

Starting the MySQL Backup Procedure

Run the commands in sequence to start the MySQL Backup procedure:

Type mysqlsh --no-defaults command. The JS prompt is opened.
Run the following commands one by one at the JS prompt.
1. Connect to a MySQL instance by typing the below command:
```
\connect root@localhost:3306
```
2. When prompted for the password, paste the password copied from /root/.my.cnf in MySQL Backup (From Version 4.4 & 5.0).
3. Run this command to begin backup:
```
util.dumpSchemas(["<DATABASE>"],"<PATH_TO_MYSQL_BACKUP_DIRECTORY>/<DATABASE>.dump",{threads:88})
```
```
For ME, use DATABASE = vsp
For MEC, use DATABASE = pldmaster
For FDP, use DATABASE = fdp
```
  Example For ME:
```
util.dumpSchemas(["vsp"] "/root/mysqlBackup/vsp.dump",{threads:88})
```
  Note:
  The total time to take a dump of all schemas depends on the size of database as well as the number of CPUs in the source machine. In our testing in lab, for a VSP database of size 1.1 TB and 96 CPUs in source machine, it took 6 minutes to complete the dump. Refer to the official MySql Shell Utility document for more details: MySQL Shell 8.0.
4. Once the backup is successful, a message is displayed as similar to the below sample:
```
105% (9.97M rows / ~9.47M rows), 26.82K rows/s, 559.12 KB/s uncompressed, 17.04 KB/s compressed
Dump duration: 00:00:52s
Total duration: 00:00:54s
Schemas dumped: 1
Tables dumped: 63
Uncompressed data size: 260.70 MB
Compressed data size: 7.81 MB
Compression ratio: 33.4
Rows written: 9970295
Bytes written: 7.81 MB
Average uncompressed throughput: 4.93 MB/s
Average compressed throughput: 147.71 KB/s
```
5. Type this command to exit from mysql-shell:
```
\quit
```
Run this command to verify a <DATABASE>.dump directory is created under the directory created from MySQL Backup (From Version 4.4 & 5.0):
```
ls -lh /root/mysqlBackup/
```
For example:
```
Example For ME,
[root@localhost ~]# ls -lh /root/mysqlBackup/
total 1.2M
drwxr-x---. 2 root root 1.2M Aug 19 13:27 vsp.dump
```

Change the Character Set Encoding of MySQL Backup

Change the character set encoding of the MySQL backup from utf8 to utf8mb4. This is required as the default character set in MySql 8.0 is utf8mb4.

Get the convertUTF8ToUTF8mb4.sh script file present in the Session Monitor installation software rpm .zip file

Copy the convertUTF8ToUTF8mb4.sh script to the same folder where the <DATABASE>.dump directory was created.

For example

[root@localhost ~]# mv convertUTF8ToUTF8mb4.sh /root/mysqlBackup/
[root@localhost ~]# ls -lrt /root/mysqlBackup/
total 144
-rwxr-xr-x. 1 root root    472 Mar 10 04:51 convertUTF8ToUTF8mb4.sh
drwxr-x---. 2 root root 143360 Mar 23 08:46 vsp.dump

Navigate to the MySQL Backup directory (For example, cd /root/mysqlBackup/) and run this command to provide the necessary permissions:
```
chmod +x convertUTF8ToUTF8mb4.sh
```
Execute the script:
```
./convertUTF8ToUTF8mb4.sh
```

Taking MySQL Dump of the Blocks Table

Take MySQL dump of blocks table. This step is required only if the node type is Mediation Engine.

Move to the MySQL Backup directory (For example, cd /root/mysqlBackup/) and take a MySQL dump of the Blocks table. Run this command:
```
mysqldump --skip-add-drop-table --skip-add-locks --no-create-info --replace vsp blocks > blocks_replace.sql
```
Verify the blocks_replace.sql is created under the MySQL Backup directory. For example:
```
[root@localhost ~]# ls -lrt /root/mysqlBackup/
total 144
-rwxr-xr-x. 1 root root    472 Mar 10 04:51 convertUTF8ToUTF8mb4.sh
drwxr-x---. 2 root root 143360 Mar 23 08:46 vsp.dump
-rwxr-xr-x. 2 root root 143360 Mar 23 08:46 blocks_replace.sql
```
Note:
This step helps to fix any discrepancy caused by difference in the number of blocks between the Source Machine and the Target Machine.

Copy the MySQL Backup Directory to the Target Machine

Check the space availability on both Source and Target Machines, and copy the MySQL Backup directory to the Target Machine (Remote Server or Shared Drive).

Run this command to check the MySQL backup directory size on the current OCSM Server (Source Machine) by running the following command.
```
du -sh <path to mysql backup folder>
```
Run the following command on the backup location of the Target Machine (Remote Server or Shared Drive) to get the available space:
```
df -kh --output=avail <path to copy backup>
```
Compare the outputs of the above commands, and make sure the available space (in Step #2) is greater than the MySQL backup size directory size (in Step #1).
Copy the MySQL Backup directory to the Target Machine (Remote Server or Shared Drive).
1. For the Remote Server:
  Copy the MySQL Backup directory containing the <DATABASE>.dump directory and the blocks_replace.sql file by running the following scp command:
```
scp -r <PATH_TO_MYSQL_BACKUP_DIRECTORY> <User>@<Target_Machine_IP>:<path to copy backup>
```
  For example:
```
scp -r /root/mysqlBackup  root@1.2.3.4:/root/ocsmBackup/
```
  Here, the Remote Server is either the Remote Server selected as part of Strategy-1 OR the newly created Target machine as part of Strategy-2.
  
  (or)
2. For the Shared Drive:
  Transfer the MySQL Backup directory containing <DATABASE>.dump directory and blocks_replace.sql file by running the following command:
```
cp -r <PATH_TO_MYSQL_BACKUP_DIRECTORY> <path to copy backup>
```
  For example:
```
cp -r /root/mysqlBackup  /mnt/oracle/ocsmBackup/
```
  Note:
  The time to copy the MySQL backup folder depends on the size of directory as well as the network bandwidth between the source and the target machine.