How to Manage MySQL Binary Logs on FlyWP

If you’re managing your website or application with FlyWP, you’re already familiar with its power and flexibility. But like any advanced hosting environment, it requires occasional maintenance to keep everything running smoothly. One often overlooked but crucial aspect is managing MySQL binary logs. 

These logs are a fantastic feature, enabling point-in-time recovery and replication. However, logs can grow out of control without proper management, consume precious disk space, and impact your server performance. Let’s walk through how to configure binary log expiration settings or manually purge logs on FlyWP — step by step.

Why Do Binary Logs Matter?

Binary logs in MySQL serve two primary purposes:

• Point-in-Time Recovery: They allow you to restore your database to a specific moment in time, which can be invaluable in the event of data loss or corruption.
• Replication:  They enable data replication to other servers, ensuring consistency across multiple environments.

But, here’s the fact that if you leave MySQL binlogs unmanaged, these can pile up, hog your server’s disk space, and potentially impact performance. So managing binary logs properly is a must to maintain the best practices of your server management operation.

Let’s check the process of managing the Binlogs on FlyWP: 

Auto Configure Binary Log Expiry Time (Recommended)

Before setting up MySQL binary log auto purge, make sure you have SSH access to your FlyWP server. You’ll need this to make the necessary adjustments to go ahead.

Connect to Your Server via SSH: Open your terminal and connect to your FlyWP server using this command

ssh [email protected]

Once logged in, you’re ready to tackle binary log management. To automatically manage the lifespan of binary logs, you need to update your MySQL configuration.

1. Locate the MySQL Configuration File

The MySQL configuration file for FlyWP is found at:

/home/fly/.fly/config/mysql/my.cnf

2. Update the Configuration

• Open the file using a text editor:

nano /home/fly/.fly/config/mysql/my.cnf

• Look for the Binary Log Settings section, which may look like this:

# Binary Log Settings

# skip-log-bin

binlog_expire_logs_seconds=259200 # 3 days

Modify it as follows:

• Remove or comment out the skip-log-bin line.
• Uncomment the binlog_expire_logs_seconds line and set the expiry time as per your needs.

For example, to keep logs for 3 days:

# Binary Log Settings

# skip-log-bin

binlog_expire_logs_seconds=259200 # 3 days

• Save and Exit (Save the changes and close the file):
  Press Ctrl + O, then Enter to save.
  Press Ctrl + X to exit.

After you press Ctrl + X to exit, you will return to the previous window, which will look like the image below:

3. Restart the MySQL Service

After making changes, restart the MySQL service to apply the new configuration:

docker compose -f /home/fly/.fly/docker-compose.yml restart mysql

Manually Purge Binary Logs

MySQL allows manual purging if you prefer cleaning up binary logs immediately or manually at your convenience. Let’s see how you can do it.

1. Execute the Purge Command

Run the following command to delete all binary logs generated before the current time:

docker compose -f /home/fly/.fly/docker-compose.yml exec mysql mysql --user="root" --password="{DB_ROOT_PASS}" -e "PURGE BINARY LOGS BEFORE NOW();"

Replace {DB_ROOT_PASS} with your MySQL root password.

2. Confirm Disk Space

Once the logs are purged, check your disk usage to ensure the cleanup was successful.

Best Practices for Managing Binary Logs

Managing binary logs is essential to maintain server performance, prevent storage overload, and ensure data replication integrity. Proper management also helps secure your data, optimize backup processes, and comply with database best practices.

1. Set an Expiry Time: Adjust binlog_expire_logs_seconds to match your data retention policy. (e.g. For 7 days, use binlog_expire_logs_seconds=604800)
2. Monitor Disk Usage: Regularly check your server’s disk space to ensure logs aren’t consuming unnecessary resources.
3. Back-Up Regularly: Before purging binary logs, always ensure you have a complete backup.
4. Avoid Disabling Logs: Binary logs are essential for data recovery and replication. Do not disable them unless you are sure they’re not needed.

Managing MySQL binary logs is a simple yet effective way to optimize server performance and storage on FlyWP. By configuring an appropriate expiry time and purging logs when needed, you can ensure a balance between maintaining essential logs and saving disk space.

If you still need more guidance on binlog management, check our docs here.

We hope this article helps you take control of binary log management on your FlyWP server. If you have any questions or face any issues, feel free to reach out to our support team.