🗄️ MySQL / MariaDB

入門資料庫 SQL 關聯式 LAMP 備份複製更新：2026 年 5 月 20 日

最廣泛使用的開源關聯式資料庫

MySQL 與分支 MariaDB 是 LAMP 堆疊的核心，支援完整 SQL 語法、事務、索引與複製，是 Web 應用後端資料儲存的主流選擇。

安裝

$ sudo apt update && sudo apt install -y mysql-server
# 或安裝 MariaDB（MySQL 開源分支）
sudo apt install -y mariadb-server

$ sudo systemctl enable --now mysql

初始安全設定

$ sudo mysql_secure_installation

依照提示設定 root 密碼、移除匿名帳號、禁止 root 遠端登入、刪除測試資料庫。

$ sudo mysql -u root -p     # 以 root 身份登入

常用資料庫操作

-- 列出所有資料庫
SHOW DATABASES;

-- 建立資料庫（UTF-8 完整支援）
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 切換資料庫
USE myapp;

-- 列出所有資料表
SHOW TABLES;

-- 退出
EXIT;

使用者與權限管理

-- 建立使用者（只允許本機連線）
CREATE USER 'appuser'@'localhost' IDENTIFIED BY '安全密碼';

-- 賦予指定資料庫的全部權限
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';

-- 只賦予讀取權限（唯讀用途）
GRANT SELECT ON myapp.* TO 'readonly'@'localhost' IDENTIFIED BY '密碼';

-- 套用權限變更
FLUSH PRIVILEGES;

-- 查看使用者的權限
SHOW GRANTS FOR 'appuser'@'localhost';

-- 刪除使用者
DROP USER 'olduser'@'localhost';

備份與還原

備份

$ # 備份單一資料庫
mysqldump -u root -p myapp > myapp_$(date +%Y%m%d).sql

# 壓縮備份
mysqldump -u root -p myapp | gzip > myapp_$(date +%Y%m%d).sql.gz

# 備份所有資料庫
mysqldump -u root -p --all-databases > all_$(date +%Y%m%d).sql

還原

$ # 還原（資料庫需先存在）
mysql -u root -p myapp < myapp_20260520.sql

# 還原壓縮備份
gunzip < myapp_20260520.sql.gz | mysql -u root -p myapp

自動排程備份

$ crontab -e

# 每天凌晨 2 點備份，保留 30 天
0 2 * * * mysqldump -u root -p密碼 myapp | gzip > /backup/myapp_$(date +\%Y\%m\%d).sql.gz
0 3 * * * find /backup -name "myapp_*.sql.gz" -mtime +30 -delete

效能調整（/etc/mysql/mysql.conf.d/mysqld.cnf）

[mysqld]
# InnoDB 緩衝池（建議設為可用 RAM 的 70%）
innodb_buffer_pool_size = 1G

# 慢查詢日誌（找出需要優化的查詢）
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 最大連線數
max_connections = 200

$ sudo systemctl restart mysql

常見問題

✕ ERROR 1045: Access denied for user 'root'@'localhost'

原因: root 密碼錯誤，或 Ubuntu 上 root 使用 auth_socket 認證。
解法: Ubuntu 上改用 sudo mysql（免密碼），或修改認證方式：ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密碼';

✕ Too many connections

原因: 同時連線數超過 max_connections 設定值。
解法: 增加 max_connections，同時檢查應用是否有連線洩漏問題（連線用完後沒有關閉）。

安裝