DevLake is designed to collect data by first deleting the existing data and then inserting new data. While this approach ensures that the latest data is always available, it leads to a rapid increase in MySQL disk consumption. This growth is primarily caused by the large size of the binary logs generated after each data collection cycle.
Because we want to ensure that the latest data is always available. If we don't delete the existing data, some old data which has been deleted from the previous step will still be available in the DevLake database.
Connect to your MySQL server using the MySQL client or any other database management tool such as PhpMyAdmin, MySQL Workbench, etc.
Run the following command to check the current status of your binary log files:
SHOW BINARY LOGS;
This will display a list of all the binary log files that are currently available on your MySQL server.
Determine the last binary log file that you want to keep. This is the file that you want to retain for any future point-in-time recovery or replication purposes.
Run the following command to purge all binary logs that are older than the binary log file that you want to retain:
PURGE BINARY LOGS BEFORE 'DATE' ;
You need to provide the specific date and time up to which you want to purge the binary logs. The date and time should be formatted as a string in the ‘YYYY-MM-DD hh:mm:ss’ format. For example, if you want to purge all binary logs before March 22, 2023, 15:30:00, you would replace DATE with ‘2023-03-22 15:30:00’, like this:
PURGE BINARY LOGS BEFORE '2023-03-22 15:30:00' ;
Note: Keep in mind that deleting old binary log files can affect point-in-time recovery and replication capabilities, so it's important to only delete files that are no longer needed.
Additionally, it's recommended to take a backup before deleting any binary log files in case you need to restore to a point before the binary logs were purged.
Connect to your MySQL server using the MySQL client or any other database management tool such as PhpMyAdmin, MySQL Workbench, etc.
Run the following command to set the expire_logs_days global variable to the number of days that you want to keep binary logs for:
SET GLOBAL expire_logs_days = 1;
services: mysql: image: mysql:8 volumes: - mysql-storage:/var/lib/mysql restart: always ports: - "127.0.0.1:3306:3306" environment: MYSQL_ROOT_PASSWORD: admin MYSQL_DATABASE: lake MYSQL_USER: merico MYSQL_PASSWORD: merico command: --character-set-server=utf8mb4 --collation-server=utf8mb4_bin --skip-log-bin
docker-compose restart mysql