Required packages

Make sure that the following packages have to be present on the Linux machine in order for AhsayOBM version 8 to be installed.

• OpenJDK Version 1.8 (https://www.freebsd.org/java/)
  The installed Java Development Kit version must at least be 8.
• GNU LIBC 2.14 (https://www.gnu.org/software/libc/)
  The installed ‘GNU LIBC’ version must at least be 2.14 for OpenJDK 8 to work.

Root account installation

AhsayOBM is installed on the MySQL database server using the root account.

Online database instance

Make sure the MySQL database instance is online.

Example: MySQL5.7 on CentOS 7

# service mysqld status
Redirecting to /bin/systemctl status  mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2019-01-02 11:42:08 HKT; 2h 46min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 16952 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─16952 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/my...

Jan 02 11:42:00 centos7 systemd[1]: Starting MySQL Server...
Jan 02 11:42:08 centos7 systemd[1]: Started MySQL Server.

Listening Port

Check the listening port of the MySQL database instance (default is 3306).

#netstat -pan|more
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1/systemd
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      21981/java
tcp        0      0 192.168.122.1:53        0.0.0.0:*               LISTEN      2489/dnsmasq
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1307/sshd
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      1110/cupsd
tcp        0      0 127.0.0.1:60024         0.0.0.0:*               LISTEN      21981/java
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      15556/master
tcp        0      0 0.0.0.0:443             0.0.0.0:*               LISTEN      21981/java
tcp       86      0 10.16.30.2:37272        203.186.85.237:443      CLOSE_WAIT  21981/java
tcp       86      0 10.16.30.2:49302        40.114.13.14:443        CLOSE_WAIT  21981/java
tcp        0     64 10.16.30.2:22           192.168.12.1:55777      ESTABLISHED 14094/sshd: root@pt
tcp6       0      0 :::111                  :::*                    LISTEN      1/systemd
tcp6       0      0 :::22                   :::*                    LISTEN      1307/sshd
tcp6       0      0 ::1:631                 :::*                    LISTEN      1110/cupsd
tcp6       0      0 ::1:25                  :::*                    LISTEN      15556/master
tcp6       0      0 :::3306                 :::*                    LISTEN      15671/mysqld
tcp6      86      0 10.16.30.2:48396        10.16.30.21:443         CLOSE_WAIT  22347/bschJW
tcp6      86      0 10.16.30.2:48428        10.16.30.21:443         CLOSE_WAIT  22347/bschJW

mysqldump utility

The mysqldump utility is installed on the MySQL database server. To locate the mysqldump utility use the whereis command:

#whereis mysqldump
mysqldump: /usr/bin/mysqldump /usr/share/man/man1/mysqldump.1.gz

mysqldump version

The mysqldump utility is the same version as the MySQL database.

#mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.24, for Linux (x86_64)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.24    |
+-----------+
1 row in set (0.00 sec)

# mysqldump --version
mysqldump  Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

MySQL database user account with correct privileges

A MySQL database user account with the following privileges must be setup for the backup operation.

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’ IDENTIFIED BY ‘password’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost.localdomain’ IDENTIFIED BY ‘password’; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’; 
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE USER 'username'@'localhost.localdomain' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.46 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost.localdomain’; 
Query OK, 0 rows affected (0.22 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.23 sec)

Resolvable localhost

Verify that “localhost” on the MySQL database server is resolvable and “localhost” is allowed to access the MySQL database instance on the MySQL service listening port (default 3306).

#ping localhost
PING localhost (127.0.0.1) 56(84) bytes of data.
64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.189 ms
64 bytes from localhost (127.0.0.1): icmp_seq=2 ttl=64 time=0.054 ms
64 bytes from localhost (127.0.0.1): icmp_seq=3 ttl=64 time=0.054 ms
64 bytes from localhost (127.0.0.1): icmp_seq=4 ttl=64 time=0.057 ms
64 bytes from localhost (127.0.0.1): icmp_seq=5 ttl=64 time=0.057 ms

--- localhost ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4000ms
rtt min/avg/max/mdev = 0.054/0.082/0.189/0.053 ms

#telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
J
5.7.24i6&VeFS!F<t'zmysql_native_password

Exclude "information_schema" and "performance_schema" databases

Exclude “information_schema” and “performance_schema” databases since they are MySQL virtual system databases, which contains information about the user databases on the MySQL instance. They are read-only and cannot be backed up.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

Temporary directory

The databases selected for backup will be temporarily spooled to a temporary directory before being uploaded to the backup server or destination storage.

Ensure that the temporary directory configured for the MySQL database backup has sufficient disk space for the backup operation, the free space on the temporary directory drive should be at least 130% of the database size. As the temporary directory is also used for storing index files and any incremental or differential delta files generated during the backup job before they are uploaded to the backup destination.

Please bear in mind the size of the databases may grow over time and you may need to review the temporary directory free space requirements on a regular basis. To calculate for the size of your databases run the command below.

mysql> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| information_schema |      0.16 |
| mysql              |      2.43 |
| performance_schema |      0.00 |
| sys                |      0.02 |
+--------------------+-----------+
4 rows in set (0.04 sec)

mysql>

AhsayOBM

AhsayOBM is installed on the MySQL database server.

Online database instance

The MySQL database instance is online.

Listening port

Check the listening port of the MySQL database instance (default is 3306) using the command netstat –b –a.

C:\>netstat -b -a

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    0.0.0.0:135            w2k16-std:0            LISTENING
  RpcSs
 [svchost.exe]
  TCP    0.0.0.0:445            w2k16-std:0            LISTENING
 Can not obtain ownership information
  TCP    0.0.0.0:2179           w2k16-std:0            LISTENING
 [vmms.exe]
  TCP    0.0.0.0:3306           w2k16-std:0            LISTENING
 [mysqld.exe]
  TCP    0.0.0.0:3389           w2k16-std:0            LISTENING
  TermService
 [svchost.exe]
  TCP    0.0.0.0:5985           w2k16-std:0            LISTENING
 Can not obtain ownership information
  TCP    0.0.0.0:47001          w2k16-std:0            LISTENING
 Can not obtain ownership information
  TCP    0.0.0.0:49664          w2k16-std:0            LISTENING
 Can not obtain ownership information
  TCP    0.0.0.0:49665          w2k16-std:0            LISTENING
 [lsass.exe]
  TCP    0.0.0.0:49666          w2k16-std:0            LISTENING
  EventLog
 [svchost.exe]
  TCP    0.0.0.0:49667          w2k16-std:0            LISTENING
 [spoolsv.exe]
  TCP    0.0.0.0:49668          w2k16-std:0            LISTENING
  SessionEnv
 [svchost.exe]
  TCP    0.0.0.0:49669          w2k16-std:0            LISTENING
  PolicyAgent

mysqldump utility

The mysqldump utility is installed on the MySQL database server. Example: the default location for the mysqldump utitlity for MySQL v5.6.x is located in the following folder C:\Program Files\MySQL\MySQL Server 5.6\bin

mysqldump version

The mysqldump utility is the same version as the MySQL database. To check the mysqldump version use the mysqldump – –version command.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump --version
mysqldump  Ver 10.13 Distrib 5.6.41, for Win64 (x86_64)

C:\Program Files\MySQL\MySQL Server 5.6\bin>

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump --version
mysqldump  Ver 8.0.12 for Win64 on x86_64 (MySQL Community Server - GPL)

C:\Program Files\MySQL\MySQL Server 8.0\bin>

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.41-log |
+------------+
1 row in set (0.00 sec)

mysql>

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)
mysql>

MySQL database user account

A MySQL database user account with the following privileges must be setup for the backup operation.

mysql> GRANT ALL PRIVILEGES ON *.* TO "username"@"localhost" IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO "username"@"localhost.localdomain" IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> CREATE USER 'root'@'localhost.localdomain' IDENTIFIED BY 'Abcd123$%';
Query OK, 0 rows affected (0.32 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost.localdomain';
Query OK, 0 rows affected (0.12 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Resolvable localhost

Verify that “localhost” on the MySQL database server is resolvable and “localhost” is allowed to access the MySQL database instance on the MySQL service listening port (default 3306).

C:\>ping localhost

Pinging 10.90.10.40 with 32 bytes of data:
Reply from 10.90.10.40: bytes=32 time<1ms TTL=64
Reply from 10.90.10.40: bytes=32 time<1ms TTL=64
Reply from 10.90.10.40: bytes=32 time<1ms TTL=64
Reply from 10.90.10.40: bytes=32 time<1ms TTL=64

Ping statistics for 10.90.10.40:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

C:\>

# telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'
J
5.6.31vB#'8%/kQ3K\n6``Aemysql_native_password

NOTE:
The telnet utility is not installed by default on some Windows versions. 	

Exclude "information_schema" and "performance_schema" databases

Exclude the “information_schema” and “performance_schema” databases are MySQL virtual system databases, which contains information about the user databases on the MySQL instance. They are read-only and cannot be backed up.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
6 rows in set (0.00 sec)

Temporary directory

The databases selected for backup will be temporarily spooled to a temporary directory before being uploaded to the backup server or destination storage. Ensure that the temporary directory configured for the MySQL database backup has sufficient disk space for the backup operation, the free space on the temporary directory drive should be at least 130% of the database size. As the temporary directory is also used for storing index files and any incremental or differential delta files generated during the backup job before they are uploaded to the backup destination.

Please bear in mind the size of the databases may grow over time and you may need to review the temporary directory free space requirements on a regular basis. To calculate for the size of your databases run the command below.

+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| information_schema |      0.01 |
| mysql              |      0.90 |
| performance_schema |      0.00 |
| sakila             |      6.44 |
| world              |      0.77 |
+--------------------+-----------+
5 rows in set (0.53 sec)

Limitations for MySQL running on Windows

1. Backup and restore must be to the same MySQL database version.
2. When restoring MySQL databases to an alternate location only one database can be selected and restored at any one time.
3. Cannot restore the MySQL database nodes to original or alternate location.
4. Restoring databases to another machine can only be done using the Restore raw file option.