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
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.
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:
# Disabling symbolic-links is recommended to prevent assorted security risks
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:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | faz | <=== | faz_hcache | <=== | mysql | +--------------------+ 4 rows in set (0.00 sec) ** faz and faz_hcache will be created after receiving log from FAZ. **
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
# mysql –u root –p mysql> grant replication slave on *.* to firstname.lastname@example.org identified by 'passwor Query OK, 0 rows affected (0.00 sec) mysql> select user,host,password from mysql.user; +------+-------------------------+-------------------------------------------+ | user | host | password | +------+-------------------------+-------------------------------------------+ | root | localhost | *676243218923905CF94CB52A3C9D3EB30CE8E20D | | root | 127.0.0.1 | *676243218923905CF94CB52A3C9D3EB30CE8E20D | | root | 192.168.0.0/255.255.0.0 | *676243218923905CF94CB52A3C9D3EB30CE8E20D | | repl | 192.168.101.154 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | +------+-------------------------+-------------------------------------------+ 4 rows in set (0.00 sec)
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:
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 email@example.com:/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. firstname.lastname@example.org'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@email@example.com ./faz/FGT60C3G12031337@002dwlog@firstname.lastname@example.org ./faz/FGT60C3G12031337@002dalog@002d1387080000@002d157e40.MYI : : : ./faz/alert_logs.frm ./faz/FGT60C3G12031337@002dslog@email@example.com ./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:
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.
For more information on how to configure and troubleshoot FortiGate and FortiAnalyzer units, please visit: http://docs.fortinet.com/
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.