# 解決 MariaDB binlog files 佔盡本機硬碟空間的問題 ###### tags: `MariaDB` `資料庫` `開發環境` `Homebrew` [toc] ## 參考資訊來源 - [MySQL 刪除 binlog 的正確方法](https://rosalie1211.blogspot.com/2019/03/mysqlbinlog.html) - [For homebrew mysql installs, where's my.cnf?](https://stackoverflow.com/questions/7973927/for-homebrew-mysql-installs-wheres-my-cnf) - [MySQL Bin Files Eating Lots of Disk Space](https://www.cyberciti.biz/faq/what-is-mysql-binary-log/) - [Mariadb bin log eat my disk space](https://community.easyengine.io/t/mariadb-bin-log-eat-my-disk-space-part2/10069) - [Binary log error in mysql](https://stackoverflow.com/questions/11445678/binary-log-error-in-mysql) > ERROR 1381 (HY000): You are not using binary logging. ## Custom config > (for MariaDB installed from Homebrew) ```shell $ vim ~/.my.cnf ``` ```shell [mysqld] log-bin = /usr/local/var/mysql/mysql-bin expire-logs-days = 3 max-binlog-size = 500M ``` ### 補充資訊 > 2024/05/26 Sun 2024/05/26 這一天剛好重建本機開發環境的 MariadB，踩了一些雷，特此做些筆記。這次安裝的是 MariaDB 10.11，有一些小細節和先前不太一樣了。 ```shell $ vim ~/.my.cnf ``` ```shell [mariadb] # 啟用 binary log，路徑和檔名皆使用預設 # log_bin (只寫這樣即可) # 啟用 binary log，路徑預設，檔名使用自訂的 mariadb-bin prefix # EX. log_bin = mariadb-bin # 啟用 binary log，路徑/名稱 自訂 # EX. log_bin = /opt/homebrew/var/mysql/mariadb-bin log_bin = /usr/local/var/logs/mysql/binlog/mariadb-bin binlog_format = mixed expire_logs_days = 2 max_binlog_size = 1000M [mysqld] default_time_zone='+00:00' ``` - Custom config 檔案一樣可放在你自己的 home directory `~/.my.cnf` - 有部份的變數 v10.11 需放在 `[mariadb]` 區塊 - Config file 之中變數名稱都使用底線 "_" ，用 dash "-" 會發生錯誤， `$ homebrew services start mariadb` 開不起服務 - MariaDB 預設不啟用 binary log，需自行啟用，可登入 console 之後下指令，或是編輯 config file 設定 編輯完成 `~/.my.cnf` 請記得將 MariaDB 關閉再重啟。 ``` $ brew services stop mariadb $ brew services start mariadb ``` 直接執行 `$ brew services restart mariadb` 也是可以啦，只是 mariadb 若是因 config 設定錯而開不了，有可能因此卡住，你就還是得打一次 `$brew services stop mariadb`。 有一件很重要的事情，如果你要自訂 binlog 路徑，你有可能會需要設好路徑權限，否則 mariadb 啟動會失敗。 ``` $ sudo chmod -R 0777 /usr/local/var/logs/mysql/binlog ``` 存取權限開到 777 當然是比較不好，有風險。比較嚴謹的做法還是把 user/group 都設好，並指定更恰當的權限設定。 將 MariaDB 重啟之後，可以登入 console 看一下設定結果。 ``` $ mariadb -h 127.0.0.1 -u root -p password: (輸入密碼) ``` ``` MariaDB [(none)]> show session variables like 'time_zone'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | time_zone | +00:00 | +---------------+--------+ 1 row in set (0.002 sec) MariaDB [(none)]> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.002 sec) MariaDB [(none)]> show binary logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 330 | +--------------------+-----------+ 1 row in set (0.000 sec) MariaDB [(none)]> ``` #### 參考資料 - [官方文件 - Time Zones](https://mariadb.com/kb/en/time-zones/) - [官方文件 - show binary logs](https://mariadb.com/kb/en/show-binary-logs/) - [官方文件 - Configuring MariaDB with Option Files](https://mariadb.com/kb/en/configuring-mariadb-with-option-files/) - [Binary Logs for MariaDB Community Server 10.6](https://mariadb.com/docs/server/ref/cs10.6/logging/binary/) - [Chmod to allow read and write permissions for directory](https://superuser.com/questions/126073/chmod-to-allow-read-and-write-permissions-for-directory) - [官方文件 - Configuring MariaDB with Option Files](https://mariadb.com/kb/en/configuring-mariadb-with-option-files/) - [How To Start Logging With MariaDB](https://betterstack.com/community/guides/logging/how-to-start-logging-with-mariadb/) - [Is it safe to delete mysql-bin files?](https://dba.stackexchange.com/questions/41050/is-it-safe-to-delete-mysql-bin-files) - [https://superuser.com/questions/590683/how-do-i-find-my-user-id-and-group-in-mac-os-x](https://superuser.com/questions/590683/how-do-i-find-my-user-id-and-group-in-mac-os-x) - [Mysql binlog path from mysql console](https://stackoverflow.com/questions/20372106/mysql-binlog-path-from-mysql-console) - [my.cnf example](https://gist.github.com/fevangelou/fb72f36bbe333e059b66) ## Setting and Result ```shell $ brew services restart mariadb $ mysql -h 127.0.0.1 -u XXXX -p <default used database> ``` ``` MariaDB [database name] > show variables like 'expire_logs_days'; ``` ``` MariaDB [database name] > show variables like 'max_binlog_size'; ``` ``` MariaDB [database name] > show binary logs; ```