MySQL 常見問題彙總：安裝、登入、密碼重置、Socket 錯誤、用戶管理、備份腳本

安裝方式

RPM

rpm -ivhU MySQL-5.0.15-0.i386.rpm
rpm -ivhU MySQL-devel-5.0.15-0.i386.rpm
rpm -ivhU MySQL-client-5.0.15-0.i386.rpm

二進位包

groupadd mysql; useradd -g mysql mysql
cd /usr/local
tar zxf mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz
ln -s mysql-standard-4.1.13-pc-linux-gnu-i686 mysql
cd mysql
scripts/mysql_install_db --user=mysql
chgrp -R mysql *
bin/mysqld_safe --user=mysql &

原始碼編譯

./configure --prefix=/usr/local/mysql --enable-assembler \
  --with-mysqld-ldflags=-all-static --localstatedir=/usr/local/mysql/data \
  --with-unix-socket-path=/tmp/mysql.sock --with-charset=complex \
  --with-low-memory --with-mit-threads
make && make install

登入與密碼管理

• 登入：mysql -u user -p password database
• 忘記 root 密碼：
  killall mysqld
mysqld_safe --skip-grant-tables &
mysql -u root
UPDATE user SET Password=PASSWORD('new') WHERE User='root';
FLUSH PRIVILEGES;
• 修改密碼：mysqladmin -u root -p'old' password 'new' 或 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new');

Socket 找不到錯誤

# 方法 1：my.cnf 指定 socket
[client]
socket = /tmp/mysql.sock

# 方法 2：TCP 連線繞過 socket
mysql -h 127.0.0.1 -u user -p

# 方法 3：建立軟連結
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

用戶授權範例

-- 任意主機，全庫讀寫
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'test1'@'%' IDENTIFIED BY 'abc';
-- 僅本機，單庫讀寫
GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO 'test2'@'localhost' IDENTIFIED BY 'abc';
-- 移除密碼
GRANT ... IDENTIFIED BY '';

常用查詢

SHOW DATABASES;
SHOW TABLES;
DESCRIBE table_name;

自動備份腳本骨架

#!/bin/bash
DBName=mysql; DBUser=root; DBPasswd=; BackupPath=/root/; LogFile=/root/db.log
NewFile="$BackupPath/db$(date +%y%m%d).tgz"
OldFile="$BackupPath/db$(date -d '5 days ago' +%y%m%d).tgz"
# 刪除 5 天前備份
[ -f "$OldFile" ] && rm -f "$OldFile"
# mysqldump 備份
mysqldump -u$DBUser -p$DBPasswd --opt $DBName > /tmp/dump.sql
tar czf "$NewFile" /tmp/dump.sql && rm /tmp/dump.sql
echo "$(date): Backup $NewFile" >> $LogFile

加入 crontab 每日執行：0 2 * * * /path/backup.sh