# 解決 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)

$ vim ~/.my.cnf

[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，有一些小細節和先前不太一樣了。

$ vim ~/.my.cnf

[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

$ 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;