如何下載 Aurora MySQL binlog 到本地伺服器？

Question

需求：需要從 binlog 中獲取指定更新行為的具體細節，這個會是長期需求。

RDS 引擎為 Aurora MySQL ，查閱文檔：
(https://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/UserGuide/USER_LogAccess.MySQL.Binarylog.html)

操作步驟：
1.在 EC2 上安裝 mysql 8.0.35 客戶端，連接 RDS 獲取結果：
```
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW BINARY LOGS;
+----------------------------+-----------+-----------+
| Log_name                   | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000178 | 134217798 | No        |
| mysql-bin-changelog.000179 | 134218106 | No        |
| mysql-bin-changelog.000180 | 134217828 | No        |
| mysql-bin-changelog.000181 | 134217876 | No        |
| mysql-bin-changelog.000182 | 134218108 | No        |
| mysql-bin-changelog.000183 | 134217855 | No        |
| mysql-bin-changelog.000184 | 134217899 | No        |
| mysql-bin-changelog.000185 | 134218171 | No        |
| mysql-bin-changelog.000186 | 134218287 | No        |
| mysql-bin-changelog.000187 | 134217919 | No        |
| mysql-bin-changelog.000188 | 134217812 | No        |
| mysql-bin-changelog.000189 | 134218197 | No        |
| mysql-bin-changelog.000190 |  82797180 | No        |
+----------------------------+-----------+-----------+
13 rows in set (0.01 sec)
```

2.在 EC2 上執行命令：
```bash
/usr/local/mysql/bin/mysqlbinlog \
    --read-from-remote-server \
    --host=${RdsHost} \
    --port=${RdsPort}  \
    --user ${RdsUser} \
    --password ${RdsPass} \
    --raw \
    --verbose \
    --result-file=/tmp/ \
    mysql-bin-changelog.000179
```
報錯提示：
```
ERROR: Got error reading packet from server: Could not find first log file name in binary log index file
```
請問如何解決？

Accepted Answer

經過嘗試，發現只與 mysqlbinlog 的參數使用方式有關係：
- `--user ${RdsUser} --password ${RdsPass}`這樣的用法有問題，提示輸入密碼即使輸入正確的密碼後依然會提示 `ERROR: Got error reading packet from server: Could not find first log file name in binary log index file`，正確用法為 `--user=${RdsUser} --password=${RdsPass}`
- `-u ${RdsUser} -p ${RdsPass}`這樣的用法也一樣會遇到上面的問題，要注意中間的空格，正確用法為 `-u${RdsUser} -p${RdsPass}`
- 上述兩種用法在非交互模式中會提示在命令行中使用密碼不太安全，個人推薦的方法是 `export MYSQL_PWD=password_of_rds_user` 然後只使用 `--user=${RdsUser}` 來完成。

Answer

你可以嘗試把 --password 後面的密碼在命令中省略，輸入後再手動輸入密碼試試看，應該會動...

如何下載 Aurora MySQL binlog 到本地伺服器？

相關內容