How to access the binary log of a Managed MySQL database?

September 25, 2019 143 views
DigitalOcean MySQL Logging

Hello, I’m just starting with a managed MySQL database and I’m wondering if it has the binary log feature enabled and if there is a way to access it somehow?

Binary log documentaion: https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

1 Answer

Hello,

Yes, it is enabled:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

I think that it is necessary for the MySQL replication and it is used by the read-only replicas. However, I don’t believe that you would be able to access them as this is a managed service.

Is there a particular reason why you need to access the bin logs?

Regards,
Bobby

  • Thank you for your reply.

    We want to access the binary log to investigate the possibility of using it to track data changes if necessary, instead of setting up manually a changelog system (since the binary log is already logging the information that we need).

    • Hello,

      I’ve tried testing that at my end and as I was able to get the following information with the default doadmin user:

      mysql> show binary logs;
      

      Output:

      +---------------+-----------+-----------+
      | Log_name      | File_size | Encrypted |
      +---------------+-----------+-----------+
      | binlog.004618 |       239 | No        |
      | binlog.004619 |       239 | No        |
      | binlog.004620 |       195 | No        |
      +---------------+-----------+-----------+
      

      Then I was able to check the events:

      mysql> show binlog events in 'binlog.004618';
      

      Output:

      +---------------+-----+----------------+-----------+-------------+---------------------------------------------+
      | Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                        |
      +---------------+-----+----------------+-----------+-------------+---------------------------------------------+
      | binlog.004618 |   4 | Format_desc    |         6 |         124 | Server ver: 8.0.16, Binlog ver: 4           |
      | binlog.004618 | 124 | Previous_gtids |         6 |         195 | 325feaec-d84b-11e9-8d90-4e7391931de6:1-1042 |
      | binlog.004618 | 195 | Rotate         |         6 |         239 | binlog.004619;pos=4                         |
      +---------------+-----+----------------+-----------+-------------+---------------------------------------------+
      

      However, I don’t think that you could access the binary logs directly with a tool like mysqlbinlog as you need direct access to the files via SSH for example.

      If you wish, you could try getting in touch with DigitalOcean’s support team and cross-checking if you could get direct access to the files somehow, but as this is a managed service it is quite unlikely:

      https://www.digitalocean.com/company/contact/#support

      Hope that this helps!
      Regards,
      Bobby

Have another answer? Share your knowledge.