Set up the Splunk Add-on for MySQL

You can configure the add-on through Splunk Web or by making changes directly in the configuration files. Due to the complexity of the setup, it is recommended to configure the add-on through Splunk Web. All the necessary data inputs will be automatically generated after completing the setup for the add-on.

Set up the add-on using Splunk Web

You can begin the setup immediately after installing the add-on to your data collection node by clicking the Set Up Now button when you receive the "Install Successful" message. If you did this, skip to step 4 below. If you selected Set Up Later, start with step 1.

1. On the Splunk instance that you want to be responsible for data collection from MySQL, usually a heavy forwarder, go to the Splunk Web home screen.

2. From the top navigation bar, click Apps > Manage Apps.

3. In the row for Splunk Add-on for MySQL, click Set up. The add-on setup screen displays.

4. Under Global Settings, type the name of the index in which to store this data. The default value is the default Splunk index, main.

5. If you want to change the Log level, select a new level from the drop down menu. The choices are INFO, DEBUG, and ERROR.

6. Under Data Collection Settings, check the Local data collection settings box to provide the settings for your local MySQL database server.

7. Provide the the IP address of your MySQL database server in the MySQL database host field. The default value is 127.0.0.1.

8. Provide the port number in the MySQL database listening port field. The default value is 3306.

9. Provide your MySQL database username in the MySQL database user field.

10. Type your MySQL database password in the MySQL database password field.

11. Retype the password in the Confirm password field.

Note: The Splunk platform encrypts the MySQL database username and password as soon as you enter these values.

12. Check the Enable data collection from MySQL log files box if you would like Splunk to discover the MySQL log files in the file system and generate the file monitoring data inputs automatically.

Note: If you check this box, after you save the settings on this page the file monitoring data inputs will be created. Afterwards, if you return to the setup page, this box will be unchecked since the inputs have already been created.

13. To enable data collection from MySQL binary log files, enter the date and time in the local time zone of the MySQL server at which to begin the log collection in the format specified in the Start date/time (in local time) of bin log text box. The add-on will collect events from the MySQL binary log starting from the date and time specified. If this field is empty, the default start date of 7 days ago will be used.

14. Under Splunk DB Connect Settings, check the Enable DB Connect data inputs for MySQL box to automatically create the necessary DB Connect inputs to collect performance and configuration logs. Specify the data sources in the text box below.

Note: The Splunk DB Connect must be installed and configured with the correct java path and the MySQL jdbc driver must be installed. See the Deploy and Use Splunk DB Connect section of the Splunk DB Connect manual for information.

Note: There are more than 35 DB Connect data inputs for a single MySQL server, so it is highly recommended to use this feature to create the inputs. If this box is not checked, you need to manually configure the DB Connect data inputs.

15. In the text box, provide the MySQL username, password, and hostname (and port if it is not listening on the default port of 3306) for each MySQL database server for which to collect logs using the format specified in the examples below.

Example scenarios:

Case 1: You have several MySQL database servers in your production environment and all of them are using the same username/password and listening on the default port (3306). In this scenario, the configuration will look similar to the example below.

mysql_username|mysql_password|mysql_server_host1,mysql_server_host2,mysql_server_host3

The username, password, and hosts are separated by a pipe character (|) and the hosts themselves are separated by a comma (,).

Case 2: In your production environment, there are several MySQL database servers and not all of them are using the same username/password or listening on the same port. MySQL database servers with shared configurations can be grouped using semi-colons (;) as illustrated in the example below.

mysql_username|mysql_password|mysql_server_host1,mysql_server_host2;mysql_username2|mysql_password2|mysql_server_host3:3307

In the example above, mysql_server_host1 and mysql_server_host2 share the same username and password and the default port. But mysql_server_host3 is using a different username/password combination and listening on a non-default port, 3307. Note that each grouping is separated by a ;.

Note: Splunk DB Connect will use the usernames/passwords specified here to perform data collection from the mysql, performance_schema, and information_schema databases. The account you use must have permissions to access these databases.

When this configuration is saved, the add-on will generate DB Connect data inputs for all of the specified MySQL servers. These inputs will be disabled by default.

16. Click Save.

Next, you need to enable the data inputs created by the add-on as described in Configure inputs for the Splunk Add-on for MySQL.

Set up the add-on using the configuration files

You can configure your add-on by providing the settings in the local mysql_db.conf file.

1. Open the mysql_db.conf file in in the default folder of the add-on:

• $SPLUNK_HOME/etc/apps/Splunk_TA_mysql/default on Unix based systems.
• %SPLUNK_HOME%\etc\apps\Splunk_TA_mysql\default on Windows systems.

The contents look like this:

[mysql_global_settings]                                                                                                                                  
index = main                                                                      
log_level = INFO                                                                  
                                                                                    
[mysql_data_collection_settings]
hostname = 127.0.0.1                                                              
port = 3306                                                                       
username =                                                                        
password =  
                                                   
mysql_file_monitor_enabled = 1                                                    
                                                                                    
# start datetime in "%Y-%m-%d %H:%M:%S" format, for e.g, "2015-06-01 00:00:00" 
mysql_binlog_start_date =                                                         
                                                                                    
[mysql_dbx_settings]                                                                                                                                                
# Enable DBX2 inputs generation by setting mysql_dbx_enabled to 1                 
mysql_dbx_enabled = 0                                                             
                                                                                    
# When mysql_dbx_enabled and mysql_db_creds is setup, the TA will create DBX2. 
# data inputs for MySQL. The value of it should be specified as in the following.
# format. Note if port is not specified, it will be default to 3306 and if db name
# is not specified, it will be default to "mysql"                                 
# username|password|db_name|host[:port],host[:port],...;username|password|db_name|host[:port],host[:port],...;
# For example:                                                                    
# username|password|mydb|host1:3307,host2,...;username2|password2|host3,host4,...
# The above example means host1 and host2 share the same username/password and have the
# same db name "mydb", but host1 has a customed listening port (3307).            
# host3 and host4 share the same username2/password2 and both of them have default db name
# which is "mysql" and default listening port which is "3306"                     
mysql_db_creds =                                                                  
                                                                                    
[mysql_log_files]                                                                 
                                                                                    
# log_files can be full file path, separated by ","                               
log_files = log_error,general_log_file,slow_query_log_file                        
                                                                                    
# sourcetypes are corresponding to each log file in the "log_files"                
# separated by ","                                                                
sourcetypes = errorLog,generalQueryLog,slowQueryLog

2. Create a file named mysql_db.conf in the local folder of the add-on:

• $SPLUNK_HOME/etc/apps/Splunk_TA_mysql/local on Unix based systems.
• %SPLUNK_HOME%\etc\apps\Splunk_TA_mysql\local on Windows systems.

3. Copy the applicable stanzas from the default mysql_db.conf to the local mysql_db.conf and provide the necessary values in the local mysql_db.conf file, referring to the comments in the default mysql_db.conf file for guidance. For more information, see the instructions in the Set up the add-on using Splunk Web section above. You do not need to change the configuration in the mysql_log_files stanza.

4. After updating the local mysql_db.conf, restart the Splunk platform in order to make the changes. The username/password will be encrypted automatically the first time the Splunk Add-on for MySQL starts.

Next, you need to enable the data inputs created by the add-on as described in Configure inputs for the Splunk Add-on for MySQL .