MySQL 安裝部署與基礎管理完全指南：Linux/Windows、配置優化、用戶權限

Linux RPM 包安裝 (CentOS/RHEL/SUSE)

核心包說明

安裝步驟

# 1. 下載 RPM 包 (官網或鏡像)
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/MySQL-5.7.44-1.el7.x86_64.rpm-bundle.tar
tar -xf MySQL-5.7.44-1.el7.x86_64.rpm-bundle.tar

# 2. 安裝依賴
yum install -y libaio numactl-libs

# 3. 批量安裝 (自動處理依賴順序)
rpm -ivh MySQL-{server,client,devel,shared}-*.rpm

# 4. 初始化數據目錄 (5.7+ 自動生成臨時 root 密碼)
mysqld --initialize --user=mysql
# 臨時密碼在 /var/log/mysqld.log 中：A temporary password is generated for root@localhost: xxxx

# 5. 啟動服務
systemctl start mysqld
systemctl enable mysqld

# 6. 首次登錄修改密碼
mysql -u root -p'臨時密碼' --connect-expired-password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';

Windows 安裝部署

1. 下載 MSI Installer 或 ZIP 免安裝包
2. MSI：圖形化向導，默認安裝至 C:\Program Files\MySQL\MySQL Server 8.0\
3. ZIP：解壓至目標目錄 (如 D:\mysql-8.0\)，手動初始化：
   mysqld --initialize-insecure --console (無密碼) 或 --initialize --console (生成隨機密碼)
4. 註冊服務：mysqld --install MySQL80
5. 啟動：net start MySQL80 或 sc start MySQL80

安裝後驗證

# 版本檢查
mysqladmin --version
# mysqladmin Ver 8.42 Distrib 5.7.44, for linux-glibc2.12 on x86_64

# 客戶端連接測試
mysql -u root -p
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

關鍵後續配置

設置 root 密碼 (舊版本/無密安裝)

mysqladmin -u root password 'NewPass123!'
# 或更新
mysqladmin -u root -p'old' password 'new'

開機自啟

# systemd (CentOS 7+)
systemctl enable mysqld

# SysVinit (舊版)
chkconfig mysqld on
# 或編輯 /etc/rc.local 添加 /etc/init.d/mysqld start

MySQL 服務管理

用戶權限體系

創建用戶 (SQL 方式)

-- 現代語法 (5.7+)
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass123!';
CREATE USER 'readonly'@'192.168.1.%' IDENTIFIED BY 'ReadOnly123!';

-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'%';
GRANT SELECT ON mydb.* TO 'readonly'@'192.168.1.%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 刷新生效
FLUSH PRIVILEGES;

權限層級對照

常用權限速查

• SELECT、INSERT、UPDATE、DELETE：基礎 CRUD
• CREATE、DROP、ALTER、INDEX：DDL
• EXECUTE：存儲過程/函數
• FILE：LOAD DATA INFILE/SELECT ... INTO OUTFILE (高危)
• PROCESS：SHOW PROCESSLIST 看全庫
• SUPER：KILL、CHANGE MASTER、PURGE BINLOG、SET GLOBAL (最高危)

/etc/my.cnf 核心配置模板

[mysqld]
# 基礎
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
user = mysql
port = 3306
bind-address = 0.0.0.0          # 監聽所有網卡
server-id = 1                   # 複制唯一標識

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

# 連接
max_connections = 1000
max_connect_errors = 100000
wait_timeout = 600
interactive_timeout = 600

# InnoDB 核心
innodb_buffer_pool_size = 4G      # 物理內存 60-80%
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_io_capacity = 2000         # SSD IOPS

# 日志
log-error = /var/log/mysqld.log
slow_query_log = 1
slow_query_log_file = /var/log/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# Binlog (複制/恢復)
log-bin = /var/lib/mysql/binlog
binlog_format = ROW
expire_logs_days = 7
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON

[client]
default-character-set = utf8mb4
socket = /var/lib/mysql/mysql.sock

[mysql]
default-character-set = utf8mb4
auto-rehash

核心管理命令速查