Installing the MySQL DB App¶
Note
This setup follows the same procedure as creating a data connection in the MySQL app. If you have already created a data connection in the app, you do not need to perform these steps again.
Install the MySQL DB for Observe using the App section under Workspace Setting.
Figure 1 - MySQL app card
The default configuration settings of the app may be sufficient for your needs, but you should consider overwriting the defaults for the following options:
Datastream - select the datastream receiving your MySQL DB metrics and logs
Global Variable Resource Attributes - A list of global variables to add as resource attributes to your MySQL DB instances. The list includes reasonable default values, but you can add extra values of particular interest.
Name Format - The default value of
MySQL/%splaces your MySQL DB resources within theMySQLfolder, but you may want to put them in a different folder.____ App - Several other apps may contain logs or metadata relevant to your MySQL instances, such as apps for Host Monitoring or various cloud providers. Use these App variables to link your logs and metadata from other apps to your MySQL DB app data.
Linking your MySQL DB data to other apps¶
Select the option for Manual install (Advanced) as you continue with the MySQL app installation. If you have already installed apps for Host Monitoring or any cloud providers, you can select the corresponding app to link their data with the data in your MySQL app. If you haven’t yet installed any of those apps, you can always return to this configuration and update it later.
The Observe MySQL app uses Telegraf to capture MySQL DB health metrics and FluentBit to capture MySQL DB logs. Use the following instructions to configure your MySQL DB instances to allow Telegraf and FluentBit to collect your data and how to install Telegraf and FluentBit for data collection.
Data Collection on Your Managed Instances¶
Configure your MySQL DB instance to write logs to a file where FluentBit can capture them. For the complete depth of observability, Observe recommends enabling all your MySQL DB logs and writing them to the following file paths in your
my.confor equivalent file:
[mysqld_safe]
log_error = /var/log/mysql/error.log
[mysqld]
general_log = on
general_log_file = /var/log/mysql/mysql.log
log_error = /var/log/mysql/error.log
slow_query_log = on
slow_query_log_file = /var/log/mysql/mysql_slow.log
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
Be sure to restart your MySQL DB service if necessary for any of these changes to take effect.
2. Prepare your MySQL DB instance to receive connections from Telegraf by running the following commands. You can also save and execute them as a file. Be sure to replace the ${MYSQL_PASSWORD_FOR_TELEGRAF} value with a secure string and have it on hand because you need it for additional configurations.
CREATE USER 'telegraf-client'@'localhost' IDENTIFIED WITH mysql_native_password by '${MYSQL_PASSWORD_FOR_TELEGRAF}';
GRANT REPLICATION CLIENT ON *.* TO 'telegraf-client'@'localhost';
GRANT PROCESS ON *.* TO 'telegraf-client'@'localhost';
GRANT SELECT ON performance_schema.* TO 'telegraf-client'@'localhost';
ALTER USER 'telegraf-client'@'localhost' WITH MAX_USER_CONNECTIONS 5;
3. Install Telegraf and FluentBit. Observe recommends using the 1-line installation instructions for the Observe Host Monitoring app.
4. Add a Telegraf config file at /etc/telegraf/telegraf.d/inputs.mysql.conf with the following content. Be sure to replace the ${MYSQL_PASSWORD_FOR_TELEGRAF} value with your previous secure string.
[[inputs.mysql]]
servers = ["telegraf-client:${MYSQL_PASSWORD_FOR_TELEGRAF}@tcp(localhost:3306)/?tls=false"]
metric_version = 2
gather_perf_events_statements = true
gather_global_variables = true
interval_slow = "10m"
MySQL Performance Schema is enabled by default and required for some dashboard content to be displayed.
5. Add a FluentBit configuration file at /etc/td-agent-bit/observe-mysql.conf with this content. If you used custom log file path values, use those instead.
[INPUT]
name tail
tag tail_mysql_error
Path_Key path
path /var/log/mysql/error.log
Read_from_Head False
db mysql-error-snapshots.db
[INPUT]
name tail
tag tail_mysql_general
Path_Key path
path /var/log/mysql/mysql.log
Read_from_Head False
db mysql-general-snapshots.db
[INPUT]
name tail
tag tail_mysql_slow_query
Path_Key path
path /var/log/mysql/mysql_slow.log
Read_from_Head False
db mysql-slow-query-snapshots.db
6. Restart Telegraf using the command, service telegraf restart.
7. Restart Fluent Bit using the command, service td-agent-bit restart.
Data collection - on instances you do not manage¶
Enable Log Collection for your MySQL DB instance, following the instructions for your cloud provider.
2. Follow the instructions for sending CloudWatch Logs into Observe.
3. You can then collect those logs by installing the Observe App for that cloud provider.
4. Prepare your MySQL DB instance to receive remote connections from Telegraf by running the following commands. You can also save and execute them as a file. Be sure to replace the ${MYSQL_PASSWORD_FOR_TELEGRAF} value with a secure string and keep it on hand because you need it for additional configurations.
CREATE USER 'telegraf-client'@'%' IDENTIFIED WITH mysql_native_password by '${MYSQL_PASSWORD_FOR_TELEGRAF}';
GRANT REPLICATION CLIENT ON *.* TO 'telegraf-client'@'%';
GRANT PROCESS ON *.* TO 'telegraf-client'@'%';
GRANT SELECT ON performance_schema.* TO 'telegraf-client'@'%';
ALTER USER 'telegraf-client'@'%' WITH MAX_USER_CONNECTIONS 5;
For MariaDB instances, the equivalent commands consist of the following:
SET old_passwords=0;
CREATE USER 'telegraf-client'@'%' IDENTIFIED BY '${MYSQL_PASSWORD_FOR_TELEGRAF}';
GRANT REPLICATION CLIENT ON *.* TO 'telegraf-client'@'%';
GRANT PROCESS ON *.* TO 'telegraf-client'@'%';
GRANT SELECT ON performance_schema.* TO 'telegraf-client'@'%';
ALTER USER 'telegraf-client'@'%' WITH MAX_USER_CONNECTIONS 5;
5. On a server that you manage, install Telegraf. Observe recommends using the 1-line installation instructions for the Observe Host Monitoring app. If you would like to manually configure telegraf, refer to the telegraf installation instructions.
6. Create a /etc/telegraf/telegraf.d/inputs.mysql.conf file with the following content, including one server list entry for each MySQL DB instance you wish to monitor. Be sure to replace the ${MYSQL_PASSWORD_FOR_TELEGRAF} value with your previous secure string. Replace the ${HOST_*} and ${PORT_*} values with those appropriate for each MySQL DB instance you wish to monitor.
[[inputs.mysql]]
servers = [
"telegraf-client:${MYSQL_PASSWORD_FOR_TELEGRAF}@tcp(${HOST_1}:${PORT_1})/?tls=false",
"telegraf-client:${MYSQL_PASSWORD_FOR_TELEGRAF}@tcp(${HOST_2}:${PORT_2})/?tls=false",
...
"telegraf-client:${MYSQL_PASSWORD_FOR_TELEGRAF}@tcp(${HOST_N}:${PORT_N})/?tls=false",
]
metric_version = 2
gather_perf_events_statements = true
gather_global_variables = true
interval_slow = "10m"
7. Restart Telegraf: service telegraf restart.
Using Custom Log Shippers¶
Observe recommends using FluentBit to collect your MySQL DB logs for the MySQL DB instances you manage, but you may use another logging software package if you prefer. Configure your log shipper to forward your MySQL DB logs to your Observe account, and create a dataset that contains your MySQL DB logs. For best results, this dataset should have as many as possible of the following fields (case sensitive):
timestamp(of type timestamp, required)inputType(string, to indicate where the logs originated)message(string)host(string, the host or instance that generated the log event)datacenter(string, the datacenter for the host or instance that generated the log event)logType(string, usually one of “general”, “error”, “slowquery”, or “audit”)tags(map of strings, for example,{"owner": "Terra Branford", "team": "Returners"})event(map of strings, for example,{"az": "figaro-south-1"})
Once your dataset exists, you can input the complete name in the MySQL app Advanced options under the custom_logs_dataset_name input.