Elasticsearch:Use self-managed Filebeat to collect MySQL logs

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

• Create an Alibaba Cloud Elasticsearch cluster.

  In this example, an Elasticsearch V6.7.0 cluster of the Standard Edition is used. For more information, see Create an Alibaba Cloud Elasticsearch cluster.

• Create an Alibaba Cloud ECS instance.

  In this example, an ECS instance that runs CentOS is used. For more information, see Create an instance by using the wizard.

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.

      

      - 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.

      

      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.

      

      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.

      

      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 logs

   Figure 2. Error 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.