All Products
Search
Document Center

Elasticsearch:Use user-created Filebeat to collect MySQL logs

Last Updated:May 22, 2023

If you want to view and analyze MySQL logs such as slow logs and error logs, you can use Filebeat to collect MySQL logs. Filebeat then sends the logs to Alibaba Cloud Elasticsearch. You can query, analyze, and present these logs in the Kibana console in a visualized manner.

Procedure

  1. Make preparations

    Create an Alibaba Cloud Elasticsearch cluster and an Elastic Compute Service (ECS) instance. The Elasticsearch cluster is used to receive the MySQL logs that are collected by Filebeat. It also provides the Kibana console to query, analyze, and present these logs in a visualized manner. The ECS instance is used to install MySQL and Filebeat.

  2. Step 1: Install and configure MySQL

    Install MySQL and configure error log files and slow query log files in the MySQL configuration file. Then, Filebeat can collect your desired logs.

  3. Step 2: Install and configure Filebeat

    Install Filebeat. Filebeat is used to collect MySQL logs and send the logs to your Elasticsearch cluster. You must enable the MySQL module in Filebeat and specify the URLs that are used to access your Elasticsearch cluster and the Kibana console of the cluster in the Filebeat configuration file.

  4. Step 3: Use the Kibana dashboard to present MySQL logs

    Perform a query test, and present the error logs and slow query logs that you want to view and analyze on the dashboard of the Kibana console.

Make preparations

Step 1: Install and configure MySQL

  1. Connect to the ECS instance.

    For more information, see Connect to a Linux instance by using a password.

    Note

    In this example, a common user is used.

  2. Download and install the MySQL source.

    wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
    sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
  3. Install MySQL.

    sudo yum install mysql-server
  4. Configure error log files and slow query log files in the my.cnf file.

    Note

    By default, the configuration of log files in MySQL is disabled. You must manually enable the log file configuration. You can also enable temporary slow logs by running a MySQL command.

    1. Open the my.cnf file.

      sudo vim /etc/my.cnf
    2. Configure log files.

      [mysqld]
      log_queries_not_using_indexes = 1
      slow_query_log=on
      slow_query_log_file=/var/log/mysql/slow-mysql-query.log
      long_query_time=0
      
      [mysqld_safe]
      log-error=/var/log/mysql/mysqld.log

      Parameter

      Description

      log_queries_not_using_indexes

      Specifies whether to record a query for which no indexes are specified as a slow query log. The value 1 indicates that the system records a query for which no indexes are specified as a slow query log. The value 0 indicates that the system does not record a query for which no indexes are specified as a slow query log.

      slow_query_log

      Specifies whether to enable slow query logs. The value on indicates that slow query logs are enabled. The value off indicates that slow query logs are disabled.

      slow_query_log_file

      Specifies the storage path of slow query logs.

      long_query_time

      Specifies the time threshold used to define a slow query log. Unit: seconds. When the query time exceeds the threshold, the MySQL database writes the query into the file that is specified by slow_query_log_file.

      Important

      For the convenience of the test, the value of this parameter is set to 0. You can specify this parameter based on your business requirements.

    3. (Optional) Create log files.

      Important

      MySQL 5.6 does not automatically create log files. You must manually create the log files.

      sudo mkdir /var/log/mysql
      sudo touch /var/log/mysql/mysqld.log
      sudo touch /var/log/mysql/slow-mysql-query.log
    4. Grant read and write permissions on the log files to all users.

      sudo chmod 777 /var/log/mysql/slow-mysql-query.log /var/log/mysql/mysqld.log
  5. Start MySQL and check its status.

    sudo systemctl start mysqld
    sudo systemctl status mysqld

Step 2: Install and configure Filebeat

  1. Log on to the Kibana console of the Elasticsearch cluster.

    For more information, see Log on to the Kibana console.

  2. In the Visualize and Explore Data section, click Logs.

  3. On the page that appears, click View setup instructions.

  4. On the Add Data to Kibana page, click MySQL logs.

  5. In the Getting Started section, click the RPM tab.

    Note

    The Linux operating system is used in this topic. Therefore, RPM is selected. You can select an appropriate installation method based on your operating system.

  6. Install Filebeat on the ECS instance as prompted.

  7. Modify the configuration of the MySQL module and specify the files of the error logs and slow logs that you want to collect.

    1. Enable the MySQL module.

      sudo filebeat modules enable mysql
    2. Open the mysql.yml file.

      sudo vim /etc/filebeat/modules.d/mysql.yml
    3. Modify the configuration of the MySQL module.

      Modify the configuration of the MySQL module
      - module: mysql
        # Error logs
        error:
          enabled: true
          var.paths: ["/var/log/mysql/mysqld.log"]
          # Set custom paths for the log files. If left empty,
          # Filebeat will choose the paths depending on your OS.
          #var.paths:
      
        # Slow logs
        slowlog:
          enabled: true
          var.paths: ["/var/log/mysql/slow-mysql-query.log"]
          # Set custom paths for the log files. If left empty,
          # Filebeat will choose the paths depending on your OS.
          #var.paths:

      Parameter

      Description

      enabled

      Set this parameter to true.

      var.paths

      Set this parameter to the path of the log file. The path must be the same as the path that is specified in the MySQL configuration file. For more information, see Step 1: Install and configure MySQL.

  8. Configure the filebeat.yml file.

    1. Open the filebeat.yml file.

      sudo vim /etc/filebeat/filebeat.yml
    2. Modify the configuration of Filebeat modules.

      Configuration of Filebeat modules
      filebeat.config.modules:
        # Glob pattern for configuration loading
        path: /etc/filebeat/modules.d/mysql.yml
      
        # Set to true to enable config reloading
        reload.enabled: true
      
        # Period on which files under path should be checked for changes
        reload.period: 1s
    3. Modify the configuration of Kibana.

      Modify the configuration of Kibana
      setup.kibana:
      host: "https://es-cn-0pp1jxvcl000*****.kibana.elasticsearch.aliyuncs.com:5601"

      host: the URL that is used to access the Kibana console. You can obtain the URL on the Kibana configuration page. For more information, see View the public endpoint of the Kibana console. Specify the URL in the format of <Public endpoint of the Kibana console>:5601.

    4. Modify the configuration of the Elasticsearch cluster.

      Modify the configuration of the Elasticsearch cluster
      output.elasticsearch:
        # Array of hosts to connect to.
        hosts: ["es-cn-0pp1jxvcl000*****.elasticsearch.aliyuncs.com:9200"]
        # Optional protocol and basic auth credentials.
        #protocol: "https"
        username: "elastic"
        password: "<your_password>"

      Parameter

      Description

      hosts

      The URL that is used to access the Elasticsearch cluster. Specify the URL in the format of <Internal or public endpoint of the Elasticsearch cluster>:9200. You can obtain the internal or public endpoint on the Basic Information page of the cluster. For more information, see View the basic information of a cluster.

      Note

      If the ECS instance and Elasticsearch cluster reside in the same virtual private cloud (VPC), use the internal endpoint. Otherwise, use the public endpoint. If you use the public endpoint to access the Elasticsearch cluster, you must configure a whitelist for access to the Elasticsearch cluster over the Internet. For more information, see Configure a public or private IP address whitelist for an Elasticsearch cluster.

      username

      The username that is used to access the Elasticsearch cluster. Default value: elastic.

      password

      The password that corresponds to the elastic username. The password is specified when you create the Elasticsearch cluster. If you forget the password, you can reset it. For more information about the procedure and precautions for resetting the password, see Reset the access password for an Elasticsearch cluster.

  9. Run the following command to start Filebeat:

    sudo filebeat setup
    sudo service filebeat start

Step 3: Use the Kibana dashboard to present MySQL logs

  1. Restart MySQL in the ECS instance and query logs for tests.

    Run the following command to restart MySQL:

    sudo systemctl restart mysqld
  2. View the queried logs.

    The following figures show the queried logs.

    Figure 1. Slow logsSlow logs

    Figure 2. Error logsError logs

  3. Log on to the Kibana console of the Elasticsearch cluster.

    For more information, see Log on to the Kibana console.

  4. In the left-side navigation pane, click Dashboard.

  5. On the Dashboards page, click [Filebeat MySQL] Overview.

  6. Select a time range in the upper-right corner and view the logs in the time range.

    View logs