Fortinet Community

jskrivan_FTNT · ‎04-15-2014

Description

This article describes how to configure a FortiAnalyzer unit for an external MySQL database and MySQL server. FortiAnalyzer software version 5.0.x and onward supports local PostgreSQL and external MySQL databases for storing logs.

Using an external MySQL database provides High Availability and load balancing functions for the FortiAnalyzer setup. This configuration is ideal for large networks and environments with high volumes of logs. It can provide performance and scalability benefits, including increased storage capacity, off-loading report query generation and drill down capabilities, as well as more flexible deployment options.

This document also describes example deployment scenarios and sample configurations.

***

Starting in FortiAnalyzer 5.0.7 and 5.2.0, remote SQL database support only covered the insertion of log data into the remote MySQL database. Historical log search and reporting capabilities, which rely on the remote SQL data, were no longer supported.

FortiAnalyzer 5.4 and onward do not support use of remote SQL servers.

Scope
Support for the remote databases has some limitations after 5.0.6

Solution

Deployment Considerations

The following are important aspects, that should be understood prior to using an external SQL database:
- Using an external SQL database reduces FortiAnalyzer SQL database load.
- Network speed and reliability is an important factor when deploying this scenario.
- In environments with high volumes of logs, an external MySQL database server will experience heavy utilization, because of SQL log insertion and reading efforts.

Network Diagram

The diagram below illustrates the network topology used. Two FortiAnalyzer units (FAZ-A and FAZ-B) connect to an external SQL server. A SQL backup server is optional, in case a replication of the database is required. In this scenario, MySQL servers can replicate their databases, providing further HA capabilities, while FAZ-C unit is dedicated for user interface and analyzer tasks (log view, drill down and reporting only). This will help to reduce the load on the primary SQL server, as well as on primary FAZ-A and FAZ-B units.

Configuration steps summary

The following is a summary of the configuration steps required:

1) Set up the MySQL database:
- Install the MySQL server.
- Change the default storage engine to MyISAM.
- Run the initial configuration.
- Grant privileges.
- Configure database replication.

2) Configure FortiAnalyzer units:
- Configure the first FortiAnalyzer unit.
- Configure remaining FortiAnalyzer units.
- Register all devices on all FortiAnalyzer units.

3) Check the SQL database

4) Replicate the SQL database (optional)
- Primary SQL server.
    - Modify the configuration file (/etc/my.cnf).
    - Create an account for replication.
    - Lock the database and perform backup.
    - Grant privileges for replication.
    - Unlock the database.
- Secondary (backup) SQL server.
    - Copy the database from the primary SQL server.
    - Modify the configuration file (/etc/my.cnf).
    - Configure parameters for connecting to the primary.
    - Start the secondary SQL server.

The configuration procedure in this example assumes that Linux has been installed and configured with basic network connections and access to a public network, and FortiAnalyzer has been installed and configured with the same access to the network.

1) Setup the MySQL database

Step 1.1 – Install the MySQL server

The first step is to install the MySQL server onto the Linux computer. There are several ways to accomplish this. In this example, the command 'yum' is used.
The public network connection and DNS server should be configured on Linux prior to this step.

The output below shows the CLI on Linux. Command yum install mysql-server will check dependencies, download and install the required packages:

[root@Linux_A ~]# yum install mysql-server
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
* base: centos.corenetworks.net
* extras: mirror.steadfast.net
* updates: mirrors.syringanetworks.net
Setting up Install Process
:
:
:
Transaction Summary
============================================================
Upgrade 4 Package(s)
Total download size: 12 M
Is this ok [y/N]: y

Step 1.2 – Changing the default storage engine to MyISAM

FortiAnalyzer software supports the MyISAM storage engine, not the default MySQL server engine InnoDB.
Configure the MySQL server instance for MyISAM by modifying the /etc/my.cnf file.

Note: Location of the configuration file may vary, and bind-address may be required to configure in my.cnf file. It depends on the OS, and the version of MySQL software.

The output below shows the /etc/my.cnf file. Option default-storage-engine=MyISAM needs to be added:

# /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log=/var/log/mysqlquery.log

log-error=/var/log/mysqlerr.log

log-warnings=1

general_log_file=/var/log/general.log

general_log=1

default-storage-engine=MyISAM <====

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

Step 1.3 – Run the initial configuration

The MySQL server package has an initial configuration script to provide the basic configuration for using MySQL server. Before running the script, the mysql server process needs to be started.

The CLI output below shows the command for starting the mysql server process:

[root@linux ~]# /etc/rc.d/init.d/mysqld start　
MySQL database initializing: Installing MySQL system tables...
OK
Filling help tables...
OK

The CLI output below shows the command mysql_secure_installation. This will configure basic settings. In this example, all default settings are selected, except password:

[root@linux ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n]

Step 1.4 – Grant privileges

At this point, the mysql server instance is configured, and is available to be accessed from the localhost only. To access the server from the FortiAnalyzer unit, a new user needs to be created.

Note: A semicolon (;) is needed at the end of each sql command.

The CLI output below shows the command to login to the mysql server from the localhost (mysql –u root –p). A password may be required, which was configured when the initial configuration script was run.

This output also shows how to grant privileges for user root, password ‘test123’ from the FortiAnalyzer source IP address (10.10.0.0/24).

# mysql -u root -p
mysql> grant all privileges on *.* to root@'10.10.0.0/255.255.255.0' IDENTIFIED BY 'test123';
mysql> select user,host,password from mysql.user;
+-----------+-------------------------+---------------+
| user      | host                    | password      |
+-----------+-------------------------+---------------+
| root      | localhost               | ***********   |
| root      | 10.10.0.0/255.255.255.0 | ***********   |
+-----------+-------------------------+---------------+
mysql> flush privileges;
CP port 3306 needs to be open on the computer for the MySQL access.

2) Configure FortiAnalyzer units

Step 2.1 – Configure the first FortiAnalyzer unit (FAZ-A)

The next step is configuring the FortiAnalyzer unit. By default, it uses a local PostgreSQL database. This needs to be changed to the remote MySQL database.
The following is a list of required configuration entries:

# set status remote

# set database-name <database name>

# set database-type mysql

# set server <mysql server-ip>

# set username <user name for accessing mysql server>

# set password <password for accessing mysql server>

Note: Existing data in the local FortiAnalyzer database will not be copied to the remote MySQL instance.

The output below shows the configuration for the external MySQL database on the FortiAnalyzer unit. The server IP address is 192.168.11.20, userid/password is root/test123 and the database-name is 'faz'.

(sql)# show
config system sql
set status remote
set database-name "faz"
set database-type mysql
set password ENC I52PVYmWL5fH7...QblN7bkA9hOrLYhxy
set server "192.168.11.20“
set username “root”

Step 2.2 – Configure another FortiAnalyzer unit (FAZ-B).

To configure another FortiAnalyzer unit, repeat the step 2.1 on another FortiAnalyzer unit (FAZ-B).

Step 2.3 – Register all devices on all FortiAnalyzer units.

Make sure that all FortiGate units sending logs are registered on ALL FortiAnalyzer units.

3. Check the MySQL database

Step 3.1 – Check that the new database is created on the MySQL server

Now that the FortiAnalyzer unit is ready to send SQL data to the external MySQL database, MySQL will create a database named as configured on the FortiAnalyzer unit (“faz” in this example) and a <name>_hcache (faz_hcache) database as well.

Note: These databases will be created after receiving logs from a FortiAnalyzer unit.

The output below shows the database information. First you need to login to the local database on the MySQL server with mysql –u root –p and type the correct password, then you will be able to see these databases using the show databases command:

Step 3.2 – Check tables

The MySQL database tables will also be created after the device is registered. Without registering the device, there will be no tables in the database.

Note: These databases will be created after receiving logs from a FortiAnalyzer unit.

The output below shows the database table information. Tables will be created for each device and log type:

mysql> use faz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with –A
mysql> show tables;
+-----------------------------------------+
| Tables_in_faz                           |
+-----------------------------------------+
| FGT60C3G12031337-alog-1380800000-34e200 |
| FGT60C3G12031337-slog-1380800000-34e200 |
| FGT60C3G12031337-tlog-1380800000-e61a80 |
| FGT60C3G12031337-vlog-1380800000-34e200 |
| FGT60C3G12031337-wlog-1380800000-34e200 |
| FGT-tlog-test-050228                    |
| alert_logs                              |
| alerts                                  |
| log_tablst                              |
| table_ref                               |
+-----------------------------------------+
10 rows in set (0.00 sec)
mysql>

Step 3.3 – Setup of external SQL database is finished

The external SQL database configuration is done, and now all logs will be stored to the external SQL database.
There are the following facts to be aware of:
- Real-time log viewer may not work. It requires logs from FortiGate unit to go through the FortiAnalyzer unit. For example, if logs are forwarded to only FAZ-A from FortiGate units in this example, the real-time log viewer does not work on FAZ-B.
- The commands exec sql-local rebuild-db/rebuild-device do not work with the external SQL database. These commands work only for local SQL database.

4) Replicate the SQL database (optional)

4.1 Primary SQL Server

Step 4.1.1 – Modify the configuration file (/etc/my.cnf)

The primary DB server must be configured for binary logging for replication. The log-bin option needs to be added in the /etc/my.cnf file.
The server-id option also needs to be configured. (example: server-id = X)

Note: The server-id option value has to be a unique number.

The output below shows /etc/my.cnf file information:

[mysqld]
log-bin
server-id=1

Step 4.1.2 – Create an account for replication

A new account on the primary SQL server needs to be created for replication services to function with the secondary SQL server.

Values in the following example are:
userid:   repl
password:   password
secondary SQL IP address:   192.168.101.154

Step 4.1.3 – Performing the database backup and copy to the secondary SQL server

A database backup must be performed on the primary SQL database and copied to the secondary SQL database.

First, the primary SQL database needs to be locked to prevent any updates. The flush tables with read lock command lock the database. Check the File and Position with the show master status command ('mysqld-bin.000001' and '503' in this example). These will be used when copying the date on the secondary SQL server (in step 4.2.3).
To lock the database and get 'Position':

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 | 503 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Then, archive (backup) the database file using the tar command, and unlock the table on primary SQL server.
To take the backup and unlock table on primary SQL server:

[root@Linux_A ~]# cd /var/lib/mysql
[root@Linux_A mysql]# tar cvf /root/sqldata.tar ./faz/
./faz/
./faz/FGT60C3G12031337@002dtlog@002d1386800000@002d161a80.frm
./faz/FGT60C3G12031337@002dwlog@002d1387080000@002d157e40.frm
./faz/FGT60C3G12031337@002dalog@002d1387080000@002d157e40.MYI
:
:
:
./faz/alert_logs.frm
./faz/FGT60C3G12031337@002dslog@002d1387080000@002d157e40.frm
./faz/log_tablst.frm
[root@Linux_A mysql]#

4.2 Secondary (backup) SQL Server.

Step 4.2.1 – Setup MySQL software on the secondary (backup) server

Repeat steps 1.1 to 1.4, if MySQL software is not set up on the secondary server yet.

Step 4.2.2 – Copy the primary SQL database file

Copy the backup file to the secondary SQL server. This example uses SCP on the secondary server:

[root@Linux_B ~]# scp root@192.168.101.156:/root/sqldata.tar .
The authenticity of host '192.168.101.156 (192.168.101.156)' can't be established.
RSA key fingerprint is 78:b0:23:fc:b9:2e:b6:35:f4:bd:08:09:60:db:fa:68.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.101.156' (RSA) to the list of known hosts.
root@192.168.101.156's password:
sqldata.tar
100% 47MB 46.5MB/s 00:00
[root@CentOS_B ~]# ls

Restore the archived file at /var/lib/mysql directory:

[root@Linux_B ~]# cd /var/lib/mysql/
[root@Linux_B mysql]#
[root@Linux_B mysql]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock
[root@Linux_B mysql]# tar xvf /root/sqldata.tar .
./faz/
./faz/FGT60C3G12031337@002dtlog@002d1386800000@002d161a80.frm
./faz/FGT60C3G12031337@002dwlog@002d1387080000@002d157e40.frm
./faz/FGT60C3G12031337@002dalog@002d1387080000@002d157e40.MYI
:
:
:
./faz/alert_logs.frm
./faz/FGT60C3G12031337@002dslog@002d1387080000@002d157e40.frm
./faz/log_tablst.frm
[root@Linux_B mysql]# ls
faz ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock
[root@CentOS_B mysql]#

Step 4.2.2 – Modify the configuration file on the secondary SQL server (/etc/my.cnf)

Add the server-id to /etc/my.cnf file on the secondary SQL server. This number has to be unique.
In this example, secondary server ID is set to '2':

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-storage-engine=MyISAM
server-id=2 ## Set server id 2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 4.2.3 – Set parameters for connecting to the primary server, and start the replication

On the secondary server, there are some parameters that need to be set for connecting to the server.
After setting all parameters, start the replication process with the “start slave” command on the secondary server.

Note: Values of master_log_file and master_log_pos parameters are the same as in step 4.1.3.

To set parameters on the secondary server and start the replication:

mysql> change master to
    -> master_host='192.168.101.156',
    -> master_user='repl',
    -> master_password='password',
    -> master_log_file='mysqld-bin.000001',
    -> master_log_pos=503;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Step 4.2.4 – Check the replication status

The MySQL log will be generated to /var/log/mysql.log. Ensure the secondary server is connected to the primary:

[root@Linux_B log]# grep replication /var/log/mysqld.log
140318 15:41:55 [Note] Slave SQL thread initialized, starting replication in log 'mysqld-bin.000001' at position 503, relay log './mysqld-relay-bin.000001' position: 4
140318 15:41:55 [Note] Slave I/O thread: connected to master 'repl@192.168.101.90:3306',replication started in log 'mysqld-bin.000001' at position 503

Other related commands for checking SQL database status:

I. On the primary server

The command show binlog events shows events in binary log:

mysql> show binlog events;
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------+
| Log_name          | Pos | Event_type | Server_id | End_log_pos | Info                                                                            |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------+
| mysqld-bin.000001 |   4 | Format_desc |         1 |         106 | Server ver: 5.1.71-log, Binlog ver: 4                                           |
| mysqld-bin.000001 | 106 | Query       |         1 |         230 | revoke all privileges on *.* from repl@'10.0.0.2'                               |
| mysqld-bin.000001 | 230 | Query       |         1 |         349 | delete from mysql.user where user='repl' and host='10.0.0.2'                    |
| mysqld-bin.000001 | 349 | Query       |         1 |         503 | grant replication slave on *.* to repl@192.168.101.154 identified by 'password' |
| mysqld-bin.000001 | 503 | Query       |         1 |         657 | grant replication slave on *.* to repl@192.168.101.154 identified by 'password' |
| mysqld-bin.000001 | 657 | Query       |         1 |         783 | delete from mysql.user where user='repl' and host='192.168.101.154'             |
| mysqld-bin.000001 | 783 | Query       |         1 |         936 | grant replication slave on *.* to repl@192.168.101.91 identified by 'password' |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

II. On the secondary (backup) server

The command stop slave/start slave stops/starts replication on the secondary server:

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>

The command show slave status shows status of the secondary server:

mysql> show slave status;
+----------------------------------+----------------+-------------+-------------+-
| Slave_IO_State                   | Master_Host    | Master_User | Master_Port |
+----------------------------------+----------------+-------------+-------------+-
| Waiting for master to send event | 192.168.101.90 | repl        |        3306 |
+----------------------------------+----------------+-------------+-------------+-
    :
    :
    :
+------------------+-------------------+-     -+-----------------------+-
| Slave_IO_Running | Slave_SQL_Running |       | Seconds_Behind_Master |
+------------------+-------------------+-     -+-----------------------+-
| Yes              | Yes               |       |                     0 |
+------------------+-------------------+-     -+-----------------------+-

Values Slave_IO_Running and Slave_SQL_Running have to be set to “yes”, otherwise replication does not work well.
The value “Seconds_Behind_Master” shows how much delay process on the secondary server. This indicates time to finish the replication.

Configure the FortiAnalyzer unit FAZ-C (optional)

Step 4.3.1 – Setup MySQL on a secondary server

Repeat step 2.1 and change parameters as necessary (SQL server IP address is the secondary SQL server address) on the FortiAnalyzer unit (FAZ-C).

Step 4.3.1 – Register all devices on all FortiAnalyzer units

Make sure that all devices sending logs are registered on ALL FortiAnalyzer units.
Note: Real time log viewer will not work on FAZ-C, because logs from FortiGate units are not directly received by this FortiAnalyzer unit.

Related Information

For more information on how to configure and troubleshoot FortiGate and FortiAnalyzer units, please visit:
http://docs.fortinet.com/

For more information about MySQL software, please visit:
https://dev.mysql.com/

Fortinet Community

Technical Tip: Configuration Example: FortiAnalyzer External SQL and High Availability