MySQL 常用配置速查：綁定 IP、字符集、自動補全、重啟驗證

1. 監聽外網 IP（預設僅 127.0.0.1）

# /etc/my.cnf 或 /etc/mysql/my.cnf
[mysqld]
bind-address = 0.0.0.0   # 監聽所有介面
# 或具體 IP
bind-address = 192.168.1.100

重啟：systemctl restart mysqld

2. 統一字符集為 utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

重啟後驗證：

mysql> STATUS;
Server characterset: utf8mb4
Db     characterset: utf8mb4
Client characterset: utf8mb4
Conn.  characterset: utf8mb4

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8mb4                          |
| character_set_connection | utf8mb4                          |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | utf8mb4                          |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/share/mysql/charsets/       |
+--------------------------+----------------------------------+

3. 啟用命令列自動補全

[mysql]
# 取消註釋或新增
auto-rehash
# 或 no-auto-rehash 關閉

重啟後進入 mysql，**先 USE db_name; 再按 Tab 鍵** 觸發表名/欄位名補全。

4. 一鍵驗證腳本

#!/bin/bash
# verify_mysql_config.sh
mysql -uroot -p -e "
  SELECT @@bind_address AS bind_address;
  SHOW VARIABLES LIKE 'character_set_%';
  SHOW VARIABLES LIKE 'collation_%';
  STATUS;
"

常見坑點